Detailed explanation about mysql data migration - Data directory direct instructions
Recently, when the server is replaced, a database migration is required, and the data in the database is migrated from server A to server B.
Due to the large amount of data, it takes too long to do the dump directly, so the following methods are used:
First, install the same version of MySQL as server A on server B, stop MySQL service, delete the installed data directory;
Then, server A locks all tables, copies the entire data directory and data files from server A to server B, and modifies the my.cnf file of MySQL on server B The datadir in points to the new data directory.
Finally, start the MySQL service on server B.
As a result, the startup failed, and an exception of "Unable to start MySQL service" was reported.Check the error log and found that the problem description is as follows:
InnoDB: Error: log file./ib_logfile0 is of different size 0 63963136 bytes
InnoDB: than specified in the.cnf file 0 6291456 bytes!
This message means that the log file is larger than the log file quota set in my.cnf.The service could not be started.The reason is that the log file is copied from server A.The log quota in my.cnf on server A is larger than the my.cnf log quota on server B.
Calculate the log file size: 63963136/(1024*1024)=61M,6291456/(1024*1024)=6M, no wonder?
Set the parameter of innodb_log_file_size in my.cnf on server B to 61M, and start mysql again, Started successfully.
Summary of the points to note:
1.Modify datadir to the new data directory.
2.Reasonably modify the value of innodb_log_file_size to the size of the actual migrated log file.
3.The modification of the character set/default engine should be the same as before the migration.