MySQL主从复制、读写分离、MMM高可用

MySQL主从复制

概述

主从复制原理

Binary log:二进制日志 Relay log:中继日志 I/O thread:I/O线程, 状态必须为开启状态(YES). SQL thread:SQL语句线程, 状态同样必须为开启状态(YES).

1.在每个事务更新数据完成之前, Master在二进制日志中记录这些改变. 写入二进制日志完成后, Master通知存储引擎提交事务.

2.Slave将Master的二进制日志复制到其中继日志. 首先, Slave开始一个工作线程-I/O线程, I/O线程在Master上打开一个普通的连接, 然后开始Binlog dump process. Binlog dump process从Master的二进制日志中读取事件, 如果已经跟上Master, 它会睡眠并等待Master产生新的事件. I/O线程将这些事件写入中继日志.

3.SQL slave thread(SQL从线程)处理该过程的最后一步. SQL线程从中继日志读取事件, 并重放其中的事件而更新Slave的数据, 使其与Master中的数据一致. 只要该线程与I/O线程保持一致, 中继日志通常会位于Os的缓存中, 所以中继日志的开销很小.

注:复制过程有一个很重要的限制, 即复制在Slave上是串行化的, 也就是说Master上的并行更新操作不能在Slave上并行操作.

MySQL支持的复制类型
1.基于语句的复制
在主服务器上执行的SQL语句, 在从服务器上执行同样的语句, MySQL默认采用基于语句的复制, 效率比较高.

2.基于行的复制
把改变的内容复制过去, 而不是把命令在从服务器上执行一遍.

3.混合类型的复制
默认采用基于语句的复制, 一旦发现基于语句无法精确复制时, 就会采用基于行的复制.

建立时间同步环境

为了保证业务环境的实时同步, 需要建立时间同步环境.

MySQL主服务器
1.安装NTP

1	yum -y install ntp

2.配置NTP

1
2
3

vim /etc/ntp.conf
  server 时间供给源 #127.127.1.0 为本地
  fudge 时间供给源 stratum 8

阿里云时间服务器
ntp1.aliyun.com
ntp2.aliyun.com
ntp3.aliyun.com
ntp4.aliyun.com
ntp5.aliyun.com
ntp6.aliyun.com
ntp7.aliyun.com

3.重启服务并设置为开机启动

# 立即同步时间
ntpdate 时间供给源 

systemctl restart ntpd
systemctl enable ntpd

MySQL从服务器
1.安装NTPdate

1 2	yum -y install ntpdate ntpdate 主服务器ip地址

2.配置NTP

1
2
3

vim /etc/ntp.conf
  server 主服务器ip地址
  fudge 主服务器ip地址 stratum 8

3.重启服务并设置为开机启动

1 2	systemctl restart ntpd systemctl enable ntpd

修改主服务器

1.修改配置文件后并重启

vim /etc/my.cnf
  [mysqld]
  server-id = 11
  log_bin = master-bin
  log-slave-updates = true
  ...

systemctl restart mysqld

2.登录mysql, 给服务器授权.

mysql -u root -p[密码]

grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123456';
flush privileges;

show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     4035 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

注: 主服务器要关闭防火墙让从服务器访问, 或者对指定主机开放端口.

修改从服务器

1.修改配置文件后并重启

vim /etc/my.cnf
  [mysqld]
  server-id = #22 #33
  log_bin = slave-bin
  log-slave-updates = true
  ...

systemctl restart mysqld

2.从服务器配置同步

mysql -u root -p[密码]
change master to master_host='主服务器ip地址', master_user='myslave', master_password='123456', master_log_file='master-bin.000001', master_log_pos=4035;

stop slave;
reset slave;
start slave;
show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 4035
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 791
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...

reset slave执行时:
1.删除slave_master_info、slave_relay_log_info的表数据;
2.删除所有relay log文件, 并重新生成;
3.不会改变gtid_executed或gtid_purged的值

验证主从配置

在主服务器上创建测试数据库

show slave hosts;
create database db_test;
use db_test;
create table zang (id int(10),name varchar(10),address varchar(20));

