MySQL 架构之主从复制和热备份

数据库扩展解决了什么问题？

热备份，多活，故障切换
负载均衡、读写分离

Replication（主从复制）常用架构

常规复制架构(Master --- Slaves)

在实际应用场景中，MySQL 复制 90% 以上都是一个 Master 复制到一个或者多个 Slave 的架构模式

缺点：

master 不能停机，停机就不能接收写请求
slave 过多会出现延迟。由于 master 需要进行常规维护停机了，那么必须要把一个 slave 提成 master，选哪一个是一个问题。

某一个 slave 提成 master 了，就存在当前 master 和之前的 master 数据不一致的情况，并且之前 master 并没有保存当前 master 节点的 binlog 文件和 pos 位置。

Dual Master （双主复制）复制架构(Master --- Master) Master)

可以配合一个第三方的工具，比如 keepalived 轻松做到 IP 的漂移，停机维护也不会影响写操作。

级联复制架构(Master --- Slaves --- Slaves ...) ...)

如果读压力加大，就需要更多的 slave 来解决，但是如果 slave 的复制全部从 master 复制，势必会加大 master 的复制 IO 的压力，所以就出现了级联复制，减轻 master 压力。

缺点： slave 延迟更加大了

Dual Master 与级联复制结合架构(Master - Master - Slaves)

这样解决了单点 master 的问题，也解决了 slave 级联延迟的问题.

Replication 机制的实现原理

mysql 数据热备份

命令行进入 mysql 的安装目录。如：
$ cd c:\Program Files\MySQL\MySQL Server 5.7\bin
在这个目录下就可以使用 mysqldump 工具。

1. 导出所有数据库
$ mysqldump -uroot -proot --all-databases >/tmp/all.sql
2. 导出db1、db2两个数据库的所有数据
$ mysqldump -uroot -proot --databases db1 db2 >/tmp/db1-db2.sql
3. 导出db1中的a1、a2表
$ mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1-tables-a1-a2.sql
4. 条件导出，导出db1表a1中id=1的数据
$ mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql
5. 只导出表结构不导出数据，--no-data
$ mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql
6. 跨服务器导出并且导入数据
$ mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错。加上 -C 参数可以启用压缩传递。
$ mysqldump --host=192.168.0.1 -uroot -proot -C --databases test |mysql --host=192.168.0.2 -uroot -proot test
这里需要注意，如果是热备份，这里需要在dump数据之前进行锁表操作，避免dump数据的时候出 现插入操作导致数据不一致的情况。

3、master 数据库锁表
mysql> flush table with read lock;

4、slave 数据库导入数据
$ mysql -uroot -proot < all.sql

5、master 数据库解锁
mysql> unlock tables;

Mysql 主从复制

docker 容器里面安装 vim

进入 docker 容器里面后

1. 备份 source.list 文件
$ mv /etc/apt/sources.list /etc/apt/sources.list.bak

2. 替换为阿里云国内镜像
$ echo "deb http://mirrors.aliyun.com/debian/ stretch main non-free contrib" >> /etc/apt/sources.list
echo "deb-src http://mirrors.aliyun.com/debian/ stretch main non-free contrib" >> /etc/apt/sources.list
echo "deb http://mirrors.aliyun.com/debian-security stretch/updates main" >> /etc/apt/sources.list
echo "deb-src http://mirrors.aliyun.com/debian-security stretch/updates main" >> /etc/apt/sources.list
echo "deb http://mirrors.aliyun.com/debian/ stretch-updates main non-free contrib" >> /etc/apt/sources.list
echo "deb-src http://mirrors.aliyun.com/debian/ stretch-updates main non-free contrib" >> /etc/apt/sources.list
echo "deb http://mirrors.aliyun.com/debian/ stretch-backports main non-free contrib" >> /etc/apt/sources.list
echo "deb-src http://mirrors.aliyun.com/debian/ stretch-backports main non-free contrib" >> /etc/apt/sources.list

3. 更新安装源
$ apt-get update

