MySQL的主从复制

     横向扩展：又称水平扩展（Scale Out），是通过负载均衡的方式，将压力疏散到后端各个节点服务器；

     纵向扩展：又称垂直扩展（Scale Up），简单提高服务器的硬件性能，此方法很容易再次出现性能瓶颈现象。

数据库服务器在涉及到均衡调度时通常会遇到很多不可忽视的问题：

     数据库服务器数据的一致性；

     多个事务提交导致的数据完整性的保证；

     服务器宕机，怎么保证事务正常提交等等。

数据同步方面，一般得解决方案有一下几种：

    同步复制 (Synchronous Replication)：发生写数据时，主服务器需要等待从服务器同步完成之后才会返回给客户端信息，等待过程中为阻塞状态，所以效率极低；

    半同步复制 (Asynchronous Replication)：发生写数据时，主服务器只同步给其中一台从服务器，半同步给其他从服务器；

    异步复制 (Semisynchronous Replication)：发生写数据时，主服务器在从服务器同步数据的同时，向客户端返回信息，这种方式可能导致主从服务器数据不一致，或者从服务器数据同步状态严重落后于主服务器上的数据。

MySQL主从复制模式：Master/Slave、Master/Master

    Master/Slave：一主多从模式，由于从服务器只有只读权限，所以此种模式只能有效分担读请求的压力，但不能分担写请求；此种模式会带来数据同步一致性的问题，可能从服务器同步的数据进度远落后于主服务器更新的进度；如果主机宕机后，则数据就无法写入。

    Master/Master：多主模式，多台服务器互为主从；这种模式会带来数据更新不一致，从而发生冲突的问题。

主/从工作流程：

    建立主从关系后，如果主服务器发生Data Change之后，Binary Log会更新日志记录；从服务器通过I/O  Thread读取主服务器上的Binary Log保存至本地的 Relay Log中，再通过SQL Thread执行本地Relay Log中的SQL语句，并将执行后的数据保存至磁盘中。

示例一：MySQL异步复制实现

 配置文件： 

Master:node7配置文件：   

   [mysqld]

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    symbolic-links=0

    innodb_file_per_table=1

    skip_name_resolve=ON

    log_bin=master-log                #开启二进制日志

    server_id=1                       #设置serverID

    [mysqld_safe]

    log-error=/var/log/mariadb/mariadb.log

    pid-file=/var/run/mariadb/mariadb.pid

Slave:node8配置文件：

   [mysqld]

    datadir=/var/lib/mysql

    socket=/var/lib/mysql/mysql.sock

    symbolic-links=0

    relay-log=relay-log               #开启relay日志

    innodb_file_per_table=1

    read-only=1                       #设置为只读

    server_id=2                       #设置serverID，必须不同于其他serverID

    [mysqld_safe]

    log-error=/var/log/mariadb/mariadb.log

    pid-file=/var/run/mariadb/mariadb.pid

启动mariaDB：

    [root@node7 ~]# systemctl start mariadb.service  

    [root@node8 ~]# systemctl start mariadb.service

从服务器需要主服务器授权一个具有REPLICATION SLAVE, REPLICATION CLIENT权限的用户：

    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;

    MariaDB [(none)]> FLUSH PRIVILEGES; 

    MariaDB [(none)]> SHOW MASTER STATUS;     #记录此时的binlog文件及位置，从服务器即将从此处进行同步

    +-------------------+----------+--------------+------------------+

    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +-------------------+----------+--------------+------------------+

    | master-log.000003 |      492 |              |                  |

    +-------------------+----------+--------------+------------------+

    1 row in set (0.00 sec)

