目录

主从复制

什么是主从复制

主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是准实时的业务数据库。您看,像在mysql数据库中,支持单项、异步赋值。在赋值过程中,一个服务器充当主服务器,而另外一台服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时,从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新,然后锁住并等到主服务器通知新的更新

主从复制的类型

基于语句的复制

主服务器上面执行的语句在从服务器上面再执行一遍,在MySQL-3.23版本以后支持。

存在的问题:时间上可能不完全同步造成偏差

基于行的复制

把主服务器上面改编后的内容直接复制过去,而不关心到底改变该内容是由哪条语句引发的,在MySQL-5.0版本以后引入。

存在的问题:比如一个工资表中有一万个用户,我们把每个用户的工资+1000,那么基于行的复制则要复制一万行的内容,由此造成的开销比较大,而基于语句的复制仅仅一条语句就可以了。

混合类型的复制

MySQL默认使用基于语句的复制,当基于语句的复制会引发问题的时候就会使用基于行的复制,MySQL会自动进行选择。

主从复制的原理

  1. 数据库有个bin-log二进制文件,记录了所有sql语句。

  2. 我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

  3. 让其在从数据的relay-log(中继日志)重做日志文件中再执行一次这些sql语句即可。

  4. 下面的主从配置就是围绕这个原理配置

  5. 具体需要三类线程来操作:

    1. binlog输出线程

      每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。在从库里,当复制开始的时候,从库就会创建两个线程进行处理:

    2. 从库I/O线程

      当START SLAVE语句在从库开始执行之后,从库创建一个I/O线程,该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件,其中包括relay log文件。

    3. 从库的SQL线程

      从库创建一个SQL线程,这个线程读取从库I/O线程写到relay log的更新事件并执行。

可以知道,对于每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程,每一个从库都有它自己的I/O线程和SQL线程。

https://gitee.com/lienhui68/picStore/raw/master/null/20201002200815.png

主从复制拓补图

https://gitee.com/lienhui68/picStore/raw/master/null/20201002200849.png

主从复制原理图

工作过程

  1. i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;
  2. 主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
  3. SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;

更详细的流程

http://img.cana.space/picStore/20201121114539.png

为什么要主从复制

做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。 架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的评率,提高单个机器的I/O性能。 读写分离,使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度。

实现服务器负载均衡

通过服务器复制功能,可以在主服务器和从服务器之间实现负载均衡。即可以通过在主服务器和从服务器之间切分处理客户查询的负荷,从而得到更好地客户相应时间。通常情况下,数据库管理员会有两种思路。

一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用,像基金净值预测的网站。其数据的更新都是有管理员更新的,即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器,专门用来数据的更新。同时设置多台从服务器,用来负责用户信息的查询。将数据更新与查询分别放在不同的服务器 上进行,即可以提高数据的安全性,同时也缩短应用程序的响应时间、提高系统的性能。

二是在主服务器上与从服务器切分查询的作业。在这种思路下,主服务器不单单要完成数据的更新、删除、插入等作业,同时也需要负担一部分查询作 业。而从服务器的话,只负责数据的查询。当主服务器比较忙时,部分查询请求会自动发送到从服务器重,以降低主服务器的工作负荷。当然,像修改数据、插入数 据、删除数据等语句仍然会发送到主服务器中,以便主服务器和从服务器数据的同步。

通过复制实现数据的异地备份

可以定期的将数据从主服务器上复制到从服务器上,这无疑是先了数据的异地备份。在传统的备份体制下,是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上,当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时,将备份数据存放在本地,也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃,此时由于备份文件仍然存放在硬盘上,数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

而如果使用复制来实现对数据的备份,就可以在从服务器上对数据进行备份。此时不仅不会干扰主服务气的正常运行,而且在备份过程中主服务器可以继 续处理相关的更新作业。同时在数据复制的同时,也实现了对数据的异地备份。除非主服务器和从服务器的两块硬盘同时损坏了,否则的话数据库管理员就可以在最 短时间内恢复数据,减少企业的由此带来的损失。

