MySQL database migration methods include the following, with specific code examples attached:
The command to back up the database is as follows:
mysqldump -u 用户名 -p 密码 数据库名 > 备份文件路径
The command to restore the database is as follows:
mysql -u 用户名 -p 密码 新数据库名 < 备份文件路径
For example, to back up the database named "old_db" to the file "old_db_backup .sql", you can use the following command:
mysqldump -u root -p password old_db > ~/old_db_backup.sql
To import the backup file "old_db_backup.sql" into the new database "new_db", you can use the following command:
mysql -u root -p password new_db < ~/old_db_backup.sql
First, enable the main server log on the original database server. Open the MySQL configuration file, find the "[mysqld]" section and add the following line:
log-bin=mysql-bin server-id=1
Then, restart the MySQL service.
On the new database server, create an empty database with the same name as the original database.
Next, on the new database server, open the MySQL configuration file, find the "[mysqld]" section and add the following line:
relay-log=mysql-relay-bin server-id=2
Restart the MySQL service.
Log in to MySQL on the new database server and execute the following command to add the original database server as the primary server to the new database server:
CHANGE MASTER TO MASTER_HOST='原数据库服务器IP地址', MASTER_USER='主服务器用户名', MASTER_PASSWORD='主服务器密码', MASTER_LOG_FILE='原数据库服务器日志文件', MASTER_LOG_POS=原数据库服务器日志位置;
Then start replication:
START SLAVE;
The command to export the original database is as follows:
mysqldump -u 用户名 -p 密码 数据库名 > 导出文件路径
For example, to export the database named "old_db" to the file "old_db_export.sql", you can use the following command:
mysqldump -u root -p password old_db > ~/old_db_export.sql
The command to import SQL files into a new database is as follows:
mysql -u 用户名 -p 密码 新数据库名 < 导出文件路径
For example, to import the SQL file "old_db_export.sql" into the new database "new_db", you can use the following command:
mysql -u root -p password new_db < ~/old_db_export.sql
The above are several common methods for MySQL database migration. Each method has its applicable scenarios. Choose the appropriate method for database migration according to the actual situation.
The above is the detailed content of What are the steps for migrating mysql database?. For more information, please follow other related articles on the PHP Chinese website!