mysql之AB复制

一，MS复制（新数据库）

传统主从复制的基本过程如下：
1)、Mysql Slave端的IO进程连接上Master，向Master请求指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
2)、Master接收到来自Slave的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端真实执行时候的那些可执行的内容，并在自身执行。

用自己的话描述传统AB复制的工作原理：
主：I/O进程
从：I/O进程 从服务器通过I/O进程跟主服务器的I/O进程通信将主服务器的BINLOG日志同步过来，并且根据BINLOG日志的内容通过SQL进程往从库写数据


主：
有数据库：test 有表：t1 t2 t3
开启binlog日志 binlog0001.log 534
账户名slave

从：
binlog0001.log 534 记录下来 993


先确认原来的主里面有没有数据

AB复制
环境：MASTER原来没有旧数据
1.主从都关闭防火墙selinux
#/etc/init.d/iptables stop //rhel6
# systemctl stop firewalld //rhel7
#setenforce 0

2.主
安装软件mysql mysql-server //rhel6
安装软件mysql-community-client mysql-commnunity-server //rhel7

配置：
#vim /etc/my.cnf
[mysqld]
log-bin = mylog //master必写
server-id = 1 //master必写

创建账户：//主服务器上创建账户
mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by '123';
mysql> flush privileges;

%在工作环境当中换成slave的主机名称或者IP地址 密码采用复杂密码

重启服务：
#/etc/init.d/mysqld restart //rhel6
#systemctl restart mysqld //rhel7

注意:如果不成功删除以前的binlog日志
replication slave：
拥有此权限可以查看从服务器，从主服务器读取二进制日志。
super权限：
允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：
必须拥有reload权限，才可以执行flush [tables | logs | privileges]

3.从
安装软件
配置：
#vim /etc/my.cnf
[mysqld]
server-id = 2
log-bin=s1log //1.双主的时候必须写 2.gtid方式的ab复制必须写
master-host = 172.16.70.250 //mysql5.1的方法
master-user = slave
master-password = 123
启动服务：
#/etc/init.d/mysqld start /rhel5/6
#systemctl restart mysqld //rhel7

指定主服务器信息：
先去主服务器查看日志信息：
mysql > show master status \G

从服务器上指定：
mysql> help change master to
mysql> CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com', //主服务器IP
MASTER_USER='replication', //主服务器用户
MASTER_PASSWORD='bigs3cret', //用户密码
MASTER_PORT=3306, //端口
MASTER_LOG_FILE='master2-bin.001', //binlog日志文件名称
MASTER_LOG_POS=4, //日志位置
MASTER_CONNECT_RETRY=10; //如果同步失败的尝试次数

启动slave服务：
mysql > start slave; //关闭：stop slave
mysql > show slave status;

测试：
登录slave
mysql> show slave status \G

如果没成功：删除从的binlog重启服务

======================================

log_slave_updates参数：

当从库log_slave_updates参数没有开启时，从库的binlog不会记录来源于主库的操作记录。只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志

二，MS复制（已经存在数据）

M-S

环境 原来主库内有数据
===========================================================================
master1 (master) ---------> master2 (slave)
192.168.122.10 192.168.122.20

MS流程：

Master
1. log-bin server-id=1 restart
2. grant replication
3. 初始化数据库　mysqldump all databases ( log_file, position) scp rsync -------> master2

Slave
1. server-id=2
2. 初始化数据库 导入数据
3. mysql > change master to
master_host=' master1', #或master1 IP
master_user='授权用户',
master_password='授权密码',
master_log_file=' xxx',
master_log_pos= xxx;
4. mysql > start slave; #启动slave角色
5. mysql > show slave status\G

Master 部署

[master1]

Master 模拟增加数据

Slave 部署

[master2]
测试授权的用户

测试：

三，设置master_info以table形式存储

MySQL5.6/7中设置relay_log_info_repository 为TABLE

把relay.info记录在slave_relay_log_info表里有两个好处：
1.relay.info明文存储不安全，把relay.info中的信息记录在table中相对安全。
2.可以避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错。

检查relay_log_info_repository,master_info_repository值是否为table，relay_log_recovery 是否开启。
mysql> SHOW VARIABLES WHERE variable_name IN ('relay_log_recovery','relay_log_info_repository','master_info_repository');

relay_log_info_repository,master_info_repository值如果为FILE，建议将其修改为TABLE.

修改步骤：
1. stop slave;

2. set GLOBAL relay_log_info_repository='TABLE';

3.在my.cnf中设置
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = on

4.#systecmctl restart mysqld

5.mysql> start slave;

6. 检查relay_log_info_repository是否修改成功。
msyql> show variables where variable_name in ('relay_log_info_repository','master_info_repository');

查询主从同步的信息：
mysql> select * from mysql.slave_master_info;
mysql> select * from mysql.slave_relay_log_info;

四，relay_log自动修复

从MySQL5.5.X版本开始，增加了relay_log_recovery参数，这个参数的作用是：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

五，MS复制（GTID方式）（重点）

M-S

GTID 基于事务ID复制
GTID
全局事务标识：global transaction identifiers
是用来代替传统复制的方法， 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

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。

