MySQL主从复制原理及配置

1 复制概述

MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1.1 mysql支持的复制类型：　　

　　（１）：基于语句的复制：  在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。  
            一旦发现没法精确复制时，  会自动选着基于行的复制。    
　　（２）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
　　（３）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
1.2 . 复制解决的问题        MySQL复制技术有以下一些特点：
        (1)    数据分布 (Data distribution )
        (2)    负载平衡(load balancing)
        (3)    备份(Backups) 
        (4)    高可用性和容错行 High availability and failover 
 1.3 复制如何工作 
            复制的原理：

      (1)    master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
      (2)    slave将master的binary log events拷贝到它的中继日志(relay log)；
      (3)    slave重做中继日志中的事件，将更改应用到自己的数据上。

下图描述了复制的过程：

                                  
          该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
      下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
      SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
        此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

2 .复制配置  有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。
要点：
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

主从复制配置

步骤如下：

主服务器：从服务器ip地址分别为

[python] view plain copy 

1. 192.168.145.222、192.168.145.226  

1、修改主服务器master:

[python] view plain copy 

1. vi /etc/my.cnf  

2. [mysqld]  

3.     log-bin=mysql-bin   #[必须]启用二进制日志  

4.     server-id=222      #[必须]服务器唯一ID，默认是1，一般取IP最后一段  

2、修改从服务器slave:

[python] view plain copy 

1. vi /etc/my.cnf  

2. [mysqld]  

3.      log-bin=mysql-bin   #[不是必须]启用二进制日志  

4.      server-id=226      #[必须]服务器唯一ID，默认是1，一般取IP最后一段  

3、重启两台服务器的MySQL

[python] view plain copy 

1. systemctl restart mariadb  

4、在主服务器上建立帐户并授权slave:

[python] view plain copy 

1. mysql  

2. mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。  

5、登录主服务器的mysql，查询master的状态

[python] view plain copy 

1. mysql>show master status;  

2.  +------------------+----------+--------------+------------------+  

3.  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  

4.  +------------------+----------+--------------+------------------+  

5.  | mysql-bin.000004 |      308 |              |                  |  

6.  +------------------+----------+--------------+------------------+  

7.  1 row in set (0.00 sec)  

   注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

6、配置从服务器Slave：

注意mysql-bin.000004和308是第五步中的File和Position

[python] view plain copy 

1. mysql>change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000004',master_log_pos=308; //注意mysql-bin.000004和308是第五步中的File和  

2. mysql>start slave; //启动从服务器复制功能  

7、检查从服务器复制功能状态：

[python] view plain copy 

1. mysql> show slave statusG  

2. ************************** 1. row ***************************  

3.   

4.             Slave_IO_State: Waiting for master to send event  

5.             Master_Host: 192.168.2.222  //主服务器地址  

6.             Master_User: mysync   //授权帐户名，尽量避免使用root  

7.             Master_Port: 3306    //数据库端口，部分版本没有此行  

8.             Connect_Retry: 60  

9.             Master_Log_File: mysql-bin.000004  

10.             Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos  

11.             Relay_Log_File: ddte-relay-bin.000003  

12.             Relay_Log_Pos: 251  

13.             Relay_Master_Log_File: mysql-bin.000004  

14.             Slave_IO_Running: Yes    //此状态必须YES  

15.             Slave_SQL_Running: Yes     //此状态必须YES  

16.                   ......  

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

 

9、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

[python] view plain copy 

1. mysql> create database hi_db;  

2.   Query OK, 1 row affected (0.00 sec)  

3.   

4.   mysql> use hi_db;  

5.   Database changed  

6.   

7.   mysql>  create table hi_tb(id int(3),name char(10));  

8.   Query OK, 0 rows affected (0.00 sec)  

9.    

10.   mysql> insert into hi_tb values(001,'bobu');  

11.   Query OK, 1 row affected (0.00 sec)  

12.   

13.   mysql> show databases;  

14.    +--------------------+  

15.    | Database           |  

16.    +--------------------+  

17.    | information_schema |  

18.    | hi_db                |  

19.    | mysql                |  

20.    | test                 |  

21.    +--------------------+  

22.    4 rows in set (0.00 sec)  

23.   

24. 从服务器Mysql查询：  

25.   

26.    mysql> show databases;  

27.   

28.    +--------------------+  

29.    | Database               |  

30.    +--------------------+  

31.    | information_schema |  

32.    | hi_db                 |       //I'M here，大家看到了吧  

33.    | mysql                 |  

34.    | test          |  

35.   

36.    +--------------------+  

37.    4 rows in set (0.00 sec)  

38.   

39.    mysql> use hi_db  

40.    Database changed  

41.    mysql> select * from hi_tb;           //查看主服务器上新增的具体数据  

42.    +------+------+  

43.    | id   | name |  

44.    +------+------+  

45.    |    1 | bobu |  

46.    +------+------+  

47.    1 row in set (0.00 sec)