mysql 主从原理及配置

MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。与之对应的是另一个同步技术是MySQL Cluster，但因为MySQL Cluster配置比较复杂，所以使用者较少。

MySQL的Replication是一个异步复制的过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），它是从一个Mysql instance（instance英文为实例）(我们称之为Master)复制到另一个Mysql instance(我们称之slave)。在master与slave之间实现整个复制过程主要由三个线程来完成，其中两个线程(SQL线程和IO线程)在slave端，另外一个线程(IO线程)在master端。

要实现MySQL的Replication，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。因为mysql的整个主从复制过程实际上就是：slave端从master端获取binlog日志，然后再在自己身上完全顺序的执行该日志中所记录的各种SQL操作。

有关具体如何开启mysql的binlog日志功能，

MySQL主从复制的基本交互过程，如下：

1、slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。

2、master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。

3、slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存在slave端）文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。

4、slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺丰依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据是完全一样的。

以上mysql主从复制交互过程比较拗口，理解起来也比较麻烦，我简化了该交互过程。如下：

1、master在执行sql之后，记录二进制log文件（bin-log）。

2、slave连接master，并从master获取binlog，存于本地relay-log中，然后从上次记住的位置起执行SQL语句，一旦遇到错误则停止同步。

从以上mysql的Replication原理可以看出：

* 主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间主从数据不一致的情况。

* 如果主从的网络断开，则从库会在网络恢复正常后，批量进行同步。

* 如果对从库进行修改数据，那么如果此时从库正在在执行主库的bin-log时，则会出现错误而停止同步，这个是很危险的操作。所以一般情况下，我们要非常小心的修改从库上的数据。

* 一个衍生的配置是双主、互为主从配置，只要双方的修改不冲突，则可以工作良好。

* 如果需要多主库的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点

主从复制配置

步骤如下：

主服务器：从服务器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 status\G  
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)