一.主要概述 MySQL数据库归档历史数据主要可以分为三种方式：一.创建编写SP、设置Event；二.通过dump导入导出；三.通过pt-archiver工具进行归档。第一种方式往往受限于同实例要求，往往被大家舍弃。第二种，性能相对较好，但是归档表较多时运维也是比较头疼的事。所以很多DBA往往采用第三种方式–pt-archiver。 pt-archiver 是Percona-Toolkit工具集中的一个组件，是一个主要用于对MySQL表数据进行归档和清除的工具。它可以将数据归档到另一张表或者是一个文件中。pt-archiver在清除表数据的过程中并不会影响OLTP事务的查询性能。对于数据的归档，它可以归档到另一台服务器上的另一张表，也可归档到一个文件中。
有一个网友通过Python 开发了将pt-archiver 打包成了一个小工具—mysql_archiver，它基本实现了归档作业的配置化部署，使用起来挺不错。“MySQL_archiver基本上实现了数据归档的自动运转，统一的归档任务调度管理、自动监控和预警、自动生成报表。在一定程度上节约了生产力，提高了运维效率。” github地址：https://github.com/dbarun/mysql_archiver 二.pt-archiver主要参数
|-----------------------------|---------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **参数** | **默认值** | **参数解释** |
| --check-slave-lag | | 指定主从复制延迟大于选项'--max-lag'指定的值之后暂停归档操作。默认情况下，工具会检查所有的从库，但该选项只作用于指定的从库(通过DSN连接方式)。 |
| --check-interval | 1s | 如果同时指定了选项'--check-slave-lag'，则该选项指定的时间为工具发现主从复制延迟时暂停的时间。每进行操作100行时进行一次检查。 |
| --\[no\]check-charset | yes | 指定检查确保数据库连接时字符集和表字符集相同。 |
| --commit-each | | 指定按每次获取和归档的行数进行提交，该选项会禁用选项'--txn-size'。 在每次获取表数据并进行归档之后，在获取下一次数据和选项'--sleep'指定的休眠时间之前，进行事务提交和刷新选项'--file'指定的文件，通过选项'--limit'控制事务的大小。 |
| --host，-h | | 指定连接的数据库IP地址。 |
| --port，-P | | 指定连接的数据库Port端口。 |
| --user，-u | | 指定连接的数据库用户。 |
| --password，-p | | 指定连接的数据库用户密码。 |
| --socket，-S | | 指定使用SOCKET文件连接。 |
| --databases，-d | | 指定连接的数据库 |
| --source | | 指定需要进行归档操作的表，该选项是必须指定的选项，使用DSN方式表示。 |
| --dest | | 指定要归档到的目标端表，使用DSN方式表示。 如果该选项没有指定的话，则默认与选项'--source'指定源端表为相同表。 |
| --where | | 指定通过WHERE条件语句指定需要归档的数据，该选项是必须指定的选项。不需要加上'WHERE'关键字，如果确实不需要WHERE条件进行限制，则指定'--where 1=1'。 |
| --file | | 指定表数据需要归档到的文件。使用类似MySQL DATE_FORMAT()格式化命名方式。 文件内容与MySQL中SELECT INTO OUTFILE语句使用相同的格式，文件命名选项如下所示： ' %Y：年，4位数(Year, numeric, four digits) %m：月，2位数(Month, numeric (01..12)) %d：日，2位数(Day of the month, numeric (01..31)) %H：小时(Hour (00..23)) %i：分钟(Minutes, numeric (00..59)) %s：秒(Seconds (00..59)) %D：数据库名(Database name) %t：表名(Table name) 例如：--file '/var/log/archive/%Y-%m-%d-%D.%t' ' |
| --output-format | | 指定选项'--file'文件内容输出的格式。 默认不指定该选项是以制表符进行字段的分隔符，如果指定该选项，则使用','(逗号)作为字段分隔符，使用'"'(双引号)将字段括起。用法示例：'--output-format=dump'。 |
| --limit | 1 | 指定每条语句获取表和归档表的行数。 |
| --max-lag | 1s | 指定允许主从复制延迟时长的最大值，单位秒。如果在每次获取行数据之后主从延迟超过指定的值，则归档操作将暂停执行，暂停休眠时间为选项'--check-interval'指定的值。待休眠时间结束之后再次检查主从延迟时长，检查方法是通过从库查询的'Seconds_Behind_Master'值来确定。如果主从复制延迟一直大于该参数指定值或者从库停止复制，则操作将一直等待直到从库重新启动并且延迟小于该参数指定值. |
| --no-delete | | 指定不删除已被归档的表数据。 |
| --progress | | 指定每多少行打印进度信息，打印当前时间，已用时间以及多少行进行归档。 |
| --purge | | 指定执行的清除操作而不是归档操作。允许忽略选项'--dest'和'--file'进行操作，如果只是清除操作可以结合选项'--primary-key-only'会更高效。 |
| --replace | | 指定写入选项'--dest'指定目标端表时改写INSERT语句为REPLACE语句。 |
| --retries | 1 | 指定归档操作遇到死锁或超时的重试次数。当重试次数超过该选项指定的值时，工具将报错退出。 |
| --run-time | | 指定工具归档操作在退出之前需要运行的时间。允许的时间后缀名为s=秒，m=分，h=小时，d=天，如果没指定，默认为s。 |
| --\[no\]safe-auto-increment | yes | 指定不使用自增列(AUTO_INCREMENT)最大值对应的行进行归档。 该选项在进行归档清除时会额外添加一条WHERE子句以防止工具删除单列升序字段具有的具有AUTO_INCREMENT属性最大值的数据行，为了在数据库重启之后还能使用到AUTO_INCREMENT对应的值，但这会引起无法归档或清除字段对应最大值的行。 |
| --sleep | | 指定工具在通过SELECT语句获取归档数据需要休眠的时间，默认值是不进行休眠。在休眠之前事务并不会提交，并且选项'--file'指定的文件不会被刷新。如果指定选项'--commit-each'，则在休眠之前会进行事务提交和文件刷新。 |
| --statistics | | 指定工具收集并打印操作的时间统计信息。 |
| --txn-size | 1 | 指定每个事务处理的行数。如果是0则禁用事务功能。 |
| --why-quit | | 指定工具打印当非因完成归档行数退出的原因。 在执行一个自动归档任务时该选项与选项'--run-time'一起使用非常方便，这样可以确定归档任务是否在指定的时间内完成。如果同时指定了选项'--statistics'，则会打印所有退出的原因。 |
| --skip-foreign-key-checks | | 指定使用语句SET FOREIGN_KEY_CHECKS = 0禁用外键检查。 |
三,安装部署mysql_archiver常见错误
1. 执行python db_archive_exec.py 127.0.0.1 db123 报错
Traceback (most recent call last):
File "/data/XXXXXX/mysql_archiver-master/db_archive_exec.py", line 7, in <module>
import db_conn
File "/data/XXXXXX/mysql_archiver-master/db_conn.py", line 4, in <module> import MySQLdb ImportError: No module named MySQLdb
解决方案:
Step 1 基础环境
rm -rf /etc/yum.repos.d/epel*
yum install postgresql-devel python3-devel postgresql-devel python-devel
Step 2 安装setuptools
（1） 下载 setuptools-39.2.0.zip
（2） 解压安装 python setup.py build
python setup.py install
step 3 安装pip
（1） 下载 pip-1.5.4.tar.gz
（2） 安装 python setup.py install
step 4 安装MySQL-python
yum install MySQL-python
2.报错缺少pt-archiver工具
sh: pt-archiver: command not found
解决方案：
下载percona-toolkit-3.2.1_x86_64.tar.gz,安装，并且设置个软连接。
例如:
ln -s /data/XXXXXXXX/percona-toolkit-3.2.1/bin/pt-archiver /usr/local/bin/pt-archiver
3.缺少文档
<type 'exceptions.Exception'>
[Errno 2] No such file or directory: u'/software/python_script/db_archive_XXXXdb_XXXtablename.log'
解决方案:
创建文档: /software/python_script
4.字符集问题
Character set mismatch: --source DSN uses utf8, table uses utf8mb4. You can disable this check by specifying --no-check-charset.
解决方案:
修改db_archive_exec.py可执行文件，针对pt-archiver添加参数 –no-check-charset
5.参数设置报错
Usage: pt-archiver [OPTIONS] --source DSN --where WHERE
Errors in command-line arguments:
* --txn-size and --commit-each are mutually exclusive.
pt-archiver nibbles records from a MySQL table. The --source and --dest
arguments use DSN syntax; if COPY is yes, --dest defaults to the key's value
from --source. For more details, please use the --help option, or try 'perldoc
/usr/local/bin/pt-archiver' for complete documentation.
解决方案: pt-archiver去掉参数 –commit-each 6.Cannot find encoding “utf8mb4”
报错信息:
Cannot find encoding "utf8mb4" at /usr/local/bin/pt-archiver line 6711.
解决方案:
pt-archiver去掉参数 –bulk-insert –bulk-delete
7.执行 db_archive_exec.py 报错
报错信息
<type 'exceptions.Exception'>
'ascii' codec can't decode byte 0xe6 in position 0: ordinal not in range(128)
解决方案
修改优化的代码部分，不要有中文，即剔除添加的中文，修改为英文即可。
8.执行db_archive_monitor.py报错
Traceback (most recent call last):
File "/data/XXXXX/mysql_archiver-master/db_archive_monitor.py", line 5, in <module>
from db_log import db_archive_monitor_log
ImportError: No module named db_log
解决方案
转到目录db_log,执行如下脚本
echo '' > __init__.py
生成init.py文件 9. crontab执行python脚本问题
调试和手动执行，都没有问题，但是发现没有成功执行备份。
为了定位问题，们可以将cron执行情况，打印初始处理。将crontab调整如下:
调整前:
34 11 * * * python /data/mysql_archiver/mysql_archiver-master/db_archive_exec.py 122.122.122.122 qqorderdb
调整后
34 11 * * * python /data/mysql_archiver/mysql_archiver-master/db_archive_exec.py 122.122.122.122 qqorder >> /data/mysql_archiver/mysql_archiver-master/db_log/logs/crontab_archiver.log 2>&1
查看执行log文件，报错信息如下：
[laobao@qqorder] tail -100 crontab_archiver.log
sh: pt-archiver: command not found sh: pt-archiver: command not found sh: pt-archiver: command not found sh: pt-archiver: command not found sh: pt-archiver: command not found
问题明白了，解决方案如下:
调整前:
调整后:
10.执行 db_archive_exec.py 可能遇到其他的报错
A.报错如下
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-archiver line 5410.
BEGIN failed--compilation aborted at /usr/local/bin/pt-archiver line 5410.
解决方案:
yum -y install perl-Digest-MD5
B.报错如下:
Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI. If DBI is not installed, try:
Debian/Ubuntu apt-get install libdbi-perl
RHEL/CentOS yum install perl-DBI
OpenSolaris pkg install pkg:/SUNWpmdbi
解决方案:
yum install perl-DBI
C.报错如下:
Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found. Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql. If DBD::mysql is not installed, try:
Debian/Ubuntu apt-get install libdbd-mysql-perl
RHEL/CentOS yum install perl-DBD-MySQL
OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql
解决方案：
yum install perl-DBD-MySQL
四.功能优化
为工具mysql_archiver添加只删除不备份的功能，即只删除源库上的数据，而不用迁移。
1.优化配置表db_archive_info的建表语句
DROP TABLE IF EXISTS `db_archive_info`;
CREATE TABLE `db_archive_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '表id',
`server_source` varchar(640) DEFAULT NULL COMMENT '源服务器',
`port_source` varchar(64) DEFAULT NULL COMMENT '源服务器端口',
`user_source` varchar(64) DEFAULT NULL COMMENT '源数据库用户',
`password_source` varchar(128) DEFAULT NULL COMMENT '源数据库密码',
`db_source` varchar(64) DEFAULT NULL COMMENT '源数据库schema',
`table_source` varchar(128) DEFAULT NULL COMMENT '源数据库表',
\`server_dest\`varchar(640) DEFAULT '' COMMENT '目标服务器', \`port_dest\` varchar(64) DEFAULT '' COMMENT '目标服务器端口', \`user_dest\` varchar(64) DEFAULT '' COMMENT '目标数据库用户', \`password_dest\` varchar(128) DEFAULT '' COMMENT '目标数据库密码', \`db_dest\` varchar(64) DEFAULT '' COMMENT '目标数据库schema', \`table_dest\` varchar(128) DEFAULT '' COMMENT '目标数据库表',
`archive_condition` varchar(1000) DEFAULT NULL COMMENT '数据归档条件',
`last_archive_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最近归档时间',
`last_archive_qty` int(10) DEFAULT NULL COMMENT '最近归档数量',
`state` varchar(255) DEFAULT 'A' COMMENT '数据行状态',
`user_created` varchar(128) DEFAULT 'SYS' COMMENT '数据行创建人',
`datetime_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据行创建时间',
`user_modified` varchar(128) DEFAULT 'SYS' COMMENT '数据行修改人',
`datetime_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据行修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='数据库归档基础信息表';
2.修改备份脚本
!/usr/bin/python
-*- coding: UTF-8 -*-
import sys
import os
import time
import db_conn
get db connection
db = db_conn.db
use cursor
cursor = db.cursor()
获取命令行参数
server_source = '10.73.129.187'
db_source = 'test123'
server_source = sys.argv[1]
db_source = sys.argv[2]
try:
SQL 查询语句
sql = "select id, server_source, port_source, user_source, password_source, db_source, table_source," \
"server_dest, port_dest, user_dest, password_dest, db_dest, table_dest, archive_condition " \
"from db_archive_info " \
"where server_source = '%s' and db_source = '%s' " % (server_source, db_source)
执行SQL语句
cursor.execute(sql)
获取所有记录列表
results = cursor.fetchall()
for row in results:
id = row[0]
server_source = row[1]
port_source = row[2]
user_source = row[3]
password_source = row[4]
db_source = row[5]
table_source = row[6]
server_dest = row[7]
port_dest = row[8]
user_dest = row[9]
password_dest = row[10]
db_dest = row[11]
table_dest = row[12]
archive_condition = row[13]
归档开始时间
archive_starttime = time.strftime('%Y-%m-%d %H:%M:%S')
生成pt-archive命令
兼容只删除不归档功能，
if server_dest != '' and port_dest != '' and port_dest != '':
archive_cmd = "/usr/local/bin/pt-archiver " \
"--source h='%s',P='%s',u='%s',p='%s',D='%s',t='%s' " \
"--dest h='%s',P='%s',u='%s',p='%s',D='%s',t='%s' " \
"--no-check-charset --charset=utf8mb4 --where '%s' --progress 50000 --limit 10000 --txn-size 10000 " \
"--statistics --purge " % \
(server_source, port_source, user_source, password_source, db_source, table_source, \
server_dest, port_dest, user_dest, password_dest, db_dest, table_dest, \
archive_condition)
elif server_dest == '' and port_dest == '' and port_dest == '':
archive_cmd = "/usr/local/bin/pt-archiver " \
"--source h='%s',P='%s',u='%s',p='%s',D='%s',t='%s' " \
"--no-check-charset --charset=utf8mb4 --where '%s' --progress 50000 --limit 10000 --txn-size 10000 " \
"--statistics --purge " % \
(server_source, port_source, user_source, password_source, db_source, table_source,archive_condition)
print archive_cmd
make a copy of original stdout route
stdout_archive = sys.stdout
define the log file that receives your log info
log_file = open("/software/python_script/db_archive_%s_%s.log" % (db_source, table_source), "w")
redirect print output to log file
sys.stdout = log_file
archive_cmd = os.popen(pt_archive)
with os.popen(archive_cmd) as c:
with open("db_archive1.log", "r") as c:
archive_log = c.read()
print(archive_log)
close log file
log_file.close()
restore the output to initial pattern
sys.stdout = stdout_archive
定义归档相关变量
inserted_qty = 0
deleted_qty = 0
归档结束时间
archive_endtime = time.strftime('%Y-%m-%d %H:%M:%S')
with open("/software/python_script/db_archive_%s_%s.log" % (db_source, table_source), "r") as f:
for line in f:
if 'INSERT' in line:
i = line.index(" ")
inserted_qty = line[i + 1:]
elif 'DELETE' in line:
i = line.index(" ")
deleted_qty = line[i + 1:]
判断归档是否失败
if server_dest != '' and port_dest != '' and port_dest != '':
if inserted_qty == deleted_qty:
archive_status = 'Y'
archive_error = ''
else:
archive_status = 'N'
archive_error = 'inserted_qty and deleted_qty are not equal'
elif server_dest == '' and port_dest == '' and port_dest == '':
archive_status = 'Y'
archive_error = 'Delete Data OP'
insert sql
sql_insert = "insert into db_archive_log(server_source, db_source, table_source, server_dest, " \
"db_dest, table_dest, archive_qty, archive_cmd, archive_log, archive_start, archive_end, " \
"archive_status, archive_error ) " \
"values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % \
(server_source, db_source, table_source, server_dest, \
db_dest, table_dest, inserted_qty, db.escape_string(archive_cmd), archive_log, archive_starttime,
archive_endtime, \
archive_status, archive_error)
exec sql
cursor.execute(sql_insert)
exec commit
db.commit()
if archive_status == 'Y':
sql_update = "update db_archive_info " \
"set datetime_modified = '%s', last_archive_date = '%s', last_archive_qty = %s " \
"where id = %d" % \
(archive_starttime, archive_endtime, inserted_qty, id)
cursor.execute(sql_update)
exec commit
db.commit()
except Exception, e:
print str(Exception)
print str(e)
3.说明
修改db_archive_info的建表语句，将默认值null 调整为''，主要是们通过 server_dest == '' and port_dest == '' and port_dest == '' 来判断是归档还是直接删除。
如果按照修改前的默认值NULL，这几列值不维护，则报错。
DBI connect('None;host=None;port=None;charset=utf8mb4;mysql_read_default_group=client','None',...) failed: Unknown MySQL server host 'None' (2) at /usr/local/bin/pt-archiver line 2525.
五.主要参考资料
1.MySQL数据归档实战-Python和pt-archiver的强强结合
https://blog.csdn.net/n88lpo/article/details/78905528?utm_medium=distribute.pc_relevant.none-task-blog-baidulandingword-14\&spm=1001.2101.3001.4242
2.MySQL数据归档小工具
https://github.com/dbarun/mysql_archiverreadme
3.在linux下安装MySQLdb及基本操作
https://www.cnblogs.com/blogsme/archive/2012/12/12/2814588.html
4.pt工具之pt-archiver
https://www.cnblogs.com/yhq1314/p/10601801.html
5.linux下pip的安装步骤及使用详解
https://www.jb51.net/article/118035.htm
6.Percona-Toolkit 之 pt-archiver 总结
https://www.cnblogs.com/dbabd/p/10721857.html
原文创作：东山絮柳仔
原文链接：https://www.cnblogs.com/xuliuzai/p/14156559.html
文章列表
- 通过Python收集MySQL MHA 部署及运行状态信息的功能实现
- 通过Python将监控数据由influxdb写入到MySQL
- 通过Python实现生成excel并邮件发送的功能
- 通过Python实现对SQL Server 数据文件大小的监控告警
- 通过 Telegraf + InfluxDB + Grafana 快速搭建监控体系的详细步骤
- 迁移Report Server DataBase时遇到的坑
- 谨慎 mongodb 关于数字操作可能导致类型及精度变化
- 详解MongoDB中的多表关联查询$lookup
- 瞧一瞧！这儿实现了MongoDB的增量备份与还原含部署代码
- 时序数据库InfluxDB的基本语法
- 数据库服务器资源使用情况周报
- 数据库如何应对保障大促活动
- 我10亿级ES数据迁到MongoDB节省90%成本!转载
- 应用部署架构演进转载 -
- 学习ProxySQL参考到几个网址
- 名言小抄五
- 关于SQL Server 镜像数据库快照的创建及使用
- 关于SQL Server 数据库归档的一些思考和改进
- 关于MongoDB时间格式转换和时间段聚合统计的用法总结
- 以实现MongoDB副本集状态的监控为例，看Telegraf系统中Exec输入插件如何编写部署
- 一个磁盘I/O故障导致的AlwaysOn FailOver 过程梳理和分析
- shell 操作钉钉机器人实现告警提醒
- python 学习笔记 四
- python 学习 三
- kapacitor的安装及部分常用命令
- TiDB 架构及设计实现
- TiDB 学习笔记一运维管理
- SQL Server 查看当前会话状态sp_WhoIsActive 转载 -
- SQL Server DB迁移工作List
- SQL Server CPU 利用率毛刺的分析定位与解决
- SQL Server Alwayson架构下 服务器 各虚拟IP漂移监控告警的功能实现 1服务器视角
- Python发送的邮件设置收件人隐藏与显示
- MySQL索引设计需要考虑哪些因素？
- MySQL日志收集之Filebeat和Logstsh的一键安装配置ELK架构
- MySQL数据归档小工具推荐及优化mysql_archiver
- MySQL数据库规范 设计规范+开发规范+操作规范
- MySQL数据库Inception工具学习与测试 笔记
- MySQL常用命令汇总偏向运维管理
- MySQL在线DDL工具 ghost
- MySQL 学习笔记四
- MySQL 学习笔记三
- MySQL alter table时执行innobackupex全备再谈Seconds_Behind_Master
- MySQL MHA 运行状态监控
- MongoDB数据库的设计规范
- MongoDB实例重启失败探究大事务Redo导致
- MongoDB 那些事一文以蔽之
- MongoDB 中的加减乘除 - 运算
- MongoDB 中数据的替换方法实现 类Replace函数功能
- Linux常用命令总结二
- K8S基础学习
