How To Backup And Restore MySQL/MariaDB Database From The Command Line

We all know that MySQL is an opensource RDBMS which was developed by ORACLE.

Most of the CMS websites such as wordpress ,joomla …are using MySQL database as a back-end.

Linux Administrator should be aware of backup and restore commands of MySQL.

The below example shows us how to take a backup and restore MySQL database using mysqldump command.

mysqldump is one of the effective method to backup mysql database .

It creates a backup file with *.sql extension , which can be restored easily.

Syntax for Single Database backup

$ sudo mysqldump -u [username] -p [don't enter the password here] [databasename] > [backupfile.sql]

Syntax for Single Database restore

$ sudo mysql -u [username] -p [don't enter the password here] [databasename] < [backupfile.sql]

Syntax for Multiple Database backup

$ sudo mysqldump -u [username] -p [password] –databases [databasename1] [databasename2] > [backupfile.sql]

Syntax for all Database backup

$ sudo mysqldump -u [username] -p [password] –-alldatabases > [backupfile.sql]

Details:

UserName : Database username
Password : Password for your database
DatabaseName : The name of your database
backupfile.sql : Backup file name

Examples: Backup & Restore

The below example shows us the way of backing up and restoring single, multiple or all databases.

1) How To Backup Single MySQL/MariaDB Database In Linux?

In this example, we are going to take backup of the testdb to testdb.sql file.

$ sudo mysqldump -u root -p testdb > testdb.sql

2) How To Restore Single MySQL/MariaDB Database In Linux?

In this example, we are going to restore the testdb database from testdb.sql file.

$ sudo mysql -u root -p testdb < testdb.sql

3) How To Backup Multiple MySQL/MariaDB Databases In Linux?

In this example, we are going to backup of the testdb1 and testdb2 to multidatabasebackup.sql file.

$ sudo mysqldump -u root -p –databases testdb1 testdb2 > multidatabasebackup.sql

4) How To Restore Multiple MySQL/MariaDB Databases In Linux?

In this example, we are going to restore the databases testdb1 and testdb2 from multidatabasebackup.sql file because the multidatabasebackup.sql file has both the database backup.

$ sudo mysql -u root -p testdb1 testdb2 < multidatabasebackup.sql

5) How To Restore Single MySQL/MariaDB Database From Full Database Backup In Linux?

In this example, we are going to restore only the testdb1 from multidatabasebackup.sql file.

$ sudo mysql -u root -p testdb1 < multidatabasebackup.sql

6) How To Restore All The MySQL/MariaDB Databases From Full Database Backup In Linux?

In this example, we are going to restore all the databases from alldatabases.sql file.

$ sudo mysql -u root -p < alldatabases.sql

7) How To Backup Single MySQL/MariaDB Database In Compressed Format?

In this example, we are going to take the compressed backup of the testdb to testdb.sql.gz file. It will reduce the disk space utilization on the server when the database size is big.

$ sudo mysqldump -u root -p testdb | gzip >testdb.sql.gz

8) How To Restore The Compressed Format Of Single MySQL/MariaDB Database In Linux?

In this example, we are going to restore the testdb database from the compressed testdb.sql.gz file.

$ sudo gunzip < testdb.sql.gz | mysql -u root-p testdb

9) Shell Scripts for Backup Automation

The below example takes an automatic backup of testdb database. The backed up database will be stored into /backup/db/testdab19-09-2013.tar.gz with the retention period of five days.

Create a file named mysql_backup.sh on desired directory and add the below code, the file permission should be 755 in order to execute the file. In my case, i have created a file under /opt/mysql_backup.sh directory.

Below is the shell script for MySQL/MariaDB backup.

# vi /opt/mysql_backup.sh

#!/bin/bash
date_format=`date +%d-%m-%Y`
mysqldump -u root testdb| gzip -9 > /backup/db/testdb-$date_format.sql.gz
find /backup/db/test* -mtime +5 -exec rm {} \;

Set an executable permission to mysql_backup.sh file.

# chmod +x /opt/mysql_backup.sh

Finally run the script to achieve this.

# sh /opt/mysql_backup.sh

Cron Job Scheduling

Cron jobs will make our life easier in executing programs on scheduled time without fail. The below cron job is scheduled to run @ 6PM everyday.

0 18 * * * /opt/mysql_backup.sh