Mysql 主从复制（AB复制）

Mysql 主从复制

mysql主从复制（也叫AB复制）是一个异步的复制，从一个mysql实例（master）复制到另一个mysql实例（slave）。

实现整个主从复制，需要由master服务器上的IO进程和slave服务器上的sql进程共同完成。

M-S 传统（一主一从）

基本原理

1.mysql slave端的IO进程连接上master，向master请求指定日志文件的指定位置（或从最开始的日志）之后的日志内容。
2.master 接收到来自slae的IO进程的请求后，负责负责的IO进程更具根据slave的请求信息，读取相应日志内容，返回给slave的IO进程、并将本次请求读取bin-log文件名及位置一起返回给slave端。
3.slave的IO进程接收到信息后，将接收到的日志内容依次添加到slave端的relay-log文件的最末端，并将读取到的master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取是能清楚的告诉“master”我需要从某个bin-log的那个位置开始往后的日志内容，请发回来。
4.slave的sql进程检测到relay-log中新增加了内容后，回马上解析relay-log的内容称为master端真实执行时候的那些可执行内容，并在自身执行。

简而言之

首先开启master的binlog功能，因为整个mysql复制过程实际上就是slave从master端获取相应的二进制日志，然后在自己的slave端完全顺序的执行日志中所记录的各种操作。（二进制日志几乎记录了除select意外所有针对数据库的sql操作语句。）

举个栗子

主（master1）

准备数据

验证主从同步使用

master1
create database master1db;
create table master1db.master1tab(name char(50));
insert into master1db.master1tab values (1111);
insert into master1db.master1tab values (2222);

开启二进制日志

master1
# vim /etc/my.cnf
log-bin
server-id=1
systemctl restart mysqld

创建复制用户

master1
mysql> grant replication slave, replication
client on *.* to 'rep'@'192.168.2.%'  identified by 'QianFeng@123';

备份master数据库数据

master1
1.备份
mysqldump -p'password' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
2.发送给另一台主机
scp -r 2028-1-1-mysql-all.sql master2:/tmp
3.观察二进制日志分割点
# vim  2028-1-1-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;

从（master2）

测试rep用户是否可用

master2
mysql -h master1 -urep -p'password'

启动服务器序号

# vim /etc/my.cnf  #不用在从设备上开启二进制日志，没有人向master2请求日志。
server-id=2   #服务器ID是必须设置的。

# systemctl restart mysqld

恢复手动同步数据

mysql>  set sql_log_bin=0;
mysql>  source /tmp/2028-1-1-mysql-full.sql

设置主服务器

mysql> change master to
master_host='master1',
master_user='rep',
master_password='password',
master_log_file='localhost-bin.000002',
master_log_pos=154;
#二进制日志的位置，应该参照主服务器备份时生成的新位置。

启动从设备

mysql> start slave;

查看启动状态（IO-YES/SQL-YES）

mysql> show slave status\G;

返回主服务器（master1）更新数据，在从服务器（master2）观察是否同步。

M-S gtid（一主一从）

基于事物ID复制

全局事物标识：global transaction identifiersd 是用来代替传统复制的方法，gtid复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置，不在使用master_log_file + master_log_pos 开启复制，而是使用master_auto_postion=1 的方式开始复制。

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

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

GTID组成

GTID = source_id:transaction_id

source_id，用于鉴别原服务器，即mysql服务器唯一的的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID。 transaction_id，为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务。

示例

3E11FA47-71CA-11E1-9E33-C80AA9429562   :    23 
前面的一串为服务器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23为transaction_id

工作原理

1.master更新数据时，会在事物前产生GTID，一同记录到binlog日志中。
2.slave端的i/o 线程将变更的binlog，写入到本地的erelay log中。
3.sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4.如果有记录，说明该GTID的事务已经执行，slave会忽略。
5.如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

举个栗子

需求

与上一个实验需求相同。master1作为主mysql，master2作为从mysql。

不同之处，使用了
“gtid_mode=ON
enforce_gtid_consistency=1”
该属性自动记录position位置。不需要手动指定了。

环境

与实验一功能相同，需重置master2数据库
# systemctl stop mysqld
# rm -rf /var/lib/mysql/*
# systemctl start mysqld
# grep password /var/log/mysqld.log
# mysqladmin -p'VsudOt+g%5Nw' password 'password'

主

启动二进制日志，服务器ID，GTID

# vim /etc/my.cnf
log-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

# systemctl restart mysqld

授权复制用户rep

mysql> grant replication slave,replication client on *.* to 'rep'@'10.18.43.%' identified by 'password';

mysql> flush privileges;

备份数据

# mysqldump -p'password' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

# scp 2028-1-1-mysql-all.sql    master2:/tmp

从

测试rep用户是否可用

mysql -h master1 -urep -p'password'

启动二进制日志，服务器ID，GTID

# vim /etc/my.cnf
log-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

# systemctl restart mysqld

还恢复手动同步数据

set sql_log_bin=0;
source /tmp/2028-1-1-mysql-full.sql
select * from master1db.master1tab;

设置主服务器

mysql> change master to
master_host='master1',
master_user='rep',
master_password='password',
master_auto_position=1;

mysql> start slave;
mysql> show slave status\G

返回主服务器（master1）更新数据，在从服务器（master2）观察是否同步。

MM-SS(双主双从)

一主一从，主服务器单点设置，若主服务器故障会影响全局的写入事件。

故设置双主。

环境

mysql版本5.7

master1   master2   
slave1    slave2  
修改本地hosts以及主机名

配置master

master1

修改配置文件

#vim /etc/my.cnf    
[mysqld]
log-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1  

授权远程账户

mysql> grant replication slave,super,reload on *.* to slave@'192.168.182.%' identified by 'password';

mysql> flush privileges;

master2

修改配置文件

# vim /etc/my.cnf
[mysqld]
log-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1    

授权远程账户

mysql> grant replication slave,super,reload on *.* to slave@'192.168.182.%' identified by 'password';

mysql> flush privileges;

重启master1和master2上的mysqld

# systemctl restart mysqld

设置主从

master1

mysql > change master to
master_host='master2',
master_user='slave',
master_password='password',
master_auto_position=1;

mysql > start slave;               //启动slave角色
mysql > show slave status\G        //查看状态

master2

mysql > change master to
master_host='master1',
master_user='slave',
master_password='password',
master_auto_position=1;

mysql > start slave;               //启动slave角色
mysql > show slave status\G        //查看状态

至此互为主从配置成功

配置slave

slave1

修改配置文件

# vim /etc/my.cnf
[mysqld]
log-bin
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

重启服务

# systemctl restart mysqld

slave2

修改配置文件

# vim /etc/my.cnf
[mysqld]
log-bin
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

重启服务

systemctl restart mysqld

设置主服务器

slave1

mysql> change master to  
master_host='master1',
master_user='slave',
master_password='password',
master_auto_position=1 for channel 'master1';

mysql> change master to  
master_host='master2',
master_user='slave',
master_password='password',
master_auto_position=1 for channel 'master2';

mysql> start slave;

slave2

mysql> change master to  
master_host='master',
master_user='slave',
master_password='password',
master_auto_position=1 for channel 'master1';

mysql> change master to  
master_host='master2',
master_user='slave',
master_password='password',
master_auto_position=1 for channel 'master2';

mysql> start slave;

查看slave状态

mysql> show slave status \G

测试

1.只要slave状态中i/o进程和sql进程为YES，基本可以确定成功
2.主服务器上写入数据，在从服务器上查看，如果能在从上看到数据，则成功！