How to backup and restore MySQL database?

MySQL is a database server that permanently stores data. If using MySQL Server, you need to create a database backup to recover from a crash. mysql provides a utility mysqldump for backup. In this article, we will introduce the archive formats concerned with backup databases in .sql format. Its various options will also be explained.

Options for creating a MySQL database backup

There are many methods for creating a database backup. For this example, we use the database name "mydb".

1. Full database backup in ordinary .sql file

 # mysqldump -u root -p mydb > mydb.sql

2. Full database backup in archived .sql.gz file

 # mysqldump -u root -p mydb |gzip> mydb.sql.gz

3. Back up a single table only

＃mysqldump -u root -p mydb tbl_student > tbl_student.sql

4. Back up multiple databases

#mysqldump -u root -p --databases mydb1 mydb2 mydb3 > mydb1-mydb2-mydb3.sql

5. Back up all databases

 # mysqldump -u root -p--all-databases> all-db-backup.sql

6. Back up only the database structure (no data)

 # mysqldump -u root -p--no-datamydb > mydb.sql

7. Back up only the database data (no table structure)

 # mysqldump -u root -p--no-create-infomydb > mydb.sql

8. Back up MySQL database in XML format

#mysqldump -u root -p --xml mydb> mydb.xml

How to restore MySQL backup?

Restoring a database from backup is very simple. We use mysql command. For example, the following command restores all backups from mydb.sql to the mydb database.

#mysql -u root -p mydb <mydb.sql

This article has ended here. For more other exciting content, you can pay attention to the relevant column tutorials on the php Chinese website! ! !

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