MK-PARA...-RESTORE(1)

📦
version 7540 · 3 versions
, in fedora 43

💖 MK-PARA...-RESTORE(1) 💖

bestie. sit down. we need to talk about mk-parallel-restore, straight out of the User Contributed Perl Documentation. it's a real one. it's also DEPRECATED, which we'll get to immediately. 🫶

🌸 NAME 🌸

mk-parallel-restore — (DEPRECATED) Load files into MySQL in parallel.

✨ SYNOPSIS ✨

okay so first things first: this tool is deprecated because, after several complete redesigns, they concluded that Perl is the wrong technology for this task. not me saying that, the manpage is. 😭 please read RISKS before you use it, fr fr. it's still useful for some people who they know aren't depending on it in production, and that's why they're not removing it from the distribution. it's staying. it's just, like, on thin ice. 💅

usage goes a lil something like this:

Usage: mk-parallel-restore [OPTION...] [DSN] PATH [PATH...]

mk-parallel-restore loads files into MySQL in parallel to make some type of data loads faster. and in all caps, because it matters:

IT IS NOT A BACKUP TOOL! 🚨

some little examples so you can see the vibe:

mk-parallel-restore /path/to/files
mk-parallel-restore --tab /path/to/files

⚠️ RISKS ⚠️

this whole section exists to tell you about the potential risks — whether known or unknown — of using this tool. and there are two main categories, bestie:

  • the risks that come from the nature of the tool itself (like, read-only tools vs. read-write tools, yk?)
  • the risks that come from bugs 🐛

so, mk-parallel-restore is not a backup program! it is only for fast data imports, for purposes such as loading test data into a system quickly. do not use mk-parallel-restore for backups. and heads up: mk-parallel-restore inserts data unless you use the --dry-run option. so like, it's giving main character energy with actual consequences. 🙈

at the time of this release, there is a bug that prevents huge statements from being printed when an error is encountered, a bug applying --[no]foreign-key-checks when truncating tables, and a bug with LOAD DATA LOCAL. three whole bugs. iconic in a cursed way. 💀

the authoritative source for updated information is always the online issue tracking system. issues that affect this tool will be marked as such. you can see a list of such issues at:

http://www.maatkit.org/bugs/mk-parallel-restore

also peep BUGS for more info on filing bugs and getting help. 🫶

🦋 DESCRIPTION 🦋

so what is it? mk-parallel-restore is a way to load SQL or delimited-file dumps into MySQL in parallel at high speed. it's especially designed for restoring files dumped by mk-parallel-dump. it automatically detects whether a file contains SQL or delimited data from the filename extension, and then either shells out to mysql or executes LOAD DATA INFILE with the file. and on UNIX-like systems, it will even make a FIFO to decompress gzipped files for LOAD DATA INFILE. the way that it just does that. 💅

by default it discovers all files in the directory you specify on the command line. it uses the file's parent directory as the database name and the file's name (up to the first dot) as the table name. so it can deal with files named like:

dir/tbl.sql
dir/tbl.txt
dir/tbl.csv

it is also happy with files that look like this, where EXT is one of the extensions just listed:

dir/tbl.EXT.000
dir/tbl.EXT.000.gz

by default, it loads SQL files first, if they exist, then loads CSV or TXT files next, in order of the numbers in the filename extension as just shown. ✨ this makes it easy for you to reload a table's definition followed by its data, in case you dumped them into separate files (as happens with mysqldump's --tab option). see mk-parallel-dump for details on how data is dumped.

exit status: 0 if everything went well, 1 if any files failed, and any other value indicates an internal error.

📢 OUTPUT 📢

output depends on verbosity. when --dry-run is given, output includes commands that would be executed. (spooky little preview. 👀)

when --verbose is 0, there is normally no output unless there's an error. silence. peace. 🧘

when --verbose is 1, there is one line of output for the entire job, showing how many tables were processed, how many files were loaded with what status, how much time elapsed, and how much time the parallel load jobs added up to. if any files were skipped, the filenames are printed to the output.

when --verbose is 2, there's one line of output per table, showing extra data such as how many threads were running when each table finished loading:

