mysql 归档文件使用率_MySQL数据归档小工具推荐及优化--mysql_archiver

本文主要是介绍mysql 归档文件使用率_MySQL数据归档小工具推荐及优化--mysql_archiver,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一.主要概述

MySQL数据库归档历史数据主要可以分为三种方式:一.创建编写SP、设置Event;二.通过dump导入导出;三.通过pt-archiver工具进行归档。第一种方式往往受限于同实例要求,往往被大家舍弃。第二种,性能相对较好,但是归档表较多时运维也是比较头疼的事。所以很多DBA往往采用第三种方式--pt-archiver。

pt-archiver是Percona-Toolkit工具集中的一个组件,是一个主要用于对MySQL表数据进行归档和清除的工具。它可以将数据归档到另一张表或者是一个文件中。pt-archiver在清除表数据的过程中并不会影响OLTP事务的查询性能。对于数据的归档,它可以归档到另一台服务器上的另一张表,也可归档到一个文件中。

有一个网友通过Python开发了将pt-archiver打包成了一个小工具—mysql_archiver,它基本实现了归档作业的配置化部署,使用起来挺不错。"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 import db_conn

File"/data/XXXXXX/mysql_archiver-master/db_conn.py", line 4, in 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.pyinstall

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.缺少文档

[Errno2] 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

Errorsin 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.

dca20d14e728cc3293e80ca81324f785.png

解决方案:

pt-archiver去掉参数 --bulk-insert --bulk-delete

7.执行 db_archive_exec.py 报错

报错信息

'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

from db_log importdb_archive_monitor_log

ImportError: No module named db_log

解决方案

转到目录db_log,执行如下脚本

echo '' > __init__.py

生成__init__.py文件

四.功能优化

为工具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 -*-

importsysimportosimporttimeimportdb_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 inresults:

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= "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= "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 inf:if 'INSERT' inline:

i= line.index(" ")

inserted_qty= line[i + 1:]elif 'DELETE' inline:

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()exceptException, e:printstr(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_archiver#readme

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

这篇关于mysql 归档文件使用率_MySQL数据归档小工具推荐及优化--mysql_archiver的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/387607

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

不懂推荐算法也能设计推荐系统

本文以商业化应用推荐为例,告诉我们不懂推荐算法的产品,也能从产品侧出发, 设计出一款不错的推荐系统。 相信很多新手产品,看到算法二字,多是懵圈的。 什么排序算法、最短路径等都是相对传统的算法(注:传统是指科班出身的产品都会接触过)。但对于推荐算法,多数产品对着网上搜到的资源,都会无从下手。特别当某些推荐算法 和 “AI”扯上关系后,更是加大了理解的难度。 但,不了解推荐算法,就无法做推荐系

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k