mysql slave 备库延迟是怎么得到的

发布网友发布时间：2022-04-29 18:23

共3个回答

懂视网时间：2022-04-29 22:44

########################################################################
####                        mysql的安装初始化                       ####
########################################################################
略

########################################################################
###   在三台服务器上搭建主从环境（vm_web1为主，vm_web2和vm_mysql1为从）###
########################################################################
1) 在主（vm_web1）上创建备份用户
MariaDB [(none)]> CREATE USER ‘mysql_slave‘@‘192.168.1.%‘ IDENTIFIED BY ‘JE8mG2ZhmRVq23uq‘;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO ‘mysql_slave‘@‘192.168.1.%‘ IDENTIFIED BY ‘JE8mG2ZhmRVq23uq‘ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2) 分别修改各个服务器的配置文件
vm_web1:
[mysqld]
log-bin = /data/mysql/binlog/mysql-bin
server-id       = 1 #设置server-id的值（server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台服务器的server_id都必须是唯一的）
[root@vm_web1 ~]# service mysql restart

vm_web2:
[mysqld]
log-bin = /data/mysql/binlog/mysql-bin
server-id       = 2
[root@vm_web2 ~]# service mysql restart

vm_mysql1:    vm_mysql1采用的是mysql的多实例所以配置有点特殊
[mysqld3306]
# 占用的端口（每一个实例占用一个端口）
port=3306
server-id       = 3
# 指定套接字文件所在的目录
socket=/tmp/mysql3306.sock
# 指定锁文件所在的位置
pid-file=/tmp/mysql3306.pid
# 指定数据库实例目录
datadir=/mariadb/data3306
log-bin=/data/mysql/3306/binlog/mysql-bin
[root@vm_mysql1 ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306 # 多实例的mysql启动和其他不同，具体参考上一篇日志

3) 开启主从

vm_web2上执行：
MariaDB [(none)]> change master to master_host=‘192.168.1.211‘,master_user=‘mysql_slave‘,master_password=‘JE8mG2ZhmRVq23uq‘,master_port=3306,master_log_file=‘mysql-bin.000001‘,master_log_pos=312;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;

vm_mysql1上执行
MariaDB [(none)]> change master to master_host=‘192.168.1.211‘,master_user=‘mysql_slave‘,master_password=‘JE8mG2ZhmRVq23uq‘,master_port=3306,master_log_file=‘mysql-bin.000001‘,master_log_pos=312;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> start slave;

4) 测试
vm_web1上：
MariaDB [master_slave_test_db]> create database master_slave_test_db;
MariaDB [master_slave_test_db]> create table m_s_test1(id int,name varchar(20));
会实时同步到vm_web2 和 vm_mysql1上

####################################################################
###               把vm_mysql1做成延迟同步                           ####
####################################################################
1) vm_web1上安装percona-xtrabackup
[root@vm_web1 ~]# yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[root@vm_web1 ~]# yum -y install percona-toolkit

2) 在延迟服务器上创建授权用户
vm_mysql1:
MariaDB [mysql]> grant all on *.* to ‘delay_user‘@‘192.168.1.%‘ identified by ‘5BbCZ4WLurwvBFbX‘;
MariaDB [mysql]> flush PRIVILEGES;

3) 主服务器上设置延迟同步
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=2m --interval=20s --run-time=6m --port 3306 192.168.1.217
参数说明： --user=‘delay_user‘ # 延迟服务器中授权的用户名 delay_user
           --password=‘5BbCZ4WLurwvBFbX‘ # 延迟服务器中授权用户的密码
           --delay=2m 延时同步的时间，这里我测试用的是2分钟
           --interval=20s 检查同步的时间，这里设置为20s
           --run-time=6m pt-slave-delay的运行时间，这里设置为6分钟
           --port 3306 延时服务器mysql开启端口
           192.168.1.217 延时服务器的IP地址

4）主服务器上插入数据，进行测试
vm_web1:
MariaDB [master_slave_test_db]> insert into m_s_test1 values(1,‘zhangsan‘);

