Linux
Nginx,MySQL,PHP/Python

MySQL主从复制高级进阶

延时从库

介绍
是我们人为配置的一种特殊从库,人为配置从库和主库延时N小时
为什么要有延时
主从复制非常擅长解决数据库物理损坏
SQL线程延时:数据已经写入relaylog中了,SQL线程‘慢点’执行
配置
db02 [(none)]>stop slave;
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>change master to
    -> master_delay=300;               设置延时300秒
Query OK, 0 rows affected (0.01 sec)

db02 [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)

db02 [(none)]>show slave status \G     检查
*************************** 1. row ***************************
                    SQL_Delay: 300
          SQL_Remaining_Delay: NULL

五分钟内侦测到误操作
1.停掉从库stop slave
2.截取relaylog,恢复到从库
3.从库替代主库工作

两库情况
db01 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

db02 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

主库产生数据并删除,利用延时从库恢复数据
db01 [(none)]>create database test  charset utf8;
Query OK, 1 row affected (0.01 sec)
db01 [(none)]>use test
Database changed
db01 [test]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
db01 [test]>insert into t1 values(1);
Query OK, 1 row affected (0.03 sec)
db01 [test]>drop database test;
Query OK, 1 row affected (0.01 sec)

检测到错误操作,停掉主从
db02 [(none)]>stop slave;
Query OK, 0 rows affected (0.01 sec)

截取relaylog
起点
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 1696
               Relay_Log_File: db02-relay-bin.000006
                Relay_Log_Pos: 360                      360是从库当前状态的position号,如果要恢复,以当前状态的position号当起点
终点
db02 [(none)]>db02 [(none)]>show relaylog events in 'db02-relay-bin.000006';
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name              | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| db02-relay-bin.000006 |    4 | Format_desc    |         7 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                              |
| db02-relay-bin.000006 |  123 | Previous_gtids |         7 |         194 | 8d397b30-8791-11e9-acbd-000c2902fc28:55-62                         |
| db02-relay-bin.000006 |  194 | Rotate         |         6 |           0 | mysql-bin.000016;pos=950                                           |
| db02-relay-bin.000006 |  241 | Format_desc    |         6 |           0 | Server ver: 5.7.20-log, Binlog ver: 4                              |
| db02-relay-bin.000006 |  360 | Gtid           |         6 |        1015 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:63' |
| db02-relay-bin.000006 |  425 | Query          |         6 |        1123 | create database test  charset utf8                                 |
| db02-relay-bin.000006 |  533 | Gtid           |         6 |        1188 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:64' |
| db02-relay-bin.000006 |  598 | Query          |         6 |        1286 | use `test`; create table t1 (id int)                               |
| db02-relay-bin.000006 |  696 | Gtid           |         6 |        1351 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:65' |
| db02-relay-bin.000006 |  761 | Query          |         6 |        1423 | BEGIN                                                              |
| db02-relay-bin.000006 |  833 | Table_map      |         6 |        1468 | table_id: 224 (test.t1)                                            |
| db02-relay-bin.000006 |  878 | Write_rows     |         6 |        1508 | table_id: 224 flags: STMT_END_F                                    |
| db02-relay-bin.000006 |  918 | Xid            |         6 |        1539 | COMMIT /* xid=44 */                                                |
| db02-relay-bin.000006 |  949 | Gtid           |         6 |        1604 | SET @@SESSION.GTID_NEXT= '8d397b30-8791-11e9-acbd-000c2902fc28:66' |
| db02-relay-bin.000006 | 1014 | Query          |         6 |        1696 | drop database test                                                 |
+-----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
15 rows in set (0.00 sec)
1014是relay-log中记录的主库最新的日志,所以误删除之前的position就是截取的终点

[root@db02 ~]# mysqlbinlog --start-position=360 --stop-position=1014 /data/mysql/db02-relay-bin.000006>/tmp/relay.sql

进行恢复
db02 [(none)]>source /tmp/relay.sql
db02 [test]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

db02 [test]>select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

恢复成功,必要时从库恢复完成后顶替主库工作

复制过滤

从库有针对的复制主库
主库参数
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:
一般库级别就够用了
配置
[root@db02 ~]# vim /etc/my.cnf 
replicate_do_db=aaa
replicate_do_db=bbb
只复制aaa库和bbb库
[root@db02 ~]# systemctl restart mysqld.service 
db02 [(none)]>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: aaaa
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 2005
               Relay_Log_File: db02-relay-bin.000004
                Relay_Log_Pos: 360
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: aaa,bbb

测试
主库创建aaa,bbb,ccc,ddd库,aaa库中创建t1表
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database bbb;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database ccc;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>create database ddd;
Query OK, 1 row affected (0.00 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)

查看从库都复制什么了
db02 [aaa]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bbb                |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)
db02 [aaa]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
只复制了我们规定的aaa库和bbb库

GTID复制

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号,并且是一个全局(主从复制)唯一的编号
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-2

GTID核心参数

gtid-mode=on                    启用GTID类型
enforce-gtid-condidtency=true   强制GTID的一致性
log-slave-updates=1             slave更新是否记入日志

GTID主从配置

db01+db02修改配置文件
[root@db01 ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-condidtency=true
log-slave-updates=1
[root@db01 ~]# systemctl restart mysqld.service 

[root@db02 ~]# vim /etc/my.cnf
[root@db02 ~]# systemctl restart mysqld.service 

搭建主从
db02 [(none)]>change master to
    -> master_host='10.0.0.51',
    -> master_user='aaaa',
    -> master_password='123',
    -> master_suto_position=1;

db02 [(none)]>start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

db01产生数据测试
db01 [(none)]>create database aaa;
Query OK, 1 row affected (0.09 sec)
db01 [(none)]>use aaa;
Database changed
db01 [aaa]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
db01 [aaa]>show master status;
+------------------+----------+--------------+------------------+--------------------------
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set        
+------------------+----------+--------------+------------------+--------------------------
| mysql-bin.000001 |      470 |              |                  | 8d397b30-8791-11e9-acbd-0
+------------------+----------+--------------+------------------+--------------------------
1 row in set (0.00 sec)

db02检查
db02 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)
db02 [(none)]>show tables from aaa;
+---------------+
| Tables_in_aaa |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

db02 [(none)]>show slave status \G
*************************** 1. row ***************************
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Retrieved_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2
            Executed_Gtid_Set: 8d397b30-8791-11e9-acbd-000c2902fc28:1-2

配置成功

GTID和普通复制的区别

1.在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便fileover
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';形式
告诉从库,我的备份中已经已经有以上事务,你就不用运行了,直接从下一个GTID开始请求binlog就行
赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

LNMP社群 不仅仅是技术

关于我们联系我们