Home > Database > Mysql Tutorial > How to transfer a MySQL database

How to transfer a MySQL database

PHPz
Release: 2023-04-20 11:47:57
Original
2495 people have browsed it

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

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.

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

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.

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

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

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

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

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

use [database name];
source [backupfile.sql];
Copy after login

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!

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