在从服务器上查看测试数据库

1	show databases;

MySQL读写分离

简介

Atlas是由Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目. 它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上, 修改了大量bug, 添加了很多功能特性.

主要功能

1.读写分离
2.从库负载均衡
3.IP过滤
4.自动分表
5.DBA可平滑上下线DB
6.自动摘除宕机的DB

Atlas相对于官方MySQL-Proxy的优势:
1.将主流程中所有Lua代码用C重写, Lua仅用于管理接口.
2.重写网络模型、线程模型
3.实现了真正意义上的连接池
4.优化了锁机制, 性能提高数十倍.

配置读写分离

在主从复制已搭建的环境下:

MySQL-Master	192.168.100.10/24
MySQL-Slave1	192.168.100.20/24
MySQL-Slave2	192.168.100.30/24
MySQL+Atlas	192.168.100.40/24

MySQL-Master、Slave1、Slave2.

配置权限给Atlas

1 2	grant all on . to '用户名'@'192.168.100.%' identified by '密码'; flush privileges;

MySQL-Atlas

1.下载Atlas并上传到当前目录下

2.安装Atlas软件, 安装好后会在/usr/local/mysql-proxy目录下生成文件夹.

rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 

cd /usr/local/mysql-proxy/
ls

bin: 可执行文件
 |__ encrypt --对MySQL密码进行加密处理
 |__ mysql-proxy --MySQL自身的读写分离代理
 |__ mysql-proxyd --属于360, 管理服务的启动、重启、停止.

lib: 库及Atlas的依赖项
log: 日志文件

conf: 配置文件
 |__ test.cnf

3.对MySQL的密码进行加密

1 2	bin/encrypt 密码加密后的密码

4.编辑test.cnf配置文件

vim conf/test.cnf
  # 管理员登陆MySQL的用户名和密码
  admin-username = user
  admin-password = pwd

  # 代理MySQL-Master
  proxy-backend-addresses = 192.168.100.10:3306

  # 代理MySQL-Slave1、MySQL-Slave2.
  proxy-read-only-backend-addresses = 192.168.100.20:3306@1, 192.168.100.30:3306@1
  pwds = 用户名:加密后的密码

  # 管理员和代理登录MySQL时, 允许访问的IP地址与端口号.
  proxy-address = 192.168.100.40:1234
  admin-address = 192.168.100.40:2345

5.重启Atlas软件

bin/mysql-proxyd test start
ps -ef | grep mysql-proxy

bin/mysql-proxyd test restart

检测读写分离

1.用Yum安装MySQL后并登陆MySQL

yum -y install mysql

# 管理员登陆MySQL
mysql -h192.168.100.40 -uuser -ppwd -P2345
select * from backends;

+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 192.168.100.10:3306 | up    | rw   |
|           2 | 192.168.100.20:3306 | up    | ro   |
|           3 | 192.168.100.30:3306 | up    | ro   |
+-------------+---------------------+-------+------+

2.在MySQL-Master上创建一个表

1
2
3

create database db_test;
use db_test;
create table zang (id int(10),name varchar(10),address varchar(20));

3.停止MySQL-Slave1、MySQL-Slave2的复制.

1	stop slave;

4.在MySQL-Master上插入一条数据

1 2	use db_test; insert into zang values('1','master','this_is_master');

5.分别在MySQL-Slave1、MySQL-Slave2中插入一条数据

# MySQL-Slave1
use db_test;
insert into zang values('2','slave1','this_is_slave1');

# MySQL-Slave2
use db_test;
insert into zang values('3','slave2','this_is_slave2');

6.在MySQL-Atlas上测试读操作

# 代理登陆MySQL
mysql -h192.168.100.40 -u用户名 -p密码 -P1234

use db_test;
select * from zang;
select * from zang;
select * from zang;
select * from zang;

MySQL-MMM高可用

MMML(Master-Master replication manager for MySQL, MySQL主主复制管理器)是一套支持双主故障切换和双主日常管理的脚本程序.

