MySQL 怎么搭建主从 mysql主从架构原理

转载

智能开发者 2023-08-30 11:46:14

文章标签 MySQL 怎么搭建主从 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;

5、查看状态