MySQL 5.5 Data Restore tricky issues

Scene:

System version: CentOS 6.4MySQL version: 5.5.35-log source distribution Logical backup command: mysqldump restore data command: source

Conditions:

backup files [[email protected] ~]# ll-d/tmp/cloudtest.sql-rw-r--r--1 root root 1575809806 February 09:04/tmp/cloudtest.sql

Recovery site:

[[email protected] ~]# mysqlwelcome to the mysql monitor.   commands end with ; or \g.your mysql connection id is  3server version: 5.5.35-log source distributioncopyright  (c)  2000, 2013,  oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . 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 = 0mysql> source /tmp/cloudtest.sql; (The execution time is about 25 minutes) 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 ' 

Workaround:

[Email protected] ~]# Vim/etc/my.cnf[mysqld]max_allowed_packet = 500m[mysqldump]quickmax_allowed_packet = 500M

[Email protected] ~]# service mysqld restart

Perform the recovery site operation again [[email protected] ~]# Mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 3Server version:5.5.35-log Source distributioncopyright (c), +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. 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 = 0mysql> source/tmp/cloudtest.sql;

This article is from the "Zheng" blog, make sure to keep this source http://467754239.blog.51cto.com/4878013/1615870

MySQL 5.5 Data Restore tricky issues