mysql表导出导入测试(utf8-utf8)

一.测试需求
将maridb 10.0.20下的表lots.t_order迁移到mysql 5.6中，字符集都是utf8，测试迁移后的表记录数是否与源表相同，汉字是否有乱码。二.基本信息
1.源表环境信息：
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec) mysql> \s
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper Connection id: 35
Current database: lots
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.5-10.0.20-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: 1 day 21 hours 43 min 38 se mysql> show create table lots.t_order\G
*************************** 1. row ***************************
Table: t_order
Create Table: CREATE TABLE `t_order` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
-----信息安全，部分信息省略------------------
PRIMARY KEY (`ID`),
KEY `index_annto_order_no` (`TO_ORDER_NO`) USING BTREE,
KEY `index_cust_order_no` (`CUSTOMER_ORDER_NO`) USING BTREE,
KEY `index_contract_no` (`contract_no`) USING BTREE,
KEY `index_express_no` (`EXPRESS_NO`) USING BTREE,
KEY `index_express_code` (`EXPRESS_CODE`) USING BTREE,
KEY `index_create_date` (`CREATE_DATE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2360435 DEFAULT CHARSET=utf8 COMMENT='订单表'
1 row in set (0.00 sec) mysql> select count(*) from t_order;
+----------+
| count(*) |
+----------+
| 1872178 |
+----------+
1 row in set (1.09 sec)
2.目标环境信息：
(product)root@localhost [(none)]> select version();
+------------+
| version() |
+------------+
| 5.6.29-log |
+------------+
1 row in set (0.00 sec) (product)root@localhost [(none)]> \s
--------------
mysql Ver 14.14 Distrib 5.6.29, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3376.sock
Uptime: 1 min 4 sec
三.源环境导出 ---导出数据库lots下的表t_order:
[apps@mvxl0781 mariadb10_data3306]mysqldump -uroot -p lots t_order >t_order.sql
[apps@mvxl0781 mariadb10_data3306]$ ls -ltr
[apps@mvxl0781 dbdat]$ ls -ltr
-rw-rw-r-- 1 apps apps 750953342 Apr 20 23:32 t_order.sql
---将导出文件通过ftp或scp复制到目标库的主机上
[apps@mvxl0781 dbdat]$ scp t_order.sql root@10.16.24.107:/data/mysql/mysql3376/data/
root@10.16.24.107's password:
t_order.sql 100% 716MB 119.4MB/s 00:06

四.目标环境导入
---建立lots库
(product)root@localhost [(none)]> create database lots;
Query OK, 1 row affected (0.00 sec) [mysql@mvxl0782 data]$ pwd
/data/mysql/mysql3376/data
----导入表
[mysql@mvxl0782 data]$ mysql -uroot -p -S /tmp/mysql3376.sock lots <t_order.sql ----查看表的记录数，与来源表的记录数相同
(product)root@localhost [lots]> select count(*) from t_order;
+----------+
| count(*) |
+----------+
| 1872178 |
+----------+
1 row in set (1.41 sec) ----表中记录汉字显正常
(product)root@localhost [lots]> select * from t_order limit 1,2\G
*************************** 1. row ***************************
ID: 2
CUSTOMER_ID: 4775
CUSTOMER_NAME: 南昌市xxxx有限公司
WAREHOUSE_NAME: 广东仓
-----信息安全，部分信息省略------------------
SHIP_TO_PROVINCE: 广东省
SHIP_TO_CITY: 佛山市
SHIP_TO_AREA: 南海区
SHIP_TO_MOBILE1:
SHIP_TO_MOBILE2: 12345678
CREATE_DATE: 2015-10-19 21:03:40
IS_SIGN: 1
SIGN_DATE: 2015-09-02 14:39:56
is_delivery: 1
delivery_date: 2015-09-01 11:50:20
business_type: B2C
contract_no: NULL
edi_id: NULL
is_got: 0
got_date: NULL
is_end: 0 总结：
maridb 10.0.20下的表在字符集相同情况下能正常迁移到mysql 5.6中，且汉字不会乱码。

更多相关文章

随机推荐