飞天班第50节：数据库高级应用-3

1、MySQL主从复制实现高可用

数据库永远是系统最脆弱的环节

80%都是查询（压垮数据库）
缓存来保护数据库：Redis、ElasticSearch
数据库系统要有一定的容灾能力
数据状态化明显，分布式、集群

高可用衡量标准

一般是通过系统的可靠性和可维护性来衡量的，有3个标准：

MTTF：平均无故障时间（衡量可靠性）
MTTR：衡量可维护性，HA = MTTF/(MTTF + MTTR) *100%
SLA（服务标准协议）：

99%，表示一年的故障时间和宕机时间不超过4天

99.9%，表示一年的故障时间和宕机时间不超过10个小时

99.99%，表示一年的故障时间和宕机时间不超过1个小时

99.999%，表示一年的故障时间和宕机时间不超过6分钟，阿里云上的很多服务承诺不超过这个时间

数据库高可用的三种方式

主从（非对称）

master-slave，热数据备份，从库一般是用来实时备份，从机可以快速切换为主库，读写分离（数据的一致性问题，会一点延迟）（业务上的分割，写连接主库，读连接从库，进行压力的分摊），从库一般设置为只读，从库通过中继日志更新数据

数据的一致性问题的解决：前端应用走缓存（Redis，Elasticsearch）因为前端对数据的实时性要求不高，后端应用Report走从库查
对称方式

两个节点，互为主从，双主模式（两个数据库的主键如果是自动生成需要初始设置成不一样的，如A-1001，1003，1005，B-1002，1004，1006）初始值不一样。雪花算法生成主键，全局唯一

双主下数据同步一定要实时
多机方式
- MHA
- MMM
- MyCat（可以实现双主机热切）

主从实现的逻辑

资源：两台数据库()
Master会将修改记录进binlog
- Slave从主库binlog读取数据写入relaylog，会启动一个Io线程读取主库的binlog更新日志写入自己的relaylog中，然后进入sleep，等待下次主库再次唤醒。从库启动一个sql线程从relaylog中读取更新事件在从库中重放。
  
  从库会2个线程：IO线程和sql线程
- relaylog和binlog的格式一样，可以用mysqlbinlog读取relaylog
- mysql> show relaylog events in ‘relay-bin.000001’; 读取relaying
- 目前数据库有两种复制方式：1、binlog的postition方式，relaylog与binlog的position位置保持一致；2、GTID方式（global transaction id）：MySQL版本>=5.7，需要开启GTID，xid是当个事务的id，可在binlog中查看

# 查看gtid_mode
mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+

配置主从复制

数据库版本要一致，或者从库版本比主库版本高

mysql -V
# mysql命令行查看版本信息
mysql -u root -p
select version();

1、Master服务器配置

# 慢查询开关
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/slow.log
slow_launch_time=4
long_query_time=3 # 慢查询时间
# chown -R mysql:mysql /usr/local/slowlog/ # slowlog目录授权给mysql用户
# 开启binlog 
server_id=213
log_bin=/usr/local/mysql/binlog/mysql-bin  # 只需要给名字前缀
binlog_format=row
expire_logs_days=3  # 3天前的binlog过期不保留
max_binlog_size=200m # binlog日志最大200m
# 重启mysql让配置生效

2、Slave服务器配置

我这里使用docker 安装从库mysql

# mysql的配置文件挂载: /usr/local/mysql-slave/conf
# mysql的数据存储目录挂载: /usr/local/mysql-slave/data
# mysql的日志文件目录挂载: /usr/local/mysql-slave/logs
[root@alibaba mysql-slave]# docker run -d -p 3307:3306 
-v /usr/local/mysql/slave/conf:/etc/mysql/mysql.conf.d 
-v /usr/local/mysql/slave/data:/var/lib/mysql 
-v /usr/local/mysql/slave/logs:/var/log/mysql
-e MYSQL_ROOT_PASSWORD=mysqlali#123 --name mysql-slave mysql:5.7
[root@alibaba mysql-slave]# ls
conf  data logs

# 挂载目录授权给mysql
[root@alibaba ~]# chown -R mysql:mysql /usr/local/mysql-slave/

从库的主从设置

# 配置文件不要忽略这个
[mysqld]
server_id=214  # 与主库不一样就行 
# 慢查询开关
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/slow.log
slow_launch_time=4
long_query_time=3  # 慢查询时间
# chown -R mysql:mysql /usr/local/mysql/   # slowlog目录授权给mysql用户

relay_log=/usr/local/relaylog/relay-bin 
relay_log_recovery=1 # 确保完整性,只有relaylog损坏，从库 就会从主库重新读取binlog到relaylog,再进行重放操作
# chown -R mysql:mysql /usr/local/relaylog/   # relaylog目录授权给mysql用户

