MySQL从入门到精通（三）

MySQL数据备份恢复以及执行优化

1. 三种Binlog模式

statement

• 纯粹的记录DML的语句
• update ad_user set username=‘michael.li’ where id=1;
• delete from ad_user where id=1;

# statement
show variables like 'log_bin';
# MySQL会自动生成一个mysql-bin-00001.log
# chown -R mysql:mysql /usr/local/binlog/
# mysql-bin是生成binlog日志的文件名前缀
log_bin=/usr/local/binlog/mysql-bin
binlog_format=statement
# binlog日志切割的大小
max_binlog_size=500m
# binlog过期清理时间
expire_logs_days=3

[mysqld]
server-id=213

mysql> show binlog events in 'mysql-bin.000001';

row（v5.7版本默认是row模式）

• 过去的历史值和现在的新值都展示出来

#修改模式
log_bin=/usr/local/binlog/mysql-bin
binlog_format=row
max_binlog_size=100m
expire_logs_days=3
server-id=8
# row模式的日志查询
update ad_user set username='michael.te' where id=1;
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

查询的结果

### DELETE FROM `michael_admin`.`ad_user`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='coding' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='123456' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### UPDATE `michael_admin`.`ad_user`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin.huang' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

• 怎么快速找到误操作的语句
  row模式的定位

mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-17 22:10' --stop-datetime='2020-06-17 22:25' -vv mysql-bin.000002

mysqlbinlog也可以查询statement模式的数据，得到时间区间

如果进行大批量的数据操作，这个时候数据库是安全的，不让MySQL记录，可以临时将binlog记录

mysql> set sql_log_bin=0; #临时关闭binlog（当前连接进程有效）

mixed

# 混合模式
mixed：95%都是以statement形式出现在binlog中，sql中有函数的时候会用row模式记录

总结

• statement模式只记录DML语句，日志文件是三种模式中最小的，但是恢复数据相对比较麻烦；
• row模式通过一种特殊的格式，将修改/删除前的数据一并展示出，日志问价是三种模式中最大的，但是row模式针对性的恢复数据会比较容易
• mixed可以理解为statement方式，只有sql里有函数出现才会触发row模式

2. 数据备份

1）备份的场景和分析

• 全量备份
• 差异备份

• 增量备份

• 时间点备份

备份类型

• 热备：在数据库正常运行的过程中进行备份， 在热备期间，数据库的读写操作均可以正常进行；热备是不能通过简单的copy命令，需要专门的备份工具，在mysql中MyISAM引擎是不支持热备的，InnoDB引擎支持热备
• 温备：比热备份稍微弱一些，在备份期间只能进行读操作
• 冷备：在备份过程中读写均不可以操作，操作最简单，数据的可靠性最高，速度也是最快的，myql中可以通过复制数据的文件来进行冷备
• 物理备份：copy文件
• 逻辑备份

常用备份工具

• mysqldump
• Percona提供的xtrabackup

2）数据备份过程中数据一致性理解

比如使用mysqldump导出数据，一张表一张表地导出数据，每个表的导出数据受限于表的大小，表越大导出速度越慢；数据涉及关联多个表的时候，在导出过程中有数据的更改的话，导出的整体的数据会存在一致性问题。

解决方法：基于某个时间导出数据时制作一个快照，导出数都是基于快照时间点处理的

# mysqldump --help
# --master-data 0(不记录position) 1(记录position位置) 2(记录position位置并注释该条) 
# routines 存储过程
# triggers 触发器
# events 事件
# single-transaction 重要:mysql导出时创建快照的启动命令
--single-transaction 
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.

#--databases michael_admin 指定导出库的名称，可以一次导出多个库 用空格分隔
# --ignore-table=michael_admin.ad_user_role --ignore-table=michael_admin.ad_user  指定不需要导出的表，可是库名.表明
# 基于innodb引擎
mysqldump -uroot -pmichael2020 -h127.0.0.1 --master-data=2 --routines --triggers --events --single-transaction --databases michael_admin --ignore-table=michael_admin.ad_user_role > mydb.sql


#基于MyISAM引擎  --lock-tables进行全表的锁表导出
# --all-dababases  / --lock-all-tables 锁定所有库的所有表
mysqldump -uroot -pmichael2020 -h127.0.0.1 --master-data=2 --routines --triggers --events --lock-tables --databases michael_admin --ignore-table=michael_admin.ad_user_role > mydb.sql

3.数据恢复的设计

恢复的前提是要有备份

1）备份设计

1. 全量备份的方式

• 定时做全量备份
• 使用crontab结合mysqldump来做定时备份
• 增量备份（数据量单日激增比较大的情况下）

2. 增量时间点补偿

• 如何补偿：全量备份都是在一个固定的时间点进行备份，有丢失备份周期内数据的风险
• 考虑修改的变化：update、delete
• 借助我们的binlog来进行时间点补偿

# 第一步，先开启binlog，过期时间要大于备份周期
# 第二步，先做全量备份,可以定时处理
crontab -e
18 20 * * * mysqldump -uroot -pmichael2020 --databases michael_admin > /usr/local/bak/bak.sql
16 20 * * * rm -rf /usr/local/bak/bak.sql(delete former backup data)


# 事故场景：完成备份后，有新增数据后 drop掉michael_admin库
# 数据恢复过程：先用备份文件恢复备份时间点所有数据bak.sql，然后根据备份数据库的position作为binlog日志的恢复数据开启节点再 binlog中恢复剩余的数据

# 1.获取bak.sql结束的position 9623  和binlog中的增量数据的结束position 9858
mysql > show binlog events in 'mysql-bin.000002'
# 2.导出binlog中的增量数据
mysqlbinlog --start-position=9623 --stop-position=9858 mysql-bin.000002 > ist.sql
# 3.增量数据的恢复根据需要把当前binlog记录关掉
mysql> set sql_log_bin=0;
# 4.执行脚本恢复数据
mysql> source bak.sql
mysql> source ist.sql


# 事故场景：对莫一条数据进行了误操作
# row模式下可以直接手工处理
mysqlbinlog --base64-output=decode-rows --start-position=9623 --stop-position=9858  -vv mysql-bin.000002

注意：在数据恢复完之后，要将binlog中记录的异常操作的数据库操作命令（一般指的是导致数据丢失的命令）防止后期恢复过程中再次导致数据丢失

4.MySQL的查询优化

数据查询优化的过程：

• SQL的优化：索引的命中优化
• 数据库表结构来进行数据的整体优化
• 提升系统配置
• 升级硬件设施

# 执行计划
explain select * from ad_user where username='coding';
索引命中规则
# 组合索引一定是最左匹配原则
# 如果你在表上建立了很多组合索引，索引文件膨胀，修改、删除、更新会比较慢

未建立索引的type

建立索引的type

索引命中后的类型(从上到下索引的效果越来好)：

• ALL （未命中索引）
• index
• range
• ref
• eq_ref
• const
• system

适合建立的列

• 频繁查询的列
• 有外键关联的列

不适合建立的列

• 值变化少的列(枚举一般不适合建立索引)
• 频繁更新的列
• 表的记录比较少：比如配置表

如何保证数据库死而不僵

• show processlist;
• kill id;

想让系统运行比较顺畅，需要持续优化

• 打开慢查询日志slow_log
• 设置查询超时时间，超过后进行记录并即使优化和更新