详细介绍MySQL如何开启主从复制实现数据同步

主从复制原理

主从复制线程

主节点

   dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点

   I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
   SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件

   master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等

   relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

主从复制特点

   异步复制
   主从数据不一致比较常见

复制架构

   Master/Slave,Master/Master,环状复制
   一主多从
   从服务器还可以再有从服务器
   一从多主：适用于多个不同数据库

复制需要考虑二进制日志时间记录格式

   STATEMENT（5.0之前）
   ROW（5.1之后，推荐）
   MIXED

搭建环境准备

搭建之前请先确保MySQL软件包已经安装

一主一从

Master节点修改数据库配置文件

[root@Master ~]#cat /etc/my.cnf[mysqld]server_id=1     # 为Master节点设置一个全局唯一的ID号binlog_format=row       # 基于行复制的数据库语句log-bin=/data/bin/mysql-bin     # 启用二进制日志

重新启动数据库服务

[root@Master ~]#service mysqld restartRestarting mysqld (via systemctl):                         [  OK  ]

Master节点上创建带有复制权限的用户账号

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';Query OK, 0 rows affected (0.00 sec)

查看Master的日志位置信息

MariaDB [mysql]> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |    912372 |+------------------+-----------+1 row in set (0.00 sec)

Slave节点修改配置文件

[root@Slave-1 ~]#cat /etc/my.cnf[mysqld]server_id=2         # Slave节点设置全局唯一的ID号read_only           # 只读

重新启动数据库服务

[root@Slave-1 ~]#systemctl restart mariadb

使用Master创建的复制权限的用户账号进行同步

MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.36.101',    ->  MASTER_USER='repluser',    ->  MASTER_PASSWORD='centos',    ->  MASTER_PORT=3306,    ->  MASTER_LOG_FILE='mysql-bin.000001',    ->  MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec)

启动Slave线程

MariaDB [(none)]> slave start;Query OK, 0 rows affected (0.00 sec)

查看线程是否启动

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.36.101                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 7389               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 7673        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes      # 从节点的IO线程            Slave_SQL_Running: Yes      # 从节点的SQL线程....        Seconds_Behind_Master: 0        # Master与SLave服务器差别延迟.....             Master_Server_Id: 11 row in set (0.00 sec)

检查数据同步情况

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

主节点中查看数据库的线程

MariaDB [hellodb]> show processlist;+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------| Id | User     | Host                 | db      | Command     | Time | State+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------|  3 | root     | localhost            | hellodb | Query       |    0 | NULL|  6 | repluser | 192.168.36.103:51516 | NULL    | Binlog Dump |  115 | Master has sent all binlog to slave; waiting for binlog to be upd+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------2 rows in set (0.00 sec)

从节点中查看数据库的线程

MariaDB [(none)]> show processlist;+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--| Id | User        | Host      | db   | Command | Time  | State                                                                       | I+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--|  4 | root        | localhost | NULL | Query   |     0 | NULL                                                                        | s|  5 | system user |           | NULL | Connect |   159 | Waiting for master to send event                                            | N|  6 | system user |           | NULL | Connect | 29259 | Slave has read all relay log; waiting for the slave I/O thread to update it | N+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--3 rows in set (0.00 sec)

一主多从：新添加一个从节点

Slave-2 节点安装数据库服务

[root@Slave-2 ~]#yum install -y mariadb-server

修改 Slave-2 节点配置文件

[root@Slave-2 ~]#cat /etc/my.cnf[mysqld]server_id=3read_only....

重新启动 Slave-2 节点数据库服务

[root@Slave-2 ~]#systemctl restart mariadb

Slave-2 节点配置同步信息

MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.36.101',    ->  MASTER_USER='repluser',    ->  MASTER_PASSWORD='centos',    ->  MASTER_PORT=3306,    ->  MASTER_LOG_FILE='mysql-bin.000001',    ->  MASTER_LOG_POS=245;Query OK, 0 rows affected (0.00 sec)

启用线程

MariaDB [(none)]> slave start;Query OK, 0 rows affected (0.00 sec)