提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步,增加了数据库系统的可用性。当主服务器出现问题时,数据库管理员可以马上让从服务器作为主服务器,用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。

  • 一是主服务器故障之后,虽然从服务器取代了主服务器的位置,但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询,而 不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级,在升级的过程中,只能够查询余额而不能够取钱。这是同样的道理。
  • 二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后,其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操作。为此就需要做好数据的安全性工作。即数据的安全策略,要与原先的主服务器完全相同。否则的话,就可能会留下一定的安全隐患。

一些细节

主库宕机后,数据可能丢失

https://gitee.com/lienhui68/picStore/raw/master/null/20201002202324.png

半同步复制—解决数据丢失的问题

  • 5.5集成到mysql,以插件的形式存在,需要单独安装
  • 确保事务提交后binlog至少传输到一个从库
  • 不保证从库应用完这个事务的binlog
  • 性能有一定的降低,响应时间会更长
  • 网络异常或从库宕机,卡主主库,直到超时或从库恢复

https://gitee.com/lienhui68/picStore/raw/master/null/20201002202353.png

从库只有一个sql Thread,主库写压力大,复制很可能延时

并行复制—-解决从库复制延迟的问题

  • 社区版5.6中新增

  • 并行是指从库多线程apply binlog库级别并行应用binlog,同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置

  • 设置sql线程数为10

    1
    
    set global slave_parallel_workers=10;
    

主从复制和主主复制区别

最大区别是 主从是对主操作数据,从会实时同步数据。反之对从操作,主不会同步数据,还有可能造成数据紊乱,导致主从失效。 主主则是无论对那一台操作,另一个都会同步数据。一般用作高容灾方案

主从架构

在MySQL主从复制架构中,读操作可以在所有的服务器上面进行,而写操作只能在主服务器上面进行。主从复制架构虽然给读操作提供了扩展,可如果写操作也比较多的话(多台从服务器还要从主服务器上面同步数据),单主模型的复制中主服务器势必会成为性能瓶颈。

主从复制方式

  • 一主一从
  • 主主复制 互为主从
  • 一主多从—扩展系统读取的性能,因为读是在从库读取的;
  • 多主一从—5.7开始支持
  • 联级复制—联级复制就是master服务器,只给一台slave服务器同步数据,然后slave服务器在向后端的所有slave服务器同步数据,降低master服务器的写压力,和复制数据的网络IO。

https://gitee.com/lienhui68/picStore/raw/master/null/20201002201815.png

演示

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
# 基本要求:Master和Slave的MySQL服务器版本一致且后台以服务运行
#  docker中创建服务器节点
# 创建master节点
docker run -p 3306:3306 --name mysql \
-v /tmp/mysql/log:/var/log/mysql \
-v /tmp/mysql/data:/var/lib/mysql \
-v /tmp/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7

# 创建slave节点
docker run -p 3307:3306 --name mysql-slave1 \
-v /tmp/mysql-slave1/log:/var/log/mysql \
-v /tmp/mysql-slave1/data:/var/lib/mysql \
-v /tmp/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7

#  初始化配置
# 两个节点对应的conf目录分别新建my.cnf文件,增加下一个代码框中配置

#  主从配置都是配在[mysqld]节点下,都是小写
# Master配置
[mysqld]
# Master配置
server-id=1
log-bin=/var/lib/mysql/mysql-bin
read-only=0
# 忽略mysql
binlog-ignore-db=mysql

# slave配置
# Master配置
server-id=2
log-bin=/var/lib/mysql/mysql-bin
# 使用read-only,可以让整个mysqld实例处于只读的状态,但是要注意的是,对于拥有SUPER权限的超级用户来说read-only是无法做限制的。因此对外,我们只能提供普通用户的权限。
read-only=1

#  master配置
# 使用docker insepect 查看节点配置信息,后面需要用到节点ip地址
# 1. GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机IP地址' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'david'@'172.17.0.4' IDENTIFIED BY '123456';
# 2. 刷新权限
mysql> FLUSH PRIVILEGES;
# 3、记录下File和Position
# 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      602 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
#  slave配置
# 配置连接信息
CHANGE MASTER TO MASTER_HOST='172.17.0.3',
MASTER_USER='david',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.00001',
MASTER_LOG_POS=154;
# 1. 使用用户名密码登录进master
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.3',
    -> MASTER_USER='david',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.00001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
