开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> （二）、MySQL主从架构 -> 正文阅读

[大数据]（二）、MySQL主从架构

MySQL主从架构

11.1 mysql replication

? 在生产环境下，如果对数据进行读写操作都是在同一个数据库中进行的。无论是在安全性、高可用性、还是高并发等各个方面都是完全不可能满足实际需求的。因此一般都是通过主从复制(master-slave)的方式来同步数据，保证数据的安全性，再通过读写分离来提升数据库的并发负载能力。

一、什么是mysql replication(mysql主从复制)

? 1、主从复制指的是当主数据库中进行了update、insert、delete操作导致数据发生改变时，变化会实时同步到一个或者多个从数据库（slave）中。

? 2、默认情况下时异步复制、无需维持长连接。

? 3、通过配置可以选择想要同步的库和表。

? 4、replication时MySQL内建的，自身带有。

二、mysql replication的原理

? 简单的说就是master将数据的更新操作写入到二进制日志中，slave同步这些二进制日志到中继（relay）日志中，然后读取中继日志中的内容，把二进制的日志解析成SQL语句并执行，从而保持与主数据库中的数据一致。

三、mysql replication的作用

? 1、读写分离，提供查询服务

? 2、实时灾备，用于故障切换

? 3、水平扩展数据库的负载能力

四、mysql replication是如何工作的

? 1、master将改变记录到二进制日志（binary log）中（这些记录叫做二进制日志事件，binary log events）

? 2、slave将master的binary log events拷贝到它的中继日志(relay log)

? 3、slave根据中继日志中的事件，对slave数据库做相应的操作，使其与主数据库一致

11.2 mysql replication 常见方案

一、一主一从

二、一主多从

? 一主多从，Master负责写操作，其他slave负责读，这种架构最大问题I/O压力集中，多台slave需要从master上同步数据，影响master的IO性能。

三、级联复制

? 级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

例如，使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates

四、互为主备

五、多主一从

11.3 mysql主从复制原理

? MySQL中从复制涉及到三个线程，一个运行在主节点（binary dump thread），其余两个(I/O thread ,SQL thread)运行在从节点

一、主节点 binary log dump 线程

当从节点连接到主节点时，主节点会生成一个binlog dump 线程，用于给从节点发送 binary-log中的内容。在从节点读取binary-log中的内容时，此线程会加锁，当读取完成时，在发送给从节点之前，锁会被释放。

二、从节点I/O线程

? 当从节点上执行了start slave 命令之后，从节点会产生一个I/O线程，用来接受主节点bin log dump线程发送过来的内容，并将内容储存到中继日志中。

三、从节点SQL线程

? SQL线程主要时将relay-log中的二进制转化为sql语句并执行，保持从节点与主节点的一致性。

注意：

? 对于每一个主从连接来说，都需要有三个线程来完成。当主节点又多个从节点进行连接的时候，主节点会分别为从节点创建一个binary log dump 线程，而每个从节点会有属于自己的I/O线程和SQL线程。

主从复制过程

? 1、主数据库上发生更新事件（update、insert、delete）被写道binlog中

? 2、从数据库启动后，产生I/O线程，从库发起连接

? 3、主数据库产生binary log dump 线程将binlog日志发送给从数据库

? 4、从数据库中 I/O线程接收到binlog内容写入到中继日志(relay log)中

? 5、从数据空中的SQL线程将relay log中的内容转换为sql语句并执行。

注意

? 主数据库必须开启binary log（二进制）功能，因为主从同步所有的操作都是基于二进制文件来完成的

11.4 mysql主从复制模式

一、异步模式

? MySQL主从复制默认是异步模式进行的。此模式中主数据库的事务提交是不经过slave确认的，master不关心slave是否接收到master发送的binlog。当slave接受到后写入到中继日志中，再转化为sql执行。如果slave并没有接收到主库发送的binlog，主库也并不知情。

? 如果master宕机，binlog还没有来及被slave接受，而slave备选举为新的主库，这样会导致数据的不一致性。

二、半同步模式

? 半同步模式是针对异步模式的缺陷进行的改进。主库必须接受到其中一个从库接受到binlog生成relay log，并给出反应后才完成commit。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XxrDOF4k-1643987258372)(C:\Users\77259\AppData\Roaming\Typora\typora-user-images\1628927111388.png)]

