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]

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

Example : Backup & Restore

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

Single Database backup & restore

[Database Backup]
$ sudo mysqldump -u root -p testdb > testdb.sql

[Database Restore]
$ sudo mysql -u root -p testdb < testdb.sql

Multiple Database backup & restore

[Database Backup]
$ sudo mysqldump -u root -p –databases testdb1 testdb2 > multidatabasebackup.sql

[Database Restore]
$ sudo mysql -u root -p testdb1 testdb2 < multidatabasebackup.sql

Restoring single database from full database backup

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

Full Database backup

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

Backing up Database with compressed format & Restoring compressed Database.

[Database Backup]
$ sudo mysqldump -u root -p testdb | gzip >testdb.sql.gz

[Database Backup]
$ sudo gunzip < testdb.sql.gz | mysql -u root-p testdb

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 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/ directory.

[Create a File]
# touch /opt/

[Assign execute permission]
# chmod +x /opt/

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

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 @ 6 PM everyday.

0 18 * * * /opt/

To know more about cron jobs refer the below link:

Suggested Read : 20 Best Crontab Practical Examples

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...

Please support the site
By clicking any of these buttons you help our site to get better