mysql（十二）

MySQL-主从复制

主从复制原理

主从复制的前提

1）两台或两台以上的数据库实例
2）主库要开启二进制日志
3）主库要有复制用户
4）主库的server_id和从库不同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos

IP：10.0.0.51
Port：3306
User：rep
Password：oldboy123
logFile：mysql-bin.000002
Pos：120

7）从库要开启相关线程：IO、SQL
8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

主从复制涉及到的文件和线程

主库：

1）主库binlog：记录主库发生过的修改事件
2）dump thread：给从库传送（TP）二进制日志线程

从库：

1）relay-log（中继日志，差异日志）：存储所有主库TP过来的binlog事件

<font color='red'>relay-log会定期清除，在一个SQL线程执行完成之后，并且长时间不用的情况下</font>

2）relay-log.info：记录relaylog的名字，和上一次读取relaylog的位置点

3）master.info：存储复制用户信息，上次请求到的主库binlog位置点

4）IO thread：接收主库发来的binlog日志，也是从库请求主库的线程

5）SQL thread：执行主库TP过来的日志

做主从复制，是为了缓解主库的压力，并不是为了备份。（延时从库）

image.png

1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info

主从复制实践（生产实践）

主库有数据，并且一直在提供服务，不停库的情况下，添加新的从库

#1.还原环境
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# rm -fr /application/mysql/data/
[root@db02 ~]# cd /application/mysql/scripts/
[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db02 scripts]# /etc/init.d/mysqld start

------