? 相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，所以，半同步复制最好在低延时的网络中使用。半同步模式不是mysql内置的，从mysql 5.5开始集成，需要master 和slave 安装插件开启半同步模式。.

三、全同步模式

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

11.5 部署mysql主从同步(一主一从)

实验环境：

主机名	ip	版本	角色
mysqlmaster	192.168.116.20	centos7	Master
mysqlslave1	192.168.116.3	centos7	slave

注意！！！注意！！！：看清楚是在哪台虚拟机进行的操作

一、建立时间服务器、同步时间

1、安装NTP

 [root@mysqlmaster ~]# yum -y install ntp

2、配置NTP

当安装好NTP后，会生成ntp.conf文件对其进行修改

[root@mysqlmaster ~]# vim /etc/ntp.conf //添加如下两行
 server 127.127.1.0  
 fudge 127.127.1.0 stratum 8

参数说明：

? server 127.127.1.0 //local clock,就和本地系统时间同步。127.127.1.0在这里是一个IP地址，不是网段。 fudge 127.127.1.0 stratum 8 设置时区为东八区，在安装系统时，选择的时上海时间

3、重启NTP服务

 [root@mysqlmaster ~]# systemctl restart ntpd  //重启ntp服务
 [root@mysqlmaster ~]# netstat -anpt | grep ntpd	//查看ntp服务
 [root@mysqlmaster ~]# systemctl enable netpd	//设置开机自动启动

4、关闭防火墙

 [root@mysqlmaster ~]# systemctl stop firewalld  //关闭防火墙
 [root@mysqlmaster ~]# systemctl disable firewalld  //关闭开机自动启动

5、在从节点上同步时间

 [root@mysqlslave1 ~]# yum -y install nptdate  //安装npt
 [root@mysqlslave1 ~]# /usr/bin/ntpdate 192.168.116.20  //此处的ip为自己的主库ip

二、配置主数据库服务器

1、进去mysql创建测试库以及数据

 mysql> create database HA;		//创建一个数据库
 mysql> use HA;				//进入此数据库
 mysql> create table T1(id int,name varchar(20));	//创建表
 mysql> insert into T1 values(1,'Tom1');	//插入一条数据

2、创建主从复制的授权用户

 mysql> grant replication slave on *.* to slave@192.168.116.3 identified by '123'
//当你的从数据库来复制的时候，使用的是slave用户， slave用户的权限是replication slave  作用到所有库下的所有表

? 注释：192.168.116.3 是自己的从库的ip

3、配置主库的主配置文件my.cnf

 [root@mysqlmaster ~]# vim /etc/my.cnf //添加一下内容
 
 [mysqld]
 ......   //省略部分内容
 log-bin=/data/mysql/log/mysql-bin-master  #启用二进制日志
 server-id=1   #数据库服务器ID标识
 binlog-do-db=HA #可以被从服务器复制的库, 即二进制需要同步的数据库名

4、重启mysql

 [root@mysqlmaster ~]# /etc/init.d/mysqld restart

5、查看master的状态

 mysql> show master status;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin-master.000019	588	HA

这里显示的信息每个人是不一样的。

6、查看二进制日志

 [root@mysqlmaster ~]# ls /data/mysql/log/

7、导出数据库

 [root@mysqlmaster ~]# mysqldump  -uroot -p123 HA >HA.sql //到处数据库
 [root@mysqlmaster ~]# scp HA.sql root@192.168.116.3 //将数据发送给从服务器

三、配置从数据库

1、两台数据库的版本要一致

mysql> show variables like '%version%';   //查看mysql版本

2、测试连接到主服务器是否正常

 [root@mysqlslave1 ~]# mysql -uslave -p123 -h 192.168.116.20  //使用在主库中授权的用户登录
 mysql> show databases; //查看库
 这里只会看到一个information_schema的库  因为用户授权的权限只是复制的权限（replication slave）

3、导入数据库，和主库保持一致

 [root@mysqlslave1 ~]# mysql -uroot -p123 -e "create database HA;" //创建HA库
 [root@mysqlslave1 ~]# mysql -uroot -p123 HA<HA.sql  //将数据导入HA

4、修改从库的my.conf文件

 [root@mysqlslave1 ~]# vim /etc/my.cnf

