美文网首页
mysql（十二）

mysql（十二）

作者: Freestyle_0f85 | 来源:发表于2019-12-12 14:00 被阅读0次

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

相关文章

网友评论

      本文标题：mysql（十二）

      本文链接：https://www.haomeiwen.com/subject/ysqtnctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|mysql（十二）|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！