执行完上面这条命令我们分别在两台服务器上查看数据的变化
vm_web2:
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec) # 实时同步

vm_mysql1:
MariaDB [master_slave_test_db]> select * from m_s_test1;
Empty set (0.00 sec) # 在vm_mysql1上并没有数据同步到
过一段时间再去查看
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec) # 发现数据已经同步了

5）pt-slave-delay 输出日志查看与分析
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=2m --interval=20s --run-time=6m --port 3306 192.168.1.217
# 连接到从服务器
2015-05-13T23:06:59 slave running 0 seconds behind # 检查从服务器落后主服务器的时间（这里是0秒 < 2分钟）
2015-05-13T23:06:59 STOP SLAVE until 2015-05-13T23:08:59 at master position mysql-bin.000001/629 # 停止SLAVE上的执行进程，直到2015-05-13T23:08:59才开启（刚好是我们设置的延迟2分钟）
2015-05-13T23:07:19 slave stopped at master position mysql-bin.000001/629 # 20s后再次查看，发现slave执行进程是停止的，并且当前时间也没有达到2015-05-13T23:08:59。所以什么事情都不用做
2015-05-13T23:07:39 slave stopped at master position mysql-bin.000001/629 # 同上
2015-05-13T23:07:59 slave stopped at master position mysql-bin.000001/629 # 同上
2015-05-13T23:08:19 slave stopped at master position mysql-bin.000001/815 # 检测到主上有数据更新
2015-05-13T23:08:39 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:08:59 no new binlog events # 开启slave执行进程，但是从上没有进行任何数据操作
2015-05-13T23:09:19 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:09:39 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:09:59 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:10:19 START SLAVE until master 2015-05-13T23:08:19 mysql-bin.000001/815 # 执行23:08:19检测到的主上更新的操作
2015-05-13T23:10:39 slave running 0 seconds behind
2015-05-13T23:10:39 STOP SLAVE until 2015-05-13T23:12:39 at master position mysql-bin.000001/815
2015-05-13T23:10:59 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:11:19 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:11:39 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:11:59 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:12:19 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:12:39 no new binlog events
2015-05-13T23:12:59 slave stopped at master position mysql-bin.000001/815
2015-05-13T23:12:59 Setting slave to run normally # pt-slave-delay退出，同步恢复到实时。
从上面的解释可以看出来从上比主上最多延迟2分钟20s最少会延迟2分钟

6) 如果我们想让pt-slave-delay在后台运行并且当进程退出时也不要追上master 则：
[root@vm_web1 ~]# nohup pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --nocontinue --delay=2m --interval=20s --port 3306 192.168.1.217 &
--continue:继续复制，正常退出（默认是yes）如果退出后重启线程没有until条件，他会正常运行追上master
--nocontinue：表示不追上
######################################################################################
###             主服务器上误删除数据后在延迟备份服务器上恢复                       ##
######################################################################################
vm_web1:
[root@vm_web1 ~]# pt-slave-delay --user=‘delay_user‘ --password=‘5BbCZ4WLurwvBFbX‘ --delay=20m --interval=100s --run-time=30m --port 3306 192.168.1.217 # 延迟同步启动

vm_web1:
MariaDB [master_slave_test_db]> delete from m_s_test1 where id=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [master_slave_test_db]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1349 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

vm_mysql1:
MariaDB [master_slave_test_db]> stop slave; # 延迟服务器上停止从
Query OK, 0 rows affected (0.00 sec)

MariaDB [master_slave_test_db]> change master to master_log_file=‘mysql-bin.000001‘,master_log_pos=1349; # 跳过误删除点直接执行后面的操作。

MariaDB [master_slave_test_db]> start slave;
Query OK, 0 rows affected (0.00 sec)

我们再次往vm_web1上添加数据，可以看到在vm_mysql1上还保留有原来的那一条数据
vm_web1:
MariaDB [master_slave_test_db]> insert into m_s_test1 values(3,‘wangwu‘);
Query OK, 1 row affected (0.00 sec)
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+--------+
| id   | name   |
+------+--------+
|    3 | wangwu |
+------+--------+
1 row in set (0.00 sec)