4. 安装 vim
$ apt-get install libtinfo5
此时提示:
You are about to do something potentially harmful.
To continue type in the phrase 'Yes, do as I say!'?]
输入：Yes, do as I say!

$ apt-get install vim -y

配置

命令：vim /etc/my.cnf

master 数据库 my.cnf 添加如下配置

server-id=135
#开启复制功能
log-bin=mysql-bin
# 自增 id 步长
auto_increment_increment=2
# 自增 id 偏移，例如：1   表示 id 从 1 开始自增，步长为 2， 则生成的id依次为：1，3，5，7，9......
# 自增 id 偏移，例如：2   表示 id 从 2 开始自增，步长为 2， 则生成的id依次为：2，4，6，8，10......
# 主从数据库架构这样设置的好处是：当数据同步时，不可能发生自增主键 id 重复的问题。
auto_increment_offset=1
# 数据库表名称小写
lower_case_table_names=1
#binlog-do-db=mstest //要同步的mstest数据库,要同步多个数据库
#binlog-ignore-db=mysql //要忽略的数据库

slave 数据库 my.cnf 添加如下配置

server-id=133
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
lower_case_table_names=1
#replicate-do-db=wang #需要同步的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema

配置完成后，重启 docker 容器

完整配置

master: /etc/mysql/conf.d/mysql.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'

server-id=3307
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
lower_case_table_names=1

slave: /etc/mysql/conf.d/mysql.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'

server-id=3308
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
lower_case_table_names=1

主从复制设置过程

1. 在master mysql添加权限
mysql> GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;

2. 在master上查看master的二进制日志
mysql> show master status;
+---------------------+---------+---------------+-------------------+-------------------+
|File                 |Position |Binlog_Do_DB   | Binlog_Ignore_DB  | Executed_Gtid_Set |
+---------------------+---------+---------------+-------------------+-------------------+
|mysql-bin.000001     |     154 |               |                   |                   |
+---------------------+---------+---------------+-------------------+-------------------+

3. 在slave中设置master的信息
mysql> change master to master_host='192.168.0.1',master_port=3307,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

4. 开启slave，启动SQL和IO线程
mysql> start slave;

5. 查看slave的状态
mysql> show slave status \G

6. 查看二进制日志是否开启
mysql> show global variables like "%log%";

7. 查看进程信息
mysql> SHOW PROCESSLIST;

8. 允许root远程连接
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Mysql 主从半同步复制

准备

加载 lib，所有主从节点都要配置
- 主库：install plugin rpl_semi_sync_master soname 'semisync_master.so';
- 从库：install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
- 可以一起装。建议一起装，因为会有主从切换的情景。
查看，确保所有节点都成功加载
- show plugins;
启用半同步
- 先启用从库上的参数，最后启用主库的参数
  - 从库：set global rpl_semi_sync_slave_enabled = {0|1}; # 1：启用，0：禁止
  - 主库：
    - set global rpl_semi_sync_master_enabled = {0|1}; # 1：启用，0：禁止
    - set global rpl_semi_sync_master_timeout = 10000; # 单位为 ms
- 风险：endbled 写入配置文件的话，会使实例启动后立即进入半同步模式，如果发生长时间断连的实例重新运行启动，有可能导致主库被拖垮。
- 建议：长时间断开的从库，重新连接后，要等待追完全部事务后，手动开启半同步模式，而不是启动后直接切换，防止冲击主库。
- master 的 my.cnf 配置
  - [mysqld]
  - rpl_semi_sync_master_enabled=1
  - rpl_semi_sync_master_timeout=1000
- slave 的 my.cnf 配置
  - [mysqld]
  - rpl_semi_sync_slave_enabled=1
- 从库重启 io_thread
  - stop slave io_thread;
  - start slave io_thread;
查询主库状态信息
- show global status like "%sync%";

状态信息重要参数

