mysql备份与恢复

1)冷备：停库、停服务来备份
即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

2)温备：不停库、不停服务来备份，会(锁表)阻止用户的写入 
即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作 

3)热备(建议)：不停库、不停服务来备份，也不会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读写操作均不是受影响

#1、向所有表施加读锁
FLUSH TABLES WITH READ LOCK;  

#2、备份数据文件
mkdir /egon_bak
cp -a /var/lib/mysql/* /egon_bak

# 数据丢失
rm -rf /var/lib/mysql/*

# 恢复数据
cp -a /egon_bak/* /var/lib/mysql

# 重启服务
systemctl restart mysql

#==========语法

mysqldump  -h 服务器  -u用户名  -p密码  选项与参数 > 备份文件.sql


===选项与参数

1、-A/--all-databases          所有库

2、-B/--databases bbs db1 db2  多个数据库

3、db1                          数据库名

4、db1 t1 t2              db1数据库的表t1、t2

5、-F                           备份的同时刷新binlog

6、-R 备份存储过程和函数数据(如果开发写了函数和存储过程，就备，没写就不备)

7、--triggers 备份触发器数据(现在都是开发写触发器)

8、-E/--events 备份事件调度器

9、-d 仅表结构

10、-t 仅数据

11、--master-data=1  备份文件中 change master语句是没有注释的，默认为1

用于已经制作好了主从，现在想扩展一个从库的时候使用

如此备份，扩展添加从库时导入备份文件后

便不需要再加mater_pos了

change matser to

master_host='10.0.0.111'

master_user='rep'

master_password=123

master_log_pos=120

master_log_file='master-bin.000001'

                              
12、--master-data=2  备份文件中 change master语句是被注释的  
  
13、--lock-all-tables 备份过程中所有表从头锁到尾，简单粗暴

在mysqldump导出的整个过程中以read方式锁住数据库中所有表，类似 flush tables with read lock 的全局锁)，

这是一个全局读锁，只允许读不允许写，以此保证数据一致性。

比如当前数据库有如下schema：

information_schema(不会导出)

mysql

performance_schema(不会导出)

sys(不会导出)

test

test1

test2

那么我们在使用mysqldump导出时：

mysqldump --lock-all-tables --set-gtid-purged=on -AER > test.sql

指定--lock-all-tables参数，那么从一开始就对整个mysql实例加global read lock锁。

这整个全局读锁会一直持续到导出结束。

所以在这个过程中，数据库实际严格处于read only状态。

所以导出的数据库在数据一致性上是被严格保证的，也就是数据是一致性的。

由于这个参数会将数据库置于read only状态(也相当于不可使用状态)，所以默认不加该参数。

这相当于脱机备份的感觉，所以生产数据库的备份策略上，也很少使用该参数。


该参数本身默认off,但使用该参数的话，也会自动将 --single-transaction 及 --lock-tables 参数置于 off 状态,他们是互斥的。


对于支持事务的表例如InnoDB和BDB，推荐使用--single-transaction选项，因为它根本不需要锁定表


14、--single-transaction： 快照备份 (搭配--master-data可以做到热备)

保证各个表具有数据一致性快照。

指定 --single-transaction 参数，那么导出过程中只能保证每个表的数据一致性(利用多版本特性实现，目前只能针对InnoDB事务表)。

比如有一个大表，mysqldump对该表的导出需要1分钟，那么在这1分钟的过程中，该表时可以被正常访问的。

(正常访问包括增删改查，但是alter table等对表结构发生更改的语句要被挂起。)

mysqldump能够保证从开始对该表进行导出，一直到对该表的导出结束，该表的数据都是开始的一致性数据快照状态。

所以该参数明显不能保证各个表之间的数据一致性(特别是外键约束的父表和子表之间)。

但是该参数能够让数据库处于可使用(就是应用感觉数据库可用)状态，相当于联机备份，所以被经常使用。

该参数默认off。


15、--lock-tables：如果是备份所有库，那么备份到某个库时只锁某个库，其他库可写，而--lock-all-tables是从始自终都全都锁定


保证各个schema具有数据一致性快照。

指定 --lock-tables 参数，那么在导出过程中能够保证各个schema的数据一致性。

比如导出 cms 库(该库有155张表)时：

mysqldump --lock-tables --set-gtid-purged=off -ER -B cms>test.sql

从命令开始，就对 cms 库的155张表加类似 lock table xxx read 的读锁。

这会导致在导出整个cms库的过程中，cms库实际上整体处于read only状态。

但是如果我们指定如下命令：

mysqldump --lock-tables --set-gtid-purged=on -AER >test.sql

来导出全部mysql库，那么当导出cms库的过程中，其他 schema 实际上是可以被正常访问的。

这个正常访问就是可以接受所有合法的sql语句。

所以该参数只能保证各个schema自己的数据一致性快照。

该参数默认on。


#==========完整语句  
mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction > /backup/full.sql


#====文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道

mysqldump -uroot -p1234563 -A -E -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz


#====导出时压缩了，导入时需要解压，可以使用zcat命令，很方便

zcat /backup/full$(date +%F).sql.gz | mysql -uroot -p123

===> 1、grep过滤

===> 2、检查事件：依据End_log_pos的提示，来确定某一个事件的起始位置与结束位置
mysql> show binlog events in 'mybinlog.000001'; 
如果事件很多，可以分段查看
mysql> show binlog events in 'mybinlog.000001' limit 0,30; 
mysql> show binlog events in 'mybinlog.000001' limit 30,30; 
mysql> show binlog events in 'mybinlog.000001' limit 60,30; 

===> 3、利用mysqlbinlog命令
生产中很多库，只有一个库的表被删除，我不可能把所有的库都导出来筛选，因为那样子binlog内容很多，辨别复杂度高，我们可以利用

[root@egon mysql]# mysqlbinlog -d db1 --start-position=123 --stop-position=154 mybinlog.000001 --base64-output=decode-rows -vvv | grep -v 'SET'

参数解释：
1)-d 参数接库名
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
2)--base64-output  显示模式
3)-vvv   显示详细信息

# 1、先打开binlog日志
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog
binlog_format='row' #(row,statement,mixed)    
binlog_rows_query_log_events=on
max_binlog_size=100M

# 2、登录数据库，插入测试数据
mysql> create database db3;
mysql> use db3;
mysql> create table t1(id int);
mysql> insert t1 values(1),(2),(3);

# 3、在命令行执行命令，进行全量备份
[root@egon mysql]# mysqldump -uroot -p123456 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz

# 4、在命令行执行命令，刷新binlog，便于日后查找
[root@egon mysql]# mysql -uroot -p123456 -e "flush logs"

# 5、登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog
mysql> use db3;
mysql> insert t1 values(4),(5),(6);

# 模拟数据丢失
mysql> drop database db1;

# 恢复数据
# 1、mysql数据导入时，临时关闭binlog，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;

# 2、先恢复全量
mysql> source /tmp/full.sql

如果是压缩包呢，那就这么做
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pEgon@123

# 3、再恢复增量
导出：注意导出binlog时不要加选项--base64-output
[root@egon mysql]# mysqlbinlog mybinlog.000002 --stop-position=531 > /tmp/last_bin.log
导入
mysql> source /tmp/last_bin.log

# 4、开启二进制日志
mysql> SET sql_log_bin=ON; 

drop database db1;

# 登录数据库，先恢复全量
mysql> set sql_log_bin=0;
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pEgon@123
mysql> select * from db1.s1; -- 查看恢复到了id=28，剩下的去增量里恢复

# 在命令行导出mybinlog.000001中的增量，然后登录库进行恢复
查找位置，发现@1=29即第一列等于29，即id=29的下一个position是10275
mysql> show binlog events in 'mybinlog.000001';  
[root@egon mysql]# mysqlbinlog mybinlog.000001 --start-position=10038  --stop-position=11340 --base64-output=decode-rows -vvv | grep -v 'SET' | less

在命令行中执行导出
[root@egon mysql]# mysqlbinlog mybinlog.000001 --start-position=10275 > /tmp/1.sql

在库内执行导入,发现恢复到了39
mysql> source /tmp/1.sql  -- 最好是在库内恢复，因为sql_log_bin=0，导入操作不会记录
mysql> select * from db1.s1;

# 在命令行导出mybinlog.000002中的增量，然后登录库进行恢复
上面恢复到了id=39，我们接着找id=40的进行恢复，查找位置
发现@1=40的position是432
发现@1=55的position是6464
mysql> show binlog events in 'mybinlog.000002'; 
[root@egon mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=40'
[root@egon mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=55'


导出
[root@egon mysql]# mysqlbinlog mybinlog.000002 --start-position=432 --stop-position=6464> /tmp/2.sql

在库内执行导入,发现恢复到了55
mysql> source /tmp/2.sql  
mysql> select * from db1.s1;

# 开启binlog
mysql> SET sql_log_bin=ON;

可以，但直接使用binlog做全量恢复，成本很高，我们只用起来做增量恢复。

正确的方案是：全备+binlog增量
每天或者每周全备一次，全备之后，那个位置点之前的binlog全都可以删除，不可能一年有上百个binlog的库

# 1、添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 无需重启
echo '- - -' > /sys/class/scsi_host/host0/scan 
echo '- - -' > /sys/class/scsi_host/host1/scan 
echo '- - -' > /sys/class/scsi_host/host2/scan 

# 2、创建逻辑卷
pvcreate /dev/sdb
vgcreate vg1 /dev/sdb
lvcreate -n lv1 -L 5G vg1 

# 3、格式化制作文件系统并挂载
mkfs.xfs /dev/mapper/vg1-lv1  
mkdir /lv1
mount /dev/mapper/vg1-lv1 /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql

# 4、修改mysql配置文件的datadir如下
[root@node1 ~]# rm -rf /var/lib/mysql/*  # 删除原数据
[root@node1 ~]# vim /etc/my.cnf    
[mysqld]
datadir=/var/lib/mysql

# 5、重启MySQL、完成初始化
[root@node1 ~]# systemctl restart mysqld 

# 6、往数据库内插入测试数据
create database db3;
use db3;
create table t1(id int);
insert t1 values(1),(2),(3);

mysql> FLUSH TABLES WITH READ LOCK;     #锁定所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# lvcreate -L 1G -s -n lv1_from_vg1_snap /dev/vg1/lv1   #创建快照卷

mysql> UNLOCK TABLES;  #解锁所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# mkdir /snap1  #创建文件夹
[root@node1 lvm_data]# mount -o nouuid /dev/vg1/lv1_from_vg1_snap /snap1

[root@localhost snap1]# cd /snap1/

[root@localhost snap1]# tar cf /tmp/mysqlback.tar *  

[root@localhost snap1]# umount /snap1/ -l
[root@localhost snap1]# lvremove vg1/lv1_from_vg1_snap

rm -rf /var/lib/mysql/*

# 恢复
tar xf /tmp/mysqlback.tar -C /var/lib/mysql/