MySQL 学习笔记四

作者: 东山絮柳仔

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.                                                                                                                                                                                                                                  |

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格式的列,如果他不是唯一识别列(唯一索引列、主键列),或者没有修改,那就不记录。 11.MySQL binlog解析工具–my2sql

工具介绍和使用方法可参照网址 https://codechina.csdn.net/mirrors/liuhr/my2sql?utm_source=csdn_github_acceleratorhttps://github.com/liuhr/my2sql

下载可在上面的链接网页上,点击下载,如下:

参考学习 1.https://blog.csdn.net/wanbin6470398/article/details/81941586 2.https://www.jianshu.com/p/65eb0526bfc0 3.MHA之masterha_master_switch https://blog.csdn.net/xxj123go/article/details/72828920 4.安全考虑binlog_row_image建议尽量使用FULL https://zhuanlan.zhihu.com/p/42096137

原文创作:东山絮柳仔

原文链接:https://www.cnblogs.com/xuliuzai/p/15333319.html

文章列表

更多推荐

更多
  • MySQL实战宝典-27分布式事务:我们到底要不要使用2PC? 27 分布式事务:我们到底要不要使用 2PC?计。但是我们一直在回避分布式数据库中最令人头疼的问题,那就是分布式事务。 今天,我们就来学习分布式事务的概念,以及如何在海量互联网业务中实现它。 分布式事务概念 事务的概念相信你已经非
  • MySQL实战宝典-11索引出错:请理解CBO的工作原理 11 索引出错:请理解 CBO 的工作原理的使用已经有了一定的了解。 而在实际工作中,我也经常会遇到一些同学提出这样的问题:MySQL 并没有按照自己的预想来选择索引,比如创建了索引但是选择了全表扫描,这肯定是 MySQL 数据库的
  • MySQL实战宝典-21数据库备份:备份文件也要检查! 21 数据库备份:备份文件也要检查!据库可以进行切换(比如 20 讲基于复制或者 InnoDB Cluster 技术的高可用解决方案)。 除了高可用设计外,对架构师来说,还要做好备份架构的设计。因为我们要防范意外情况的发生,比如黑客删
  • MySQL实战宝典-15MySQL复制:最简单也最容易配置出错 15 MySQL 复制:最简单也最容易配置出错结构设计、索引设计。对业务开发的同学来说,掌握这些内容已经能很好地面向业务逻辑进行编码工作了。 但是业务需要上线,所以除了表和索引的结构设计之外,你还要做好高可用的设计。因为在真实的生产环
  • MySQL实战宝典-07表的访问设计:你该选择SQL还是NoSQL? 07 表的访问设计:你该选择 SQL 还是 NoSQL?的访问选型。这样一来,字段类型选择 + 物理存储设计 + 表的访问设计,就完成了表结构设计的所有内容。 前面 6 讲,我演示的都是通过 SQL 的方式对表进行访问,但从 MySQ
  • MySQL实战宝典-17高可用设计:你怎么活用三大架构方案? 17 高可用设计:你怎么活用三大架构方案?是为了铺垫 MySQL 数据库的高可用架构设计。因为复制是高可用的基础,但只用复制同步数据又远远不够,你还要结合自己的业务进行高可用设计。 同时,高可用也不仅仅是数据库的事情,你要从业务的全流
  • MySQL实战宝典-06表压缩:不仅仅是空间压缩 06 表压缩:不仅仅是空间压缩用户表、订单表。既然我们已经掌握了表的逻辑设计,那这一讲就继续学习不同业务表的物理存储设计。 据我观察,很多同学不会在表结构设计之初就考虑存储的设计,只有当业务发展到一定规模才会意识到问题的严重性。而物理
  • MySQL实战宝典-19高可用套件:选择这么多,你该如何选? 19 高可用套件:选择这么多,你该如何选?求,通过无损半同步复制的方式进行三园区的同城容灾设计,以及三地务中心的跨城容灾设计。 但是当数据库发生宕机时,MySQL 的主从复制并不会自动地切换,这需要高可用套件对数据库主从进行管理。
  • MySQL实战宝典-09索引组织表:万物皆索引 09 索引组织表:万物皆索引步深入了解 MySQL 的 B+ 树索引的具体使用,这一讲我想和你聊一聊 MySQL InnoDB 存储引擎的索引结构。 InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎,在海量大并发的 OL
  • MySQL实战宝典-12JOIN连接:到底能不能写JOIN? 12 JOIN 连接:到底能不能写 JOIN?优工作。但除了单表的 SQL 语句,还有两大类相对复杂的 SQL,多表 JOIN 和子查询语句,这就要在多张表上创建索引,难度相对提升不少。 而很多开发人员下意识地认为 JOIN 会降低
  • 近期文章

    更多
    文章目录

      推荐作者

      更多