key	description
Rpl_semi_sync_master_clients	支持和注册半同步复制的已连 Slave 数
Rpl_semi_sync_master_no_times	master 关闭半同步复制的次数
Rpl_semi_sync_master_no_tx	master 没有收到 slave 的回复而提交的次数，可以理解为 master 等待超时的次数，即半同步模式不成功提交数量
Rpl_semi_sync_master_status	ON 是活动状态（半同步），OFF 是非活动状态（异步），用于表示主服务器使用的是异步复制模式，还是半同步复制模式
Rpl_semi_sync_master_tx_avg_wait_time	master 花在每个事务上的平均等待时间
Rpl_semi_sync_master_tx_waits	master 等待成功的次数，即 master 没有等待超时的次数，也就是成功提交的次数
Rpl_semi_sync_master_yes_tx	master 成功接收到 slave 的回复的次数，即半同步模式成功提交数量。

查询主库参数信息
- show global variables like '%sync%';

参数信息重要参数

key	description
rpl_semi_sync_master_enabled	（主库）是否启动半同步
rpl_semi_sync_master_timeout	等待多时毫秒后变成异步复制，默认是 10000ms
rpl_semi_sync_master_wait_point	5.7 默认 AFTER_SYNC（增强版半同步复制，无损复制模式），在得到 slave 的应答后再 commit，可选值 AFTER_COMMIT，在 master 提交后同步数据给 slave，然后 master 等待 slave 应答，应答成功返回客户端

after_commit 缺点
- 缺点 1：幻读。 当用户发起一个事务，该事务已经写入 redo 日志和 binlog 日志，但该事务还没写入从库，此时处在 waiting slave dump 处，此时另一个用户可以读取到这条数据，而他自己却不能；
- 缺点 2：数据丢失。 一个事务在 waiting slave dump 处 crash 后，主库将比从库多一条数据

mysql 官网配置信息详解

https://dev.mysql.com/doc/refman/5.6/en/replication-options-reference.html

Mysql 主从半同步复制的两种模式

AFTER SYNC

After Sync 是 MySQL5.7 官方新加的用以解决 MySQL5.6 半同步缺陷的选项，也是官方推荐的方式．实际上，客户端发出 commit 请求后，在主库上写入 binlog 并推送给 slave，slave 接收到 binlog 并写入 relaylog，发送 ACK 确认已经接收 binlog 后，master 在引擎层 commit，客户端接收 commit 完成，此时其他会话才可以看见已提交的数据．

故障分析：假设 master 在接收 ACK 确认时宕机，因为在引擎层并没有提交，HA 切换到从库，因为 binlog 已经写入从库的 relaylog，因此不会造成数据丢失，个人认为是目前比较完美的解决方式．

AFTER COMMIT

After commit 是 MySQL5.6 半同步参数，区别于 after sync , after sync 是在接收 ack 确认以后主库在引攀层做提交，而 after commit 是先在引擎层做提交后等待 ACK 确认。因此，在写入数据后并且在从库确认之前，其他的客户端可以看到在这一事务．故障分析：

binlog 未发送到从库：事务 B 获取到事务 A 提交的内容．此时宕机故障切换到 slave．事务 B 获取到的内容却丢失了。事务 A commit 没有收到反馈信息（则需要业务判断了）。
binlog 已经发送给从库：事务 B 获取到事务 A 提交的内容，故障切换到 slave, B 仍然获取到 A 提交的内容，没毛病。事务 A commit 没有收到反馈信息，若重新执行该事务，则相当于执行两次 A 事务（则需要业务判断了）。

Mysql Cluster

1. 创建docker network
$ docker network create cluster — subnet=192.168.0.0/16

2. 运行管理节点容器
$ docker run -d --net=cluster --name=management1 --ip=192.168.0.2 -v /home/mysql-cluster/conf/mysql-cluster.cnf:/etc/mysql-cluster.cnf mysql/mysql-cluster ndb_mgmd

3. 运行数据节点容器
$ docker run -d --net=cluster --name=ndb1 --ip=192.168.0.3 mysql/mysql-cluster ndbd
$ docker run -d --net=cluster --name=ndb2 --ip=192.168.0.4 mysql/mysql-cluster ndbd

