mysql 主从同步

1 MySQL主从介绍
2 准备工作
3 配置主
4 配置从
5 测试主从同步

1 MySQL主从介绍

 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，
 另外一台B也会跟着写数据，两 者数据实时同步的
 MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
 主从过程大致有3个步骤
 1）主将更改操作记录到binlog里
 2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
 3）从根据relaylog里面的sql语句按顺序执行
 主上有一个log dump线程，用来和从的I/O线程传递binlog
 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，
 另外一个SQL线程用来把relaylog里面的sql语句落地

2 准备工作

主从安装数据库

3 配置主

安装mysql
 修改my.cnf，增加server-id=130和log_bin=aminglinux1
 修改完配置文件后，启动或者重启mysqld服务
 把mysql库备份并恢复成aming库，作为测试数据
 mysqldump -uroot mysql > /tmp/mysql.sql
 mysql -uroot -e “create database aming”
 mysql -uroot aming < /tmp/mysql.sql
 创建用作同步数据的用户
 grant replication slave on *.* to 'repl'@slave_ip identified by 'password';
 flush tables with read lock;
 show master status;
 #备份数据库
 mysqldump -uroot test2 > /tmp/test2.sql
 

4 配置从

将主上备份的数据库文件拷到从
[root@localhost mysql]# scp 192.168.88.132:/tmp/test2.sql /tmp/
The authenticity of host '192.168.88.132 (192.168.88.132)' can't be established.
ECDSA key fingerprint is SHA256:2hHTtNk79V3FSh1NUUM+lRMeJWgHjkQ6bhRaKZwp+5I.
ECDSA key fingerprint is MD5:f5:72:66:36:e1:74:7e:58:b8:43:ea:f8:ae:6e:98:8a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.88.132' (ECDSA) to the list of known hosts.
root@192.168.88.132's password: 
test2.sql   
安装mysql
 查看my.cnf，配置server-id=132，要求和主不一样
 修改完配置文件后，启动或者重启mysqld服务
 把主上aming库同步到从上
 可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库
 mysql -uroot
 stop slave；
 change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx,
 start slave;
 还要到主上执行 unlock tables
 #报错
 Slave_IO_Running: connecting
Slave_SQL_Running: Yes
 #解决
 检查防火墙

5 测试主从同步

从上执行mysql -uroot
 show slave stauts\G
 看是否有
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 还需关注
 Seconds_Behind_Master: 0  //为主从延迟的时间
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:主服务器上
 binlog-do-db=      //仅同步指定的库
 binlog-ignore-db= //忽略指定库
 从服务器上
 replicate_do_db= //仅同步指定的库
 replicate_ignore_db= //忽略指定库
 replicate_do_table=
 replicate_ignore_table=
 replicate_wild_do_table=   //如aming.%, 支持通配符% 
 replicate_wild_ignore_table=

主上 mysql -uroot aming  
 select count(*) from db;
 truncate table db;
 到从上 mysql -uroot aming
 select count(*) from db;
 主上继续drop table db;
 从上查看db表

总结

主从的目的？
实时备份（也叫热备）
冷备mysqldump（也叫冷备）
为什么要设置主从
防止主挂掉，从可以实时快速的恢复业务
读写分离
主 写
从 读
一主多从：主写从读，负载降低，能承担更大的并发量
不停库做主从
xtrabackup→innobackupex（支持备份master）
主从复制方式
异步方式

在主节点写入日志即返回成功，默认情况下MySQL5.5/5.6/5.7和mariaDB10.0/10.1的复制功能是异步的。
异步复制可以实现最佳的性能，主库把binlog日志发送给从库，这一动作就结束了，并不验证从库，
会造成主从库数据不一致。

半同步方式

一主多从模式下，有一个从节点返回成功，即成功，不必等待多个节点全部返回。
MySQL半同步，MySQL5.5由Google贡献的补丁才开始支持半同步复制（semi Replication）模式，
该模式可以确保从服务器接收完主服务器发送的binlog日志文件并写入自己的中继日志（relay log）里，
然后会给主服务器一个反馈，告诉对方已经接收到完毕，这时主库线程才返回当前session告知操作完成，
当出现超时情况时，源主服务器会暂时切换到异步复制模式，直到至少有一台设置为半同步复制模式的
从服务器及时收到信息为止。

