pt-archiver

用于清理数据、归档数据

[root@slave159 opt]# pt-archiver --versionpt-archiver 2.2.15[root@slave159 opt]# pt-archiver --helppt-archiver nibbles records from a MySQL table.  The --source and --destarguments use DSN syntax; if COPY is yes, --dest defaults to the key's valuefrom --source.  For more details, please use the --help option, or try 'perldoc/usr/local/bin/pt-archiver' for complete documentation.Usage: pt-archiver [OPTIONS] --source DSN --where WHEREOptions:  --analyze=s                Run ANALYZE TABLE afterwards on --source and/or --                             dest  --ascend-first             Ascend only first column of index  --ask-pass                 Prompt for a password when connecting to MySQL  --buffer                   Buffer output to --file and flush at commit  --bulk-delete              Delete each chunk with a single statement (                             implies --commit-each)  --[no]bulk-delete-limit    Add --limit to --bulk-delete statement (default                             yes)  --bulk-insert              Insert each chunk with LOAD DATA INFILE (implies --                             bulk-delete --commit-each)  --charset=s            -A  Default character set  --[no]check-charset        Ensure connection and table character sets are the                             same (default yes)  --[no]check-columns        Ensure --source and --dest have same columns (                             default yes)  --check-interval=m         If --check-slave-lag is given, this defines how                             long the tool pauses each time it discovers that a                             slave is lagging (default 1s).  Optional suffix s=                             seconds, m=minutes, h=hours, d=days; if no suffix,                             s is used.  --check-slave-lag=s        Pause archiving until the specified DSN's slave                             lag is less than --max-lag  --columns=a            -c  Comma-separated list of columns to archive  --commit-each              Commit each set of fetched and archived rows (                             disables --txn-size)  --config=A                 Read this comma-separated list of config files; if                             specified, this must be the first option on the                             command line  --database=s           -D  Connect to this database  --delayed-insert           Add the DELAYED modifier to INSERT statements  --dest=d                   DSN specifying the table to archive to  --dry-run                  Print queries and exit without doing anything  --file=s                   File to archive to, with DATE_FORMAT()-like                             formatting  --for-update               Adds the FOR UPDATE modifier to SELECT statements  --header                   Print column header at top of --file  --help                     Show help and exit  --high-priority-select     Adds the HIGH_PRIORITY modifier to SELECT                             statements  --host=s               -h  Connect to host  --ignore                   Use IGNORE for INSERT statements  --limit=i                  Number of rows to fetch and archive per statement (                             default 1)  --local                    Do not write OPTIMIZE or ANALYZE queries to binlog  --low-priority-delete      Adds the LOW_PRIORITY modifier to DELETE statements  --low-priority-insert      Adds the LOW_PRIORITY modifier to INSERT or                             REPLACE statements  --max-flow-ctl=f           Somewhat similar to --max-lag but for PXC clusters  --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.  --no-ascend                Do not use ascending index optimization  --no-delete                Do not delete archived rows  --optimize=s               Run OPTIMIZE TABLE afterwards on --source and/or --                             dest  --password=s           -p  Password to use when connecting  --pid=s                    Create the given PID file  --plugin=s                 Perl module name to use as a generic plugin  --port=i               -P  Port number to use for connection  --primary-key-only         Primary key columns only  --progress=i               Print progress information every X rows  --purge                    Purge instead of archiving; allows omitting --file                             and --dest  --quick-delete             Adds the QUICK modifier to DELETE statements  --quiet                -q  Do not print any output, such as for --statistics  --replace                  Causes INSERTs into --dest to be written as REPLACE  --retries=i                Number of retries per timeout or deadlock (default                             1)  --run-time=m               Time to run before exiting.  Optional suffix s=                             seconds, m=minutes, h=hours, d=days; if no suffix,                             s is used.  --[no]safe-auto-increment  Do not archive row with max AUTO_INCREMENT (                             default yes)  --sentinel=s               Exit if this file exists (default /tmp/pt-archiver-                             sentinel)  --set-vars=A               Set the MySQL variables in this comma-separated                             list of variable=value pairs  --share-lock               Adds the LOCK IN SHARE MODE modifier to SELECT                             statements  --skip-foreign-key-checks  Disables foreign key checks with SET                             FOREIGN_KEY_CHECKS=0  --sleep=i                  Sleep time between fetches  --sleep-coef=f             Calculate --sleep as a multiple of the last SELECT                             time  --socket=s             -S  Socket file to use for connection  --source=d                 DSN specifying the table to archive from (required)  --statistics               Collect and print timing statistics  --stop                     Stop running instances by creating the sentinel                             file  --txn-size=i               Number of rows per transaction (default 1)  --user=s               -u  User for login if not current user  --version                  Show version and exit  --[no]version-check        Check for the latest version of Percona Toolkit,                             MySQL, and other programs (default yes)  --where=s                  WHERE clause to limit which rows to archive (                             required)  --why-quit                 Print reason for exiting unless rows exhaustedOption types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=timeRules:  Specify at least one of --dest, --file, or --purge.  --ignore and --replace are mutually exclusive.  --txn-size and --commit-each are mutually exclusive.  --low-priority-insert and --delayed-insert are mutually exclusive.  --share-lock and --for-update are mutually exclusive.  --analyze and --optimize are mutually exclusive.  --no-ascend and --no-delete are mutually exclusive.  DSN values in --dest default to values from --source if COPY is yes.DSN syntax is key=value[,key=value...]  Allowable DSN keys:  KEY  COPY  MEANING  ===  ====  =============================================  A    yes   Default character set  D    yes   Database that contains the table  F    yes   Only read default options from the given file  L    yes   Explicitly enable LOAD DATA LOCAL INFILE  P    yes   Port number to use for connection  S    yes   Socket file to use for connection  a    no    Database to USE when executing queries  b    no    If true, disable binlog with SQL_LOG_BIN  h    yes   Connect to host  i    yes   Index to use  m    no    Plugin module name  p    yes   Password to use when connecting  t    yes   Table to archive from/to  u    yes   User for login if not current user  If the DSN is a bareword, the word is treated as the 'h' key.Options and values after processing arguments:  --analyze                  (No value)  --ascend-first             FALSE  --ask-pass                 FALSE  --buffer                   FALSE  --bulk-delete              FALSE  --bulk-delete-limit        TRUE  --bulk-insert              FALSE  --charset                  (No value)  --check-charset            TRUE  --check-columns            TRUE  --check-interval           1  --check-slave-lag          (No value)  --columns                  (No value)  --commit-each              FALSE  --config                   /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-archiver.conf,/root/.percona-toolkit.conf,/root/.pt-archiver.conf  --database                 (No value)  --delayed-insert           FALSE  --dest                     (No value)  --dry-run                  FALSE  --file                     (No value)  --for-update               FALSE  --header                   FALSE  --help                     TRUE  --high-priority-select     FALSE  --host                     (No value)  --ignore                   FALSE  --limit                    1  --local                    FALSE  --low-priority-delete      FALSE  --low-priority-insert      FALSE  --max-flow-ctl             (No value)  --max-lag                  1  --no-ascend                FALSE  --no-delete                FALSE  --optimize                 (No value)  --password                 (No value)  --pid                      (No value)  --plugin                   (No value)  --port                     (No value)  --primary-key-only         FALSE  --progress                 (No value)  --purge                    FALSE  --quick-delete             FALSE  --quiet                    FALSE  --replace                  FALSE  --retries                  1  --run-time                 (No value)  --safe-auto-increment      TRUE  --sentinel                 /tmp/pt-archiver-sentinel  --set-vars                   --share-lock               FALSE  --skip-foreign-key-checks  FALSE  --sleep                    (No value)  --sleep-coef               (No value)  --socket                   (No value)  --source                   (No value)  --statistics               FALSE  --stop                     FALSE  --txn-size                 1  --user                     (No value)  --version                  FALSE  --version-check            TRUE  --where                    (No value)  --why-quit                 FALSE

