MySQL 主从复制总结

2021-02-16 | 129 浏览

MySQL主从复制总结

Mysql的主从架构模式，是很多企业广泛使用，并且大家所广为熟知的一种架构模式，这是DBA所应该熟练掌握的技能。

1.mysql主从复制主要用途

a.用于备份，避免影响业务

b.实时灾备，用于故障切换

c.读写分离，提供查询服务

2.mysql主从复制存在的问题

a.主库宕机后, 数据可能丢失

b.主库写压力大, 复制可能会延时

3.mysql主从复制解决方法

a.半同步复制、或者全同步复制. 要求: Mysql5.7版本

b.并行复制, 解决从库复制延迟的问题. 建议: 5.7版

4.mysql主从复制原理

1.在主库上把将更改DDL DML DCL记录到二进制日志Binary Log中。

2.备库I/O线程将主库上的二进制日志复制到自己的中继日志Relay Log中。

3.备库SQL线程读取中继日志中的事件，将其重放到备库数据库之上。

1.MySQL数据库传统复制

数据库传统主从复制如下, 如需实现多从, 将以下实验从案例在配置一次即可

操作系统	MySQL版本	IP	角色
Centos7.4	MysQL5.7	192.168.70.160	Master
Centos7.4	MysQL5.7	192.168.70.161	Slave

/etc/hosts文件解析
192.168.70.160 master1
192.168.70.161 Slave1
Master配置
//1.配置文件/etc/my.cnf
log-bin
server-id=160

//2.重启mysql服务生效
[root@Master ~]# systemctl restart mysqld

//3.模拟线上数据库
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | tt   |
|    2 | ttt  |
+------+------+

//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client  on *.*
to 'rep'@'192.168.70.%' identified by 'Rep123.com';

//5.导出当前数据
[root@Master ~]# mysqldump -uroot -pBgx123.com \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs  > /root/db-$(date +%F)-all.sql

//5.将备份文件传送至Slave
[root@Master ~]# scp /root/db-2018-05-10-all.sql  root@slave1:/root
Slave配置
//1.检查是否能使用远程账户登录
[root@slave ~]# mysql -hmaster -urep -pRep123.com

//2.修改配置文件/etc/my.cnf, 从需开启binlog
server-id=161

//3.重启mysql数据库服务
[root@slave ~]# systemctl restart mysqld

//4.导入数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Bgx123.com' -e "source /root/db-2018-05-10-all.sql"

//5.指向Master,无需指定binlogfile和pos
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com';

//6.启动slave角色
mysql> start slave;

//7.查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master1-bin.000003
          Read_Master_Log_Pos: 589
               Relay_Log_File: Slave1-relay-bin.000004
                Relay_Log_Pos: 613
        Relay_Master_Log_File: Master1-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2.MySQL数据库Gtid复制

数据库Gtid主从复制如下, 如需实现多从, 将以下实验从案例在配置一次即可

操作系统	MySQL版本	IP	角色
Centos7.4	MysQL5.7	192.168.70.160	Master
Centos7.4	MysQL5.7	192.168.70.161	Slave

/etc/hosts文件解析
192.168.70.160 master1
192.168.70.161 Slave1
注意:如果实验过传统主从复制, 请重置Slave数据库,文章后续会介绍
Master配置
//1.配置文件/etc/my.cnf
log-bin 
server-id=160
gtid_mode = ON 
enforce_gtid_consistency=1 

//2.重启mysql服务生效
[root@Master ~]# systemctl restart mysqld

//3.模拟线上数据库
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | tt   |
|    2 | ttt  |
+------+------+

//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client  on *.*
to 'rep'@'192.168.70.%' identified by 'Rep123.com';

//5.导出当前数据
[root@Master ~]# mysqldump -uroot -pBgx123.com \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs  > /root/db-$(date +%F)-all.sql

//5.将备份文件传送至Slave
[root@Master ~]# scp /root/db-2018-05-10-all.sql  root@slave1:/root
Slave配置
//1.重新初始化环境
[root@Slave1 ~]# systemctl stop mysqld
[root@Slave1 ~]# rm -rf /var/lib/mysql/*
[root@Slave1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@Slave1 ~]# systemctl start mysqld

//2.检查是否能使用远程账户登录
[root@slave ~]# mysql -hmaster -urep -pRep123.com

//3.修改配置文件/etc/my.cnf
server-id=161
gtid_mode = ON 
enforce_gtid_consistency=1
[可选,保存连接信息至表中]
#log-info-repository=TABLE
#relay-log-intp-repository=TABLE

