刷到一个比较骚的DataX迁移MySQL到OB的方法...
詹姆斯邦德007约 8 分钟作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
擅长主流数据Oracle、MySQL、PG、openGauss运维
备份恢复，安装迁移，性能优化、故障应急处理等可提供技术业务：
1.DB故障处理/疑难杂症远程支援
2.Mysql/PG/Oracle/openGauss
数据库部署及数仓搭建
•••
微信:jem_db
QQ交流群:587159446
公众号：IT邦德
@
1.OceanBase4.2 集群
1.1 集群状态
1.2 租户使用
2.MySQL8
2.1 安装部署
2.2 相关配置
2.3 压测sysbench部署
2.4 sysbench生成数据
3.DataX
3.1 下载安装
3.2 模板配置
4.同步作业
4.1 同步表结构
4.2 启动作业
5.报错
6.总结
前言
本文详细介绍了从MySQL8.0版本向OceanBase4.2迁移数据的详细过程
1.OceanBase4.2 集群
1.1 集群状态
1.启动 OceanBase 数据库
[root@centos79 ~]# su - admin
[admin@centos79 ~]$ obd cluster start obtest2.查看集群状态
[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;
+-----------+-------------+--------------+------------+--------------------+
| TENANT_ID | TENANT_NAME | PRIMARY_ZONE | COMPATIBLE | COMPATIBILITY_MODE |
+-----------+-------------+--------------+------------+--------------------+
| 1 | sys | RANDOM | 4.2.2.0 | MYSQL |
| 1001 | META$1002 | zone1 | 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;
+-----------+-------------+--------------+------------+--------------------+
| TENANT_ID | TENANT_NAME | PRIMARY_ZONE | COMPATIBLE | COMPATIBILITY_MODE |
+-----------+-------------+--------------+------------+--------------------+
| 1 | sys | RANDOM | 4.2.2.0 | MYSQL |
| 1001 | META$1002 | zone1 | 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-libs2 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 sysbenchError: 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.gztar -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.jsoncat > /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.json2023-03-15 17:12:11.358 [job-0] INFO JobContainer - PerfTrace not enable!
2023-03-15 17:12:11.359 [job-0] INFO StandAloneJobContainerCommunicator - Total 100000 records, 18788895 bytes | Speed 1.79MB/s, 10000 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 3.153s | All Task WaitReaderTime 0.719s | Percentage 100.00%
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迁移方便，便捷，文末列出的报错希望能给大家带来帮助。