例1、只清理数据

mysql> create table t(id int,name varchar(30),primary key(id))engine=innodb;Query OK, 0 rows affected (0.11 sec)mysql> insert into t values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');Query OK, 10 rows affected (0.11 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> exitBye[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where 'id>=7' --purge --limit=1 --no-check-charset

查询日志中的相关内容

150929 15:20:54    9 Connecthuang@192.168.60.159 on test    9 Queryset autocommit=0    9 QuerySELECT @@SQL_MODE    9 QuerySHOW VARIABLES LIKE 'wait\_timeout'    9 QuerySET SESSION wait_timeout=10000    9 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/    9 QuerySHOW VARIABLES LIKE 'version%'    9 QuerySHOW ENGINES    9 QuerySHOW VARIABLES LIKE 'innodb_version'    9 Queryshow variables like 'innodb_rollback_on_timeout'    9 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */    9 QueryUSE `test`    9 QuerySHOW CREATE TABLE `test`.`t`    9 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */    9 QuerySHOW VARIABLES LIKE 'wsrep_on'    9 QuerySHOW VARIABLES LIKE 'wsrep_on'    9 QuerySHOW VARIABLES LIKE 'version%'    9 QuerySHOW ENGINES    9 QuerySHOW VARIABLES LIKE 'innodb_version'    9 QuerySELECT CONCAT(@@hostname, @@port)    9 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=7) ORDER BY `id` LIMIT 1    9 QueryDELETE FROM `test`.`t` WHERE (`id` = '7')    9 Querycommit    9 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=7) AND ((`id` >= '7')) ORDER BY `id` LIMIT 1    9 QueryDELETE FROM `test`.`t` WHERE (`id` = '8')    9 Querycommit    9 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=7) AND ((`id` >= '8')) ORDER BY `id` LIMIT 1    9 QueryDELETE FROM `test`.`t` WHERE (`id` = '9')    9 Querycommit    9 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=7) AND ((`id` >= '9')) ORDER BY `id` LIMIT 1    9 QueryDELETE FROM `test`.`t` WHERE (`id` = '10')    9 Querycommit    9 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=7) AND ((`id` >= '10')) ORDER BY `id` LIMIT 1    9 Querycommit    9 Quit