从服务器开始做同步复制的配置：

    MariaDB [(none)]> CHANGE MASTER TO

        -> MASTER_HOST=‘192.168.17.70‘,

        -> MASTER_USER=‘hisen‘,

        -> MASTER_PASSWORD=‘hisen‘,

        -> MASTER_LOG_FILE=‘master-log.000003‘,

        -> MASTER_LOG_POS=492;

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> SHOW SLAVE STATUS\G;         #查看从服务器状态

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

                   Slave_IO_State:

                      Master_Host: 192.168.17.70

                      Master_User: hisen

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: master-log.000003

              Read_Master_Log_Pos: 492

                   Relay_Log_File: relay-log.000001

                    Relay_Log_Pos: 4

            Relay_Master_Log_File: master-log.000003

                 Slave_IO_Running: No                    #IO-Thread没有启动

                Slave_SQL_Running: No                    #SQL-Thread没有启动

    MariaDB [(none)]> START SLAVE;        启动IO-Thread和SQL-Thread        

    Query OK, 0 rows affected (0.02 sec)

    MariaDB [(none)]> SHOW SLAVE STATUS\G;

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 192.168.17.70   #主服务器地址

                      Master_User: hisen                     #复制时用到的用户名

                      Master_Port: 3306                       #主服务器端口

                    Connect_Retry: 60                            #重试连接时长

                  Master_Log_File: master-log.000003      #同步主服务器上的binlog文件

              Read_Master_Log_Pos: 492                                     #binlog文件的位置

                   Relay_Log_File: relay-log.000002        #本地relay文件

                    Relay_Log_Pos: 530                                     #relay文件位置

            Relay_Master_Log_File: master-log.000003

                 Slave_IO_Running: Yes               #IO-Thread启动

                Slave_SQL_Running: Yes               #SQL-Thread启动

测试主从复制：

Master配置：      

    MariaDB [(none)]> CREATE DATABASE hisendb;

    Query OK, 1 row affected (0.00 sec)

    MariaDB [(none)]> USE hisendb;

    Database changed

    MariaDB [hisendb]> CREATE TABLE teachers (TID TINYINT NOT NULL PRIMARY KEY,Name VARCHAR(50),Age INT,Gender CHAR(10));

    Query OK, 0 rows affected (0.02 sec)

    MariaDB [hisendb]> INSERT INTO teachers VALUES (1,‘Mo Yuan‘,100,‘M‘),(2,‘Bai Qian‘,150,‘F‘),(3,‘Dong Huadijun‘,100,‘M‘);

    Query OK, 3 rows affected (0.01 sec)

    Records: 3  Duplicates: 0  Warnings: 0

    MariaDB [hisendb]> SELECT * FROM teachers;

    +-----+---------------+------+--------+

    | TID | Name          | Age  | Gender |

    +-----+---------------+------+--------+

    |   1 | Mo Yuan       |  100 | M      |

    |   2 | Bai Qian      |  150 | F      |

    |   3 | Dong Huadijun |  100 | M      |

    +-----+---------------+------+--------+

    3 rows in set (0.00 sec)

Slave端查看：

    MariaDB [(none)]> SHOW DATABASES;

    +--------------------+

    | Database           |

    +--------------------+

    | information_schema |

    | hisendb            |

    | mysql              |

    | performance_schema |

    | test               |

    +--------------------+

    5 rows in set (0.01 sec)

    MariaDB [(none)]> USE hisendb;

    Database changed

    MariaDB [hisendb]> SELECT * FROM teachers;

    +-----+---------------+------+--------+

    | TID | Name          | Age  | Gender |

    +-----+---------------+------+--------+

    |   1 | Mo Yuan       |  100 | M      |

    |   2 | Bai Qian      |  150 | F      |

    |   3 | Dong Huadijun |  100 | M      |

    +-----+---------------+------+--------+

    3 rows in set (0.00 sec)

对比可知，已经实现主从同步的效果！

再次查看Master信息：

    MariaDB [(none)]> SHOW MASTER STATUS;

    +-------------------+----------+--------------+------------------+

    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +-------------------+----------+--------------+------------------+

    | master-log.000003 |     1008 |              |                  |

    +-------------------+----------+--------------+------------------+

    1 row in set (0.00 sec)

再次查看Slave信息：

    MariaDB [hisendb]> SHOW SLAVE STATUS\G;

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 192.168.17.70

                      Master_User: hisen

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: master-log.000003

              Read_Master_Log_Pos: 1008                             #已经跟Master的binlog位置相同

                   Relay_Log_File: relay-log.000002

                    Relay_Log_Pos: 1046

            Relay_Master_Log_File: master-log.000003

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

示例二：MySQL半同步复制实现

需要插件支持：semisync_master.so、semisync_slave.so

