MySQL database backup & restore

Creating and restoring backups of MySQL databases on Linux using mysqldump and mysql
created by on 2013-04-17

Backup

Command

Using mysqldump to create a database backup:

mysqldump --default-character-set="UTF8" -h localhost -P 3306 -u root -p --opt "Databasename" > database-backup.sql

Script

A shell script for creating backups of a MySql database with mysqldump:

# !/bin/bash

# Parameters
user="root"
port="3306"
hostname="localhost"
databasename="Some_Database"

# Assemble filename
currentdate=$(date +"%y-%m-%d")
backupfilename="$databasename-$currentdate.sql"
compressedbackupfilename="$backupfilename.bz2"

# Trigger Backup
mysqldump --default-character-set="UTF8" -h $hostname -P $port -u $user -p --opt $databasename | bzip2 > $compressedbackupfilename

Restore

Command

Using mysql to restore a database backup:

mysql --default-character-set="UTF8" -h localhost -P 3333 -u root -p "Databasename" < database-backup.sql
Tags: