Skip to content
Databases
MySQL backup and restore with mysqldump

MySQL backup and restore with mysqldump

Back up

  1. Put the server in read-only state:

    FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

    Keep this session open until the backup finishes – the lock is released when the session ends.

  2. Back up the data with the backup script or with: mysqldump -h host_name --user=user_name --password=password --events --opt --single-transaction db_for_backup | gzip > backup_name.gz

  3. Turn read-only mode off:

    SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Restore

  • Use the restore script, or:
  • mysql -u user db_name < backup_name
  • gunzip < backup_name.gz | mysql -u user -p db_name

mysql_backup_by_db.sh

Interactive script: dumps every database (or a single one) into its own gzipped file in mysql_dump_<date>/ and prints a status table.

#!/bin/bash

read -e -p "Type host for backup, followed by [ENTER]: " -i "127.0.0.1" host
read -e -p "Type username for $host, followed by [ENTER]: " -i "root" user
read -e -p  "Type password for $user, followed by [ENTER]: " -i "" passwd
read -e -p  "Database which should be backuped [ENTER]: " -i "all" db

DB_BACKUP=mysql_dump_$(date +%F)

mkdir $DB_BACKUP
cd $DB_BACKUP
if [ $db == "all" ]
then
	for db in $(mysql  -h $host --user=$user --password=$passwd -e 'show databases' -s --skip-column-names|grep -viE '(staging|performance_schema|information_schema)'); do
		file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
		mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
		[ -s $file_name ] && res=Done || res=FAIL
		echo $db"|"$file_name"|"$res
	done | column -s "|" -t
else
                file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
                mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
                [ -s $file_name ] && res=Done || res=FAIL
                echo $db"|"$file_name"|"$res
fi        	| column -s "|" -t

mysql_restore.sh

Restores every mysqldump* file from the directory passed as the first argument, creating each database first. The files must be uncompressed (gunzip them first).

#!/bin/bash

for i in `ls $1/mysqldump*`; do
    db=`sed -n -e 3p $i | awk -F "Database: " {'print $2'}`
    mysql -e "create database \`$db\`"
    echo `mysql -u root "$db" < $i`
    echo "created $db and filled from $i file"
done
Last updated on
MySQL cheatsheet