Mysql Replication系列之原理及配置
mysql的复制(replication)是一个异步的复制从一个mysql instace(master)复制到另一个mysql instace(slave)。实现整个复制操作主要有三个线程完成的,其中两个线程在slave(SQL_thread和IO_thread),另外一个线程在master(IO_thread)上。
Replication的原理如图所示:
具体步骤:
1. Master接收用户的请求,进行相磁的写操作后生成MySQL binlog;
2.Slave会启动两个线程,一个IO_thread,一个SQL_thread。IO_thread连接到Master上后会告诉Master我需要那个binlog及binlog positition之后的日志;
3.Master接收到来自Slave的IO_thread进程的请求后,通过负责复制的IO_thread根据请求信息读取指定日志指定位置之后的日志信息,返回给Slave 的IO_thread。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;
4.Slave的IO_thread接收到信息后,为了必避免机器故障,日志丢失,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”;
5. Slave上SQL_thread读取本的relay-log从中读到了变化过程或是SQL在进行执行,执行后的结果写入Slave上的数据文件中;
Master_Log_File,Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文 件和位置, 对应master的binlog文件和位置。
Relay_Log_File,Relay_Log_Pos记录了SQL thread执行到relay log的那个文件和位置,对应的是slave上的relay log文件和位置。
Relay_Master_Log_File,Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置,对应的master上binlog的文件和位置。
Mysql Replication是基于binlog来完成的,那么我就需要认识一下binlog。
1.MySQL日志的格式(实际生产中基本都是ROW,建议你用MIXED):
Binlog_format=STATEMENT|ROW|MIXED
STATEMENT : 基于操作的SQL语句记录到binlog中。
ROW: 基于行的变更情况记录,会记录行变更前的样子及变更后的内容。
MIXED: 混后使用ROW和STATEMENT格式。对于DDL记录会STATEMENT,对于TABLE
注意:如果使用Innodb表,事务级别使用了 READ COMMITTED or READ UNCOMMITTED日志级别只能使用row格式。但在使用ROW格式中DDL语句还是会记录来STATEMENT格式。
基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication,RBR),混合模式复制(mixed-based replication,MBR)。
ROW:
优点:
1.数据库变更采用行模式记录
2.slave上数据很少会不一致
3.任何情况都可以被复制,这对复制来说是最安全可靠的
4.和其他大多数数据库系统的复制技术一样
5.多数情况下,从服务器上的表如果有主键的话,复制就会快了很多
6.复制以下几种语句时的行锁更少:
a.INSERT ... SELECT
b. 包含 AUTO_INCREMENT 字段的 INSERT
c. 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
7.执行 INSERT,UPDATE,DELETE 语句时锁更少
8.从服务器上采用多线程来执行复制成为可能
缺点:
1.binlog 大了很多
2.复杂的回滚时 binlog 中会包含大量的数据
3.主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生 binlog 的并发写问题
4.UDF 产生的大 BLOB 值会导致复制变慢
5.无法从 binlog 中看到都复制了写什么语句
6.当在非事务表上执行一段堆积的SQL语句时,最好采用 SBR 模式,否则很容易导致主从服务器的数据不一致情况发生
STATEMENT:
优点:
1.历史悠久,技术成熟
2.binlog文件较小
3.binlog中包含了所有数据库更改信息,可以据此来审核数据库的安全等情况
4.binlog可以用于实时的还原,而不仅仅用于复制
5.主从版本可以不一样,从服务器版本可以比主服务器版本高
缺点:
1.不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
2.调用具有不确定因素的 UDF 时复制也可能出问题
3.使用以下函数的语句也无法被复制:
a.LOAD_FILE()
b.UUID()
c.USER()
d.FOUND_ROWS()
e.SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
4.INSERT ... SELECT 会产生比 RBR 更多的行级锁
5.复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时,需要比 RBR 请求更多的行级锁
6.对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
7.对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
8.存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
9.确定了的 UDF 也需要在从服务器上执行
10.数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
11.执行复杂语句如果出错的话,会消耗更多资源
MIXED:
Mixed: 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。
说了这么多,我们现在就来动手配置一个主从吧(只是在学习环境里用)
1.部署环境整理:
角色 | Ip:port | Server-id | 必备条件 |
Master | 192.168.11.20:3306 | 203306 | a.启用log-bin; b.主库上创建复制用户(grant replication slave on *.* to 'repl'@'%' identified by 'repl';) |
Slav | 192.168.11.21:3306 | 213306 |
b.在从库执行change master to master_host='192.168.11.128',master_port=3306, master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=253;
c.start slave;
d.show slave status\G;Slave_IO_Running, Slave_SQL_Running都是YES,说明从库已配置成功
e.主库上利用show processlist看看有没有同步的进程Command : Binlog Dump说明主库已配置成功
f.在主库随便创建一个数据库(表),然后在从库看看是否复制过来啦。