# 2、开启Slave从机的复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 3、查看Slave状态
# Slave_IO_Running(连接正常)  Slave_SQL_Running(启动时relay log恢复执行正常) 必须同时为Yes 说明主从复制配置成功!
mysql> SHOW SLAVE STATUS\G
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.3
                  Master_User: david
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2375
               Relay_Log_File: c08ffe95e598-relay-bin.000003
                Relay_Log_Pos: 2588
        Relay_Master_Log_File: 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: 2375
              Relay_Log_Space: 2802
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 42702f0c-02e8-11eb-a401-0242ac110003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

#  测试主从配置
# 1. 主机写数据
mysql> create database test;
mysql> create table student(id int, name varchar(20));
mysql> insert into student values(1, 'david');
# 2. 从机查看数据
mysql> show databases;
mysql> use test;
mysql> select * from student;
+------+-------+
| id   | name  |
+------+-------+
|    1 | david |
+------+-------+
1 rows in set (0.00 sec)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[client]
#mysqlde utf8字符集默认为3位的,不支持emoji表情及部分不常见的汉字,故推荐使用utf8mb4
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
#设置client连接mysql时的字符集,防止乱码
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'

#数据库默认字符集
character-set-server=utf8

#数据库字符集对应一些排序等规则,注意要和character-set-server对应
collation-server=utf8_general_ci

# 跳过mysql程序起动时的字符参数设置 ,使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
skip-name-resolve


# 数据库错误日志文件
log-error = error.log

注意事项

演示过程中可能会出错,导致有脏数据产生需要重置

1
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

可以执行如下命令解决

1
2
3
mysql> stop slave;
mysql> reset slave;
mysql> start slave;

命令解释

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
reset slave是各版本Mysql都有的功能,可以让slave忘记自己在master binary log中的复制位置。

reset slave命令主要完成以下工作内容:
  -删除master.info和relay-log.info文件
  -删除所有的relay log(包括还没有应用完的日志)
  -创建一个新的relay log文件
  -将复制延迟选项 master_delay设置为0

reset slave不会修改gtid_executed、gtid_purged的值。

在执行reset slave之前,必须执行stop slave。

在mysql 5.6之后,reset slave不会修改任何复制配置参数,所有的连接信息仍然保留在内存中,包括主库地址、端口、用户、密码等。也就是说在执行reset slave之后还可以直接运行start slave命令而不必重新输入change master to命令,而运行show slave status也仍和没有运行reset slave一样,有正常的输出。

reset slave之后关闭mysqld就会清除连接参数。

在mysql 5.6.3之后,可以使用reset slave all来清除连接参数,运行show slave status就输出为空了。
mysql 5.6.7之后,reset slave会隐式提交事务。 

运行reset slave命令需要reload权限。MHA在做故障切换时,就会在新主上运行命令RESET SLAVE /*!50516 ALL */ ,清除掉它的所有从库信息。

docker-compose版演示

一主一从架构

docker-compose.yml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
version: '2'
services: 
    m1: 
        image: mysql:5.7
        container_name: m1
        volumes: 
            - ./m1/log:/var/log/mysql
            - ./m1/data:/var/lib/mysql
            - ./m1/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3306:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.2 # 设置静态ipv4地址
    s1: 
        image: mysql:5.7
        container_name: s1
        volumes: 
            - ./s1/log:/var/log/mysql
            - ./s1/data:/var/lib/mysql
            - ./s1/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3307:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.3 # 设置静态ipv4地址
        depends_on:
            - m1
networks: # 使用自定义网络
    mysql:
        # ipam插件只在compose version 2中好使,
        # issue:https://github.com/docker/docker.github.io/pull/1636
        ipam: # 要使用静态ip必须使用ipam插件
            config:
            - subnet: 182.30.0.0/16 # 网段/16位掩码
              gateway: 182.30.0.1 # 网关地址

执行dcp

1
$ dcp -f mysql-1m-1s.yml up -d