主节点：     

    MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;

    Query OK, 0 rows affected (0.10 sec)

    MariaDB [(none)]>

    MariaDB [(none)]>

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;

    +------------------------------------+-------+

    | Variable_name                      | Value |

    +------------------------------------+-------+

    | rpl_semi_sync_master_enabled       | OFF   |

    | rpl_semi_sync_master_timeout       | 10000 |

    | rpl_semi_sync_master_trace_level   | 32    |

    | rpl_semi_sync_master_wait_no_slave | ON    |

    +------------------------------------+-------+

    4 rows in set (0.00 sec)

    MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;   

    +------------------------------------+-------+

    | Variable_name                      | Value |

    +------------------------------------+-------+

    | rpl_semi_sync_master_enabled       | ON    |     #半同步主节点开启

    | rpl_semi_sync_master_timeout       | 10000 |     #主节点等待超时时间，默认10S

    | rpl_semi_sync_master_trace_level   | 32    |

    | rpl_semi_sync_master_wait_no_slave | ON    |

    +------------------------------------+-------+

    4 rows in set (0.00 sec)

    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘hisen‘@‘192.168.17.%‘ IDENTIFIED BY ‘hisen‘;

    MariaDB [(none)]> SHOW MASTER STATUS;

    +-------------------+----------+--------------+------------------+

    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +-------------------+----------+--------------+------------------+

    | master-log.000003 |      417 |              |                  |

    +-------------------+----------+--------------+------------------+

    1 row in set (0.00 sec)

    MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;        

    +--------------------------------------------+-------+

    | Variable_name                              | Value |

    +--------------------------------------------+-------+

    | Rpl_semi_sync_master_clients               | 1     |     #已经有一个半同步客户端

    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |

    | Rpl_semi_sync_master_net_wait_time         | 0     |

    | Rpl_semi_sync_master_net_waits             | 0     |

    | Rpl_semi_sync_master_no_times              | 1     |

    | Rpl_semi_sync_master_no_tx                 | 1     |

    | Rpl_semi_sync_master_status                | ON    |     #已经确认为半同步复制的主节点

    | Rpl_semi_sync_master_timefunc_failures     | 0     |

    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |

    | Rpl_semi_sync_master_tx_wait_time          | 0     |

    | Rpl_semi_sync_master_tx_waits              | 0     |

    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

    | Rpl_semi_sync_master_wait_sessions         | 0     |

    | Rpl_semi_sync_master_yes_tx                | 0     |

    +--------------------------------------------+-------+

    14 rows in set (0.01 sec)

从节点：     

    MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;

    +---------------------------------+-------+

    | Variable_name                   | Value |

    +---------------------------------+-------+

    | rpl_semi_sync_slave_enabled     | OFF   |

    | rpl_semi_sync_slave_trace_level | 32    |

    +---------------------------------+-------+

    2 rows in set (0.00 sec)

    MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=ON;

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%‘;  

    +---------------------------------+-------+

    | Variable_name                   | Value |

    +---------------------------------+-------+

    | rpl_semi_sync_slave_enabled     | ON    |

    | rpl_semi_sync_slave_trace_level | 32    |

    +---------------------------------+-------+

    2 rows in set (0.00 sec)

               MariaDB [(none)]> CHANGE MASTER TO

        -> MASTER_HOST=‘192.168.17.70‘,

        -> MASTER_USER=‘hisen‘,

        -> MASTER_PASSWORD=‘hisen‘,

        -> MASTER_LOG_FILE=‘master-log.000003‘,

        -> MASTER_LOG_POS=417;

               Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> START SLAVE;

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> SHOW SLAVE STATUS\G;

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 192.168.17.70

                      Master_User: hisen

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: master-log.000003

              Read_Master_Log_Pos: 417

                   Relay_Log_File: relay-log.000002

                    Relay_Log_Pos: 530

            Relay_Master_Log_File: master-log.000003

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

    MariaDB [hisendb]> SHOW GLOBAL STATUS LIKE ‘%semi%‘;

    +----------------------------+-------+

    | Variable_name              | Value |

    +----------------------------+-------+

    | Rpl_semi_sync_slave_status | ON    |     #已经确认为半同步复制中从节