MySQL data archiving tool mysql_archiver detailed explanation

I. Overview

There are three main ways to archive historical data in MySQL database: 1. Create and write SP and set Event; 2. Import and export through dump; 3. Archive through pt-archiver tool. The first method is often limited by the same instance requirements and is often abandoned. The second type has relatively better performance, but operation and maintenance is also a headache when there are many archive tables. Therefore, many DBAs often use the third method - pt-archiver.

pt-archiver is a component of the Percona-Toolkit toolset, and is a tool mainly used to archive and clear MySQL table data. It can archive the data into another table or a file. pt-archiver does not affect the query performance of OLTP transactions during the process of clearing table data. For archiving of data, it can be archived to another table on another server or to a file.

A netizen developed pt-archiver into a small tool - mysql_archiver through Python. It basically realizes the configurable deployment of archiving jobs and is very good to use. "MySQL_archiver basically realizes the automatic operation of data archiving, unified archiving task scheduling management, automatic monitoring and early warning, and automatic report generation. To a certain extent, it saves productivity and improves operation and maintenance efficiency."

GitHub address: https://github.com/dbarun/mysql_archiver

2. pt-archiver main parameters

3. Common errors in installing and deploying mysql_archiver

1. Execute python db_archive_exec.py 127.0.0.1 db123 and an error is reported

Traceback (most recent call last):
File "/data/mysql_archiver/mysql_archiver-master/db_archive_exec.py", line 7, in <module>
import db_conn
File "/data/mysql_archiver/mysql_archiver-master/db_conn.py", line 4, in <module>
import MySQLdb
ImportError: No module named MySQLdb

Solution:

Step 1 Basic Environment

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

Step 2: Install setuptools

(1) Download setuptools-39.2.0.zip
(2) Unzip and install python setup.py build
    python setup.py install

Step 3 Install pip

(1) Download pip-1.5.4.tar.gz
(2) Install python setup.py install

Step 4 Install MySQL-python

yum install MySQL-python

2. Report an error about missing pt-archiver tool

sh: pt-archiver: command not found

Solution:

Download percona-toolkit-3.2.1_x86_64.tar.gz, install it, and set up a soft link.

For example:

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

3. Lack of documentation

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

Solution:

Create a file: /software/python_script

4. Character set problem

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

Solution:

Modify the db_archive_exec.py executable file and add the parameter --no-check-charset for pt-archiver

5. Parameter setting error

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.

Solution:

pt-archiver removes the parameter --commit-each

6.Cannot find encoding "utf8mb4"

Error message:

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

Solution:

pt-archiver removes the parameters --bulk-insert --bulk-delete

IV. Main reference materials

1. MySQL data archiving practice - the powerful combination of Python and 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 Data Archiving Tool

https://github.com/dbarun/mysql_archiver#readme

3. Install MySQLdb and basic operations under Linux

https://www.cnblogs.com/blogsme/archive/2012/12/12/2814588.html

4.pt tool pt-archiver

https://www.cnblogs.com/yhq1314/p/10601801.html

5. Installation steps and detailed usage of pip under Linux

https://www.jb51.net/article/118035.htm

6. Summary of Percona-Toolkit's pt-archiver

https://www.cnblogs.com/dbabd/p/10721857.html

This is the end of this article about the detailed explanation of the MySQL data archiving tool mysql_archiver. For more relevant MySQL data archiving content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!