- 论坛徽章:
- 0
|
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
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
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
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.
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u3/111930/showart_2184779.html |
|