What are the steps for migrating mysql database?

MySQL database migration methods include the following, with specific code examples attached:

1. Database backup and recovery
   Database backup and recovery Is one of the most common migration methods. First, you need to back up the original database to a file, and then import the backup file into the new database.

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

2. Database replication
   Database replication is a method of copying the original database to a new database server. Using database replication, migration can be completed without downtime or disconnection of database services.

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;

3. Database export and import
   Database export and import is a method of exporting the original database as a SQL file and then importing the SQL file into the new database.

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!