MMM使用Perl语言开发, 主要用来监控和管理MySQL Master-Master(双主)复制, 这套脚本程序一方面实现了故障切换的功能, 另一方面其内部附加的工具脚本也可以实现多个Slave的read负载均衡.

MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip. 同时它还可以备份数据, 实现两节点之间的数据同步等, 但MMM无法完全保证数据的一致性.

mmm_mon: 监控进程, 负责所有的监控工作, 决定和处理所有节点角色活动. 此脚本需要在监管机上运行.
mnm_agent: 运行在每个MySQL服务器上的代理进程, 完成监控的探针工作和执行简单的远端服务设置. 此脚本需要在被监管机上运行.
mmm_control: 一个简单的脚本, 提供管理mmm_mond进程的命令.

mysal-mmm的监管端会提供多个虚拟IP(VIP), 包括一个可写VIP, 多个可读VIP, 通过监管的管理, 这些IP会绑定在可用MySQL之上, 当某一台MySQL宕机时, 监管会将VIP迁移至其他MySQL.

MMM高可用搭建

搭建环境:

MySQL-Master1	192.168.100.10/24
MySQL-Master2	192.168.100.20/24
MySQL-Slave1	192.168.100.30/24
MySQL-Slave2	192.168.100.40/24
MySQL+Atlas+monitor	192.168.100.50/24

配置MySQL主主复制

1.全部都安装上MySQL
二进制安装MySQL-5.7.30

2.配置Master1、2和Slave1、2的my.cnf文件

vim /etc/my.cnf
  # MySQL-Master1
  server_id = 11
  log_bin = master-bin
  log-slave-updates = true
  auto-increment-increment = 2
  auto-increment-offset = 1
  
  # MySQL-Master2
  [mysqld]
  server_id = 22
  log_bin = master-bin
  log-slave-updates = true
  auto-increment-increment = 2
  auto-increment-offset = 2
  
  # MySQL-Slave1
  [mysqld]
  server_id = 33
  relay-log = relay-log-bin
  relay-log-index = slave-relay-bin.index
  log-slave-updates = true
  
  # MySQL-Slave2
  [mysqld]
  server_id = 44
  relay-log = relay-log-bin
  relay-log-index = slave-relay-bin.index
  log-slave-updates = true

systemctl restart mysqld

注: 如果是虚拟机克隆出来的话, 需删除auto.cnf文件后重启.

1
2
3

systemctl stop mysqld
rm -rf /data/mysql/auto.cnf
systemctl start mysqld

3.配置Master1、2的主主模式

# MySQL-Master1
grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123456';
flush privileges;
show master status;--Master2定位

change master to master_host='192.168.100.20', master_user='myslave', master_password='123456', master_log_file='master-bin.000002', master_log_pos=615;

# MySQL-Master2
grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123456';
flush privileges;
show master status;--Master1定位

change master to master_host='192.168.100.10', master_user='myslave', master_password='123456', master_log_file='master-bin.000003', master_log_pos=1076;

Master1:
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     1076 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

Master2:
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      615 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

4.查看Master1、2的主从状态

1 2	start slave; show slave status\G;

Master1:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.20
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 615
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...

Master2:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 1076
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...

5.测试主主同步, 在Master2中新建一个库.

1
2
3

create database db_test;
use db_test;
create table zang (id int(10),name varchar(10),address varchar(20));

6.回到Master1中进行查看

1	show databases;

配置MySQL主从复制

1.查看Master1的状态值

1	show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     1249 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

2.配置Slave1、2为Master1的从库

change master to master_host='192.168.100.10', master_user='myslave', master_password='123456', master_log_file='master-bin.000003', master_log_pos=1249;

start slave;
show slave status\G;

配置MySQL-MMM

1.全部都安装上MMM

1 2	wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo yum install -y mysql-mmm*

2.在Master1、2和Slave1、2上授权monitor访问

# 监控用户授权
grant replication client on *.* to 'mmm_monitor'@'192.168.100.%' identified by 'monitor';

# 代理用户授权
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.100.%' identified by 'agent';
flush privileges;

3.修改每台的mmm_common.conf文件

