迁移 MySQL 数据到 OceanBase 集群

mysqldump

MySQL 准备数据

• 部署 mysql

  • 略

• 下载 MySQL 示例数据

  • https://dev.mysql.com/doc/index-other.html

wget https://downloads.mysql.com/docs/sakila-db.tar.gz

• 导入数据

$ tar -zxvf sakila-db.tar.gz
$ /data/mysql/mysql8.0/bin/mysql -u root -p123456 -S /data/mysql/mysql_data/data/mysql_3306.sock < sakila-schema.sql
$ /data/mysql/mysql8.0/bin/mysql -u root -p123456 -S /data/mysql/mysql_data/data/mysql_3306.sock < sakila-data.sql

$ /data/mysql/mysql8.0/bin/mysql -u root -p123456 -S /data/mysql/mysql_data/data/mysql_3306.sock -e "show tables from sakila"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+

mysqldump 导出数据

/data/mysql/mysql8.0/bin/mysqldump -u root -S /data/mysql/mysql_data/data/mysql_3306.sock -p123456  \
--single-transaction --set-gtid-purged=off --default-character-set=utf8 \
--compact --skip-add-drop-table sakila >> sakila-dump.sql

OB 导入数据

• 处理数据文件

# 去掉所有注释
cat  sakila-dump.sql |grep -v "/*\!"| grep -v "^--" > ob-sakila.sql
# 去掉建表语句中指定编码排序的部分
sed -i "s#COLLATE=utf8mb4_0900_ai_ci##g" ob-sakila.sql

• 连接 OB

obclient -h10.0.0.107 -uroot@tenant1 -P2883 -Doceanbase -A -p

• 创建库并导入数据

create database sakila;
use sakila;
source /root/ob-sakila.sql

datax

安装

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

配置同步

vi job/mysql2ob.json

{
    "job": {
        "setting": {
            "speed": {
                "channel": 4 
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "tu",
                        "password": "123456",
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "table": [
                                    "actor"
                                ],
                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/sakila?useUnicode=true&characterEncoding=utf8"]
                            }
                        ]
                    }
                },

                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": [
                                    "||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://10.0.0.107:2883/sakila?useUnicode=true&characterEncoding=utf8"
                                ],
                                "table": [
                                    "actor"
                                ]
                            }
                        ],
                        "username": "root@tenant1",
                        "password":"123456",
                        "writerThreadCount":1,
                        "batchSize": 100,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

同步

./bin/datax.py  job/mysql2ob.json