资讯专栏INFORMATION COLUMN

MySQL数据库运维之主从复制搭建

ad6623 / 491人阅读

摘要:上篇文章详细介绍了数据库的单机多实例搭建,本篇文章将在上篇文章的基础上介绍主从复制过程,其中常见的复制架构有异步复制,半同步复制及同步复制。

上篇文章详细介绍了MySQL数据库的单机多实例搭建,本篇文章将在上篇文章的基础上介绍MySQL主从复制过程,其中常见的复制架构有:异步复制,半同步复制及同步复制。常用的复制架构有异步复制及半同步复制!

一、常见的复制架构

1、主主复制
(1)结构图:

(2)说明:主主复制即复制的两个实例互为主从,两个库中都可以同时读和写;
(3)优点:

a、对于读写请求都较多的需求,可以在多个实例之间分摊读写请求,减轻单实例的读写压力
b、互为主从,当一个示例出故障时,可以迅速切换到另外一个实例上,提供读写服务

2、一主一从
(1)结构图:

(2)说明:指的是在两个数据库实例中,一个实例扮演着主库的角色,另一个实例扮演着从库的角色。这种方案中,从库通常用来作为备份使用,提供服务的多为主库;
(3)优点:

a、多数情况下,可以有效降低因某台数据库服务器故障而导致数据丢失的概率
b、作为备份服务器,可以在从库上完成在线数据的全备份,而不影响主库的写服务

3、一主多从
(1)结构图:

(2)说明:指的是在多个数据库实例中,只包含了一个主库,其他实例都作为该主库的从库,这种架构是业务规模较大场景中的一种复制架构;
(3)优点:

a、该方已经比较成功,而且使用范围极为广泛,出问题之后可以迅速找到解决方案
a、作为主库的备份,可以迅速扩展多个从库
b、可以使用mysql-proxy等中间件提供读写分离服务,通过多个从库来应对大量的读请求,提高网站的吞吐量
c、当主库出故障时,从库可以快速接管主库,成为新的主库,提供写服务

二、主从复制的原理和过程

1、主从异步复制的原理

主库上的二进制bin-log中记录主库的所有DML操作,同时在主库上运行有一个IO线程,用于响应从库上的bin-log日志读取请求;在从库上运行有一个IO线程和一个SQL线程,IO线程会实时通过网络请求去从库上读取bin-log日志,然后写入到自身的relay-log日志文件中,同时运行在从库上的SQL线程会去解析并读取relay-log,然后在自身库上执行读取到的SQL,完成主从数据的同步,示意图如下:

2、主从同步的工作过程
(1)详细过程

a、主库上会开启了二进制bin-log日志记录,同时运行有一个IO线程;
b、主库上对于需要同步的数据库或者表所发生的所有DML操作都会被记录到bin-log二进制日志文件中;
c、从库上开启relay-log日志,同时运行有一个IO线程和一个SQL线程;
d、IO线程负责从主库中读取bin-log二进制日志,并写入到本地的relay-log日志中,同时记录从库所读取到的主库的日志文件位置信息,以便下次从这个位置点再次读取;
e、SQL线程负责从本地的relay-log日志中读取同步到的二进制日志,并解析为数据库可以识别的SQL语句,然后应用到本地数据库,完成同步;
f、执行完relay-log中的操作之后,进入睡眠状态,等待主库产生新的更新;

(2)以上详细过程可总结为三步

第一步:主库在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中;
第二步:从库开启一个I/O线程,该线程对主库打开一个普通连接,主要工作是读取二进制日志。如果读取的进度已经跟上了主库,就进入睡眠状态并等待主库产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中;
第三步:SQL线程会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致;

三、MySQL异步复制搭建过程(单机多实例介绍,沿用上篇文章中搭建的多实例环境)
1、环境准备

操作系统:CentOS6.9
服务器IP:192.168.0.10
数据库版本:MySQL-5.6.39
数据库实例:实例1--3306端口(主),实例2--3307端口(从)

2、编辑3306实例的配置文件,打开该实例的二进制日志,并修改server-id,如下

