Linux:MYSQL（十一）LVM，mysqldump备份

备份和恢复

• 为什么要备份
  灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

• 备份注意要点
  能容忍最多丢失多少数据
  恢复数据需要在多长时间内完成
  需要恢复哪些数据

• 还原要点
  做还原测试，用于测试备份的可用性
  还原演练

备份和恢复

• 备份时需要考虑的因素
  温备的持锁多久
  备份产生的负载
  备份过程的时长
  恢复过程的时长

• 备份什么
  数据
  二进制日志、InnoDB的事务日志
  程序代码（存储过程、函数、触发器、事件调度器）
  服务器的配置文件

• 物理和逻辑备份

  • 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
  • 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

• 备份类型：完全备份，增量备份，差异备份

  • 完全备份：整个数据集
  • 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
  • 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘

• 冷、温、热备份
  • 冷备：读写操作均不可进行
  • 温备：读操作可执行；但写操作不可执行
  • 热备：读写操作均可执行
    MyISAM：温备，不支持热备
    InnoDB：都支持

备份工具

• cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份

• LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份

• mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份

• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现

• mysqlbackup：热备份， MySQL Enterprise Edition组件

• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

cp tar 备份

全部复制：

1.打包备份
tar Jcvf /data/mysql.bak.tar.xz /var/lib/mysql/
2.停服务
systemctl stop mariadb
3.删除数据
cd /var/lib/mysql/
rm -rf *
4.拷贝数据
scp -rp /data/mysql.bak.tar.xz 192.168.32.17:/data
5.解压缩
tar xf /data/mysql.bak.tar.xz
6.移动到数据库文件位置 (记得同时复制配置文件，以免不一样吗，，，，，，，， ）
mv * /var/lib/mysql
7.启动服务
systemctl start mariadb

##LVM备份
基于LVM的备份
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志

一、准备工作

1.创建逻辑卷
fdisk /dev/sda
p-n-t(8e)
partprobe
pvcreate /dev/sda6
vgcreate vg_data /dev/sda6
lvcreate -n mysqldata -L 2G vg_data
lvcreate -n binlog -L 4G vg_data
mkfs.xfs /dev/vg_data/mysqldata
mkfs.xfs /dev/vg_data/binlog
2.挂载
mkdir /vg_data/{binlong,mysqldata} -pv
mount /dev/vg_data/binlog /vg_data/binlog/
mount /dev/vg_data/mysqldata /vg_data/mysqldata/
3.迁移,修改配置文件路径
vim /etc/my.cnf
[mysqld]
datadir=/vg_data/mysqldata
log_bin=/vg_data/binlog/mysql-bin
chown -R mysql.mysql /vg_data/
4.重启数据库
systemctl restart mariadb


reset master;可清空二进制日志
show master logs;查看

二、数据库加锁，开始快照

mysql -e 'flush tables with read lock'
mysql -e 'show master logs' > /root/bin.log
cat bin.log 记住位置7697

lvcreate -s -n snap_mysql -L 1G /dev/vg_data/mysqldata -p r (只读属性）
mysql -e 'unlick tables'

三、把快照内容拷贝出来

lvs查看快照，之后挂载
mount /dev/vg_data/snap_mysql /mnt 不让挂载，因为UUID一样
mount -o nuuid,norecovery /dev/vg_data/snap_mysql /mnt
cp -av /mnt/* /data/test  复制内容

lvremove /dev/vg_data/snap_mysql 删除快照

四、用二进制日志，把快照之后的修改操作还原到最新

mysql -e 'show binary logs' 查看当前编号
cat /root/bin.log 根据此日志位置7697
mysqlbinlog --start-position=7696 mysql-bin.000001 > /root/binlog.sql
mysqlbinlog mysql-bin.000002 >> /root/binlog.sql
进入mysql
>set sql_log_bin=off;
>source /root/binlog.sql;
>set sql_log_bin-on;

备份和恢复

• 逻辑备份工具：mysqldump, mydumper, phpMyAdmin

• Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

• mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
  mysqldump [OPTIONS] database [tables]
  mysqldump [OPTIONS] –B DB1 [DB2 DB3…]
  mysqldump [OPTIONS] –A [OPTIONS]

• mysqldump参考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump hellodb > /data/hellodb_bak.sql
可直接导出为sql语句文件

mysql -e 'create database hi'
mysql hi < /data/hellodb_bak.sql
导入需重新建数据库
注意：这样会出现的问题：原数据库字符集不知道，原数据库名，其他定义没有备份，还原有可能带来麻烦,所以需带选项备份

mysqldump常见选项：

-A， --all-databases 备份所有数据库，含create database

mysqldump -A > /data/all`data +%F`.sql
全部备份下来并添加日期

-B , --databases db_name… 指定备份的数据库，包括create database语句

mysqldump -uroot -pdushan -B hellodb > /data/bak_B.sql
挑选helloddb数据库备份，备份包括名称定义等

mysqldump -uroot -pdushan -B hellodb |gzip > /data/bak_B.sql.gz
mysqldump -uroot -pdushan -B hellodb |xz > /data/bak_B.sql.xz
直接备份并压缩

unxz bak_B.sql.xz
xz -d bak_B.sql.xz
解压缩

-E, --events：备份相关的所有event scheduler

-R, --routines：备份所有存储过程和自定义函数

–triggers：备份表相关触发器，默认启用,用–skip-triggers，不备份触发器

–default-character-set=utf8 指定字符集

–master-data[=#]： 此选项须启用二进制日志
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句
2：记录为注释的CHANGE MASTER TO语句
此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启–single-transaction）

mysqldum -A --master-data=2 >all.sql
记录时间点，主从复制时候用2，记录时间点之前加注释，1则不加

-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single-transaction或-x，–master-data 一起使用实现，此时只刷新一次日志

–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 使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码

-q, --quick 不缓存查询，直接输出，加快备份速度

MyISAM引擎的备份选项：

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：

-x,–lock-all-tables：加全局读锁，锁定所有库的所有表，同时加–single-transaction或–lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库

-l,–lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致

注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

##InnoDB备份选项
支持热备，可用温备但不建议用

–single-transaction

此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务

此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥

备份大型表时，建议将–single-transaction选项和–quick结合一起使用

分库备份数据库脚本

查看数据库,根据库名写脚本：
mysql -e 'show databases' |cat 
生成数据库名：
mysql -e 'show databases'| grep -Ev '^Database|info|performanc

一、while…do备份数据库

mysql -e 'show databases'| grep -Ev '^Database|info|performanc'|while read dbname;do mysqldump -B $dbname | gzip > /data/${dbname}_bak`date +%F` .gz;done

二、sed备份数据库

思路：先挑出数据库，之后用sed///替换并分组，把/换成@

mysql -e 'show databases' | grep -Ev '^Database|info| performanc' |sed -r 's@(.*)@mysqldump -B \1| gzip > /data/\1_bak`date +F%`.gz@' |bash

三、for…in备份数据库

for db in `mysql -e 'show databases'| grep -Ev '^Database|info|performanc'`;do mysqldump -B $db | gzip > /data/&{db}_bak`date +%F.gz;done

！！若想脚本直接执行传给bash即可

cat f1.sh |bash

生产环境实战备份策略

• InnoDB建议备份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

• MyISAM建议备份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

实战练习

一、还原初始状态

1.修改一下二进制日志位置,启动二进制
[root@dushan7 ~]vim /etc/my.cnf
[mysqld]
log-bin=/data/binlog/mysql-bin 
datadir=/var/lib/mysql
2.创建文件夹，注意更改权限
[root@dushan7 ~]#mkdir /data/binlog
[root@dushan7 ~]#chown mysql.mysql /data/binlog/

3.重启服务
[root@dushan7 ~]#systemctl restart mariadb

4.进入mysql查看一下二进制文件，没用的可以删除掉
mysql> show master logs;
[root@dushan7 ~]#ll /var/lib/mysql/ 
[root@dushan7 ~]#cd /var/bin/mysql/
[root@dushan7 ~]#rm -f *bin*
5.重启服务
[root@dushan7 ~]#systemctl restart mariadb

二、备份还原
1.开始备份

mysqldump -A -F --single-transaction -- master-data=2 --hex-blob |gzip > /data/all_`date +%F`.sql.gz

2.修改数据库表

mysql>insert teachers values (1,'a',30,'F');
mysql>insert teachers values (6,'b',49,'F');

3.模拟破坏数据库表

systemctl stop mariadb
rm -rf /var/lib/mysql/*

4.还原备份状态（还原时进制用户访问数据库）iptables

开启后，进入mysql,关闭二进制不要退，导入备份的文件
systemctl start mariadb
gzip -d all.sql.gz 
mysql>set sql_log_bin=off;
mysql>source /data/all.sql;

5.还原到最新位置状态

查看一下备份的数据位置，然后制作二进制进文件，把后面的日志都导进来
grep "CHANGE MASTER" /data/all.sql 查看 position mysql-bin.00002 245
cd /data/binlog/
mysqlbinlog --start-position=245 mysql-bin.000002 > incr.sql
mysqlbinlog --start-position=245 mysql-bin.000003 >> incr.sql 
注意是追加！！！！

less incr.sql 可查看一下下

进入先关闭二进制，导入二进制文件，还原设置,查看一下
mysql>set sql_log_bin=off
mysql>source /data/binlog/incr.sql
mysql>set sql_log_bin=on
mysql>select * from teachers;

6.开放用户访问数据库

模拟场景2：周日做的备份，周一上午10点被删除，10点10分发现问题，开始修复

1.周日做的备
mysqldum -A -F --single-transaction --smaster-data=2 |gzip >/data/all.sql.gz

2.修改数据库

4.删除表10:00
drop table testlog

5.修改数据库 10:00-10:10

6.发现问题，上锁开始分析
mysql>flush tables with read lock;
（接着配置防火墙策略，进制用户访问）

7.开始还原备份时状态
停服务
systemctl stop mariadb
删除之前库
rm-rf /var/lib/mysql/*
启服务
systemctl start mariadb
gzip -d /data/all.sql.gz
mysql
>set sql_log_bin=off
>source /data/all.sql;

8.分析二进制日志，找到drop table指令
grep -i "change master" /data/all.sql 245 mysql-bin.000002
mysqlbinlog --start-position=245 mysql-bin.000002 > /data/incr.sql

vim /data/incr.sql
找到drop table指令，注释此指令

mysqlbinlog --start-position=245 mysql-bin.000003>> /data/incr.sql
mysqlbinlog --start-position=245 mysql-bin.000004>> /data/incr.sql

9.还原到最新状态
mysql
>set sql_log_bin=off;
>source /data/incr.sql;
>set sql_log_bin=on;

10.复查数据完整性

11.开放客服访问