mk-parallel-dump

mk-parallel-dump --tab --base-dir /tmp/dumps

mk-parallel-dump --databases db --tables foo \     --chunk-size 10000 --threads 8

mk-parallel-dump --chunk-size 10k

mk-parallel-dump

mk-parallel-dump --base-dir /path/to/elsewhere

mk-parallel-dump --tab

#!/bin/sh   CNT=`ls | grep -c old`;   if [ -d default ]; then mv default default.old.$CNT;   mk-parallel-dump   if [ $? != 0 ]   then      echo "There were errors, not purging old sets."   else      echo "No errors during dump, purging old sets."      rm -rf default.old.*   fi

• A table that is dumped in chunks can be dumped in many threads simultaneously.
• Dumping in chunks creates small files, which can be imported more efficiently and safely. Importing a single huge file can be a lot of extra work for transactional storage engines like InnoDB. A huge file can create a huge rollback segment in your tablespace. If the import fails, the rollback can take a very long time.

CHUNK  TIME  EXIT  SKIPPED DATABASE.TABLE      db  0.28     0        0 sakila             all  0.28     0        0 -

The CHUNK column signifies what kind of information is in the line:

Value  Meaning  =====  ========================================================  db     This line contains summary information about a database.  tbl    This line contains summary information about a table.  
       
          This line contains information about the Nth chunk of a         table.
       

The types of lines you'll see depend on the   option and   options. mk-parallel-dump treats everything as a chunk. If you don't specify  , then each table is one big chunk and each database is a chunk (of all its tables). Thus, there is output for numbered table chunks ( ), table chunks, and database chunks.

The TIME column shows the wallclock time elapsed while the chunk was dumped. If CHUNK is "db" or "tbl", this time is the total wallclock time elapsed for the database or table.

The EXIT column shows the exit status of the chunk. Any non-zero exit signifies an error. The cause of errors are usually printed to STDERR.

The SKIPPED column shows how many chunks were skipped. These are not errors. Chunks are skipped if the dump can be resumed. See  .

The DATABASE.TABLE column shows to which table the chunk belongs. For "db" chunks, this shows just the database. Chunks are printed when they complete, and this is often out of the order you'd expect. For example, you might see a chunk for db1.table_1, then a chunk for db2.table_2, then another chunk for db1.table_1, then the "db" chunk summary for db2.

If you specify  , then the tool adds a PROGRESS column after DATABASE.TABLE, which contains text similar to the following:

PROGRESS  4.10M/4.10M 100.00% ETA ... 00:00 (2009-10-16T15:37:49)  done at 2009-10-16T15:37:48, 1 databases, 16 tables, 16 chunks

This column shows information about the amount of data dumped so far, the amount of data left to dump, and an ETA ("estimated time of arrival"). The ETA is a best-effort prediction when everything will be finished dumping. Sometimes the ETA is very accurate, but at other times it can be significantly wrong.

CHUNK  TIME  EXIT  SKIPPED DATABASE.TABLE     tbl  0.07     0        0 sakila.payment     tbl  0.08     0        0 sakila.rental      tbl  0.03     0        0 sakila.film         db  0.28     0        0 sakila             all  0.28     0        0 -

CHUNK  TIME  EXIT  SKIPPED DATABASE.TABLE             0  0.03     0        0 sakila.payment             1  0.03     0        0 sakila.payment          tbl  0.10     0        0 sakila.payment      0  0.01     0        1 sakila.store             tbl  0.02     0        1 sakila.store              db  0.20     0        1 sakila                   all  0.21     0        1 -

Prompt for a password when connecting to MySQL.

type: string

The base directory in which files will be stored.

The default is the current working directory. Each database gets its own directory under the base directory. So if the base directory is 

/tmp and database 

foo is dumped, then the directory

/tmp/foo is created which contains all the table dump files for 

foo.

default: yes

Process tables in descending order of size (biggest to smallest).

This strategy gives better parallelization. Suppose there are 8 threads and the last table is huge. We will finish everything else and then be running single-threaded while that one finishes. If that one runs first, then we will have the max number of threads running at a time for as long as possible.

default: yes

Dump the master/slave position.

Dump binary log positions from both 

SHOW MASTER STATUS and 