检查同步情况

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || mysql              || performance_schema || test               |+--------------------+6 rows in set (0.00 sec)

主主复制：两台服务器互为主从

Master-1 修改配置文件

[root@Master-1 data]#cat /etc/my.cnf[mysqld]server_id=1log_bin     # 启用日志# 为避免ID冲突问题，需要添加下面两行配置：Master-1 为奇数增长，Master-2 为偶数增长auto_increment_offset=1     # 开始点auto_increment_increment=2  # 增长幅度....

Master-2 修改配置文件

[root@Master-2 data]#cat /etc/my.cnf[mysqld]server_id=2log_binauto_increment_offset=2auto_increment_increment=2

重新启动服务

[root@Master-1 ~]#systemctl restart mariadb[root@Master-2 ~]#systemctl restart mariadb

Master-1 创建权限用户，Master-2 先设置为从服务器进行同步 Master-1 的数据库

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';Query OK, 0 rows affected (0.00 sec)

Master-2 同步Master-1

MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.36.101',    ->  MASTER_USER='repluser',    ->  MASTER_PASSWORD='centos',    ->  MASTER_PORT=3306,    ->  MASTER_LOG_FILE='mysql-bin.000001',    ->  MASTER_LOG_POS=245;Query OK, 0 rows affected (0.00 sec)

启用线程

MariaDB [(none)]> slave start;Query OK, 0 rows affected (0.00 sec)

检查同步情况

MariaDB [(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mysql]> select user,password,host from user;+----------+-------------------------------------------+--------------+| user     | password                                  | host         |+----------+-------------------------------------------+--------------+| root     |                                           | localhost    || root     |                                           | 127.0.0.1    || root     |                                           | ::1          || repluser | *128977E278358FF80A246B5046F51043A2B1FCED | 192.168.36.% |+----------+-------------------------------------------+--------------+7 rows in set (0.00 sec)

Master-1 向 Master-2 进行同步

MariaDB [mysql]> CHANGE MASTER TO    -> MASTER_HOST='192.168.36.103',    -> MASTER_USER='repluser',    -> MASTER_PASSWORD='centos',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='mysql-bin.000001',    -> MASTER_LOG_POS=245;Query OK, 0 rows affected (0.05 sec)

Master-1 同步状态

MariaDB [(none)]> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.36.103                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 245               Relay_Log_File: mariadb-relay-bin.000003                Relay_Log_Pos: 529        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes....           Master_SSL_Allowed: No....             Master_Server_Id: 21 row in set (0.01 sec)

此时Master-1、Master-2 实现主主复制功能

半同步复制

   半同步复制的作用是：主服务器宕机后，所有备份服务器主动向同步数据最多的服务器进行数据的同步，以确保数据损失降到最低。

半同步是通过插件功能达成的

Master节点安装semisync_master.so插件

MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';Query OK, 0 rows affected (0.07 sec)

启用插件功能

MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)

设置超时时长

MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_timeout=6000;Query OK, 0 rows affected (0.00 sec)

查看Master插件状态

MariaDB [db1]> show variables like '%semi%';+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled       | ON    || rpl_semi_sync_master_timeout       | 6000  || rpl_semi_sync_master_trace_level   | 32    || rpl_semi_sync_master_wait_no_slave | ON    |+------------------------------------+-------+4 rows in set (0.00 sec)

Master修改配置文件并重启数据库服务

[root@Master data]#cat /etc/my.cnf[mysqld]...rpl_semi_sync_master_enabled        # 启用插件功能...[root@Master data]#systemctl restart mariadb

Slave节点安装semisync_slave.so插件

MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Query OK, 0 rows affected (0.00 sec)

启用slave插件功能

MariaDB [db1]> SET GLOBAL rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)

Slave修改配置文件并重启数据库服务

[root@Slave data]#cat /etc/my.cnf[mysqld]...rpl_semi_sync_slave_enabled        # 启用插件功能...[root@Slave data]#systemctl restart mariadb

停止Slave 数据库服务进行测试

MariaDB [(none)]> create database db3;Query OK, 1 row affected (6.00 sec)     # 由等待时长可以看出，半同步插件已经起到效果