This is an old revision of the document!
#!/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
How to backup mysql database with LVM snapshots:
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