vm_web2:

MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+--------+
| id   | name   |
+------+--------+
|    3 | wangwu |
+------+--------+
1 row in set (0.00 sec)

vm_mysql1:
MariaDB [master_slave_test_db]> select * from m_s_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    3 | wangwu   |
+------+----------+
2 rows in set (0.00 sec)

使用pt-slave-delay实现mysql的延迟备份

标签：pt-slave-delay mysql

热心网友时间：2022-04-29 19:52

使用 bcc 工具观测 MySQL：1）dbstat功能：将 MySQL/PostgreSQL 的查询延迟汇总为直方图

语法：

dbstat [-h] [-v] [-p [PID [PID ...]]] [-m THRESHOLD] [-u] [-i INTERVAL] {mysql,postgres}

选项：

{mysql,postgres} # 观测哪种数据库-h, --help # 显示帮助然后退出-v, --verbose # 显示BPF程序-p [PID [PID ...]], --pid [PID [PID ...]] # 要观测的进程号，空格分隔-m THRESHOLD, --threshold THRESHOLD # 只统计查询延迟比此阈值高的-u, --microseconds # 以微秒为时间单位来显示延迟(默认单位：毫秒)-i INTERVAL, --interval INTERVAL # 打印摘要的时间间隔(单位：秒)

示例：

# 使用 sysbench 在被观测数据库上执行 select[root@liuan tools]# dbstat mysql -p `pidof mysqld` -uTracing database queries for pids 3350 slower than 0 ms...^C[14:42:26] query latency (us)

2）dbslower

功能：跟踪 MySQL/PostgreSQL 的查询时间高于阈值

语法：

dbslower [-h] [-v] [-p [PID [PID ...]]] [-x PATH] [-m THRESHOLD] {mysql,postgres}

参数：

{mysql,postgres} # 观测哪种数据库 -h, --help # 显示帮助然后退出 -v, --verbose # 显示BPF程序 -p [PID [PID ...]], --pid [PID [PID ...]] # 要观测的进程号，空格分隔 -m THRESHOLD, --threshold THRESHOLD # 只统计查询延迟比此阈值高的 -x PATH, --exe PATH # 数据库二进制文件的位置

示例：

# 使用sysbench在被观测数据库上执行update_index [root@liuan tools]# dbslower mysql -p `pidof mysqld` -m 2 Tracing database queries for pids 3350 slower than 2 ms... TIME(s) PID MS QUERY 1.765087 3350 2.996 UPDATE sbtest1 SET k=k+1 WHERE id=963 3.187147 3350 2.069 UPDATE sbtest1 SET k=k+1 WHERE id=628 5.945987 3350 2.171 UPDATE sbtest1 SET k=k+1 WHERE id=325 7.771761 3350 3.853 UPDATE sbtest1 SET k=k+1 WHERE id=5955. 使用*

bcc 基于 eBPF 开发（需要 Linux 3.15 及更高版本）。bcc 使用的大部分内容都需要 Linux 4.1 及更高版本。

"bcc.usdt.USDTException: failed to enable probe 'query__start'; a possible cause can be that the probe requires a pid to enable" 需要 MySQL 具备 Dtrace tracepoint。

热心网友时间：2022-04-29 21:10

可以参考一下
http://guwhuzhe.iteye.com/blog/1901707
在mysql的备库的监控中有一项很重要的指标:Seconds_Behind_Master,这个值是怎么得到的呢？下面从5.1.58的代码中分析一下：
mysql的replication中有2个比较重要的class：Master_info(rpl_mi.h),
Relay_log_info(rpl_rli.h)，他们分别对应于master,info文件和slave.info文件；很显
然，Master_info是io_thread需要的，Relay_log_info是sql_thread需要的。Master_info中有一个变
量 clock_diff_with_master，这个值记录着mysql的主库和备库的时间差，可以理解为主备的主机时间差。