[root@WB-BLOG ~]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# vim my.cnf
    [mysqld]
    server_id=3
    log_bin=/mysql_data/3306/data/mysql-bin
    log_bin_index=/mysql_data/3306/data/mysql-bin-index
    binlog_format=mixed
    ...

参数解释:

(1)server-id:用来标识一个唯一的实例,如果是在同一个局域网内,可以使用ip地址的最后一段,要保证唯一
(2)log_bin:二进制日志文件的路径,mysql用户对该路径必须具有读写权限
(3)log_bin_index:二进制文件的索引路径,mysql用户对该路径必须具有读写权限
(4)binlog_format:表示二进制日志内容的记录方式,有三种方式:
    a、row:基于行记录的方式,MySQL会将真实发生变化的行记录进日志,所以如果有update更新全表的操作,二进制日志文件会变得非常大。通常用于SQL语句复杂但是影响的行比较少的场景
    b、statement:基于语句的方式,MySQL会将导致数据发生变化的SQL语句记录到日志文件中,适用于一条语句影响很多行的场景,但是注意当在主库上使用到了UUID,SYSDATE,FOUND_ROWS函数时,使用statement方式的复制会出现主从不一致的情况;
    c、mixed:混合记录模式,MySQL会自动进行判断具体是使用row格式还是statement格式,通常情况下都使用mixed,由MySQL来进行判断

3、重启主库

[root@WB-BLOG ~]# cd /mysql_data/3306/
[root@WB-BLOG 3306]# ./mysqld restart

4、备份主库的数据

[root@WB-BLOG 3306]# cd /usr/local/mysql-5.6.39/bin/
[root@WB-BLOG tmp]# ./mysqldump -uroot -proot -h127.0.0.1 -P3306 -S /mysql_data/3306/data/mysql.sock -A --master-data=2 -F --single-transaction | gzip > /tmp/mysql_all.sql.gz

参数说明:

-S:指定socket文件,单机多实例必须要指定
-A:--all-databases,表示备份所有的数据库
--master-data:表示change master命令是否包括在备份之后的sql文件中,常用的值有1和2
    1:表示change master指令在sql文件中处于打开状态,可用于快速创建主从同步,不用再次手动修改日志文件名称和位置点
    2:表示change master指令在sql文件中会被注释,从库上使用change master时需要手动指定日志文件的文件名和位置点
-F:表示备份日志的时候刷新二进制日志,重新创建一个新的二进制日志文件
--single-transaction:用于InnoDB存储引擎格式的表备份,导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上执行unlock tables,然后执行导出
gzip:表示将备份的sql文件压缩
#其他常见参数在后面的MySQL数据备份于恢复会详细介绍

5、登陆主库,然后创建复制账户

[root@WB-BLOG 3306]# cd ..
[root@WB-BLOG mysql_data]# ./mysql_login.sh

mysql-server-3306> USE mysql
#授权从库的
mysql-server-3306> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to "repl"@"127.0.0.1" IDENTIFIED BY "repl";
mysql-server-3306> FLUSH PRIVILEGES;

6、查看主库的二进制日志文件及位置点

mysql-server-3306> show master status G
*************************** 1. row ***************************
             File: mysql-bin.000014
         Position: 367
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

7、将主库导出的数据导入从库中

[root@WB-BLOG mysql_data]# cd /usr/local/mysql-5.6.39/bin/
[root@WB-BLOG bin]# gzip -d /tmp/mysql_all.sql.gz | ./mysql -uroot -proot -S /mysql_data/3307/data/mysql.sock

8、修改从库的配置文件,开启relay-log日志,并设置server-id,如下

[mysqld]
server-id=4
relay_log=/mysql_data/3307/data/relay-log
relay_log_index = /mysql_data/3307/data/relay-log-index
...

9、修改从库上的master指向,使其指向主库,并且从主库上最新的二进制日志和位置点开始同步,然后启动主从同步

[root@WB-BLOG mysql_data]# ./mysql_login.sh
mysql-server-3307> CHANGE MASTER TO master_host = "127.0.0.1",master_port = 3306,master_user="repl",master_password="repl",master_log_file="mysql-bin.000014",master_log_pos = 367; 
mysql-server-3307> START SLAVE;
mysql-server-3307> SHOW SLAVE STATUS G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 425
               Relay_Log_File: relay-log.000004
                Relay_Log_Pos: 588
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