SHOW SLAVE STATUS, whichever can be retrieved from the server. The data is dumped to a file named 

00_master_data.sql in the  .

The file also contains details of each table dumped, including the WHERE clauses used to dump it in chunks.

short form: -A; type: string

Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.

type: string

Number of rows or data size to dump per file.

Specifies that the table should be dumped in segments of approximately the size given. The syntax is either a plain integer, which is interpreted as a number of rows per chunk, or an integer with a suffix of G, M, or k, which is interpreted as the size of the data to be dumped in each chunk. See   for more details.

Fetch and buffer results in memory on client.

By default this option is not enabled because it causes data to be completely fetched from the server then buffered in-memory on the client. For large dumps this can require a lot of memory

Instead, the default (when this option is not specified) is to fetch and dump rows one-by-one from the server. This requires a lot less memory on the client but can keep the tables on the server locked longer.

Use this option only if you're sure that the data being dumped is relatively small and the client has sufficient memory. Remember that, if this option is specified, all   will buffer their results in-memory, so memory consumption can increase by a factor of N  .

type: Array

Read this comma-separated list of config files; if specified, this must be the first option on the command line.

Do   dump in CSV format (implies  ).

Changes   options so the dump file is in comma-separated values (CSV) format. The SELECT INTO OUTFILE statement looks like the following, and can be re-loaded with the same options:

SELECT * INTO OUTFILE %D.%N.%6C.txt   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'   LINES TERMINATED BY '\n' FROM %D.%N;

short form: -d; type: hash

Dump only this comma-separated list of databases.

type: string

Dump only databases whose names match this Perl regex.

short form: -F; type: string

Only read mysql options from the given file. You must give an absolute pathname.

Print commands instead of executing them.

short form: -e; type: hash

Dump only tables that use this comma-separated list of storage engines.

Use 

FLUSH TABLES WITH READ LOCK.

This is enabled by default. The lock is taken once, at the beginning of the whole process and is released after all tables have been dumped. If you want to lock only the tables you're dumping, use  .

Execute 

FLUSH LOGS when getting binlog positions.

This option is NOT enabled by default because it causes the MySQL server to rotate its error log, potentially overwriting error messages.

default: yes

Compress (gzip) SQL dump files; does not work with  .

The IO::Compress::Gzip Perl module is used to compress SQL dump files as they are written to disk. The resulting dump files have a 

.gz extension, like 

table.000000.sql.gz. They can be uncompressed with 

gzip. 

mk-parallel-restore will automatically uncompress them, too, when restoring.

This option does not work with   because the MySQL server writes the tab dump files directly using 

SELECT INTO OUTFILE.

Show help and exit.

short form: -h; type: string

Connect to host.

type: Hash

Ignore this comma-separated list of databases.

type: string

Ignore databases whose names match this Perl regex.

type: Hash; default: FEDERATED,MRG_MyISAM

Do not dump tables that use this comma-separated list of storage engines.

The schema file will be dumped as usual. This prevents dumping data for Federated tables and Merge tables.

type: Hash

Ignore this comma-separated list of table names.

Table names may be qualified with the database name.

type: string

Ignore tables whose names match the Perl regex.

Use 

LOCK TABLES (disables  ).

Disables   (unless it was explicitly set) and locks tables with 

LOCK TABLES READ. The lock is taken and released for every table as it is dumped.

Dump float types with extra precision for lossless restore (requires  ).

Wraps these types with a call to 

FORMAT() with 17 digits of precision. According to the comments in Google's patches, this will give lossless dumping and reloading in most cases. (I shamelessly stole this technique from them. I don't know enough about floating-point math to have an opinion).

This works only with  .

short form: -p; type: string

Password to use when connecting.

type: string

Create the given PID file. The file contains the process ID of the script. The PID file is removed when the script exits. Before starting, the script checks if the PID file already exists. If it does not, then the script creates and writes its own PID to it. If it does, then the script checks the following: if the file contains a PID and a process is running with that PID, then the script dies; or, if there is no process running with that PID, then the script overwrites the file with its own PID and starts; else, if the file contains no PID, then the script dies.

short form: -P; type: int

Port number to use for connection.

Display progress reports.

