====== MySQL backup ======

===== script dump =====

==== script 1 ====

<code>
#!/bin/bash
# Mettre un date au nom du fichier
DATE=$(date +"%Y%m%d")

# Dossier de sauvegarde
BACKUP_DIR="/backup/mysqldump"

# MySQL username/password
MYSQL_USER="root"
MYSQL_PASSWORD="YOURSECUREPASSWORD"

# Commandes MySQL nécessaires pour faire le backup
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# Bases de données MySQL que vous voulez ignorer
SKIPDATABASES="information_schema|performance_schema|mysql|database1|database2"

# Rétention des données (les jours pour garder les backups)
RETENTION=5

# Création d'un nouveau dossier avec la date du jour
mkdir -p $BACKUP_DIR/$DATE

# Donne la liste de toutes les bases des données. 
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# Copier les bases des données distinctes et fait un fichier gzip pour chaque database.
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Efface les fichiers plus vieux que votre date de RETENTION
find $BACKUP_DIR/* -mtime +$RETENTION -delete
</code>

==== script 2 ====

<code>
#!/bin/sh

#----------------------------------------------------------
# a simple mysql database backup script.
# version 2, updated March 26, 2011.
# copyright 2011 alvin alexander, http://alvinalexander.com
#----------------------------------------------------------
# This work is licensed under a Creative Commons 
# Attribution-ShareAlike 3.0 Unported License;
# see http://creativecommons.org/licenses/by-sa/3.0/ 
# for more information.
#----------------------------------------------------------

# (1) set up all the mysqldump variables
FILE=minime.sql.`date +"%Y%m%d"`
DBSERVER=127.0.0.1
DATABASE=XXX
USER=XXX
PASS=XXX

# (2) in case you run this more than once a day, remove the previous version of the file
unalias rm     2> /dev/null
rm ${FILE}     2> /dev/null
rm ${FILE}.gz  2> /dev/null

# (3) do the mysql database backup (dump)

# use this command for a database server on a separate host:
#mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}

# use this command for a database server on localhost. add other options if need be.
mysqldump --opt --user=${USER} --password=${PASS} ${DATABASE} > ${FILE}

# (4) gzip the mysql database dump file
gzip $FILE

# (5) show the user the result
echo "${FILE}.gz was created:"
ls -l ${FILE}.gz
</code>
===== Backup using LVM snapshot =====

How to backup mysql database with LVM snapshots:
<cli prompt='#'>
root@mysqlsrv:~# mysql -u root -p
mysql> flush tables with read lock;
mysql> flush logs;
mysql> quit;

root@mysqlsrv:~# lvcreate --snapshot --size=1000M --name=backupmysql /dev/vg01/mysql

root@mysqlsrv:~# mysql -u root -p
mysql> unlock tables;
mysql> quit;

root@mysqlsrv:~# mkdir -p /backup/mysql
root@mysqlsrv:~# mount -o ro /dev/vg01/backupmysql /backup/mysql
root@mysqlsrv:~# cd /backup/mysql
root@mysqlsrv:~# tar czvf mysql.$(date +"%m-%d%-%Y).tar.gz mysql
root@mysqlsrv:~# umount /backup/mysql
root@mysqlsrv:~# lvremove -f /dev/vg01/backupmysql
</cli>