查询表中的数据

[root@slave159 test]# mysql -uhuang -phuang test -e "select * from t"Warning: Using a password on the command line interface can be insecure.+----+------+| id | name |+----+------+|  1 | 111  ||  2 | 222  ||  3 | 333  ||  4 | 444  ||  5 | 555  ||  6 | 666  |+----+------+

例2、被删除数据的表必须有索引，否则会报错

mysql> drop table t;Query OK, 0 rows affected (0.13 sec)mysql>  create table t(id int,name varchar(30))engine=innodb;Query OK, 0 rows affected (0.08 sec)mysql> mysql> insert into t values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');Query OK, 10 rows affected (0.18 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> exitBye[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where 'id>=7' --purge --limit=1 --no-check-charsetCannot find an ascendable index in table at /usr/local/bin/pt-archiver line 3201.mysql -uhuang -phuang test -e "alter table t add index idx_id(id);"[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where 'id>=7' --purge --limit=1 --no-check-charset

相关的查询日志

150929 15:42:11   51 Connecthuang@192.168.60.159 on test   51 Queryset autocommit=0   51 QuerySELECT @@SQL_MODE   51 QuerySHOW VARIABLES LIKE 'wait\_timeout'   51 QuerySET SESSION wait_timeout=10000   51 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/   51 QuerySHOW VARIABLES LIKE 'version%'   51 QuerySHOW ENGINES   51 QuerySHOW VARIABLES LIKE 'innodb_version'   51 Queryshow variables like 'innodb_rollback_on_timeout'   51 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   51 QueryUSE `test`   51 QuerySHOW CREATE TABLE `test`.`t`   51 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */   51 QuerySHOW VARIABLES LIKE 'wsrep_on'   51 QuerySHOW VARIABLES LIKE 'wsrep_on'   51 QuerySHOW VARIABLES LIKE 'version%'   51 QuerySHOW ENGINES   51 QuerySHOW VARIABLES LIKE 'innodb_version'   51 QuerySELECT CONCAT(@@hostname, @@port)   51 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (id>=7) ORDER BY `id` LIMIT 1   51 QueryDELETE FROM `test`.`t` WHERE ((('7' IS NULL AND `id` IS NULL) OR (`id` = '7'))) LIMIT 1   51 Querycommit   51 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (id>=7) AND ((('7' IS NULL OR `id` >= '7'))) ORDER BY `id` LIMIT 1   51 QueryDELETE FROM `test`.`t` WHERE ((('8' IS NULL AND `id` IS NULL) OR (`id` = '8'))) LIMIT 1   51 Querycommit   51 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (id>=7) AND ((('8' IS NULL OR `id` >= '8'))) ORDER BY `id` LIMIT 1   51 QueryDELETE FROM `test`.`t` WHERE ((('9' IS NULL AND `id` IS NULL) OR (`id` = '9'))) LIMIT 1   51 Querycommit   51 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (id>=7) AND ((('9' IS NULL OR `id` >= '9'))) ORDER BY `id` LIMIT 1   51 QueryDELETE FROM `test`.`t` WHERE ((('10' IS NULL AND `id` IS NULL) OR (`id` = '10'))) LIMIT 1   51 Querycommit   51 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (id>=7) AND ((('10' IS NULL OR `id` >= '10'))) ORDER BY `id` LIMIT 1   51 Querycommit   51 Quit