[root@db03 ~]# /etc/init.d/mysqld stop
[root@db03 ~]# rm -fr /application/mysql/data/
[root@db03 ~]# cd /application/mysql/scripts/
[root@db03 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
[root@db03 scripts]# /etc/init.d/mysqld start

#2.修改主库的配置
[root@db01 ~]# vim /etc/my.cnf
log-bin=mysql-bin
binlog_format=row
server_id=10

#3.修改从库的配置
[root@db02 ~]# vim /etc/my.cnf
server_id=5

[root@db02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

------

[root@db03 ~]# vim /etc/my.cnf
server_id=5

[root@db03 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

#4.主库操作
    #创建主从复制用户
    mysql> grant replication slave on *.* to slave@'%' identified by '123';

    #查看binlog位置点？(新主从环境)
    mysql> show master status;
    +------------------+----------+
    | File             | Position | 
    +------------------+----------+
    | mysql-bin.000001 |   134    | 
    +------------------+----------+
    
    #有数据的情况，打点全备
    [root@db01 ~]# mysqldump -A -R --triggers --master-data=1 --single-transaction |gzip > /tmp/replication.sql.gz
    
    #将打点全备的数据，发送到从库上
    [root@db01 ~]# scp /tmp/replication.sql.gz 172.16.1.52:/tmp
    [root@db01 ~]# scp /tmp/replication.sql.gz 172.16.1.53:/tmp
    
    #导入数据
    [root@db02 ~]# zcat /tmp/replication.sql.gz |mysql
    [root@db03 ~]# zcat /tmp/replication.sql.gz |mysql
    
    #如果全备数据很大，建议不要scp
    [root@db01 ~]# zcat /tmp/replication.sql.gz |mysql -uroot -p123 -h10.0.0.52
    [root@db01 ~]# zcat /tmp/replication.sql.gz |mysql -uroot -p123 -h10.0.0.53

#5.从库操作
    #找位置点和名字
    [root@db02 ~]# zcat /tmp/replication.sql.gz |head -22|tail -1
    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=161362;

    #执行同步主库
     change master to
     master_user='slave',
     master_password='123',
     master_host='10.0.0.51',
     master_log_file='mysql-bin.000002',
     master_log_pos=161362;
     
     #开启IO和SQL线程
     start slave;
     
     #检查主从复制状态
     show slave status\G
     
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

MySQL主从复制问题

IO

image.png

1.网络

ping 10.0.0.51

2.端口

telnet 10.0.0.51 3306
tcping 10.0.0.51 3306

3.用户名

4.密码

mysql -uslave -p123 -h10.0.0.51
image.png

5.反向解析


image.png
vim /etc/my.cnf
[mysqld]
skip_name_resolve

#不正经
skip-name-resolv
skip-name-resolve
skip_name_resolv

6.binlog的名字和位置点一定要一致

image.png
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 |  3149338 |
+------------------+----------+

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=3149338;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7.server_id相同

image.png
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
[root@db03 ~]# vim /etc/my.cnf
server_id=5

8.UUID相同

image.png
#1.修改uuid
[root@db03 data]# vim auto.cnf 
[auto]
server-uuid=54c76db8-20eb-11ea-bed9-000c29e98744
[root@db03 data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

#2.删除uuid
[root@db03 data]# rm -fr /application/mysql/data/auto.cnf
[root@db03 data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

SQL

主库和从库数据不一致：

  • 主库上有从库没有的数据
image.png
[root@db03 data]# vim /etc/my.cnf
slave-skip-errors=1032,1062,1007,1049

[root@db03 data]# /etc/init.d/mysqld restart
  • 主库上没有从库上有的数据
image.png
set global sql_slave_skip_counter=1;

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

但是以上操作都是有风险存在的

做主从复制之前，保证主库和从库的数据一致性。

处理方法三：

1）重新备份数据库，恢复到从库
2）给从库设置为只读

#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
read_only=1

MySQL延时从库

image.png

延时从库，原理，在SQL线程上做手脚，不影响IO线程连接dump线程取数据。

延时从库操作步骤

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_delay=180;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#新从库
change master to
master_host='10.0.0.51',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000001',
master_port=3306,
master_log_pos=250,
master_delay=3600;

企业中一般会延时3-6小时

企业案例

思考问题：

总数据量级500G，正常备份去恢复需要1.5-2小时
1）配置延时3600秒

mysql>CHANGE MASTER TO MASTER_DELAY = 3600;

2）主库

drop database db;

3）怎么利用延时从库，恢复数据？

思路：

1.停止SQL线程

mysql> stop slave sql_thread;

2.找到relaylog的名字和起始位置点

[root@db03 data]# cat relay-log.info 
./db03-relay-bin.000003
283

3.查看relay log到删库之前

[root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv db03-relay-bin.000003
12611

4.导出被删除的库

[root@db03 data]# mysqldump -uroot -p123 -A  > /tmp/zls1_new.sql

5.截取relay log

[root@db03 data]# mysqlbinlog --start-position=283 --stop-position=12611 db03-relay-bin.000003 > /tmp/delay.sql

6.将导出的sql文件发送到主库

[root@db03 data]# scp /tmp/*.sql 172.16.1.51:/tmp

7.在主库导入数据

[root@db01 data]# mysql < /tmp/zls1_new.sql 
[root@db01 data]# mysql < /tmp/delay.sql

8.在延时从库开启SQL线程

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

半同步复制

image.png
[root@db03 data]# cd /application/mysql/lib/plugin
image.png
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

#主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#主库启用插件
SET GLOBAL rpl_semi_sync_master_enabled = 1;

#检查安装
mysql> show variables like'rpl%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled       | ON       |
| rpl_semi_sync_master_timeout       | 1000     |
| rpl_semi_sync_master_trace_level   | 32       |
| rpl_semi_sync_master_wait_no_slave | ON       |
| rpl_stop_slave_timeout             | 31536000 |
+------------------------------------+----------+

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 136   |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

#从库上安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

#从库启动插件
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启IO线程
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)

过滤复制

主库配置过滤

白名单:只记录白名单中列出的库的二进制日志

  • binlog-do-db
binlog-do-db=wzry


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 | wzry         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

黑名单：不记录黑名单列出的库的二进制日志

  • binlog-ignore-db
binlog-ignore-db=wzry

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     3248 |              | wzry             |                   |
+------------------+----------+--------------+------------------+-------------------+

从库配置过滤

白名单：只执行白名单中列出的库或者表的中继日志

  • --replicate-do-db=test
  • --replicate-do-table=test.t1
  • --replicate-wild-do-table=test.t*

黑名单：不执行黑名单中列出的库或者表的中继日志

  • --replicate-ignore-db
  • --replicate-ignore-table
  • --replicate-wild-ignore-table
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 219,427评论 6赞 508
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,551评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 165,747评论 0赞 356
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,939评论 1赞 295
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,955评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,737评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,448评论 3赞 420
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,352评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,834评论 1赞 317
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,992评论 3赞 338
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,133评论 1赞 351
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,815评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,477评论 3赞 331
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,022评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,147评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,398评论 3赞 373
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,077评论 2赞 355

推荐阅读更多精彩内容

  • Mysql 主从复制实战(学习笔记十三)
    https://www.cnblogs.com/along21/p/8011596.html https://bl...
    SkTj阅读 3,144评论 1赞 4
  • MySQL 主从同步延迟的原因及解决办法
    Mysql主从基本原理，主要形式以及主从同步延迟原理 (读写分离)导致主库从库数据不一致问题的及解决方案 一、主从...
    薛延祥阅读 1,092评论 0赞 6
  • MySQL应用实践
    《老男孩Linux运维》笔记MySQL-Documentation 概述 MySQL介绍 MySQL属于传统关系型...
    Zhang21阅读 1,007评论 0赞 9
  • 046:生活流程化
    许多大咖们都有一个习惯：生活流程化。例如扎克伯格，衣橱里挂满了许多同样的衣物；例如奥巴马，每天起床锻炼，看新闻，许...
    夜雨狂歌如梦阅读 442评论 2赞 0
  • 元编程(二)
    之前的文章提到，使用type关键字可以用来动态地生成一个类，但是这样写实在是太麻烦了，需要创建一大堆的参数。大部分...
    EvinK阅读 153评论 0赞 0