//4.重启mysql数据库服务
[root@slave ~]# systemctl restart mysqld

//5.导入数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Bgx123.com' -e "source /root/db-2018-05-10-all.sql"

//6.指向Master,gtid自动协商同步
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1;

//7.启动slave角色
mysql> start slave;

//8.查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master1-bin.000003
          Read_Master_Log_Pos: 589
               Relay_Log_File: Slave1-relay-bin.000004
                Relay_Log_Pos: 613
        Relay_Master_Log_File: Master1-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.MySQL数据库双主复制

注意: 数据库双主M-M架构, 需要基于gtid的主从架构演变
Gtid M-M复制, 基于主从模式,演变为双主
//Master1
1.修改配置
    log -bin 
    server-id=1 
    gtid_mode = ON 
    enforce_gtid_consistency=1 
2.重启mysql数据库
    systemctl restart mysqld
3.授权
4.导出数据库

//Master2
1.修改配置
    log -bin 
    server-id=2
    gtid_mode = ON
    enforce_gtid_consistency=1 
2.重启mysql数据库
    systemctl restart mysqld
3.授权
    给master1授权，由于之前授权针对网段
4.恢复Master1数据, 保持主从同步

master1和master2数据必须保持一致
在master1上执行 change to master --> mater2
在master2上执行  change to master -->master1

4.MySQL数据库多源复制

数据库M-M-S-S
[root@Master1 ~]# cat /etc/hosts
192.168.70.160 master1
192.168.70.161 master2

192.168.70.162 slave2
192.168.70.163 slave3

 Master1
 1.修改配置
     log -bin 
     server_id=160
     gtid_mode = ON
     enforce_gtid_consistency=1
 2.重启mysql服务器
     systemctl restart mysqld
 
 3.授权(建议网段)
     mysql> grant replication slave,replication client on *.* to 
     rep@'192.168.70.%' identified by 'Rep123.com';
     mysql> flush privileges;
 
 4.导出对应的数据
     mysqldump -uroot -p'Bgx123.com' \
     --all-databases \
     --master-data=1 \
     --single-transaction \
     --flush-logs > /root/$(date +%F)-mysql-all.sql
 
 5.分发对应的数据
     scp  --> master2 
     scp  --> slave1 
     scp  --> slave2

Master2
1.修改配置
    server_id=161
    log_bin
    gtid_mode = ON
    enforce_gtid_consistency = 1
2.重启

3.导入数据
    [root@Master2 ~]# mysql -uroot -pBgx123.com  -e "reset master;"
    [root@Master2 ~]# mysql -uroot -pBgx123.com  < /root/2018-05-10-mysql-test.sql
4.验证远程账户是否可用
    1.防火墙
    2.账户密码

5.登陆数据库,清理从库的二进制日志
    mysql> reset master;

6.指定master
    mysql> change master to
        master_host='master1',
        master_user='rep',
        master_password='Rep123.com',
        master_auto_position=1;

7.启动 slave 角色
    mysql > start slave; 
8.查看角色状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master1-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: Master2-relay-bin.000002
                Relay_Log_Pos: 371
        Relay_Master_Log_File: Master1-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


Master1
    mysql> flush privileges;
    mysql>change master to
    master_host='master2',
    master_user='rep',
    master_password='Rep123.com',
    master_auto_position=1;
2.启动slave角色
    mysql > start slave; #
3.查看slave状态
mysql > show slave status\G

双主OK

Slave1和Slave2一致
1. 修改配置
    server_id=162
    gtid_mode = ON
    enforce_gtid_consistency = 1
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
2.重启数据库
    [root@slave2 ~]# systemctl restart mysqld

3. 初始化数据库 导入数据
    [root@slave2 ~]# mysql -uroot -p'Bgx123.com' -e "reset master;"
    [root@slave2 ~]# mysql -uroot -p'Bgx123.com' < /root/2018-05-10-mysql-test.sql 

4.清理二进制日志文件
    reset master;
  
//指向Master1
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1 for channel 'master1-channel';

//指向Master2
mysql> change master to
master_host='master2',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1 for channel 'master2-channel';

5.mysql > start slave;
6.mysql > show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master1-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: [email protected]
                Relay_Log_Pos: 371
        Relay_Master_Log_File: Master1-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
mysql> show slave status\G
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master1-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: [email protected]
                Relay_Log_Pos: 371
        Relay_Master_Log_File: Master1-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5.MysQL数据库读写分离

