主从延时,gtid复制
1. 主从延时
1.1 主从延时的监控
1.有没有问题
show slave sattus \G
Seconds_Behind_Master: 0
2.有没有主库的原因
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 194
3.有没有及时的回放
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 194
cat relay-log.info
[root@sjk1 data]# cat relay-log.info
7
./sjk1-relay-bin.000011
407
mysql-bin.000014
194
0
0
1
2.延时从库
sql线程延时:数据已经写入了relaylog中了,sql线程慢点运行
一般企业建议3-6小时
2.1 配置从库延时
stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;
start slave;
show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
测试在主库上删除一个数据库
从库
show slave status \G
Relay_Log_File: sjk1-relay-bin.000002
show relaylog events in 'sjk1-relay-bin.000002';
2.2 延时从库的故障处理
恢复思路
1.监控到从库处理逻辑故障
2.停从库sql线程,记录已经回放的位置点
stop slave sql_thred;
show slave status \G
Relay_Log_File: sjk1-relay-bin.000002
Relay_Log_Pos: 320
3.截取
起点:
Relay_Log_File: sjk1-relay-bin.000002
Relay_Log_Pos: 320
终点:drop之前
show relaylog events in 'sjk1-relay-bin.000002';
4.模拟sql线程回放日志
从库 source
5.恢复业务情况
1.从库代替主库
2. 从库导出故障库,还原到主库中
2.3 演示
主库
create database ys;
use ys;
create table t1(id int);
insert into t1 values(1);
commit;
drop database ys;
从库,停止SLAVE_SQL_THREAD
stop slave sql_thread;
show slave status \G
Relay_Log_File: sjk1-relay-bin.000002
Relay_Log_Pos: 477
找到截取点
show relaylog events in 'sjk1-relay-bin.000002';
477-1102
截取日志
cd /data/mysql/data
mysqlbinlog --start-position=477 --stop-position=1102 sjk1-relay-bin.000002 >/tmp/ys.sql
恢复relay到从库
set sql_log_bin=0;
source /tmp/ys.sql
3. 过滤复制
快速的恢复实验环境
从库
stop slave
reset salve all
主库
reset master;
从库:
CHANGE MASTER TO
MASTER_HOST='192.168.80.90',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
3.1 过滤复制的应用
主库:
配置文件
binlog_do_db 白名单要进行复制的数据库
binlog_ignore_db 不进行数据恢复的数据库
从库:
mysql> show slave status \G
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
4. GTID复制
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号,并且是一个全局(主从复制)唯一的编号。
它的官方定义如下:
GTID = source_id :transaction_id
4.1 GTID的核心参数
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
4.2 gtid复制配置过程
192.168.80.90 | sjk1 |
---|---|
192.168…80.91 | sjk2 |
192.168.80.92 | sjk3 |
4.2.1 环境的清除
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
4.2.2 配置文件
sjk1
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=sjk1 [\\d]>
EOF
slave1(sjk2):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=sjk2 [\\d]>
EOF
slave2(sjk3):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=sjk3 [\\d]>
EOF
4.2.3 初始化
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/data --basedir=/opt/mysql
4.2.4 数据库的启动
cat > mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
systemctl start mysqld
4.2.5 构建主从
主库:sjk1
grant replication slave on *.* to repl@'192.168.80.%' identified by '123';
从库:sjk2 sjk3
change master to
master_host='192.168.80.90',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
主库查看
sjk1 [(none)]>sjk1 [(none)]>show processlist;
+----+------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| 4 | repl | 192.168.80.91:49912 | NULL | Binlog Dump GTID | 80 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 5 | repl | 192.168.80.92:43216 | NULL | Binlog Dump GTID | 72 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 6 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
GTID 复制和普通复制的区别
(0)在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便Failover
(1)额外功能参数(3个)
(2)change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
(3)在复制过程中,从库不再依赖master.info文件,而是直接读取最后一个relaylog的 GTID号
(4) mysqldump备份时,默认会将备份中包含的事务操作,以以下方式
#### SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';
告诉从库,我的备份中已经有以上事务,你就不用运行了,直接从下一个GTID开始请求binlog就行。
5. 半同步介绍
半同步
解决主从复制数据一致性问题.
ACK ,从库relay落地,IO线程会返回一个ACK,主库的 ACK_reciver .主库事务才能提交.如果一直ACK没收到,超过10秒钟会切换为异步复制.