摘要：MYSQL的lvm快照备份实现过程，mysqldump备份的过程.
LVM快照备份
特点：近乎热备的备份
实现LVM
$ echo '- - -' > /sys/class/scsi_host/host2/scan
$ lsblk
$ pvcreate /dev/sdb
$ vgcreate vg0 /dev/sdb
$ lvcreate -n lv_mysql -l 50%FREE vg0
$ lvcreate -n lv_binlog -l 50%FREE vg0
$ mkfs.ext4 /dev/vg0/lv_mysql
$ mkfs.ext4 /dev/vg0/lv_binlog
$ mkdir /data/{mysql,binlog}
$ mount /dev/vg0/lv_mysql /data/mysql
$ mount /dev/vg0/lv_binlog /data/binlog
$ chown -R mysql.mysql /data/
$ vim /etc/my.cnf
datadir=/data/mysql
log_bin=/data/binlog/mysql-bin
$ systemctl restart mariadb
快照备份
-- 读锁表
flush tables with read lock;
-- 刷新日志
flush logs;
-- 记录bin-log的position
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 17750 |
| mysql-bin.000002 | 385 |
+------------------+-----------+
-- 创建lvm快照
lvcreate -n lv_mysql_snap -L 10G -s -p r /dev/vg0/lv_mysql
-- 解锁
unlock tables;
-- 将快照的数据备份出来,centos6不用加-o选项
$ mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt
$ cp -a /mnt/ /backup
$ umount /mnt
$ lvremove /dev/vg0/lv_mysql_snap
模拟场景
-- 数据已经变更
create database testdb;
use hellodb;
update students set name='houzi' where stuid=25;
drop table teachers;
备份恢复至最新状态
$ rm -rf /data/mysql/*
-- 禁止用户访问
$ systemctl stop mariadb
$ cp -av /backup/* /data/mysql
$ vim /etc/my.cnf
[mysqld]
skip_network
$ systemctl start mariadb
$ mysql
flush tables with read lock;
show master logs;
-- 导入最新的二进制日志，恢复至破坏后的节点
$ mysqlbinlong --start-position=385 mysql-bin.000004 >/backup/bin.sql
$ mysqlbinlong --start-position=385 mysql-bin.000005 >>/backup/bin.sql
$ mysql < /backup/bin.log
mysqldump备份
mysqldump参考： 官方说明
mysqldump常见选项
-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括create database语句
-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和存储函数
--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--master-data[=#]： 此选项须启用二进制日志
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭--lock-tables功能，自动打开--lock-all- tables功能（除非开启--single-transaction）
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--flush-logs和-x，--master-data或-single-transaction,此时只刷新一次，建议：和-x，--master-data或 --single-transaction一起使用
--compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
-q, --quick 不缓存查询，直接输出，加快备份速度
实验选项演示:
比较常用的是mysqldump -A,mysqldump -A -F,mysqldump -B database
$ mysqldump -uroot -pcentos -B hellodb > /backup/hellodb_B.sql
$ mysqldump -uroot -pcentos -A |gzip > /backup/all.sql.gz
$ rm -rf /var/lib/mysql/*
$ gzip -d /backip/all.sql.gz #gunzip /backip/all.sql.gz
$ mysql < /backup/all.sql
$ mysqldump -A -F > /backup/all_f.log # 滚动刷新日志
$ mysql -e 'show binary logs'
InnoDB备份选项：
支持热备，可用温备但不建议用
--single-transaction此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务; 此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE; 此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥; 备份大型表时，建议将--single-transaction选项和--quick结合一起使用;
MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作 锁定方法如下： -x,
--lock-all-tables:,加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能 注意：数据量大时，可能会导致长时间无法并发访问数据库 -l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致 注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
实验
实验1
实现不同数据库的备份，但是和mysqldump -A不同，将每个数据库分别打包
**方法1：**利用sed,生成一个备份脚本
$ mysql -e 'show databases' | grep -iEv 'database|schema' | sed -r 's/(.*)/mysqldump -uroot -B \1 |gzip >\1-`date +%F`.sql.gz/' >backup.sh
$ cat backup.sh
mysqldump -uroot -B hellodb |gzip >hellodb-`date +%F`.sql.gz
mysqldump -uroot -B mysql |gzip >mysql-`date +%F`.sql.gz
mysqldump -uroot -B test |gzip >test-`date +%F`.sql.gz
**方法2：**利用for循环，
#!/bin/bash
for db in `mysql -e 'show databases' | grep -iEv 'database|schema'` ;do
mysqldump -uroot -B $db > ${db}-`date +%F`.sql.gz
done
总结：
复习了脚本的用法及sed，grep,gzip，for循坏，又能够实现备份不同数据库的分类存档
实验2
场景：数据库文件损坏，如何恢复。前提：有备份文件,启用了二进制日志；
$ mysql < hellodb.sql
$ mysqldump -A -F --single-transaction --master-data=1 >/backup/full.sql
$ less |/backup/full.sql
···
#记录的节点位置
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;
···
模拟数据改变
-- 数据发生了变化
create database testdb;
use hellodb;
update students set name='houzi' where stuid=25;
drop table teachers;
删库恢复
$ vim /etc/my.cnf
[mysqld]
skip_network
$ rm -rf /data/mysql/* #数据被破坏
$ mysql -e 'use hellodb'
ERROR 1049 (42000): Unknown database 'hellodb'
$ systemctl restart mariadb
$ mysqlbinlog --start-position=245 mysql-bin.000004 >/backup/bin.sql
$ mysql < /backup/full.sql
$ mysql < /backup/bin.sql
实验3
场景：9点有全备份，10点的时候误删除表，10:10后续用户修改过另外一张表，如何还原把删除表的操作还原；条件：log-bin必须启用，有数据备份。
场景模拟
$ mysqldump -A -F --single-transaction --master-data=1 >/backup/full.sql
-- 修改表
use hellodb
insert students values(26,'dongfei',25,'M',2,3)
-- 误删除
drop table students;
-- 用户继续修改
insert courses values(8,'magedu');
定点到删除的位置
flush tables with read lock;
flush logs;
-- 查看当前的bin-log的position
show master logs;
#查询全备份的position，确定全备份到目前之间的所有日志文件及position
$ head /backup/full.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245;
#找到删除语句的，删除此语句
$ mysqlbinlog --start-position=245 mysql-bin.000009 >/backup/bin.sql
$ vim /backup/bin.sql
将drop table students; 这个语句的执行at段删掉即可
还原
$ systemctl stop mariadb
$ rm -rf /data/mysql/*
$ systemctl start mariadb
$ mysql < /backup/full.sql
$ mysql < /backup/bin.sql
$ mysql -e 'show tables'
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students | #恢复删错的表，且最新数据保存下来了
| teachers |
| toc |
+-------------------+
生产环境备份实战策略
innodb:建议的策略
#!/bin/bash
BACKUP="/backup"
BACKUP_TIME=`date +%F_%T`
mysqldump -uroot -ppassword -A -F -E -R --single-transaction --master-data=1 --flush-privileges > $BACKUP/fullbak_$BACKUP_TIME.sql
MyISAM:建议备份策略
#!/bin/bash
BACKUP="/backup"
BACKUP_TIME=`date +%F_%T`
mysqldump -uroot -ppassword -A -F -E -R -x --master-data=1 --flush-privileges >${BACKUP} /fullbak_${BACKUP_TIME}.sql