MySQL数据归档小工具推荐及优化--mysql_archiver

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

rm -rf /etc/yum.repos.d/epel*
yum install postgresql-devel python3-devel postgresql-devel python-devel

（1）    下载    setuptools-39.2.0.zip
（2）    解压安装 python setup.py build
                python setup.py install

（1）    下载 pip-1.5.4.tar.gz
（2）    安装 python setup.py install

yum install MySQL-python

sh: pt-archiver: command not found

ln -s /data/XXXXXXXX/percona-toolkit-3.2.1/bin/pt-archiver /usr/local/bin/pt-archiver

<type 'exceptions.Exception'>
[Errno 2] No such file or directory: u'/software/python_script/db_archive_XXXXdb_XXXtablename.log'

Character set mismatch: --source DSN uses utf8, table uses utf8mb4.  You can disable this check by specifying --no-check-charset.

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.

Cannot find encoding "utf8mb4" at /usr/local/bin/pt-archiver line 6711.

<type 'exceptions.Exception'>
'ascii' codec can't decode byte 0xe6 in position 0: ordinal not in range(128)

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

echo '' > __init__.py

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

[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

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

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

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

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='数据库归档基础信息表';

#!/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)

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.