Backup Database Using Mysqldump


Backup Database Using Mysqldump. Backup and restore databases occur frequently. When the database is large, we cannot use the GUI. At this point, we need to use the command line to execute.

Here are some ways to backup database using Mysqldump.

1. Backup a database – Backup Database Using Mysqldump

You use the following command to backup mydb database, replace the username with your username

mysqldump -u username -p mydb > mydb.sql

You can both backup and compress to gzip, you can use the following command:

mysqldump -u username -p mydb | gzip > mydb.sql.gz

The system will ask for the password and perform the backup, creating the backup file in the working directory

2. Backup multiple databases

To backup multiple databases at the same time, use the command:

mysqldump -u username -p --databases dbname1 dbname2 dbname3 > backup.sql

To backup and compress, you use:

mysqldump -u username -p --databases dbname1 dbname2 dbname3 | gzip > backup.sql.gz

3. Backup full databases

If you have multiple databases, you can perform backups with just one command

mysqldump -u username -p -–all-databases > alldb.sql

Or backup and use gzip to compress it

mysqldump -u username -p -–all-databases | gzip > alldb.sql.gz

The above commands will create a backup alldb.sql or alldb.sql.gz file containing all the databases contained in MySQL.

4. Restore database

To restore the database, you use the command

mysql -u username -p < backup.sql

Above are the ways to backup the database. You can see more articles about MYSQL: Install MYSQL 5.7 on CentOS 7

Leave a Reply