MySQL主从复制《主库已经有数据的解决方案》《几种复制模式》

发布于 2020-04-23

MySQL主从复制架构

目前MySQL支持两种复制类型：

1.传统方式：
基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的。
2.Gtid方式（MySQL>=5.7推荐使用）：
基于GTID的复制中，从库会告知主库已经执行的事务的GTID的值，然后主库会将所有未执行的事务的GTID的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次。

MySQL复制有多种类型：

1.异步复制
　　　　一个主库，一个或多个从库，数据异步同步到从库。
2.同步复制
　　　　在MySQL cluster中特有的复制方式。

3.半同步复制
　　　　在异步复制的基础上，确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物并日志记录下来。

4.延迟复制
　　　　在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数。

下面是基于二进制日志点的异步复制：

MySQL主从复制原理：

我们在MySQL中配置了主从之后，只要我们对Master节点进行了写操作，这个操作将会被保存到MySQL的binary-log（bin-log）日志当中，当slave连接到master的时候，master机器会为slave开启binlog dump线程。当master 的 binlog发生变化的时候，Master的dump线程会通知slave，并将相应的binlog内容发送给Slave。而Slave节点在主从同步开启的时候，会创建两个线程，一个I/O线程，一个SQL线程。

I/O线程：该线程链接到master机器，master机器的binlog发送到slave的时候，IO线程会将该日志内容写在本地的中继日志（Relay log）中。
SQL线程：该线程读取中继日志中的内容，并且根据中继日志中的内容对Slave数据库做相应的操作。
可能造成的问题：在写请求相当多的情况下，可能会造成Slave数据和Master数据不一致的情况，这是因为日志传输过程中的短暂延迟、或者写命令较多，系统速度不匹配造成的。
这大致就是MySQL主从同步的原理，真正在其中起到作用的实际上就是这两个日志文件，binlog和中继日志（Relay log）。

一. 配置主从数据库服务器参数：

Master服务器参数：

[mysqld]
log-bin = /www/server/data/mysql-bin
binlog_format = mixed
server-id = 100
#expire_logs_days = 10 #日志过期时间
#max_binlog_size = 200M #日志最大容量，可以不设置，有默认值，设置后MySQL无法重启，我遇到情况
binlog_do_db = test
#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

2. Slave服务器参数：

[mysqld]
log-bin = /www/server/data/mysql-bin
binlog_format = mixed
server-id = 200
#expire_logs_days = 10 #日志过期时间
#max_binlog_size = 200M #日志最大容量，可以不设置，有默认值，设置后MySQL无法重启，我遇到情况

relay_log = /www/server/data/relay-bin
#指定relay_log日志的存放路径和文件前缀 ，不指定的话默认以主机名作为前缀

read_only = on
skip_slave_start = on

#下面两个参数是把主从复制信息存储到innodb表中，默认情况下主从复制信息是存储到文件系统中的，如果从服务器宕机，很容易出现文件记录和实际同步信息不同的情况，存储到表中则可以通过innodb的崩溃恢复机制来保证数据记录的一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

二. 在Master服务器上建立复制账号：

需要设置REPLICATION SLAVE权限:

CREATE USER '账号'@'2.7.4.5' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'1.1.1.1';   
flush privileges;   #刷新权限

三. 在Slave服务器的操作：

查看master的 binlog的文件名和binlog偏移量

输入 show master status; 
查看master的 binlog的文件名和binlog偏移量

2. 配置slave服务器：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='1.1.1.1',
    ->     MASTER_USER='账号',
    ->     MASTER_PASSWORD='密码',
    ->     MASTER_LOG_FILE='mysql-bin.00001',
    ->     MASTER_LOG_POS=66;
#注意，这里的master_log_file，就是binlog的文件名，输入上图中的mysql-bin.00001,每个人的都可能不一样。
#注意，这里的master_log_pos是binlog偏移量，输入上图中的66，每个人的都可能不一样。

四. master和salve数据库的数据保持一致（主库已经有数据的解决方案）

主从数据库的数据要保持一致，不然主从同步会出现bug...

