mysql5.1.33 to mysql5.5.33 in the process of data migration

Background: The company to the Test library mysql5.1.33 above the data migrated to mysql5.5.33 above, 5.1 above the table is mostly MyISAM table, in order to make the data consistent, with the--lock-all-table option

Precautions:

1, you can pilot the permission table, so that you can move the business to the successful part of the library above

2, the guide before the Binlog to be banned, so that the speed of the import will be faster,set global Sql_log_bin=off, there is no binlog to disable the case of about 1G per hour can only lead to more than the data. Approximately 20G of data can be imported per hour after disabling Binlog

3, select Innobackupex Backup will be faster.

4, import and export to record the log, record the time of import and export, so in case of error, you can immediately locate the error
5, pay attention to import and export parameters, use--skip-opt carefully, will change the properties of the table

6. Main import and Export parameters:--default-character-set=utf8--opt--extended-insert=false--triggers-r--hex-blob

(-x,--lock-all-tables, do not add-X if strict consistency is not required, because it locks the entire library)

P.S:

Cannot set such a global variable in version 5.6, will error

Set global Sql_log_bin=off;

ERROR 1231 (42000): Variable ' sql_log_bin ' can ' t is set to the value of ' off '

This article is from the "Always on the Road" blog, please be sure to keep this source http://chenql.blog.51cto.com/8732050/1682008

mysql5.1.33 to mysql5.5.33 in the process of data migration