现在conf文件夹是空,我们需要拷贝一份配置文件过来,可以新建一个mysql容器,使用docker cp命令复制配置文件过来

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
# 1.这个tag规定必须要设置-e MYSQL_ROOT_PASSWORD=333,否则会启动不了
$ docker run -p 3309:3306 -e MYSQL_ROOT_PASSWORD=333 -d mysql:5.7
d81483a5c0a991bd8ee794e81edfdaecb470f140787223b84f3da14ad8f2fe80
# 2.进入容器
de d8 bash
# 3.查看配置文件加载顺序
root@d81483a5c0a9:/etc/mysql/mysql.conf.d# mysql --verbose --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
# 4.查看配置文件,由于/etc/my.cnf不存在,我们查看 /etc/mysql/my.cnf
root@d81483a5c0a9:/etc/mysql# cat my.cnf
...
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
# 5./etc/mysql/conf.d/目录不存在,查看/etc/mysql/mysql.conf.d/
root@d81483a5c0a9:/etc/mysql/mysql.conf.d# ls
mysqld.cnf
# 6.可以确定配置文件就是mysqld.cnf,查看mysqld.cnf
...
[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 7.将mysql.conf.d/目录下内容拷贝到宿主机目录m1/conf和s1/conf下
$ docker cp d8:/etc/mysql/mysql.conf.d/. ~/docker/compose-config/mysql/m1/conf
$ docker cp d8:/etc/mysql/mysql.conf.d/. ~/docker/compose-config/mysql/s1/conf
# 8.打开error日志注释,配置主库
#********** 配置master *******#

# 主服务器ID 必须唯一,一般配置ip尾号,比如主库ip 182.30.0.2
server-id=2

# 开启及设置二进制日志文件名称,会存放在/var/lib/mysql/mysql-bin
log_bin=mysql-bin

# 要同步的数据库
binlog-do-db=eden

# 不需要同步的数据库
binlog-ignore-db=mysql    
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

# 设置logbin格式
binlog_format= MIXED # binlog日志格式,mysql默认采用statement,建议使用mixed

# 9.打开error日志注释,配置从库
#********** 配置master *******#
server-id=3
relay-log=mysql-relay 
# 10.重启容器以使配置生效
$ docker restart c2
$ docker restart f0
# 11.查看配置
$ de f0 bash
root@f0a1ad4998a8:/# mysql -uroot -p333
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
# 可以看到配置已经生效了,接下来使用navicat连接主从服务器,配置权限以及同步事宜
# 12.删除d8容器,只是拷贝配置文件用的
$ docker rm -f d8
binlog_format详解

mysql复制主要有三种方式:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED。

  1. STATEMENT模式(SBR)

    每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)

  2. ROW模式(RBR)

    不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。

  3. MIXED模式(MBR)

    以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。

使用navicat连接主从服务器,配置权限以及同步事宜

http://img.cana.space/picStore/20201121131009.png

  1. m1

    1
    2
    3
    4
    5
    6
    7
    8
    
    -- 创建从机访问用户
    CREATE USER 'slave1'@'182.30.0.3' IDENTIFIED BY '123456';
    -- 授予slave1用户主从同步权限
    GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'182.30.0.3';
    -- 刷新权限
    FLUSH PRIVILEGES;
    -- 查看主服务器状态,见下图
    SHOW MASTER STATUS
    

    查看主服务器状态:

    http://img.cana.space/picStore/20201121131417.png

  2. s1

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    
    -- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
    CHANGE MASTER TO MASTER_HOST = '182.30.0.2',
    MASTER_USER = 'slave1',
    MASTER_PASSWORD = '123456',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 776;
       
    -- 启动主从复制
    START SLAVE;
       
    -- 查看从机状态,见下图
    SHOW SLAVE STATUS;
    

    查看从机状态:

    20201121132047

    有两个yes才OK;假如有 No 或者 Connecting,请看下方日志 或者找到Mysql日志文件查看具体问题;

    请注意,主从 MySQL下的数据(data)目录下有个文件auto.cnf,文件中定义了uuid,要保证主从数据库实例的 uuid不一样,建议直接删除掉,重启服务后将会重新生成。可以解决Slave_IO_Running No的问题。

mysql主从复制测试

在主服务器上新建eden数据库,创建表,crud数据,从服务器都会自动同步

http://img.cana.space/picStore/20201121132257.png

主库执行:

1
2
3
4
5
6
create table student(
	id int,
	name varchar(20)
);

