一、部署MySQL
mysql下载地址：https://dev.mysql.com/downloads/mysql/
安装5.7.43版本：
第一步：创建用户
[root@localhost ~]# useradd mysql
[root@localhost ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
第二步：解压安装介质
[root@localhost ~]# tar -xvf mysql-5.7.43-el7-x86_64.tar.gz
[root@localhost ~]# mv mysql-5.7.43-el7-x86_64 /usr/local/mysql
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql
[root@localhost mysql]# mkdir data
[root@localhost mysql]# chown -R mysql:mysql data/
第三步：初始化mysql
[root@localhost bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
注：生成初始化密码
第四步：修改配置脚本
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
character-set-server=utf8mb4
[client]
port = 3306
socket = /tmp/mysql.sock
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
第五步：启动数据库
[root@localhost ~]# vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost ~]# service mysqld start
SUCCESS!
第六步：登录root用户
[root@localhost ~]# mysql -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43
Copyright (c) 2000, 2023, 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>
#修改root用户密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY xxxx;
#查看mysql字符集
mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
#添加用户root用户远程权限：
mysql> create user root@'%';
mysql> grant all privileges on *.* to root@'%' identified by 'system';
二、mysqldump迁移数据到oceanbase
1.创建测试数据
mysql> create database prod;
mysql> use prod;
Database changed
mysql> create table names( id int primary key ,name varchar(10),age int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into names values (1,'张三',18);
insert into names values (2,'李四',28);
insert into names values (3,'王五',38);
insert into names values (4,'赵六',48);
insert into names values (5,'张三',58);
mysql> select * from names;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 18 |
| 2 | 李四 | 28 |
| 3 | 王五 | 38 |
| 4 | 赵六 | 48 |
| 5 | 张三 | 58 |
+----+--------+------+
5 rows in set (0.00 sec)
#通过mysqldump导出数据
[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases prod > ~/prod.sql
Enter password:
2.将数据导入到oceanbase
（1）通过obproxy连接myoadb 租户oceanbase集群
[admin@obproxy ~]$ obclient -h192.168.64.101 -uroot@myoadb#myob -P2883 -p -c -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 117
Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oadb |
| oceanbase |
| test |
+--------------------+
5 rows in set (0.013 sec)
（2）将mysql备份文件发送至proxy目标端（64.101），创建数据库prod恢复
[root@localhost ~]# scp prod.sql 192.168.64.101:/home/admin
[root@obproxy ~]# chown admin:admin /home/admin/prod.sql
[admin@obproxy ~]$ obclient -h192.168.64.101 -uroot@myoadb#myob -P2883 -p -c -A
obclient [(none)]> create database prod;
obclient [(none)]> use prod;
obclient [prod]> source /home/admin/prod.sql;
Query OK, 0 rows affected (0.002 sec)
obclient [prod]> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| names |
obclient [prod]> select * from names;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 18 |
| 2 | 李四 | 28 |
| 3 | 王五 | 38 |
| 4 | 赵六 | 48 |
| 5 | 张三 | 58 |
+----+--------+------+
5 rows in set (0.012 sec)
注：数据从mysql导入至oceanbase数据库成功。
三、通过datax从mysql离线导入数据到oceanbase
1.datax下载
下载地址：https://github.com/alibaba/DataX
Google浏览器无法下载，使用火狐或者Edge下载即可
[root@localhost ~]# tar -xvf datax.tar.gz
#测试datax是否安装成功
[root@localhost bin]# python datax.py ../job/job.json
日志打印：[root@localhost ~]# cp datax/job/job.json datax/job/mysql.json
2024-03-13 22:07:41.402 [job-0] INFO JobContainer -
任务启动时刻 : 2024-03-13 22:07:31
任务结束时刻 : 2024-03-13 22:07:41
任务总计耗时 : 10s
任务平均流量 : 253.91KB/s
记录写入速度 : 10000rec/s
读出记录总数 : 100000
读写失败总数 : 0
2.下载并安装maven
[root@localhost ~]# tar -xvf apache-maven-3.3.9-bin.tar.gz
（1）#配置maven环境变量
M2_HOME=/root/apache-maven-3.3.9
export PATH=${M2_HOME}/bin:${PATH}
#重新加载配置文件
[root@localhost ~]# source /etc/profile
[root@localhost ~]# mvn -v
（2）编辑json
[root@localhost job]# vi /root/datax/job/mysql.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "system",
"column": [
"*"
],
"connection": [
{
"table": [
"t1"
],
"jdbcUrl": ["jdbc:mysql://192.168.64.105:3306/prod?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table business"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obdemo:sys||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.64.101:2883/prod",
"table": [
"t1"
]
}
],
"username": "root",
"password":"system",
"writerThreadCount":10,
"batchSize": 100,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
3.执行数据迁移
[root@localhost ~]# cd /root/datax/job
[root@localhost job]# python ../bin/datax.py mysql.json