查询表中数据

[root@slave159 test]# mysql -uhuang -phuang test -e "select * from t;"Warning: Using a password on the command line interface can be insecure.+------+------+| id   | name |+------+------+|    1 | 111  ||    2 | 222  ||    3 | 333  ||    4 | 444  ||    5 | 555  ||    6 | 666  |+------+------+

只要表上有索引，过滤非索引字段删除也是可以的

[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where 'name='555'' --purge --limit=1 --no-check-charset150929 15:43:30   55 Connecthuang@192.168.60.159 on test   55 Queryset autocommit=0   55 QuerySELECT @@SQL_MODE   55 QuerySHOW VARIABLES LIKE 'wait\_timeout'   55 QuerySET SESSION wait_timeout=10000   55 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/   55 QuerySHOW VARIABLES LIKE 'version%'   55 QuerySHOW ENGINES   55 QuerySHOW VARIABLES LIKE 'innodb_version'   55 Queryshow variables like 'innodb_rollback_on_timeout'   55 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   55 QueryUSE `test`   55 QuerySHOW CREATE TABLE `test`.`t`   55 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */   55 QuerySHOW VARIABLES LIKE 'wsrep_on'   55 QuerySHOW VARIABLES LIKE 'wsrep_on'   55 QuerySHOW VARIABLES LIKE 'version%'   55 QuerySHOW ENGINES   55 QuerySHOW VARIABLES LIKE 'innodb_version'   55 QuerySELECT CONCAT(@@hostname, @@port)   55 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (name=555) ORDER BY `id` LIMIT 1   55 QueryDELETE FROM `test`.`t` WHERE ((('5' IS NULL AND `id` IS NULL) OR (`id` = '5'))) LIMIT 1   55 Querycommit   55 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (name=555) AND ((('5' IS NULL OR `id` >= '5'))) ORDER BY `id` LIMIT 1   55 Querycommit   55 Quit   [root@slave159 test]# mysql -uhuang -phuang test -e "select * from t;" Warning: Using a password on the command line interface can be insecure.+------+------+| id   | name |+------+------+|    1 | 111  ||    2 | 222  ||    3 | 333  ||    4 | 444  ||    6 | 666  |+------+------+mysql -uhuang -phuang test -e "alter table t add index idx_name(name);"[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where 'name='333'' --purge --limit=1 --no-check-charset150929 15:46:52   63 Connecthuang@192.168.60.159 on test   63 Queryset autocommit=0   63 QuerySELECT @@SQL_MODE   63 QuerySHOW VARIABLES LIKE 'wait\_timeout'   63 QuerySET SESSION wait_timeout=10000   63 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/   63 QuerySHOW VARIABLES LIKE 'version%'   63 QuerySHOW ENGINES   63 QuerySHOW VARIABLES LIKE 'innodb_version'   63 Queryshow variables like 'innodb_rollback_on_timeout'   63 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */   63 QueryUSE `test`   63 QuerySHOW CREATE TABLE `test`.`t`   63 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */   63 QuerySHOW VARIABLES LIKE 'wsrep_on'   63 QuerySHOW VARIABLES LIKE 'wsrep_on'   63 QuerySHOW VARIABLES LIKE 'version%'   63 QuerySHOW ENGINES   63 QuerySHOW VARIABLES LIKE 'innodb_version'   63 QuerySELECT CONCAT(@@hostname, @@port)   63 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (name=333) ORDER BY `id` LIMIT 1   63 QueryDELETE FROM `test`.`t` WHERE ((('3' IS NULL AND `id` IS NULL) OR (`id` = '3'))) LIMIT 1   63 Querycommit   63 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`idx_id`) WHERE (name=333) AND ((('3' IS NULL OR `id` >= '3'))) ORDER BY `id` LIMIT 1   63 Querycommit   63 Quit

从上面的实验可以看出，被删除数据的表必须有索引存在（这个索引不是条件过滤字段也可以）。这个应该是percnoa为了尽量减小对在线其他业务的影响，所以要求必须有索引，这样加的锁就可以小一些。

例3、只把数据导出到外部文件，但是不删除源表里的数据

mysql> drop table t;Query OK, 0 rows affected (0.05 sec)mysql> create table t(id int,name varchar(30),primary key(id))engine=innodb;Query OK, 0 rows affected (0.11 sec)mysql> insert into t values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');Query OK, 10 rows affected (0.11 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> exitBye[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where '1=1' --no-check-charset --no-delete --file="/mnt/archiver.dat" 150929 16:30:28  132 Connecthuang@192.168.60.159 on test  132 Queryset autocommit=0  132 QuerySELECT @@SQL_MODE  132 QuerySHOW VARIABLES LIKE 'wait\_timeout'  132 QuerySET SESSION wait_timeout=10000  132 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/  132 QuerySHOW VARIABLES LIKE 'version%'  132 QuerySHOW ENGINES  132 QuerySHOW VARIABLES LIKE 'innodb_version'  132 Queryshow variables like 'innodb_rollback_on_timeout'  132 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */  132 QueryUSE `test`  132 QuerySHOW CREATE TABLE `test`.`t`  132 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */  132 QuerySHOW VARIABLES LIKE 'wsrep_on'  132 QuerySHOW VARIABLES LIKE 'wsrep_on'  132 QuerySHOW VARIABLES LIKE 'version%'  132 QuerySHOW ENGINES  132 QuerySHOW VARIABLES LIKE 'innodb_version'  132 QuerySELECT CONCAT(@@hostname, @@port)  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '1')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '2')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '3')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '4')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '5')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '6')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '7')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '8')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '9')) ORDER BY `id` LIMIT 1  132 Querycommit  132 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '10')) ORDER BY `id` LIMIT 1  132 Querycommit  132 Quit[root@slave159 test]# cat /mnt/archiver.dat 11112222333344445555666677778888999910101010[root@slave159 test]# mysql -uhuang -phuang test -e "select * from t;" Warning: Using a password on the command line interface can be insecure.+----+--------+| id | name   |+----+--------+|  1 | 111    ||  2 | 222    ||  3 | 333    ||  4 | 444    ||  5 | 555    ||  6 | 666    ||  7 | 777    ||  8 | 888    ||  9 | 999    || 10 | 101010 |+----+--------+[root@slave159 test]# pt-archiver --source h=192.168.60.159,D=test,t=t --user=huang --password=huang --where '1=1' --no-check-charset --no-delete --file="/mnt/archiver.dat" --limit=3150929 16:32:52  138 Connecthuang@192.168.60.159 on test  138 Queryset autocommit=0  138 QuerySELECT @@SQL_MODE  138 QuerySHOW VARIABLES LIKE 'wait\_timeout'  138 QuerySET SESSION wait_timeout=10000  138 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/  138 QuerySHOW VARIABLES LIKE 'version%'  138 QuerySHOW ENGINES  138 QuerySHOW VARIABLES LIKE 'innodb_version'  138 Queryshow variables like 'innodb_rollback_on_timeout'  138 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */  138 QueryUSE `test`  138 QuerySHOW CREATE TABLE `test`.`t`  138 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */  138 QuerySHOW VARIABLES LIKE 'wsrep_on'  138 QuerySHOW VARIABLES LIKE 'wsrep_on'  138 QuerySHOW VARIABLES LIKE 'version%'  138 QuerySHOW ENGINES  138 QuerySHOW VARIABLES LIKE 'innodb_version'  138 QuerySELECT CONCAT(@@hostname, @@port)  138 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `id` LIMIT 3  138 Querycommit  138 Querycommit  138 Querycommit  138 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '3')) ORDER BY `id` LIMIT 3  138 Querycommit  138 Querycommit  138 Querycommit  138 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '6')) ORDER BY `id` LIMIT 3  138 Querycommit  138 Querycommit  138 Querycommit  138 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '9')) ORDER BY `id` LIMIT 3  138 Querycommit  138 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > '10')) ORDER BY `id` LIMIT 3  138 Querycommit  138 Quit

例4、把数据归档到其他表（支持远程归档）

mysql> drop table t;Query OK, 0 rows affected (0.05 sec)mysql> create table t(id int,name varchar(30),primary key(id))engine=innodb;Query OK, 0 rows affected (0.11 sec)mysql> insert into t values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');Query OK, 10 rows affected (0.11 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> exitByept-archiver --source h=192.168.60.159,D=test,t=t,u=huang,p=huang --dest h=192.168.61.42,D=test_archive,t=arch_t,u=huang,p=huang --where 'id>=1' --no-check-charset150929 17:03:19  185 Connecthuang@192.168.60.159 on test  185 Queryset autocommit=0  185 QuerySELECT @@SQL_MODE  185 QuerySHOW VARIABLES LIKE 'wait\_timeout'  185 QuerySET SESSION wait_timeout=10000  185 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/  185 QuerySHOW VARIABLES LIKE 'version%'  185 QuerySHOW ENGINES  185 QuerySHOW VARIABLES LIKE 'innodb_version'  185 Queryshow variables like 'innodb_rollback_on_timeout'  185 Query/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */  185 QueryUSE `test`  185 QuerySHOW CREATE TABLE `test`.`t`  185 Query/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */  185 QuerySHOW VARIABLES LIKE 'wsrep_on'  185 QuerySHOW VARIABLES LIKE 'wsrep_on'  185 QuerySHOW VARIABLES LIKE 'version%'  185 QuerySHOW ENGINES  185 QuerySHOW VARIABLES LIKE 'innodb_version'  185 QuerySELECT CONCAT(@@hostname, @@port)  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '1')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '1')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '2')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '2')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '3')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '3')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '4')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '4')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '5')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '5')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '6')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '6')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '7')  185 Querycommit150929 17:03:20  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '7')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '8')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '8')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '9')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '9')) ORDER BY `id` LIMIT 1  185 QueryDELETE FROM `test`.`t` WHERE (`id` = '10')  185 Querycommit  185 QuerySELECT /*!40001 SQL_NO_CACHE */ `id`,`name` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE (id>=1) AND ((`id` >= '10')) ORDER BY `id` LIMIT 1  185 Querycommit  185 Quit[root@slave159 test]# mysql -uhuang -phuang test -e "select * from t;" [root@slave159 test]# mysql -h 192.168.61.42 -uhuang -phuang test_archive -e "select * from arch_t;" Warning: Using a password on the command line interface can be insecure.+----+--------+| id | name   |+----+--------+|  1 | 111    ||  2 | 222    ||  3 | 333    ||  4 | 444    ||  5 | 555    ||  6 | 666    ||  7 | 777    ||  8 | 888    ||  9 | 999    || 10 | 101010 |+----+--------+