正文
mysql多种备份方式比较及实现
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
Mysql备份
MySQL的备份和还原：
备份：存放为副本-->数据备份
RAID1，RAID10：保证硬件损坏而不会业务中止，不能保证逻辑上的损害
例如：DROP TABLE mydb.tb1;如果使用raid1，则两边都删除了，则需要备份
备份类型
热备份、温备份和冷备份
热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；
物理备份和逻辑备份
物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；
完全备份、增量备份和差异备份
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；
备份什么：
数据、配置文件、二进制日志、事务日志
热备份：
MyISAM: 温备份
InnoDB: xtrabackup, mysqldump
MySQL --> 从：
物理备份：速度快
逻辑备份：速度慢、丢失浮点数精度；方便使用文本处理工具直接对其处理、可移植能力强；
备份策略：完全+增量；完全+差异
MySQL备份工具：
mysqldump: 逻辑备份工具、MyISAM(只能温备)、InnoDB(热备份)
mysqlhotcopy：物理备份工具、温备份
文件系统工具：
cp：冷备
lv: 逻辑卷的快照功能，几乎热备；
mysql> FLUSH TABLES;
mysql> LOCK TABLES 创建快照：释放锁，而后复制数据
或者：FLUSH TABLES WITH READ LOCK
第三组工具：
ibbackup: 商业工具
xtrabackup: 开源工具
mysqldump: 逻辑备份
mysqldump（完全备份）+ 二进制日志
完全+增量：
备份单个数据库，或库中特定表
mysqldump DB_NAME [tb1] [tb2]
参数：
--master-data={0|1|2}
0: 不记录二进制日志文件及路位置；
1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；
2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；
常用 --master-data=2
--lock-all-tables：锁定所有表
--flush-logs: 执行日志flush；
--single-transaction如果指定库中的表类型均为InnoDB，可使用热备；
备份数据库：
--all-databases: 备份所有库
例：mysqldump -uroot -p --master-data=2 --lock-all-tables --flush-logs --all-databases > all.sql
--databases DB_NAME,DB_NAME,...: 备份指定库
例：mysqldump -uroot -p --master-data=2 --lock-all-tables --flush-logs --databases shao shao1 shao2 > shao_all.sql
备份一个库
mysqldump -uroot -p --master-data=2 --lock-all-tables --flush-logs shao > shao.sql
备份某个库下的表
mysqldump -uroot -p --master-data=2 --lock-all-tables --flush-logs shao A1 > shao.sql
备份全部表类型为InnoDB存储引擎
mysqldump -uroot -p --single-transaction shao > shao.sql
数据库备份还原（mysql还原时不能自己创建库，需登录mysql服务器创建库以后，才能还原）
mysql -uroot -p shao < shao.sql 还原一个库
mysql -uroot -p < all.sql 还原所以数据库
--events 导出事件
--routines 导出存储过程以及自定义函数
--triggers 导出触发器。该选项默认启用，用--skip-triggers禁用它。
例：（备份加还原,适合实际生产环境）
备份策略：每周完全+每日增量
完全备份：mysqldump
增量备份：备份二进制日志文件(flush logs)
步骤：
1、mysqldump -uroot -p --master-data=2 --flush-logs --lock-all-tables --all-databases > all.sql -->备份所以数据库
2、mysql> SHOW BINARY LOGS; -->查看二进制日志
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysqld-bin.000001 | 2092456 |
| mysqld-bin.000002 | 120 |
+-------------------+-----------+
mysql> PURGE BINARY LOGS TO 'mysqld-bin.000002';
(删除之前的二进制文件，避免占用太多的空间，但不建议删除二进制文件，应复制一份以后，在删除)
mysql> DELETE FROM shao WHERE id<6; -->模拟删除一些数据
mysql> FLUSH LOGS; -->刷新一下日志
mysql> SHOW BINARY LOGS; -->查看日志滚动后
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysqld-bin.000002 | 375 |
| mysqld-bin.000003 | 120 | -->滚动后的二进制日志
+-------------------+-----------+
3、# mysqlbinlog mysqld-bin.000002 > /root/first.sql -->将日志滚动前的二进制日志备份第一天的增量
4、mysql> INSERT INTO shao VALUES (10,'HSY'),(25,'CSA'),(36,'ASJK');
（模拟插入一些数据）
5、模拟删除所有数据库（注：千万不能删除二进制文件）
rm -rf /var/lib/mysql/* -->删除所有mysql的数据库
Service mysqld restart -->重启数据库进行还原
6、mysql>SET sql_log_bin=1; -->还原过程不需要记录到二进制日志，暂时关闭
7、mysql -uroot -p < all.sql 还原所有数据库
mysql < first.sql 还原第一次的增量备份
（查看表发现还原第一次增量备份后，模拟删除的数据没有了）
8、还原到服务器最后时刻
# mysqlbinlog mysqld-bin.000003 > two.sql -->制作当前二进制的sql文件
# mysql < two.sql -->导入当前二进制文件，还原到服务器最初时刻
或者
# mysqlbinlog mysqld-bin.000003 | mysql -uroot -p
9、mysql>SET sql_log_bin=1; -->还原完毕以后，开启二进制记录
注：在还原数据库的时候，执行的动作是不用写入二进制文件的，可以实现还原的时候关闭二进制文件的写入，还原完毕以后打开
SET sql_log_bin=0/on; 只针对当前会话起作用
SET sql_log_bin=1/off;
逻辑备份（文本文件备份方式）
备份：
mysql> SELECT * INTO OUTFILE '/tmp/user.txt' FROM mysql.user;
--->备份mysql库的user表
mysql> SELECT * INTO OUTFILE '/tmp/user.txt' FROM mysql.user where host=’%’ and user=’root’; --->from后面跟where条件
注：指定的备份路径属主属组必须的是mysql才行
还原：
mysql> LOAD DATA INFILE '/tmp/A1.txt' INTO TABLE A2;
注：写入二进制文件中，是以行的复制记录，备份快，还原快，但是只能单张表备份，不便于管理。
Xtrabackup备份
安装Xtrabackup
1、自动安装percona的yum仓库(以下分别为x86_64和i386平台)
X86_64位
#rpm -ivh http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
32位
#rpm -ivh http://www.percona.com/redir/downloads/percona-release/percona-release-0.0-1.i386.rpm
2、yum list | grep percona -->查看软件包
3、安装
yum install percona-xtrabackup-24
4、报错处理
报错：libz.so.1(ZLIB_1.2.0)(64bit) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64
解决方法：可以安装个新版试试，我今天也遇到这个问题，下了最新的安装上就可以了
get http://zlib.net/zlib-1.2.8.tar.gz
tar zxf zlib-1.2.8.tar.gz
cd zlib-1.2.8
./configure
make && make install
cp /usr/local/lib/libz.so.1.2.8 /lib64/
cd /lib64/
rm libz.so.1
ln -s libz.so.1.2.8 libz.so.1
2、报错
解决方法：其实是因为percona-xtrabackup依赖libev.so.4() (64bit)的包，而这个包只能从EPEL源(Extra Packages for Enterprise Linux，企业版Linux的额外软件包 是Fedora小组维护的一个软件仓库项目，为RHEL/CentOS提供他们默认不提供的软件包)安装 ,所以只要安装了这个包即可解决问题.
从上下载libev-4.15-1.el6.rf.x86_64.rpm安装包
在http://rpmfind.net/linux/rpm2html/search.php页面上输入libev下载
安装libev
Xtrabackup备份
完全备份与还原：
#innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
例：innobackupex --user=root --password=123456 /root/backup/
准备(prepare)一个完全备份
innobackupex --apply-log /root/backup/2016-03-29_16-14-40/
复制二进制日志：
mysql> flush logs;
# cp loclahost-bin.000001 /root/
还原完全备份的数据：
# innobackupex --copy-back /path/to/BACKUP-DIR
例：# innobackupex --copy-back /root/backup/2016-03-29_16-14-40/
注意：还原备份时，是不需要启动mysql数据库的
改变属主属组
chown -R mysql:mysql /var/lib/mysql （不改无法启动数据库）
导入之前cp出去的二进制日志
# mysqlbinlog loclahost-bin.000001 > abc.sql
mysql> SET sql_log_bin=0;
mysql> source /root/abc.sql
mysql> SET sql_log_bin=1;
增量备份（每次还原后，都必须做一次完全备份，在做增量备份）
# innobackupex --user=root --password=123456 /root/backup/
注：增量备份前先完全备份
第一次增量备份
# innobackupex --incremental /backup --incremental-basedir=BASEDIR
其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
例：# innobackupex --incremental /root/backup/ --incremental-basedir=/root/backup/2016-03-29_16-38-02/ --user=root --password=123456
第二次增量备份
#innobackupex --incremental /root/backup/ --incremental-basedir=/root/backup/2016-03-29_16-41-04/ --user=root --password=123456
注：将--incremental-basedir指向第一次增量备份的目录
执行准备回滚操作
准备完全备份：
# innobackupex --apply-log --redo-only /root/backup/2016-03-29_16-38-02/
准备第一次增量备份，提交事物
# innobackupex --apply-log --redo-only /root/backup/2016-03-29_16-38-02/ --incremental-dir=/root/backup/2016-03-29_16-41-04/
准备第二次增量备份，提交事物
# innobackupex --apply-log --redo-only /root/backup/2016-03-29_16-38-02/ --incremental-dir=/root/backup/2016-03-29_16-46-57/
复制二进制日志：
mysql> flush logs;
# cp loclahost-bin.000001 /root/
还原数据库
# innobackupex --copy-back /root/backup/2016-03-29_16-38-02/
注：只需要还原完全备份的就可以
改变属主属组
chown -R mysql:mysql /var/lib/mysql （不改无法启动数据库）
导入之前cp出去的二进制日志
# mysqlbinlog loclahost-bin.000001 > abc.sql
mysql> SET sql_log_bin=0;
mysql> source /root/abc.sql
mysql> SET sql_log_bin=1;
MySQL的LVM快照备份、恢复
1、划分分区【标识号：8e】
2、创建lvm分区：
(1)、建立pv
pvcreate /dec/sda5 #查看pv：pvs
(2)、建立vg(卷组)
vgcreate vgtest /dev/sda5#查看vg：vgs
(3)、建立lv(逻辑分区),并指定大小
lvcreate -L 4G -n lvtest vgtest #查看lv：lvs
(4)、格式化lv(格式化逻辑分区)
mkfs.ext4 /dev/vgtest/lvtest
3、迁移原mysql数据：
(1)、将建立的分区挂载到临时目录【不可以挂载到/tmp目录下】
mount /dev/vgtest/lvtest /mysql#/mysql为临时新建的目录
(2)、迁移原mysql数据
①、停止mysql服务
service mysqld stop
②、cd var/lib/mysql
③、tar cf - . | tar -xf - -C /mysql
#将数据迁移到刚才挂载的逻辑分区上【/mysql】
- 指输出到屏幕#
.指当前目录
-C：指定解压的目录
(3)、卸载临时分区
umount /mysql
(4)、重新挂载到mysql服务的分区（/var/lib/mysql）
mount /dev/vgtest/lvtest /var/lib/mysql
(5)、审核/var/lib/mysql的权限，特别注意selinux
恢复/var/lib/mysql目录权限：
restorecon -R /var/lib/mysql
(6)、启动mysql服务，若能正常启动，则OK
service mysqld restart
4、创建快照：
(1)、锁表：在命令行模式下
Mysql>flush tables with read lock
(2)、查看position号：
Mysql>Show master status
#记录下position号，假定为106
(3)、创建快照：
lvcreate -L 40M -s -n mysnap1 /dev/vgtest/lvtest
#-L：指定容量【容量一般为已知容量的2倍，用完后释放】
#-s：建立快照
-n指定快照名
(4)、解锁
Mysql>unlock tables
5、备份：
(1)、挂载快照【这里我们还是挂载到刚才建的临时目录】
mount /dev/vgtest/mysnap1 /mysql#默认快照建立后，位于卷组里
(2)、从快照里备份，默认日志不备份
tar cf 123.tar文件1文件2文件3文件4。。。。。
(3)、删除快照，释放空间
lvremove /dev/vgtest/mysnap16、
恢复：
(1)、停mysql服务
service mysqld stop
(2)、初始化mysql环境，日志保留
(3)、恢复备份的tar包【123.tar】
tar xf 123.tar -C /var/lib/mysql
(4)、重演日志，开始位置，为刚才记录position号
mysqlbinlog --start-posirion=106 mysql-bin.000013 > 13.sql -->按照日志保存
mysqlbinlog --start-datetime=”2016-03-29 10:58:26” mysqld-bin.000003 mysqld-bin.000004 > /root/backup/log.sql -->按照时间保存在一起
(5)、启动mysql服务
service mysqld start
(6)、还原二进制日志
mysql < /root/backup/log.sql
7、测试：
登陆到mysql数据库，查看数据是否完整，即可！！
