Backup Restore MySQL data

De Wiki do Bernardino
Ir para: navegação, pesquisa

Backup Database

To backup a database use the mysqldump command, which lets you download the entire contents of a database. This script allows a simple way to do the backup of your Mysql database:

#!/bin/bash
# Created by jose@bernardino.org

username="root"
password="YOUR_MYSQL_PASSWORD"
export d=`date +%u`
 
function backup-mysql()
{
  cd ~
  mkdir -p backup.mysql/$database/$d
  for i in `echo "show tables" | mysql -u $username -p$password $database|grep -v Tables_in_`;
  do
 	echo $i; mysqldump --add-drop-table --allow-keywords -q -a -c -u $username -p$password $database $i > backup.mysql/$database/$d/$i.sql
  done
}
 
function backup-mysql-dir()
{
  mkdir -p $dir
  for i in `echo "show tables" | mysql -u $username -p$password $database|grep -v Tables_in_`;
  do
      echo $i; mysqldump --add-drop-table --allow-keywords -q -a -c -u $username -p$password $database $i > $dir/$i.sql
  done
}

[ $# -gt 0 ] || { echo "usage: $0 database [dir]" ; echo -n ; echo "show databases" | mysql -u $username -p$password; exit 1;} database=$1
echo -----------------------------------------------
if [ $# -eq 2 ]
then
   dir=$2
   echo Database: $database Backup Directory: $dir
   backup-mysql-dir
else
   echo Database: $database Backup Day: $d
   backup-mysql
fi
echo -----------------------------------------------

All information is stored in the home directory folder backup.mysql, inside this a folder is created with the database name and the current day of the week. If you performed a daily backup, you have a week of backups. You may also specify the directory where you want to put the backup.

Restore Database

To restore a previous backup use the follow script:

#!/bin/bash
# Created by jose@bernardino.org

username="root"
password="YOUR_MYSQL_PASSWORD"
export d=`date +%u`

function restore-mysql()
{
  for i in ~/backup.mysql/$database/$d/*
  do
    echo Processing $i
    mysql -u $username -p$password $database < $i
  done
}

function restore-mysql-dir()
{
  for i in ~/$dir/*
  do
    echo Processing $i
    mysql -u $username -p$password $database < $i
  done
}

[ $# -gt 0 ] || { echo "usage: $0 database [dir]" ; echo -n ; echo "show databases" | mysql -u $username -p$password; exit 1;}
cd ~
database=$1
if [ $# -eq 2 ]
then
  dir=$2
  restore-mysql-dir
else
  restore-mysql
fi

Create Database

A simple way to create a database and a default user (with the same name of the database) with a given password:

#!/bin/bash
# Created by jose@bernardino.org

username="root"
password="YOUR_MYSQL_PASSWORD"
export d=`date +%u`

function createdbuser-mysql()
{
  echo "CREATE DATABASE $database;" | mysql -u $username -p$password
  echo "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';" | mysql -u $username -p$password
  echo "GRANT ALL ON $dbuser.* TO '$database'@'localhost';" | mysql -u $username -p$password
}

[ $# -gt 1 ] || { echo "usage: $0 database userpass" ; echo -n ; echo "show databases" | mysql -u $username -p$password; exit 1;}

database=$1
dbuser=$1
dbpass=$2

createdbuser-mysql