一、使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中。
1.在MySQL中创建数据库：
[admin@dell angel ~\]$ mysql －－version # 查询MySQL版本号
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86\_64) using readline 5.1
启动MYSQL：
systemctl start mariadb
连接MYSQL：mysql -uroot -p123 -P3306 -c -A -h 127.1
查看数据库：
创建数据库：create database if not exists lxs_db4;
打开数据库：use lxs_db4;
创建表：
CREATE TABLE `table4_lxs` (
`lxs_id` int(11) NOT NULL AUTO_INCREMENT,
`lxsname` varchar(50) NOT NULL,
PRIMARY KEY (`lxs_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
MariaDB [lxs_db4]> show tables;
±------------------+
| Tables_in_lxs_db4 |
±------------------+
| table4_lxs |
±------------------+
1 row in set (0.00 sec)
给表中插入数据：
MariaDB [lxs_db4]> insert into table4_lxs (lxs_id, lxsname) value(1, ‘angel’);
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db4]> insert into table4_lxs (lxs_id, lxsname) value(2, ‘alice’);
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db4]> select * from table4_lxs;
±-------±--------+
| lxs_id | lxsname |
±-------±--------+
| 1 | angel |
| 2 | alice |
±-------±--------+
2 rows in set (0.01 sec)
2.在MySQL数据库中导出表的结构和数据：
[admin@dell angel ~]$ mysqldump -uroot -p123 -P3306 -h127.1 -d lxs_db4 --compact>table4_lxs_ddl.sql
[admin@dell angel ~]$ mysqldump -uroot -p123 -P3306 -h127.1 -t lxs_db4 > table4_lxs_data.sql
[admin@dell angel ~]$ ll /home/admin/*.sql
-rw-rw-r–. 1 admin admin 1393 3月 23 04:38 /home/admin/table4_lxs_data.sql
-rw-rw-r–. 1 admin admin 353 3月 23 04:33 /home/admin/table4_lxs_ddl.sql
3.启动OB：
[admin@dell angel ~]$ cd /home/admin/oceanbase && bin/observer -i wlp1s0 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store -r ‘192.168.43.196:2882:2881’ -c 20220221 -n obdemo -o “memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=16,net_thread_count=4,datafile_size=50G,stack_size=1536K”
查看进程：
ps -ef|grep observer
监听：
netstat -ntlp
集群：
obclient -h 192.168.43.196 -P 2881 -uroot -c -A
MySQL [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
±-------------------+
7 rows in set (0.010 sec)
MySQL [(none)]> exit
Bye
有OB数据库证明集群成功。
连接代理：cd ~/obproxy-3.2.0 && bin/obproxy -r “192.168.43.196:2881” -p 2883 -o “enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false” -c obdemo
查看进程：ps -ef|grep obproxy
监听：netstat -ntlp |grep obproxy
用代理登录OB：obclient -h 192.168.43.196 -u root@sys#obdemo -P 2883 -p123 -c -A oceanbase
MySQL [oceanbase]> show databases;
±-------------------+
| Database |
±-------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
±-------------------+
7 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-03-20 14:17:38.179901 | 2022-03-20 14:17:38.179901 | 1 | sys_unit_config | 5 | 2.5 | 1610612736 | 1342177280 | 10000 | 5000 | 53687091200 | 9223372036854775807 |
| 2022-03-20 19:01:31.380284 | 2022-03-20 19:01:31.380284 | 1001 | lxs_unit1 | 1 | 1 | 1073741824 | 1073741824 | 1000 | 1000 | 10737418240 | 10000 |
±---------------------------±---------------------------±---------------±----------------±--------±--------±-----------±-----------±---------±---------±--------------±--------------------+
2 rows in set (0.015 sec)
MySQL [oceanbase]> select resource_pool_id, name from __all_resource_pool;
±-----------------±----------+
| resource_pool_id | name |
±-----------------±----------+
| 1 | sys_pool |
| 1001 | lxs_pool1 |
±-----------------±----------+
2 rows in set (0.004 sec)
MySQL [oceanbase]> select gmt_create,gmt_modified,tenant_id,tenant_name,replica_num,zone_list,primary_zone,info,locality,default_tablegroup_id,drop_tenant_time,status from __all_tenant;
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------+
| gmt_create | gmt_modified | tenant_id | tenant_name | replica_num | zone_list | primary_zone | info | locality | default_tablegroup_id | drop_tenant_time | status |
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------+
| 2022-03-20 14:17:38.200085 | 2022-03-20 14:17:38.200085 | 1 | sys | -1 | zone1 | zone1 | system tenant | FULL{1}@zone1 | -1 | -1 | TENANT_STATUS_NORMAL |
| 2022-03-20 19:05:40.831319 | 2022-03-20 19:05:40.831319 | 1001 | lxs_tenant1 | -1 | zone1 | RANDOM | | FULL{1}@zone1 | -1 | -1 | TENANT_STATUS_NORMAL |
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------+
2 rows in set (0.002 sec)
MySQL [oceanbase]> exit
Bye
设置白名单：
[admin@dell angel obproxy-3.2.0]$ obclient -h 192.168.43.196 -u root@sys#obdemo -P 2883 -p123 -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.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]> ALTER TENANT lxs_tenant1 SET VARIABLES ob_tcp_invited_nodes=’%’;
Query OK, 0 rows affected (0.011 sec)
MySQL [oceanbase]>
登录租户：
[admin@dell angel obproxy-3.2.0]$ obclient -h 192.168.43.196 -u root@lxs_tenant1 -P 2883 -c -A
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 © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MySQL [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
| lxs_db |
±-------------------+
5 rows in set (0.016 sec)
MySQL [(none)]> use lxs_db
Database changed
MySQL [lxs_db]> show tables;
Empty set (0.007 sec)
4.导入MYSQL数据库的表到OB中：
导入数据：
MySQL [lxs_db]> source /home/admin/table4_lxs_data.sql
Query OK, 0 rows affected (0.002 sec)
导入表结构：
MySQL [lxs_db]> source /home/admin/table4_lxs_ddl.sql
Query OK, 0 rows affected (0.001 sec)
MySQL [lxs_db]> show tables;
±-----------------+
| Tables_in_lxs_db |
±-----------------+
| table4_lxs |
±-----------------+
1 row in set (0.002 sec)
MySQL [lxs_db]> select * from table4_lxs;
±-------±--------+
| lxs_id | lxsname |
±-------±--------+
| 1 | angel |
| 2 | alice |
±-------±--------+
2 rows in set (0.001 sec)
MySQL [lxs_db]>
MySQL [lxs_db]> select count(*) from table4_lxs;
±---------+
| count(*) |
±---------+
| 2 |
±---------+
1 row in set (0.017 sec)
MySQL [lxs_db]>
5.导入成功，踩过和坑：
a.mysqldump不支持SQL_NOTES，删除相关语句。
b.mysqldump不支持latin1，删除相关语句。
c.特别要注意创建表的语法格式！
如果查看DDL 文件中，直接指定了CHARSET=latin1，会导致导入数据时出现乱码，为了防止这种情况发生，可以在创建表格的时候做成这样的格式：
CREATE TABLE `table4_lxs` ( `lxs_id` int(11) NOT NULL AUTO_INCREMENT, `lxsname` varchar(50) NOT NULL, PRIMARY KEY (`lxs_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
后面在MySQL上建表的时候，可以先指定charset utf8，或者直接
把charset 删除掉也可以。
二、离线使用 datax 迁移 MySQL表的数据 到 OceanBase 用户的表中：
1.下载datax ：
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz # 下载 datax
tar -xf datax.tar.gz # 解压
find ./datax/plugin -name “.*” | xargs rm –f # 删除datax中的隐藏文件
2.验证datax 的有效性：
sudo python /usr/local/datax/bin/datax.py …/job/job.json
验证成功！
3.编辑json文件：
{
"job": {
"content": \[{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": \[
"\*"
\],
"connection": \[{
"jdbcUrl": \["jdbc:mysql://127.0.0.1:3306/lxs\_db4?useUnicode=true&characterEncoding=utf8"\],
"table": \[
"table4\_lxs"
\]
}\],
"password": "123",
"username": "root",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": \[
"\*"
\],
"preSql": \[
"truncate table lxs\_table4"
\],
"connection": \[{
"jdbcUrl": "||\_dsc\_ob10\_dsc\_||obdemo:lxs\_tenant1||\_dsc\_ob10\_dsc\_||jdbc:oceanbase://192.168.43.196:2883/lxs\_db4",
"table": \[
"lxs\_table4"
\]
}\],
"obWriteMode": "insert",
"password": "123",
"username": "root"
}
}
}\],
"setting": {
"speed": {
"channel": "4"
}
}
}
}
4.命令执行成功：
sudo python /usr/local/datax/bin/datax.py /usr/local/datax/job/table4 .json
5.踩过的坑：
- datax执行时数据库及表的密码必须与json文件一致，否则无法执行成功。
| 给我点个赞 |
