Backup and Restore MySQL database using mysqldump

Everybody knows MySQL is opensource RDBMS which was developed by ORACLE and most of the small company, wordpress & joomla, etc..websites are using MySQL database. So, we should aware for backup and restore commands of MySQL which will help us when we are need to take backup and restore. The below example explains, how to take a backup and restore your MySQL database using mysqldump. mysqldump is an effective method to backup mysql database. It creates a *.sql file and you can restore a *.sql file anywhere you want restore.

# 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 full/all Database backup : #
$ sudo mysqldump -u [username] -p [password] –-alldatabases > [backupfile.sql]

# Details of above syntax: #
UserName : Database username
Password : Password for your database
DatabaseName : The name of your database
backupfile.sql : The file to which the backup should be written.

1) Backup & restore Examples

You can use the below examples to take a backup of single, multiple & all databases and restore the same.

# Single Database backup Example : #
$ sudo mysqldump -u root -p testdb > testdb.sql
[Enter the password when prompt for password]

# Single Database restore Example : #
$ sudo mysql -u root -p testdb < testdb.sql
[Enter the password when prompt for password]

# Multiple Database backup Example : #
$ sudo mysqldump -u root -p –databases testdb1 testdb2 > multidatabasebackup.sql
[Enter the password when prompt for password]

# Multiple Database restore Example : #
$ sudo mysql -u root -p testdb1 testdb2 < multidatabasebackup.sql
[Enter the password when prompt for password]

# Multiple Database restore single database from multiple database backup file : #
$ sudo mysql -u root -p testdb1 < multidatabasebackup.sql
[Enter the password when prompt for password]

# All Database backup Example : #
$ sudo mysql -u root -p < alldatabases.sql
[Enter the password when prompt for password]

# Backup Database with compressed format : #
$ sudo mysqldump -u root -p testdb | gzip >testdb.sql.gz
[Enter the password when prompt for password]

# Restore compressed Database format : #
$ sudo gunzip < testdb.sql.gz | mysql -u root-p testdb
[Enter the password when prompt for password]

2) Shell Scripts for Backup automation

As a server administrator you won’t remember everything, so for routine works you can use the cron jobs to execute the program to your convenience.

Example

The below example is to take an automatic backup of “testdb” database and store it to “/backup/db/testdab19-09-2013.tar.gz” and keep 5 days backup.
create the file mysql_backup.sh on mysql bin directory and add the below code, the file permission should be 755 so that you can execute the file.

#!/bin/bash
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 for scheduling backups at your convenience

Cron Entry

The schedule cron will be executed every day 6’o clock

0 18 * * * /bin/mysql_backup.sh

For cron job’s example : View More

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...

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