注意:上述结果中Slave_IO_Running和Slave_SQL_Running都为Yes表示主从同步成功,如果为Connecting...,可以等待一会再次查看,如果为No,表示同步失败;
参数说明:

master_host:主库的主机名或者IP地址
master_port:主库的端口号,必须为整数,不能加引号,否则会提示错误
master_user:在主库上添加的复制用户名称
master_password:在主库上添加的复制用户密码
master_log_file:主库当前的二进制日志文件名称
master_log_pos:主库当前的二进制文件位置点,整数,不可加引号,否则会提示错误

开启主从的另外一种方法是分别开启SQL线程和IO线程,如下:

mysql> START SLAVE IO_THREAD;
mysql> START SLAVE SQL_THREAD;

10、验证,登陆主库,然后创建数据库,查看从库是否可以正常同步

mysql-server-3306> CREATE DATABASE test_db;
mysql-server-3306> QUIT
mysql-server-3307> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
#从上面的结果可以看到,test_db已经同步到3307实例上了

11、至此,MySQL的主从复制搭建完毕。

12、主从同步中常见的问题
(1)从库的IO线程无法连接,通过"show slave status G"可以查看到具体的错误信息
原因1:在主库上创建的用户授权错误,导致从库无法远程连接主库
解决办法1:在主库上通过"show grants for "user"@"ip";"查看授权是否正确,如果错误,重新授权即可

原因2:如果是独立主机上的两个主从数据库实例,授权正确的情况下,可能是由于主库的防火墙拦截导致从库无法连接主库
解决办法2:关闭主库的防火墙,或者在主库所在服务器添加防火墙规则,允许从库的tcp连接

(2)从库启动的时候提示server-id冲突,导致无法同步主库上的数据
原因:主从库配置文件中的server-id相同了
解决办法:将主库可从库配置文件中的server-id改为不同,重新开启从库上的同步即可

(3)在从库上执行了创建库或者表的操作,然后在主库上又执行了一遍,导致同步错误,如下:

           Last_SQL_Error: Error "Can"t create database "test1"; database exists" on query. Default database: "test1". Query: "create database test1"

原因:从库上创建了库,主库上再次创建,从库会将主库上的创建过程再次应用到从库,导致从库上创建同名的库,发生错误
解决办法:停止从库,然后设置sql_slave_skip_count,使其跳过同步主库创建库的操作,从下一个操作开始同步,如下:

#停止从库
mysql-server-3307> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
#向前跳跃一步,从下一个点开始同步
mysql-server-3307> SET GLOBAL sql_slave_skip_counter =1;
Query OK, 0 rows affected (0.00 sec)
#重新开启从库上的同步
mysql-server-3307> START SLAVE ;
Query OK, 0 rows affected (0.03 sec) 
#再次查看,发现已经正常

针对直接写从库的操作,可以再从库上创建一个普通用户,授予其部分操作权限,然后设置从库的只读,通过在从库的配置文件中增加"read-only"参数来设置。但是注意,这个参数对而且只对非super用户生效,对root用户没有任何效果。

13、再生产场景下如何保证主库上的用户可以有写权限,从库上的用户只有读权限
方法1:在设置从库同步的时候,排除对mysql系统库的同步,通过在配置文件中指定binlog_ignore_db=mysql来排除不需要同步的库,或者在配置文件中指定binlog_do_db=db_name只来同步需要同步的库,然后分别在主库上创建可以写的用户,在从库上创建只能读的用户;

[mysqld]
binlog_ignore_db=mysql
binlog_do_db=user_db

方法2:在未排除任何库的情况下,先在主库上创建可以读写的用户,然后在从库中从新回收用户的写权限;
方法3:在主库和从库上创建不同的用户,然后分别授予不同的权限,使得主库只能写,从库只能读;

四、MySQL半同步搭建过程(介绍过程仍然使用单机多实例的环境)

1、定义
是介于异步复制和全同步复制之间的一种复制方式,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。

