mysql主从复制基础

1.高可用方案

负载均衡:有一定的高可用性

LVS Nginx

主备系统:有高可用性,但是需要切换,是单活的架构

KA , MHA, MMM

真正高可用(多活系统):

NDB Cluster Oracle RAC Sysbase cluster , InnoDB Cluster（MGR）,PXC , MGC

主从复制介绍

1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等

搭建前提

## 2.1 两台以上mysql实例 ,server_id,server_uuid不同
## 2.2 主库开启二进制日志
## 2.3 专用的复制用户
## 2.4 保证主从开启之前的某个时间点,从库数据是和主库一致
## 2.5 告知从库,复制user,passwd,IP port,以及复制起点(change master to)
## 2.6 线程(三个):Dump thread  IO thread  SQL thread 开启(start slave)

master节点相关配置

master必须开启二进制日志

log_bin = /data/mysql/mysql-bin

为当前节点设置一个全局唯一的id

server_id =1

创建有复制权限的账号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.57.%' IDENTIFIED BY '123';
FLUSH PRIVILEGES;

备份主库数据 从库导入

mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/tmp/full.sql
从库执行导入
source  full.sql

查看备份文件过滤binlog
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=448;

2.从节点相关配置

slave启动中继日志

reley_log = /data/mysql/reley-bin

为当前节点设置一个全局唯一的id

server_id =2

使用有复制权限的账号,连接主服务器启动复制

slave> CHANGE MASTER TO
 MASTER_HOST='192.168.57.7',
 MASTER_USER='repl',
 MASTER_PASSWORD='123',
 MASTER_PORT=3307,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=448,
 MASTER_CONNECT_RETRY=10;    #重试的次数

开启复制，从节点操作

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

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从故障分析与监控

线程相关

主库

mysql> show full processlist;
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| 6 | repl | 192.168.57.7:35422 | NULL | Binlog Dump | 231 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+----+------+--------------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)

从库

show slave status \G

主库相关信息监控

Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.57.7
 Master_User: repl
 Master_Port: 3307
 Connect_Retry: 10
 Master_Log_File: mysql-bin.000002
 Read_Master_Log_Pos: 448

从库中继信息

Relay_Log_File: localhost-relay-bin.000002
 Relay_Log_Pos: 320
 Relay_Master_Log_File: mysql-bin.000002

从库复制线程有关的状态

Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Last_Errno: 0
 Last_Error:

过滤复制有关的状态

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

主从延迟状态

Seconds_Behind_Master: 0

延时从库有关的状态(人为)

SQL_Delay: 0
SQL_Remaining_Delay: NULL

GTID 复制有关的状态

Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

主从复制原理

1.change master to 时，ip pot user password binlog position写入到master.info进行记录
2. start slave 时，从库会启动IO线程和SQL线程
3.IO_T，读取master.info信息，获取主库信息连接主库
4. 主库会生成一个准备binlog DUMP线程，来响应从库
5. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
11. relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

主从重构

stop  slave
reset slave all
change master to
start slave

1、如何限制从服务器只读？

更改slave的全局服务器变量read-only为YES；
注意：此限制对于拥有SUPER权限用户无效；
MariaDB [mysql]> SET GLOBAL read_only = 1;        
[mysqld]
read_only = 1
阻止所有用户执行写操作：
MariaDB [mysql]> flush tables with read lock;

2.如何保证主从复制时的事务安全？

前提：mysql对二进制日志事件数据会有缓冲;

在master上设置如下参数：
sync_binlog = 1