Methods of backing up and restoring MySQL database

How to back up and restore MySQL database? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

MySQL is a database server that permanently stores data. If you use MySQLServer, you need to create a database backup to recover from a crash. Mysql provides a utility, mysqldump, for backup.

Options for creating MySQL database backups

There are many ways to create database backups. For this example, we use the database name "mydb".

1. Full database backup in a normal .sql file

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

two。 Archive the full database backup in the .sql.gz file

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

3. Back up only a single table

# 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. Backup only database data (no table structure)

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

8. Back up the MySQL database in XML format

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

How do I restore an MySQL backup?

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

# mysql-u root-p mydb