MySQL主从
一、概念
MHA (Master High Availability)能在0-30秒内完成故障数据库切换,最多程度保证数据的一致性。
MHA由两部分组成,MHA Manager(管理节点),MHA node(数据节点)。
MMM 双主故障切换。
主从复制通过binlog日志复制到同步的本地服务器中,然后由本地线程读取日志里边的SQL语句重新应用到MySQL数据库中。
1.1 主从复制实现的步骤
1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
2)slave将master的binary log events 拷贝到他的中继日志(relay log);首先,slave开始一个工作线程:I/O线程,该线程在master上打开一个普通连接,然后开始binlog dump porcess。binlog dump process从master的二进制日志中读取事件(接收的单位是event)。
3)slave重做中继日志中的事件,将日志操作还原并生成数据;SQl slave thread(SQL从线程)是处理该过程的最后一步,SQL线程从中继日志读取事件,并重放其中的事件,(回放的单位也是event)而更新slave的数据,使其与master中的数据一致。
1.2 重做日志和二进制日志(redo log与binlog)区别
二进制日志:记录MySQL数据库相关的日志记录,包括所有的存储引擎;
重做日志:只记录InnoDB存储引擎本身的事务是指;
写入内容:
二进制日志:记录事务具体操作内容,是逻辑日志;
重做日志:记录每个页的更改的物理情况;
写入时间:
二进制日志:旨在事务(提交后)进行写入,只写磁盘一次,不论这事务量多大;
重做日志:在事务进行中(提交前和提交后),就不断有重做日志条目(redo entry)写入重做日志文件。
1.3 MySQL主从复制技术的同步方式
1) 异步复制(asynchronous) 有丢数据的风险
2)全同步复制(fully synchronous) 保证数据安全,不丢数据,损失性能;
3)传统半同步复制(Semi synchronous)
4)无损复制 (lossless replication)
1.4 异步复制原理
原理: 在异步复制中,主库(master) 写数据到二进制日志(binlog)且同步(sync),从库请求二进制日志(Slave Request binlog)后写入(中断日志)relay-log并flush disk(持久化到了磁盘的relay.log中)。主库将时间写入binlog,提交事务,自身并不指导从库是否接收是否处理。
优点:复制的性能最好
缺点:master挂掉后,slave可能会丢失事务
1.5 全同步复制原理
在全同步复制中,主库(master)写数据到二进制日志(binlog)且同步(sync),所有从库请求二进制日志(binlog)且同步(sync),所有从库请求二进制日志(slave request biinlog)后写入中断日志(relay-log)并flush disk,并且应用完日志且commit。
理解:当主提交一个事务,在主库向前端业务请求返回一个commit成功的指令前,必须保证从已经提交了这个事务(所有从库不但接收了,还必须应用该事务日志)。
优点:数据不会丢失
缺点:会阻塞master session,性能差
1.6 传统的版同步复制
原理:在半同步复制中,主库(master)写数据到二进制日志文件(binlog)且sync,并且提交(commit),然后一直等待确认(ACK)。
当至少一个从库请求二进制日志(slave request binlog)写入到中继日志(relay-log)并flush disk ,就返回确认(ACK)不需要回放完成日志。
优点:有数据丢失的分险,低
缺点:会阻塞master session
1.7 GTID特性
GTID(Global Transaction identifieers):对于一个已提交事务的编号,事务的唯一编号,并且是一个全局的唯一的编号。GTID和事务会记录到binlog中,用来标识事务。
GTID是用来替代以前创痛复制方法(binlog+postition)。
MySQL支持GTID后,一个事务在集群中就不在孤单,在每一个节点中,如果存在具有形同标识符的情况,可以避免同一个事务,在同一个节点中出现多次的情况。
GTID的出现,最直接的效果就是,每一个事物在集群中有了唯一性的意义,相对于行复制来讲,数据安全性更高,故障切换更简单。
GTID是由server_uuid:Sequence_Number组成
Server_Uuid:是一个MySQL实例的全局唯一标识;存放在$datadir/auto.cnf
Sequence_number:是MySQL内部的一个事务的编号,一个MySQL实例不会重复的序列号,也表示在该实例上已经提交事务的数量,并且随着事务的提交而递增。
1.8 主从复制原理
(1)当一个事务在主库端执行并提交时候,产生GTID,一同记录到binlog日志中;
(2)binlog传输到slave,并存储到slave的relaylog后,读取这个GTID的这个值设置gtid_next变量,即告诉Slave,下一个要执行的GTID值;
(3)sql线程从relay log中获取GTID,然后对比slave端的binlog是否有该GTID;
(4)如果有记录,说明该GTID事务已经执行,salve会忽略;
(5)如果没有,slave就会执行该GTID事务,并记录该GTID到滋生的binlog;
(6)解析过程中会判断是否有主键,如果有就用二级索引,如果没有就用全部扫描。
1.9 GTID限制
(1)不支持 create table ...select 语句
(2)不支持create temporary table 和drop temporary table
(3)不推荐GTID模式的实例上进行 mysql_upgrade;
1.10 GTID的参数
gtid_mode=on
on:产生GTID,slave只接收带GTID的事务
on_permissive:产生GTID,slave接收不带GTID事务也接收带GTID的事务
off: 不产生GTID,slave只接收不带GTID的事务
off_permissive:不产生GTID,salve接收不带GTID事务也接收带GTID的事务
复制过滤参数:
主节点: binlog-do-db 表示和哪个数据库相关的写入类、修改类指令会被写入到二进制文件中去
binlog=ignore-db 表示忽略,除了这个数据库之外的都复制
从节点:
replicate_do_db
replicate_ignore_db
replicate_do_table
replicate_ignore_table
replicate_wild_do_table 用来解决跨库更新的问题
replicate_wild_ignore_table 用来解决跨库更新的问题
二、主从搭建
2.1 传统的异步复制
主节点创建用户
mysql> create user replicate@'%'; Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'replicate'@'%' identified by 'replicate';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replicate'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
主节点配置
server_id=001 log_bin=/data/binlog/mysql-bin log_bin_index=/data/binlog/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on expire_logs_days=50 sync_binlog=1 innodb_flush_log_at_trx_commit=1
从节点配置
server_id=002 log_bin=/data/binlog/mysql-bin log_bin=/data/binlog/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on relay_log=/data/relaylog/relay.log read_only=1 master_info_repository=table relay_log_info_repository=table slave_skip_errors=ddl_exist_errors slave_preserve_commit_order=1
重启两台数据库
[root@MySQLSlver ~]# systemctl restart mysqld
查看主节点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
从节点
mysql> change master to -> master_host='192.168.43.6', -> master_user='replicate', -> master_password='replicate', -> master_log_file='mysql-bin.000001', -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.04 sec)
启动从节点报错,这是由于之前有relaylog
mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决
mysql> reset slave; Query OK, 0 rows affected (0.08 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec)
修改延迟复制
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to MASTER_DELAY=60; Query OK, 0 rows affected (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec)
2.2 基于GTID的主从
主节点配置
server_id=001 log_bin=/data/binlog/mysql-bin log_bin_index=/data/binlog/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on expire_logs_days=50 sync_binlog=1 innodb_flush_log_at_trx_commit=1 gtid_mode=on enforce_gtid_consistency=1 log-slave-updates=1 binlog_gtid_simple_recovery=1
从节点配置
server_id=002 log_bin=/data/binlog/mysql-bin log_bin=/data/binlog/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on relay_log=/data/relaylog/relay.log read_only=1 master_info_repository=table relay_log_info_repository=table slave_skip_errors=ddl_exist_errors slave_preserve_commit_order=1
gtid_mode=on
enforce_gtid_consistency=1
log-slave-updates=1
binlog_gtid_simple_recovery=1
主库创建账号
mysql> create user replicate@'%'; Query OK, 0 rows affected (0.01 sec) mysql> alter user 'replicate'@'%' identified by 'replicate'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'replicate'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
从库
mysql> change master to -> master_host='192.168.43.6', -> master_user='replicate', -> master_password='replicate', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)