vim /etc/mysql-mmm/mmm_common.conf
    active_master_role      writer
    <host default>
                              # 本机网卡
        cluster_interface       ens33 
        pid_path                /run/mysql-mmm-agent.pid
        bin_path                /usr/libexec/mysql-mmm/

                              # 主数据库用户
        replication_user        myslave

                              # 主数据库用户密码
        replication_password    123456

                              # 代理用户
        agent_user              mmm_agent
        
                              # 代理用户密码
        agent_password          agent
    </host>

    <host db1>
        ip      192.168.100.10
        mode    master
        peer    db2
    </host>

    <host db2>
        ip      192.168.100.20
        mode    master
        peer    db1
    </host>

    <host db3>
        ip      192.168.100.30
        mode    slave
    </host>

    <host db4>
        ip      192.168.100.40
        mode    slave
    </host>

    <role writer>
        hosts   db1, db2
        # 写操作的VIP地址
        ips     192.168.100.12 
        mode    exclusive
    </role>

    <role reader>
        hosts   db3, db4
        # 读操作的VIP地址
        ips     192.168.100.34, 192.168.100.35
        mode    balanced
    </role>

4.修改Master1、2和Slave1、2上的mmm_agent.conf文件, 不同的主机为不同的值.

vim /etc/mysql-mmm/mmm_agent.conf
    # Master1
    include mmm_common.conf
    this db1

    # Master2
    include mmm_common.conf
    this db2

    # Slave1
    include mmm_common.conf
    this db3

    # Slave2
    include mmm_common.conf
    this db4

5.修改monitor上的mmm_mon.conf文件

vim /etc/mysql-mmm/mmm_mon.conf
    include mmm_common.conf

    <monitor>
        ip                  127.0.0.1
        pid_path            /run/mysql-mmm-monitor.pid
        bin_path            /usr/libexec/mysql-mmm
        status_path         /var/lib/mysql-mmm/mmm_mond.status
        # 要监控的ip地址
        ping_ips            192.168.100.10, 192.168.100.20, 192.168.100.30, 192.168.100.40 
        auto_set_online     60

        # The kill_host_bin does not exist by default, though the monitor will
        # throw a warning about it missing.  See the section 5.10 "Kill Host
        # Functionality" in the PDF documentation.
        #
        # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
        #
    </monitor>

    <host default>
        # 监控用户
        monitor_user        mmm_monitor

        # 监控用户密码
        monitor_password    monitor
    </host>

    debug 0

6.启动代理和监控

# monitor
systemctl start mysql-mmm-monitor
systemctl enable mysql-mmm-monitor

# Master1、2和Slave1、2
systemctl start mysql-mmm-agent
service mysql-mmm-agent status
systemctl enable mysql-mmm-agent

7.配置读写分离
(1).Master1、2和Slave1、2配置权限给Atlas

1 2	grant all on . to 'Atlas'@'192.168.100.%' identified by '密码'; flush privileges;

(2).安装Atlas软件

1 2	wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

(3).将配置权限的密码进行加密

1
2
3

cd /usr/local/mysql-proxy/
bin/encrypt 密码
  加密后的密码

(4).编辑test.cnf配置文件

vim conf/test.cnf
  # 管理员登陆MySQL的用户名和密码
  admin-username = user
  admin-password = pwd

  # 代理MySQL-Master
  proxy-backend-addresses = 192.168.100.10:3306, 192.168.100.20:3306

  # 代理MySQL-Slave1、MySQL-Slave2.
  proxy-read-only-backend-addresses = 192.168.100.30:3306@1, 192.168.100.40:3306@1
  pwds = Atlas:加密后的密码

  # 设置Atlas的运行方式, 设为true时为守护进程方式, 设为false时为前台方式.
  daemon = true

  # 管理员和代理登录MySQL时, 允许访问的IP地址与端口号.
  proxy-address = 0.0.0.0:1234
  admin-address = 0.0.0.0:2345

(5).重启Atlas软件

bin/mysql-proxyd test start
ps -ef | grep mysql-proxy

bin/mysql-proxyd test restart

(6).检查读写分离

