Easily import, export (restore, backup) MySQL databases on Linux servers

As the website runs, the MySQL database is getting bigger and bigger, and the backup problem is becoming more and more obvious. When using phpmyadmin to export or import, it may not be imported or exported because the timeout file is too large. The following VPS Detectives say a few solutions:

Tools/Materials

MYSQL, phpmyadmin

Steps/Methods

MySQL Database Export

Method 1: mysqldump command

Execute the command: /usr/local/mysql/bin/mysqldump -u username-p database name xml file name

if the database username has a password password After the execution, you will be prompted to enter a password. If the database username does not have a password, delete the “-p” parameter in the command.

(Note: It is recommended to use the MySQL root account for operation, because some databases may have only corresponding users to operate.)

Method 2: phpmyadmin

phpmyadmin Support The database file is directly backed up to the directory specified by the configuration file. The default installation of lnmp uses the modified configuration. You can directly back up the database to /home/wwwroot/phpmyadmin/save/, and then download it through http or ftp. can. For the time being, I have not tested a relatively large database. Users of lnmp can test it and give me feedback.

Method 3: Third-party software

For example: Imperial Backup King, a stable and efficient software designed for the backup and import of MYSQL big data, the system uses volume backup and import, in theory You can back up any size database. According to the feedback on the Internet, it is still good to install and use.

MySQL Database Import

Method 1: mysql Command

Execute the command: /usr/local/mysql/bin/mysql -u username-p database name<file The name

parameter is the same as the mysqldump.

(Note: The imported database name needs to already exist.)

Method 2: phpmyadmin

phpmyadmin can import /home/wwwroot/phpmyadmin/upload/to the specified In the database, you can upload the database backup file to the /home/wwwroot/phpmyadmin/upload/directory for import.

Precautions

It is recommended to use the MySQL root account for operation, as some databases may have only corresponding users to operate.