📣 1.OceanBase4.2 集群

✨ 1.1 集群状态

1.启动 OceanBase 数据库

[root@centos79 ~]# su - admin

[admin@centos79 ~]$ obd cluster start obtest

2.查看集群状态

[admin@centos79 ~]$ obd cluster display obtest

✨ 1.2 租户使用

1.租户信息

--使用 root 用户登录到集群的 sys 租户。

[admin@centos79 ~]$ obclient -h192.168.3.20 -uroot@sys -P2883 -p'gxmxiv4fV6uKhJfgDktn' -A

--通过 DBA_OB_TENANTS 视图，查看所有的租户信息

obclient [(none)]> select TENANT_ID,TENANT_NAME,PRIMARY_ZONE,COMPATIBLE,COMPATIBILITY_MODE from oceanbase.DBA_OB_TENANTS;

+-----------+-------------+--------------+------------+--------------------+

+-----------+-------------+--------------+------------+--------------------+

| 1 | sys | RANDOM | 4.2.2.0 | MYSQL |

| 1002 | mq_t1 | zone1 | 4.2.2.0 | MYSQL |

+-----------+-------------+--------------+------------+--------------------+

1.租户信息

--使用 root 用户登录到集群的 sys 租户。

[admin@centos79 ~]$ obclient -h192.168.3.20 -uroot@sys -P2883 -p'gxmxiv4fV6uKhJfgDktn' -A

--通过 DBA_OB_TENANTS 视图，查看所有的租户信息

obclient [(none)]> select TENANT_ID,TENANT_NAME,PRIMARY_ZONE,COMPATIBLE,COMPATIBILITY_MODE from oceanbase.DBA_OB_TENANTS;

+-----------+-------------+--------------+------------+--------------------+

+-----------+-------------+--------------+------------+--------------------+

| 1 | sys | RANDOM | 4.2.2.0 | MYSQL |

| 1002 | mq_t1 | zone1 | 4.2.2.0 | MYSQL |

+-----------+-------------+--------------+------------+--------------------+

📣 2.MySQL8

✨ 2.1 安装部署

1 删除mariadb

先查看一下是否已经安装了

rpm -qa|grep mariadb

删除mariadb

rpm -e --nodeps mariadb-libs

2 RPM包下载

yum install wget -y && wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar

3 解压缩

tar -xvf mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar

4 安装

yum -y install libstdc++*

yum -y install libaio

yum -y install perl perl-devel

yum -y install libncurses*

yum -y install autoconf

yum -y install numactl.x86_64

安装 community-common

rpm -ivh --nodeps --force mysql-community-common-8.0.33-1.el7.x86_64.rpm

安装 community-libs

rpm -ivh --nodeps --force mysql-community-libs-8.0.33-1.el7.x86_64.rpm

安装 community-client

rpm -ivh --nodeps --force mysql-community-client-8.0.33-1.el7.x86_64.rpm

安装 community-server

rpm -ivh --nodeps --force mysql-community-server-8.0.33-1.el7.x86_64.rpm

5 初始化

mysqld --initialize

chown mysql:mysql /var/lib/mysql -R

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

✨ 2.2 相关配置

--默认密码

cat /var/log/mysqld.log | grep password |awk -F ": " '{print $2}'

--修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

--设置远程

create user 'root'@'%' identified with mysql_native_password by 'root';

grant all privileges on *.* to 'root'@'%' with grant option;

flush privileges;

✨ 2.3 压测sysbench部署

sysbench是一款开源的多线程性能测试工具，可以执行CPU/内存/线程/IO/数据库等方面的性能测试

[root@centos79 ~]# yum install sudo

[root@centos79 ~]# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

[root@centos79 ~]# sudo yum -y install sysbench

Error: Package: 2:postfix-2.10.1-9.el7.x86_64 (@anaconda)

Requires: libmysqlclient.so.18(libmysqlclient_18)(64bit)

Error: Package: sysbench-1.0.20-1.el7.x86_64 (akopytov_sysbench)

Requires: libmysqlclient.so.18()(64bit)

Error: Package: sysbench-1.0.20-1.el7.x86_64 (akopytov_sysbench)

Requires: libmysqlclient.so.18(libmysqlclient_18)(64bit)

Error: Package: 2:postfix-2.10.1-9.el7.x86_64 (@anaconda)

Requires: libmysqlclient.so.18()(64bit)