1 2	mysql -h192.168.100.50 -uuser -ppwd -P2345 select * from backends;

+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 192.168.100.10:3306 | up    | rw   |
|           2 | 192.168.100.20:3306 | up    | rw   |
|           3 | 192.168.100.30:3306 | up    | ro   |
|           4 | 192.168.100.40:3306 | up    | ro   |
+-------------+---------------------+-------+------+

7.在monitor上检查群集状态

1	mmm_control checks all

db4  ping         [last change: 2020/08/25 17:12:14]  OK
db4  mysql        [last change: 2020/08/25 17:12:14]  OK
db4  rep_threads  [last change: 2020/08/25 17:12:14]  OK
db4  rep_backlog  [last change: 2020/08/25 17:12:14]  OK: Backlog is null
db2  ping         [last change: 2020/08/25 17:12:14]  OK
db2  mysql        [last change: 2020/08/25 17:20:52]  OK
db2  rep_threads  [last change: 2020/08/25 17:26:04]  OK
db2  rep_backlog  [last change: 2020/08/25 17:12:14]  OK: Backlog is null
db3  ping         [last change: 2020/08/25 17:12:14]  OK
db3  mysql        [last change: 2020/08/25 17:12:14]  OK
db3  rep_threads  [last change: 2020/08/25 17:12:14]  OK
db3  rep_backlog  [last change: 2020/08/25 17:12:14]  OK: Backlog is null
db1  ping         [last change: 2020/08/25 17:12:14]  OK
db1  mysql        [last change: 2020/08/25 17:16:21]  OK
db1  rep_threads  [last change: 2020/08/25 17:12:14]  OK
db1  rep_backlog  [last change: 2020/08/25 17:12:14]  OK: Backlog is null

8.在monitor上查看群集状态

1	mmm_control show

db1(192.168.100.10) master/ONLINE. Roles: writer(192.168.100.12)
db2(192.168.100.20) master/ONLINE. Roles: 
db3(192.168.100.30) slave/ONLINE. Roles: reader(192.168.100.35)
db4(192.168.100.40) slave/ONLINE. Roles: reader(192.168.100.34)

(*1)关闭SeLinux子安全系统, 让agent访问数据.

1	setenforce 0

# Warning: agent on host db1 is not reachable
# Warning: agent on host db2 is not reachable
# Warning: agent on host db3 is not reachable
# Warning: agent on host db4 is not reachable

(*2)如果出现此状态, 则对所有主机进行时间同步.

1 2	yum -y install ntpdate ntpdate ntp1.aliyun.com

db1(192.168.100.10) master/AWAITING_RECOVERY. Roles: 
db2(192.168.100.20) master/AWAITING_RECOVERY. Roles: 
db3(192.168.100.30) slave/AWAITING_RECOVERY. Roles: 
db4(192.168.100.40) slave/AWAITING_RECOVERY. Roles:

测试MySQL-MMM

1.故障转移切换
(1)停止Master1的MySQL服务

# Master1
systemctl stop mysqld

# monitor
mmm_contorl show

# 几秒过后
mmm_contorl show

2.模拟主主故障插入数据测试, 虚拟ip地址漂移, 存在延10秒左右.

# monitor
mysql -uAtlas -h192.168.100.12 -p密码
use db_test;
insert into zang values('1','master','this_is_master');

# Master2
mysql -uroot -p[密码]
use db_test;
select * from zang;

3.在monitor中查看群集状态

systemctl start mysqld
mmm_contorl show 
mmm_contorl show 
mmm_contorl show

# 关闭状态
db2(192.168.100.20) master/HARD_OFFLINE. Roles:
# 正在恢复
db2(192.168.100.20) master/AWAITING_RECOVERY. Roles: 
# 已恢复在线状态
db2(192.168.100.20) master/ONLINE. Roles:

4.在Master1中查看数据是否同步

1
2
3

mysql -uroot -p[密码]
use db_test;
select * from zang;

5.模拟主从故障

# Slave1
systemctl stop mysqld

# monitor
mmm_contorl show

# Slave1
systemctl start mysqld

# monitor
mmm_contorl show