insert into student values(1, '张三');

查看从库

http://img.cana.space/picStore/20201121132511.png

主从复制实现常用命令

START SLAVE

不带任何参数,表示同时启动I/O 线程和SQL线程。

I/O线程从主库读取bin log,并存储到relay log中继日志文件中。

SQL线程读取中继日志,解析后,在从库重放。

STOP SLAVE

完成停止I/O 线程和SQL线程的操作。

SHOW MASTER STATUS

查看主服务器状态;参考上面图片

SHOW SLAVE STATUS

查看从服务器状态:参考上面图片

RESET MASTER

删除所有index file 中记录的所有binlog 文件,将日志索引文件清空,创建一个新的日志文件,这个命令通常仅仅用于第一次用于搭建主从关系的时的主库,执行这个命令需要先STOP SLAVE,然后再执行,执行完后需要重新

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.2',
MASTER_USER = 'slave1',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 776;

-- 启动主从复制
START SLAVE;

-- 查看从机状态,见下图
SHOW SLAVE STATUS;

其中position位置需要重新查一遍主库状态SHOW MASTER STATUS

双主双从架构

2对mysql主从复制搭建

同上,dockerfile

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
version: '2'
services: 
    m1: 
        image: mysql:5.7
        container_name: m1
        volumes: 
            - ./m1/log:/var/log/mysql
            - ./m1/data:/var/lib/mysql
            - ./m1/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3306:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.2 # 设置静态ipv4地址
    s1: 
        image: mysql:5.7
        container_name: s1
        volumes: 
            - ./s1/log:/var/log/mysql
            - ./s1/data:/var/lib/mysql
            - ./s1/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3307:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.3 # 设置静态ipv4地址
    m2: 
        image: mysql:5.7
        container_name: m2
        volumes: 
            - ./m2/log:/var/log/mysql
            - ./m2/data:/var/lib/mysql
            - ./m2/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3308:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.4 # 设置静态ipv4地址
    s2: 
        image: mysql:5.7
        container_name: s2
        volumes: 
            - ./s2/log:/var/log/mysql
            - ./s2/data:/var/lib/mysql
            - ./s2/conf:/etc/mysql/mysql.conf.d
        restart: always
        environment:
            - MYSQL_ROOT_PASSWORD=333
        ports: 
            - 3309:3306
        networks: 
            mysql: 
                ipv4_address: 182.30.0.5 # 设置静态ipv4地址
networks: # 使用自定义网络
    mysql:
        # ipam插件只在compose version 2中好使,
        # issue:https://github.com/docker/docker.github.io/pull/1636
        ipam: # 要使用静态ip必须使用ipam插件
            config:
            - subnet: 182.30.0.0/16 # 网段/16位掩码
              gateway: 182.30.0.1 # 网关地址

配置:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
# 2.启动容器
$ dcp -f mysql-2m-2s.yml up -d
# 3.查看容器
$ docker ps
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS              PORTS                               NAMES
2b935933bb73        mysql:5.7             "docker-entrypoint.s…"   11 seconds ago      Up 9 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   m1
183a495aed35        mysql:5.7             "docker-entrypoint.s…"   11 seconds ago      Up 9 seconds        33060/tcp, 0.0.0.0:3307->3306/tcp   s1
beac52d4d757        mysql:5.7             "docker-entrypoint.s…"   11 seconds ago      Up 9 seconds        33060/tcp, 0.0.0.0:3309->3306/tcp   s2
a86b03091875        mysql:5.7             "docker-entrypoint.s…"   11 seconds ago      Up 9 seconds        33060/tcp, 0.0.0.0:3308->3306/tcp   m2
9bfe53198c21        lienhui68/mycat:1.0   "/bin/sh -c '/home/m…"   53 minutes ago      Up 53 minutes       0.0.0.0:8066->8066/tcp              mycat01
# 4.查看容器ip
$ docker_ip
/m1 - 182.30.0.2
/s1 - 182.30.0.3
/m2 - 182.30.0.4
/s2 - 182.30.0.5
/mycat01 - 182.30.0.9
# 5.m1,m2我们要加配置:涉及到数据插入id不冲突,以及数据同步;
# 6.m1
#********** 配置master *******#

