Home > Database > Mysql Tutorial > What are the steps for migrating mysql database?

What are the steps for migrating mysql database?

王林
Release: 2024-02-19 16:52:05
Original
706 people have browsed it

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 密码 数据库名 > 备份文件路径
Copy after login

The command to restore the database is as follows:

mysql -u 用户名 -p 密码 新数据库名 < 备份文件路径
Copy after login

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
Copy after login

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
Copy after login
  1. 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
Copy after login

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
Copy after login

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=原数据库服务器日志位置;
Copy after login

Then start replication:

START SLAVE;
Copy after login
  1. 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 密码 数据库名 > 导出文件路径
Copy after login

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
Copy after login

The command to import SQL files into a new database is as follows:

mysql -u 用户名 -p 密码 新数据库名 < 导出文件路径
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template