MySQL主从同步配置

（一）主从同步配置

MySQL主从默认使用异步模式。

异步模式下，主节点执行完客户端提交的事务后立即提交事务并返回给客户端，并不关心 log dump 线程是否成功地将将此次事务写进 binglog 并且发送给从库。假如执行事务的主线程提交事务后，log dump 线程还未来得及写入 binlog，此时系统宕机，则会造成 binglog 中没有保存刚才提交的事务，造成主从数据不一致。
优点：异步模式下，主线程不用关系同步操作，性能最好。
缺点：可能导致主从数据的不一致。

半同步方式，当主库在执行完客户端提交的事务后不是立即提交事务，而是等待 log dump 线程将此次事务同步到binlog 发送给从库，并且至少一个从库成功保存到其relay log中，此时主库的才提交事务并返回客户端。
优点：相比于异步模式，半同步方式一定程度上保证了数据同步的可靠性。
缺点：增加了主库响应客户端的延时，延时至少为一个 TCP/IP 的往返时间，即 binglog 发送给从库至收到从库的响应时间。

全同步方式，当主库在执行完客户端提交的事务后，必须等待此次的binlog发送给从库，并且所有从库成功地执行完该事务后，主库才能返回客户端。其与半同步复制的区别如下：半同步下，主库等待binlog写入到从库的relay log即可返回，全同步方式下，必须等到从库执行事务成功。
半同步下，至少一个从库响应后主库即可返回客户端，全同步下必须等待所有的从库返回。
优点：对比半同步复制方式，全同步复制方式数据一致性的可靠性进一步提高
缺点：执行事务时，主库需要等待所有的从库执行成功后才能返回，所以会大大提高主库的响应时间。

我使用的是两台centos7虚拟机来做实验的，主服务器ip为192.168.2.128，从服务器ip为192.168.2.130
安装mysql就不用说了吧，不对，我们需要安装的是mariadb，命令行安装yum install mariadb mariadb-server -y就可以了
主，从服务器安装好mariabd后，开启mysql服务，修改密码（应该是这条命令mysql_secure_installtion），修改好密码后进入mysql,命令mysql -uroot -p 你的密码

在主服务器上进行下列操作 ：

mysql> GRANT all privileges ON *.* TO 'repl'@'192.168.2.%' IDENTIFIED BY 'mysql';   #新建用户，并授权，注意要是全部权限（其实这个权限过大了），或者复制权限

修改my.cnf,vi /etc/my.cnf
在[mysqld]下面增加下面两行代码
server_id=1 #给数据库服务的唯一标识，我一般设置为服务器Ip最后一个数 
log-bin=master-bin #设置为master
保存退出并重启MySQL服务
查看日志
mysql> SHOW MASTER STATUS;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000001 | 1285 | | |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)
显示上面的内容说明配置正确了

记录File | Position下面的那两个值了，在从服务器配置时我们需要用到

 

从服务器配置如下 ：
也是修改my.cnf，在[mysqld]下面增加如下内容
server_id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
重启mysql服务器
进入mysql，执行下面的命令，其中Master服务器产生的File | Position对应下面的master_log_file和master_log_pos值，填写完之后在主服务器执行一下SHOW MASTER STATUS;确保这两个值是正确对应的

change master to master_host='192.168.2.128', //Master 服务器Ip
master_port=3306,
master_user='repl',
master_password='mysql',
master_log_file='master-bin.000001',
master_log_pos=1285;

然后start slave; 

接着在从服务器上shwo slave status\G;如果返回结果跟下面差不多（主要就是Slave_IO_Running和Slave_SQL_Running这两个值一定要是yes才是配置正确的）

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.128
                  Master_User: kali
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: slave-relay-bin.000005
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000003
             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: 245
              Relay_Log_Space: 1109
              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
1 row in set (0.00 sec)

补充：下面是我再配置过程中遇到的一些问题

一：我的第一个错误是执行start slave后报

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

上面这个错误是因为我master_log_file=’master-bin.000001’这里填错了，没有对应master服务器，master_log_pos的值不对应也会报错 
我因为配置了很多次主从都没有成功，一直报上面那个错误，所以可以使用stop slave;然后reset slave;重置slave配置，接着在执行那条change master命令,最后执行start slave;就不会报错了

