mysqldump从mysql迁移数据到OceanBase
使用mysqldump导出数据
/usr/bin/mysqldump --single-transaction -B employees -S /data/mysql/mysql.sock -uroot -p > dump.sql
ob使用source加载
obclient -P2883 -h 192.168.56.20 -uroot@mq_t1 -A
校验数据
使用统计信息
两个数据库收集统计信息的命令相同
收集统计信息
analyze table employees.departments;
analyze table employees.dept_emp;
analyze table employees.dept_manager;
analyze table employees.employees;
analyze table employees.salaries;
analyze table employees.titles;
- mysql查询数据
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA='employees';
+--------------+----------------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | TABLE_ROWS |
+--------------+----------------------+------------+------------+
| employees | current_dept_emp | VIEW | NULL |
| employees | departments | BASE TABLE | 9 |
| employees | dept_emp | BASE TABLE | 331143 |
| employees | dept_emp_latest_date | VIEW | NULL |
| employees | dept_manager | BASE TABLE | 24 |
| employees | employees | BASE TABLE | 291715 |
| employees | salaries | BASE TABLE | 2844535 |
| employees | titles | BASE TABLE | 440956 |
+--------------+----------------------+------------+------------+
8 rows in set (0.00 sec)
- ob查询数据
obclient [information_schema]> select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA='employees';
+--------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+--------------+------------+
| employees | departments | 9 |
| employees | dept_emp | 331603 |
| employees | dept_manager | 24 |
| employees | employees | 300024 |
| employees | salaries | 2844535 |
| employees | titles | 443308 |
+--------------+--------------+------------+
6 rows in set (0.008 sec)
- 总结
- mysql中有两个对象是视图,视图没有导入
- 导入过程中报错"ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'",将导出文件中的"COLLATE=utf8mb4_0900_ai_ci"删除即可解决.
- 使用统计信息对数据的方式不够准确,如果数据量比较小,通过count(*)也不是不可以.
使用count(*)
- mysql库
mysql> select count(*) from employees.departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from employees.dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from employees.dept_manager;
+----------+
| count(*) |
+----------+
| 24 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from employees.employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.07 sec)
mysql> select count(*) from employees.salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.46 sec)
mysql> select count(*) from employees.titles;
+----------+
| count(*) |
+----------+
| 443308 |
+----------+
1 row in set (0.09 sec)
- ob库
obclient [information_schema]> select count(*) from employees.departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.108 sec)
obclient [information_schema]> select count(*) from employees.dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
1 row in set (0.160 sec)
obclient [information_schema]> select count(*) from employees.dept_manager;
+----------+
| count(*) |
+----------+
| 24 |
+----------+
1 row in set (0.024 sec)
obclient [information_schema]> select count(*) from employees.employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.341 sec)
obclient [information_schema]> select count(*) from employees.salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (1.989 sec)
obclient [information_schema]> select count(*) from employees.titles;
+----------+
| count(*) |
+----------+
| 443308 |
+----------+
1 row in set (0.212 sec)
