MySQL 学习笔记（四）--复制、MHA切换、binlog解析工具

1.复制模式

（1）MySQL 复制模式默认是异步的。主库将事务Binlog事件写入到Binlog文件中，此时主库只会通知Dump线程发送这些新的Binlog，然后主库就会继续处理提交操作，而此时不会保证这些Binlog传到任何一个从库上。
（2）半同步复制必须是主库和从库两端都开启时才可以。
（3）从库节点只有在接收到某一事务的所有Binlog,将其写入并Flush 到 Relay Log 文件之后，才会通知对应主库上的等待线程。在等待过程中，如果所有的从节点都超过了配置的超时时间，主库会自动转换为异步复制，当至少一个半同步的从节点赶上来时，主库会自动转换为半同步方式的复制。
（4）全同步复制，当主库提交事务之后，所有的从库节点必须都收到、Apply并且提交这些事务，然后主库线程才能继续做后续操作。

2.Binlog 与 Redo log的区别

（1）binlog是MySQL Server层记录的日志， redo log是InnoDB存储引擎层的日志。

（2）MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。

（3）binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。

（4）在两阶段提交中所处的位置、功能不同。MySQL（开启Binlog）内部会自动将普通事务当作一个XA事务来处理，在提交事务的过程中，MySQL会自动为每个事务分配一个唯一的XID，这个XID会被记录到Binlog 和 InnoDB Redo log中。Prepare阶段:告诉InnoDB引擎做Prepare，InnoDB更新事务状态，并将Redo log刷入磁盘；Commit阶段：先记录Binlog日志，然后告诉InnoDB引擎做Commit。

3.两阶段提交与宕机重启对事务的处理

数据库库宕机重启，事务可能出于以下四种状态：

（1）InnoDB 引擎已经提交（commit）。此时，Binlog中也一定完整地记录了该事务。所有事务是一致的，无需处理。

（2）InnoDB已完成了Prepare阶段，Binlog中已经有了该事务的Events，但是InnoDB引擎未提交。需要通知InnoDB引擎提交这些事务。

（3）InnoDB已完成了Prepare阶段，Binlog中没有该事务的Events。因为Binlog没有记录，需要通知InnoDB回滚这些事务。

（4）InnoDB还未完成Prepare阶段，Binlog中没有该事务的Events。需要通知InnoDB回滚这些事务。

4.事务的提交过程

此时SQL已经成功执行了，已经产生了语句的redo和undo内存日志，已经进入了事务commit步骤。事务提交的整个过程如下：

（1）记录Binlog是在InnoDB引擎Prepare（即Redo Log写入磁盘）之后.

（2）每个阶段都进行一次fsync操作才能保证上下两层数据的一致性。阶段1的fsync由参数innodb_flush_log_at_trx_commit=1控制，阶段2的fsync由参数sync_binlog=1控制，俗称“双1”，是保证crash-safe的根本。

5.MHA在线自动切换【Scheduled(Online) Master Switch】

Program flows for the scheduled master switch is slightly different from the master failover. For example, you do not need to power off the master server, but you need to make sure that write queries are not executed on the master. By settingmaster_ip_online_change_script, you can control how to disallow write traffics on the current master (i.e. dropping writable users, setting read_only=1, etc) before executing FLUSH TABLES WITH READ LOCK, and how to allow write traffics on the new master.

迁移命令如下

/usr/local/bin/masterha_master_switch --conf=/etc/app1.cnf --master_state=alive --orig_master_is_new_slave --running_updates_limit=1000 --interactive=0

主要参数

