mysql 主从提高性能_【MySQL主从复制】提高服务性能，看看叭？

aligaduo 个人博客​www.zhangxiaoshuai.fun

MySQL Replication

主从复制允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。

为什么要进行复制？

为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库。分散对数据库的访问压力，提升整个系统的性能和可用性，降低大访问量引发数据库宕机的故障率。

下图为MySQL官网给出的主从复制的原理图，从图中可以简单的了解读写分离及主从同步的过程。

复制策略：同步策略：Master要等待所有Slave应答之后才会提交(MySql对DB操作的提交通常是先对操作事件进行二进制日志文件写入然后再进行提交)。

半同步策略：Master等待至少一个Slave应答就可以提交。

异步策略：Master不需要等待Slave应答就可以提交。

延迟策略：Slave要至少落后Master指定的时间。

复制的优势：性能方面：MySQL复制是一种Scale-out方案，也即“水平扩展”，将原来的单点负载扩散到多台Slave机器中去，从而提高总体的服务性能。在这种方式下，所有的写操作，当然包括UPDATE操作，都要发生在Master服务器上。读操作发生在一台或者多台Slave机器上。这种模型可以在一定程度上提高总体的服务性能，Master服务器专注于写和更新操作，Slave服务器专注于读操作，我们同时可以通过增加Slave服务器的数量来提高读服务的性能。——实现“读”与“写”分离

故障恢复：同时存在多台Slave提供读操作服务，如果有一台Slave挂掉之后我们还可以从其他Slave读取，如果配置了主从切换的话，当Master挂掉之后我们还可以选择一台Slave作为Master继续提供写服务，这大大增加了应用的可靠性。

数据分析：实时数据可以存储在Master，而数据分析可以从Slave读取，这样不会影响Master的性能。

复制原理：

MySQL中有一个日志叫做binary Log(二进制日志)。这个日志的主要功能就是记录所有修改的数据库的SQL语句。

而主从复制的原理就是：从服务器把主服务器中的bin log文件拿到，执行一遍，这样就可以与主服务器上的数据保持一致了。

复制过程：

1.开启主节点中的二进制日志，记录对数据库修改的所有事件；

2.从节点开启一个I/O线程，通过mysql协议，请求主节点的binLog文件中的事件；

3.主节点启动一个dump线程，检查自己bin log文件中的事件，跟从节点请求的位置对比，如果从节点请求没有位置参数，那么主节点就会从binLog文件的第一个事件开始，顺序将所有事件传输给从节点；

4.从节点将主节点中发送过来的数据存储到Relay log(中继日志)中，并记录该次请求到主节点的具体是哪一个二进制日志和文件内部的哪个位置；

5.从节点启动另一个线程(sql Thread)，把刚刚存储到中继日志中的文件读取出来，并在本地执行一次。

复制中线程的作用：

从节点：I/O Thread: 从 Master 节点请求二进制日志事件，并保存于中继日志中。

sql Thread: 从Relay log 中读取日志事件并在本地完成重放。

主节点：

·Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程，用于向从节点发送二进制事件。

主从节点配置步骤：

主节点：

1. 启用二进制日志。

2. 为当前节点设置一个全局唯一的server_id。

3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

1. 启动中继日志。

2. 为当前节点设置一个全局唯一的server_id。

3. 使用有复制权限的用户账号连接至主节点，并启动复制线程。

搭建环境：

主节点：192.168.232.128

从节点：192.168.232.129

mysql版本：mysql -5.7.30

linux版本：CentOS 7 64bit

主节点配置：

vim /etc/my.cnf

在上面的配置文件中增加如下指令：

log-bin = mysql-bin

server-id =1

innodb-file-per-table =ON

skip_name_resolve=ON

保存退出

service mysqld restart

登录数据库，查看二进制日志是否在开启状态

show global variables like '%log%';

查看主节点二进制日志列表

查看主节点的server_id是否设置成功

show global variables like '%server%';

将从节点添加到主节点中，使其具有复制权限

grant replication slave,replication client on *.* to 'testUser'@'192.168.232.129' identified by '123456';

如果在添加过程中出现：mysql密码策略问题导致无法创建，可以去修改mysql的密码校验等级或者和密码长度即可。

一定要确保3306端口是打开状态！！！

从节点配置：

开启中继日志

vim /etc/my.cnf

在上面的配置文件中添加下面指令：

relay-log=relay-log

relay-log-index=relay-log.index

server-id=2

innodb_file_per_table=ON

skip_name_resolve=ON

然后可以再通过上述一些命令查看设置是否生效。

添加 主节点主机，访问主节点的用户名及密码，主节点二进制文件信息。

注意：主节点的二进制文件一定要是主节点二进制列表中的最后一个二进制文件。(show master logs;)

CHANGE MASTER TO MASTER_HOST='192.168.232.128',MASTER_USER='testUser',MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=627;

启动从节点的I/O线程和SQL线程：

使用命令 show slave status\G;查看两个线程的状态；

使用命令start slave;同时启动两个线程，启动之后确定一下两个的状态是否为yes；

测试：

在主服务器中创建两个测试数据库，然后去从服务器中查看该数据库是否存在；

Master：

Slave:

自己挖的坑，哭着也要埋了：

配置好主、从节点之后，直接就去测试了；我先是在主库中创建了一个数据库，然后去从库中查看是否存在该库，结果，一根毛都没有……。

1.我先是查看主库中的配置问题，一切ok；

2.然后去到从库中查看，配置没有问题啊，线程在配置的时候也好像打开了啊；

3.show slave status\G;查看线程状态，Slave_IO_Running: connecting,这是什么鬼？io线程就没有连接到主库上，一直处于正在连接状态；

4.突然想到是不是因为防火墙的问题(也不是突然想到，存在这样问题的原因是很多的，通过排查之后定位到了防火墙)，因为是两个服务器进行交互，而linux开启的端口有限，很多都是需要自己去手动开启的。去主库查看，还真是3306端口没开放;

firewall-cmd --state //查看防火墙状态

systemctl stop firewalld.service

systemctl disable firewalld.service

systemctl start firewalld.service

firewall-cmd --zone=public --add-port=3306/tcp --permanent//指定3306端口永久开放

firewall-cmd --reload//重启防火墙

一顿操作之后，终于恢复了正常；

主从复制架构中应注意的问题：

从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读

在从服务器上设置：

read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。

阻止所有用户：

mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？

1：在主节点设置参数

sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。

如果用到innode 存储引擎:

innodb_flush_logs_at_trx_commit=ON(刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。)

innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能)

sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。

微信公众号：