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 — #mysql -uroot –p
  • First create the database — mysql> create database [database_name];
  • Exit the MySQL shell — exit;
  • Then restore the database — # mysql -uroot -p [database_name] < dumpfilename.sql

Set permissions on database

After the restore you have to set your permissions again on the database.

  • Access the MySQL shell — #mysql -uroot -p
  • Set permissions. This will automatically create the user. — grant all privileges on databasename.* to ‘username’@’localhost’ identified by ‘password’;
  • Activate permissions — flush privileges;
  • Exit the MySQL shell — exit;

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 — vi file /etc/my.cnf
  • Add the following lines at the bottom of the file — [mysqldump] user = mysql-user password = your-password

Setting up a cron job to backup all mysql databases

  • First start editing the crontab — crontab –e
  • 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. — 0 0 * * * mysqldump –all-databases > /root/sqldump.sql

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