GTID

2、GTID事物是全局唯一性的，且一个事务对应一个GTID。

3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。
而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。

6、在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，
目的是记录执行过的GTID（强制）。

二、GTID的组成部分：

前面是server_uuid：后面是一个序列号

例如：server_uuid：sequence number

7800a22c-95ae-11e4-983d-080027de205a:10

UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。

Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

三、GTID比传统复制的优势：

1、更简单的实现failover，不用以前那样在需要找log_file和log_Pos。

2、更简单的搭建主从复制。

3、比传统复制更加安全。

4、GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。

四、GTID的工作原理：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

要点：

1、slave在接受master的binlog时，会校验master的GTID是否已经执行过（一个服务器只能执行一次）。

2、为了保证主从数据的一致性，多线程只能同时执行一个GTID。

六、使用GTID搭建mysql的主从复制的主要参数：

[mysqld]
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=2003306    #每天实例的server_id都要不一样
 
#binlog
log-bin=mysqlbin
log-slave-updates=1   #允许下端接入slave
binlog_format=row      #强烈建议，其他格式可能造成数据不一致
 
#relay log
skip_slave_start=1
注意：建议使用mysql-5.6.5以上的最新版本。

（二）、启动GTID的两种方法：

方法一、

1、如果是在已经跑的服务器，你需要重启一下mysql server。

2、启动之前，一定要先关闭master的写入，保证所有slave端都已经和master端数据保持同步。

3、所有slave需要加上skip_slave_start=1的配置参数，避免启动后还是使用老的复制协议。

方法二、

1、如果是新搭建的服务器，直接启动就行了。

七、master-slave搭建的注意事项：

（一）、使用GTID的方式，把salve端挂载master端：

1、启动以后最好不要立即执行事务，而是先change master上。

2、然后在执行事务，当然知不是必须的。

3、使用下面的sql切换slave到新的master。

stop slave;

change master to

master_host = 192.168.100.200,

master_port = 3306,

master_user = abobo,

master_password=123,

master_auto_position = 1;

(二)、如果给已经运行的GTID的master端添加一个新的slave

 有两种方法：

方法一、适用于master也是新建不久的情况。

1、如果你的master所有的binlog还在。可以选择类似于上面的方法，安装slave，直接change master to到master端。

2、原理是直接获取master所有的GTID并执行。

3、优点：简单方便。

4、缺点：如果binlog太多，数据完全同步需要时间较长，并且master一开始就启用了GTUD。

方法二、适用于拥有较大数据的情况。（推荐）

1、通过master或者其他slave的备份搭建新的slave。（看第三部分）

2、原理：获取master的数据和这些数据对应的GTID范围，然后通过slave设置@@global.gtid_purged跳过备份包含的gtid。

3、优点：是可以避免第一种方法的不足。

4、缺点：相对来说有点复杂。

（三）、通过备份搭建新的slave：（方法二的扩展）

两种方法：

方法一、mysqldump的方式：

1、在备份的时候指定--master-data=2（来保存binlog的文件号和位置的命令）。

2、使用mysqldump的命令在dump文件里可以看到下面两个信息：

SET @@SESSION.SQL_LOG_BIN=0;

SET @@GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8';

3、将备份还原到slave后，使用change master to命令挂载master端。

注意：在mysql5.6.9以后的命令才支持这个功能。

方法二、percona Xtrabackup

1、Xtrabackup_binlog_info文件中，包含global.gtid_purged='XXXXXX:XXXX'的信息。

2、然后到slave去手工的 SET GLOBAL.GTID_PURGED='XXXXXX:XXXX'。

3、恢复备份，开启change master to 命令。

注意：如果系统运行了很久，无法找到GTID的变好了，可以通过上面的方式进行查找。

八、GTID如何跳过事务冲突：

1、这个功能主要跳过事务，代替原来的set global sql_slave_skip_counter = 1。

2、由于在这个GTID必须是连续的，正常情况同一个服务器产生的GTID是不会存在空缺的。
所以不能简单的skip掉一个事务，只能通过注入空事物的方法替换掉一个实际操作事务。

3、注入空事物的方法：

stop slave;

set gtid_next='xxxxxxx:N';

