User Tools

Site Tools


linux:mysql_bkp

This is an old revision of the document!


MySQL backup

script dump

#!/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

Backup using LVM snapshot

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
linux/mysql_bkp.1642167692.txt.gz · Last modified: 2022/01/14 14:41 by manu