===========================================================================
master1 (master) ---------> master2 (slave)
192.168.122.10 192.168.122.20

建议把master2重置，因为上一个实验环境的原因

[root@master2 ~]# systemctl stop mysqld
[root@master2 ~]# rm -rf /var/lib/mysql/*
[root@master2 ~]# systemctl start mysqld
[root@master2 ~]# grep password /var/log/mysqld.log
[root@master2 ~]# mysqladmin -p'5ovl_*4WV0Ct' password '(TianYunYang123)'

MS流程 GTID：

Master
1. log-bin server-id=1 gtid_mode = ON enforce_gtid_consistency=1 restart
2. grant replication
3. 初始化数据库　mysqldump all databases scp rsync -------> master2

Slave
1. server-id=2 gtid_mode = ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE (slave)
2. 初始化数据库 导入数据
3. mysql > change master to
master_host=' master1',
master_user='授权用户',
master_password='授权密码',
master_auto_position=1;
4. mysql > start slave; #启动slave角色
5. mysql > show slave status\G

Master 部署

[master1]

binlog_format=row #强烈建议，其他格式可能造成数据不一致

SLave 部署

[master2]

binlog_format=row #强烈建议，其他格式可能造成数据不一致

实验的详细过程（对上面的整理）很重要：

2018/05/15 - 16:03 wing测试
环境：centos7u4
master 10.0.0.131
slave 10.0.0.130

所有机器都作以下操做：
修改主机名称
分别为：master和slave