Progress is displayed each time a table or chunk of a table finishes dumping. Progress is calculated by measuring the average data size of each full chunk and assuming all bytes are created equal. The output is the completed and total bytes, the percent completed, estimated time remaining, and estimated completion time. For example:

40.72k/112.00k  36.36% ETA 00:00 (2009-10-27T19:17:53)

If   is not specified then each table is effectively one big chunk and the progress reports are pretty accurate. When   is specified the progress reports can be skewed because of averaging.

Progress reports are inaccurate when a dump is resumed. This is known issue and will be fixed in a later release.

short form: -q

Quiet output; disables  .

default: yes

Resume dumps.

type: string; default: wait_timeout=10000

Set these MySQL variables. Immediately after connecting to MySQL, this string will be appended to SET and executed.

short form: -S; type: string

Socket file to use for connection.

Issue 

STOP SLAVE on server before dumping data.

This ensures that the data is not changing during the dump. Issues 

START SLAVE after the dump is complete.

If the slave is not running, throws an error and exits. This is to prevent possibly bad things from happening if the slave is not running because of a problem, or because someone intentionally stopped the slave for maintenance or some other purpose.

Dump tab-separated (sets   0).

Dump via 

SELECT INTO OUTFILE, which is similar to what 

mysqldump does with the   option, but you're not constrained to a single database at a time.

Before you use this option, make sure you know what 

SELECT INTO OUTFILE does! I recommend using it only if you're running mk-parallel-dump on the same machine as the MySQL server, but there is no protection if you don't.

This option sets   to zero so auto-created directories are writable by the MySQL server.

short form: -t; type: hash

Dump only this comma-separated list of table names.

Table names may be qualified with the database name.

type: string

Dump only tables whose names match this Perl regex.

type: int; default: 2

Number of threads to dump concurrently.

Specifies the number of parallel processes to run. The default is 2 (this is mk-parallel-dump, after all -- 1 is not parallel). On GNU/Linux machines, the default is the number of times 'processor' appears in 

/proc/cpuinfo. On Windows, the default is read from the environment. In any case, the default is at least 2, even when there's only a single processor.

default: yes

Enable TIMESTAMP columns to be dumped and reloaded between different time zones. mk-parallel-dump sets its connection time zone to UTC and adds 

SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change. This option also protects against changes due to daylight saving time.

This option is identical to 

mysqldump --tz-utc. In fact, the above text was copied from mysqldump's man page.

type: string

Set the program's   to this octal value.

This is useful when you want created files and directories to be readable or writable by other users (for example, the MySQL server itself).

short form: -u; type: string

User for login if not current user.

short form: -v; cumulative: yes

Be verbose; can specify multiple times.

See  .

Show version and exit.

short form: -w; type: time; default: 5m

Wait limit when the server is down.

If the MySQL server crashes during dumping, waits until the server comes back and then continues with the rest of the tables. 

mk-parallel-dump will check the server every second until this time is exhausted, at which point it will give up and exit.

This implements Peter Zaitsev's "safe dump" request: sometimes a dump on a server that has corrupt data will kill the server. mk-parallel-dump will wait for the server to restart, then keep going. It's hard to say which table killed the server, so no tables will be retried. Tables that were being concurrently dumped when the crash happened will not be retried. No additional locks will be taken after the server restarts; it's assumed this behavior is useful only on a server you're not trying to dump while it's in production.

default: yes

Add a chunk for rows with zero or zero-equivalent values. The only has an effect when   is specified. The purpose of the zero chunk is to capture a potentially large number of zero values that would imbalance the size of the first chunk. For example, if a lot of negative numbers were inserted into an unsigned integer column causing them to be stored as zeros, then these zero values are captured by the zero chunk instead of the first chunk and all its non-zero values.

• dsn: charset; copy: yes
  Default character set.
• dsn: database; copy: yes
  Default database.
• dsn: mysql_read_default_file; copy: yes
  Only read default options from the given file
• dsn: host; copy: yes
  Connect to host.
• dsn: password; copy: yes
  Password to use when connecting.
• dsn: port; copy: yes
  Port number to use for connection.
• dsn: mysql_socket; copy: yes
  Socket file to use for connection.
• dsn: user; copy: yes
  User for login if not current user.

wget    or   wget

MKDEBUG=1 mk-....

VERSION