How to transfer a MySQL database

MySQL is a free relational database management system with a wide range of applications. During operations such as project migration, server replacement, and backup, we often need to transfer the MySQL database. This article will introduce how to transfer a MySQL database.

1. Back up MySQL database

Before transferring, we need to back up the MySQL database file first. There are many backup methods, the most commonly used of which is to use the mysqldump command. Enter the following command in the terminal to back up the database:

mysqldump -u [username] -p [database name] > [backupfile.sql]

where [username] is the MySQL user name, [database name] is the name of the database to be backed up, and [backupfile.sql] is the file name saved after backup. . After executing this command, you will be prompted to enter the MySQL password. After entering the correct password, wait for a while to complete the backup.

2. Create a new server and install MySQL

Before transferring the MySQL database, we need to install MySQL on the destination server. The installation method is related to the specific system. Please choose the corresponding method according to the actual situation.

After the installation is complete, we need to set the root password for MySQL. Enter the following command in the terminal:

mysqladmin -u root password 'new-password'

Replace [new-password] with the new password you set. If a password has been set on the MySQL server before, you need to add the -p parameter to the command and enter the existing password to confirm your identity.

3. Import backup

Before importing the database file to the new server, we need to copy the backup file from the original server to the target server. You can use the scp command to achieve this:

scp [backupfile.sql] [username]@[destination]:[path]

Where [destination] is the target server IP, [username] is the login user name, [backupfile.sql] is the backup file name, and [path] is the path where the target server saves the file to be backed up. After entering the command, you will be prompted to enter the login password. Enter the correct password to complete the file transfer.

On the target server, we enter the MySQL terminal and create a new database:

mysql -u root -p
create database [database name];

Where [database name] is the name of the newly created database. At this time we need to confirm whether the MySQL account on the new server has administrator rights. If not, you need to use the following command to authorize:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '[password]' WITH GRANT OPTION;

Replace [password] with the password of the root user. After the authorization is successful, enter the following command again to confirm the login:

mysql -u root -p

Then, we enter the database directory to be imported and dump the backup file to the new database:

use [database name];
source [backupfile.sql];

Among them, [database name ] is the name of the newly created database, and [backupfile.sql] is the name of the backup file. After execution, the database transfer is completed.

Summary

The process of transferring MySQL database is relatively complicated, but as long as we follow the above steps step by step, we can complete the transfer of MySQL database. Before transferring the MySQL database, be sure to consider the security of the data. It is recommended to back up the data before transferring to reduce risks.

The above is the detailed content of How to transfer a MySQL database. For more information, please follow other related articles on the PHP Chinese website!