您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL从零到有27:主从复制(原理 + 实践)

时间:10-09来源:作者:点击数:
CDSY,CDSY.XYZ

概念

主从复制,是指建立一个和主数据库完全一样的数据库环境(称为从数据库),并将主库的操作行为进行复制的过程:将主数据库的DDL和DML的操作日志同步到从数据库上,

然后在从数据库上对这些日志进行重新执行,来保证从数据库和主数据库的数据的一致性。

为什么要做主从复制

1、在复杂的业务操作中,经常会有操作导致锁行甚至锁表的情况,如果读写不解耦,会很影响运行中的业务,使用主从复制,让主库负责写,从库负责读。

即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运行。

2、保证数据的热备份,主库宕机后能够及时替换主库,保障业务可用性。

3、架构的演进:业务量扩大,I/O访问频率增高,单机无法满足,主从复制可以做多库方案,降低磁盘I/O访问的频率,提高单机的I/O性能。

4、本质上也是分治理念,主从复制、读写分离即是压力分拆的过程。

5、读写比也影响整个拆分方式,读写比越高,主从库比例应越高,才能保证读写的均衡,才能保证较好的运行性能。读写比下的主从分配方法下:

读写比 主库 从库
50:50 1 1
66.6:33.3 1 2
80:20 1 4
-- -- -- -- -- --

主从复制的原理

当在从库上启动复制时,首先创建I/O线程连接主库,主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程,I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去,之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用,

如下图所示:

细化一下有如下几个步骤:

1、MySQL主库在事务提交时把数据变更(insert、delet、update)作为事件日志记录在二进制日志表(binlog)里面。

2、主库上有一个工作线程 binlog dump thread,把binlog的内容发送到从库的中继日志relay log中。

3、从库根据中继日志relay log重做数据变更操作,通过逻辑复制来达到主库和从库的数据一致性。

4、MySQL通过三个线程来完成主从库间的数据复制,其中binlog dump线程跑在主库上,I/O线程和SQL线程跑在从库上。拥有多个从库的主库会为每一个连接到主库的从库创建一个binlog dump线程。  

搭建主从实例

我们这边在个人PC机上进行MySQL主从复制的搭建测试,所以使用Docker会更方便。有如下优势:

1、可以节省资源。

2、相对于Docker来说,虚拟机搭建对机器配置有要求,且安装MySQL步骤繁琐。

3、一台机器上可以运行多个Docker容器,所以我们可以部署多个MySQL服务。

4、Docker容器之间相互独立,有独立ip,互不冲突

5、Docker使用步骤简便,启动容器在秒级别。

Daocker安装

我这边是mac机,以此作为示范。可以用Homebrew 进行安装,也可以手动下载安装。

手动下载的话可以点击以下链接下载 Edge Docker for Mac。

如同Mac OS 其它软件一样,安装也非常简单,双击下载的 .dmg 文件,然后将鲸鱼图标拖拽到 Application 文件夹即可。 

从应用中找到 Docker 图标并点击运行。去Docker Hub上去注册一个Docker ID,进行登录即可。

打开终端,可以查看Docker相关的版本及相关信息。可以输入如下信息:

# 查看docker版本
docker --version
# 查看docker基本信息
docker info 

搭建主从服务器

1、拉取docker的MySQL镜像,这边以5.7的版本为准:

docker pull mysql:5.7    

2、使用此镜像启动主库容器:

docker run -p 3307:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 

从这边可以看出,Master主库容器对外映射的端口是3307,账号默认root,密码是123456,执行完之后,docker中运行了一个名为master的MySQL实例。

docker容器是相互独立的,每个容器有其独立的ip,所以不同容器使用相同的端口并不会冲突。这里我们应该尽量使用mysql默认的3306端口,否则可能会出现无法通过ip连接docker容器内mysql的问题。

因为容器内的3306端口映射的对外端口是3307,所以外部主机可以通过 宿主机ip:3307 访问到MySQL的服务,而实际是访问容器内MySQL的3306端口,密码就是我们前面设置好的123456。