2、优缺点
(1)优点:有效的提高了数据的安全性,需要等到数据写到从库之后才返回给客户端;
(2)缺点:因为需要等待至少一个从库接收到并写入relaylog中,索引会造成一定的网络延迟,需要在网络延迟较低的环境中使用

3、搭建过程
(1)前提条件:

a、MySQL数据库版本为5.5及以上
b、属性变量have_dynamic_loading的值为YES
c、异步复制已经搭建完成

(2)查看主库和从库上的have_dynamic_loading变量

[root@WB-BLOG mysql_data]# ./mysql_login.sh 
mysql-server-3306> SHOW VARIABLES LIKE "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)

(3)登陆主库,在主库上安装半同步插件

mysql-server-3306> INSTALL PLUGIN rpl_semi_sync_master SONAME "semisync_master.so";
Query OK, 0 rows affected (0.01 sec)
mysql-server-3306> SHOW PLUGINS G
*************************** 43. row ***************************
   Name: rpl_semi_sync_master
 Status: ACTIVE
   Type: REPLICATION
Library: semisync_master.so
License: GPL
43 rows in set (0.00 sec)
#查看输出结果中包括上面的一行,表示半同步插件安装成功

注:如果想卸载半同步插件,可以使用如下命令:

mysql-server-3306> UNINSTALL PLUGIN rpl_semi_sync_master;

(4)登陆从库,安装从库上的半同步插件

mysql-server-3307> INSTALL PLUGIN rpl_semi_sync_slave SONAME "semisync_slave.so";
Query OK, 0 rows affected (0.01 sec)
mysql-server-3307> SHOW PLUGINS;
*************************** 43. row ***************************
   Name: rpl_semi_sync_slave
 Status: ACTIVE
   Type: REPLICATION
Library: semisync_slave.so
License: GPL
43 rows in set (0.01 sec)

注:从库上的半同步插件,也可以使用如下命令完成卸载:

mysql-server-3307> UNINSTALL PLUGIN rpl_semi_sync_slave;

(5)查看插件是否加载成功
主库:

mysql-server-3306> SELECT plugin_name,plugin_status FROM information_schema.plugins WHERE plugin_name LIKE "%semi%";
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

从库:

mysql-server-3307> SELECT plugin_name,plugin_status FROM information_schema.plugins WHERE plugin_name LIKE "%semi%";
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

(6)配置并开启主库的半同步复制,然后重启主库

[root@WB-BLOG 3306]# vim my.cnf
#在mysqld段下面添加如下内容:
    [mysqld]
    plugin-load = rpl_semi_sync_master=semisync_master.so
    rpl_semi_sync_master_enabled=1
[root@WB-BLOG 3306]# ./mysqld restart

(7)配置并开启从库的半同步复制,然后重启从库

[root@WB-BLOG 3307]# vim my.cnf
#添加如下内容:
    [mysqld]
    plugin-load = rpl_semi_sync_slave=semisync_slave.so
    rpl_semi_sync_slave_enabled=1
[root@WB-BLOG 3307]# ./mysqld restart

(8)重启从库上的IO线程

mysql-server-3307> STOP SLAVE IO_THREAD;          
Query OK, 0 rows affected (0.00 sec)
ysql-server-3307> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

(9)查看主库和从库上的半同步复制是否在运行
登录主库查看:

mysql-server-3306> SHOW STATUS LIKE "rpl_semi_sync_master_status";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+

登录从库查看:

mysql-server-3307> SHOW STATUS LIKE "rpl_semi_sync_slave_status";      
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

上述结果表示主库和从库上的半同步复制运行正常。

(10)验证半同步复制是否正常
验证方法:正常在主库上创建一张表,会立刻返回,耗时0.1s。关闭从库的io线程,然后在主库上执行建表操作,会发现,主库上回阻塞10秒之后才会返回,而这个时间正好和主库上的rpl_semi_sync_master_timeout相同,表示半同步起作用了,主库的DDL操作需要等到从库应用完relaylog之后才返回;

#主库执行:
mysql-server-3307> STOP SLAVE IO_THREAD;

#从库执行:
mysql-server-3306> CREATE TABLE test(id int);
Query OK, 0 rows affected (10.03 sec)

