How to backup MySQL using mysqldump on Linux

1. BACKUP A DATABASE

Here I want to backup a database named mydb, just replace username with your credential and execute the following command:

mysqldump -u username -p mydb > mydb.sql

To backup and compress it, we can use gzip:

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

You will be prompted for entering your password, the output will be placed right into your current working directory

2. BACKUP MULTIPLE DATABASES

If you want to back up more than one database, specify each of them on the command line

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

To backup and compress, use gzip:

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

3. BACKUP ALL DATABASES

If you want to backup all the database, enter the command:

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

Or backup and compress all of them:

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

4. RESTORE DATABASE

Để restore database, bạn sử dụng câu lệnh

mysql -u username -p < backup.sql