mysqldump和max-allowed-packet的测试:
导入和导出都包括
环境准备:
库中有表:
mysql> select table_schema,data_length/1024/1024 from information_schema.tables where table_name='plugindata';
+--------------+-----------------------+
| table_schema | data_length/1024/1024 |
+--------------+-----------------------+
| t | 39.51562500 |
| t1 | 61.51562500 |
+--------------+-----------------------+
2 rows in set (0.00 sec)
表中包含了longblob字段
CREATE TABLE `plugindata` (
`a` bigint(20) NOT NULL,
`b` varchar(255) COLLATE utf8_bin NOT NULL,
`c` varchar(255) COLLATE utf8_bin NOT NULL,
`d` datetime DEFAULT NULL,
`e` longblob,
PRIMARY KEY (`a`),
UNIQUE KEY `bb` (`b`),
UNIQUE KEY `cc` (`c`)
) ENGINE=InnoDB
t库下的这个表中只有一行记录
mysql> select count(*) from t.plugindata;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.02 sec)
t1库上的这个表中队了包含上面那一行记录,还有另外小一些的8行记录
mysql> select count(*) from t1.plugindata;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
t库下的大小是39M
和库下的大小是61M
测试:
场景1:
set global max_allowed_packet=16777216
mysqldump默认的max_allowed_packet是24M
mysqldump -uroot -ptest -S /data/mysqld.sock t plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 0
mysqldump -uroot -ptest -S /data/mysqld.sock t1 plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 1
都导出报错
场景2:
加大mysqldump的 max_allowed_packet
set global max_allowed_packet=16777216
mysqldump的max_allowed_packet改为40M
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=41943040 t plugindata > /tmp/a.sql --可以正常导出
导出那个大一点的表
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=41943040 t1 plugindata > /tmp/a.sql --也可以正常导出
mysqldump的max_allowed_packet改为39M
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=40894464 t plugindata > /tmp/a.sql --可以正常导出
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=40894464 t1 plugindata > /tmp/a.sql --也可以正常导出
mysqldump的max_allowed_packet改为38M
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=39845888 t plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 0
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=39845888 t1 plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 1
两种都报错
场景3:
加大mysql的 max_allowed_packet到40M
set global max_allowed_packet=41943040
mysqldump -uroot -ptest -S /data/mysqld.sock t plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 0
mysqldump -uroot -ptest -S /data/mysqld.sock t1 plugindata > /tmp/a.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `plugindata` at row: 1
都报错
场景3:
加大mysql的 max_allowed_packet到60M 也试了加到600M都不行
set global max_allowed_packet=62914560
导入测试:
一条记录的导出为par.sql ,9条记录的导出为full.sql
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=40894464 t plugindata > /tmp/part.sql
mysqldump -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=40894464 t1 plugindata > /tmp/full.sql
测试:
场景1:
set global max_allowed_packet=16777216
mysql命令行默认的max_allowed_packet大小是16M
mysql -uroot -ptest -S /data/mysqld.sock test1 < /tmp/part.sql
ERROR 2006 (HY000) at line 43: MySQL server has gone away
导入报错
场景2:
set global max_allowed_packet=41943040
mysql命令行默认的max_allowed_packet大小是16M
mysql -uroot -ptest -S /data/mysqld.sock test1 < /tmp/part.sql
ERROR 2006 (HY000) at line 43: MySQL server has gone away
导入报错
场景3:
set global max_allowed_packet=16777216
mysql命令行改为40M
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=41943040 test1 < /tmp/part.sql
ERROR 2006 (HY000) at line 43: MySQL server has gone away
导入报错
场景4:
set global max_allowed_packet=16777216
mysql命令行改为400M
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=419430400 test1 < /tmp/part.sql
ERROR 2006 (HY000) at line 43: MySQL server has gone away
导入报错
场景5:
set global max_allowed_packet=41943040
mysql命令行改为40M
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=41943040 test1 < /tmp/part.sql
ERROR 2006 (HY000) at line 43: MySQL server has gone away
导入报错
场景6:
set global max_allowed_packet=47185920
mysql命令行改为45M
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=47185920 test1 < /tmp/part.sql
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=47185920 test1 < /tmp/full.sql
两个都可以成功
查询时:
mysql> set global max_allowed_packet=471859200;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mysql -uroot -ptest -S /data/mysqld.sock
mysql> select * from t.plugindata;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 15868
Current database: *** NONE ***
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes
加大参数: 即可成功
mysql -uroot -ptest -S /data/mysqld.sock --max-allowed-packet=41943040
总结:
1 导出:
mysqldump的max_allowed_packet参数为最大行的所占用的长度即可导出成功,mysql服务器参数无关
2 导入:
mysql服务器参数和mysql命令行参数都略大于最大行的长度方可导入成功
3 如果想在mysql客户端也能成功显示记录的,需要进入mysql命令行的参数上加上max-allowed-packet为最大行的大小
转载请注明源出处
QQ 273002188 欢迎一起学习
QQ 群 236941212
QQ 273002188 欢迎一起学习
QQ 群 236941212
oracle,mysql,mongo 相互交流
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-1815789/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-1815789/