主库已经有数据的解决方案：

第一种方案是选择忽略主库之前的数据，不做处理。这种方案只适用于不重要的可有可无的数据，并且业务上能够容忍主从库数据不一致的场景。

第二种方案是对主库的数据进行备份，然后将主数据库中导出的数据导入到从数据库，然后再开启主从复制，以此来保证主从数据库数据一致。

下面是第二种方案的操作：

锁定主数据库，只允许读取不允许写入，这样做的目的是防止备份过程中或备份完成之后有新数据插入，导致备份数据和主数据数据不一致。
mysql> flush tables with read lock;

2. 通过mysql主服务器上的全备初始化从服务器上数据：

[root@localhost data]# cd /data/db_backup/
[root@localhost db_backup]#  mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > all.sql
Enter password:

3. 解锁主数据库：

unlock tables;

4. 把数据全量导入slave数据库，保证主从数据一致

五. 开始主从同步:

在从服务器MySQL命令行
输入 start slave;
开启从同步
然后输入 show slave statusG;
查看从节点状态

六. 注意事项：

如果出现IO线程一直在Connecting状态，可以看看是不是俩台机器无法相互连接，如果可以相互连接，那么有可能是Slave账号密码写错了，重新关闭Slave然后输入上面的配置命令再打开Slave即可。

如果出现SQL线程为NO状态，那么有可能是从数据库和主数据库的数据不一致造成的，或者事务回滚，如果是后者，先关闭stop slave，然后先查看master的binlog和position，然后输入配置命令，再输入set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;，再重新start slave;即可，如通过是前者，那么就排查一下是不是存在哪张表没有被同步，是否存在主库存在而从库不存在的表，自己同步一下再重新配置一遍即可。

Could not find first log file name in binary log index file

如果查看从库状态发现此问题，请查看主库状态，将其中的File和Position字段通过在从库中执行以下SQL语句写入从库配置中。

change master to master_log_file='mysql-bin.000001',master_log_pos=3726

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

如果启动slave时出现此错误，主要可能是因为保存着以前slave用的relay-log，可以执行以下语句来启动slave。

reset slave;
start slave;

七. 总结：

1.MySQL 从5.6（MySQL>=5.7 在5.7完善，建议使用）引入了GTID(Global Transaction IDs)使其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。配置方法和基于日志的主从复制大体一样，所以不做重复。MySQL5.7推荐使用GTID配置主从复制。

2.由于高并发下，MySQL主从复制，会导致主从同步延时的问题。MySQL 在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。具体的解决办法下篇文章论述。。。

mysql主主半同步

1.半同步概述

先了解下mysql的几种复制

异步复制
MySQL复制默认是异步复制，Master将事件写入binlog，提交事务，自身并不知道slave是否接收是否处理；
缺点：不能保证所有事务都被所有slave接收。
同步复制
Master提交事务，直到事务在所有slave都已提交，才会返回客户端事务执行完毕信息；
缺点：完成一个事务可能造成延迟。
半同步复制
当Master上开启半同步复制功能时，至少有一个slave开启其功能。当Master向slave提交事务，且事务已写入relay-log中并刷新到磁盘上，slave才会告知Master已收到；若Master提交事务受到阻塞，出现等待超时，在一定时间内Master 没被告知已收到，此时Master自动转换为异步复制机制；
注：半同步复制功能要在Master和slave上开启才会起作用，只开启一边，依然是异步复制。

下面详细说明主主半同步：

通过mysql的半同步插件，在两个数据库之间相互设置半同步，实现主主半同步架构。

这里做相对于半同步的好处在于，两个数据库处于完全对等的地位，可以很方便的做自动切换。

有一点需要注意的是，推荐设置read-only，让同时只有一个数据库可写。如果两个同时写，如果出现了同步不一致，这时恢复数据需要对比两个数据库。

自动切换可以使用keepalived做vip的漂移

同步过程

1. 在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按照事务提交的顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了

