mysql主从同步延迟zabbix怎么监控_随笔

使用 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。

部署完成之后，通过VIP：192.168.21.254，根据LVS调度算法来访问后端真实的MySQL从服务器，实现负载均衡。

具体 *** 作：

第一部分：分别在两台MySQL从服务器上 *** 作

一、关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

二、配置防火墙，开启3306端口

vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

:wq! #保存退出

/etc/init.d/iptables restart #重启防火墙使配置生效

三、绑定LVS虚拟服务器（VIP）：192.168.21.254到lo:0

vi /etc/rc.d/init.d/realserver #编辑，添加以下代码

#################################################

#!/bin/sh

# chkconfig: - 80 90

# description:realserver

# mysql_vip start realserver

mysql_vip=192.168.21.254 #LVS虚拟服务器（VIP）

. /etc/rc.d/init.d/functions

case "$1" in

start)

ifconfig lo:0 $mysql_vip netmask 255.255.255.255 broadcast $mysql_vip

/sbin/route add -host $mysql_vip dev lo:0

echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore

echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce

echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore

echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce

sysctl -p >/dev/null 2>&1

echo "RealServer Start OK"

stop)

ifconfig lo:0 down

route del $mysql_vip >/dev/null 2>&1

echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore

echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce

echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore

echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce

echo "RealServer Stoped"

echo "Usage: $0 {start|stop}"

exit 1

esac

exit 0

#################################################

chmod +x /etc/rc.d/init.d/realserver #添加脚本执行权限

chkconfig realserver on #添加开机启动

/etc/rc.d/init.d/realserver start #开启，参数stop为关闭

四、调整服务器参数，使LVS虚拟服务器（VIP）忽略ARP广播包

vi /etc/sysctl.conf #编辑

net.ipv4.ip_forward= 1 #修改0为1，开启转发

net.ipv4.conf.lo.arp_ignore= 1

net.ipv4.conf.lo.arp_announce= 2

net.ipv4.conf.all.arp_ignore= 1

net.ipv4.conf.all.arp_announce= 2

:wq! #保存退出

/sbin/sysctl -p #使配置立即生效

五、设置能够远程监控MySQL主从同步状态的MySQL用户和密码

mysql -u root -p #进入MySQL控制台

insert into mysql.user(Host,User,Password) values('localhost','checkslave',password('123456')) #新建账户checkslave，密码123456

flush privileges #刷新系统授权表

grant all on *.* to 'checkslave'@'192.168.21.129' identified by '123456' with grant option #允许账户checkslave从LVS主服务器192.168.21.129连接到数据库服务器

grant all on *.* to 'checkslave'@'192.168.21.130' identified by '123456' with grant option #允许账户checkslave从LVS从服务器192.168.21.130连接到数据库服务器

第二部分：分别在两台LVS主备服务器上 *** 作

一、关闭SElinux、配置防火墙

1、vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

2、vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -d 224.0.0.18 -j ACCEPT #允许VRRP（虚拟路由器冗余协议）组播地址通信

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

:wq! #保存退出

/etc/init.d/iptables restart #重启防火墙使配置生效

二、安装lvs软件

yum install ipvsadm #Linux 2.6内核已经集成了lvs软件，此处ipvsadm为lvs管理工具

modprobe ip_vs #加载ip_vs

三、安装keepalived

yum install gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel #安装编译工具包

下载keeplived：http://www.keepalived.org/software/keepalived-1.2.12.tar.gz

上传keepalived-1.2.12.tar.gz到/usr/local/src目录

cd /usr/local/src

tar zxvf keepalived-1.2.12.tar.gz

cd keepalived-1.2.12

./configure #配置，必须看到以下提示，说明配置正确，才能继续安装

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

Use VRRP Framework : Yes

make #编辑

make install #安装

cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

mkdir /etc/keepalived

cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/sbin/keepalived /usr/sbin/

cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

chmod +x /etc/rc.d/init.d/keepalived #添加执行权限

