为什么要进行主从复制?
①.如果对主数据库进行写入操作时,有任何一句SQL语句需要锁表(排他锁),那么其他事务访问过来时都没有权限,非常影响其他的业务,如果我们进行主从复制,那么我们可以对从数据库进行读操作,对主数据库进行写操作,可以读写分离,大大提升效率。
②.同时可以对数据进行一个备份,防范于未然,提高数据的安全性。
什么是主从复制?
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。
话不多说,上图
架构图原理
1.首先我们要在master上面开启二进制日志。
2.当master上执行SQL操作导致数据发生变化时,二进制日志会发生变化。
3.master上面的dump线程会主动通知slave服务器上面的 io线程读取二进制日志,io线程拿到二进制日志后会写入到relay log中继日志
4.slave上面的sql 线程会去读取新产生的中继日志并且执行里面的操作。从而达到slave和master上面的数据一样,实现数据的一致性。
相关问题
问题: 1.到底是主服务器主动通知从服务器来取二进制日志,还是从服务器每隔一段时间来拿二进制日志? 主服务器主动通知 2.从服务器怎么知道这次拿二进制里面的哪些内容? slave服务器里面有master.info文件记录了该拿二进制文件里面哪些内容 3.是否任何一台机器都可以充当从服务器,不需要验证就可以去拿主服务器的二进制日志? 需要在master上面建用户并且授权(replication slave),slave服务器的master_info文件记录了用户和密码验证自己身份(MASTER_USER='slave',MASTER_PASSWORD='123456') 4.从服务器如何知道谁是它的主服务器呢? master.info文件里面记录的master的主机地址(MASTER_HOST) 5.master.info文件的作用? 这个文件在从服务器上面用于记录master的ip,连接过去复制二进制日志的账号和密码。二进制日志文件的名字和位置号。 (CHANGE MASTER TO MASTER_HOST='192.168.2.140', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='node2-bin.000001', MASTER_LOG_POS=154;) 6.relay-log.info文件的作用 slave上面sql线程会读取中继日志并且执行操作,relay-log.info记录上一次sql线程读取的位置,与master.info作用类似
主从复制的几个模式
异步复制
MySQL异步复制是主从复制过程中默认的复制模式。主从复制涉及三个线程,master I/O线程、slave I/O线程、slave sql线程。因为是异步复制,所以master事务的提交,不需要经过slave的确认,即master I/O线程提交事务后,不需要等待slave I/O线程的回复确认,master并不保证binlog一定写入到了relay log中;而slave I/O把binlog写入relay log后,由slave sql线程异步执行应用到slave mysql中,slave I/O也不需要slave sql线程的回复确认,并不保证relay log日志完整写入到了mysql中。
总结:master不管slave的死活,只管自己的二进制日志已发出。
半同步复制
基于传统异步存在的缺陷,mysql在5.5版本推出半同步复制,是对传统异步复制的改进。在master事务commit前,必须确保binlog日志已经写入slave 的relay log日志中,收到slave给master的响应后,才能进行事务的commit。但是后半部分的relay log到sql线程仍然属于异步执行。
总结:master管slave一部分死活,确定二进制日志发出并且写入relay log中,之后不管。
同步复制(组复制)
MGR:MySQL group replication 组复制,最低3台,对多9台
基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证,MySQL官方在5.7.17版本正式推出组复制(MySQL Group Replication,简称MGR)。
由若干个节点共同组成一个复制组,一个事务的提交,必须经过组内大多数节点(N / 2 + 1)决议并通过,才能得以提交。如上图所示,由3个节点组成一个复制组,Consensus(共识)层为一致性协议层,在事务提交过程中,发生组间通讯,由2个节点决议(certify)通过这个事务,事务才能够最终得以提交并响应。
引入组复制,主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体),实现了分布式下数据的最终一致性,提供了真正的数据高可用方案(是否真正高可用还有待商榷)。其提供的多写方案,给我们实现多活方案带来了希望。
MGR环境下,服务器数量必须是3台以上,并且是单数,实现2/n+1的算法。
MGR的解决方案现在具备的特性
数据一致性保障:确保集群中大部分节点收到日志
多节点写入支持:多写模式下支持集群中的所有节点都可以写入(但是考虑1到高并发场景下,保证数据高度一致性,生产并没有选择多主写入,使用单主集群)
Fault Tolerance: 确保系统发生故障(包括脑裂)依然可用,双写对系统无影响
MGR的解决方案目前的影响
- 仅支持InnoDB表,并且每张表一定要有一个主键,用于做write set的冲突检测;
- 必须打开GTID特性,二进制日志格式必须设置为ROW,用于选主与write set
- COMMIT可能会导致失败,类似于快照事务隔离级别的失败场景
- 目前一个MGR集群最多支持9个节点
- 不支持外键于save point特性,无法做全局间的约束检测与部分部分回滚
- 二进制日志不支持binlog event checksum
半同步复制操作
操作
1.在master上面安装半同步的插件
root@(none) 20:18 mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)2.临时和永久开启半同步配置和设置超时时间
root@(none) 20:19 mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1;
Query OK, 0 rows affected (0.00 sec)root@(none) 20:20 mysql>SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.02 sec)[root@node2 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1 #添加
rpl_semi_sync_master_timeout=1000 # 1 second 添加刷新一下服务
[root@node2 ~]# service mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL. SUCCESS!
[root@node2 ~]#3.在slave上面也安装插件
root@(none) 11:22 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.71 sec)4.配置半同步
root@(none) 11:23 mysql>SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.04 sec)[root@web-slave mysql]# vim /etc/my.cnf
[root@web-slave mysql]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS!
[root@web-slave mysql]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38-log MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@(none) 20:41 mysql>
5.在slave和master上执行下列语句,查看是否设置成功
root@(none) 20:48 mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME
-> LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+root@(none) 20:21 mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME
-> LIKE '%semi%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
6.验证
master上面
root@(none) 20:44 mysql>create database zyq123;
Query OK, 1 row affected (0.00 sec)root@(none) 20:51 mysql>use zyq123;
Database changed
root@zyq123 20:51 mysql>create table t1(id int);
Query OK, 0 rows affected (0.01 sec)slave上面
root@(none) 20:49 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TENNIS |
| hunan |
| mysql |
| performance_schema |
| sc |
| sys |
| user |
| zyq123 |
+--------------------+
9 rows in set (0.00 sec)root@(none) 20:51 mysql>use zyq123;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
root@zyq123 20:52 mysql>show tables;
+------------------+
| Tables_in_zyq123 |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
GTID介绍
GTID出现之前,在一主多从的复制拓扑中,如果主库宕机,需要从多个从库选择之一作为新主库,这个过程比较复杂。没有一种直接了当的方法找到其它从库对应的新主库二进制日志坐标。通常的做法是先要寻找每个从库复制原主库的最后语句,然后找到新主库中包含该语句的二进制日志文件,其中该语句后的第一个事件位置即为连接新主库的二进制坐标。主要难点在于不存在一个唯一标识指出“复制原主库的最后语句”,于是后来的MySQL中就出现了GTID的概念。
什么是GTID?
Global Transaction Identifier
是在整个复制环境中对一个事务的唯一标识,全局唯一,一个事务对应一个GTID。
它是MySQL 5.6加入的一个强大特性,目的在于能够实现主从自动定位和切换,而不像以前需要指定文件和位置。
比binlog+pos的传统方式,在恢复的时候,以前做过的不在执行,可以节约时间。
在传统的主从复制中,slave端不用开启binlog,但是在GTID主从复制中,必须开启binlog。
为了保证数据的一致性,多线程同时执行一个GTID。
工作原理
1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描
基于GTID的半同步主从复制操作
配置基于GTID的半同步主从复制
查看master上的配置
[root@sc-master ~]# cat /etc/my.cnf
[mysqld_safe][client]
socket=/data/mysql/mysql.sock[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#二进制日志开启
log_bin
server_id = 1#开启半同步,需要提前安装半同步的插件
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second#开启gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
[root@sc-master ~]#
[root@sc-master mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@sc-master mysql]#
配置slave的配置
[root@sc-slave mysql]# cat /etc/my.cnf
[mysqld_safe][client]
socket=/data/mysql/mysql.sock[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8mb4#跳过密码验证操作
#skip-grant-tables
#sanchuang = 1#开启错误日志
#error log
#log_error = tanxue.err#开启通用日志
#general log
#general_log#开启慢日志
#slow query log
#slow_query_log = 1
#long_query_time = 0.001#log bin 二进制日志
log_bin
server_id = 2#每隔15天自动清除日志
expire_logs_days = 15#开启gtid功能
gtid-mode=ON
enforce-gtid-consistency=ON#开启这个可以让其他slave拿自己的二进制日志进行备份
#开启半同步,需要提前安装半同步的插件
log_slave_updates=ON
rpl_semi_sync_slave_enabled=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m scmysql>
[root@sc-slave mysql]#
[root@sc-slave mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@sc-slave mysql]#5.在master上新建一个授权用户,给slave来复制二进制日志
root@(none) 11:19 scmysql>grant replication slave on *.* to 'renxj'@'192.168.2.%' identified by 'Sanchuang1234#';
Query OK, 0 rows affected, 1 warning (0.04 sec)root@(none) 11:23 scmysql>
6.在slave上配置master info的信息
root@(none) 16:33 scmysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 16:33 scmysql>reset slave all; #清除从上面的信息,包括master_info
Query OK, 0 rows affected (0.00 sec)root@(none) 16:33 scmysql>
CHANGE MASTER TO MASTER_HOST='192.168.2.140' ,-> MASTER_USER='slave',-> MASTER_PASSWORD='123456',-> MASTER_PORT=3306,-> master_auto_position=1; #GTID的不同之处 之间是MASTER_LOG_FILE 和 MASTER_LOG_POS
Query OK, 0 rows affected, 2 warnings (0.03 sec)root@(none) 16:33 scmysql>
root@(none) 16:33 scmysql>start slave;
Query OK, 0 rows affected (0.01 sec)root@(none) 16:34 scmysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.197
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-slave-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: sc-master-relay-bin.000002
Relay_Log_Pos: 373
Relay_Master_Log_File: sc-slave-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: 154
Relay_Log_Space: 584
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: 2
Master_UUID: 3082bd74-0f0c-11ed-b3cb-000c29de76cb
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)ERROR:
No query specifiedroot@(none) 16:34 scmysql>
在master上查看
root@(none) 16:35 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.06 sec)
在slave上查看
root@(none) 16:35 scmysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| 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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.02 sec)root@(none) 16:36 scmysql>
root@(none) 16:37 mysql>
验证GTID的半同步主从复制
在master上操作
root@(none) 16:37 mysql>create database tanxue;
Query OK, 1 row affected (0.03 sec)root@(none) 16:37 mysql>use tanxue;
Database changed
root@tanxue 16:38 mysql>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)root@tanxue 16:38 mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0root@tanxue 16:38 mysql>
在slave上查看
root@(none) 16:36 scmysql>use tanxue;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
root@tanxue 16:38 scmysql>show tables;
+------------------+
| Tables_in_tanxue |
+------------------+
| t1 |
+------------------+
1 row in set (0.01 sec)root@tanxue 16:39 scmysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)root@tanxue 16:39 scmysql>
root@tanxue 16:39 scmysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.197
Master_User: renxj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc-slave-bin.000002
Read_Master_Log_Pos: 747
Relay_Log_File: sc-master-relay-bin.000002
Relay_Log_Pos: 966
Relay_Master_Log_File: sc-slave-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: 747
Relay_Log_Space: 1177
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: 2
Master_UUID: 3082bd74-0f0c-11ed-b3cb-000c29de76cb
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3082bd74-0f0c-11ed-b3cb-000c29de76cb:1-3 #gtid的编号
Executed_Gtid_Set: 3082bd74-0f0c-11ed-b3cb-000c29de76cb:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)ERROR:
No query specifiedroot@tanxue 16:39 scmysql>
====
业务量小:推荐使用gtid的半同步主从复制,只要2~3台服务器
业务量大: 推荐使用组复制,至少3,5,7,9台服务器
====但是当我们需要实现级联同步时,即以这样的一个模式,A>B>C实现三级同步时,AB库除了需要设置log-bin参数还需要添加一个参数:log-slave-updates
log-slave-updates参数默认时关闭的状态,如果不手动设置,那么bin-log只会记录直接在该库上执行的SQL语句,由replication机制的SQL线程读取relay-log而执行的SQL语句并不会记录到bin-log,那么就无法实现上述的三级级联同步。