begin;commit;

set gtid_next='AUTOMAIC';

start slave;

4、这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。

九、GTID的参数注释：
[master]>show global variables like '%gtid%';
1、enforce_gtid_consistency：开启gtid的一些安全限制（介意开启）。
 
2、gtid_executed：全局和seeeion级别都可以用。用来保存已经执行过的GTIDs。
贴士：show  master status\G;输出结果中的Executed_Gtid_Set和gitd_executed一致。reset master时，此值会被清空。
 
3、gtid_owned：全局和session级别都可用，全局表示所有服务器拥有GTIDs，session级别表示当前client拥有所有GTIDs。（此功能用的少）
 
4、gtid_mode：是否开启GTID功能。
 
5、gtid_purged：全局参数，设置在binlog中，已经purged的GTIDs，
并且purged掉的GTIDs会包含到gtid_executed中。
贴士：从而导致slave不会再去master请求这些GTIDs，并且Executed_Gtid_Set为空时，才可以设置此值。
 
6、gtid_next：这个时session级别的参数：
[master]>show session variables like '%gtid_next%';
 
十、关于GTID的一些功能限制：
（一）、更新非事务引擎：
1、Case重现：
master：对一个innodb表做一个多sql更新的事物，效果是产生一个GTID。
slave：对应的表是MYISAM引擎，执行这个GTID的第一个语句后就会报错，因为非事务引擎一个sql就是一个事务。
 
2、错误编号：
last_Errno:1756
 
3、异常恢复方案：
（1）、简单的stop slave; start slave;就能够忽略错误。但是这个时候主从的一致性已经出现问题。需要手工的把slave差的数据补上。
（2）、首先将引擎调整为一样的，slave也改为事务引擎。
 
（二）、create table ....select statements
1、case重现：
 master：直接执行一个create table select * from table;的sql
 
2、报错：
error 1786
 
3、原理：
由于create table ...select语句会生成两个sql，一个是DDL创建表SQL，
一个是insert into 插入数据的sql。由于DDL会导致自动提交，所以这个sql至少需要两个GTID，
但是GTID模式下，只能给这个sql生成一个GTID，如果强制执行会导致和上面更新非事务引擎一样的结果。
 
（三）、一个sql同事操作innodb引擎和myisam引擎：
case重现：t1表是innodb，t2表是myisam
1、update t1,t2 set t1.id=1000,t2.id=1000 where t1.id=t2.id;
2、报错：1785
3、原理和第二个相同。
 
（四）、在一个replication grouop 中，所有的mysql必须要统一开启或者关闭GTID功能。
1、case重现：
将一个未开启gtid的slave通过原始的binlog和pos方式连接到开启GTID的master。
 
2、报错：
The slave IO thread stops because the master has @@GLOBAL.GTID_MODE ON and this server has @@GLOBAL.GTID_MODE OFF。
 
（五）、在一个replication group中，如果开启GTID以后，就不再允许使用classic的复制方式：
1、case重现：
将一个开启gtid的slave通过原始的binlog和pos方式连接到开启GTID的master。
 
2、报错：
ERROR 1776(HY000)：Parameters MASTER_LOG_FILE，MASTER_LOG_POS，RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active。
 
（六）、GTID_MODE是not online的：
需要重启才能生效，官方暂时不支持平滑的从classic replication切换到GTID replication。
贴士：
由于GTID开启需要重启系统，一个复制组中所有的实例必须统一开启或者关闭GTID,开启GTID以后不能在使用classic复制。
问题：
也就是说在线业务必须统一关闭，然后再启动，会导致服务中断。
 
解决方案：
1、针对这种情况，社区有两种对应的平滑升级的方案：
一种是booking.com出品，这两个差别在淘宝9月份数据库月报里有说明，加了一个桥接的服务器，既可以运行GTID模式下，也可以运行classic模式下。
另外一种是facebook.com出品。所有的slave可以在开启GTID模式的情况下，可以连接到没有开启GTID模式的master。
 
2、可以关闭一个部分，停止写操作，但是读不用，将另一部分改成GTID模式。
 
（七）、Temporary tables。
1、create temporary table和drop temporary table语句一样在GTID环境下不支持。
如果--enforce_gtid_consistency参数开启，并且autocommit=1，那么可以使用。
 
