1MK-ARCHIVER(1) User Contributed Perl Documentation MK-ARCHIVER(1)
2
6 mk-archiver - Archive rows from a MySQL table into another table or a
7 file.
8
10 Usage: mk-archiver [OPTION...] --source DSN --where WHERE
11 mk-archiver nibbles records from a MySQL table. The --source and
13 --dest arguments use DSN syntax; if COPY is yes, --dest defaults to the
14 key's value from --source.
15 Examples:
17 Archive all rows from oltp_server to olap_server and to a file:
19 mk-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server \
21 --file '/var/log/archive/%Y-%m-%d-%D.%t' \
22 --where "1=1" --limit 1000 --commit-each
23 Purge (delete) orphan rows from child table:
25 mk-archiver --source h=host,D=db,t=child --purge \
27 --where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'
28
30 The following section is included to inform users about the potential
31 risks, whether known or unknown, of using this tool. The two main
32 categories of risks are those created by the nature of the tool (e.g.
33 read-only tools vs. read-write tools) and those created by bugs.
34 mk-achiver is a read-write tool. It deletes data from the source by
36 default, so you should test your archiving jobs with the "--dry-run"
37 option if you're not sure about them. It is designed to have as little
38 impact on production systems as possible, but tuning with "--limit",
39 "--txn-size" and similar options might be a good idea too.
40 If you write or use "--plugin" modules, you should ensure they are good
42 quality and well-tested.
43 At the time of this release there is an unverified bug with
45 "--bulk-insert" that may cause data loss.
46 The authoritative source for updated information is always the online
48 issue tracking system. Issues that affect this tool will be marked as
49 such. You can see a list of such issues at the following URL:
50 <http://www.maatkit.org/bugs/mk-archiver>.
51 See also "BUGS" for more information on filing bugs and getting help.
53
55 mk-archiver is the tool I use to archive tables as described in
56 <http://tinyurl.com/mysql-archiving>. The goal is a low-impact,
57 forward-only job to nibble old data out of the table without impacting
58 OLTP queries much. You can insert the data into another table, which
59 need not be on the same server. You can also write it to a file in a
60 format suitable for LOAD DATA INFILE. Or you can do neither, in which
61 case it's just an incremental DELETE.
62 mk-archiver is extensible via a plugin mechanism. You can inject your
64 own code to add advanced archiving logic that could be useful for
65 archiving dependent data, applying complex business rules, or building
66 a data warehouse during the archiving process.
67 You need to choose values carefully for some options. The most
69 important are "--limit", "--retries", and "--txn-size".
70 The strategy is to find the first row(s), then scan some index forward-
72 only to find more rows efficiently. Each subsequent query should not
73 scan the entire table; it should seek into the index, then scan until
74 it finds more archivable rows. Specifying the index with the 'i' part
75 of the "--source" argument can be crucial for this; use "--dry-run" to
76 examine the generated queries and be sure to EXPLAIN them to see if
77 they are efficient (most of the time you probably want to scan the
78 PRIMARY key, which is the default). Even better, profile mk-archiver
79 with mk-query-profiler and make sure it is not scanning the whole table
80 every query.
81 You can disable the seek-then-scan optimizations partially or wholly
83 with "--no-ascend" and "--ascend-first". Sometimes this may be more
84 efficient for multi-column keys. Be aware that mk-archiver is built to
85 start at the beginning of the index it chooses and scan it forward-
86 only. This might result in long table scans if you're trying to nibble
87 from the end of the table by an index other than the one it prefers.
88 See "--source" and read the documentation on the "i" part if this
89 applies to you.
90
92 If you specify "--progress", the output is a header row, plus status
93 output at intervals. Each row in the status output lists the current
94 date and time, how many seconds mk-archiver has been running, and how
95 many rows it has archived.
96 If you specify "--statistics", "mk-archiver" outputs timing and other
98 information to help you identify which part of your archiving process
99 takes the most time.
100
102 mk-archiver tries to catch signals and exit gracefully; for example, if
103 you send it SIGTERM (Ctrl-C on UNIX-ish systems), it will catch the
104 signal, print a message about the signal, and exit fairly normally. It
105 will not execute "--analyze" or "--optimize", because these may take a
106 long time to finish. It will run all other code normally, including
107 calling after_finish() on any plugins (see "EXTENDING").
108 In other words, a signal, if caught, will break out of the main
110 archiving loop and skip optimize/analyze.
111
113 Specify at least one of "--dest", "--file", or "--purge".
114 "--ignore" and "--replace" are mutually exclusive.
116 "--txn-size" and "--commit-each" are mutually exclusive.
118 "--low-priority-insert" and "--delayed-insert" are mutually exclusive.
120 "--share-lock" and "--for-update" are mutually exclusive.
122 "--analyze" and "--optimize" are mutually exclusive.
124 "--no-ascend" and "--no-delete" are mutually exclusive.
126 DSN values in "--dest" default to values from "--source" if COPY is
128 yes.
129 --analyze
131 type: string
132 Run ANALYZE TABLE afterwards on "--source" and/or "--dest".
134 Runs ANALYZE TABLE after finishing. The argument is an arbitrary
136 string. If it contains the letter 's', the source will be
137 analyzed. If it contains 'd', the destination will be analyzed.
138 You can specify either or both. For example, the following will
139 analyze both:
140 --analyze=ds
142 See <http://dev.mysql.com/doc/en/analyze-table.html> for details on
144 ANALYZE TABLE.
145 --ascend-first
147 Ascend only first column of index.
148 If you do want to use the ascending index optimization (see
150 "--no-ascend"), but do not want to incur the overhead of ascending
151 a large multi-column index, you can use this option to tell mk-
152 archiver to ascend only the leftmost column of the index. This can
153 provide a significant performance boost over not ascending the
154 index at all, while avoiding the cost of ascending the whole index.
155 See "EXTENDING" for a discussion of how this interacts with
157 plugins.
158 --ask-pass
160 Prompt for a password when connecting to MySQL.
161 --buffer
163 Buffer output to "--file" and flush at commit.
164 Disables autoflushing to "--file" and flushes "--file" to disk only
166 when a transaction commits. This typically means the file is
167 block-flushed by the operating system, so there may be some
168 implicit flushes to disk between commits as well. The default is
169 to flush "--file" to disk after every row.
170 The danger is that a crash might cause lost data.
172 The performance increase I have seen from using "--buffer" is
174 around 5 to 15 percent. Your mileage may vary.
175 --bulk-delete
177 Delete each chunk with a single statement (implies
178 "--commit-each").
179 Delete each chunk of rows in bulk with a single "DELETE" statement.
181 The statement deletes every row between the first and last row of
182 the chunk, inclusive. It implies "--commit-each", since it would
183 be a bad idea to "INSERT" rows one at a time and commit them before
184 the bulk "DELETE".
185 The normal method is to delete every row by its primary key. Bulk
187 deletes might be a lot faster. They also might not be faster if
188 you have a complex "WHERE" clause.
189 This option completely defers all "DELETE" processing until the
191 chunk of rows is finished. If you have a plugin on the source, its
192 "before_delete" method will not be called. Instead, its
193 "before_bulk_delete" method is called later.
194 WARNING: if you have a plugin on the source that sometimes doesn't
196 return true from "is_archivable()", you should use this option only
197 if you understand what it does. If the plugin instructs
198 "mk-archiver" not to archive a row, it will still be deleted by the
199 bulk delete!
200 --[no]bulk-delete-limit
202 default: yes
203 Add "--limit" to "--bulk-delete" statement.
205 This is an advanced option and you should not disable it unless you
207 know what you are doing and why! By default, "--bulk-delete"
208 appends a "--limit" clause to the bulk delete SQL statement. In
209 certain cases, this clause can be omitted by specifying
210 "--no-bulk-delete-limit". "--limit" must still be specified.
211 --bulk-insert
213 Insert each chunk with LOAD DATA INFILE (implies "--bulk-delete"
214 "--commit-each").
215 Insert each chunk of rows with "LOAD DATA LOCAL INFILE". This may
217 be much faster than inserting a row at a time with "INSERT"
218 statements. It is implemented by creating a temporary file for
219 each chunk of rows, and writing the rows to this file instead of
220 inserting them. When the chunk is finished, it uploads the rows.
221 To protect the safety of your data, this option forces bulk deletes
223 to be used. It would be unsafe to delete each row as it is found,
224 before inserting the rows into the destination first. Forcing bulk
225 deletes guarantees that the deletion waits until the insertion is
226 successful.
227 The "--low-priority-insert", "--replace", and "--ignore" options
229 work with this option, but "--delayed-insert" does not.
230 --charset
232 short form: -A; type: string
233 Default character set. If the value is utf8, sets Perl's binmode
235 on STDOUT to utf8, passes the mysql_enable_utf8 option to
236 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
237 other value sets binmode on STDOUT without the utf8 layer, and runs
238 SET NAMES after connecting to MySQL.
239 See also "--[no]check-charset".
241 --[no]check-charset
243 default: yes
244 Ensure connection and table character sets are the same. Disabling
246 this check may cause text to be erroneously converted from one
247 character set to another (usually from utf8 to latin1) which may
248 cause data loss or mojibake. Disabling this check may be useful or
249 necessary when character set conversions are intended.
250 --[no]check-columns
252 default: yes
253 Ensure "--source" and "--dest" have same columns.
255 Enabled by default; causes mk-archiver to check that the source and
257 destination tables have the same columns. It does not check column
258 order, data type, etc. It just checks that all columns in the
259 source exist in the destination and vice versa. If there are any
260 differences, mk-archiver will exit with an error.
261 To disable this check, specify --no-check-columns.
263 --check-interval
265 type: time; default: 1s
266 How often to check for slave lag if "--check-slave-lag" is given.
268 --check-slave-lag
270 type: string
271 Pause archiving until the specified DSN's slave lag is less than
273 "--max-lag".
274 --columns
276 short form: -c; type: array
277 Comma-separated list of columns to archive.
279 Specify a comma-separated list of columns to fetch, write to the
281 file, and insert into the destination table. If specified, mk-
282 archiver ignores other columns unless it needs to add them to the
283 "SELECT" statement for ascending an index or deleting rows. It
284 fetches and uses these extra columns internally, but does not write
285 them to the file or to the destination table. It does pass them to
286 plugins.
287 See also "--primary-key-only".
289 --commit-each
291 Commit each set of fetched and archived rows (disables
292 "--txn-size").
293 Commits transactions and flushes "--file" after each set of rows
295 has been archived, before fetching the next set of rows, and before
296 sleeping if "--sleep" is specified. Disables "--txn-size"; use
297 "--limit" to control the transaction size with "--commit-each".
298 This option is useful as a shortcut to make "--limit" and
300 "--txn-size" the same value, but more importantly it avoids
301 transactions being held open while searching for more rows. For
302 example, imagine you are archiving old rows from the beginning of a
303 very large table, with "--limit" 1000 and "--txn-size" 1000. After
304 some period of finding and archiving 1000 rows at a time, mk-
305 archiver finds the last 999 rows and archives them, then executes
306 the next SELECT to find more rows. This scans the rest of the
307 table, but never finds any more rows. It has held open a
308 transaction for a very long time, only to determine it is finished
309 anyway. You can use "--commit-each" to avoid this.
310 --config
312 type: Array
313 Read this comma-separated list of config files; if specified, this
315 must be the first option on the command line.
316 --delayed-insert
318 Add the DELAYED modifier to INSERT statements.
319 Adds the DELAYED modifier to INSERT or REPLACE statements. See
321 <http://dev.mysql.com/doc/en/insert.html> for details.
322 --dest
324 type: DSN
325 DSN specifying the table to archive to.
327 This item specifies a table into which mk-archiver will insert rows
329 archived from "--source". It uses the same key=val argument format
330 as "--source". Most missing values default to the same values as
331 "--source", so you don't have to repeat options that are the same
332 in "--source" and "--dest". Use the "--help" option to see which
333 values are copied from "--source".
334 WARNING: Using a default options file (F) DSN option that defines a
336 socket for "--source" causes mk-archiver to connect to "--dest"
337 using that socket unless another socket for "--dest" is specified.
338 This means that mk-archiver may incorrectly connect to "--source"
339 when it connects to "--dest". For example:
340 --source F=host1.cnf,D=db,t=tbl --dest h=host2
342 When mk-archiver connects to "--dest", host2, it will connect via
344 the "--source", host1, socket defined in host1.cnf.
345 --dry-run
347 Print queries and exit without doing anything.
348 Causes mk-archiver to exit after printing the filename and SQL
350 statements it will use.
351 --file
353 type: string
354 File to archive to, with DATE_FORMAT()-like formatting.
356 Filename to write archived rows to. A subset of MySQL's
358 DATE_FORMAT() formatting codes are allowed in the filename, as
359 follows:
360 %d Day of the month, numeric (01..31)
362 %H Hour (00..23)
363 %i Minutes, numeric (00..59)
364 %m Month, numeric (01..12)
365 %s Seconds (00..59)
366 %Y Year, numeric, four digits
367 You can use the following extra format codes too:
369 %D Database name
371 %t Table name
372 Example:
374 --file '/var/log/archive/%Y-%m-%d-%D.%t'
376 The file's contents are in the same format used by SELECT INTO
378 OUTFILE, as documented in the MySQL manual: rows terminated by
379 newlines, columns terminated by tabs, NULL characters are
380 represented by \N, and special characters are escaped by \. This
381 lets you reload a file with LOAD DATA INFILE's default settings.
382 If you want a column header at the top of the file, see "--header".
384 The file is auto-flushed by default; see "--buffer".
385 --for-update
387 Adds the FOR UPDATE modifier to SELECT statements.
388 For details, see
390 <http://dev.mysql.com/doc/en/innodb-locking-reads.html>.
391 --header
393 Print column header at top of "--file".
394 Writes column names as the first line in the file given by
396 "--file". If the file exists, does not write headers; this keeps
397 the file loadable with LOAD DATA INFILE in case you append more
398 output to it.
399 --help
401 Show help and exit.
402 --high-priority-select
404 Adds the HIGH_PRIORITY modifier to SELECT statements.
405 See <http://dev.mysql.com/doc/en/select.html> for details.
407 --host
409 short form: -h; type: string
410 Connect to host.
412 --ignore
414 Use IGNORE for INSERT statements.
415 Causes INSERTs into "--dest" to be INSERT IGNORE.
417 --limit
419 type: int; default: 1
420 Number of rows to fetch and archive per statement.
422 Limits the number of rows returned by the SELECT statements that
424 retrieve rows to archive. Default is one row. It may be more
425 efficient to increase the limit, but be careful if you are
426 archiving sparsely, skipping over many rows; this can potentially
427 cause more contention with other queries, depending on the storage
428 engine, transaction isolation level, and options such as
429 "--for-update".
430 --local
432 Do not write OPTIMIZE or ANALYZE queries to binlog.
433 Adds the NO_WRITE_TO_BINLOG modifier to ANALYZE and OPTIMIZE
435 queries. See "--analyze" for details.
436 --low-priority-delete
438 Adds the LOW_PRIORITY modifier to DELETE statements.
439 See <http://dev.mysql.com/doc/en/delete.html> for details.
441 --low-priority-insert
443 Adds the LOW_PRIORITY modifier to INSERT or REPLACE statements.
444 See <http://dev.mysql.com/doc/en/insert.html> for details.
446 --max-lag
448 type: time; default: 1s
449 Pause archiving if the slave given by "--check-slave-lag" lags.
451 This option causes mk-archiver to look at the slave every time it's
453 about to fetch another row. If the slave's lag is greater than the
454 option's value, or if the slave isn't running (so its lag is NULL),
455 mk-table-checksum sleeps for "--check-interval" seconds and then
456 looks at the lag again. It repeats until the slave is caught up,
457 then proceeds to fetch and archive the row.
458 This option may eliminate the need for "--sleep" or "--sleep-coef".
460 --no-ascend
462 Do not use ascending index optimization.
463 The default ascending-index optimization causes "mk-archiver" to
465 optimize repeated "SELECT" queries so they seek into the index
466 where the previous query ended, then scan along it, rather than
467 scanning from the beginning of the table every time. This is
468 enabled by default because it is generally a good strategy for
469 repeated accesses.
470 Large, multiple-column indexes may cause the WHERE clause to be
472 complex enough that this could actually be less efficient.
473 Consider for example a four-column PRIMARY KEY on (a, b, c, d).
474 The WHERE clause to start where the last query ended is as follows:
475 WHERE (a > ?)
477 OR (a = ? AND b > ?)
478 OR (a = ? AND b = ? AND c > ?)
479 OR (a = ? AND b = ? AND c = ? AND d >= ?)
480 Populating the placeholders with values uses memory and CPU, adds
482 network traffic and parsing overhead, and may make the query harder
483 for MySQL to optimize. A four-column key isn't a big deal, but a
484 ten-column key in which every column allows "NULL" might be.
485 Ascending the index might not be necessary if you know you are
487 simply removing rows from the beginning of the table in chunks, but
488 not leaving any holes, so starting at the beginning of the table is
489 actually the most efficient thing to do.
490 See also "--ascend-first". See "EXTENDING" for a discussion of how
492 this interacts with plugins.
493 --no-delete
495 Do not delete archived rows.
496 Causes "mk-archiver" not to delete rows after processing them.
498 This disallows "--no-ascend", because enabling them both would
499 cause an infinite loop.
500 If there is a plugin on the source DSN, its "before_delete" method
502 is called anyway, even though "mk-archiver" will not execute the
503 delete. See "EXTENDING" for more on plugins.
504 --optimize
506 type: string
507 Run OPTIMIZE TABLE afterwards on "--source" and/or "--dest".
509 Runs OPTIMIZE TABLE after finishing. See "--analyze" for the
511 option syntax and <http://dev.mysql.com/doc/en/optimize-table.html>
512 for details on OPTIMIZE TABLE.
513 --password
515 short form: -p; type: string
516 Password to use when connecting.
518 --pid
520 type: string
521 Create the given PID file when daemonized. The file contains the
523 process ID of the daemonized instance. The PID file is removed
524 when the daemonized instance exits. The program checks for the
525 existence of the PID file when starting; if it exists and the
526 process with the matching PID exists, the program exits.
527 --plugin
529 type: string
530 Perl module name to use as a generic plugin.
532 Specify the Perl module name of a general-purpose plugin. It is
534 currently used only for statistics (see "--statistics") and must
535 have "new()" and a "statistics()" method.
536 The "new( src =" $src, dst => $dst, opts => $o )> method gets the
538 source and destination DSNs, and their database connections, just
539 like the connection-specific plugins do. It also gets an
540 OptionParser object ($o) for accessing command-line options
541 (example: "$o-"get('purge');>).
542 The "statistics(\%stats, $time)" method gets a hashref of the
544 statistics collected by the archiving job, and the time the whole
545 job started.
546 --port
548 short form: -P; type: int
549 Port number to use for connection.
551 --primary-key-only
553 Primary key columns only.
554 A shortcut for specifying "--columns" with the primary key columns.
556 This is an efficiency if you just want to purge rows; it avoids
557 fetching the entire row, when only the primary key columns are
558 needed for "DELETE" statements. See also "--purge".
559 --progress
561 type: int
562 Print progress information every X rows.
564 Prints current time, elapsed time, and rows archived every X rows.
566 --purge
568 Purge instead of archiving; allows omitting "--file" and "--dest".
569 Allows archiving without a "--file" or "--dest" argument, which is
571 effectively a purge since the rows are just deleted.
572 If you just want to purge rows, consider specifying the table's
574 primary key columns with "--primary-key-only". This will prevent
575 fetching all columns from the server for no reason.
576 --quick-delete
578 Adds the QUICK modifier to DELETE statements.
579 See <http://dev.mysql.com/doc/en/delete.html> for details. As
581 stated in the documentation, in some cases it may be faster to use
582 DELETE QUICK followed by OPTIMIZE TABLE. You can use "--optimize"
583 for this.
584 --quiet
586 short form: -q
587 Do not print any output, such as for "--statistics".
589 Suppresses normal output, including the output of "--statistics",
591 but doesn't suppress the output from "--why-quit".
592 --replace
594 Causes INSERTs into "--dest" to be written as REPLACE.
595 --retries
597 type: int; default: 1
598 Number of retries per timeout or deadlock.
600 Specifies the number of times mk-archiver should retry when there
602 is an InnoDB lock wait timeout or deadlock. When retries are
603 exhausted, mk-archiver will exit with an error.
604 Consider carefully what you want to happen when you are archiving
606 between a mixture of transactional and non-transactional storage
607 engines. The INSERT to "--dest" and DELETE from "--source" are on
608 separate connections, so they do not actually participate in the
609 same transaction even if they're on the same server. However, mk-
610 archiver implements simple distributed transactions in code, so
611 commits and rollbacks should happen as desired across the two
612 connections.
613 At this time I have not written any code to handle errors with
615 transactional storage engines other than InnoDB. Request that
616 feature if you need it.
617 --run-time
619 type: time
620 Time to run before exiting.
622 Optional suffix s=seconds, m=minutes, h=hours, d=days; if no
624 suffix, s is used.
625 --[no]safe-auto-increment
627 default: yes
628 Do not archive row with max AUTO_INCREMENT.
630 Adds an extra WHERE clause to prevent mk-archiver from removing the
632 newest row when ascending a single-column AUTO_INCREMENT key. This
633 guards against re-using AUTO_INCREMENT values if the server
634 restarts, and is enabled by default.
635 The extra WHERE clause contains the maximum value of the auto-
637 increment column as of the beginning of the archive or purge job.
638 If new rows are inserted while mk-archiver is running, it will not
639 see them.
640 --sentinel
642 type: string; default: /tmp/mk-archiver-sentinel
643 Exit if this file exists.
645 The presence of the file specified by "--sentinel" will cause mk-
647 archiver to stop archiving and exit. The default is
648 /tmp/mk-archiver-sentinel. You might find this handy to stop cron
649 jobs gracefully if necessary. See also "--stop".
650 --set-vars
652 type: string; default: wait_timeout=10000
653 Set these MySQL variables.
655 Specify any variables you want to be set immediately after
657 connecting to MySQL. These will be included in a "SET" command.
658 --share-lock
660 Adds the LOCK IN SHARE MODE modifier to SELECT statements.
661 See <http://dev.mysql.com/doc/en/innodb-locking-reads.html>.
663 --skip-foreign-key-checks
665 Disables foreign key checks with SET FOREIGN_KEY_CHECKS=0.
666 --sleep
668 type: int
669 Sleep time between fetches.
671 Specifies how long to sleep between SELECT statements. Default is
673 not to sleep at all. Transactions are NOT committed, and the
674 "--file" file is NOT flushed, before sleeping. See "--txn-size" to
675 control that.
676 If "--commit-each" is specified, committing and flushing happens
678 before sleeping.
679 --sleep-coef
681 type: float
682 Calculate "--sleep" as a multiple of the last SELECT time.
684 If this option is specified, mk-archiver will sleep for the query
686 time of the last SELECT multiplied by the specified coefficient.
687 This is a slightly more sophisticated way to throttle the SELECTs:
689 sleep a varying amount of time between each SELECT, depending on
690 how long the SELECTs are taking.
691 --socket
693 short form: -S; type: string
694 Socket file to use for connection.
696 --source
698 type: DSN
699 DSN specifying the table to archive from (required). This argument
701 is a DSN. See "DSN OPTIONS" for the syntax. Most options control
702 how mk-archiver connects to MySQL, but there are some extended DSN
703 options in this tool's syntax. The D, t, and i options select a
704 table to archive:
705 --source h=my_server,D=my_database,t=my_tbl
707 The a option specifies the database to set as the connection's
709 default with USE. If the b option is true, it disables binary
710 logging with SQL_LOG_BIN. The m option specifies pluggable
711 actions, which an external Perl module can provide. The only
712 required part is the table; other parts may be read from various
713 places in the environment (such as options files).
714 The 'i' part deserves special mention. This tells mk-archiver
716 which index it should scan to archive. This appears in a FORCE
717 INDEX or USE INDEX hint in the SELECT statements used to fetch
718 archivable rows. If you don't specify anything, mk-archiver will
719 auto-discover a good index, preferring a "PRIMARY KEY" if one
720 exists. In my experience this usually works well, so most of the
721 time you can probably just omit the 'i' part.
722 The index is used to optimize repeated accesses to the table; mk-
724 archiver remembers the last row it retrieves from each SELECT
725 statement, and uses it to construct a WHERE clause, using the
726 columns in the specified index, that should allow MySQL to start
727 the next SELECT where the last one ended, rather than potentially
728 scanning from the beginning of the table with each successive
729 SELECT. If you are using external plugins, please see "EXTENDING"
730 for a discussion of how they interact with ascending indexes.
731 The 'a' and 'b' options allow you to control how statements flow
733 through the binary log. If you specify the 'b' option, binary
734 logging will be disabled on the specified connection. If you
735 specify the 'a' option, the connection will "USE" the specified
736 database, which you can use to prevent slaves from executing the
737 binary log events with "--replicate-ignore-db" options. These two
738 options can be used as different methods to achieve the same goal:
739 archive data off the master, but leave it on the slave. For
740 example, you can run a purge job on the master and prevent it from
741 happening on the slave using your method of choice.
742 WARNING: Using a default options file (F) DSN option that defines a
744 socket for "--source" causes mk-archiver to connect to "--dest"
745 using that socket unless another socket for "--dest" is specified.
746 This means that mk-archiver may incorrectly connect to "--source"
747 when it is meant to connect to "--dest". For example:
748 --source F=host1.cnf,D=db,t=tbl --dest h=host2
750 When mk-archiver connects to "--dest", host2, it will connect via
752 the "--source", host1, socket defined in host1.cnf.
753 --statistics
755 Collect and print timing statistics.
756 Causes mk-archiver to collect timing statistics about what it does.
758 These statistics are available to the plugin specified by
759 "--plugin"
760 Unless you specify "--quiet", "mk-archiver" prints the statistics
762 when it exits. The statistics look like this:
763 Started at 2008-07-18T07:18:53, ended at 2008-07-18T07:18:53
765 Source: D=db,t=table
766 SELECT 4
767 INSERT 4
768 DELETE 4
769 Action Count Time Pct
770 commit 10 0.1079 88.27
771 select 5 0.0047 3.87
772 deleting 4 0.0028 2.29
773 inserting 4 0.0028 2.28
774 other 0 0.0040 3.29
775 The first two (or three) lines show times and the source and
777 destination tables. The next three lines show how many rows were
778 fetched, inserted, and deleted.
779 The remaining lines show counts and timing. The columns are the
781 action, the total number of times that action was timed, the total
782 time it took, and the percent of the program's total runtime. The
783 rows are sorted in order of descending total time. The last row is
784 the rest of the time not explicitly attributed to anything.
785 Actions will vary depending on command-line options.
786 If "--why-quit" is given, its behavior is changed slightly. This
788 option causes it to print the reason for exiting even when it's
789 just because there are no more rows.
790 This option requires the standard Time::HiRes module, which is part
792 of core Perl on reasonably new Perl releases.
793 --stop
795 Stop running instances by creating the sentinel file.
796 Causes mk-archiver to create the sentinel file specified by
798 "--sentinel" and exit. This should have the effect of stopping all
799 running instances which are watching the same sentinel file.
800 --txn-size
802 type: int; default: 1
803 Number of rows per transaction.
805 Specifies the size, in number of rows, of each transaction. Zero
807 disables transactions altogether. After mk-archiver processes this
808 many rows, it commits both the "--source" and the "--dest" if
809 given, and flushes the file given by "--file".
810 This parameter is critical to performance. If you are archiving
812 from a live server, which for example is doing heavy OLTP work, you
813 need to choose a good balance between transaction size and commit
814 overhead. Larger transactions create the possibility of more lock
815 contention and deadlocks, but smaller transactions cause more
816 frequent commit overhead, which can be significant. To give an
817 idea, on a small test set I worked with while writing mk-archiver,
818 a value of 500 caused archiving to take about 2 seconds per 1000
819 rows on an otherwise quiet MySQL instance on my desktop machine,
820 archiving to disk and to another table. Disabling transactions
821 with a value of zero, which turns on autocommit, dropped
822 performance to 38 seconds per thousand rows.
823 If you are not archiving from or to a transactional storage engine,
825 you may want to disable transactions so mk-archiver doesn't try to
826 commit.
827 --user
829 short form: -u; type: string
830 User for login if not current user.
832 --version
834 Show version and exit.
835 --where
837 type: string
838 WHERE clause to limit which rows to archive (required).
840 Specifies a WHERE clause to limit which rows are archived. Do not
842 include the word WHERE. You may need to quote the argument to
843 prevent your shell from interpreting it. For example:
844 --where 'ts < current_date - interval 90 day'
846 For safety, "--where" is required. If you do not require a WHERE
848 clause, use "--where" 1=1.
849 --why-quit
851 Print reason for exiting unless rows exhausted.
852 Causes mk-archiver to print a message if it exits for any reason
854 other than running out of rows to archive. This can be useful if
855 you have a cron job with "--run-time" specified, for example, and
856 you want to be sure mk-archiver is finishing before running out of
857 time.
858 If "--statistics" is given, the behavior is changed slightly. It
860 will print the reason for exiting even when it's just because there
861 are no more rows.
862 This output prints even if "--quiet" is given. That's so you can
864 put "mk-archiver" in a "cron" job and get an email if there's an
865 abnormal exit.
866
868 These DSN options are used to create a DSN. Each option is given like
869 "option=value". The options are case-sensitive, so P and p are not the
870 same option. There cannot be whitespace before or after the "=" and if
871 the value contains whitespace it must be quoted. DSN options are
872 comma-separated. See the maatkit manpage for full details.
873 • a
875 copy: no
877 Database to USE when executing queries.
879 • A
881 dsn: charset; copy: yes
883 Default character set.
885 • b
887 copy: no
889 If true, disable binlog with SQL_LOG_BIN.
891 • D
893 dsn: database; copy: yes
895 Database that contains the table.
897 • F
899 dsn: mysql_read_default_file; copy: yes
901 Only read default options from the given file
903 • h
905 dsn: host; copy: yes
907 Connect to host.
909 • i
911 copy: yes
913 Index to use.
915 • m
917 copy: no
919 Plugin module name.
921 • p
923 dsn: password; copy: yes
925 Password to use when connecting.
927 • P
929 dsn: port; copy: yes
931 Port number to use for connection.
933 • S
935 dsn: mysql_socket; copy: yes
937 Socket file to use for connection.
939 • t
941 copy: yes
943 Table to archive from/to.
945 • u
947 dsn: user; copy: yes
949 User for login if not current user.
951
953 mk-archiver is extensible by plugging in external Perl modules to
954 handle some logic and/or actions. You can specify a module for both
955 the "--source" and the "--dest", with the 'm' part of the
956 specification. For example:
957 --source D=test,t=test1,m=My::Module1 --dest m=My::Module2,t=test2
959 This will cause mk-archiver to load the My::Module1 and My::Module2
961 packages, create instances of them, and then make calls to them during
962 the archiving process.
963 You can also specify a plugin with "--plugin".
965 The module must provide this interface:
967 new(dbh => $dbh, db => $db_name, tbl => $tbl_name)
969 The plugin's constructor is passed a reference to the database
970 handle, the database name, and table name. The plugin is created
971 just after mk-archiver opens the connection, and before it examines
972 the table given in the arguments. This gives the plugin a chance
973 to create and populate temporary tables, or do other setup work.
974 before_begin(cols => \@cols, allcols => \@allcols)
976 This method is called just before mk-archiver begins iterating
977 through rows and archiving them, but after it does all other setup
978 work (examining table structures, designing SQL queries, and so
979 on). This is the only time mk-archiver tells the plugin column
980 names for the rows it will pass the plugin while archiving.
981 The "cols" argument is the column names the user requested to be
983 archived, either by default or by the "--columns" option. The
984 "allcols" argument is the list of column names for every row mk-
985 archiver will fetch from the source table. It may fetch more
986 columns than the user requested, because it needs some columns for
987 its own use. When subsequent plugin functions receive a row, it is
988 the full row containing all the extra columns, if any, added to the
989 end.
990 is_archivable(row => \@row)
992 This method is called for each row to determine whether it is
993 archivable. This applies only to "--source". The argument is the
994 row itself, as an arrayref. If the method returns true, the row
995 will be archived; otherwise it will be skipped.
996 Skipping a row adds complications for non-unique indexes. Normally
998 mk-archiver uses a WHERE clause designed to target the last
999 processed row as the place to start the scan for the next SELECT
1000 statement. If you have skipped the row by returning false from
1001 is_archivable(), mk-archiver could get into an infinite loop
1002 because the row still exists. Therefore, when you specify a plugin
1003 for the "--source" argument, mk-archiver will change its WHERE
1004 clause slightly. Instead of starting at "greater than or equal to"
1005 the last processed row, it will start "strictly greater than."
1006 This will work fine on unique indexes such as primary keys, but it
1007 may skip rows (leave holes) on non-unique indexes or when ascending
1008 only the first column of an index.
1009 "mk-archiver" will change the clause in the same way if you specify
1011 "--no-delete", because again an infinite loop is possible.
1012 If you specify the "--bulk-delete" option and return false from
1014 this method, "mk-archiver" may not do what you want. The row won't
1015 be archived, but it will be deleted, since bulk deletes operate on
1016 ranges of rows and don't know which rows the plugin selected to
1017 keep.
1018 If you specify the "--bulk-insert" option, this method's return
1020 value will influence whether the row is written to the temporary
1021 file for the bulk insert, so bulk inserts will work as expected.
1022 However, bulk inserts require bulk deletes.
1023 before_delete(row => \@row)
1025 This method is called for each row just before it is deleted. This
1026 applies only to "--source". This is a good place for you to handle
1027 dependencies, such as deleting things that are foreign-keyed to the
1028 row you are about to delete. You could also use this to
1029 recursively archive all dependent tables.
1030 This plugin method is called even if "--no-delete" is given, but
1032 not if "--bulk-delete" is given.
1033 before_bulk_delete(first_row => \@row, last_row => \@row)
1035 This method is called just before a bulk delete is executed. It is
1036 similar to the "before_delete" method, except its arguments are the
1037 first and last row of the range to be deleted. It is called even
1038 if "--no-delete" is given.
1039 before_insert(row => \@row)
1041 This method is called for each row just before it is inserted.
1042 This applies only to "--dest". You could use this to insert the
1043 row into multiple tables, perhaps with an ON DUPLICATE KEY UPDATE
1044 clause to build summary tables in a data warehouse.
1045 This method is not called if "--bulk-insert" is given.
1047 before_bulk_insert(first_row => \@row, last_row => \@row, filename =>
1049 bulk_insert_filename)
1050 This method is called just before a bulk insert is executed. It is
1051 similar to the "before_insert" method, except its arguments are the
1052 first and last row of the range to be deleted.
1053 custom_sth(row => \@row, sql => $sql)
1055 This method is called just before inserting the row, but after
1056 "before_insert()". It allows the plugin to specify different
1057 "INSERT" statement if desired. The return value (if any) should be
1058 a DBI statement handle. The "sql" parameter is the SQL text used
1059 to prepare the default "INSERT" statement. This method is not
1060 called if you specify "--bulk-insert".
1061 If no value is returned, the default "INSERT" statement handle is
1063 used.
1064 This method applies only to the plugin specified for "--dest", so
1066 if your plugin isn't doing what you expect, check that you've
1067 specified it for the destination and not the source.
1068 custom_sth_bulk(first_row => \@row, last_row => \@row, sql => $sql,
1070 filename => $bulk_insert_filename)
1071 If you've specified "--bulk-insert", this method is called just
1072 before the bulk insert, but after "before_bulk_insert()", and the
1073 arguments are different.
1074 This method's return value etc is similar to the "custom_sth()"
1076 method.
1077 after_finish()
1079 This method is called after mk-archiver exits the archiving loop,
1080 commits all database handles, closes "--file", and prints the final
1081 statistics, but before mk-archiver runs ANALYZE or OPTIMIZE (see
1082 "--analyze" and "--optimize").
1083 If you specify a plugin for both "--source" and "--dest", mk-archiver
1085 constructs, calls before_begin(), and calls after_finish() on the two
1086 plugins in the order "--source", "--dest".
1087 mk-archiver assumes it controls transactions, and that the plugin will
1089 NOT commit or roll back the database handle. The database handle
1090 passed to the plugin's constructor is the same handle mk-archiver uses
1091 itself. Remember that "--source" and "--dest" are separate handles.
1092 A sample module might look like this:
1094 package My::Module;
1096 sub new {
1098 my ( $class, %args ) = @_;
1099 return bless(\%args, $class);
1100 }
1101 sub before_begin {
1103 my ( $self, %args ) = @_;
1104 # Save column names for later
1105 $self->{cols} = $args{cols};
1106 }
1107 sub is_archivable {
1109 my ( $self, %args ) = @_;
1110 # Do some advanced logic with $args{row}
1111 return 1;
1112 }
1113 sub before_delete {} # Take no action
1115 sub before_insert {} # Take no action
1116 sub custom_sth {} # Take no action
1117 sub after_finish {} # Take no action
1118 1;
1120
1122 You can download Maatkit from Google Code at
1123 <http://code.google.com/p/maatkit/>, or you can get any of the tools
1124 easily with a command like the following:
1125 wget http://www.maatkit.org/get/toolname
1127 or
1128 wget http://www.maatkit.org/trunk/toolname
1129 Where "toolname" can be replaced with the name (or fragment of a name)
1131 of any of the Maatkit tools. Once downloaded, they're ready to run; no
1132 installation is needed. The first URL gets the latest released version
1133 of the tool, and the second gets the latest trunk code from Subversion.
1134
1136 The environment variable "MKDEBUG" enables verbose debugging output in
1137 all of the Maatkit tools:
1138 MKDEBUG=1 mk-....
1140
1142 You need Perl, DBI, DBD::mysql, and some core packages that ought to be
1143 installed in any reasonably new version of Perl.
1144
1146 For a list of known bugs see <http://www.maatkit.org/bugs/mk-archiver>.
1147 Please use Google Code Issues and Groups to report bugs or request
1149 support: <http://code.google.com/p/maatkit/>. You can also join
1150 #maatkit on Freenode to discuss Maatkit.
1151 Please include the complete command-line used to reproduce the problem
1153 you are seeing, the version of all MySQL servers involved, the complete
1154 output of the tool when run with "--version", and if possible,
1155 debugging output produced by running with the "MKDEBUG=1" environment
1156 variable.
1157
1159 Thanks to the following people, and apologies to anyone I've omitted:
1160 Andrew O'Brien,
1162
1164 This program is copyright 2007-2011 Baron Schwartz. Feedback and
1165 improvements are welcome.
1166 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
1168 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
1169 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
1170 This program is free software; you can redistribute it and/or modify it
1172 under the terms of the GNU General Public License as published by the
1173 Free Software Foundation, version 2; OR the Perl Artistic License. On
1174 UNIX and similar systems, you can issue `man perlgpl' or `man
1175 perlartistic' to read these licenses.
1176 You should have received a copy of the GNU General Public License along
1178 with this program; if not, write to the Free Software Foundation, Inc.,
1179 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
1180
1182 Baron Schwartz
1183
1185 This tool is part of Maatkit, a toolkit for power users of MySQL.
1186 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
1187 primary code contributors. Both are employed by Percona. Financial
1188 support for Maatkit development is primarily provided by Percona and
1189 its clients.
1190
1192 This manual page documents Ver 1.0.27 Distrib 7540 $Revision: 7531 $.
1193perl v5.32.1 2021-01-26 MK-ARCHIVER(1)