The Percona Toolkit contains many tools for MySQL. Usually, we use the pt-archive to archive our data. Pt-archive can archive the table data to a table or file, also can be used to purge data.
First of all, how to install the Percona-Toolkit?
1. Download a suitable package at https://www.percona.com/downloads/percona-toolkit/LATEST/ , for example, i choose the percona-toolkit-3.0.11-1.el6.x86_64.rpm.
2. Use rpm to install
|
1 |
rpm -ivh percona-toolkit-3.0.11-1.el6.x86_64.rpm |
You may need to install the dependencies:
|
1 2 3 4 5 6 7 |
yum install perl-DBI yum install perl-DBD-MySQL yum install perl-IO-Socket-SSL yum install perl-Digest-MD5 |
3. Check if the installation is successful
|
1 |
pt-archiver --help |
If it shows the help information, then the installation is finished.
4. Create users for the archiving
At the source side, the user should have at least SELECT, DELETE privileges.
At the target side, the user should have at least INSERT privilege.
We can know why it needs these privileges from the general log below.
Secondly, how to archive data?
(1) Archive data
Now, i’ll archive my table ‘testarchive.sourcetb’ to the target table,
Source database info: 192.168.100.184:8001 testarchive.sourcetb
Target database info: 192.168.100.103:4001 destdb.targettb
Source info:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show create table sourcetb \G *************************** 1. row *************************** Table: sourcetb Create Table: CREATE TABLE `sourcetb` ( `id` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from sourcetb; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ |
Because PT-ARCHIVE won’t create the table automatically, so i have to create a same table on the target database:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `targettb` ( `id` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Then executing as follows:
|
1 |
pt-archiver --source h=192.168.100.184,P=8001,D=testarchive,t=sourcetb,u=archive,p=archivepwd --where 'id < 3' --dest h=192.168.100.103,P=4001,u=archive_wr,p=wrpwd,D=destdb,t=targettb --limit=1 --no-delete --why-quit 2>&1 >> archive.log |
–source: DSN specifying the table to archive from (required)
–dest: DSN specifying the table to archive to
–where: limit which rows to archive
–no-delete: Do not delete archived rows
–limit: Number of rows to fetch and archive per statement (default 1)
–why-quit: Print reason for exiting unless rows exhausted
The execution log was printed into archive.log
(2)Check the result
After that, check the source table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from testarchive.sourcetb; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ |
Check the target table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from destdb.targettb; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | +----+------+ |
It shows that the rows(” id < 3 “) have been archived to the target table, and the source rows are still exist(” –no-delete “).
(3)What the PT-ARCHIVE did?
What the pt-archive actually did in MySQL? As we know, the general log of MySQL can show all the queries, so we can get the details from the general log( turn it on before archiving data):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> show variables like '%general_log%'; +------------------+-----------------------------------+ | Variable_name | Value | +------------------+-----------------------------------+ | general_log | ON | | general_log_file | /data/mysql8001/mysql-general.log | +------------------+-----------------------------------+ |
If the general_log is OFF, use set global general_log=on; to turn it on, and then open the “general_log_file“.
Source side:
Target side:
Pt-archive performs a lot of checks when it is executed, and it also allows you turn these checks off.
If you want to delete the data, then you can remove the “–no-delete” option, and we can get “delete” from the general log:
Other options of the pt-archive
You can use pt-archiver --help to get whole help information
Here i list some commonly used options:
–source: DSN specifying the table to archive from (required)
–dest: DSN specifying the table to archive to
–where: limit which rows to archive
–no-delete: Do not delete archived rows
–limit: Number of rows to fetch and archive per statement (default 1)
–why-quit: Print reason for exiting unless rows exhausted
–replace: Causes INSERTs into –dest to be written as REPLACE
–bulk-delete: Delete each chunk with a single statement (implies –commit-each)
–statistics: Collect and print timing statistics
–max-lag=m: Pause archiving if the slave given by –check-slave-lag lags (default 1s). Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix, s is used.
–sleep=i: Sleep time between fetches
