Mysql管理必备工具Maatkit详解之二（mk-archiver）

2009年05月16日　作者： 大头刚　

mk-archiver - 删除时按行备份，支持备份到异地库。安装方法可以参考。

mysql在删除数据的时候，是无法rollback(innodb引擎除外)的，如果你删错了数据，那么你就只能进行恢复了，这显然是很耗成本的操作。
当然你也可以在执行delete的之前，用mysqldump进行备份，或者你在删除之前，先执行load data 或者 insert into as select,至少需要执行2次命令，并且不支持备份到异地库。

那么，下面这个工具可能是你需要的。

利用mk-archiver工具，可以在删除mysql表数据的时候，把需要删除的数据备份。

备份的方式有2种，其一是备份到文件，就像load data一样。其二是备份到另外1张结构相同的表，支持备份到异地库。

mysql> select * from test;
+------+------+
| uid  | note |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
|    3 | NULL | 
|    4 | NULL | 
|    5 | NULL | 
|    6 | NULL | 
|    7 | NULL | 
|    8 | NULL | 
|    9 | NULL | 
+------+------+
9 rows in set (0.00 sec)
 
mk-archiver --source h=localhost,u=sg,p='xxxx',D=test,t=test --file '/u01/%Y-%m-%d-%D.%t' --where 'uid<5'
Cannot find an ascendable index in table at /usr/bin/mk-archiver line 1262.

需要给这个表加一个索引。

mysql> create index uid on test(uid);
Current database: test
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0
 
mysql> \! mk-archiver --source h=localhost,u=sg,p='xxxx',D=test,t=test --file '/u01/%Y-%m-%d-%D.%t' --where 'uid<5'
 
mysql> select *  from test;
Current database: test
 
+------+------+
| uid  | note |
+------+------+
|    5 | NULL | 
|    6 | NULL | 
|    7 | NULL | 
|    8 | NULL | 
|    9 | NULL | 
+------+------+
5 rows in set (0.00 sec)

OK，已经被删除了。被删除的数据在指定的文件里

mysql> \! cat /u01/2008-07-31-test.test
1       \N
2       \N
3       \N
4       \N

想恢复回来，很简单。

mysql> LOAD DATA LOCAL INFILE '/u01/2008-07-31-test.test' into table test;  
Current database: test
 
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> select *  from test;
+------+------+
| uid  | note |
+------+------+
|    4 | NULL | 
|    3 | NULL | 
|    2 | NULL | 
|    1 | NULL | 
|    5 | NULL | 
|    6 | NULL | 
|    7 | NULL | 
|    8 | NULL | 
|    9 | NULL | 
+------+------+
9 rows in set (0.00 sec)

当然，也可以把数据备份到另外一个相同结构的表，这样需要先把备份表建立，支持备份到异地库。

mysql> create table test2 select * from test where 1=2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| uid   | int(11)  | YES  |     | NULL    |       | 
| note  | char(10) | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> select * from test2;
Empty set (0.00 sec)
 
mysql>  \! mk-archiver --source h=localhost,u=sg,p='xxxx',D=test,t=test --dest h=localhost,u=sg,p='xxxx',D=test,t=test2  --where 'uid<5'
 
mysql> select *  from test2;
+------+------+
| uid  | note |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
|    3 | NULL | 
|    4 | NULL | 
+------+------+
4 rows in set (0.00 sec)

OK,还有其他的一些参数，可以看下帮助文件。

mk-archiver --help

mk-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
mk-archiver' for complete documentation.

Usage: mk-archiver --source DSN --where WHERE 

Options:
  --analyze      -Z  Run ANALYZE TABLE afterwards on --source and/or --dest
  --ascendfirst      Ascend only first column of index
  --askpass          Prompt for password for connections
  --buffer       -b  Buffer output to --file and flush at commit
  --bulkdel          Delete each chunk with a single statement (implies
                     --commit-each)
  --bulkins          Insert each chunk with LOAD DATA INFILE (implies --bulkdel
                     --commit-each)
  --charset      -A  Default character set
  --[no]chkcols  -C  Ensure --source and --dest have same columns (default)
  --columns      -c  Comma-separated list of columns to archive
  --commit-each      Commit each set of fetched and archived rows (disables -z)
  --delayedins       Add the DELAYED modifier to INSERT statements
  --dest         -d  Table to archive to
  --file         -f  File to archive to, with DATE_FORMAT()-like formatting
  --forupdate        Adds the FOR UPDATE modifier to SELECT statements
  --header       -h  Print column header at top of --file
  --help             Show this help message
  --hpselect         Adds the HIGH_PRIORITY modifier to SELECT statements
  --ignore       -i  Use IGNORE for INSERT statements
  --limit        -l  Number of rows to fetch and archive per statement (default
                     1)
  --local        -L  Do not write OPTIMIZE or ANALYZE queries to binlog
  --lpdel            Add the LOW_PRIORITY modifier to DELETE statements
  --lpins            Add the LOW_PRIORITY modifier to INSERT statements
  --noascend         Do not use ascending index optimization
  --nodelete         Do not delete archived rows
  --optimize     -O  Run OPTIMIZE TABLE afterwards on --source and/or --dest
  --pkonly       -k  Primary key columns only
  --plugin           Perl module name to use as a generic plugin
  --progress     -P  Print progress information every X rows
  --purge        -p  Purge, not archive; allows to omit --file and --dest
  --quickdel         Add the QUICK modifier to DELETE statements
  --quiet            Do not print any output, such as for --statistics
  --replace      -r  Use REPLACE instead of INSERT statements
  --retries      -R  Number of retries per timeout or deadlock (default 1)
  --[no]safeautoinc  Do not archive row with max AUTO_INCREMENT (default)
  --sentinel     -S  Sentinel file; default /tmp/mk-archiver-sentinel
  --setvars          Set these MySQL variables (default wait_timeout=10000)
  --sharelock        Adds LOCK IN SHARE MODE to SELECT statements
  --skipfkchk    -K  Turn off foreign key checks
  --sleep        -e  Sleep time between fetches
  --source       -s  Table to archive from (required)
  --statistics       Collect and print timing statistics
  --stop             Stop running instances by creating the sentinel file
  --test         -t  Test: print queries and exit without doing anything
  --time         -m  Time to run before exiting.  Optional suffix s=seconds,
                     m=minutes, h=hours, d=days; if no suffix, s is used.
  --txnsize      -z  Number of rows per transaction; disable with 0; default 1)
  --version          Output version information and exit
  --where        -W  WHERE clause to limit which rows to archive (required)
  --whyquit      -q  Print reason for exiting unless rows exhausted
  --ignore and --replace are mutually exclusive.
  --txnsize and --commit-each are mutually exclusive.
  --lpins and --delayedins are mutually exclusive.
  --sharelock and --forupdate are mutually exclusive.
  --analyze and --optimize are mutually exclusive.
  Specify at least one of --dest, --file, or --purge.
  DSN values in --dest default to values from --source if COPY is yes.
  --noascend and --nodelete are mutually exclusive.