OceanBase发布了升级版本4.0之后，我已经进行过了安装测试，感觉比3.x版本安装轻松容易多了。安装详情可见如下链接https://www.modb.pro/db/566449。
我以前做过OB3.x版本的数据迁移，感兴趣的同学可以查看https://www.modb.pro/db/411540。这一次，我将进行OB4.0版本的数据迁移，看一下与OB3.x版本的数据迁移的差距。
以下是使用mysqldump 将 mysql的表结构和数据同步到OceanBase租户中的详细步骤。
一、在MySQL中创建数据库：
1.查询MySQL版本号
[admin@dell angel ~]$ mysql --version # 查询MySQL版本号
3版-mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
4版-mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
后加 
[angel@localhost ~]$ mysql --version
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
后加[angel@XTZJ-20211224SR ~]$ mysql --version
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
[angel@XTZJ-20211224SR ~]$
2.启动MYSQL：
systemctl start mariadb
3版-直接进入下一个界面(当时由于无法截图)
4版-此时需要输入管理员密码才能进入到下一个界面。
如果不输入密码，或者输入错误会显示如下画面：
Failed to start mariadb.service: Access denied
See system logs and 'systemctl status mariadb.service' for details.
如果输入正确密码，会显示如下画面：
3.连接MYSQL：
mysql -uroot -P3306 -c -A -h 127.1
[angel@XTZJ-20211224SR ~]$ mysql -uroot -P3306 -c -A -h 127.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
4.查看数据库：
show databases;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
5.创建数据库：
create database if not exists lxs_db_20230301;
MariaDB [(none)]> create database if not exists lxs_db_20230301;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
6.打开数据库：
use lxs_db_20230301;
MariaDB [(none)]> use lxs_db_20230301;
Database changed
MariaDB [lxs_db_20230301]>
7.创建表：
CREATE TABLE `table_20230301` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
MariaDB [lxs_db_20230301]> CREATE TABLE `table_20230301` (
->
-> `id` int(11) NOT NULL AUTO_INCREMENT,
->
-> `name` varchar(50) NOT NULL,
->
-> PRIMARY KEY (`id`)
->
-> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
MariaDB [lxs_db_20230301]>
8.查看表：
show tables;
MariaDB [lxs_db_20230301]> show tables;
+---------------------------+
| Tables_in_lxs_db_20230301 |
+---------------------------+
| table_20230301 |
+---------------------------+
1 row in set (0.00 sec)
MariaDB [lxs_db_20230301]>
9.给表中插入数据：
MariaDB [lxs_db_20230301]> insert into table_20230301 (id, name) value(1, 'angel');
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db_20230301]> insert into table_20230301 (id, name) value(2, 'alice');
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db_20230301]> insert into table_20230301 (id, name) value(1, 'angel');
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db_20230301]> insert into table_20230301 (id, name) value(2, 'alice');
Query OK, 1 row affected (0.00 sec)
MariaDB [lxs_db_20230301]>
10.查询表中数据：
select * from table_20230301;
MariaDB [lxs_db_20230301]> select * from table_20230301;
+----+-------+
| id | name |
+----+-------+
| 1 | angel |
| 2 | alice |
+----+-------+
2 rows in set (0.01 sec)
MariaDB [lxs_db_20230301]>
11.退出数据库：
exit;
MariaDB [lxs_db_20230301]> exit; Bye [angel@XTZJ-20211224SR ~]$
12.查看路径：
pwd
[angel@XTZJ-20211224SR ~]$ pwd /home/angel
最后一步查看路径是因为不知道为什么开始时进入系统的路径与现在的表现形式是不同的！但是路径是一样的！
二、在MySQL数据库中导出表的结构和数据：
mysqldump -uroot -P3306 -h127.1 -d lxs_db_20230301 --compact>table_20230301_ddl.sql
mysqldump -uroot -P3306 -h127.1 -t lxs_db_20230301 > table_20230301_data.sql
ll /home/admin/*.sql
[angel@XTZJ-20211224SR ~]$ mysqldump -uroot -P3306 -h127.1 -d lxs_db_20230301 --compact>table_20230301_ddl.sql
[angel@XTZJ-20211224SR ~]$ mysqldump -uroot -P3306 -h127.1 -t lxs_db_20230301 > table_20230301_data.sql
[angel@XTZJ-20211224SR ~]$ ll /home/angel/*.sql
-rw-rw-r--. 1 angel angel 1421 3月 18 01:55 /home/angel/table_20230301_data.sql
-rw-rw-r--. 1 angel angel 344 3月 18 01:54 /home/angel/table_20230301_ddl.sql
三、启动OB4.0：
1.启动OB：
obd cluster start demo
[angel@XTZJ-20211224SR ~]$ obd cluster start demo
Get local repositories ok
Search plugins ok
Open ssh connection ok
Load cluster param plugin ok
Cluster status check ok
Check before start observer ok
Check before start obproxy ok
Check before start obagent ok
Check before start prometheus ok
Check before start grafana ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize cluster ok
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize cluster ok
Start obagent ok
obagent program health check ok
Start promethues ok
prometheus program health check ok
Connect to Prometheus ok
Initialize cluster ok
Start grafana ok
grafana program health check ok
Connect to grafana ok
Initialize cluster ok
Wait for observer init ok
+---------------------------------------------+
| observer |
+-----------+---------+------+-------+--------+
| ip | version | port | zone | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P2881 -uroot -Doceanbase
+---------------------------------------------+
| obproxy |
+-----------+------+-----------------+--------+
| ip | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 127.0.0.1 | 2883 | 2884 | active |
+-----------+------+-----------------+--------+
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase
+----------------------------------------------------+
| obagent |
+----------------+-------------+------------+--------+
| ip | server_port | pprof_port | status |
+----------------+-------------+------------+--------+
| 192.168.43.196 | 8088 | 8089 | active |
+----------------+-------------+------------+--------+
+-------------------------------------------------------+
| prometheus |
+----------------------------+------+----------+--------+
| url | user | password | status |
+----------------------------+------+----------+--------+
| http://192.168.43.196:9090 | | | active |
+----------------------------+------+----------+--------+
+---------------------------------------------------------------------+
| grafana |
+----------------------------------------+-------+-----------+--------+
| url | user | password | status |
+----------------------------------------+-------+-----------+--------+
| http://192.168.43.196:3000/d/oceanbase | admin | oceanbase | active |
+----------------------------------------+-------+-----------+--------+
demo running
[angel@XTZJ-20211224SR ~]$
2.集群：
obclient -h 127.0.0.1 -P 2881 -p123 -uroot -c -A
[angel@XTZJ-20211224SR ~]$ obclient -h 127.0.0.1 -P 2881 -p123 -uroot -c -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487619
Server version: OceanBase_CE 4.0.0.0 (r100000272022110114-6af7f9ae79cd0ecbafd4b1b88e2886ccdba0c3be) (Built Nov 1 2022 14:57:18)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [(none)]>
3.查询OB数据库：
show databases;
obclient [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| LBACSYS |
| mysql |
| oceanbase |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
7 rows in set (0.021 sec)
obclient [(none)]>
有OB数据库证明集群成功。
4.打开OB数据库：
use oceanbase;
obclient [(none)]> use oceanbase;
Database changed
obclient [oceanbase]>
5.创建资源规格：
create resource unit lxs_unit max_cpu 1, min_cpu 1, memory_size '2g', max_iops 1024, min_iops 1024, iops_weight 0, log_disk_size '2g';
obclient [oceanbase]> create resource unit lxs_unit max_cpu 1, min_cpu 1, memory_size '2g', max_iops 1024, min_iops 1024, iops_weight 0, log_disk_size '2g';
Query OK, 0 rows affected (0.020 sec)
obclient [oceanbase]>
6.查询资源规格：
select * from __all_unit_config;
obclient [oceanbase]> select * from __all_unit_config;
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | memory_size | log_disk_size | max_iops | min_iops | iops_weight |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
| 2022-11-08 15:56:02.432599 | 2022-11-08 15:56:02.432599 | 1 | sys_unit_config | 1 | 1 | 2147483648 | 2147483648 | 10000 | 10000 | 1 |
| 2023-03-23 23:10:34.735236 | 2023-03-23 23:10:34.735236 | 1001 | lxs_unit | 1 | 1 | 2147483648 | 2147483648 | 1024 | 1024 | 0 |
+----------------------------+----------------------------+----------------+-----------------+---------+---------+-------------+---------------+----------+----------+-------------+
2 rows in set (0.004 sec)
obclient [oceanbase]>
7.创建资源池：
create resource pool lxs_pool unit='lxs_unit', unit_num=1, zone_list=('zone1');
obclient [oceanbase]> create resource pool lxs_pool unit='lxs_unit', unit_num=1, zone_list=('zone1');
Query OK, 0 rows affected (0.733 sec)
obclient [oceanbase]>
8.查询资源池：
select resource_pool_id, name from __all_resource_pool;
obclient [oceanbase]> select resource_pool_id, name from __all_resource_pool;
+------------------+----------+
| resource_pool_id | name |
+------------------+----------+
| 1 | sys_pool |
| 1001 | lxs_pool |
+------------------+----------+
2 rows in set (0.001 sec)
obclient [oceanbase]>
select resource_pool_id, name,tenant_id from __all_resource_pool;
obclient [oceanbase]> select resource_pool_id, name,tenant_id from __all_resource_pool;
+------------------+----------+-----------+
| resource_pool_id | name | tenant_id |
+------------------+----------+-----------+
| 1 | sys_pool | 1 |
| 1001 | lxs_pool | -1 |
+------------------+----------+-----------+
2 rows in set (0.004 sec)
obclient [oceanbase]>
tenant_id=-1,表示没有关联到用户。
9.创建业务租户：
create tenant lxs_tenant resource_pool_list=('lxs_pool');
obclient [oceanbase]> create tenant lxs_tenant resource_pool_list=('lxs_pool');
Query OK, 0 rows affected (12.139 sec)
obclient [oceanbase]>
10.查询业务租户：
select * from __all_tenant;
obclient [oceanbase]> select * from __all_tenant;
+----------------------------+----------------------------+-----------+-------------+-----------+--------------+--------+----------------+---------------+---------------+-------------------+-----------------------+--------------------+------------------+--------+---------------+
| gmt_create | gmt_modified | tenant_id | tenant_name | zone_list | primary_zone | locked | collation_type | info | locality | previous_locality | default_tablegroup_id | compatibility_mode | drop_tenant_time | status | in_recyclebin |
+----------------------------+----------------------------+-----------+-------------+-----------+--------------+--------+----------------+---------------+---------------+-------------------+-----------------------+--------------------+------------------+--------+---------------+
| 2022-11-08 15:56:02.447474 | 2022-11-08 15:56:02.447474 | 1 | sys | zone1 | RANDOM | 0 | 0 | system tenant | FULL{1}@zone1 | | -1 | 0 | -1 | NORMAL | 0 |
| 2023-03-24 00:16:17.286788 | 2023-03-24 00:16:27.167154 | 1003 | META$1004 | zone1 | RANDOM | 0 | 0 | | FULL{1}@zone1 | | -1 | 0 | -1 | NORMAL | 0 |
| 2023-03-24 00:16:17.312906 | 2023-03-24 00:16:27.185767 | 1004 | lxs_tenant | zone1 | RANDOM | 0 | 0 | | FULL{1}@zone1 | | -1 | 0 | -1 | NORMAL | 0 |
+----------------------------+----------------------------+-----------+-------------+-----------+--------------+--------+----------------+---------------+---------------+-------------------+-----------------------+--------------------+------------------+--------+---------------+
3 rows in set (0.004 sec)
11.退出sys，登录lxs_tenant：
exit; obclient -h 127.0.0.1 -u root@lxs_tenant -P 2881 -c -A oceanbase
obclient [oceanbase]> exit
Bye
[angel@localhost ~]$ obclient -h 127.0.0.1 -u root@lxs_tenant -P 2881 -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487629
Server version: OceanBase_CE 4.0.0.0 (r100000272022110114-6af7f9ae79cd0ecbafd4b1b88e2886ccdba0c3be) (Built Nov 1 2022 14:57:18)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]>
12.查询数据库：
show databases;
obclient [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oceanbase |
| test |
+--------------------+
4 rows in set (0.015 sec)
13.创建数据库：
create database lxs_db_20230301;
obclient [oceanbase]> create database lxs_db_20230301;
Query OK, 1 row affected (0.116 sec)
14.查询新数据库：
show databases;
obclient [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxs_db_20230301 |
| mysql |
| oceanbase |
| test |
+--------------------+
5 rows in set (0.020 sec)
obclient [oceanbase]>
15.打开新数据库：
use lxs_db_20230301;
obclient [oceanbase]> use lxs_db_20230301;
Database changed
obclient [lxs_db_20230301]>
四、导入MYSQL数据库的表到OB4.0中：
1.导入数据：
source /home/angel/table_20230301_data.sql
obclient [lxs_db_20230301]> source /home/angel/table_20230301_data.sql
Query OK, 0 rows affected (0.015 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.012 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.000 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.000 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.004 sec)
Query OK, 0 rows affected (0.000 sec)
ERROR 1146 (42S02): Table 'lxs_db_20230301.table_20230301' doesn't exist
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
2.导入表结构：
source /home/angel/table_20230301_ddl.sql
obclient [lxs_db_20230301]> source /home/angel/table_20230301_ddl.sql Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected, 1 warning (0.240 sec) Query OK, 0 rows affected (0.000 sec) obclient [lxs_db_20230301]>
3.查询数据库中的表：
show tables;
obclient [lxs_db_20230301]> show tables;
+---------------------------+
| Tables_in_lxs_db_20230301 |
+---------------------------+
| table_20230301 |
+---------------------------+
1 row in set (0.011 sec)
obclient [lxs_db_20230301]>
4.查询表中的数据：
select * from table_20230301;
obclient [lxs_db_20230301]> select * from table_20230301;
Empty set (0.472 sec)
obclient [lxs_db_20230301]>
5.重新导入数据：
source /home/angel/table_20230301_data.sql
obclient [lxs_db_20230301]> source /home/angel/table_20230301_data.sql
Query OK, 0 rows affected (0.003 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.005 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.002 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.001 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 2 rows affected (0.357 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
obclient [lxs_db_20230301]>
6.再次查询表中的数据：
select * from table_20230301;
obclient [lxs_db_20230301]> select * from table_20230301;
+----+-------+
| id | name |
+----+-------+
| 1 | angel |
| 2 | alice |
+----+-------+
2 rows in set (0.001 sec)
obclient [lxs_db_20230301]>
7.查询表中的数据数量：
select count(*) from table_20230301;
obclient [lxs_db_20230301]> select count(*) from table_20230301;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.000 sec)
obclient [lxs_db_20230301]>
五、导入成功，踩过的坑：
先导入数据，再导入表结构而导致表空的报错！
ERROR 1146 (42S02): Table 'lxs_db_20230301.table_20230301' doesn't exist
重新又第二次导入数据成功了！
六、总结：
由于OB4.0安装顺畅了，能够使从MySQL到OB用户的数据离线同步数据迁移方便了很多。其中节省时间和步骤的主要方面在于OB3.x版本安装时步骤繁琐，OB4.0在安装步骤上进行了多方面的优化，并且各个部件进行了免手动安装，一键完成安装。
启动OB只需要一次性输入命令：
obd cluster start demo
等到显示：
demo running
时，OB就启动成功了！
总之，
七、感谢：
在我遇到困难的时候，君野老师给我提供了解决问题的路径。在创建资源单元资格时，我依旧按照OB3.x版本安装时的命令输入，没有成功。我到网上发帖子，君野老师耐心地给我提供了帮助！OB4.0
OB4.0创建资源单元资格时，亦旧不那么繁琐，只需要如下一条命令，
create resource unit lxs_unit max_cpu 1, min_cpu 1, memory_size '2g', max_iops 1024, min_iops 1024, iops_weight 0, log_disk_size '2g';
资源单元资格就创建完毕！
同时感谢小墨老师为我及时提供了征文信息和我的文章错误与不足之处以及批评指正，还要感谢OB征文小助手的帮助！
| 给我点个赞 |
最后修改时间：2023-04-01 02:06:26
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