read_only=on # 从服务器只读
skip_slave_start=on # slave重启后不会自己启动复制链路，需要手动启动，建议手动启动，好处是检查无误后启动

master_info_repository=TABLE # 将主从服务的信息保存进表
relay_log_info_repository=TABLE # 将主从服务的信息保存进表

replicate-do-db=icoding_admin

在宿主机/usr/local/mysql-slave/conf 目录下新建配置文件myconfig.cnf

注意:

不管你是docker安装mysql 还是非docker安装mysql，一定要赋权慢日志，中继日志的目录的写权限给mysql，或者直接将目录的owner改为mysql用户

chown -R mysql:mysql /usr/local/slowlog/

下面是我在mysql容器内将日志目录的owner改mysql，你只赋予w 写权限给mysql也是可以的

配置完毕重启从库

3、主从指明不同步的数据库

# Master 的my.cnf配置，不配置的话默认同步所有数据库
# 不同步数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 只同步某一些数据库
binlog-do-db=icoding_admin

# Slave 的my.cnf配置，不配置的话默认复制所有数据库
# 复制哪些数据库
replicate-do-db=icoding_admin
replicate-do-db=game
# 不复制哪些数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema

# 像极了爱情，master给不给数据是master的事，slave从库要不要数据也是slave的事，所以配置同步的数据库是很灵活的

4、在Master上建立授权账号

# Master授权给slave
mysql> use mysql;
mysql> grant replication slave on *.* to 'syncuser'@'172.17.0.2' identified by 'sync#123456';
mysql> flush privileges;

从库mysql 创建run时没有指定网络，默认在docker0里

发现mysql-slave的ip是172.17.0.2，所以我这里赋予master的主从复制账号的登录host ip 是 172.17.0.2

5、初始化Slave数据

# Master和Slave的数据库版本要一致，Master<=Slave
# --flush-logs 刷新，生成新的binlog日志
mysqldump -uroot -p123456 -h127.0.0.1 --flush-logs --master-data=2 --routines --triggers --events --single-transaction --databases icoding_admin > mydb.sql
# 下面这段是mydb.sql文件里记录的position位置
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; 就用导出的日志pos

# 查看主库的数据库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

把全量备份文件放到从库服务器上执行恢复

[root@alibaba bak]# cp mydb.sql /usr/local/mysql-slave/data
# 进入容器mysql从库
[root@alibaba mysql-slave]# docker exec -it mysql-slave /bin/bash
root@5340e71431d0:/# cd /var/lib/mysql

进入mysql命令行初始化数据库

# mysql容器内
root@5340e71431d0:/var/lib/mysql# mysql -uroot -p
# 使用全量备份文件初始化数据库
mysql> source mydb.sql;

6、启动复制链路

# 在Slave上执行，要注意binlog文件与position位置与上面第5步导出的mydb.sql里的一致，阿里云服务器的内网IP是172.18.196.184
mysql> 
change master to master_host='172.18.196.184', 
master_port=3306,
master_user='syncuser',
master_password='sync#123456',
master_log_file='mysql-bin.000004',
master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

注意事项

主从复制的时候主从数据库的UUID不能一样，如果是镜像方式，启动前将UUID文件删除，会重新创建
cd /var/lib/mysql
cat auto.cnf  # 将他删除
server-uuid=890980d9-b072-11ea-8ab8-00163e03668d

7、启动主从复制

# 在Slave上执行
# 启动slave 复制
mysql> start slave;
# 停止slave 复制
mysql> stop slave;
# 查看主从复制的状态和日志错误
mysql> show slave status \G;

启动复制后，查看复制状态 show slave status

要Slave_IO_Running 和Slave_SQL_Running都为Yes才算正常。

主库插入数据

insert into ad_user values(5,'meimei','123123');

从库查看数据，数据被同步过来了，再查看一下slave status;

mysql> select * from ad_user;
+----+------------+----------+
| id | username   | password |
+----+------------+----------+
|  1 | arry       | 1234567  |
|  2 | gavin      | 1234567  |
|  3 | coding_qin | 1234567  |
|  4 | allen      | 1234567  |
|  5 | meimei     | 123123   |
+----+------------+----------+
5 rows in set (0.01 sec)

发现主库的binlog和从库的relaylog已经变化了，同步成功

使用GTID方式进行复制链路

# 默认GTID是不开启的
# 如果开启以下操作不可用
# create table ..... select * from xxx; 不可用
# 在一个事务里不能创建和删除表
# 在一个事务里更新innodb引擎和myisam引擎不可用

场景延时复制

无意中删除了一个数据，可以做延时从库的复制

# 从库做备份就无法做到数据的及时恢复
# 让我的从库做到延时同步
mysql> stop slave;
mysql> change master to master_delay=10; # 延时10秒,IO线程才从个主库的binlog更新数据到relaylog
mysql> start slave;
mysql> show slave status \G;