登录完之后可以看到一个空库,这个就是我们在docker里面拉的MySQL镜像。

3、同样的道理,我们继续建一个slave(从库)的MySQL实例。注意名称为slave,端口为3308

docker run -p 3308:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 

4、主从都搭建完成之后,我们可以使用  docker ps 来查看当前运行的容器信息,如下:

注意下第一个参数为 container id,是我们后续进入服务进行配置的容器识别编号。 

配置主服务器(Master)

首先,进入到 Master 服务器。

wengzhihua@B000000147796DS ~ % docker exec -it 777fe9ce7f9d /bin/bash
root@777fe9ce7f9d:/# 

这边注意:777fe9ce7f9d 是 master 的 container id,然后查看MySQL的状态。

root@777fe9ce7f9d:/# service mysql status
[info] MySQL Community Server 5.7.35 is running.

然后到MySQL的目录下去修改配置,切换到/etc/mysql目录下,然后 vi my.cnf 对my.cnf进行编辑:

root@777fe9ce7f9d:/# cd /etc/mysql
root@777fe9ce7f9d:/etc/mysql# vi my.cnf
bash: vi: command not found 

此时会报出 bash: vi: command not found ,需要我们在docker容器内部自行安装vim。我们使用  apt-get install vim  命令来安装vim

root@777fe9ce7f9d:/etc/mysql# apt-get install vim
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package vim 

这边又提示 Unable to locate package vim。

执行 apt-get update ,然后再次执行 apt-get install vim 即可成功安装vim。

然后我们就可以使用vim编辑my.cnf,在my.cnf中添加如下配置:

[mysqld]
## 设置server_id,一般设置为IP,同一局域网内使用唯一值即可,注意要保证唯一,这边我们暂且使用主库的映射端口,方便识别
server_id=3307
## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)
log-bin=test-mysql-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

配置完成之后重启服务:

service mysql restart 

这个命令会使得容器停止,重新启动就可以了。

接下来创建数据同步用户:

root@777fe9ce7f9d:/etc/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

这里主要是要授予用户 slave,REPLICATION SLAVE权限和REPLICATION CLIENT权限,用于同步数据。

配置从服务器(Slave)

同主服务器一样的操作方式,先进入MySQL配置文件

wengzhihua@B000000147796DS ~ % docker exec -it d4ba5e063deb /bin/bash
root@d4ba5e063deb:/# cd etc/mysql
root@d4ba5e063deb:/etc/mysql# vi my.cnf  

然后配置从库的信息: 

[mysqld]
## 设置server_id,一般设置为IP,注意要唯一,这边我们使用从库的映射端口,方便识别
server_id=3308
## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
log-bin=test-mysql-slave1-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1

配置完成之后重启服务  service mysql restart  :

root@d4ba5e063deb:/etc/mysql# service mysql restart
[info] Stopping MySQL Community Server 5.7.35.
...
[info] MySQL Community Server 5.7.35 is stopped.
[info] Re-starting MySQL Community Server 5.7.35. 

跟上面一样,这个命令会使得容器停止,重新启动就可以了。 

完成Master和Slave的连接

注意,需要保证 Master 和 Slave 除了不同步的数据库,其他数据库的数据要一致。

1、在 Master 进入 MySQL, 然后执行命令:

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| test-mysql-bin.000001 |      617 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下 File 和 Position 字段的值,后面会用到。

2、然后再查询一下主从两个容器所对应的IP,主库对应 172.17.0.2,从库对应172.17.0.3:

wengzhihua@B000000147796DS ~ % docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED       STATUS          PORTS                                                  NAMES
d4ba5e063deb   mysql:5.7   "docker-entrypoint.s…"   5 hours ago   Up 19 minutes   33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp   slave
777fe9ce7f9d   mysql:5.7   "docker-entrypoint.s…"   5 hours ago   Up 51 minutes   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   master
wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' 777fe9ce7f9d
172.17.0.2
wengzhihua@B000000147796DS ~ % docker inspect --format='{{.NetworkSettings.IPAddress}}' d4ba5e063deb
172.17.0.3

