.\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings. \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote. | will give a
.\" real vertical bar. \*(C+ will give a nicer C++. Capital omega is used to
.\" do unbreakable dashes and therefore won't be available. \*(C` and \*(C'
.\" expand to `' in nroff, nothing in troff, for use with C<>.
.tr \(*W-|\(bv\*(Tr
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
. ds -- \(*W-
. ds PI pi
. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch
. ds L" ""
. ds R" ""
. ds C` ""
. ds C' ""
'br\}
.el\{\
. ds -- \|\(em\|
. ds PI \(*p
. ds L" ``
. ds R" ''
'br\}
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
.\" entries marked with X<> in POD. Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.if \nF \{\
. de IX
. tm Index:\\$1\t\\n%\t"\\$2"
..
. nr % 0
. rr F
.\}
.\"
.\" For nroff, turn off justification. Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.hy 0
.if n .na
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear. Run. Save yourself. No user-serviceable parts.
. \" fudge factors for nroff and troff
.if n \{\
. ds #H 0
. ds #V .8m
. ds #F .3m
. ds #[ \f1
. ds #] \fP
.\}
.if t \{\
. ds #H ((1u-(\\\\n(.fu%2u))*.13m)
. ds #V .6m
. ds #F 0
. ds #[ \&
. ds #] \&
.\}
. \" simple accents for nroff and troff
.if n \{\
. ds ' \&
. ds ` \&
. ds ^ \&
. ds , \&
. ds ~ ~
. ds /
.\}
.if t \{\
. ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
. ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
. ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
. ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
. ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
. ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
. \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
. \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
. \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
. ds : e
. ds 8 ss
. ds o a
. ds d- d\h'-1'\(ga
. ds D- D\h'-1'\(hy
. ds th \o'bp'
. ds Th \o'LP'
. ds ae ae
. ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "MYSQL-ARCHIVER 1"
.TH MYSQL-ARCHIVER 1 "2007-10-15" "perl v5.8.8" "User Contributed Perl Documentation"
.SH "NAME"
mysql\-archiver \- Archive rows from a MySQL table into another table or a file.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\& mysql-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server \e
\& --file '/var/log/archive/%Y-%m-%d-%D.%t' --limit 1000 --commit-each
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
mysql-archiver is the tool I use to archive tables as described in
.
The goal is a low\-impact, forward-only job to nibble old data out of the table
without impacting \s-1OLTP\s0 queries much. You can insert the data into another
table, which need not be on the same server. You can also write it to a file
in a format suitable for \s-1LOAD\s0 \s-1DATA\s0 \s-1INFILE\s0. Or you can do neither, in which
case it's just an incremental \s-1DELETE\s0.
.PP
mysql-archiver is extensible via a plugin mechanism. You can inject your own
code to add advanced archiving logic that could be useful for archiving
dependencies, applying complex business rules, or building a data warehouse
during the archiving process.
.PP
You need to choose values carefully for some options. The most important are
\&\*(L"\-\-limit\*(R", \*(L"\-\-retries\*(R", and \*(L"\-\-txnsize\*(R".
.PP
The strategy is to find the first row(s), then scan some index forward-only to
find more rows efficiently. Each subsequent query should not scan the entire
table; it should seek into the index, then scan until it finds more archivable
rows. Specifying the index with the 'i' part of the \*(L"\-\-source\*(R" argument can be
crucial for this; use \*(L"\-\-test\*(R" to examine the generated queries and be sure to
\&\s-1EXPLAIN\s0 them to see if they are efficient (most of the time you probably want
to scan the \s-1PRIMARY\s0 key, which is the default). Even better, profile
mysql-archiver with mysql-query-profiler and make sure it is not scanning the
whole table every query.
.PP
You can disable the seek-then-scan optimizations partially or wholly with
\&\*(L"\-\-noascend\*(R" and \*(L"\-\-ascendfirst\*(R". Sometimes this may be more efficient for
multi-column keys.
.PP
At the moment, there are some limitations to ease the task of working with NULLs
and indexes. The table must have a \s-1PRIMARY\s0 key, and any index you specify in
\&\*(L"\-\-source\*(R" must be composed entirely of non-NULL columns (or, if you specify
\&\*(L"\-\-ascendfirst\*(R", the first column must be non\-NULL). These restrictions may be
removed in future versions.
.SH "ERROR-HANDLING"
.IX Header "ERROR-HANDLING"
mysql-archiver tries to catch signals and exit gracefully; for example, if you
send it \s-1SIGTERM\s0 (Ctrl\-C on UNIX-ish systems), it will catch the signal, print a
message about the signal, and exit fairly normally. It will not execute
\&\*(L"\-\-analyze\*(R" or \*(L"\-\-optimize\*(R", because these may take a long time to finish.
It will run all other code normally, including calling \fIafter_finish()\fR on any
plugins (see \*(L"\s-1EXTENDING\s0\*(R").
.PP
In other words, a signal, if caught, will only break out of the main archiving
loop and skip optimize/analyze.
.SH "OPTIONS"
.IX Header "OPTIONS"
Some options are negatable by specifying them in their long form with a \-\-no
prefix.
.IP "\-\-analyze" 4
.IX Item "--analyze"
Runs \s-1ANALYZE\s0 \s-1TABLE\s0 after finishing. The argument is an arbitrary string. If it
contains the letter 's', the source will be analyzed. If it contains 'd', the
destination will be analyzed. You can specify either or both. For example, the
following will analyze both:
.Sp
.Vb 1
\& --analyze=ds
.Ve
.Sp
See for details on \s-1ANALYZE\s0
\&\s-1TABLE\s0.
.IP "\-\-ascend" 4
.IX Item "--ascend"
Causes mysql-archiver to optimize repeated \s-1SELECT\s0 queries so they seek into the
index where the previous query ended, then scan along it, rather than scanning
from the beginning of the table every time. This is enabled by default because
it is generally a good strategy for repeated accesses.
.Sp
Large, multiple-column indexes may cause the \s-1WHERE\s0 clause to be complex enough
that this could actually be less efficient. Consider for example a four-column
\&\s-1PRIMARY\s0 \s-1KEY\s0 on (a, b, c, d). The \s-1WHERE\s0 clause to start where the last query
ended is as follows:
.Sp
.Vb 4
\& WHERE (a > ?)
\& OR (a = ? AND b > ?)
\& OR (a = ? AND b = ? AND c > ?)
\& OR (a = ? AND b = ? AND c = ? AND d >= ?)
.Ve
.Sp
Populating the placeholders with values uses memory and \s-1CPU\s0, adds network
traffic and parsing overhead, and may make the query harder for MySQL to
optimize.
.Sp
Ascending the index might not be necessary if you know you are simply removing
rows from the beginning of the table in chunks, but not leaving any holes, so
starting at the beginning of the table is actually the most efficient thing to
do.
.Sp
See also \*(L"\-\-ascendfirst\*(R". See \*(L"\s-1EXTENDING\s0\*(R" for a discussion of how this
interacts with plugins.
.IP "\-\-ascendfirst" 4
.IX Item "--ascendfirst"
If you do want to use the ascending index optimization (see \*(L"\-\-ascend\*(R"), but do
not want to incur the overhead of ascending a large multi-column index, you can
use this option to tell mysql-archiver to ascend only the leftmost column of the
index. This can provide a significant performance boost over not ascending the
index at all, while avoiding the cost of ascending the whole index.
.Sp
See \*(L"\s-1EXTENDING\s0\*(R" for a discussion of how this interacts with plugins.
.IP "\-\-askpass" 4
.IX Item "--askpass"
Prompt for password for connections.
.IP "\-\-buffer" 4
.IX Item "--buffer"
Disables autoflushing to \*(L"\-\-file\*(R" and flushes \*(L"\-\-file\*(R" to disk only when a
transaction commits. This typically means the file is block-flushed by the
operating system, so there may be some implicit flushes to disk between
commits as well. The default is to flush \*(L"\-\-file\*(R" to disk after every row.
.Sp
The danger is that a crash might cause lost data.
.Sp
The performance increase I have seen from using \*(L"\-\-buffer\*(R" is around 5 to 15
percent. Your mileage may vary.
.IP "\-\-chkcols" 4
.IX Item "--chkcols"
Enabled by default; causes mysql-archiver to check that the source and destination
tables have the same columns. It does not check column order, data type, etc.
It just checks that all columns in the source exist in the destination and
vice versa. If there are any differences, mysql-archiver will exit with an
error.
.IP "\-\-columns" 4
.IX Item "--columns"
Specify a comma-separated list of columns to fetch, write to the file, and
insert into the destination table. If specified, mysql-archiver completely
ignores every other column.
.Sp
If you specify this option, at present you need to at least specify the
columns in the primary key and the index given in \*(L"\-\-source\*(R" (if any).
.Sp
See also \-\-pkonly.
.IP "\-\-commit\-each" 4
.IX Item "--commit-each"
Commits transactions and flushes \*(L"\-\-file\*(R" after each set of rows has been
archived, before fetching the next set of rows, and before sleeping if \*(L"\-\-sleep\*(R"
is specified. Disables \*(L"\-\-txnsize\*(R"; use \*(L"\-\-limit\*(R" to control the transaction size
with \*(L"\-\-commit\-each\*(R".
.Sp
This option is useful as a shortcut to make \*(L"\-\-limit\*(R" and \*(L"\-\-txnsize\*(R" the same
value, but more importantly it avoids transactions being held open while
searching for more rows. For example, imagine you are archiving old rows from
the beginning of a very large table, with \*(L"\-\-limit\*(R" 1000 and \*(L"\-\-txnsize\*(R" 1000.
After some period of finding and archiving 1000 rows at a time, mysql-archiver
finds the last 999 rows and archives them, then executes the next \s-1SELECT\s0 to find
more rows. This scans the rest of the table, but never finds any more rows. It
has held open a transaction for a very long time, only to determine it is
finished anyway. You can use \*(L"\-\-commit\-each\*(R" to avoid this.
.IP "\-\-delayedins" 4
.IX Item "--delayedins"
Adds the \s-1DELAYED\s0 modifier to \s-1INSERT\s0 or \s-1REPLACE\s0 statements. See
for details.
.IP "\-\-dest" 4
.IX Item "--dest"
This item specifies a table into which mysql-archiver will insert rows
archived from \*(L"\-\-source\*(R". It uses the same key=val argument format as \*(L"\-\-source\*(R".
Missing values default to the same values as \*(L"\-\-source\*(R", so you don't have to
repeat options that are the same in \*(L"\-\-source\*(R" and \*(L"\-\-dest\*(R". The 'm' part is \s-1NOT\s0
defaulted from \*(L"\-\-source\*(R".
.IP "\-\-file" 4
.IX Item "--file"
Filename to write archived rows to. A subset of MySQL's \s-1\fIDATE_FORMAT\s0()\fR
formatting codes are allowed in the filename, as follows:
.Sp
.Vb 6
\& %d Day of the month, numeric (01..31)
\& %H Hour (00..23)
\& %i Minutes, numeric (00..59)
\& %m Month, numeric (01..12)
\& %s Seconds (00..59)
\& %Y Year, numeric, four digits
.Ve
.Sp
You can use the following extra format codes too:
.Sp
.Vb 2
\& %D Database name
\& %t Table name
.Ve
.Sp
Example:
.Sp
.Vb 1
\& --file '/var/log/archive/%Y-%m-%d-%D.%t'
.Ve
.Sp
The file's contents are in the same format used by \s-1SELECT\s0 \s-1INTO\s0 \s-1OUTFILE\s0, as
documented in the MySQL manual: rows terminated by newlines, columns
terminated by tabs, \s-1NULL\s0 characters are represented by \eN, and special
characters are escaped by \e. This lets you reload a file with \s-1LOAD\s0 \s-1DATA\s0
\&\s-1INFILE\s0's default settings.
.Sp
If you want a column header at the top of the file, see \*(L"\-\-header\*(R". The file is
auto-flushed by default; see \*(L"\-\-buffer\*(R".
.IP "\-\-forupdate" 4
.IX Item "--forupdate"
Adds the \s-1FOR\s0 \s-1UPDATE\s0 modifier to \s-1SELECT\s0 statements. For details, see
.
.IP "\-\-header" 4
.IX Item "--header"
Writes column names as the first line in the file given by \*(L"\-\-file\*(R". If the
file exists, does not write headers; this keeps the file loadable with \s-1LOAD\s0
\&\s-1DATA\s0 \s-1INFILE\s0 in case you append more output to it.
.IP "\-\-help" 4
.IX Item "--help"
Displays a help message.
.IP "\-\-hpselect" 4
.IX Item "--hpselect"
Adds the \s-1HIGH_PRIORITY\s0 modifier to \s-1SELECT\s0 statements. See
for details.
.IP "\-\-ignore" 4
.IX Item "--ignore"
Causes INSERTs into \*(L"\-\-dest\*(R" to be \s-1INSERT\s0 \s-1IGNORE\s0.
.IP "\-\-limit" 4
.IX Item "--limit"
Limits the number of rows returned by the \s-1SELECT\s0 statements that retrieve rows
to archive. Default is one row. It may be more efficient to increase the
limit, but be careful if you are archiving sparsely, skipping over many rows;
this can potentially cause more contention with other queries, depending on the
storage engine, transaction isolation level, and options such as \*(L"\-\-forupdate\*(R".
.IP "\-\-local" 4
.IX Item "--local"
Adds the \s-1NO_WRITE_TO_BINLOG\s0 modifier to \s-1ANALYZE\s0 and \s-1OPTIMIZE\s0 queries. See
\&\*(L"\-\-analyze\*(R" for details.
.IP "\-\-lpdel" 4
.IX Item "--lpdel"
Adds the \s-1LOW_PRIORITY\s0 modifier to \s-1DELETE\s0 statements. See
for details.
.IP "\-\-lpins" 4
.IX Item "--lpins"
Adds the \s-1LOW_PRIORITY\s0 modifier to \s-1INSERT\s0 or \s-1REPLACE\s0 statements. See
for details.
.IP "\-\-optimize" 4
.IX Item "--optimize"
Runs \s-1OPTIMIZE\s0 \s-1TABLE\s0 after finishing. See \*(L"\-\-analyze\*(R" for the option syntax and
for details on
\&\s-1OPTIMIZE\s0 \s-1TABLE\s0.
.IP "\-\-pkonly" 4
.IX Item "--pkonly"
A shortcut for specifying \*(L"\-\-columns\*(R" with the primary key columns.
.IP "\-\-progress" 4
.IX Item "--progress"
Prints current time, elapsed time, and rows archived every X rows.
.IP "\-\-purge" 4
.IX Item "--purge"
Allows archiving without a \*(L"\-\-file\*(R" or \*(L"\-\-dest\*(R" argument, which is effectively a
purge since the rows are just deleted.
.Sp
If you just want to purge rows, consider specifying the table's primary key
columns with \*(L"\-\-pkonly\*(R". This will prevent fetching all columns from the
server for no reason.
.IP "\-\-quickdel" 4
.IX Item "--quickdel"
Adds the \s-1QUICK\s0 modifier to \s-1DELETE\s0 statements. See
for details. As stated in the
documentation, in some cases it may be faster to use \s-1DELETE\s0 \s-1QUICK\s0 followed by
\&\s-1OPTIMIZE\s0 \s-1TABLE\s0. You can use \*(L"\-\-optimize\*(R" for this.
.IP "\-\-replace" 4
.IX Item "--replace"
Causes INSERTs into \*(L"\-\-dest\*(R" to be written as \s-1REPLACE\s0.
.IP "\-\-retries" 4
.IX Item "--retries"
Specifies the number of times mysql-archiver should retry when there is an
InnoDB lock wait timeout or deadlock. When retries are exhausted,
mysql-archiver will exit with an error.
.Sp
Consider carefully what you want to happen when you are archiving between a
mixture of transactional and non-transactional storage engines. The \s-1INSERT\s0 to
\&\*(L"\-\-dest\*(R" and \s-1DELETE\s0 from \*(L"\-\-source\*(R" are on separate connections, so they do not
actually participate in the same transaction even if they're on the same
server. However, mysql-archiver implements simple distributed transactions in
code, so commits and rollbacks should happen as desired across the two
connections.
.Sp
At this time I have not written any code to handle errors with transactional
storage engines other than InnoDB. Request that feature if you need it.
.IP "\-\-safeautoinc" 4
.IX Item "--safeautoinc"
Adds an extra \s-1WHERE\s0 clause to prevent MySQL Archiver from removing the newest
row when ascending a single-column \s-1AUTO_INCREMENT\s0 key. This guards against
re-using \s-1AUTO_INCREMENT\s0 values if the server restarts, and is enabled by
default.
.Sp
The extra \s-1WHERE\s0 clause uses a non-correlated subquery, which MySQL should
optimize into a constant when compiling the query, and should therefore be
very efficient. If you are worried about this, use \*(L"\-\-test\*(R" and \s-1EXPLAIN\s0 to
examine the generated queries. You should see the subquery's plan as
\&\*(L"\s-1SUBQUERY\s0... Select tables optimized away\*(R".
.IP "\-\-sentinel" 4
.IX Item "--sentinel"
The presence of the file specified by \*(L"\-\-sentinel\*(R" will cause mysql-archiver to
stop archiving and exit. The default is /tmp/mysql\-archiver\-sentinel. You
might find this handy to stop cron jobs gracefully if necessary. See also
\&\*(L"\-\-stop\*(R".
.IP "\-\-sharelock" 4
.IX Item "--sharelock"
Adds the \s-1LOCK\s0 \s-1IN\s0 \s-1SHARE\s0 \s-1MODE\s0 modifier to \s-1SELECT\s0 statements. For details, see
.
.IP "\-\-skipfkchk" 4
.IX Item "--skipfkchk"
Disables foreign key checks with \s-1SET\s0 FOREIGN_KEY_CHECKS=0.
.IP "\-\-sleep" 4
.IX Item "--sleep"
Specifies how long to sleep between \s-1SELECT\s0 statements. Default is not to
sleep at all. Transactions are \s-1NOT\s0 committed, and the \*(L"\-\-file\*(R" file is \s-1NOT\s0
flushed, before sleeping. See \*(L"\-\-txnsize\*(R" to control that.
.Sp
If \*(L"\-\-commit\-each\*(R" is specified, committing and flushing happens before sleeping.
.IP "\-\-source" 4
.IX Item "--source"
Specifies a table to archive from. This argument is specially formatted as a
key=value,key=value string. Keys are a single letter. Most options control
how mysql-archiver connects to MySQL:
.Sp
.Vb 8
\& KEY MEANING
\& === =======
\& h Connect to host
\& P Port number to use for connection
\& S Socket file to use for connection
\& u User for login if not current user
\& p Password to use when connecting
\& F Only read default options from the given file
.Ve
.Sp
The following options select a table to archive:
.Sp
.Vb 5
\& KEY MEANING
\& === =======
\& D Database to archive
\& t Table to archive
\& i Index to use
.Ve
.Sp
The following options specify pluggable actions, which an external Perl module
can provide:
.Sp
.Vb 3
\& KEY MEANING
\& === =======
\& m Package name of an external Perl module (see EXTENDING).
.Ve
.Sp
The only required part is the table; other parts may be read from various
places in the environment (such as options files). Here is an example:
.Sp
.Vb 1
\& --source h=my_server,D=my_database,t=my_tbl
.Ve
.Sp
The 'i' part deserves special mention. This tells mysql-archiver which index
it should scan to archive. This appears in a \s-1FORCE\s0 \s-1INDEX\s0 or \s-1USE\s0 \s-1INDEX\s0 hint in
the \s-1SELECT\s0 statements used to fetch archivable rows. If you don't specify
anything, mysql-archiver will try to use a \s-1PRIMARY\s0 \s-1KEY\s0 if one exists. In my
experience this usually works well, so most of the time you can probably just
omit the 'i' part.
.Sp
The index is used to optimize repeated accesses to the table; mysql-archiver
remembers the last row it retrieves from each \s-1SELECT\s0 statement, and uses it to
construct a \s-1WHERE\s0 clause, using the columns in the specified index, that should
allow MySQL to start the next \s-1SELECT\s0 where the last one ended, rather than
potentially scanning from the beginning of the table with each successive
\&\s-1SELECT\s0. If you are using external plugins, please see \*(L"\s-1EXTENDING\s0\*(R" for a
discussion of how they interact with ascending indexes.
.IP "\-\-statistics" 4
.IX Item "--statistics"
Causes mysql-archiver to collect and print timing statistics about what it does.
The statistics look like this:
.Sp
.Vb 6
\& Action Count Time Pct
\& commit 10 0.1079 88.27
\& select 5 0.0047 3.87
\& deleting 4 0.0028 2.29
\& inserting 4 0.0028 2.28
\& other 0 0.0040 3.29
.Ve
.Sp
The columns are the action, the total number of times that action was timed, the
total time it took, and the percent of the program's total runtime. The rows
are sorted in order of descending total time. The last row is the rest of the
time not explicitly attributed to anything. Actions will vary depending on
command-line options.
.Sp
This option requires the standard Time::HiRes module, which is part of core Perl
on reasonably new Perl releases.
.IP "\-\-stop" 4
.IX Item "--stop"
Causes mysql-archiver to create the sentinel file specified by \*(L"\-\-sentinel\*(R" and
exit. This should have the effect of stopping all running instances which are
watching the same sentinel file.
.IP "\-\-test" 4
.IX Item "--test"
Causes mysql-archiver to exit after printing the filename and \s-1SQL\s0 statements
it will use.
.IP "\-\-time" 4
.IX Item "--time"
Causes mysql-archiver to stop after the specified time has elapsed. The
argument can have a suffix of s, m, h, or d, indicating seconds, minutes, hours,
or days. The number is interpreted as seconds if there is no suffix.
.IP "\-\-txnsize" 4
.IX Item "--txnsize"
Specifies the size, in number of rows, of each transaction. Default is one
row. Zero disables transactions altogether. After mysql-archiver processes
this many rows, it commits both the \*(L"\-\-source\*(R" and the \*(L"\-\-dest\*(R" if given, and
flushes the file given by \*(L"\-\-file\*(R".
.Sp
This parameter is critical to performance. If you are archiving from a live
server, which for example is doing heavy \s-1OLTP\s0 work, you need to choose a good
balance between transaction size and commit overhead. Larger transactions
create the possibility of more lock contention and deadlocks, but smaller
transactions cause more frequent commit overhead, which can be significant. To
give an idea, on a small test set I worked with while writing mysql\-archiver, a
value of 500 caused archiving to take about 2 seconds per 1000 rows on an
otherwise quiet MySQL instance on my desktop machine, archiving to disk and to
another table. Disabling transactions with a value of zero, which turns on
autocommit, dropped performance to 38 seconds per thousand rows.
.Sp
If you are not archiving from or to a transactional storage engine, you may
want to disable transactions so mysql-archiver doesn't try to commit.
.IP "\-\-version" 4
.IX Item "--version"
Output version information and exit.
.IP "\-\-where" 4
.IX Item "--where"
Specifies a \s-1WHERE\s0 clause to limit which rows are archived. Do not include the
word \s-1WHERE\s0. You may need to quote the argument to prevent your shell from
interpreting it. For example:
.Sp
.Vb 1
\& --where 'ts < current_date - interval 90 day'
.Ve
.Sp
For safety, \*(L"\-\-where\*(R" is required. If you do not require a \s-1WHERE\s0 clause, use
\&\*(L"\-\-where\*(R" 1=1.
.IP "\-\-whyquit" 4
.IX Item "--whyquit"
Causes mysql-archiver to print a message if it exits for any reason other than
running out of rows to archive. This can be useful if you have a cron job with
\&\*(L"\-\-time\*(R" specified, for example, and you want to be sure mysql-archiver is
finishing before running out of time.
.SH "EXTENDING"
.IX Header "EXTENDING"
mysql-archiver is extensible by plugging in external Perl modules to handle
some logic and/or actions. You can specify a module for both the \*(L"\-\-source\*(R" and
the \*(L"\-\-dest\*(R", with the 'm' part of the specification. For example:
.PP
.Vb 1
\& --source D=test,t=test1,m=My::Module1 --dest m=My::Module2,t=test2
.Ve
.PP
This will cause mysql-archiver to load the My::Module1 and My::Module2 packages,
create instances of them, and then make calls to them during the archiving
process. The module must provide this interface:
.ie n .IP "new(dbh => $dbh\fR, db => \f(CW$db_name\fR, tbl => \f(CW$tbl_name)" 4
.el .IP "new(dbh => \f(CW$dbh\fR, db => \f(CW$db_name\fR, tbl => \f(CW$tbl_name\fR)" 4
.IX Item "new(dbh => $dbh, db => $db_name, tbl => $tbl_name)"
The plugin's constructor is passed a reference to the database handle, the
database name, and table name. The plugin is created just after mysql-archiver
opens the connection, and before it examines the table given in the arguments.
This gives the plugin a chance to create and populate temporary tables, or do
other setup work.
.IP "before_begin(cols => \e@cols)" 4
.IX Item "before_begin(cols => @cols)"
This method is called just before mysql-archiver begins iterating through rows
and archiving them, but after it does all other setup work (examining table
structures, designing \s-1SQL\s0 queries, and so on). This is the only time
mysql-archiver tells the plugin column names for the rows it will pass the
plugin while archiving.
.IP "is_archivable(row => \e@row)" 4
.IX Item "is_archivable(row => @row)"
This method is called for each row to determine whether it is archivable. This
only applies to \*(L"\-\-source\*(R". The argument is the row itself, as an arrayref. If
the method returns true, the row will be archived; otherwise it will be skipped.
.Sp
Skipping a row adds complications for non-unique indexes. Normally
mysql-archiver uses a \s-1WHERE\s0 clause designed to target the last processed row as
the place to start the scan for the next \s-1SELECT\s0 statement. If you have skipped
the row by returning false from \fIis_archivable()\fR, mysql-archiver could get into
an infinite loop because the row still exists. Therefore, when you specify a
plugin for the \*(L"\-\-source\*(R" argument, mysql-archiver will change its \s-1WHERE\s0 clause
slightly. Instead of starting at \*(L"greater than or equal to\*(R" the last processed
row, it will start \*(L"strictly greater than.\*(R" This will work fine on unique
indexes such as primary keys, but it may skip rows (leave holes) on non-unique
indexes or when ascending only the first column of an index.
.IP "before_delete(row => \e@row)" 4
.IX Item "before_delete(row => @row)"
This method is called for each row just before it is deleted. This only applies
to \*(L"\-\-source\*(R". This is a good place for you to handle dependencies, such as
deleting things that are foreign-keyed to the row you are about to delete. You
could also use this to recursively archive all dependent tables.
.IP "before_insert(row => \e@row)" 4
.IX Item "before_insert(row => @row)"
This method is called for each row just before it is inserted. This only
applies to \*(L"\-\-dest\*(R". You could use this to insert the row into multiple tables,
perhaps with an \s-1ON\s0 \s-1DUPLICATE\s0 \s-1KEY\s0 \s-1UPDATE\s0 clause to build summary tables in a data
warehouse.
.IP "\fIafter_finish()\fR" 4
.IX Item "after_finish()"
This method is called after mysql-archiver exits the archiving loop, commits all
database handles, closes \*(L"\-\-file\*(R", and prints the final statistics, but before
mysql-archiver runs \s-1ANALYZE\s0 or \s-1OPTIMIZE\s0 (see "\-\-analyze and "\-\-optimize).
.PP
If you specify a plugin for both \*(L"\-\-source\*(R" and \*(L"\-\-dest\*(R", mysql-archiver constructs,
calls \fIbefore_begin()\fR, and calls \fIafter_finish()\fR on the two plugins in the order
\&\*(L"\-\-source\*(R", \*(L"\-\-dest\*(R".
.PP
mysql-archiver assumes it controls transactions, and that the plugin will \s-1NOT\s0
commit or roll back the database handle. The database handle passed to the
plugin's constructor is the same handle mysql-archiver uses itself. Remember
that \*(L"\-\-source\*(R" and \*(L"\-\-dest\*(R" are separate handles.
.PP
A sample module might look like this:
.PP
.Vb 1
\& package My::Module;
.Ve
.PP
.Vb 4
\& sub new {
\& my ( $class, %args ) = @_;
\& return bless(\e%args, $class);
\& }
.Ve
.PP
.Vb 5
\& sub before_begin {
\& my ( $self, %args ) = @_;
\& # Save column names for later
\& $self->{cols} = $args{cols};
\& }
.Ve
.PP
.Vb 5
\& sub is_archivable {
\& my ( $self, %args ) = @_;
\& # Do some advanced logic with $args{row}
\& return 1;
\& }
.Ve
.PP
.Vb 3
\& sub before_delete {} # Take no action
\& sub before_insert {} # Take no action
\& sub after_finish {} # Take no action
.Ve
.PP
.Vb 1
\& 1;
.Ve
.SH "SYSTEM REQUIREMENTS"
.IX Header "SYSTEM REQUIREMENTS"
You need Perl, \s-1DBI\s0, DBD::mysql, and some core packages that ought to be
installed in any reasonably new version of Perl.
.SH "OUTPUT"
.IX Header "OUTPUT"
If you specify \*(L"\-\-print\*(R", the output is a header row, plus status output at
intervals. Each row in the status output lists the current date and time, how
many seconds mysql-archiver has been running, and how many rows it has
archived.
.SH "BUGS"
.IX Header "BUGS"
Please use the Sourceforge bug tracker, forums, and mailing lists to request
support or report bugs: .
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
Thanks to the following people, and apologies to anyone I've omitted:
.PP
Andrew O'Brien,
.SH "COPYRIGHT, LICENSE AND WARRANTY"
.IX Header "COPYRIGHT, LICENSE AND WARRANTY"
This program is copyright (c) 2007 Baron Schwartz. Feedback and improvements
are welcome.
.PP
\&\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 \*(L"\s-1AS\s0 \s-1IS\s0\*(R" \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0
\&\s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0
\&\s-1MERCHANTIBILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0.
.PP
This program is free software; you can redistribute it and/or modify it under
the terms of the \s-1GNU\s0 General Public License as published by the Free Software
Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.
.PP
You should have received a copy of the \s-1GNU\s0 General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA\s0.
.SH "AUTHOR"
.IX Header "AUTHOR"
Baron Schwartz
.SH "VERSION"
.IX Header "VERSION"
This manual page documents Ver 1.0.1 Distrib 1053 \f(CW$Revision:\fR 792 $.