添加如下配置

 server-id = 2  
 relay-log=/data/mysql/log/relay-log-bin  //中继日志文件的路径名称
 relay-log-index=/data/mysql/log/slave-relay-bin.index  //中继日志索引文件的路径名称

注释： server-id ：从服务器ID号，不要和主ID相同，如果设置多个从服务器，每个从服务器必须有一个唯一的 server-id值，必须与主服务器的以及其它从服务器的不相同。这些ID值能唯一识别复制服务器群集中的每个服务器实例

5、重启服务

 [root@mysqlslave1 ~]#  /etc/init.d/mysqld restart

6、在从库中更改其相关配置

 [root@mysqlslave1 ~]#  mysql -uroot -p123
 mysql> stop slave; //停止slave
 mysql> change master to master_host='192.168.116.20',
						 master_user='slave',master_password='123',
 						 master_log_file='mysql-bin-master.000019',master_log_pos=588;

注释：

? master_host：主库的ip地址

? master_user：在主库中做的具有replication权限的授权用户名

? master_password：主库的数据库密码

? master_log_file：主库的二进制文件

? master_log_pos：主库的二进制文件的其实位置

这里 master_log_file，master_log_pos的值通过上边的命令show master status; 在主库中查看，一定要与主库的查询结果保持一致。

7、启动slave

 mysql> start slave;

四、查看各个部分的状态情况

1、在slave中查看slave状态

mysql> show slave status;

查看其中的关键信息：

? 1）Master_Host：主库的ip地址是否正确

? 2）Master_User：是否使用的是在主库中授权的用户

? 3）Master_Log_File：是否与在主库中进行show master status显示的信息一致

? 4）Slave_IO_Running：从库中的I/O线程是否开启

? 5）Slave_SQL_Running：从库中的SQL线程是否开启

2、在主库中查看是否创建了连接

mysql> show processlist \G;

查看其中关键信息：

? 1）User：用户是否是授予aplication slave权限的用户

? 2）Host：是否是从库的ip地址

? 3）Command：Binlog Dump 是否开启

五、错误分析

出现错误一般为一下情况：

? 1、没有关闭防火墙

解决：

 [root@mysqlmaster ~]# systemctl stop firewalld

? 2、主库在用户授权时，从库的ip写错

解决：

进入主库mysql 进行查看
mysql> select user,host from mysql.user;
查看授权用户对用的ip是否为从库

? 3、在从库中进行更改配置时出错

解决：

进入从库 重新更改配置
mysql> change master to master_host='192.168.116.20',
						master_user='slave',master_password='123',
 						master_log_file='mysql-bin-master.000019',master_log_pos=588; 
master_log_file master_log_pos 这两个值  进入主库进行查看保持与主库一致
mysql> show master status;

注意！！！这里的master_log_file、master_log_po 的值一定要与主库对应

11.6 部署联级复制（M-S-S）

实验环境

主机	IP	系统版本	角色
mysqlmaster	192.168.116.20	CentOS 7	Master
mysqlslave1	192.168.116.3	CentOS 7	Slave 中继
mysqlslave2	192.168.116.2	CentOS 7	Slave

一、同步时间

? 同以上操作，在三台虚拟机上都进行

二、部署Master

1、在主服务器上授权用户

[root@mysqlmaster ~]# mysql -uroot -p123	//进入数据库
mysql> grant replication slave on *.* to slave@'192.168.116.%' identified by '123' //授权用户
mysql> flush privileges; //刷新授权用户

2、创建数据库

 mysql> create database HA;
 mysql> use HA;
 mysql> create table T1(id int,name varchar(20));
 mysql> insert into T1 values(1,'tom1');

3、修改主库配置文件

[root@mysqlmaster ~]# vim /etc/my.cnf
添加以下内容
[mysqld]
………省略部分内容
server-id=1	
binlog-do-db=HA		
log-bin=/data/mysql/log/mysql-bin-master	
sync-binlog=1
binlog-format=row

参数说明

server-id：指定主库实例，每台Mysql服务器中的id不能相同

binlog-do-db：指定要实现复制的库

log-bin：二进制文件的文件位置

sync-binlog：每写缓冲多少次就同步到磁盘