参数	作用
--master_state	强制参数. 可选有： "dead" or "alive". 如果设置为 alive，将执行在线切主操作。
--orig_master_is_new_slave	After master switch completes, the previous master will run as a slave of the new master. By default, it's disabled (the previous master will not join new replication environments). If you use this option, you need to set repl_password parameter in the config file because current master does not know the replication password for the new master. 将老主切换成新主的slave节点。如果不指定这个参数，老主将不加入新的集群环境。
--new_master_host	可选参数。指定新的主节点。如果不指定，程序自动选举。
--running_updates_limit	单位为seconds。默认值为1 second。当老主当前写操作执行超过N秒，或者任何一台slave的 slaves behind master 超过N秒，切换都将终止。
--interactive	1为交互模式，默认；0为非交互。
--skip_lock_all_tables	可选参数。When doing master switch, MHA runs FLUSH TABLES WITH READ LOCK on a orig master to make sure updates are really stopped. But FLUSH TABLES WITH READ LOCK is very expensive and if you can make sure that no updates are coming to the orig master (by killing all clients at master_ip_online_change_script etc), you may want to avoid to lock tables by using this argument，切换过程中，老主将被施加 FLUSH TABLES WITH READ LOCK ，这是个昂贵的操作，如果你可以确定没有写操作，可以指定这个参数。
--remove_orig_master_conf	可选参数。When this option is set, if master switch succeeds correctly, MHA Manager automatically removes the section of the dead master from the configuration file. By default, the configuration file is not modified at all.

例如我们在本公司经常使用的切换命令如下:

masterha_master_switch --conf=/etc/mha配置文件 --master_state=alive --new_master_host=新主ServerIP --new_master_port=端口号 --orig_master_is_new_slave --running_updates_limit=30

6.MHA Scheduled(Online) Master Switch的限制条件

Online master switch starts only when all of the following conditions are met.

IO threads on all slaves are running
SQL threads on all slaves are running
Seconds_Behind_Master on all slaves are less or equal than --running_updates_limit seconds
On master, none of update queries take more than --running_updates_limit seconds in the show processlist output

The reasons of these restrictions are for safety reasons, and to switch to the new master as quickly as possible.

7.MHA故障切换和在线切换的代码解析

可以参考这个网址:http://blog.chinaunix.net/uid-20726500-id-5700631.html

8.查看存储过程定义的权限

在MySQL5.7或之前版本，可以通过授予用户查询mysql.proc来间接实现查看存储过程定义的权限，在MySQL 8.0 可以通过授予用ALTER ROUTINE的权限来间接实现查看存储过程定义的权限（如果存储过程有指定DEFINER，此方法无效;即使授予了ALTER ROUTINE，那么其他用户依然无法查看这些存储过程定义),，两者都有一个问题，那就是会放大权限.

5.7 版本

 grant select on mysql.proc TO 'XXXX'@'%';

缺点: 能看到所有数据库存储过程的定义（不仅仅是某个某个存储过程，或某个数据库的存储过程的定义）

8.0 版本:

grant alter routine on test.XXXX to 'ut01'@'%';

缺点:1.授予权限的用户不仅可以查看存储过程定义，而且可以删除这个存储过程;2.如果用户指定了DEFINER,那么其他用户无法查看这些存储过程定义.

关于存储的其他权限

alter routine---修改与删除存储过程/函数

create routine--创建存储过程/函数

execute--调用存储过程/函数

9.Flush Statement

FLUSH LOGS, FLUSH BINARY LOGS, FLUSH TABLES WITH READ LOCK (with or without a table list), and FLUSH TABLES tbl_name ... FOR EXPORT are not written to the binary log in any case because they would cause problems if replicated to a replica.

The FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are written to the binary log and thus replicated to replicas. This is not normally a problem because these statements do not modify table data.To suppress(阻止\禁止) logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

10.binlog_row_image三种设置

名词解析:

before image：前镜像，即数据库表中修改前的内容。
after image：后镜像，即数据库表中修改后的内容。

binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB

FULL: Log all columns in both the before image and the after image.
binlog日志记录所有前镜像和后镜像。

MINIMAL: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.
binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。

For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

如果没有唯一识别列(唯一索引列、主键列)，例如只有普通key，那么MINIMAL格式的前镜像也会记录所有所有列，但后镜像依然只记录修改列。
noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.
binlog记录所有的列，就像full格式一样。但对于BLOB或TEXT格式的列，如果他不是唯一识别列(唯一索引列、主键列)，或者没有修改，那就不记录。