---

主从复制原理主从复制线程主节点

   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

搭建环境准备主机IP地址类型CentOS7.6192.168.36.101MasterCentOS7.6192.168.36.103SlaveCentOS7.6192.168.36.104Slave搭建之前请先确保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 eventMaster_Host: 192.168.36.101Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 7389 Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 7673Relay_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)

主主复制：两台服务器互为主从主机IP地址类型CentOS7.6192.168.36.101Master-1CentOS7.6192.168.36.103Master-2Master-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 eventMaster_Host: 192.168.36.103Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000003Relay_Log_Pos: 529Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: YesSlave_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) # 由等待时长可以看出，半同步插件已经起到效果