Mysql主从复制

最新推荐文章于 2024-08-01 18:56:05 发布

橘子没有壳

最新推荐文章于 2024-08-01 18:56:05 发布

阅读量246

点赞数 1

分类专栏： linux企业级文章标签： mysql 基于GTID的主从复制异步复制半同步复制组复制（全同步复制）

本文链接：https://blog.csdn.net/qq_37382077/article/details/97511547

版权

linux企业级专栏收录该内容

53 篇文章 0 订阅

订阅专栏

文章目录

mysql的主从复制(默认异步)
基于GTID的主从复制
mysql半同步复制
mysql组复制（全同步复制）

mysql的主从复制(默认异步)

异步复制：
主库将所有的操作记录到日志文件中，从库IO线程获到日志文件保存到自己的服务器，称为中继日志；SQL线程将中继日志的操作回放一遍，然后从库执行所有的操作，来保持与主库同步。
异步复制效率更高，因为异步复制master不需要通知等待slave进行同步，就进行下一步操作。
半同步复制：一个slave完成就OK，相比异步复制，提高了数据完整性。
全同步复制：master在所有slave同步完成后才进行下一步操作，会影响用户体验。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。
因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

1)安装需要的包

yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm 
mysql-community-common-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm 
mysql-community-server-5.7.24-1.el7.x86_64.rpm
#安装后会替换mariadb相关的库文件

#server2也需要装

2)修改配置文件，配合官方文档看

vim /etc/my.cnf

log-bin=mysql-bin 	##文档最后加，二进制日志复制，id号来区分主机
server-id=1

3)启动mysql

systemctl start mysqld

4)会生成一个临时密码

cat /var/log/mysqld.log | grep password

2019-07-29T11:33:17.037000Z 1 [Note] A temporary password is generated for root@localhost: yVg9y08iQ;=>

5)尝试连接，但是发现不能查看库

mysql -uroot -pyVg9y08iQ;=> #使用上面生成的临时密码
mysql> show databases;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

6)安全初始化

mysql_secure_installation

重置密码，要求有大小写，数字，特殊字符，第一个选 n（不重设密码），其他全选 y
完成后可以登录数据库

7)创建并授权用来做复制的用户
在Master的数据库中建立一个备份帐户：
每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件 master.info 中

mysql> create USER 'repl'@'172.25.40.%' IDENTIFIED BY 'Westos+123';

mysql> grant replication slave on *.* to repl@'172.25.40.%' identified by 'Westos+123';

mysql> show plugins;	##查看插件，因为有密码插件，所以密码必须设置为复杂的

mysql> show master status;	##查看master状态,二进制文件及位置

±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000002 | 1258 | | | |
±-----------------±---------±-------------±-----------------±------------------+

在物理机上尝试连接master的数据库，测试repl帐号

mysql -h 172.25.40.1 -urepl -pWestos+123

可以登录，但是查看不到任何信息，因为没有权限。

在server2上同样初始化mysql
修改/etc/my.cnf

vim /etc/my.cnf

server-id=2	  ##文件最后加

启动mysql

systemctl start mysqld

在 server2 上配置master信息

mysql> change master to master_host='172.25.40.1', master_user='repl', master_password='Westos+123', master_log_file='mysql-bin.000002', master_log_pos=1258;

MASTER_LOG_POS 是日志的开始位置
master_log_pos 的值是先在server1上执行show master status看到的信息

mysql> start slave;

mysql> show slave status\G	##查看主从复制状态

#这两个参数是Yes，表示成功

在这里插入图片描述

8)创建新数据，测试主从同步是否生效
注意：写操作只能在master节点上做，因为master节点不会去同步slave节点的内容

mysql> create database monster;	##在server2上发现也能看到monster库
mysql> use monster;
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(15) not null);	 ##建表

mysql> desc usertb;	##查看表信息

mysql> insert into usertb values ('user1','123');	##插入数据

mysql> select * from usertb;	##查看

在slave节点（server2）测试：
在这里插入图片描述

基于GTID的主从复制

由于同一事务的GTID在所有节点上的值一致
我们都不需要知道GTID的具体值
‘前提：需要做好前面的binlog复制’
在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步

从服务器连接到主服务器之后，把自己执行过的GTID(Executed_Gtid_Set)<SQL线程> 、获取到的GTID(Retrieved_Gtid_Set）<IO线程>发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主，先change到同步最成功的那台从服务器，等把GTID全部补全了，就可以把它提升为主了

show variables like 'log_%'; #查看二进制日志

1)先在server1上添加配置

vim /etc/my.cnf ##在文件最后添加

gtid_mode=ON
enforce-gtid-consistency=true

mysql> select * from gtid_executed;
##是空的，因为还没重启 ##在slave上面看Empty set (0.00 sec)