4. 运行SQL节点容器
$ docker run -d --net=cluster --name=mysql1 --ip=192.168.0.10 -e MYSQL_ROOT_PASSWORD=123 mysql/mysql-cluster mysqld
$ docker run -d --net=cluster --name=mysql2 --ip=192.168.0.11 -e MYSQL_ROOT_PASSWORD=123 mysql/mysql-cluster mysqld

mysql-cluster.cnf 配置文件

Mysql 高可用

SYSLOGD_OPTIONS="" 改为 SYSLOGD_OPTIONS="-r -m 2 -c 2"
$ vi /etc/sysconfig/rsyslog

创建日志文件
$ touch /var/log/haproxy.log

启动日志
$ systemctl restart rsyslog.service

启动haproxy cd /usr/sbin/haproxy
$ ./haproxy -f /etc/haproxy/haproxy.cfg

在两台数据库添加远程访问权限
$ GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
$ FLUSH PRIVILEGES;

测试haproxy服务器能否连接到数据库服务器
安装mysql客户端
$ yum install -y mysql
$ mysql -uroot -proot -h 192.168.88.135
$ mysql -uroot -proot -h 192.168.88.133

在非haproxy服务器测试通过访问haproxy访问到mysql服务
$ mysql -uroot -proot -h 192.168.88.132 -P 3300

haproxy docker 安装

docker run -ti -d -p 3300:3300 -p 1080:1080 --name haproxy -v /home/haproxy/conf/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg haproxy:1.6.15

访问页面 http://192.168.88.132:1080/stats

keepalived 源码安装

$ wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
$ tar -zxvf keepalived-1.3.5.tar.gz
安装openssl openssl-devel
$ yum -y install openssl openssl-devel
$ ./configure --prefix=/usr/local/keepalived --sbindir=/usr/sbin/ --sysconfdir=/etc/ -- mandir=/usr/local/share/man/
$ make && make install

修改配置 $ vi /etc/keepalived/keepalived.conf $ chmod +x /etc/keepalived/chk.sh

keepalived yum 安装

预先安装好epel-release源
$ yum list installed|grep epel-release

查找可用安装的keepalived源
$ yum search keepalived

命令进行安装
$ yum install keepalived -y

启动keepalived服务
$ systemctl start keepalived

使用yum安装的会有一个默认配置文件模板 路径为/etc/keepalived/keepalived.conf

yum install ipvsadm -y

检测 mysql 的 docker 容器是否存活脚本

mysql -h 192.168.67.140 -u root -p123456 -P 3307 -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
    echo " $host mysql login successfully "
    exit 0
else
    echo " mysql login faild"
    killall keepalived
    exit 2
fi

脚本语法说明

mysql -h 192.168.67.140 -u root -p123456 -P 3307 -e "show status;" >/dev/null 2>&1 >/dev/null 2>&1 输出”黑洞” $? 上一个指令是否执行成功 0 成功 1 失败

MySQL 架构之主从复制和热备份 ​

数据库扩展解决了什么问题？ ​

Replication（主从复制）常用架构 ​

常规复制架构(Master --- Slaves) ​

Dual Master （双主复制）复制架构(Master --- Master) Master) ​

级联复制架构(Master --- Slaves --- Slaves ...) ...) ​

Dual Master 与级联复制结合架构(Master - Master - Slaves) ​

Replication 机制的实现原理 ​

mysql 数据热备份 ​

Mysql 主从复制 ​

docker 容器里面安装 vim ​

配置 ​

完整配置 ​

主从复制设置过程 ​

Mysql 主从半同步复制 ​

准备 ​

mysql 官网配置信息详解 ​

Mysql 主从半同步复制的两种模式 ​

AFTER SYNC ​

AFTER COMMIT ​

Mysql Cluster ​

mysql-cluster.cnf 配置文件 ​

Mysql 高可用 ​

haproxy docker 安装 ​

访问页面 http://192.168.88.132:1080/stats ​

keepalived 源码安装 ​

keepalived yum 安装 ​