MySQL 5.5 mysqldump工作过程中遇到的问题

t4r23

                      场景1：

1 2 3 4

系统版本：CentOS 6.4 MySQL版本：5.5.35-log Source distribution 逻辑备份命令：mysqldump 还原数据命令：source

条件：

1 2 3

备份文件 [iyunv@localhost ~]# ll -d /tmp/cloudTest.sql -rw-r--r-- 1 root root 1575809806 2月  28 09:04 /tmp/cloudTest.sql

问题：备份文件cloudTest,sql是二进制安装MySQL5.5下的备份文件，然后导入到源码编译MySQL5.5环境下中遇到的导入错误，重复导入几次，依然报错如下：
恢复现场：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

[iyunv@localhost ~]# mysql Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.5.35-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> create database cloudTest; mysql> user cloudTest; mysql> set session sql_log_bin = 0 mysql> source /tmp/cloudTest.sql; ......(执行时长25分钟左右) ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL' ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL' ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of 'NULL' ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL' ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL' Query OK, 0 rows affected (0.00 sec) ERROR 1231 (42000): Variable 'collation_connection' can't be set to the value of 'NULL' ERROR 1231 (42000): Variable 'sql_notes' can't be set to the value of 'NULL'

解决办法：

1 2 3 4 5 6 7

[iyunv@localhost ~]# vim /etc/my.cnf [mysqld] max_allowed_packet = 500M [mysqldump] quick max_allowed_packet = 500M

1	[iyunv@localhost ~]# service mysqld restart

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

再次执行恢复现场操作 [iyunv@localhost ~]# mysql Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.5.35-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> create database cloudTest; mysql> user cloudTest; mysql> set session sql_log_bin = 0 mysql> source /tmp/cloudTest.sql;

解释参数的含义：
max_allowed_packet

1	缓冲区的最大大小为客户端/服务器通信。默认为24MB，最大1GB。

net_buffer_length

1	该缓冲区的初始大小的客户端/服务器通信。创建多行插入语句时（与延伸————插入或选择选项），mysqldump创建行了net_buffer_length长度。如果你增加这个变量，你还应该确保在MySQL服务器变量至少大net_buffer_length。

场景2：

问题：在使用mysqldump对mysql进行数据库备份的时候，发现某些库不能直接备份；比如

1 2 3

[iyunv@localhost script]# mysqldump -uroot -p performance_schema > /tmp/performance_schema.sql Enter password: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

于是，今天在MySQL 5.5官网上看到了mysqldump的用法，才明白原来是这样的

解决办法：

1	--skip-lock-tables

1 2 3 4

[iyunv@localhost script]# mysqldump -uroot -p performance_schema --skip-lock-tables > /tmp/performance_schema.sql Enter password: [iyunv@localhost script]# ll -d /tmp/performance_schema.sql -rw-r--r-- 1 root root 16728 2月  28 15:38 /tmp/performance_schema.sql