2. 从库会启动一个工作线程，I/O线程跟主库建立一个普通的客户端连接，然后再主库上启动一个特殊的二进制转储(binlog dump)线程，这个二进制转储线程会读取主库上二进制日志中的事件。如果该线程追赶上了主库，就进入睡眠状态，直到主库发送信号通知有新事件产生再次唤醒

3. 从库SQL线程从中继日志中读取事件，并在从库上执行，完成复制

半同步相对异步来说，能解决数据不丢失的情况，但是不能很好的解决切换问题

MySQL默认的复制是异步复制

主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理

配置了自动切换可能出现的问题：如果主库出现异常，此时主上已经提交的事务可能并没有同步到从库上，此时强行将从提升为主，就会导致新主库上的数据不完整

推荐的解决方案是，可写数据库切换的时候检查同步状态

2.配置主主半同步

测试环境：数据库mysql 5.7.20，CentOS7.2.1511(dvd)

1.准备数据库

yum install mysql-community-server

mysql-community-server-5.7.20-1.el7.x86_64

注意：安装server的时候,client也会被自动装上。但是装client，不会装server

使用无密码方式创建数据目录

目录一定要不存在

注意：CentOS7中mysql的多实例管理可以直接通过systemctl进行。在my.cnf配置中，section必须有@关键字才能进行管理，所以这里创建数据目录也统一使用实例名字

mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysqld@3320

配置my.cnf文件
[mysqld@3320] #注意section中要有@符号

datadir = /var/lib/mysqld@3320 #数据目录

socket = /var/lib/mysqld@3320/mysqld@3320.sock

port = 3320

pid-file = /var/run/mysqld@3320/mysqld@3320.pid

log-error = /var/log/mysqld.log

skip-name-resolve = 1

# bin-log相关配置

log-bin = mysql-bin

binlog_cache_size = 128K

innodb_flush_log_at_trx_commit = 1

binlog_format = MIXED

expire_logs_days = 7

max_binlog_size = 500M

log-slave-updates = 0

slave-skip-errors = 1062,1032

# relay-log配置

relay_log_recovery = 1

relay-log = mysqld@3320-relay-bin

# 数据库的配置

user = mysql

language = /usr/share/mysql/english

default-storage-engine = InnoDB

character-set-server = UTF8

master_info_repository = TABLE

relay_log_info_repository = TABLE

tmpdir = /var/tmp

# 设置read-only为可读可写

read-only = 0

#mysql5.7后必须要有server-id才能启动

server-id = 1
接下来可以启动数据库了

systemctl start mysqld@3320

2.创建用户

我们创建数据目录的方式是没有密码的。所以可以直接登陆

注意：只能使用sock文件方式登录

mysql -uroot -S /var/lib/mysqld@3320/mysqld@3320.sock

我们先设置root账户的密码。再创建一个用于配置半同步

SET password='admin';
grant all on *.* to 'root'@'127.0.0.1' identified by 'admin' with grant option;
flush privileges;

退出再登陆，看root密码是否创建成功

grant replication client, replication slave on *.* to 'SemiSync'@'%' identified by 'admin';
#远程同步账号需要reload，super权限

3.安装半同步插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

通过show plugins可以查看到安装的插件

安装完插件后修改my.cnf配置文件，在文件中加入下列选项

#启用binlog，启动半同步

sync_binlog = 1

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

另外一个节点重复上述步骤，创建一个相同的实例

注意：server_id 需要不同，半同步插件也需要安装，因为我们配置的是主主半同步

4.配置半同步

设置192.168.184.54为主，192.168.184.40为从

先在54上执行show master status，查看binlog文件及位置，显示如下

注意在这一步要确认没有数据写入，不然会导致不同步。通常在这里会锁表防止有新数据产生

然后在40上设置从

使用show slave statusG查看半同步状态。

这里的如果图中（没有截取完整的命令输出）最后两行不是Yes的话，可以在输出命令的Last_IO_Error或者Last_SQL_Error选项中查看错误提示。再根据提示处理

到这里，单向的半同步就配置好了，接下来在另外一个节点设置相同的半同步。这样就组成了主主半同步

现在就可以测试数据同步了