Tuncay Sahin

ICT Engineer | Docent ICT & Trainer

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

#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

Meer informatie

Voor meer informatie of voor een persoonlijk adviesgesprek kunt u altijd vrijblijvend contact met mij opnemen.

E-Mail

info@tuncaysahin.nl