使用 python 管理 mysql 开发工具箱 - 2

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
应用型负载均衡 ALB,每月750个小时 15LCU
简介: 这篇博文接着上篇文章《使用 python 管理 mysql 开发工具箱 - 1》,继续写下自己学习 python 管理 MySQL 中的知识记录。 一、MySQL 的读写分离 学习完 MySQL 主从复制之后,可以考虑实现 MySQL 的读写分离,从而提高 MySQL 系统的整体性能。

这篇博文接着上篇文章《使用 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 53 { any; };    # 修改为any
    listen-on-v6 port 53 { ::1; };
    ... ... ... ...
    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. (
                    0   ; 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: :: 1 #53
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 256 -n HOST -r /dev/urandom dnskey

生成 2 个文件:

[root@vip ~]# ls Kexample.com.+157+46922.*
Kexample.com.+157+46922.key  Kexample.com.+157+46922.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. 86499 A 192.168.0.1
update add s.db.example.com. 86499 A 192.168.0.2
update add s.db.example.com. 86499 A 192.168.0.8
update add s.db.example.com. 86499 A 127.0.0.1
show 
send

4. 赋予/var/named目录写权限

chmod g+w /var/named

5. 手动更新dns记录

[root@vip ~]# nsupdate -k Kdnskey.+157+42183.key update.txt

6. 验证

[root@vip ~]# host s.db.example.com localhost
Using domain server:
Name: localhost
Address: ::1#53
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: ::1#53
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

待更新。。。。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
20
分享
相关文章
Python环境变量在开发和运行Python应用程序时起着重要的作用
Python环境变量在开发和运行Python应用程序时起着重要的作用
102 15
[python 技巧] 快速掌握Streamlit: python快速原型开发工具
本文旨在快速上手python的streamlit库,包括安装,输入数据,绘制图表,基础控件,进度条,免费部署。
160 64
[python 技巧] 快速掌握Streamlit: python快速原型开发工具
【01】python开发之实例开发讲解-如何获取影视网站中经过保护后的视频-用python如何下载无法下载的视频资源含m3u8-python插件之dlp-举例几种-详解优雅草央千澈
【01】python开发之实例开发讲解-如何获取影视网站中经过保护后的视频-用python如何下载无法下载的视频资源含m3u8-python插件之dlp-举例几种-详解优雅草央千澈
【01】python开发之实例开发讲解-如何获取影视网站中经过保护后的视频-用python如何下载无法下载的视频资源含m3u8-python插件之dlp-举例几种-详解优雅草央千澈
10个必备Python调试技巧:从pdb到单元测试的开发效率提升指南
在Python开发中,调试是提升效率的关键技能。本文总结了10个实用的调试方法,涵盖内置调试器pdb、breakpoint()函数、断言机制、logging模块、列表推导式优化、IPython调试、警告机制、IDE调试工具、inspect模块和单元测试框架的应用。通过这些技巧,开发者可以更高效地定位和解决问题,提高代码质量。
140 8
10个必备Python调试技巧:从pdb到单元测试的开发效率提升指南
python已经安装有其他用途如何用hbuilerx配置环境-附带实例demo-python开发入门之hbuilderx编译器如何配置python环境—hbuilderx配置python环境优雅草央千澈
python已经安装有其他用途如何用hbuilerx配置环境-附带实例demo-python开发入门之hbuilderx编译器如何配置python环境—hbuilderx配置python环境优雅草央千澈
python已经安装有其他用途如何用hbuilerx配置环境-附带实例demo-python开发入门之hbuilderx编译器如何配置python环境—hbuilderx配置python环境优雅草央千澈
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
210 15
Python Web开发:Django框架下的全栈开发实战
【10月更文挑战第27天】本文介绍了Django框架在Python Web开发中的应用,涵盖了Django与Flask等框架的比较、项目结构、模型、视图、模板和URL配置等内容,并展示了实际代码示例,帮助读者快速掌握Django全栈开发的核心技术。
216 45
使用Python开发获取商品销量详情API接口
本文介绍了使用Python开发获取商品销量详情的API接口方法,涵盖API接口概述、技术选型(Flask与FastAPI)、环境准备、API接口创建及调用淘宝开放平台API等内容。通过示例代码,详细说明了如何构建和调用API,以及开发过程中需要注意的事项,如数据库连接、API权限、错误处理、安全性和性能优化等。
95 5
|
2月前
|
如何使用Python开发API接口?
在现代软件开发中,API(应用程序编程接口)用于不同软件组件之间的通信和数据交换,实现系统互操作性。Python因其简单易用和强大功能,成为开发API的热门选择。本文详细介绍了Python开发API的基础知识、优势、实现方式(如Flask和Django框架)、实战示例及注意事项,帮助读者掌握高效、安全的API开发技巧。
298 3
如何使用Python开发API接口?
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等