Explain how to use the mysqldump command to backup and restore MySQL data

Lao Zuo used to“ Linux VPS uses the command to quickly import/back up the MYSQL database "The use of mysqldump Back up the website database, many group friends mentioned that the PHPMYADMIN export is more complete than the PHPMYADMIN export. Of course, there is little difference between using PHPMYADMIN to export a database and using MYSQLDUMP for websites with small databases. The latter may be more accurate and complete when the data structure is complex or the database is large.

In sorting out this article today, first of all, I didn't understand it too well when I shared the article, and second, I sorted out and learned the detailed usage of the legendary mysqldump backup and restore MYSQL database.

First, commonly used backup MYSQL database

Mysqldump - u database user name - p database password database name>database file name.sql

This is what we usually use Backup database First edit the database connection information of the website, then enter it in SSH, and then press Enter to back up the SQL database file in our current directory.

Second, cross host Backup database

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

This method can copy the sourceDb in the host 1 to the targetDb in the host 2, but the targetDb database must be created in advance by the host 2.

Third, database recovery and restore

Mysql - u database user name - p database password database name<database file name.sql

Similarly, similar to the first method, edit the script link above, and then restore the database in the corresponding site directory, provided that we first upload the SQL file to the current directory.

For ordinary users, these methods are sufficient. If you are not sure about the integrity of the backup, you can refer to“ Four methods for common Linux VPS hosts to back up MYSQL databases "Use a variety of different methods to back up the website database. If our website is to be migrated, it is better to delete the original data after the migration is successful and there is no problem to ensure the integrity.