# 主服务器ID 必须唯一,一般配置ip尾号,比如主库ip 182.30.0.2
server-id=2

# 开启及设置二进制日志文件名称,会存放在/var/lib/mysql/mysql-bin
log_bin=mysql-bin

# 要同步的数据库
binlog-do-db=eden

# 不需要同步的数据库
binlog-ignore-db=mysql    
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

# 设置logbin格式
binlog_format= MIXED # binlog日志格式,mysql默认采用statement,建议使用mixed


#********** 配置master(双主双从) *******#
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates

#表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,他的取值范围是1 .. 65535
auto-increment-offset=1

# 7.m2
# david @ Davids-Macbook-Pro in ~/docker/compose-config/mysql/m2/conf [16:27:42]
$ cp ../../m1/conf/mysqld.cnf .
# 编辑
#********** 配置master *******#

# 主服务器ID 必须唯一,一般配置ip尾号,比如主库ip 182.30.0.2
server-id=4

# 开启及设置二进制日志文件名称,会存放在/var/lib/mysql/mysql-bin
log_bin=mysql-bin

# 要同步的数据库
binlog-do-db=eden

# 不需要同步的数据库
binlog-ignore-db=mysql    
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

# 设置logbin格式
binlog_format= MIXED # binlog日志格式,mysql默认采用statement,建议使用mixed


#********** 配置master(双主双从) *******#
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates

#表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,他的取值范围是1 .. 65535
auto-increment-offset=2

# 8.s1 不用变
# 9.s2
#********** 配置master *******#
server-id=5
relay-log=mysql-relay 

# 10. 重新启动容器,为了方便,直接down掉重新up,也可以一个个地$ dcp -f mysql-2m-2s.yml restart m1
$ dcp -f mysql-2m-2s.yml down
$ dcp -f mysql-2m-2s.yml up -d

m1和s1中删掉之前创建的eden库,执行reset master

m1

m1 删除user里的原先slave用户,重新权

http://img.cana.space/picStore/20201121164116.png

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 这样之后s1和m2都可以进行主从同步
CREATE USER 'slave'@'182.30.0.%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'182.30.0.%';

FLUSH PRIVILEGES;
-- 删除所有index file 中记录的所有binlog 文件,将日志索引文件清空,创建一个新的日志文件,这个命令通常仅仅用于第一次用于搭建主从关系的时的主库,执行这个命令需要先STOP SLAVE,然后再执行,执行完后需要重新
-- 先停止slave, 在从库上执行stop slave
RESET MASTER;

-- 查看主服务器状态
SHOW MASTER STATUS;

s1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.2',
MASTER_USER = 'slave',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;

-- 启动主从复制
START SLAVE;

-- 查看从机状态,见下图
SHOW SLAVE STATUS;

m2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 这样之后s1和m2都可以进行主从同步
CREATE USER 'slave'@'182.30.0.%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'182.30.0.%';

FLUSH PRIVILEGES;
-- 删除所有index file 中记录的所有binlog 文件,将日志索引文件清空,创建一个新的日志文件,这个命令通常仅仅用于第一次用于搭建主从关系的时的主库,执行这个命令需要先STOP SLAVE,然后再执行,执行完后需要重新
RESET MASTER;

-- 查看主服务器状态
SHOW MASTER STATUS;

s2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.4',
MASTER_USER = 'slave',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;

stop slave;
-- 启动主从复制
START SLAVE;

-- 查看从机状态,见下图
SHOW SLAVE STATUS;

s1和s2的从机状态中sql线程和io线程都要是running状态才可以

m1和m2互为主从

m2复制m1配置

m1先reset

m2里配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.2',
MASTER_USER = 'slave',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;

-- 启动主从复制
START SLAVE;

-- 查看从机状态,见下图
SHOW SLAVE STATUS;

m1复制m2配置

m2先reset

m1里配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.4',
MASTER_USER = 'slave',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;

-- 启动主从复制
START SLAVE;

-- 查看从机状态,见下图
SHOW SLAVE STATUS;

测试

  • 在m1创建数据库eden, 其他3个库都创建了eden库
  • m1创建表、插入删除,其他3个库同步更新
  • m2创建表、插入删除,其他3个库同步更新