系统	数据库版本	地址	角色
Centos7.4	MySQL5.7	192.168.70.163	Mycat中间件
Centos7.4	MySQL5.7	192.168.70.160	MySQL-Master
Centos7.4	MySQL5.7	192.168.70.161	MySQL-Slave1
Centos7.4	MySQL5.7	192.168.70.162	MySQL-Slave2

1.部署MyCat

//Mycat依赖于Java环境
[root@Mycat ~]# yum install -y java
[root@Mycat ~]# java -version
openjdk version "1.8.0_161"
OpenJDK Runtime Environment (build 1.8.0_161-b14)
OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)

//下载MyCat
[root@Mycat ~]# mkdir /soft/
[root@Mycat ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /soft/
[root@Mycat ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

2.配置应用程序连接MyCat账户密码

[root@Mycat ~]# vim /soft/mycat/conf/server.xml
#注意:底下有user用户需注释

    <!-- 应用连接mycat账户-->
    <user name="blog">
       <!-- 应用连接mycat密码-->
        <property name="password">123456</property>
        <!-- 针对哪个库进行授权 -->
        <property name="schemas">blog</property>
    </user>
  
    <user name="www">
        <property name="password">123456</property>
        <property name="schemas">www</property>
    </user>

3.配置Mycat连接后端数据库

balance负载均衡类型
- 1.balance="0"不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
- 2.balance="1", 所有的主机都参与select语句的负载均衡, 但写语句还是由writeHost
- 3.balance="2"所有读操作都随机的在writeHost、readhost上分发。

[root@Mycat ~]# cat /soft/mycat/conf/schema.xml 
[root@Mycat conf]# cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!--链接mycat后的显示名称 -->
    <schema name="blog" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
    <schema name="www" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"> </schema>

    <!--dateNode数据节点 dataHost相当于主机池-->
    <dataNode name="dn1" dataHost="dn1pool" database="blog" />
    <dataNode name="dn2" dataHost="dn2pool" database="www" />

    <!--定义blog资源池限制-->
    <dataHost name="dn1pool" maxCon="1000" minCon="10" balance="1"
      writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <!--健康检查-->
    <heartbeat>select user()</heartbeat>
  
    <writeHost host="Master" url="192.168.70.160:3306" user="blog" password="Bgx123.com">
    <readHost host="Slave1" url="192.168.70.161:3306" user="blog" password="Bgx123.com" />
    <readHost host="Slave2" url="192.168.70.162:3306" user="blog" password="Bgx123.com" />
    </writeHost>
</dataHost>

    <!--定义www资源池限制-->
    <dataHost name="dn2pool" maxCon="1000" minCon="10" balance="1"
      writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

    <!--健康检查-->
    <heartbeat>select user()</heartbeat>
    <!--定义www库读写主机,可以定义多个-->
    <writeHost host="Master" url="192.168.70.160:3306" user="www" password="Bgx123.com">
    <readHost host="Slave1" url="192.168.70.161:3306" user="www" password="Bgx123.com" />
        <readHost host="Slave2" url="192.168.70.162:3306" user="www" password="Bgx123.com" />
    </writeHost>
</dataHost>
</mycat:schema>

4.配置MySQL相关信息, 均在MySQL-Master上操作

mysql> create databse blog;
mysql> create databse www;
mysql> create table blog.b(id int);
mysql> create table www.w(id int);
mysql> grant all  on www.* to www@'192.168.70.%' identified by 'Bgx123.com';
mysql> grant all  on blog.* to blog@'192.168.70.%' identified by 'Bgx123.com';

5.启动MyCat中间件

[root@Mycat ~]# /soft/mycat/bin/mycat start
Starting Mycat-server...
[root@Mycat conf]# lsof -i :8066
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
java    5352 root   76u  IPv6  52537      0t0  TCP *:8066 (LISTEN)
[root@Mycat conf]# lsof -i :9066
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
java    5352 root   72u  IPv6  52535      0t0  TCP *:9066 (LISTEN)

6.测试Mycat中间件

//需要开启日志为deblog可查看详细分离情况
[root@Mycat ~]# vim /soft/mycat/conf/log4j2.xml
<asyncRoot level="debug" includeLocation="true">
[root@Mycat ~]# tail -f /soft/mycat/log/mycat.log |grep "222222"


//连接Mycat使用不规则语句插入与查询, 便于查看策略
mysql> insert into blog.b valuse (222222);
mysql> select * from blog.b where id = '222222';

6..MySQL数据库架构演变

数据库