Oceanbase学习之一迁移mysql数据到oceanbase

一、数据库环境

#mysql环境

root@192.168.150.162 20:28:  [(none)]> select version();

±----------+

| version() |

±----------+

| 8.0.26    |

±----------+

1 row in set (0.00 sec)

root@192.168.150.162 20:28:  [(none)]> show variables like ‘%char%’;

±-------------------------±----------------------------------+

| Variable_name            | Value                             |

±-------------------------±----------------------------------+

| character_set_client     | utf8mb4                           |

| character_set_connection | utf8mb4                           |

| character_set_database   | utf8mb4                           |

| character_set_filesystem | binary                            |

| character_set_results    | utf8mb4                           |

| character_set_server     | utf8mb4                           |

| character_set_system     | utf8mb3                           |

| character_sets_dir       | /usr/local/mysql8/share/charsets/ |

±-------------------------±----------------------------------+

8 rows in set (0.00 sec)

#当前mysql环境下的表

#oceanbase环境

obclient [test]> select version();

±-----------------------------+

| version()                    |

±-----------------------------+

| 5.7.25-OceanBase_CE-v4.2.1.2 |

±-----------------------------+

1 row in set (0.002 sec)

obclient [test]> show variables like ‘%chara%’;

±-------------------------±--------+

| Variable_name            | Value   |

±-------------------------±--------+

| character_set_client     | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database   | utf8mb4 |

| character_set_filesystem | binary  |

| character_set_results    | utf8mb4 |

| character_set_server     | utf8mb4 |

| character_set_system     | utf8mb4 |

±-------------------------±--------+

7 rows in set (0.005 sec)

确认mysql与oceanbase的字符集一样

二、mysqldump迁移数据到OceanBase

通过MySQL下的mysqldump将数据导出为SQL文本格式，将数据备份文件传输到OceanBase数据库主机后，通过source命令导入到OceanBase数据库。

#当前mysql下的表

MySQL [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MySQL [test]> show tables;

±------------------+

| Tables_in_test    |

±------------------+

| cluster_test      |

| cluster_test1     |

| cluster_test2     |

| cluster_test3     |

| cluster_test4     |

| t1                |

| t2                |

| t8                |

| t_smallint        |

| test_clustered    |

| test_nonclustered |

±------------------+

11 rows in set (0.00 sec)

# 通过mysqldump导出数据

mysqldump -h  192.168.150.162 -uroot -P4000 -p --database test  > test_oceanbase.sql

#传输脚本到oceanbase服务器

scp test_oceanbase.sql 192.168.150.116:/home/admin

#oceanbase导入

obclient [test]> source test_oceanbase.sql

obclient [test]> show tables;

±------------------+

| Tables_in_test    |

±------------------+