Mysql Replication系列之原理及配置

ew3y 10年前

       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  
    2.找到master上的log及position生成change master to语句:         a.在主库通过show master status,可以查看到log以及position
        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.在主库随便创建一个数据库(表),然后在从库看看是否复制过来啦。