方案：master（一个实时slave做读写分离，一个延时slave做备份）

双主互备

主从复制，从库不用开启binlog，如果互备的话就一定要开启。

1、配置

# 将上面的主从模式修改为双主模式 互相复制
#-------1、Master配置--------
[mysqld]
server_id=213
log_bin=/usr/local/mysql/binlog/mysql-bin  # 开启binlog，只需要给名字前缀
binlog_format=row
expire_logs_days=3  # 3天前的binlog过期不保留
max_binlog_size=200m # binlog日志最大200m

# 慢查询
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/slow.log
slow_launch_time=4
long_query_time=3 # 慢查询时间
# chown -R mysql:mysql /usr/local/mysql/ # mysql目录授权给mysql用户

log_slave_updates=true # 双主互备必须开启，否则只是主从关系
relay_log=/usr/local/mysql/relaylog/relay-bin 
relay_log_recovery=1 
master_info_repository=TABLE # 将主从服务的信息保存进表
relay_log_info_repository=TABLE # 将主从服务的信息保存进表
skip_slave_start=on # slave重启后不会自己启动复制链路，需要手动启动，建议手动启动，好处是检查无误后启动

# 只同步的同步的数据库
binlog-do-db=icoding_admin 

#-----2、Slave配置-----------
[mysqld]
server_id=214  # 与主库不一样就行 
log_bin=/var/log/mysql/mysql-bin  # 开启binlog，只需要给名字前缀
binlog_format=row
expire_logs_days=3  # 3天前的binlog过期不保留
max_binlog_size=200m # binlog日志最大200m

# 慢查询
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
slow_launch_time=4
long_query_time=3  # 慢查询时间
# chown -R mysql:mysql /usr/local/slowlog/   # slowlog目录授权给mysql用户

log_slave_updates=true # 双主互备必须开启，否则只是主从关系
relay_log=/var/log/mysql/relay-bin 
relay_log_recovery=1 # 确保完整性,只有relaylog损坏，从库 就会从主库重新读取binlog到relaylog,再进行重放操作
master_info_repository=TABLE # 将主从服务的信息保存进表
relay_log_info_repository=TABLE # 将主从服务的信息保存进表
# chown -R mysql:mysql /var/log/mysql   # relaylog目录授权给mysql用户
skip_slave_start=on # slave重启后不会自己启动复制链路，需要手动启动，建议手动启动，好处是检查无误后启动

binlog-do-db=icoding_admin

master 与slave都重启一下mysql

2、Slave上建立授权账号

上面的主从复制Master已经建立授权账号，同样Slave也要一个授权账号给Master

# Slave授权给Master
mysql> use mysql;
mysql> grant replication slave on *.* to 'syncuser'@'172.18.196.184' identified by 'sync#123456';
mysql> flush privileges;

3、分别启动复制链路

第一步：开启Slave的复制

登录master查看它的master status

登录slave，开启复制

mysql> change master to master_host='172.18.196.184', 
master_port=3306,
master_user='syncuser',
master_password='sync#123456',
master_log_file='mysql-bin.000005',
master_log_pos=154;
mysql> start slave;
mysql> show slave status \G;

第二步：开启Master的复制

登录slave查看它的master status

登录master，开启复制

mysql> change master to master_host='172.18.196.184', 
master_port=3307,
master_user='syncuser',
master_password='sync#123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;
mysql> start slave;
mysql> show slave status \G;

4、测试

# 创建测试用户
mysql> grant SELECT, INSERT, UPDATE, DELETE on `icoding_admin`.* to 'icoding'@'%' identified by '123456';
mysql> flush privileges;

注意：mysql重启后，要手动开启复制

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

常见问题

1、**Fatal error : The slave I/O thread stops because master and slave have equal MySql server UUIDs**

上面报错说的是replication架构中需要保证每个mysql实例UUID(UUID保存在datadir目录下的auto.cnf文件中)唯一，就跟server_id一样需要保证主从架构中所有mysql实例server_id唯一。

解决：删除备库的auto.cnf文件，启动备库实例，此时备库就会产生一个新的auto.cnf文件(产生新的UUID)。

2、**ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository**

mysql.slave_relay_log_info表中保留了以前的复制信息，导致新从库启动时无法找到对应文件

解决：

# 1、重置
mysql> reset slave;
# 2、重新配置master
mysql> change master to ......
# 3、启动复制链路
mysql> start slave;

3、**Got fatal error 1236 from master when reading data from binary log: 'Could not open log file'**

在master 方 flush logs

ysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | dcmp,test    |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在slave执行

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> change master to master_host='10.200.132.168',master_user='root',master_password='123456',master_port=3308, master_log_file='mysql-bin.000003',master_log_pos=154; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)