DATABASE TABLE            FILES  TIME STATUS THREADS
sakila   language             2  0.07      0       2
sakila   film_actor           2  0.07      0       2
sakila   actor                2  0.06      0       2
sakila   payment              2  0.07      0       2
sakila   transport_backup     2  0.05      0       2
sakila   country              2  0.08      0       2
sakila   film                 2  0.05      0       2
sakila   rental               2  0.07      0       2

look at those threads. two of them. every time. we love consistency. 👑

🏎️ SPEED OF PARALLEL LOADING 🏎️

user-contributed benchmarks are welcome! 🫶 see http://www.paragon-cs.com/wordpress/?p=52 for one user's experiences.

more of this page is coming, bestie — don't go anywhere 💖

✨ OPTIONS ✨

okay bestie, this tool takes extra command-line args, so go peek at the "SYNOPSIS" and the usage info if you need the deets 💖

  • --ask-pass — prompts you for a password when it's connecting to MySQL. shy girl behavior, love that for us 🙈
  • --[no]atomic-resume — default: yes

Treats chunks as atomic when it's resuming a restore.

By default mk-parallel-restore resumes restoration from the first chunk that's missing all its rows. For dumps of transactionally-safe tables (InnoDB), it literally cannot happen that a chunk is only partially restored. So resuming from the first missing chunk is totally safe, no cap ✨

BUT. For dumps of non-transactionally safe tables, a chunk can be only partially restored, and in that case the chunk will wrongly look fully restored 😭 so you must specify --no-atomic-resume so that the partially restored chunk gets fully restored. periodt.

  • --base-dir — type: string

The directory where FIFO files get created 🎀

  • --[no]biggest-first — default: yes

Restores the biggest tables first, for maximum concurrency. main character tables go first, obviously 👑

  • --[no]bin-log — default: yes

Enables binary logging (SET SQL_LOG_BIN=1).

Restore operations are replicated by default (SQL_LOG_BIN=1) — except for --tab restores, which are not replicated by default (SQL_LOG_BIN=0). This keeps big loads from getting logged into the server's binary log, which, thank god.

The value you give on the command line overrides the defaults. So specifying --bin-log together with --tab will let the --tab restore replicate 💅

  • --bulk-insert-buffer-size — type: int

Sets bulk_insert_buffer_size before each LOAD DATA INFILE.

Has no effect without --tab. (iconic but only in specific circumstances, iykyk)

  • --charset — short form: -A; type: string; default: BINARY

Sets the connection, database, and LOAD DATA INFILE character set.

The default is BINARY, which is the safest value for LOAD DATA INFILE. Has no effect without --tab.

  • --[no]commit — default: yes

Commits after each file. slay, one at a time 🫶

  • --config — type: Array

Reads this comma-separated list of config files; if you specify it, it must be the first option on the command line. no exceptions bestie.

  • --create-databases — creates databases if they don't already exist ✨
  • --[no]create-tables — default: yes

Creates tables.

See also --[no]drop-tables.

  • --csv — files are in CSV format (which implies --tab).

Changes the --tab options so that this LOAD DATA INFILE statement gets used:

LOAD DATA INFILE <filename> INTO TABLE <table>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n';
  • --database — short form: -D; type: string

Loads all files into this database.

Overrides the database that's normally specified by the directory the files live in. Does not specify a default database for the connection.

  • --databases — short form: -d; type: hash

Restores only this comma-separated list of databases.

  • --databases-regex — type: string

Restores only databases whose names match this regex 🧚

  • --decompress — type: string; default: gzip -d -c

The command used to decompress and print .gz files to STDOUT (basically a zcat moment).

  • --defaults-file — short form: -F; type: string

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

  • --[no]disable-keys — default: yes

Executes ALTER TABLE DISABLE KEYS before each MyISAM table.

This option only works with MyISAM tables, just so u know 💜

  • --[no]drop-tables — default: yes

Executes DROP TABLE IF EXISTS before creating each table. dramatic but necessary 💀

  • --dry-run — prints commands instead of executing them. pretend mode, love that 🌸
  • --fast-index — does InnoDB plugin fast index creation by restoring secondary indexes after data.

