MySQL 八、数据库备份和还原
1、二进制日志相关配置
1）查看使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
ex: show master logs;
show binary logs;
这两个命令是一样的
物理大小
2）查看使用中的二进制日志文件
SHOW MASTER STATUS;
表示当前二进制数据库，处于什么位置（下图中当前处于245位置）
当对表进行修改后，可看到位置发生了变化
update students set name='Shi Banyu' where stuid=1;
show master status;
3）查看查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
ex: show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
show binlog events in 'a.000002';
show binlog events in 'a.000002' from 245;
2、二进制日志客户端命令工具：mysqlbinlog
1）生成新日志文件: flush logs
flush logsl 触发生成新的日志
生成新的日志a.000003,
新的事件将记录到这个新的日志文件里
2）窥探二进制文件的内容
mysqlbinlog [OPTIONS] log_file…
ex： mysqlbinlog /data/mylog/a.000003
对表做一次修改
再查看二进制日志文件，可看到：
at 245及之后的位置，记录了数据库发生的改变。
因此，也就可以利用二进制文件，来还原数据库
3）一个还原的小实验
将二进制文件导出到文件
mysqlbinlog /data/mylog/a.000003 > bin.sql
对表再次进行修改：
update students set name='Shi Po Tiandi' where stuid=2;
select * from students;
将修改之前的二进制文件内容，再导入到数据库中（猜猜会发生什么？）
mysql < bin.sql
表又复原到修改之前的状态了
可以看到
4）mysqlbinlog客户端命令参数
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
示例：
mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;
ex:
mysqlbinlog /data/mylog/a.000003 --start-position=316 --stop-position=431
5）删除二进制日志文件
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
删除指定日志文件之前的文件
purge binary logs to 'a.000003'; （删除到3,3之前的都删掉）
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #];
日志文件从#开始记数，默认从1开始，一般是master第一次启动时执行，MariaDB10.1.6开始支持TO #
RESET MASTER; 彻底删除，重新计数
二、备份和恢复
1）全备份、增量备份示意（每周全备、每日增量备份）
全 增 增 增 增 增 增
+--------+-------+---------+------+-------+-------+
日 一 二 三 四 五 六
2）默认mysql备份相关配置
分库、分表
innodb_file_per_table=ON （建议默认配置）
2、备份工具
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统管理工具进行备份
mysqlhotcopy：几乎冷备；（仅适用于MyISAM存储引擎）
1）mysqldump备份工具的使用
mysqldump是一个客户端命令，通过mysql协议连接至mysqld服务器进行备份：
mysqldump [OPTIONS] database [tables] 备份特定数据库的某张单表
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] 备份指定的若干个数据库
mysqldump [OPTIONS] –A [OPTIONS] 备份所有数据库
2）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 不缓存查询，直接输出，加快备份速度
实验一 ：冷备份数据库，并还原
思路：停服务，拷贝出数据库文件，进行冷备份
备份
1）查看当前的数据库，共有5个数据库
mysql -e 'show databases'
2）停服务
systemctl stop mariadb
3）备份文件：打包压缩拷贝出来 （对/var/lib/mysql整个文件夹，即所有数据全都备份）
tar Jcvf /data/all.tar.xz /var/lib/mysql/
注：1、生产环境应将备份文件与数据库服务器分开存放
2、将备份文件传输到非本 地服务器保存，以备还原时使用
4）模拟mysql数据库出现故障
rm -rf /var/lib/mysql/* （仅删除/var/lib/mysql/目录下的所有文件）
还原
5）用备份的文件读数据库进行还原
tar xvf /data/all.tar.xz
mv var/lib/mysql/* /var/lib/mysql/
查看文件都已复原回去
6）重启服务
systemctl start mariadb
7）测试还原效果
实验三、基于LVM快照的备份（配合tar、cp等工具）
前提：
要实现基于LVM快照的备份，需要将数据库放到快照的空间，才能实现这种备份
1、将数据库数据迁移到逻辑卷
2、
备份
1）创建逻辑卷
fdisk /dev/sda
pvcreate /dev/sda6 创建pv
pvdisplay 确认
vgcreate vg0 dev/sda6 创建vg，将该pv加到vg中
vgdisplay 确认
lvcreate -L 5G -n lv_mysql vg0 创建lv.存放mysql数据
lvcreate -L 3G -n lv_binlog vg0 创建lv.存放二进制日志文件
lvs
mkfs.xfs /dev/vg0/lv_mysql 格式化文件系统
mkfs.xfs /dev/vg0/lv_binlog
mkdir /data/{mysql,binlog} 创建两个挂载点
mount /dev/vg0/lv_mysql /data/mysql/ 挂载
mount /dev/vg0/lv_binlog /data/binlog/
或需要永久挂载，修改/etc/fstab
vim /etc/fstab
添加两行
UUID=4615d276-95f9-4d03-a337-9438e9fb89d4 /data/mysql xfs default 0 0
UUID=9ab9b41f-1315-490a-b573-3144807f7313 /data/binlog xfs default 0 0
mount -a
2）修改目录权限
chown -R mysql: /data/mysql
chown -R mysql: /data/binlog
3）修改配置文件
按照规划将二进制日志和数据库文件分别存放于这两个目录
vim /etc/my.cnf
4）重启服务
systemctl restart mariadb
5）对数据库添加读锁，防止写入数据的操作
备份过程中暂停用户的读写，因此要先对数据库添加读锁，暂时禁止写的操作
MariaDB[(none)]> flush tables with read lock;
flush logs; 刷新日志，让生成新的日志
记录当前的二进制日志：mysql-bin.000004 245
注意：锁定，然后做快照再解锁，这个过程要快速，不能让用户一直不能访问，达到近乎热备的效果
6）对数据库中的某张表进行修改
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> show tables;
6）创建lvm快照（相当于对数据库做了备份）
lvcreate -L 1G -n lv_mysql_snap -s -p r /dev/vg0/lv_mysql
-s ：快照
-p r：只读
7）对数据库解锁（恢复让用户正常访问）
MariaDB [(none)]> unlock tables; 解锁
测试下解锁后能否正常写入
create database dbx1;
show databases;
8）挂载逻辑卷快照
要导出快照里的备份，必须先能够访问快照的内容，因此对快照进行临时挂载
mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt
9）拷贝出逻辑卷快照里的文件,进行备份
cp -a /mnt/* /backup/
10）删除快照，否则影响用户访问数据库性能
umount /mnt 取消快照挂载
lvremove /dev/vg0/lv_mysql_snap 删除快照
再创建两个数据库，这是删除快照后创建的
MariaDB [(none)]> create database dbx2;
MariaDB [(none)]> create database dbx3;
模拟数据库损坏
rm -rf /data/mysql/*
还原
1）停止服务
systemctl stop mariadb
2） 拷贝备份文件至数据目录
cp -av /backup/* /data/mysql/
3）启动服务
systemctl start mariadb
此时可看到，数据库还原至快照前状态
但是删除快照后创建的两个数据库dbx2,dbx3已经丢失了。
如果想恢复，可用二进制日志继续恢复:
5）添加读锁
flush tables with read lock; 添加读锁，暂时保持数据不能修改
（记录当前的二进制日志：mysql-bin.000004 245）
当前二进制已变为：mysql-bin.000005 | 245
6）将二进制日志导出，用以修复数据库
因为mysql-bin.000004 和mysql-bin.000005是快照之后，记录了最近发生的变化，因此将其导出，再利用它们修复数据库
cd /data/binlog （必须在此目录下）
mysqlbinlog --start-position=245 mysql-bin.000004 > /backup/bin.sql
mysqlbinlog mysql-bin.000005 >> /backup/bin.sql
7）确保恢复过程中，没有用户可以读取或写入
vim /etc/my.cnf
[mysqld]
skip_networking （禁止使用网络连接将使用户将无法连接数据库，只有自己可连）
或
iptables –A
重启mysql服务使生效
systemctl restart mariadb
导入二进制文件
mysql < /backup/bin.sql
此时数据库已还原至最新状态
8）恢复用户访问数据库
vim /etc/my.cnf
[mysqld]
skip_networking 去掉
或清除防火墙
systemctl restart mariadb
至此，通过lvm快照方式的备份还原就完成了。