#查看主库上的rpl_semi_sync_maser_timeout
mysql-server-3306> SHOW VARIABLES LIKE "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+

至此,MySQL的半同步复制搭建完成。

4、半同步搭建中常见问题
(1)主从不能正常同步:和主从同步无法正常复制的排查方法相同
(2)不能正常安装半同步插件
原因1:可能是版本问题
解决办法1:查看MySQL实例的版本,如果版本问题,更换新版本重新安装即可

mysql> SELECT version();

原因2:MySQL的安装目录中未包含用于半同步复制的共享库
解决办法2:找到该版本对应的半同步共享库,然后重新安装

五、全同步复制
同步复制在所有复制方案中最安全,但是性能最差,而且需要使用DRBD(分布式复制块设备)来完成数据的同步,DRBD是一种类似于"rsync+inotify"的架构,通常使用较少,几乎不用,此处不做详细介绍。

到此,MySQL的主从复制介绍完毕,主从复制是一块很大的内容,包括延迟排查,数据一致问题、快速主从搭建及主从复制的高可用,后面会继续写文章介绍,欢迎转发评论!

后续文章将更新在个人小站上,欢迎查看。

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/47800.html

相关文章

  • MySQL据库维之主从复制延迟问题排查

    摘要:这个参数表示事务执行完成之后,多久的频率刷新一次日志到磁盘上,可用的值有如下几种表示会将日志缓冲区中的数据每秒一次地写入日志文件中,并且日志文件的刷盘操作同时进行。 上篇文章介绍了单机环境下的MySQL主从异步复制和主从半同步复制的搭建过程。搭建过程很简单,但是在实际使用过程中,更多的是解决问题,本篇文章将介绍一下MySQL主从复制中常见的问题以及如何定位问题和如何解决问题。 一、从库...

    xumenger 评论0 收藏0
  • MySQL据库维之读写分离01

    摘要:之前文章介绍了的主从同步复制,了解了主从同步的架构及搭建过程。这种通过将数据库按照职责划分的架构,称为数据库的读写分离架构。 之前文章介绍了MySQL的主从同步复制,了解了主从同步的架构及搭建过程。主从同步一方面用来做数据库的数据备份,另外一个很重要的因素是用来解决网站的读写瓶颈,本篇文章将介绍MySQL读写分离的原理,架构,然后使用mysql-proxy中间件搭建一个简单的mysql...

    cppowboy 评论0 收藏0
  • MySQL据库维之MySQL高可用01

    摘要:分库分表完成之后,数据容量及性能增加了,但是紧接着会面临着可用性的问题。但是,,,本身也是单点,需要实现高可用,后面介绍应用高可用时会详细介绍。至此,的高可用概念及使用实现的高可用已经搭建验证完毕,篇幅较长,难免有笔误,欢迎评论转发 前面文章介绍了MySQL的读写分离及分库分表过程。对于分库和分表,实现策略有好多种,需要根据业务来确定最适合的分库分表架构,文章中还有好多未涉及到的,读者...

    cnio 评论0 收藏0
  • MySQL据库维之读写分离03

    摘要:上篇文章详细介绍了中间件的搭建及读写分离操作。以上内容介绍了使用取模的方式借助中间件实现数据库的分库分表及读写分离过程。 上篇文章详细介绍了MySQL中间件MyCat的搭建及读写分离操作。在实际场景中,面对大容量的数据,在数据库层面中通常的解决方案有两种,第一种是读写分离,第二种就是数据库的分库和分表了,本篇文章将基于MyCat中间件,详细介绍一下通过MyCat综合实现分库、分表及读写...

    ningwang 评论0 收藏0
  • MySQL据库维之数据备份01

    摘要:注意,是大写的示例示例完成服务器上数据库实例的全量备份检查文件是否正常,这步很很很重要。 上篇文章介绍了MySQL主从复制中常见问题排查。在主从复制架构搭建完成之后,需要定期对数据进行备份,本篇文章开始就来介绍MySQL数据库的数据备份与恢复,由于备份及恢复内容较多,分多个小节介绍,本小节先来介绍MySQL数据库备份的相关概念及mysqldump逻辑备份工具。 1、备份类型(1)按照备...

    Simon 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<