博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
mylvmback备份MySQL数据库
阅读量:5825 次
发布时间:2019-06-18

本文共 5015 字,大约阅读时间需要 16 分钟。

  hot3.png

对MySQL数据库进行备份操作,需要考虑到数据引擎、数据大小、是否锁表、是否在线、备份耗时等因素。对于不同的数据引擎备份方式有所不同,有些备份方式不兼容其他引擎的。推荐使用 工具来对MySQL数据库进行备份。Percona xtrabackup相关内容可以参见:http://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
二.下载安装
# 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=
四.实践
# 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
五. 说明 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= 上面的内容是我在2010的时候写的,总结到此处,希望对使用 mylvmback者有所帮助。 转载请注明来自 :  http://www.ttlsa.com/html/3666.html

转载于:https://my.oschina.net/766/blog/210878

你可能感兴趣的文章
django 目录结构修改
查看>>
win8 关闭防火墙
查看>>
CSS——(2)与标准流盒模型
查看>>
C#中的Marshal
查看>>
linux命令:ls
查看>>
Using RequireJS in AngularJS Applications
查看>>
hdu 2444(二分图最大匹配)
查看>>
【SAP HANA】关于SAP HANA中带层次结构的计算视图Cacultation View创建、激活状况下在系统中生成对象的研究...
查看>>
DevOps 前世今生 | mPaaS 线上直播 CodeHub #1 回顾
查看>>
iOS 解决UITabelView刷新闪动
查看>>
CentOS 7 装vim遇到的问题和解决方法
查看>>
JavaScript基础教程1-20160612
查看>>
ios xmpp demo
查看>>
python matplotlib 中文显示参数设置
查看>>
【ros】Create a ROS package:package dependencies报错
查看>>
通过容器编排和服务网格来改进Java微服务的可测性
查看>>
re:Invent解读:没想到你是这样的AWS
查看>>
PyTips 0x02 - Python 中的函数式编程
查看>>
使用《Deep Image Prior》来做图像复原
查看>>
Linux基础命令---rmdir
查看>>