对MySQL数据库进行备份操作，需要考虑到数据引擎、数据大小、是否锁表、是否在线、备份耗时等因素。对于不同的数据引擎备份方式有所不同，有些备份方式不兼容其他引擎的。推荐使用Percona xtrabackup工具来对MySQL数据库进行备份。Percona xtrabackup相关内容可以参见：https://www.ttlsa.com/?s=xtrabackup 。 下面给大家介绍下mylvmbackup备份工具，顾名思义，从名字就能看出是针对采用LVM磁盘的mysql数据库的备份工具。基本原理就是首先flush tables with read lock，建立lvm快照，unlock table，copy files，删除lvm快照。
一．前提条件
perl-5
perl-DBD
perl-DBI
perl-Config-IniFiles //读取程序的全局配置文件
perl-TimeDate
perl-DBD-MySQL文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
二．下载安装文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
# wget http://lenzg.net/mylvmbackup/mylvmbackup-0.13.tar.gz # tar zxvf mylvmbackup-0.13.tar.gz # cd mylvmbackup-0.13/ # make prefix=/usr/local/mylvmbackup install # make uninstall (删除安装)
三．配置
[mysql] //连接数据库的定义
user=root
password=password
host=localhost
port=3306
socket=/tmp/mysql.sock
mycnf=/etc/my.cnf
#
[lvm] //lvm参数的定义，参照实际环境
vgname=VolGroup00
lvname=lvmydata
backuplv= //定义snapshot卷的名称，默认是_sanpshot
# lvsize must be able to cope with differences
lvsize=5G //定义快照大小
#
[fs]
xfs=0
mountdir=/var/tmp/mylvmbackup/mnt/ //挂载目录。默认的
backupdir=/var/tmp/mylvmbackup/backup/ //备份目录也可以备份到远程主机。默认的
relpath= //将逻辑卷与mysql data目录关联起来。默认为空
#
[tools] //使用命令的定义
lvcreate=/usr/sbin/lvcreate
lvremove=/usr/sbin/lvremove
lvs=/usr/sbin/lvs
mount=/bin/mount
tar=/bin/tar
compress=/bin/gzip
# alternative tar backup compression tools
#compress=lzma
#compress=bzip2
# or (for no compression):
#compress=cat
#
rsync=/usr/bin/rsync
umount=/bin/umount
#
# Other configuration options
#
[misc]
backuptype=tar //定义备份类型
prefix=backup //备份文件前缀名称，默认是backup
suffix=_mysql //备份文件后缀名称，默认是_mysql
tararg=cvfp //定义tar参数，默认是cvf
tarsuffixarg= //定义tar后缀参数，默认为空
tarfilesuffix=.tar.gz
compressarg=--stdout --verbose --best
# for LZMA:
#compressarg=--stdout --verbose -7
# for bzip2:
#compressarg=--stdout --verbose -7
# for cat:
#compressarg= # ie. nothing
rsyncarg=-avWP
rsnaparg=7
datefmt=%Y%m%d_%H%M%S //时间戳格式
innodb_recover=0
pidfile=/var/tmp/mylvmbackup_recoverserver.pid
skip_flush_tables=0
extra_flush_tables=0
skip_mycnf=0
hooksdir=/usr/local/mylvmbackup/share/mylvmbackup //hook目录
skip_hooks=0
keep_snapshot=0 //是否保留snaphot
keep_mount=0 //是否卸载snaphot
quiet=0 //定义记录日志类型
#
# Logging options. The Sys::Syslog module is required for syslog option
# See "perldoc Sys::Syslog" for more information.
#
[logging]
# 'console' (STDOUT, STDERR) or 'syslog' or 'both'.
log_method=both
# 'native', 'tcp', 'udp'. Default is 'native'
syslog_socktype=native
syslog_facility=
# If using remote syslog, don't forget to change the socket type to tcp or udp.
syslog_remotehost=文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
四．实践文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
# mylvmbackup
20101103 11:05:13 Info: Connecting to database...
20101103 11:05:13 Info: Flushing tables with read lock...
20101103 11:05:13 Info: Taking position record into /tmp/mylvmbackup-backup-20101103_110513_mysql-bTF6zV.pos...
20101103 11:05:13 Info: Running: /usr/sbin/lvcreate -s --size=20G --name=lvmydata_snapshot /dev/VolGroup00/lvmydata
File descriptor 4 (socket:[2407170]) leaked on lvcreate invocation. Parent PID 1909: /usr/bin/perl
Logical volume "lvmydata_snapshot" created
20101103 11:05:13 Info: DONE: taking LVM snapshot
20101103 11:05:13 Info: Unlocking tables...
20101103 11:05:13 Info: Disconnecting from database...
20101103 11:05:13 Info: Mounting snapshot...
20101103 11:05:13 Info: Running: /bin/mount -o rw /dev/VolGroup00/lvmydata_snapshot /var/tmp/mylvmbackup/mnt/backup
20101103 11:05:13 Info: DONE: mount snapshot
20101103 11:05:13 Info: Copying /tmp/mylvmbackup-backup-20101103_110513_mysql-bTF6zV.pos to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20101103_110513_mysql.pos...
20101103 11:05:13 Info: Copying /etc/my.cnf to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20101103_110513_mysql_my.cnf...
20101103 11:05:13 Info: Taking actual backup...
20101103 11:05:13 Info: Creating tar archive /var/tmp/mylvmbackup/backup/backup-20101103_110513_mysql.tar.gz
20101103 11:05:13 Info: Running: cd '/var/tmp/mylvmbackup/mnt' ;'/bin/tar' cvfp - backup/ backup-pos/backup-20101103_110513_mysql.pos backup-pos/backup-20101103_110513_mysql_my.cnf| /bin/gzip --stdout --verbose --best -> /var/tmp/mylvmbackup/backup/backup-20101103_110513_mysql.tar.gz.INCOMPLETE-1dnZuce
backup/
(压缩包的过程，省略)
20101103 11:46:39 Info: DONE: create tar archive
20101103 11:46:39 Info: Cleaning up...
20101103 11:46:39 Info: Running: /bin/umount /var/tmp/mylvmbackup/mnt/backup
20101103 11:46:39 Info: DONE: Unmounting /var/tmp/mylvmbackup/mnt/backup
20101103 11:46:39 Info: LVM Usage stats:
20101103 11:46:39 Info: LV VG Attr LSize Origin Snap% Move Log Copy% Convert
20101103 11:46:39 Info: lvmydata_snapshot VolGroup00 swi-a- 20.00G lvmydata 0.03
20101103 11:46:39 Info: Running: /usr/sbin/lvremove -f /dev/VolGroup00/lvmydata_snapshot
Logical volume "lvmydata_snapshot" successfully removed
20101103 11:46:39 Info: DONE: Removing snapshot文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
五. 说明
1. backup-pos目录下会有两个文件：
backup-timestamp_mysql_my.cnf my.cnf的配置文件
backup-timestamp_mysql.pos
2. 这个对主从环境下很有用，记录的内容有：
Master:File=
Master:Position=
Master:Binlog_Do_DB=
Master:Binlog_Ignore_DB=文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
上面的内容是我在2010的时候写的，总结到此处，希望对使用 mylvmback者有所帮助。文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
转载请注明来自运维生存时间: https://www.ttlsa.com/html/3666.html文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mylvmback-mysql-database-backup/
评论