6.1.1.1 服务性能扩展方式
6.1.1.2 MySQL的扩展
6.1.1.3 复制的功用
6.1.1.4 复制架构
一主一从复制架构
一主多从复制架构
6.1.1.5 主从复制原理（面试）
主从复制相关线程
主节点：必须开启二进制日志功能
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：建议开启二进制日志功能，可以预防主服务器挂掉，上位
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
范例: 中继日志
[root@slave ~]#file /var/lib/mysql/mariadb-relay-bin.000001
/var/lib/mysql/mariadb-relay-bin.000001: MySQL replication log, server id 18 MySQL V5+, server version 10.3.17-MariaDB-log
[root@slave ~]#mysqlbinlog /var/lib/mysql/mariadb-relay-bin.000001|head
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200615 17:58:48 server id 18 end_log_pos 256 CRC32 0x7bd00c79 Start:
binlog v 4, server v 10.3.17-MariaDB-log created 200615 17:58:48
BINLOG '
WEbnXg8cAAAA/AAAAAABAAAAAAQAMTAuMy4xNy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
6.1.1.6 主从复制特点
6.1.1.7 各种复制架构
复制需要考虑二进制日志事件记录格式
参考官网
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置：
(1) 启用二进制日志
[mysqld]
log_bin
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]
server-id=#
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
说明：
server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
(4) 查看从二进制日志的文件和位置开始进行复制
SHOW MASTER LOG;
从节点配置：
(1) 启动中继日志
[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;
范例：新建主从复制
#主节点
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
[root@master ~]#systemctl restart mariadb
[root@master ~]#mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%'
identified by 'magedu';
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28052 |
| mariadb-bin.000002 | 545 |
+--------------------+-----------+
2 rows in set (0.001 sec)
#从节点
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
[root@slave ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.8.8',
MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=545;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 26987890
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 26987902
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 26987890
Relay_Log_Space: 26988213
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #复制的延迟时间
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 34
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 100006
1 row in set (0.000 sec)
范例：主服务器非新建时,主服务器运行一段时间后，新增从节点服务器
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
主服务器：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#ll /var/lib/mysql/ #多出两个文件
-rw-rw---- 1 mysql mysql 330 Jun 14 09:50 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 21 Jun 14 09:50 mariadb-bin.index
#查看日志节点
MariaDB [(none)]> show master logs; #先查看节点，再创建账户
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.000 sec)
#主节点上要建立一个参与复制的账户
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu';
#repluser是用户名，10.0.0.%代表可以从这个网段中任何一个主机连接，identified by 指定密码
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#mysqldump -A --single-transaction --master-data=1 -F > /backup/all.sql
[root@centos8 ~]#ll /backup/all.sql
-rw-r--r-- 1 root root 487711 Jun 14 09:55 /backup/all.sql
[root@centos8 ~]#vim /backup/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
#复制数据库
[root@centos8 ~]#scp /backup/all.sql 10.0.0.18:/data
从服务器：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
[root@centos8 ~]#systemctl start mariadb
#主从复制的配置
方法一：
[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
方法二：
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8',
MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
#还原数据库和复制信息
[root@centos8 ~]#mysql < /data/all.sql
#此时复制信息已经准备好了
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 375
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: No #I/O线程未启用
Slave_SQL_Running: No #SQL线程未启用
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 375
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL #复制延期，主节点和从节点复制差了多长时间，注意观察，最好是0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
#查看新增加的文件
[root@centos8 ~]#ll /var/lib/mysql/ -t
total 122952
-rw-rw---- 1 mysql mysql 50331648 Jun 14 10:31 ib_logfile0
-rw-rw---- 1 mysql mysql 12582912 Jun 14 10:31 ibdata1
drwx------ 2 mysql mysql 4096 Jun 14 10:31 mysql
drwx------ 2 mysql mysql 272 Jun 14 10:31 hellodb
-rw-rw---- 1 mysql mysql 56 Jun 14 10:31 relay-log.info #二进制日志和中继日志的对应关系
-rw-rw---- 1 mysql mysql 256 Jun 14 10:31 mariadb-relay-bin.000001
-rw-rw---- 1 mysql mysql 27 Jun 14 10:31 mariadb-relay-bin.index #复制下来的中继日志
-rw-rw---- 1 mysql mysql 154 Jun 14 10:31 master.info #主节点的信息
#启用两个线程
MariaDB [(none)]> show processlist; #这是从节点启用前的进程
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.000 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 572
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 754
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes #线程已开启
Slave_SQL_Running: Yes #线程已开启
Seconds_Behind_Master: 0 #复制延迟为0；说明已经复制成功
...省略...
MariaDB [(none)]> show processlist; #这是从节点的进程
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 13 | system user | | NULL | Slave_IO | 143 | Waiting for master to send event | NULL | 0.000 |
| 14 | system user | | NULL | Slave_SQL | 143 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
| 15 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
8 rows in set (0.000 sec)
MariaDB [(none)]> show processlist; #从节点启用后，主节点上的Dump线程同时启用
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 11 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 12 | repluser | 10.0.0.18:55922 | NULL | Binlog Dump | 323 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
7 rows in set (0.000 sec)
简洁步骤：
#在主服务器完全备份
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 >
/backup/fullbackup_`date +%F_%T`.sql
[root@master ~]#ll /backup/
total 2988
-rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql
[root@master ~]#scp /backup/fullbackup_2019-11-27_17\:41\:17.sql
192.168.8.11:/data/
#建议优化主和从节点服务器的性能
#global innodb_flush_log_at_trx_commit=2
#sync_binlog=0
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0.001 sec)
#将完全备份还原到新的从节点
[root@slave ~]#dnf -y install mariadb-server
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=11
read-only
[root@slave ~]#systemctl restart mariadb
#配置从节点，从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.10',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql
[root@slave ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.8.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 389
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;
限制从服务器为只读
read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效
注意：以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;
在从节点清除信息
注意：以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和PASSWORD 等
复制错误解决方法
可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL
START SLAVE 语句，指定执到特定的点
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
[thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD
范例：复制冲突的解决
#方法1
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;
#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
[root@slave1 ~]#systemctl restart mariadb
保证主从复制的事务安全
参看https://mariadb.com/kb/en/library/server-system-variables/
在master节点启用参数：
sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
innodb_support_xa=ON #分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘
在slave节点启用服务器选项：
skip-slave-start=ON #不自动启动slave
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
范例：当master服务器宕机，提升一个slave成为新的master
#找到哪个从节点的数据库是最新，让它成为新master
[root@centos8 ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000002
1180
mysql-bin.000002
996
0
#新master修改配置文件，关闭read-only配置
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin
#清除旧的master复制信息
MariaDB [hellodb]>set global read_only=off;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
#在新master上完全备份
[root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql
[root@slave1 ~]#scp backup.sql 10.0.0.28:
#分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据
#其它所有 slave 重新还原数据库，指向新的master
[root@slave2 ~]#vim backup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
MariaDB [hellodb]>start slave;
需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制
[mysqld]
server-id=18
log_bin
log_slave_updates
read-only
案例：三台主机实现级联复制
#在10.0.0.8充当master
#在10.0.0.18充当级联slave
#在10.0.0.28充当slave
#在master实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql
MariaDB [(none)]> show master logs; #先查看节点，再创建账户，这样复制过去的日志里就有创建账号的记录
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28198 |
| mariadb-bin.000002 | 541 |
+--------------------+-----------+
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu';
[root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
[root@centos8 ~]#scp /data/all.sql 10.0.0.18:/data
[root@centos8 ~]#scp /data/all.sql 10.0.0.28:/data
#在中间级联slave实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
read-only
log_slave_updates #级联复制中间节点的必选项
[root@centos8 ~]#systemctl restart mariadb
#还原数据库
[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=541;
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/all.sql
MariaDB [(none)]> show master logs; #记录二进制位置，给第三个节点使用
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28200 |
| mariadb-bin.000002 | 471 |
+--------------------+-----------+
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> start slave;
#在第三个节点slave上实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
read-only
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18', #中间节点的IP
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=471;
[root@centos8 ~]#mysql < /data/all.sql
[root@centos8 ~]#mysql -e 'start slave;'
注意：统一版本，配置上时主主复制，实际当成一主一从，即可以考虑设置为一读一写，这样在主服务器宕掉的情况下，从服务器可以迅速上位，两边配置基本一样，只有读写不同
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号（在复制数据之后，二进制日志里就记录了创建过程）
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
范例：实现两个节点的主主复制模型
检查两个数据库是否都是新装，若有一个有数据，那就先备份
备份建议加时间
help change master to
再导入数据前关闭二进制日志功能
开启线程
二进制日志没增长，数据却复制过来了，原因是：
主服务器：dump线程
从服务器：iothread线程、sqlthread线程
本机的二进制日志只记录本机收到的二进制修改，别的机器传来的不影响本机的二进制日志
#在第一个master节点上实现
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8 #指定id
log-bin #开启二进制日志
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
[root@master1 ~]#systemctl start mariadb
[root@master1 ~]#mysql
MariaDB [(none)]>show master logs; #记录位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.000 sec)
#建立复制账号，此操作是在记录位置之后发生的，所以账号就复制过去了，另一个服务器就不需要再创建，两边用同一个账号进行彼此复制
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu';
#在第二个master节点上实现
[rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
[root@master2 ~]#systemctl start mariadb
[root@master2 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.019 sec)
MariaDB [(none)]> start slave; #开启进程，实现了单向复制
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> show master logs; #查看二进制位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.000 sec)
#在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> create database db1;
MariaDB [(none)]> use db1
MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));
#两个节点分别插入数据
#在第一个节点上执行，奇数增长，
MariaDB [db1]> create database db1;
MariaDB [db1]> insert t1 (name) values('user1');
#在第二个节点上执行，偶数增长
MariaDB [db1]> insert t1 (name) values('user2');
#两个节点同时插入数据
MariaDB [db1]> insert t1 (name) values('userX');
MariaDB [db1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.001 sec)
#两个节点同时创建数据库，发生复制冲突
MariaDB [db1]> create database db2;
MariaDB [db1]> show slave status\G
默认情况下，MySQL的复制功能是异步的（客户端更新数据，主服务器收到后立刻返回成功结果，但此时数据还并没有复制到从服务器上），异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
半同步复制实现：
客户端发请求写操作发送给主服务器，主服务器在自己服务器上更新，找一个从节点复制且必须复制成功（也就是所有从节点中至少有一个成功），最后主服务器把成功结果返还给客户端
说明：半同步机制里，任何一个从节点超过10秒（默认）没复制成功，主服务器就告诉客户数据已经更新成功
官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/
范例：CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
#在master实现，启用半同步功能
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端
[root@centos8 ~]#systemctl restart mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master |