这篇博文接着上篇文章《使用 python 管理 mysql 开发工具箱 - 1》，继续写下自己学习 python 管理 MySQL 中的知识记录。
一、MySQL 的读写分离
学习完 MySQL 主从复制之后，可以考虑实现 MySQL 的读写分离，从而提高 MySQL 系统的整体性能。具体控制读写的路由功能可以交给应用程序或者MySQL-Proxy 程序来实现。读写分离其实就是让 Client 写入 master，而读数据从 slave 节点，这样减少了 master 既写又读的压力。这里没有具体介绍如何实现读写分离的功能，后续研究一下 MySQL Proxy 程序，这是 MySQL 官方提供的实现读写分离的程序。
二、slave 节点的负载均衡
1. 使用 DNS 来实现负载均衡
往往 slave 节点是多个，实现 slave 节点的负载均衡是非常重要的。其实可以采用 dns 的功能，一个域名指向多个 slave 的 IP 地址，这样 Client 每次解析到的 slave 地址都是平均分布的，简单的实现了负载均衡的功能。
2. 健康检查监控
我们自己需要实现一个监控程序，检查 slave 的健康情况，包括如下几个方面：
- 是否能连接 slave 节点，判断 timeout 是否会超时即可
- 检查 slave 状态，是否能正常工作。执行 show slave status\G; 查看 IO/SQL Running 是否正常。
- 主从同步时间间隔是否过长。如果 Second_behind_master > 2 认为太慢了
监控程序扫描所有 slave 节点，判断上述指标，把健康的 slave 节点加入到 DNS 解析记录里边，有问题的剔除出去。
三、DNS 基本安装和配置
1. 安装 rpm 包
[root@vip ~]# yum install bind -y
2. 修改配置文件named.conf
options {
listen-on port { any; }; # 修改为any
listen-on-v6 port { ::; };
... ... ... ...
allow-query { any; }; # 修改为any
添加内容：
zone "example.com" IN {
type master;
file "example.com.zone";
};
3. 添加设置区域zone文件
[root@vip ~]# vim /var/named/example.com.zone # 添加如下内容
$TTL 1D
@ IN SOA ns.example.com. root.example.com. (
; serial
1D ; refresh
1H ; retry
1W ; expire
3H ); minimum
NS ns.example.com.
ns A 192.168.0.8
www A 192.168.0.2
4. 启动named服务
[root@vip ~]# service named start
5. 测试dns解析
[root@vip ~]# host www.example.com. localhost
Using domain server:
Name: localhost
Address: :: #
Aliases: # 成功解析OK。
www.example.com has address 192.168.0.2
四、DNS 实现动态记录更新
DNS动态更新必要性：
- 某个slave出现故障，DNS应该将该slave剔除，不要解析这个slave节点
- 复制比较慢，拖后腿的slave节点也应该剔除出去。
考虑：类似keepalived的健康检查。
1. 生成key文件
[root@vip ~]# dnssec-keygen -a HMAC-MD5 -b -n HOST -r /dev/urandom dnskey
生成 2 个文件：
[root@vip ~]# ls Kexample.com.++.*
Kexample.com.++.key Kexample.com.++.private
2. 修改配置文件named.conf，让dns支持更新：添加如下代码
key "example.com" { # 该key为新增加内容
algorithm HMAC-MD5;
secret "25z/5wjwD4GsMgQluWagfkQ9TSqpoJzYbh/I/QEZo2M="; # secret内容参考Kexample.com.+157+46922.key文件内容
};
zone "example.com" IN {
type master;
file "example.com.zone";
allow-update { key "example.com"; }; # 增加一行
};
3. 创建update.txt文件
使用nsupdate前需要创建个文件，告诉nsupdate怎么样去更新update.txt，内容如下：
server 127.0.0.1
debug yes
zone example.com.
update delete s.db.example.com. A
update add s.db.example.com. A 192.168.0.1
update add s.db.example.com. A 192.168.0.2
update add s.db.example.com. A 192.168.0.8
update add s.db.example.com. A 127.0.0.1
show
send
4. 赋予/var/named目录写权限
chmod g+w /var/named
5. 手动更新dns记录
[root@vip ~]# nsupdate -k Kdnskey.++.key update.txt
6. 验证
[root@vip ~]# host s.db.example.com localhost
Using domain server:
Name: localhost
Address: ::#
Aliases:
s.db.example.com has address 192.168.0.1
s.db.example.com has address 192.168.0.2
s.db.example.com has address 192.168.0.8
s.db.example.com has address 127.0.0.1
7. 问题总结
- 1. 看日志文件log
- 2. 看权限错误
- 3. 看程序的用户 ps -ef | grep named
- 4. 看相关配置文件的权限
- 5. iptables和selinux是否关闭
五、Python 实现 DNS 查询
需要使用到 dnspython 模块，需要执行 pip install dnspython 安装此模块。
参考：http://blog.chinaunix.net/uid-24690947-id-1747409.html
六、Python 实现 DNS 动态更新
代码参考：
# 动态更新dns记录
def dnsUpdate(zone, name, rdlist):
key = dns.tsigkeyring.from_text({zone:keyring})
up = dns.update.Update(zone, keyring=key)
rdata_list = [dns.rdata.from_text(dns.rdataclass.IN, dns.rdatatype.A, i) for i in rdlist]
ttl = 60
rdata_set = dns.rdataset.from_rdata_list(ttl, rdata_list)
up.replace(name, rdata_set)
q = dns.query.tcp(up, '127.0.0.1')
# 调用
dnsUpdate('example.com', 's.db', alive)
七、MySQL 从服务器状态检查
按照检查的要求，对 slave 进行健康检查，代码如下：
#!/usr/bin/env python
#encoding: utf-8 import MySQLdb # 通过shell命令获取key列表格式
# mysql -S /tmp/slave01.sock -e "show slave status\G" | awk -F: 'NR!=1{print $1}' | awk '{printf "\""$1"\",\n"}' > a.txt
keys = (
"Slave_IO_State",
"Master_Host",
"Master_User",
"Master_Port",
"Connect_Retry",
"Master_Log_File",
"Read_Master_Log_Pos",
"Relay_Log_File",
"Relay_Log_Pos",
"Relay_Master_Log_File",
"Slave_IO_Running",
"Slave_SQL_Running",
"Replicate_Do_DB",
"Replicate_Ignore_DB",
"Replicate_Do_Table",
"Replicate_Ignore_Table",
"Replicate_Wild_Do_Table",
"Replicate_Wild_Ignore_Table",
"Last_Errno",
"Last_Error",
"Skip_Counter",
"Exec_Master_Log_Pos",
"Relay_Log_Space",
"Until_Condition",
"Until_Log_File",
"Until_Log_Pos",
"Master_SSL_Allowed",
"Master_SSL_CA_File",
"Master_SSL_CA_Path",
"Master_SSL_Cert",
"Master_SSL_Cipher",
"Master_SSL_Key",
"Seconds_Behind_Master",
"Master_SSL_Verify_Server_Cert",
"Last_IO_Errno",
"Last_IO_Error",
"Last_SQL_Errno",
"Last_SQL_Error",
) # 模拟一下slave节点列表, 设置注意实验时设置某些实例为不健康状态
conf = {
'master':'127.0.0.1:3306',
'slave':[
'127.0.0.1:3307',
'192.168.0.8:3307',
'127.0.0.1:3308',
'192.168.0.8:3308',
'127.0.0.1:3309',
'192.168.0.8:3309',
]
} # 检查slave节点的状态是否正常
def checkSlaveStatus(host, port):
try:
conn = MySQLdb.connect(host=host, port=port, user='root', connect_timeout=1)
except Exception, e:
print e
return False
cur = conn.cursor()
cur.execute('show slave status')
data = cur.fetchall() # 只获取到了冒号后边的value, key没有获取到, 和sql shell显示不同. # 将keys和data组合为字典的结构
data = dict(zip(keys, data[0])) # IO/SQL Running 是否正常
if data['Slave_IO_Running'] == 'No' or data['Slave_SQL_Running'] == 'No':
return False
elif data['Seconds_Behind_Master'] > 2: # 主从复制时间持续超过2秒, 太慢了
return False # 到这里肯定是没问题的了
return True # 将ip:port解析为主机+端口
def parseIP(s):
host, port = s.split(':')
return host, int(port) if __name__ == '__main__':
#host = '127.0.0.1' # 写IP好像连不上, 需要授权相应的主机
#port = 3307
alive = []
for ip in conf['slave']:
host, port = parseIP(ip)
print checkSlaveStatus(host, port)
八、MySQL 从服务器状态更新
对 slave 健康状态检查后，将健康的节点列表记录，更新到 DNS 记录中。代码如下：
#!/usr/bin/env python
#encoding: utf-8 import MySQLdb
import dns.query
import dns.update
import dns.tsigkeyring # 通过shell命令获取key列表格式
# mysql -S /tmp/slave01.sock -e "show slave status\G" | awk -F: 'NR!=1{print $1}' | awk '{printf "\""$1"\",\n"}' > a.txt
keys = (
"Slave_IO_State",
"Master_Host",
"Master_User",
"Master_Port",
"Connect_Retry",
"Master_Log_File",
"Read_Master_Log_Pos",
"Relay_Log_File",
"Relay_Log_Pos",
"Relay_Master_Log_File",
"Slave_IO_Running",
"Slave_SQL_Running",
"Replicate_Do_DB",
"Replicate_Ignore_DB",
"Replicate_Do_Table",
"Replicate_Ignore_Table",
"Replicate_Wild_Do_Table",
"Replicate_Wild_Ignore_Table",
"Last_Errno",
"Last_Error",
"Skip_Counter",
"Exec_Master_Log_Pos",
"Relay_Log_Space",
"Until_Condition",
"Until_Log_File",
"Until_Log_Pos",
"Master_SSL_Allowed",
"Master_SSL_CA_File",
"Master_SSL_CA_Path",
"Master_SSL_Cert",
"Master_SSL_Cipher",
"Master_SSL_Key",
"Seconds_Behind_Master",
"Master_SSL_Verify_Server_Cert",
"Last_IO_Errno",
"Last_IO_Error",
"Last_SQL_Errno",
"Last_SQL_Error",
) # 模拟一下slave节点列表, 设置注意实验时设置某些实例为不健康状态
conf = {
'master':'127.0.0.1:3306',
'slave':[
'127.0.0.1:3307',
'192.168.0.8:3307',
'127.0.0.1:3308',
'192.168.0.8:3308',
'127.0.0.1:3309',
'192.168.0.8:3309',
]
} keyring = '25z/5wjwD4GsMgQluWagfkQ9TSqpoJzYbh/I/QEZo2M=' # 检查slave节点的状态是否正常
def checkSlaveStatus(host, port):
try:
conn = MySQLdb.connect(host=host, port=port, user='root', connect_timeout=1)
except Exception, e:
print e
return False
cur = conn.cursor()
cur.execute('show slave status')
data = cur.fetchall() # 只获取到了冒号后边的value, key没有获取到, 和sql shell显示不同. # 将keys和data组合为字典的结构
data = dict(zip(keys, data[0])) # IO/SQL Running 是否正常
if data['Slave_IO_Running'] == 'No' or data['Slave_SQL_Running'] == 'No':
return False
elif data['Seconds_Behind_Master'] > 2: # 主从复制时间持续超过2秒, 太慢了
return False # 到这里肯定是没问题的了
return True # 将ip:port解析为主机+端口
def parseIP(s):
host, port = s.split(':')
return host, int(port) # 动态更新dns记录
def dnsUpdate(zone, name, rdlist):
key = dns.tsigkeyring.from_text({zone:keyring})
up = dns.update.Update(zone, keyring=key)
rdata_list = [dns.rdata.from_text(dns.rdataclass.IN, dns.rdatatype.A, i) for i in rdlist]
ttl = 60
rdata_set = dns.rdataset.from_rdata_list(ttl, rdata_list)
up.replace(name, rdata_set)
q = dns.query.tcp(up, '127.0.0.1')
#print q if __name__ == '__main__':
#host = '127.0.0.1' # 写IP好像连不上, 需要授权相应的主机
#port = 3307
alive = []
for ip in conf['slave']:
host, port = parseIP(ip)
if checkSlaveStatus(host, port):
alive.append(host)
# 解释下这里为什么要设置slave的alive集群阈值
# 如果不设置阈值, 那么存在健康的slave过少, 会导致slave的雪崩现象
# 反而会影响服务的正常运行, 保证只有在一定数量情况下才更新dns记录.
if float(len(alive))/len(conf['slave']) > 0.6:
dnsUpdate('example.com', 's.db', alive) # 注意：
# 1. dns服务一定要保证/var/named目录组用户有写的权限
# 2. iptables 和 selinux 一定要设置好, 最好设置为关闭状态.
九、MySQL 监控测试
通过上边的代码已经实现了 slave 的健康检查，DNS 的动态更新。现在可以做一下测试：
> 执行：
[root@vip mysqlmanager]# python mysql_dns_monitor.py
> 结果：
[root@vip mysqlmanager]# host s.db.example.com localhost
Using domain server:
Name: localhost
Address: ::#
s.db.example.com has address 127.0.0.1 # 已经更新了记录
s.db.example.com has address 192.168.0.8 # 更新了记录，并解析到ip地址，表明已经成功OK.
> 扩展：
其实可以准备几*立的虚拟机来做测试，每台虚拟机作为要给 slave 节点，模拟一些健康问题，看是否能够正确检测并更新到。
十、MySQL 从服务器信息来自CMDB
待更新。。。。