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)