sync_binlog=1：表示同步写缓冲和磁盘二进制日志文件，不使用文件系统缓存，在使用innodb事务引擎时，在复

? 制环境中，为了保证最大的可用性，都设置为“1”，但会对影响io的性能。

binlog-format：格式化二进制文件日志

4、重启服务、查看状态

[root@mysqlmaster ~]# /etc/init.d/mysqld restart
//进入数据库后
mysql> show master status;//显示主服务器的当前binlog文件及事件位置

5、导出主服务器HA库完整备份，拷贝到中继服务器和slave服务器

 [root@mysqlmaster ~]# mysqldump -uroot -p123 -B HA>ha.sql
 [root@mysqlmaster ~]# scp ha.sql root@192.168.116.2:~
 [root@mysqlmaster ~]# scp ha.sql root@192.168.116.3:~

三、部署Slave中继

1、导入数据库

 [root@mysqlslave1 ~]# mysql -uroot -p123 <ha.sql

2、配置从库的配置文件

 [root@mysqlslave1 ~]# vim /etc/my.cnf
 添加以下内容
  …………   //省略部分内容
 server-id=2
 log-slave-updates=1
 log-bin=/data/mysql/log/mysql-bin-slave1
 relay-log=/data/mysql/log/relay-log-bin
 relay-log-index=/data/mysql/log/slave-relay-bin.index

参数说明

server-id：指定主库实例，每台Mysql服务器中的id不能相同

log-slave-updates：在上面讲到的中从复制过程中，从库会通过I/O线程将读取主库的二进制文件并写到自己的中继日志中，在这里的Slave不仅需要接收，它还需要往下传递二进制文件。所以添加这个参数后，中继从库在把接收到的二进制文件写入到从库时，同时也会写到自己的二进制文件中。在级联复制中，这个参数是必要的。

log-bin：指定二进制文件位置

relay-log：指定中继文件位置

relay-log-index：指定中继文件索引位置

3、重启mysql

 [root@mysqlslave1 ~]# /etc/init.d/mysqld restart

4、进行从库的授权

 [root@mysqlslave1 ~]# mysql -uroot -p123
 mysql> stop slave;
 msyql> change master to master_host='192.168.116.20',master_user='slave',
 		master_password='123',master_log_file='mysql-bin-master.000001',
 		master_log_pos=154;
 mysql> start slave;

上边的实验已经介绍过这些参数的值，这里不再过多讲解。

5、查看中继服务的状态

 mysql> show slave status \G 
 //通过上面的方法进行检查其中的参数 I/O、SQL 两个线程的状态

6、授权用户（给mysqlslave2使用）

mysql> grant replication slave on *.* to 'slave'@'192.168.116.%' identified by '123'
mysql> flush privileges;
mysql> show master status; //查看slave中继服务器的当前的binlog文件即事件位置

四、部署slave

1、导入数据库

 [root@mysqlslave2 ~]# mysql -uroot -p123 <ha.sql

2、修改配置文件

 [root@mysqlslave2 ~]# vim /etc/my.cnf
 //添加以下内容
  [mysqld]
 ………省略部分内容
 server-id = 3
 relay-log=/data/mysql/log/relay-log-bin 
 relay-log-index=/data/mysql/log/slave-relay-bin.index

3、重启mysql

 [root@mysqlslave2 ~]# /etc/init.d/mysqld restart

4、授权

 [root@mysqlslave2 ~]# mysql -uroot -p123 //进入数据库
 mysql> stop slave;
 mysql> change master to master_host='192.168.116.3',master_user='slave',
 		master_password=123,master_log_file='mysql-bin-slave1.000001',
 		master_log_pos='448';
 mysql> start slave;

5、查看状态

 mysql> show slave status\G

根据上述的主从实验进行状态判断，判断状态都正常的情况下，即完成了级联复制的部署

………省略部分内容
server-id = 3
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index


3、重启mysql

```bash
 [root@mysqlslave2 ~]# /etc/init.d/mysqld restart

4、授权

 [root@mysqlslave2 ~]# mysql -uroot -p123 //进入数据库
 mysql> stop slave;
 mysql> change master to master_host='192.168.116.3',master_user='slave',
 		master_password=123,master_log_file='mysql-bin-slave1.000001',
 		master_log_pos='448';
 mysql> start slave;