In this article you will find several practical examples on how to use mysqldump tool to backup and restore MySQL Databases.
The mysqldump tool creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database, just execute the *.sql file on destination database.
Backup a database
Access the MySQL shell | #mysql -uroot –p |
Show Databases | mysql> show databases; |
Backup a database | # mysqldump -uroot -p[root_password] [database_name] > dumpfilename.sql |
Restore a database
Make sure to create the database before you can perform the restore.
Access the MySQL shell |
|
First create the database |
|
Exit the MySQL shell |
|
Then restore the database |
|
Set permissions on database
After the restore you have to set your permissions again on the database.
Access the MySQL shell |
|
Set permissions. This will automatically create the user. |
|
Activate permissions |
|
Exit the MySQL shell |
|
Schedule a backup job
Save your default password for mysqldump
To run mysqldump in a cron job, you need to set a username and password to the MySQL configuration file to be used by the mysqldump command. Otherwise it’s not possible to use mysqldump without a password.
Edit the MySQL configuration file |
|
Add the following lines at the bottom of the file |
|
Setting up a cron job to backup all mysql databases
First start editing the crontab |
|
Now add a new line, which will run our command.This simple command will back up all databases to the file /root/sqldump.sql at midnight every day. |
|
Backup All databases using shell script
Below you will find a script to have gzip compressed daily backups of all your databases. You can also schedule this script in crontab.
#!/bin/bash
### Create Directory with Date where Database backup will be stored. ####
month=$(date | awk ‘{print $2}’)
day=$(date | awk ‘{print $3}’ )
year=$(date | awk ‘{print $6}’)
foldername=$(echo $day$month$year”_backups”)
### List all the databases in /usr/local/dblist file. ####
mysql -u root -p’mysqlpassword’ -e ‘show databases’ >/usr/local/dblist
list=$(cat /usr/local/dblist)
echo $foldername
### Create Backup Directory in /Backup/mysqlbackup … ####
mkdir -p /Backup/mysqlbackup/$foldername
for i in $list
do
echo $i
mysqldump -u root -p’mysqlpassword’ $i | gzip > /Backup/mysqlbackup/$foldername/$i.sql.gz
echo ” “$i”.sql.gz file saved..”
done