[root@centos79 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm

[root@centos79 ~]# rpm -ivh mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm

[root@centos79 ~]# find / -name libmysqlclient.so.18

find: ‘/proc/22337’: No such file or directory

/usr/lib64/mysql/libmysqlclient.so.18

--版本号

[root@centos79 ~]# sysbench --version

sysbench 1.0.20

✨ 2.4 sysbench生成数据

1.登陆MySQL

[root@centos79 ~]# mysql -uroot -p -h192.168.3.20

mysql> create database obtest;

2.sysbench写数据

[root@centos79 ~]# sysbench /usr/share/sysbench/oltp_common.lua \

--time=300 --mysql-host=192.168.3.20 --mysql-port=3306 --mysql-user=root \

--mysql-password=root --mysql-db=obtest --table-size=100000 --tables=10 \

--threads=16 --events=999999999 prepare

3.确认数据

[root@centos79 ~]# mysql -uroot -p -h192.168.3.20

mysql> use obtest;

mysql> show tables;

+------------------+

| Tables_in_obtest |

+------------------+

| sbtest1 |

| sbtest10 |

| sbtest2 |

| sbtest3 |

| sbtest4 |

| sbtest5 |

| sbtest6 |

| sbtest7 |

| sbtest8 |

| sbtest9 |

+------------------+

10 rows in set (0.01 sec)

mysql> select count(*) from sbtest1;

+----------+

| count(*) |

+----------+

| 100000 |

+----------+

mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as total_size

FROM information_schema.TABLES

WHERE TABLE_SCHEMA='obtest'

order by length desc;

📣 3.DataX

✨ 3.1 下载安装

wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz

tar -xf datax.tar.gz -C /usr/local/

cd /usr/local/datax

# 删除datax中的隐藏文件

find /usr/local/datax/plugin -name ".*" | xargs rm -f

--安装依赖

yum install -y python2 java

-- 生成模板文件

python2 /usr/local/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /usr/local/datax/job/my2ob.json

✨ 3.2 模板配置

# 根据实际环境修改

vi /usr/local/datax/job/my2ob.json

cat > /usr/local/datax/job/my2ob.json <<"EOF"

{

"job": {

"content": [

{

"reader": {

"name": "mysqlreader",

"parameter": {

"column": ["*"],

"connection": [

{

"jdbcUrl": ["jdbc:mysql://192.168.3.20:3306/obtest"],

"table": ["sbtest1"]

}

],

"password": "root",

"username": "root",

"where": ""

}

},

"writer": {

"name": "oceanbasev10writer",

"parameter": {

"column": ["*"],

"connection": [

{

"jdbcUrl": "||_dsc_ob10_dsc_||obcluster:mq_t1||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.3.20:2883/testdb",

"table": ["sbtest1"]

}

],

"obWriteMode": "insert",

"password": "123456",

"username": "root"

}

],

"setting": {

"speed": {

"channel": "1"

}

EOF

📣 4.同步作业

✨ 4.1 同步表结构

--使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中

1.导出mysql数据

# 导出表结构

[root@centos79 ~]# mysqldump -h127.0.0.1 -P3306 -proot -uroot -d obtest --compact > mysql_ddl.sql

# 导出数据

[admin@10 tpcc-mysql]$ mysqldump -h127.0.0.1 -P3306 -proot -uroot -t obtest --compact > mysql_data.sql

[root@centos79 ~]# chown admin:admin /root/mysql_ddl.sql

[root@centos79 ~]# chown admin:admin /root/mysql_data.sql

[root@centos79 ~]# cp mysql_d* /home/admin

2.导入OceanBase

--租户登陆

obclient -uroot@mq_t1 -h127.1 -P2883 -p123456 -A

-- 导入表结构

MySQL [(none)]> use testdb

MySQL [(none)]> source /home/admin/mysql_ddl.sql

obclient [testdb]> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| course_tb |

| sbtest1 |

| sbtest10 |

| sbtest2 |

| sbtest3 |

| sbtest4 |

| sbtest5 |

| sbtest6 |

| sbtest7 |

| sbtest8 |

| sbtest9 |

+------------------+

11 rows in set (0.002 sec)

.............

-- 导入数据

MySQL [testdb]> source /home/admin/mysql_ddl.sql

✨ 4.2 启动作业

--启动同步作业

python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/my2ob.json

2023-03-15 17:12:11.358 [job-0] INFO JobContainer - PerfTrace not enable!

2023-03-15 17:12:11.360 [job-0] INFO JobContainer -

任务启动时刻 : 2023-03-15 17:12:11

任务结束时刻 : 2023-03-15 17:13:18

任务总计耗时 : 11s

任务平均流量 : 1.79MB/s

记录写入速度 : 10000rec/s

读出记录总数 : 100000

读写失败总数 : 0

📣 5.报错

1.DataX启动作业，无法连接MySQL如下报错
2024-03-16 13:02:59.505 [job-0] WARN  DBUtil - test connection of [jdbc:mysql://192.168.3.20:3306/obtest?useUnicode=true&characterEncoding=utf8&useSSL=false] failed, for Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的 账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).].  -  具体错误信息为：com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server..
这个在确定数据库连接没有问题的情况下，
应该是数据库驱动版本的问题
确定一下使用的数据库的版本是不是mysql8及其以上，
如果是，需要在/datax/lib下加入一个mysql8版本的驱动jar包
2.OceanBase4.2导入表结构报错
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
是因为linux下MySQL版本不兼容导致的
把文件中的所有的utf8mb4_0900_ai_ci替换为utf8mb4_general_ci
以及utf8mb4替换为utf8
obclient [testdb]> SELECT COLLATION("oceanbase");
+------------------------+
| COLLATION("oceanbase") |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.001 sec)
3.DataX运行时报错
运行时出错, 具体原因请参看DataX运行结束时的错误诊断信息 .].  - java.lang.NumberFormatException: multiple points
        at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1914)
根DataX版本有关，下载最新的DataX即可

📣 6.总结

从MySQL向OceanBase迁移数据的基本方法：mysqldump、datax 、canal 等.本次基于datax迁移方便，便捷，文末列出的报错希望能给大家带来帮助。

ITPub博客

刷到一个比较骚的DataX迁移MySQL到OB的方法...

最新文章