（八）、关于Errant transaction
1、Errant transaction：所谓的errant transaction也就是没有规范的从master执行，
而是直接从slave执行的事务。
2、由于GTID协议的原因，最开始已经提过（参见GTID architecture）。
3、如果slave有errant transaction产生，由于GTID协议中的规则，很容易导致failover失败。
主要有两种情况：
 
a、在slave上做了无用的或者临时的errant transaction操作，如果该slave升级成为master的话，
连接到它的所有数据库都会获取到这个事务。如果一样就会产生冲突。
 
b、由于做了这个errant transaction这个事务以后，其他的slave还没有获取这个errant transaction的GTID，
需要从master上发同步给其他的slave，但是主的binlog又被删掉了，这时将会报错。
 
4、总之：尽量避免产生errant transaction。可以通过：set sql_log_bin=off的方式在slave执行sql，
但是也要考虑到数据一致性。

MySQL 主从复制主要有以下几种方式：
 基于 SQL 语句的复制(statement-based replication, SBR)；
 基于行的复制(row-based replication, RBR)；
混合模式复制(mixed-based replication, MBR)；
 基于 SQL 语句的方式最古老的方式，也是目前默认的复制方式，后来的两种是 MySQL 5 以后才出现的复制方式。
RBR 的优点：

 任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
RBR 的缺点：

binlog 大了很多
 复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，
这会导致频繁发生 binlog 的并发写问题
无法从 binlog 中看到都复制了写什么语句
SBR 的优点：

历史悠久，技术成熟 binlog 文件较小
binlog 中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
binlog 可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR 的缺点：

 不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候。
 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响 
 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源。

无论选择哪种方式复制，都会影响到复制的效率以及服务器的损耗，甚以及数据一致性性问题，
目前其实没有很好的客观手手段去评估一个系统更适合哪种方式的复制。

第二个问题是关于主从同步的监控问题，Mysql 有主从同步的状态信息，可以通过命令 show slave status获取，
除了获知当前是否主从同步正常工作，另外一个重要指标就是 Seconds_Behind_Master，
从字面理解，它表示当前 MySQL 主从数据的同步延迟，单位是秒，但这个指标从 DBA 的角度并不能简单的理解为延迟多少秒，
感兴趣的同学可以自己去研究，但对于应用来说，简单的认为是主从同步的时间差就可以了，另外，当主从同步停止以后，
重新启动同步，这个数值可能会是几万秒，取决于主从同步停止的时间长短，我们可以认为数据此时有很多天没有同步了，
而这个数值越接近零，则说明主从同步延迟最小，我们可以采集这个指标并汇聚曲线图，
来分析我们的数据库的同步延迟曲线，然后根据此曲线，给出一个合理的阀值，主从同步的时延小于阀值时，
我们认为从库是同步的，此时可以安全的从从库读取数据。

有的同学，遇到主从不能正常同步，提示uuid相同的错误。这是因为克隆机器导致。
https://www.2cto.com/database/201412/364479.html

扩展部分
不停库不锁表在线主从配置
http://seanlook.com/2015/12/14/mysql-replicas/
主从不同步
http://www.rfyy.net/archives/2309.html
http://blog.51cto.com/storysky/259280
主主
关于 auto_increment https://blog.csdn.net/leshami/article/details/39779509
http://www.cnblogs.com/ygqygq2/p/6045279.html
mysql-proxy 实现读写分离
http://blog.51cto.com/zzclinux/1980487
mysql-proxy类似的产品有：
mycat 基于阿里的开源软件cobar，官网 www.mycat.io
https://my.oschina.net/ruoli/blog/1789370
mycat实现分库分表
https://www.cnblogs.com/joylee/p/7513038.html
atlas 出自于360，不维护不更新了 https://blog.csdn.net/AnPHPer/article/details/80566385
mysql环形主从
http://ask.apelearn.com/question/11437
mysql架构演变 http://www.aminglinux.com/bbs/thread-8025-1-1.html
MHA架构
http://blog.51cto.com/xiaoshuaigege/2060768
比较复杂的mysql集群架构 http://ask.apelearn.com/question/17026

转载于:https://my.oschina.net/zenghong133/blog/3057260