This option only works with InnoDB tables and the InnoDB plugin.

  • --[no]fifo — default: yes

Streams files into a FIFO for --tab.

Loads compressed tab-separated files by piping them into a FIFO and using the FIFO with LOAD DATA INFILE, instead of decompressing the files on disk. Sets --umask to 0.

  • --[no]foreign-key-checks — default: yes

Sets FOREIGN_KEY_CHECKS=1 before LOAD DATA INFILE.

  • --help — shows help and exits 💖
  • --host — short form: -h; type: string

Connects to host.

  • --ignore — adds the IGNORE modifier to LOAD DATA INFILE.
  • --ignore-databases — type: Hash

Ignores this comma-separated list of databases.

  • --ignore-tables — type: Hash

Ignores this comma-separated list of table names.

Table names may be qualified with the database name.

  • --local — uses the LOCAL option for LOAD DATA INFILE.

If you enable this option, the files are read locally by the client library, not by the server.

  • --[no]lock-tables — locks tables before LOAD DATA INFILE.
  • --[no]no-auto-value-on-0 — default: yes

Sets SQL NO_AUTO_VALUE_ON_ZERO.

  • --only-empty-databases — restores only to empty databases.

By default mk-parallel-restore will restore tables into a database so long as it exists (or gets created by --create-databases). This option is a safety feature that prevents any tables from being restored into a database that already has tables — even if those tables are the exact same ones being restored. If you specify this option, every database must have zero tables.

This implicitly disables --[no]resume. --create-databases will work if the database doesn't already exist and it creates it.

The databases are checked after all filters (--databases, etc.) 👑

  • --password — short form: -p; type: string

Password to use when connecting. keep it secret keep it safe 🙈

  • --pid — type: string

Creates the given PID file. The file contains the process ID of the script. The PID file gets removed when the script exits. Before starting, the script checks if the PID file already exists. If it doesn't, then the script creates it and writes its own PID into it. If it does exist, 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's 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.

  • --port — short form: -P; type: int

Port number to use for the connection.

  • --progress — displays progress messages ✨

Progress is displayed each time a table finishes loading. Progress is calculated by measuring the size of each file to be loaded, and assuming all bytes are created equal. The output is the completed and total size, the percent completed, estimated time remaining, and estimated completion time. so satisfying to watch ngl 🍓

  • --quiet — short form: -q

Sets --verbose to 0. shhh 🤫

  • --replace — adds the REPLACE modifier to LOAD DATA INFILE.
  • --[no]resume — default: yes

Resumes the restore from a previously incomplete restore.

By default, mk-parallel-restore checks each table's chunks for existing rows and restores only from the point where a previous restore stopped. Specify --no-resume to disable restore resumption and fully restore every table.

Restore resumption does not work with tab-separated files or dumps that were not chunked. sad but true 😭

  • --set-vars — type: string; default: wait_timeout=10000

Sets these MySQL variables. Immediately after connecting to MySQL, this string gets appended to SET and executed.

  • --socket — short form: -S; type: string

Socket file to use for the connection.

  • --tab — loads tab-separated files with LOAD DATA INFILE.

This is similar to what mysqlimport does, but more flexible 💅

The following options are enabled unless they're specifically disabled on the command line:

  • --commit
  • --[no]disable-keys
  • --[no]no-auto-value-on-0

And the following options are disabled (--no-bin-log, etc.) unless they're specifically enabled on the command line:

  • --[no]bin-log
  • --[no]unique-checks
  • --[no]foreign-key-checks
  • --tables — short form: -t; type: hash

Restores only this comma-separated list of table names.

Table names may be qualified with the database name.

  • --tables-regex — type: string

Restores only tables whose names match this regex 🧚

  • --threads — type: int; default: 2

Specifies the number of parallel processes to run.

The default is 2 (this is mk-parallel-restore after all — 1 is not parallel, bestie). 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. parallelism slay 🔥

  • --truncate — runs TRUNCATE TABLE before LOAD DATA INFILE.

This will delete all rows from a table before loading the first tab-delimited file into it. clean slate energy ✨

  • --umask — type: string

Sets the program's umask to this octal value.

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

  • --[no]unique-checks — default: yes

