mysql 主从 应用场景_MySQL主从复制与读写分离（原理适用场景+实践操作）

MySQL主从复制与读写分离

一、前言

​ 前面我们已经对MySQL数据库的安装、命令、备份、索引、事务以及存储引擎等各个方面有了初步的理解，而今天我们将从宏观的角度来理解什么是MySQL数据库的主从复制与读写分离。

二、场景描述

​ 在实际的生产环境中，如果对MySQL数据库的读与写都在一台服务器上进行操作，无论是在安全性，高可用性、还是高并发性等诸多方面都是无法满足的；而这就需要对MySQL数据库进行主从复制与读写分离。

​ 我们使用一个架构图来引出MySQL数据库主从复制的原理以及其作用，请看下图：

服务器作用：

MySQL主服务器：负责数据的写入；

MySQL从服务器：同步主服务器的数据并进行数据的轮循读取；

​ 那么这三台具备相同服务的MySQL服务器就称为“MySQL群集”。我们可以从上图中看出，这样的安排实现数据同步的基础上，实现数据的读写分离，从而在保证数据的可靠性的同时也大大减轻了主服务器的压力。

​ 下面我们对MySQL的主从复制和读写分离进行逐一介绍并给出配置实例。

三、MySQL主从复制原理

​ MySQL主从复制与读写分离有着紧密的联系，可以这么说，MySQL的读写分离的实现需要基于主从复制的基础之上。

3.1MySQL支持的复制类型

基于语句的复制；——基于SQL语言命令的复制形式，使用SQL命令执行复制，效率高

基于行的复制；——复制数据库变化的内容，不是执行命令

混合类型的复制；——默认采用语句类型的复制，如果发现有不确定问题等其他原因造成无法复制的情况再进行基于行的复制

3.2MySQL复制的工作过程

我们通过下图来理解MySQL主、从服务器是如何进行复制同步的过程的。

1)首先，MySQL主服务器在更新数据库或其他进行数据库相关操作时，会在二进制日志文件中记录这些改变(这我们在前面的增量备份与恢复的文章中进行了讲述，log-bin的设置以及如何使用mysqladmin命令刷新该日志。)当写入日志完成后，主服务器会告知存储引擎提交事务；

2)MySQL从服务器会将主服务器的二进制日志文件(Binary log)复制到其中继日志(Relay log)中。中继日志通常存放在系统缓存中，因此中继日志的开销很小；

3)从服务器通过自身线程从中继日志中读取事件，更新自身的日志文件使其与主服务器中的数据一致。

ps:复制过程中有一个很重要的限制，即在从服务器上复制是串行化的，这就表明主服务器上的并行更新操作不能在从服务器上并行操作。

四、MySQL主从复制配置流程与实际操作

4.1主从复制所需环境

所需设备(我们在虚拟机上模拟进行配置)清单：

Centos7一台：作为主服务器——master，ip地址为192.168.68.133

Centos7两台：作为从服务器——slave1、slave2IP地址分别为192.168.68.129、192.168.68.132

所需安装服务：

安装并且配置ntp服务、都安装了MySQL5.7版本的数据库

4.2主从复制具体流程步骤

4.2.1准备工作

​ 首先，我们联想一下增量备份的恢复操作，我们都是依赖于data目录下的二进制日志文件，通过两种方法实现的，其中一种就是根据时间节点进行备份恢复操作的。那么我们需要进行MySQL主从服务器复制，就需要先同步所有MySQL服务器的系统时间。

4.2.1.1主服务器准备工作的配置

1)服务器名称设置以区别

hostnamectl set-hostname master

su

2)安装配置ntp服务

yum install ntp -y

#修改ntp服务配置文件，添加下面的两句

vim /etc/ntp.conf

server 127.127.68.0 #服务器本地网段，127.127表示的就是192.168

fudge 127.127.68.0 stratum 8 #时区设置(东八区)

#保存退出

systemctl start ntpd

#可以使用netstat -nutp | grep ntpd 命令查看服务开启状态

3)关闭防火墙和SELinux功能

systemctl stop firewalld

setenforce 0

4.2.1.2两台从服务器的准备工作的配置(二者步骤一致)

1)服务器名称设置

hostnamectl set-hostname slave1(slave2)

su

2)两台从服务器上安装ntp和ntpdate服务并开启服务

yum install ntp ntpdate -y

systemctl start ntpd

3)两台从服务器上关闭防火墙和SELinux功能

systemctl stop firewalld

setenforce 0

4)使用ntpdate命令进行时间同步

[root@slave1 ~]# /usr/sbin/ntpdate 192.168.68.133

9 Jan 15:35:13 ntpdate[67450]: the NTP socket is in use, exiting

4.2.2MySQL服务的配置

4.2.2.1主服务器上MySQL的修改与配置

1)修改mysql的主配置文件并重启mysql服务

vim /etc/my.cnf

#配置如下：

log-bin = master-bin #二进制日志文件 master-bin可以自己设置

server-id = 1 #服务器的id号，用于区别

log-slave-updates=true #开启从服务器更新日志功能(结合复制流程连接)

systemctl restart mysqld.service

2)进入数据库进行权限设置(授权)与刷新

[root@lokott ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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.

mysql> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;#刷新系统权限表

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000001 | 603 | | | |

+-------------------+----