MySQL data migration a detailed explanation of the considerations for direct substitution of the data directory
- 2020-05-27 07:22:53
- OfStack
Recently, the server was changed, and the data in the database should be migrated from the server A to the server B.
Due to the large amount of data, it takes too long to directly do dump, so the following method is adopted:
First, we installed MySQL on server B in the same version as server A, stopped MySQL service, and deleted the data directory after installation.
Then, server A locks all the tables, copies the entire data directory and data file from server A to server B, and modifies datadir in MySQL's my.cnf file on server B to point to the new data directory.
Finally, start the MySQL service on the server B.
As a result, the startup failed, and an exception "unable to start MySQL service" was reported. After checking the error log, the problem was described 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 information means that the log file is larger than the log file quota set in my.cnf and the service cannot be started. The reason is that the log files are copied from server A, and the log quota in my.cnf on server A is larger than the log quota in my.cnf on server B.
Calculated log file size: 63963136/(1024*1024)=61M,6291456/(1024*1024)=6M, no wonder?
Set the parameter innodb_log_file_size in my.cnf on the server B to 61M, start mysql again, succeed.
Summarize the matters needing attention:
1. Modify datadir to the new data directory.
2. Reasonably modify the value of innodb_log_file_size to be the actual log file size migrated.
3. Change of character set/default engine to be associated with migration presystem 1.
Due to the large amount of data, it takes too long to directly do dump, so the following method is adopted:
First, we installed MySQL on server B in the same version as server A, stopped MySQL service, and deleted the data directory after installation.
Then, server A locks all the tables, copies the entire data directory and data file from server A to server B, and modifies datadir in MySQL's my.cnf file on server B to point to the new data directory.
Finally, start the MySQL service on the server B.
As a result, the startup failed, and an exception "unable to start MySQL service" was reported. After checking the error log, the problem was described 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 information means that the log file is larger than the log file quota set in my.cnf and the service cannot be started. The reason is that the log files are copied from server A, and the log quota in my.cnf on server A is larger than the log quota in my.cnf on server B.
Calculated log file size: 63963136/(1024*1024)=61M,6291456/(1024*1024)=6M, no wonder?
Set the parameter innodb_log_file_size in my.cnf on the server B to 61M, start mysql again, succeed.
Summarize the matters needing attention:
1. Modify datadir to the new data directory.
2. Reasonably modify the value of innodb_log_file_size to be the actual log file size migrated.
3. Change of character set/default engine to be associated with migration presystem 1.