二：show slave status \G;显示不正确
mysql >show slave status \G; 
不是显示 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
而是显示 
Slave_IO_Running: Connecting 
Slave_SQL_Running: Yes 
这表示我们还没有配置正确 
后来我试了试远程用reql用户登录主服务器myql，发现无法登录，后来上网查了查资料，发现原来是防火墙阻止了，那么让我们简单粗暴的解决问题吧，systmctl stop firewalld 

后来再次实验遇到过一个很郁闷的问题就是一直显示Slave_IO_Running: Connecting 

后来我发现创建用户时，我是这么创建的
grant all privileges on test.* to bp@’192.168.245.140’ identified by ‘123456’;
注意到有什么不一样的地方不。其实语句本身没有错误，但是用在这里却不行，会导致从库出错，从库必须对全部的数据库据都有全部权限,而我这里只授权了test里面的表权限，坑人啊

报slave_io running:no 可能是防火墙问题，可以尝试关闭mysql 主服务器的防火墙

 （二）主从同步延迟原因

1.slave服务器性能差

2.网络延迟

3.锁等待

4.master/slave系统负载高

（三）主从同步延迟解决方案

如果是常见的错误码导致的延迟，Slave_SQL_Running为No，比较简单临时处理做法是跳过该错误号，直接执行后续SQL，需要在my.cnf的mysqld模块下增加slave_skip_errors=错误码|all|ddl_exist_errors。需重启MySQL
常见error code代表的错误如下：

1007： 数据库已存在，创建数据库失败
1008： 数据库不存在，删除数据库失败
1050： 数据表已存在，创建数据表失败
1051： 数据表不存在，删除数据表失败
1054： 字段不存在，或程序文件跟数据库有冲突
1060： 字段重复，导致无法插入
1061： 重复键名
1068： 定义了多个主键
1094： 位置线程ID
1146： 数据表缺失，请恢复数据库
1053： 复制过程中主服务器宕机
1062： 主键冲突 Duplicate entry '%s' for key %d

1.升级从库服务器配置
2.增加从库
3.增加redis等nosql做缓存
4.限流、降级
5.在写入主库时，确保数据都同步到从库才返回这条数据写入成功，但是对性能和时间消耗很大
6.优化DDL执行效率
7.如果是网络延迟，可将主从放在同一个交换机下，且使用万兆网络
8. 升级MySQL版本到5.7，使用并行复制（5.7之前只支持单线程复制）
9.slave关闭sync_binlog,innodb_flush_log_at_trx_commit

PS:sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。两个值同时设置为1是，写入性能会受到一定限制，只有对数据安全要求很高的场景才建议使用，如涉及到金额的订单业务

innodb_flush_log_at_trx_commit 配置说明：

默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。 

（四）Binlog三种模式

 MySQL的binlog有三种模式:Row　　Statement　　Mixed
配置：在my.cnf的mysqld模块中配置binlog_format="STATEMENT"/"ROW"/"MIXED"

Row Level行模式，日志会记录每一行数据被修改的形式。优点：记录每一行数据修改的细节，不会出现某些特定情况下存储过程或function以及trigger的调用和出发无法被正确复制的问题。缺点：所有执行执行的语句当记录到日志中时，都将以每行记录的修改来记录，会产生大量的日志内容。

Statement Level语句模式（默认），每条会修改数据的sql都会记录到master的bin-log中。优点：解决了row level下的缺点，不需要记录每行数据的变化，减少bin-log日志量，节约IO，提高性能。缺点：特定情况下回造成MySQL的复制出现问题，主要是修改数据时使用某些特定函数或功能时会出现。

Mixed自动模式，MySQL会自动区分对待记录的日志格式，也就是在statement和row之间选择一种。

行模式和语句模式区别：删除100万数据，语句模式只记录一跳delete * from test，行模式会记录100万条删除命令

模式的选择，1.存储过程、触发器、函数比较少，可以选择默认的Statement语句模式，反之使用Mixed模式。如果存储过程、触发器、函数使用较多，又希望数据最大化一致，此时最好选择row行模式，但是要注意，该模式的binlog非常‘重’。


补充说明：

从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行SQL的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以mysql实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，semi-sync复制，指的就是主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了。

所谓并行复制，指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
1）主从复制的原理；
2）主从延迟问题产生的原因；
3）主从复制的数据丢失问题，以及半同步复制的原理；
4）并行复制的原理，多库并发重放relay日志，缓解主从延迟问题。

主从同步常见问题参考：
https://www.cnblogs.com/kevingrace/p/6261111.html