Sets UNIQUE_CHECKS=1 before LOAD DATA INFILE.

  • --user — short form: -u; type: string

User for login if not the current user.

  • --verbose — short form: -v; cumulative: yes; default: 1

Verbosity; can specify multiple times.

Repeatedly specifying it increments the verbosity. Default is 1 if not specified. See "OUTPUT".

  • --version — shows version and exits 💖
  • --wait — short form: -w; type: time; default: 5m

Wait limit when the server is down.

If the MySQL server crashes during loading, it waits until the server comes back and then continues with the rest of the files. mk-parallel-restore will check the server every second until this time is exhausted, at which point it gives up and exits 😭

🌸 DSN OPTIONS 🌸

Okay so these DSN options are literally how you build a DSN, bestie. Each one goes in like option=value. And heads up — they're case-sensitive, so P and p are NOT the same option, do not come for me about it. There can't be any whitespace before or after the =, and if your value has whitespace in it, you gotta quote it. DSN options are comma-separated. Peep the maatkit manpage for the full tea on all of it. ✨

  • A — dsn: charset; copy: yes — the default character set, your whole vibe's encoding 💅
  • D — dsn: database; copy: yes — the default database, like, the one you're actually in
  • F — dsn: mysql_read_default_file; copy: yes — only read default options from the given file, nothing else
  • h — dsn: host; copy: yes — connect to host, the address of your girl
  • p — dsn: password; copy: yes — the password to use when connecting 🤫
  • P — dsn: port; copy: yes — the port number to use for the connection
  • S — dsn: mysql_socket; copy: yes — socket file to use for the connection
  • u — dsn: user; copy: yes — user for login if it's not the current user

🌈 DOWNLOADING 🌈

You can download Maatkit from Google Code at <http://code.google.com/p/maatkit/>, or honestly you can just grab any of the tools with a command like this:

wget http://www.maatkit.org/get/toolname
or
wget http://www.maatkit.org/trunk/toolname

Where toolname gets swapped out for the name (or even just a fragment of a name) of literally any of the Maatkit tools. Once it's downloaded, it's ready to run — no installation needed, we love a low-maintenance queen. 🫶

The first URL gets you the latest released version of the tool, and the second gets you the latest trunk code from Subversion.

🦋 ENVIRONMENT 🦋

The environment variable MKDEBUG turns on verbose debugging output in all of the Maatkit tools:

MKDEBUG=1 mk-....

🎀 SYSTEM REQUIREMENTS 🎀

You need Perl, DBI, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. Like, if your Perl is ancient that's on you bestie. 😭

💀 BUGS 💀

For a list of known bugs, go look at <http://www.maatkit.org/bugs/mk-parallel-restore>.

Please use Google Code Issues and Groups to report bugs or request support: <http://code.google.com/p/maatkit/>. You can also join #maatkit on Freenode to discuss Maatkit, come hang out. 💜

Please include the complete command-line used to reproduce the problem you're seeing, the version of all MySQL servers involved, the complete output of the tool when run with --version, and if possible, the debugging output produced by running with the MKDEBUG=1 environment variable. Give us the receipts, fr fr. 🔥

This program is copyright 2007-2011 Baron Schwartz. Feedback and improvements are welcome. 🫶

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Read that again. No warranty, periodt. 💅

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic License. On UNIX and similar systems, you can issue man perlgpl or man perlartistic to read these licenses. 📜

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

⭐ SEE ALSO ⭐

See also mk-parallel-dump.

🌸 AUTHOR 🌸

Baron Schwartz

✨ ABOUT MAATKIT ✨

This tool is part of Maatkit, a toolkit for power users of MySQL. Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the primary code contributors. Both are employed by Percona. Financial support for Maatkit development is primarily provided by Percona and its clients. Shoutout to Percona, iconic sponsorship behavior. 👑

💖 VERSION 💖

This manual page documents Ver 1.0.24 Distrib 7540 $Revision: 7477 $.

And that's the whole page, bestie — documented by perl v5.42.0 and dated 2025-07-24, so if you're reading this you're literally holding the freshest copy. Go restore some databases, you absolute main character. 🦋✨💅

📦 where this page comes from