3、然后到 Slave 中进入 mysql,执行命令:

mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='test-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;  
Query OK, 0 rows affected, 2 warnings (0.02 sec)

大意就是在从库使用什么账号(slave)进行同步,同步的主库的IP(master_host)、密码(master_password)、端口(master_port)、binlog日志文件(master_log_file)以及其实同步的位置(master_log_pos)等,

理解下这个命令的各个参数:

master_host: Master 的IP地址
master_user: 在 Master 中授权的用于数据同步的用户,就我们之前在主库容器里创建的哪个slave用户
master_password: 同步数据的用户的密码
master_port: Master 的数据库的端口号,注意,这边是3306,不是3307,3307是映射外部宿主主机的,写成3307,会造成 Slave_IO_Running 一直是 Connecting 状态,在这边踩坑了
master_log_file: 指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值
master_log_pos: 从哪个 Position 开始读,即上文中提到的 Position 字段的值
master_connect_retry: 当重新建立主从连接时,如果连接失败,重试的时间间隔,单位是秒,默认是60秒。

在 Slave 的 MySQL 终端执行查看主从同步状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 617
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 617
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No

SlaveIORunning 和 SlaveSQLRunning 是No,表明 Slave 还没有开始复制过程。相反 SlaveIORunning 和 SlaveSQLRunning 是Yes表明已经开始工作了。

执行一下命令,开始启动主从同步,   Slave_IO_Running: Connecting, Slave_SQL_Running: Yes 。

start slave; 
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 2351
               Relay_Log_File: edu-mysql-relay-bin.000006
                Relay_Log_Pos: 1259
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2351
              Relay_Log_Space: 1640
              Until_Condition: None

验证同步是否成功:

1、我们在主服务这边创建一个test库,库下面创建了一个person表,并增加了一条数据。

2、转到从库这边,马上就查询到数据了 

主从同步延迟解决方案

最近部门在招人,面试Java程序员的时候在数据库部分问的最频繁的问题就是这个,问题不难,不过很多候选人同学没办法比较完整的回答,跟今天的标题有点相关,我就放上来。

按照我们这边的要求,主从库同步应该是近实时的,极端情况下也不应该超过8s,如果超过,我们认为是有问题的。

  • 保证数据库处在最有状态下:优化系统配置(链接层或者存储引擎层):最大连接数、允许错误数、允许超时时间、pool_size、log_size,保证内存、CPU、存储空间的扩容(硬件部分)。
  • 业务量不多的情况下,不做读写分离。既然主从延迟是由于从库同步写库不及时引起的,那我们也可以在有主从延迟的地方改变读库方式,由原来的读从库改为读主库。当然这也会增加代码的一些逻辑复杂性。(部分业务读主库)
  • 假如你的业务时间允许,你可以在写入主库的时候,确保数据都同步到从库了之后才返回这条数据写入成功,当然如果有多个从库,你也必须确保每个从库都写入成功。显然,这个方案对性能和时间的消耗是极大的,不推荐。
  • 可以引入redis或者其他nosql数据库来存储我们经常会产生主从延迟的业务数据。当我在写入数据库的同时,我再写入一份到redis中。我们可以先去查看redis中是否有这个数据,如果有我们就可以直接从redis中读取这个数据。当数据真正同步到数据库中的时候,再从redis中把数据删除。
  • 任何的服务器都是有吞吐量的限制的,没有任何一个方案可以无限制的承载用户的大量流量。所以我们必须估算好我们的服务器能够承载的流量上限是多少。达到这个上限之后,就要采取缓存,限流,降级的方式来应对我们的流量。这也是应对主从延迟的根本处理办法。
  • 如果系统流量确实庞大,单纯的读写分离已经无法解决问题了,那么就应该对数据库进一步治理,垂直分区和水平分区是不错的方式,下一章我们会详细说说。
CDSY,CDSY.XYZ
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