管理mysql主从有2年多了,管理过200多组mysql主从,几乎涉及到各个版本的主从,本博文属于总结性的,有一部分是摘自网络,大部分是根据自己管理的心得和经验所写,整理了一下,分享给各位同行,希望对大家有帮助,互相交流。
一、mysql主从的原理
1、Replication 线程
Mysql的 Replication 是一个异步的复制过程(mysql5.1.7以上版本分为异步复制和半同步两种模式),从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程)在 Slave 端,另外一个线程(IO线程)在 Master 端。
要实现 MySQL 的 Replication ,首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能,否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项,或者在 f 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。
2、MySQL 复制的基本过程如下:
2.1.Slave 上面的IO线程连接上 Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2.2. Master 接收到来自 Slave 的 IO 线程的请求后,通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息,返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;
2.3. Slave 的 IO 线程接收到信息后,将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中,以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”
2.4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句,并在自身执行这些 Query。这样,实际上就是在 Master 端和 Slave 端执行了同样的 Query,所以两端的数据是完全一样的。
3、Mysql复制的几种模式
3.1.从 MySQL 5.1.12 开始,可以用以下三种模式来实现:
– 基于SQL语句的复制(statement-based replication, SBR),
– 基于行的复制(row-based replication, RBR),
– 混合模式复制(mixed-based replication, MBR)。
相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。 MBR 模式中,SBR 模式是默认的。
在运行时可以动态改动 binlog的格式,除了以下几种情况:
1.存储流程或者触发器中间
2.启用了NDB
3.当前会话试用 RBR 模式,并且已打开了临时表
如果binlog采用了 MIXED 模式,那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式:
1.当DML语句更新一个NDB表时
2.当函数中包含 UUID() 时
3.2个及以上包含 AUTO_INCREMENT 字段的表被更新时
4.行任何 INSERT DELAYED 语句时
5.用 UDF 时
6.视图中必须要求运用 RBR 时,例如建立视图是运用了 UUID() 函数
3.2.设定主从复制模式:
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
也可以在运行时动态修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
3.3.两种模式各自的优缺点:
SBR 的优点:
历史悠久,技能成熟
binlog文件较小
binlog中包含了所有数据库修改信息,可以据此来审核数据库的安全等情况
binlog可以用于实时的还原,而不仅仅用于复制
主从版本可以不一样,从服务器版本可以比主服务器版本高
SBR 的缺点:
不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出疑问
运用以下函数的语句也不能被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
INSERT … SELECT 会产生比 RBR 更多的行级锁
复制须要执行 全表扫描(WHERE 语句中没有运用到索引)的 UPDATE 时,须要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
确定了的 UDF 也须要在从服务器上执行
数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
执行复杂语句如果出错的话,会消耗更多资源
RBR 的优点:
任何情况都可以被复制,这对复制来说是最安全可靠