mysql主从复制

一.MySQL 主从复制主要用途

1.读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2.数据实时备份
当系统中某个节点发生故障时，可以方便的故障切换
3.高可用HA
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

二.复制原理

（1）Slave上面的IO线程连接上Master，并请求从指定Binary log文件的指定位置（或者从最开始的日志）之后的日志内容；
（2）Master接收到来自Slave的IO线程的请求后，通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave端的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在Master端Binary log文件的名称以及在Binary log中的位置；
（3）Slave的IO线程收到信息后，将接收到的日志内容依次写入到Slave端的RelayLog文件（中继日志）的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我。”
（4）Slave的SQL线程检测到Relay Log中新增加了内容后，会马上解析该Log文件中的内容成为在Master端真实执行时候的那些可执行的查询或操作语句，并在自身执行那些查询或操作语句，这样，实际上就是在master端和Slave端执行了同样的查询或操作语句，所以两端的数据是完全一样的。

简述：

主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

整个复制过程中主要有这三个线程：

在主库里，
binlog输出线程：
每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。对于每一个即将发送给从库的sql事件，binlog输出线程会将其锁住。一旦该事件被线程读取完之后，该锁会被释放

在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
从库I/O线程
当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。
从库的SQL线程
从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

---

三.配置(当主/备均为刚初始的数据库时)

初始化环境

#同步时间：
yum -y install ntp ntpdate
ntpdate cn.pool.ntp.org
hwclock --systohc

#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

在主库上：

1.修改主库配置文件
开启log-bin功能，并指定server_id，要跟从库不一样。这里log-bin必须指定，不然的话bin-log日志会根据主机名来命名，假如主库的主机名改变了，那主从复制就失效了。

vim /etc/my.cnf
[mysqld]
server_id=27
log-bin=mysql-bin
log-bin-index=master-bin.index #用于跟踪磁盘上存在哪些二进制日志文件，mysql用它来定位二进制日志

修改完成后重启服务

systemctl restart mysql

2.创建具有复制权限的用户

mysql> create user 'copy'@'%' identified by '123';
mysql> grant replication slave on *.* to 'copy'@'%';
刷新授权表信息
mysql> flush privileges;

3.锁表设置只读(为后面备份准备，注意生产环境要提前申请停机时间）

mysql> flush tables with read lock;
提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';
测试锁表后是否可以创建数据库

4.查看主库状态
查看主库状态，即当前日志文件名和二进制日志偏移量

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从库上

1.修改配置文件，必须指定中继日志的名称

[mysqld]
server_id=66
relay-log=relay-log
relay-log-index=relay-log.index #用于跟踪磁盘上存在哪些中继日志文件，mysql用它来定位中继日志

修改完成后重启服务

systemctl restart mysql

2.在从库里添加主库信息

mysql> change master to
MASTER_HOST='192.168.62.136',
MASTER_PORT=3306,
MASTER_USER='copy',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',  #日志文件名和日志偏移量要跟主库的状态对应起来
MASTER_LOG_POS=155;

3.开启主从同步

mysql> start slave;

检查状态：

mysql> show slave status\G

主从状况监测主要参数

Slave_IO_Running: IO线程是否打开 YES/No/NULL
Slave_SQL_Running: SQL线程是否打开 YES/No/NULL
Seconds_Behind_Master: 和主库同步的延迟的秒数

可能导致主从延时的因素：

主从时钟是否一致
网络通信是否存在延迟
是否和日志类型，数据过大有关
从库性能，有没开启binlog
从库查询是否优化

4.常见状态错误排除

发现IO进程错误，检查日志，排除故障：

[ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
找到原因：从5.6开始复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，如果主从都是克隆的机器可能uuid会一样

解决方法：
在mysql的数据目录里找到auto.cnf文件，将其修改一下，或者直接删除并重启服务，会生成一个新的，这样两个机器的uuid就不一样了

show slave status;报错：Error xxx doesn’t exist

解决方法：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;

5.成功开启主从同步后，进行测试

mysql> show slave status\G 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在主库上面创建数据库，写入数据，会自动同步到从库，但是从库写的数据不会同步到主库，因此一般将从库设置为只读模式来保证数据一致性

---

四.针对已经运行一段时间的主库实现主/备

已经运行一段时间的主库做主备，步骤跟上面差不多是一样的，就是需要将主库的数据先备份到从库，保证在做主从同步之前两个库的数据是一致的，然后再继续做主从。
需要注意的是如果这个从库以前做过主从，要将之前遗留下来的中继日志全部删除干净。

初始化备库（使其和主库数据一致）： 逻辑备份或物理备份

主库：

mysql> flush tables with read lock;	 #锁表设置只读，防止在给从库做备份期间主库有数据写入导致数据不一致
mysqldump -uroot -p123 --routines --events --triggers --master-data=2 --flush-logs --all-databases > all_`date +%F`.sql
scp all_2019-07-21.sql 192.168.62.136:/root

从库：

[root@localhost ~]# mysql -uroot -p123 <all_2019-07-21.sql 

然后做其他步骤，跟上面相同
注意最后测试的时候，要把主库的锁给解了

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

---

注意：
在做mysql双主模式时，要注意自增主键冲突的问题，即在复制的过程中，如果不设计主键的自增方式，B master到A master的数据肯定会因为ID冲突而插入失败。

解决办法：
事先设置好offset和increment的值，即：实现设置好自增字段的初始值和步长。主库A为奇数起步，主库B为偶数起步。两者都采用相同的步长。

A mater：
#-------------------------------------
auto_increment_offset=1
auto_increment_increment=2
#------------------------------------- 

B master：
#-------------------------------------
auto_increment_offset=2
auto_increment_increment=2
#-------------------------------------

这样，A 生成的ID就是 1,3,5,7...
B生成的ID为2,4,6,8....