chkconfig keepalived on #设置开机启动

service keepalived start #启动

service keepalived stop #关闭

service keepalived restart #重启

四、安装perl、perl-DBI、DBD-mysql模块以及MySQL客户端 #perl脚本连接MySQL数据库需要

yum install perl perl-DBI perl-DBD-MySQL mysql #执行此命令安装

vi /tmp/test_perl.pl

#!/usr/bin/perl

print "Hello, world!\n"

:wq! #保存退出

perl /tmp/test_perl.pl #运行测试脚本，如果出现：Hello,world! 说明perl安装成功

perldoc DBI #查看DBI模块是否安装

perldoc DBD::mysql #查看DBD::mysql模块是否安装

五、配置keepalived

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf-bak

vi /etc/keepalived/keepalived.conf #编辑，修改为以下代码

##################################################################################################

! Configuration File for keepalived

global_defs {

notification_email {

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 192.168.200.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state MASTER #LVS备机修改为BACKUP

interface eth0

virtual_router_id 51

priority 100 #LVS备机修改为80

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.21.254

}

notify_master "/etc/keepalived/clean_arp.sh" #LVS主备机都为notify_master

}

virtual_server 192.168.21.254 3306 {

delay_loop 30

lb_algo wlc

lb_kind DR

#nat_mask 255.255.255.0

persistence_timeout 120

protocol TCP

real_server 192.168.21.127 3306 {

weight 1

MISC_CHECK {

misc_path "/etc/keepalived/check_slave.pl 192.168.21.127"

misc_dynamic

}

real_server 192.168.21.128 3306 {

weight 1

MISC_CHECK {

misc_path "/etc/keepalived/check_slave.pl 192.168.21.128"

misc_dynamic

}

##################################################################################################

:wq! #保存退出

六、设置MySQL主从监控脚本check_slave.pl

vi /etc/keepalived/check_slave.pl #编辑，添加以下代码

##################################################################################################

#!/usr/bin/perl -w

use DBI

use DBD::mysql

# CONFIG VARIABLES

$SBM = 120

$db = "osyunweidb"

$host = $ARGV[0]

$port = 3306

$user = "checkslave"

$pw = "123456"

# SQL query

$query = "show slave status"

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError =>0,PrintError =>0 })

if (!defined($dbh)) {

exit 1

}

$sqlQuery = $dbh->prepare($query)

$sqlQuery->execute

$Slave_IO_Running = ""

$Slave_SQL_Running = ""

$Seconds_Behind_Master = ""

while (my $ref = $sqlQuery->fetchrow_hashref()) {

$Slave_IO_Running = $ref->{'Slave_IO_Running'}

$Slave_SQL_Running = $ref->{'Slave_SQL_Running'}

$Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'}

}

$sqlQuery->finish

$dbh->disconnect()

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {

exit 1

} else {

if ( $Seconds_Behind_Master >$SBM ) {

exit 1

} else {

exit 0

}

##################################################################################################

:wq! #保存退出

chmod +x /etc/keepalived/check_slave.pl #添加脚本执行权限

七、设置更新LVS虚拟服务器（VIP）地址的arp记录到网关脚本

vi /etc/keepalived/clean_arp.sh #编辑，添加以下代码

#!/bin/sh

VIP=192.168.21.254

GATEWAY=192.168.21.2

/sbin/arping -I eth0 -c 5 -s $VIP $GATEWAY &>/dev/null

:wq! #保存退出

chmod +x /etc/keepalived/clean_arp.sh #添加脚本执行权限

八、测试LVS+Keepalived是否正常运行

service keepalived restart #在两台LVS主备服务器上重启keepalived

ipvsadm -L #在两台LVS主备服务器上执行此命令，

ip addr show #在两台LVS主备服务器上执行此命令，

#从图中可以看到VIP：192.168.21.254现在指向的是LVS主服务器

1、关闭LVS主服务器的keepalived服务，查看LVS备服务器是否正常接管keepalived

service keepalived stop #在LVS主服务器上运行

此时，在两台LVS主备服务器上执行ip addr show命令，

可以看到VIP：192.168.21.254现在指向的是LVS备服务器，说明LVS备服务器已经接管keepalived，测试成功。

2、停止MySQL从服务器192.168.21.127上面的主从同步服务

mysql -u root -p #进入MySQL控制台

slave start#停止同步

在LVS备服务器执行ipvsadm -L命令，可以看到MySQL从服务器192.168.21.127已经从负载均衡列表中被剔除了，测试成功。

3、从客户端连接LVS虚拟服务器（VIP）：192.168.21.254

telnet 192.168.21.254 3306 #执行此命令，说明测试成功。

tail -f /var/log/messages #查看LVS日志信息

至此，LVS+Keepalived实现MySQL从库读 *** 作负载均衡配置完成。

如果您不太熟悉MySQL监控，开始可以看一看流行的监控软件。例如，下面列出了MySQL Cacti Plugin

Innodb Buffer Pool Activity

•Pages Created

•Pages Written

•Pages Read

Innodb Buffer Pool Pages

•Pool Size

•Database Pages

•Free Pages

•Modified Pages

Inoodb File I/O

•File Reads

•Files Writes

•Log Writes

•File Fsyncs

Innodb Pending I/O

•Aio Log Ios

•Aio Sync ios

•Buffer Pool Flushes

•Chkp Writes

•Ibuf Aio Reads

•Log Flushes

•Log Writes

•Normal Aio Reads

•Normal Aio Writes

Innodb Insert Buffer

•Inserts

•Merged

•Merges

Innodb Log

•Log Buffer Size

•Log Bytes Written

•Log Bytes Flushed

•Unflushed Log

Innodb Row Operations

•Rows Read

•Rows Deleted

•Rows Updated

•Rows Inserted

Innodb Semaphores

•Spin Rounds

•Spin Waits

•OS Waits

Innodb Transactions

•Innodb Transactions

•Current Transactions

•History List

•Read Views

MySQL Binary/Relay Logs

•Binlog Cache use

•Binlog Cache Disk Use

•Binary Log Space

•Relay Log Space

MySQL Command Counters

•Questions

•SELECT

•DELETE

•INSERT

•UPDATE

•REPLACE

•LOAD

•DELETE MULTI

•INSERT SELECT

•UPDATE MULTI

•REPLACE SELECT

MySQL Connections

•Max Connections

•Max Used Connections

•Aborted Clients

•Aborted Connects

•Threads Connected

•Connections

MySQL Files and Tables

•Table Cache

•Open Tables

•Open Files

•Opened Tables

MySQL Network Traffic

•Bytes Received

•Bytes Sent

MySQL Processlist

•State Closing Tables

•State Copying to Tmp Table

•State End

•State Freeing Items

•State Init

•State Locked

•State Login

•State Preparing

•State Reading From Net

•State Sending Data

•State Sorting Result

•State Statistics

•State Updating

•State Writing to Net

•State None

•State Other

MySQL Query Cache

•Queries In Cache

•Hits

•Inserts

•Not Cached

•Lowmem Prunes

MySQL Query Cache Memory

•Query Cache Size

•Free Memory

•Total Blocks

•Free Blocks

MySQL Replication

•Slave Running

•Slave Stopped

•Slave Lag

•Slave Open Temp Tables

•Slave Retried Transactions

MySQL Select Types

•Select Full Join

•Select Full Range Join

•Select Range

•Select Range Check

•Select Scan

MySQL Sorts

•Sort Rows

•Sort Range

•Sort Merge Passes

•Sort Scan

MySQL Table Locks

•Table Locks Immediate

•Table Locks Waited

•Slow Queries

MySQL Temporary Objects

•Created Tmp Tables

•Created Tmp Disk Tables

•Created Tmp Files

MySQL Threads

•Thread Cache Size

•Threads Created

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/7219103.html

mysql主从同步延迟zabbix怎么监控

发表评论

评论列表（0条）