练习从mysql迁移到oceanbase
一、准备mysql环境
１.所用操作系统版本为openeuler 20.03sp2
[root@localhost srv]# cat /etc/os-release
NAME="openEuler"
VERSION="20.03 (LTS-SP2)"
ID="openEuler"
VERSION_ID="20.03"
PRETTY_NAME="openEuler 20.03 (LTS-SP2)"
ANSI_COLOR="0;31"
[root@localhost srv]# df -hT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 3.7G 0 3.7G 0% /dev
tmpfs tmpfs 3.7G 0 3.7G 0% /dev/shm
tmpfs tmpfs 3.7G 8.7M 3.7G 1% /run
tmpfs tmpfs 3.7G 0 3.7G 0% /sys/fs/cgroup
/dev/mapper/openeuler-root ext4 70G 20G 46G 31% /
tmpfs tmpfs 3.7G 0 3.7G 0% /tmp
/dev/sda1 ext4 976M 126M 784M 14% /boot
tmpfs tmpfs 746M 0 746M 0% /run/user/0
已提前建立mysql用户
[root@localhost srv]# id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)
2、下载安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
３、解压软件包
tar xvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 /opt/mysql
chown -R mysql:mysql /opt/mysql
４、建立参数文件
[root@localhost srv]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[mysqld]
port=3306
basedir=/opt/mysql
datadir=/opt/mysql/data
socket=/opt/mysql/data/mysql.sock
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4
主要是文件的位置，字符集，端口等
切换到mysql用户初始化库
su - mysql
cd /opt/mysql
mkdir data
先不生成root密码
bin/mysqld --initialize-insecure --explicit_defaults_for_timestamp --basedir=.. --datadir=data
2022-02-19T03:48:32.375940Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option
启动mysql
bin/mysqld &
４、登录数据库
mysql -uroot -h127.1 -P3306 -p
mysql>create database tpcc;
mysql>grant all privileges on tpcc.* to 'tpcc'@'%' identified by 'tpcc100;'
mysql>grant select on information_schema.* to tpcc;
mysql>exit
导入测试数据
mysql -u tpcc -h 127.1 -P 3306 -p tpcc100 -D tpcc <f1db_data.sql
登录数据库查看表导入情况
[mysql@localhost ~]$ mysql -utpcc -h127.1 -P3306 -ptpcc100 -D tpcc
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+----------------------+
| Tables_in_tpcc |
+----------------------+
| circuits |
| constructorResults |
| constructorStandings |
| constructors |
| driverStandings |
| drivers |
| lapTimes |
| pitStops |
| qualifying |
| races |
| results |
| seasons |
| status |
+----------------------+
13 rows in set (0.00 sec)
每个表的数据量
mysql> select table_name,table_rows from tables where table_schema='tpcc';
+----------------------+------------+
| table_name | table_rows |
+----------------------+------------+
| circuits | 79 |
| constructorResults | 11950 |
| constructorStandings | 12716 |
| constructors | 211 |
| driverStandings | 33394 |
| drivers | 854 |
| lapTimes | 514592 |
| pitStops | 8828 |
| qualifying | 9135 |
| races | 1080 |
| results | 25400 |
| seasons | 73 |
| status | 137 |
+----------------------+------------+
13 rows in set (0.00 sec)
mysql>exit
源数据准备完毕。
二、目标端oceanbase资源情况
１、oceanbase 是在三台openeuler20-03sp3上用OBD离线安装的1-1-1集群，操作系统情况
[root@node1 datax]# cat /etc/os-release
NAME="openEuler"
VERSION="20.03 (LTS-SP3)"
ID="openEuler"
VERSION_ID="20.03"
PRETTY_NAME="openEuler 20.03 (LTS-SP3)"
ANSI_COLOR="0;31"
[root@node1 datax]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 7.6G 0 7.6G 0% /dev
tmpfs 7.6G 0 7.6G 0% /dev/shm
tmpfs 7.6G 65M 7.6G 1% /run
tmpfs 7.6G 0 7.6G 0% /sys/fs/cgroup
/dev/mapper/openeuler-root 23G 18G 3.6G 84% /
tmpfs 7.6G 144K 7.6G 1% /tmp
/dev/sda1 976M 244M 665M 27% /boot
/dev/sdb 12G 2.2G 9.8G 19% /fcfs
tmpfs 1.6G 0 1.6G 0% /run/user/1001
tmpfs 1.6G 0 1.6G 0% /run/user/0
２、启动集群
[admin@node1 ~]$ obd cluster start ob_cluster
Get local repositories and plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
Check before start obproxy ok
Start observer ok
observer program health check ok
Connect to observer ok
Wait for observer init ok
+------------------------------------------------+
| observer |
+--------------+---------+------+-------+--------+
| ip | version | port | zone | status |
+--------------+---------+------+-------+--------+
| 192.168.56.7 | 3.1.2 | 2881 | zone1 | active |
| 192.168.56.8 | 3.1.2 | 2881 | zone2 | active |
| 192.168.56.9 | 3.1.2 | 2881 | zone3 | active |
+--------------+---------+------+-------+--------+
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
+------------------------------------------------+
| obproxy |
+--------------+------+-----------------+--------+
| ip | port | prometheus_port | status |
+--------------+------+-----------------+--------+
| 192.168.56.7 | 2883 | 2884 | active |
+--------------+------+-----------------+--------+
ob_cluster running
３、登录到集群查看资源情况
[root@node1 datax]# obclient -h192.168.56.7 -uroot@sys -P2883 -c -A -pobroot123
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [oceanbase]> select * from __all_server;
+----------------------------+----------------------------+--------------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time |
+----------------------------+----------------------------+--------------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| 2022-02-15 22:47:37.705052 | 2022-02-15 22:48:09.701386 | 192.168.56.7 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1644936486687194 | 0 | 1 | 0 |
| 2022-02-15 22:47:36.830003 | 2022-02-15 22:48:11.904630 | 192.168.56.8 | 2882 | 2 | zone2 | 2881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1644936488695873 | 0 | 1 | 0 |
| 2022-02-15 22:47:36.925526 | 2022-02-15 22:48:11.991770 | 192.168.56.9 | 2882 | 3 | zone3 | 2881 | 0 | active | 0 | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 0 | 1644936489727507 | 0 | 1 | 0 |
+----------------------------+----------------------------+--------------+----------+----+-------+------------+-----------------+--------+-----------------------+----------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
3 rows in set (0.003 sec)
MySQL [oceanbase]> select * from __all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_iops | min_iops | max_disk_size | max_session_num |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
| 2022-02-18 20:37:21.352650 | 2022-02-18 20:37:21.352650 | 1 | sys_unit_config | 5 | 2.5 | 2576980377 | 2147483648 | 10000 | 5000 | 4615831552 | 9223372036854775807 |
| 2022-02-18 20:59:58.319001 | 2022-02-18 20:59:58.319001 | 1001 | unit1 | 4 | 4 | 3221225472 | 3221225472 | 1000 | 500 | 10737418240 | 300 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+------------+------------+----------+----------+---------------+---------------------+
2 rows in set (0.005 sec)
MySQL [oceanbase]> select * from __all_unit;
+----------------------------+----------------------------+---------+------------------+----------+-------+--------------+----------+---------------------+-----------------------+----------------+--------+--------------+
| gmt_create | gmt_modified | unit_id | resource_pool_id | group_id | zone | svr_ip | svr_port | migrate_from_svr_ip | migrate_from_svr_port | manual_migrate | status | replica_type |
+----------------------------+----------------------------+---------+------------------+----------+-------+--------------+----------+---------------------+-----------------------+----------------+--------+--------------+
| 2022-02-18 20:37:21.379579 | 2022-02-18 20:37:21.379579 | 1 | 1 | 0 | zone1 | 192.168.56.7 | 2882 | | 0 | 0 | ACTIVE | 0 |
| 2022-02-18 20:37:21.395615 | 2022-02-18 20:37:21.395615 | 2 | 1 | 0 | zone2 | 192.168.56.8 | 2882 | | 0 | 0 | ACTIVE | 0 |
| 2022-02-18 20:37:21.409243 | 2022-02-18 20:37:21.409243 | 3 | 1 | 0 | zone3 | 192.168.56.9 | 2882 | | 0 | 0 | ACTIVE | 0 |
| 2022-02-18 21:00:33.347730 | 2022-02-18 21:00:33.347730 | 1001 | 1001 | 0 | zone1 | 192.168.56.7 | 2882 | | 0 | 0 | ACTIVE | 0 |
| 2022-02-18 21:00:33.356002 | 2022-02-18 21:00:33.356002 | 1002 | 1001 | 0 | zone2 | 192.168.56.8 | 2882 | | 0 | 0 | ACTIVE | 0 |
| 2022-02-18 21:00:33.366983 | 2022-02-18 21:00:33.366983 | 1003 | 1001 | 0 | zone3 | 192.168.56.9 | 2882 | | 0 | 0 | ACTIVE | 0 |
+----------------------------+----------------------------+---------+------------------+----------+-------+--------------+----------+---------------------+-----------------------+----------------+--------+--------------+
6 rows in set (0.005 sec)
４、创建普通租户
MySQL [oceanbase]> create tenant t_obdemo resource_pool_list=(‘p1’);
Query OK, 0 rows affected (6.325 sec)
MySQL [oceanbase]> alter tenant t_obdemo set variables ob_tcp_invited_nodes=’%’;
Query OK, 0 rows affected (0.058 sec)
５、连接到普通租户，建立普通数据库及用户
[admin@node1 .ssh]$ obclient -h 192.168.56.7 -P2881 -uroot@t_obdemo -p -c -A oceanbase
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221524014
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [oceanbase]> create database tpcc;
Query OK, 1 row affected (0.060 sec)
MySQL [oceanbase]> create user tpcc identified by ‘tpcc100’;
Query OK, 0 rows affected (0.078 sec)
MySQL [oceanbase]> grant all privileges on tpcc.* to tpcc@’%’;
Query OK, 0 rows affected (0.049 sec)
[admin@node1 .ssh]$ obclient -h 192.168.56.7 -P2881 -utpcc@t_obdemo -ptpcc100 -c -A tpcc
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 3221527628
Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [tpcc]> show tables;
Empty set (0.001 sec)
准备了一个空的数据库，准备接收从mysql迁移过来的数据。
三、进行数据迁移
１、用mydumper迁移
mydumper 是比mysqldump效率更高的迁移工具，支持并发操作。
下载
wget https://github.com/mydumper/mydumper/releases/download/v0.11.5-2/mydumper-0.11.5-2.el7.x86_64.rpm
安装
[root@localhost ~]# rpm -ivh mydumper-0.11.5-2.el7.x86_64.rpm
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mydumper-0.11.5-2 ################################# [100%]
安装完成后同时有了导出工具mydumper和导入工具myloader
为节省时间，本次只导出一个表races
导出前的数据量
MySQL [tpcc]> select count(1) from races;
+----------+
| count(1) |
+----------+
| 1080 |
+----------+
1 row in set (0.012 sec)
导出
mydumper -u tpcc -p tpcc100 -P 3306 -h 192.168.56.40 -B tpcc -T races -o /tmp/test
[root@node1 tmp]# cd test
[root@node1 test]# ll
total 140K
-rw-r--r-- 1 root root 75 Feb 19 08:56 metadata
-rw-r--r-- 1 root root 122K Feb 19 08:56 tpcc.races.00000.sql
-rw-r--r-- 1 root root 4 Feb 19 08:56 tpcc.races-metadata
-rw-r--r-- 1 root root 555 Feb 19 08:56 tpcc.races-schema.sql
-rw-r--r-- 1 root root 118 Feb 19 08:56 tpcc-schema-create.sql
导入到OB:
[root@node1 test]# myloader -u tpcc@t_obdemo#ob_cluster -p obroot123 -h 192.168.56.7 -P 2883 -B tpcc -d /tmp/test
** (myloader:12459): WARNING **: 09:01:36.503: Set session failed: SET SESSION SQL_LOG_BIN = 0
** (myloader:12459): CRITICAL **: 09:01:36.507: Error occours between lines: 0 and 2 on file tpcc-schema-create.sql: Unknown collation: 'utf8mb4_unicode_ci'
** (myloader:12459): WARNING **: 09:01:36.533: Set session failed: SET SESSION SQL_LOG_BIN = 0
** (myloader:12459): WARNING **: 09:01:36.550: Set session failed: SET SESSION SQL_LOG_BIN = 0
** (myloader:12459): WARNING **: 09:01:36.572: Set session failed: SET SESSION SQL_LOG_BIN = 0
** (myloader:12459): WARNING **: 09:01:36.616: Set session failed: SET SESSION SQL_LOG_BIN = 0
这里出错，是因为mysql版本是5.7.36，而OB对应的mysql版本是5.6.25，可以忽略。
查看导入的数据情况
MySQL [tpcc]> select count(1) from races;
+----------+
| count(1) |
+----------+
| 1080 |
+----------+
1 row in set (0.012 sec)
2、用datax迁移，换个较大的表lapTimes
解压datax.tar.gz
编辑迁移配置文件my2ob.json
cat my2ob.json
{
"content":[
{
"reader":{
"name":"mysqlreader",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":[
"jdbc:mysql://192.168.56.40:3306/tpcc?useSSL=false"
],
"table":[
"lapTimes"
]
}
],
"password":"*******",
"username":"tpcc"
}
},
"writer":{
"name":"oceanbasev10writer",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":"jdbc:oceanbase://192.168.56.7:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table":[
"lapTimes"
]
}
],
"obWriteMode":"insert",
"password":"*********",
"username":"tpcc@t_obdemo#ob_cluster"
}
}
}
],
"setting":{
"speed":{
"channel":10
}
}
}
执行迁移
[root@node1 datax]# bin/datax.py my2ob.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2022-02-19 08:44:33.196 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2022-02-19 08:44:33.204 [main] INFO Engine - the machine info =>
osInfo: Azul Systems, Inc. 1.8 25.302-b08
jvmInfo: Linux amd64 4.19.90-2201.4.0.0135.oe1.x86_64
cpu num: 16
totalPhysicalMemory: -0.00G
freePhysicalMemory: -0.00G
maxFileDescriptorCount: -1
currentOpenFileDescriptorCount: -1
GC Names [PS MarkSweep, PS Scavenge]
MEMORY_NAME | allocation_size | init_size
PS Eden Space | 256.00MB | 256.00MB
Code Cache | 240.00MB | 2.44MB
Compressed Class Space | 1,024.00MB | 0.00MB
PS Survivor Space | 42.50MB | 42.50MB
PS Old Gen | 683.00MB | 683.00MB
Metaspace | -0.00MB | 0.00MB
2022-02-19 08:44:33.221 [main] INFO Engine -
2022-02-19 08:45:03.932 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2022-02-19 08:45:03.932 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /srv/datax/hook
2022-02-19 08:45:03.933 [job-0] INFO JobContainer -
[total cpu info] =>
averageCpu | maxDeltaCpu | minDeltaCpu
-1.00% | -1.00% | -1.00%
[total gc info] =>
NAME | totalGCCount | maxDeltaGCCount | minDeltaGCCount | totalGCTime | maxDeltaGCTime | minDeltaGCTime
PS MarkSweep | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s
PS Scavenge | 10 | 10 | 10 | 0.094s | 0.094s | 0.094s
2022-02-19 08:45:03.934 [job-0] INFO JobContainer - PerfTrace not enable!
2022-02-19 08:45:03.934 [job-0] INFO StandAloneJobContainerCommunicator - Total 514592 records, 11168108 bytes | Speed 363.54KB/s, 17153 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 20.158s | All Task WaitReaderTime 0.822s | Percentage 100.00%
2022-02-19 08:45:03.938 [job-0] INFO JobContainer -
任务启动时刻 : 2022-02-19 08:44:33
任务结束时刻 : 2022-02-19 08:45:03
任务总计耗时 : 30s
任务平均流量 : 363.54KB/s
记录写入速度 : 17153rec/s
读出记录总数 : 514592
读写失败总数 : 0
没有失败记录，再到库中查看
MySQL [tpcc]> select count(1) from lapTimes;
+----------+
| count(1) |
+----------+
| 514592 |
+----------+
1 row in set (0.355 sec)
两边数据一致，迁移完成。
最后修改时间：2022-05-05 18:09:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