域名解析
[root@master ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.131 master
10.0.0.130 slave

ssh公钥    //为了免密码登陆

ssh-keygen      制作公钥

ssh-copy-id -i  +IP    发送公钥给对方

关闭selinux和防火墙
[root@master ~]# setenforce 0 && systemctl stop firewalld

确保时间一致
主机之间的连同性

下载并安装Mysql5.7(所有机器都做)
#lftp 10.18.41.251
> cd 1802
> mirror mysql57

# cd mysql57
# rpm -ivh *.rpm

启动mysql:（所有机器都做）
# systemctl start mysqld

注意：
mysql5.7启动之后自带密码，不能直接登陆
获取mysql5.7初始启动密码：
# grep passw /var/log/mysqld.log
2018-05-15T07:12:00.852544Z 1 [Note] A temporary password is generated for root@localhost: N-+e*ZL:_3gh

测试密码是否可用：
# mysql -uroot -p'N-+e*ZL:_3gh'

这里注意在改为简单密码之前一定要先修改为一个复杂密码     // mysqladmin -uroot -p'N-+e*ZL:_3gh'    password  '新复杂密码'

去掉简单密码限制功能：
#vim /etc/my.cnf //追加如下选项
[mysqld]
validate_password=off           //加这一行是为了修改简单密码

重启服务
修改成简单密码：现在密码为123

--------------------
master:
修改配置文件:
#vim /etc/my.cnf
[mysqld]
validate_password=off

log-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

授权远程账户：
mysql> grant replication slave,super,reload on *.* to slave@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

slave:
修改配置文件
# vim /etc/my.cnf
[mysqld]
validate_password=off

log-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

重启master和slave上的mysqld:
[root@master mysql]# systemctl restart mysqld
[root@slave mysql]# systemctl restart mysqld

继续配置slave:
# mysql -u root -p123
mysql > change master to
master_host='master',
master_user='slave',
master_password='123',
master_auto_position=1;
mysql > start slave; //启动slave角色
mysql > show slave status\G //查看状态

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

六，MMSS（多源复制）

M-M-S-S

MultiSoure Replication
===========================================================================
M-M GTID
M-S GTID

M-M流程(master1 <-----> master2)：

　 续上一实验

Master2: 主服务器
1. log-bin server-id=2 gtid_mode=ON enforce_gtid_consistency=1 restart
2. master1 和 master2数据已经一致
3. 给master1授权，由于之前授权针对网段 'rep'@'192.168.122.%' 【master1】

Master1: 从服务器
mysql> CHANGE MASTER TO
MASTER_HOST='master2',
MASTER_USER='slave',
MASTER_PASSWORD='123',
master_auto_position=1

M-M OK

...

slave1,slave2:

MultiSource 多源复制

重新初始化数据库（保证master1,master2,slave1,slave2数据一致）


导入数据库报错：
解决：mysql> reset master; [slave1, slave2]

CHANGE MASTER TO

   MASTER_HOST='master1',
   MASTER_USER='slave',
   MASTER_PASSWORD='123',
   master_auto_position=1 for channel 'master1'

CHANGE MASTER TO
   MASTER_HOST='master2',
   MASTER_USER='slave',
   MASTER_PASSWORD='123',
   master_auto_position=1 for channel 'master2'

测试：

停止master1:

停止slave1或slave2

以上实验的详细过程

2018/05/16 - 14:48 wing测试
环境：centos7u4
master 10.0.0.131
master2 10.0.0.130
slave1 10.0.0.140
slave2 10.0.0.141

所有机器都作以下操做：
修改主机名称
分别为：master和master2 slave1 slave2

域名解析
[root@master ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.131 master
10.0.0.130 master2
10.0.0.140 slave1
10.0.0.141 slave2


ssh公钥
关闭selinux和防火墙
[root@master ~]# setenforce 0 && systemctl stop firewalld


确保时间一致
主机之间的连同性

下载并安装Mysql5.7(所有机器都做)
#lftp 10.18.41.251
> cd 1802
> mirror mysql57

# cd mysql57
# rpm -ivh *.rpm

启动mysql:（所有机器都做）
# systemctl start mysqld

注意：
mysql5.7启动之后自带密码，不能直接登陆
获取mysql5.7初始启动密码：
# grep passw /var/log/mysqld.log
2018-05-15T07:12:00.852544Z 1 [Note] A temporary password is generated for root@localhost: N-+e*ZL:_3gh

测试密码是否可用：
# mysql -uroot -p'N-+e*ZL:_3gh'

去掉简单密码限制功能(做实验的时候先不要作这一步)：
#vim /etc/my.cnf //追加如下选项
[mysqld]
validate_password=off

重启服务
修改成简单密码：现在密码为123

--------------------
master:
修改配置文件:
#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@'%' identified by 'Qianfeng123!@';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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@'%' identified by 'Qianfeng123!@';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

重启master和master2上的mysqld:
[root@master mysql]# systemctl restart mysqld
[root@master2 mysql]# systemctl restart mysqld

master:
# mysql -u root -p'Qianfeng123!@'
mysql > change master to
master_host='master2',
master_user='slave',
master_password='Qianfeng123!@',
master_auto_position=1;
mysql > start slave; //启动slave角色
mysql > show slave status\G //查看状态

master2:
# mysql -u root -p'Qianfeng123!@'
mysql > change master to
master_host='master',
master_user='slave',
master_password='Qianfeng123!@',
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 -u root -p'Qianfeng123!@'
mysql> change master to
master_host='master',
master_user='slave',
master_password='Qianfeng123!@',
master_auto_position=1 for channel 'master';

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

mysql> start slave;

slave2:
# mysql -u root -p'Qianfeng123!@'
mysql> change master to
master_host='master',
master_user='slave',
master_password='Qianfeng123!@',
master_auto_position=1 for channel 'master';

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

mysql> start slave;

两个slave分别查看状态：
mysql> show slave status \G
这里一般会看到错误如： Slave_IO_Runing:NO
Slave_SQL_Runing:YES
这里会报一个日志的错误，这里需要在master1和master2的sql的里面执行reset master
的操作，在slave1和slave2里面执行执行stop slave ， reset master，reset slave 操 作。
如果只能实现主从的同步，不能实现主主的同步可以看以下的操作，以下可以解决四台机器时时同步数据：

所有机器全部执行下面命令：
mysql > stop slave;
mysql > reset master;
mysql > reset slave;
mysql >
然后所有slave机器重新change master to 指定主服务器信息

测试：
1.只要slave状态中i/o进程和sql进程为YES，基本可以确定成功
2.主服务器上写入数据，在从服务器上查看，如果能在从上看到数据，则成功！
注意：每次关机时先要stop slave 再 systemctl stop mysqld 再关机

重新初始化mysql（就是彻底删除mysql）

查看现在是否存在Mysql进程：
#lsof -i:3306

如果有：
# systemctl stop mysqld

# pkill -9 mysqld //mysql5.5版本必须得这样杀进程(只限于编译安装)

删除配置配置文件：
# rm -rf /etc/my.cnf

清库：
# rm -rf /var/lib/mysql/*

想删除Mysql，除了上面那些步骤之外还需要作以下操作：
# yum erase `rpm -qa | grep mysql`
# rm -rf /var/log/mysql.log
# userdel -r mysql

针对以上错误的多源复制排错

所有机器全部执行下面命令：
mysql > stop slave;
mysql > reset master;
mysql > reset slave;
mysql >
然后所有slave机器重新change master to 指定主服务器信息

--------------------
错误：(这个方法适用于单主的情况，多主的情况下如果两个主的GTID一样也可以，否则不行)
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

1.master上：找到已经purge的gtid
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | a24eebd9-58dd-11e8-9199-000c29525d57:1-5,
e66e8e73-58dd-11e8-8480-000c2990f3a0:1-5 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | a24eebd9-58dd-11e8-9199-000c29525d57:1-5 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

2.在从服务器上强制设置跳过已经清理的gtid
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_purged = 'e66e8e73-58dd-11e8-8480-000c2990f3a0:1-5';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql> reset master; //如果上一步报错，则执行重置master,然后再次执行上一步命令
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_purged = 'e66e8e73-58dd-11e8-8480-000c2990f3a0:1-5';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
看到两个进程状态为YES

NTP时间服务器

NTP时间服务器服务器端：
软件安装
# yum install ntp -y

修改配置文件：

# vim /etc/ntp.conf 删除21到24行，替换成下面两行
server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 2

启动服务：端口是123
# systemctl start ntpd


客户端跟时间服务器对时间：
# yum install ntpdate -y
# ntpdate 时间服务器的IP地址