[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# mysqlbinlog mysql-bin.000002	##可以看到之前做的操作都在里面

[root@server1 mysql]# cat auto.cnf 	##看到server1的uuid
[auto]
server-uuid=f84e8de1-38a2-11e9-b78c-5254009afece

systemctl restart mysqld

2)在server2上配置（mysql5.6 slave必须开启binlog日志但是5.7中不是必须的）

gtid_mode=ON(必选)
enforce-gtid-consistency=true（必选）
log_bin=ON（可选）–高可用切换，最好设置ON
log-slave-updates=ON（可选）–高可用切换，最好设置ON

vim /etc/my.cnf ##在文件最后添加

gtid_mode=ON
enforce-gtid-consistency=true

systemctl restart mysqld ##重启

#先在server2上查看主从复制状态是否正常

[root@server2 mysql]# pwd
/var/lib/mysql
[root@server2 mysql]# cat relay-log.info	#查看relay-log,通过回放中继日志来同步master相同的操作

mysql> stop slave;	##先停止复制


mysql> CHANGE MASTER TO		##修改master信息
    -> MASTER_HOST = '172.25.40.1',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'Westos+123',
    -> MASTER_AUTO_POSITION = 1;	##启用gtid，它是自动的

mysql> start slave;

mysql> show slave status\G	##查看状态，
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set:

可以看到下面两个参数是空的，因为还没有自动跟踪和执行的动作。
在这里插入图片描述

在server1上插入数据：

mysql> insert into usertb values ('user2','222');

再在server2上查看状态

mysql> show slave status\G;

     Retrieved_Gtid_Set: a89a2873-b1f4-11e9-9732-525400d55165:1
       Executed_Gtid_Set: a89a2873-b1f4-11e9-9732-525400d55165:1

发现这两个参数变了，从1位置开始复制的

在这里插入图片描述
再查看gtid模式复制的起始和结束位置

mysql> use mysql;

   mysql> select * from gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| a89a2873-b1f4-11e9-9732-525400d55165 |              1 |            1 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

server2同步过来了
在这里插入图片描述

mysql半同步复制

异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

在2010年MySQL 5.5版本之前，一直采用的是这种异步复制的方式。主库的事务执行不会管备库的同步进度，如果备库落后，主库不幸crash，那么就会导致数据丢失。于是在MySQL在5.5中就顺其自然地引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log中

参照官方文档

1）正常的复制为：事务一（t1）写入binlog buffer；dumper线程通知slave有新的事务t1；binlog buffer进行checkpoint；slave的io线程接收到t1并写入到自己的的relay log；slave的sql线程写入到本地数据库。这时，master和slave都能看到这条新的事务，即使master挂了，slave可以提升为新的master。

2）异常的复制为：事务一（t1）写入binlog buffer；dumper线程通知slave有新的事务t1；binlog buffer进行checkpoint；slave因为网络不稳定，一直没有收到t1；master挂掉，slave提升为新的master，t1丢失。

3）很大的问题是：主机和从机事务更新的不同步，就算是没有网络或者其他系统的异常，当业务并发上来时，slave因为要顺序执行master批量事务，导致很大的延迟。

为了弥补以上几种场景的不足，MySQL从5.5开始推出了半同步复制。相比异步复制，半同步复制提高了数据完整性，因为很明确知道，在一个事务提交成功之后，这个事务就至少会存在于两个地方。即在master的dumper线程通知slave后，增加了一个ack（消息确认），即是否成功收到t1的标志码，也就是dumper线程除了发送t1到slave，还承担了接收slave的ack工作。如果出现异常，没有收到ack，那么将自动降级为普通的复制，直到异常修复后又会自动变为半同步复制。

半同步复制—操作步骤：

1.在主库上（server1）安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

2.查看插件是否安装

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

3.激活插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled =1;

4.在slave节点上（server2）也安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

5.激活插件

SET GLOBAL rpl_semi_sync_slave_enabled =1;

6.重启IO线程使半同步生效

mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

7.在master节点上查看半同步是否生效

mysql> show status like '%rpl%';

看到

| Rpl_semi_sync_master_status                | ON    |
表示已开启半同步

mysql> show variables like '%rpl%';

+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         | 是否开启半同步
| rpl_semi_sync_master_timeout              | 10000      | 切换为异步复制的timeout，10s
| rpl_semi_sync_master_trace_level          | 32         |用于开启半同步复制模式时的调试级别,默认是32
| rpl_semi_sync_master_wait_for_slave_count | 1          |至少有N个slave接收到日志
| rpl_semi_sync_master_wait_no_slave        | ON         |是否允许master 每个事物提交后都要等待slave的receipt信号。默认为on 
| rpl_semi_sync_master_wait_point           | AFTER_SYNC | 等待的point
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+--------控制stop slave 的执行时间，在重放一个大的事务时,突然执行stop slave命令， stop slave会执行很久,这时可能产生死锁或阻塞,严重影响性能----+默认延迟10s

具体参数可以看官方文档

8.在slave节点上查看

mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |是否开启半同步
| rpl_semi_sync_slave_trace_level | 32       |用于开启半同步复制模式时的调试级别,默认是32
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+

9.停止slave上的io线程再测试

mysql> STOP SLAVE IO_THREAD;

10.在master上插入数据

mysql> use monster;
mysql> insert into usertb values ('user3','333');
Query OK, 1 row affected (10.00 sec)

在这里插入图片描述
#等待10s才成功，因为上面超时时间是10s，10s后如果没有收到slave节点的返回，就会切换到异步复制

查看半同步状态也是off，待同步的事务也是1

mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| 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                | OFF   |
| 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     |
+--------------------------------------------+-------+

再次插入时就不会延迟，因为已经是异步了

mysql> insert into usertb values ('user3','333');
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述
11.slave上开启io线程

mysql> START SLAVE IO_THREAD;

查看进程

mysql> show processlist;输出结果显示了有哪些线程在运行

########################################################################

说明各列的含义和用途 id列:一个标识 user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句
host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户 db列:显示这个进程目前连接的是哪个数据库
command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）
time列:此这个状态持续的时间，单位是秒 state列:显示使用当前连接的sql语句的状态

########################################################################

查看表信息，已经复制过来
在这里插入图片描述

mysql组复制（全同步复制）

server3安装mysql。
server1（master）节点：

1.关闭mysqld

systemctl stop mysqld

2.删除mysql数据

rm -fr /var/lib/mysql/*

3.修改配置文件(server1,2,3都修改) 参照官网
避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错，在从服务器上实现事故安全功能，增加配置：
master_info_repository=TABLE
relay_log_info_repository=TABLE

对于多源复制是必须的，不然会出现错误：
ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

vim /etc/my.cnf

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id=1 # server2：server_id=2；server3：server_id=3；
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE #关闭binlog校验
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW #组复制依赖基于行的复制格式

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01"	#这里写不是群组里的任何一个UUID
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.40.1:24901" #本机ip
loose-group_replication_group_seeds= "172.25.40.1:24901,172.25.40.2:24901,172.25.40.3:24901"
loose-group_replication_bootstrap_group=off	##插件是否自动引导，这个选项一般都要off掉，只需要由发起组复制的节点开启，并只启动一次，如果是on，下次再启动时，会生成一个同名的组，可能会发生脑裂
loose-group_replication_ip_whitelist="127.0.0.1,172.25.40.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF  #后两行是开启多主模式的参数

4.启动mysqld

systemctl start mysqld

5.初始化数据库

[root@server1 mysql]# grep password /var/log/mysqld.log

登录数据库，先修改密码

mysql> alter user root@localhost identified by 'Westos+123';

6.配置看官网复制

mysql> SET SQL_LOG_BIN=0;	#关闭二进制日志，防止传到其他server上
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+123';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;	##开启日志

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+123' FOR CHANNEL 'group_replication_recovery'; # server1也会做slave节点

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> SHOW PLUGINS;	##查看插件
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

mysql> SET GLOBAL group_replication_bootstrap_group=ON;	##组复制发起节点开启这个参数

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SELECT * FROM performance_schema.replication_group_members;	##查看server1是否online

注意：先不要添加数据最后测试的时候再添加数据

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);

mysql> INSERT INTO t1 VALUES (1, 'Luis');

mysql>  SELECT * FROM t1;

添加server2到组内
server2上：

[root@server2 mysql]# systemctl stop mysqld

rm -fr /var/lib/mysql/*

vim /etc/my.cnf #参考server1

启动mysqld

systemctl start mysqld

初始化

grep password /var/log/mysqld.log

进入数据库

 mysql> alter user root@localhost identified by 'Westos+123'; #修改root用户密码

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+123';

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+123' FOR CHANNEL 'group_replication_recovery';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#这里不需要做server1上做的SET GLOBAL group_replication_bootstrap_group=ON;

mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
'这里开启组复制可能会报错,查看mysql日志'
'cat /var/log/mysqld.log ,找到解决办法'
2019-07-29 T08:51:52.838185Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

mysql -pWestos+123

mysql> STOP GROUP_REPLICATION;

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;  ##直接做这一步 不用等报错

mysql> START GROUP_REPLICATION;

恢复正常

再配置server3，步骤和server2配置相同

配置好后在server1上查看

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 43a9e177-46c2-11e9-9b8a-52540039a8e5 | server3     |        3306 | ONLINE       |
| group_replication_applier | 926e543f-4635-11e9-bb87-525400cf2a01 | server2     |        3306 | ONLINE       |
| group_replication_applier | de5a155a-462d-11e9-b9df-5254004c61ed | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

看到3台都是online，表示正常
这时在任何一个节点都可以看到刚才插入的数据
在任何节点写入数据，其他节点也能看到
如在server3上：

mysql> INSERT INTO t1 VALUES (2, 'wds');

在server2和server1上查看：
mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | wds  |
+----+------+

可以看到刚插入的数据