MyDumper

https://mydumper.github.io/mydumper/docs/html/
Admin
MyDumper is a MySQL logical backup tool that parallelizes database backups and restores using...
Tokens:23,360
Snippets:301
Trust Score:8.3
Update:1 week ago
Show doc for...
Tokens:
Raw
### Install mydumper-lint via go install

Source: https://mydumper.github.io/mydumper/docs/html/mydumper_lint.html

Install mydumper-lint using go install. Alternatively, download a release from the project.

```shell
go install github.com/tomsihap/mydumper-lint/cmd/mydumper-lint@latest
```

--------------------------------

### Install MySQL dev library on OpenSUSE

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MySQL development library on OpenSUSE.

```shell
zypper install libmysqlclient-devel

```

--------------------------------

### Install MyDumper from FreeBSD ports

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MyDumper from FreeBSD ports.

```shell
cd /usr/ports/databases/mydumper && make install

```

--------------------------------

### Install MyDumper on FreeBSD with pkg

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MyDumper on FreeBSD using the package manager.

```shell
pkg install mydumper

```

--------------------------------

### Build and install MyDumper with CMake

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Builds and installs MyDumper using CMake. Optionally pass parameters like -DMYSQL_CONFIG or -DCMAKE_INSTALL_PREFIX. Ensure pkg-config, mysql_config, and pcre-config are in $PATH. Binlog dump is disabled by default; add -DWITH_BINLOG=ON to enable. For MySQL libs < 5.7, add -DWITH_SSL=OFF.

```shell
cmake .
make
sudo make install

```

--------------------------------

### Install MyDumper on macOS with Homebrew

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MyDumper on macOS using Homebrew.

```shell
brew install mydumper

```

--------------------------------

### Install MySQL/Percona/MariaDB dev libraries on Fedora/RedHat/CentOS

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MySQL, Percona, or MariaDB development libraries on Fedora/RedHat/CentOS. Choose one vendor.

```shell
yum install -y mysql-devel
yum install -y Percona-Server-devel-57
yum install -y mariadb-devel

```

--------------------------------

### Install MyDumper development dependencies on OpenSUSE

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs development dependencies for compiling MyDumper on OpenSUSE.

```shell
zypper install glib2-devel pcre-devel

```

--------------------------------

### List encrypted backup files

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Example output showing encrypted backup files in the dump directory.

```bash
# ls dump/
metadata                               sbtest_proxysql.sbtest1.00001.sql.enc
sbtest_direct-schema-create.sql.enc    sbtest_proxysql.sbtest1.00002.sql.enc
sbtest_proxysql.sbtest1.00000.sql.enc  sbtest_proxysql.sbtest1-schema.sql.enc

```

--------------------------------

### Install MySQL/Percona/MariaDB dev libraries on Ubuntu/Debian

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MySQL, Percona, or MariaDB development libraries on Ubuntu/Debian. Choose one vendor.

```shell
apt-get install libmysqlclient-dev
apt-get install libperconaserverclient20-dev
apt-get install libmariadbclient-dev

```

--------------------------------

### constant examples for email and tax

Source: https://mydumper.github.io/mydumper/docs/html/masquerade.html

Shows how to use constant to replace column content with a calculated value based on other columns.

```plaintext
`email`=constant concat(first_name,concat(".",concat(last_name,"@sakilacustomer.org")))
`tax`=constant price*21/100
```

--------------------------------

### mydumper with bzip2 compression

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Example command to compress backups with bzip2 using mydumper.

```bash
mydumper -o data --clear -T sakila.film \
         --exec-per-thread="/usr/bin/bzip2" \
         --exec-per-thread-extension=".bz2"

```

--------------------------------

### Install MyDumper development dependencies on Fedora/RedHat/CentOS

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs development dependencies for compiling MyDumper on Fedora/RedHat/CentOS.

```shell
yum install -y cmake gcc gcc-c++ git make
yum install glib2-devel pcre-devel openssl-devel

```

--------------------------------

### Install MyDumper development dependencies on Ubuntu/Debian

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs development dependencies for compiling MyDumper on Ubuntu/Debian.

```shell
apt-get install cmake g++ git
apt-get install libglib2.0-dev libpcre3-dev libssl-dev

```

--------------------------------

### myloader with bzip2 decompression

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Example command to decompress bzip2 backups with myloader.

```bash
myloader -d data -o \
         --exec-per-thread="/usr/bin/bzip2 -d" \
         --exec-per-thread-extension=".bz2"

```

--------------------------------

### Backup files produced by bzip2 compression

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Example output listing of backup files after using bzip2 compression.

```bash
-rw-r--r-- 1 circleci circleci   520 Jul 16 13:59 metadata
-rw-r----- 1 circleci circleci 11500 Jul 16 13:59 sakila.film.00000.sql.bz2
-rw-r----- 1 circleci circleci 11360 Jul 16 13:59 sakila.film.00001.sql.bz2
-rw-r----- 1 circleci circleci   758 Jul 16 13:59 sakila.film-schema.sql.bz2
-rw-r----- 1 circleci circleci   322 Jul 16 13:59 sakila-schema-create.sql.bz2

```

--------------------------------

### Install MariaDB dev library on macOS with MacPorts

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs MariaDB development library on macOS (<= 10.13 High Sierra < 13 Ventura) using MacPorts.

```shell
sudo port install mariadb-10.11
sudo port select mysql

```

--------------------------------

### random_format examples with file, number, and regex tags

Source: https://mydumper.github.io/mydumper/docs/html/masquerade.html

Shows how to use random_format with file, number, and regex tags. The size of number or string tags is specified after the tag name. Other characters between tags are preserved.

```plaintext
`pad`=random_format <file words_alpha.txt.100>-<file words_alpha.txt.100>-<file words_alpha.txt.100>-<file words_alpha.txt.100>-<file words_alpha.txt.100>
`pad`=random_format <number 9>-<number 9>-<number 9>-<number 9>-<number 9>
`phone_number`=random_format <regex '.{4}$'><number 4>
```

--------------------------------

### Add MyDumper APT repository signing key

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Downloads and installs the MyDumper GPG key to /etc/apt/keyrings/mydumper.asc. For releases older than Debian 12 and Ubuntu 22.04, the /etc/apt/keyrings directory must be created with permissions 0755 before running this command.

```bash
wget -qO- 'https://keyserver.ubuntu.com/pks/lookup?op=get&search=0x1D357EA7D10C9320371BDD0279EA15C0E82E34BA&exact=on' | sudo tee /etc/apt/keyrings/mydumper.asc

```

--------------------------------

### Import MyDumper GPG key

Source: https://mydumper.github.io/mydumper/docs/html/YUM.html

Imports the MyDumper GPG key from the Ubuntu keyserver. Required before installing the repository. The key ID is 0x79EA15C0E82E34BA.

```bash
wget -O GPG-KEY-MyDumper "https://keyserver.ubuntu.com/pks/lookup?op=get&search=0x79EA15C0E82E34BA"
rpm --import GPG-KEY-MyDumper

```

--------------------------------

### apply examples for concat and date_add

Source: https://mydumper.github.io/mydumper/docs/html/masquerade.html

Shows how to use apply to apply a function to a column whose value will be shown. This is not a data masking function; the content is written anyway. Test with sakila.film table.

```plaintext
`title`=apply 'concat(' '," - part 2")'
`last_update`=apply 'date_add(' ', interval 1 year)'
```

--------------------------------

### Identify encrypted file type

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Example output of the file command showing the encrypted file is OpenSSL encrypted data.

```bash
# file dump/sbtest_direct-schema-create.sql.enc
dump/sbtest_direct-schema-create.sql.enc: openssl enc\'d data with salted password

```

--------------------------------

### Exclude all databases starting with test

Source: https://mydumper.github.io/mydumper/docs/html/regex.html

Uses a negative lookahead to exclude any database whose name starts with 'test'.

```bash
# mydumper --regex '^(?!(test))'

```

--------------------------------

### Run mydumper and myloader with defaults-file

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Runs mydumper and myloader with the Homebrew configuration file, which may be located in /usr/local/etc or /opt/homebrew/etc.

```shell
mydumper --defaults-file=/opt/homebrew/etc/mydumper.cnf
myloader --defaults-file=/opt/homebrew/etc/mydumper.cnf

```

--------------------------------

### Run mydumper in daemon mode with snapshot interval and log file

Source: https://mydumper.github.io/mydumper/docs/html/daemon.html

Use the --daemon option to enable continuous backup mode. The --snapshot-interval=30 creates a snapshot every 30 minutes, and --logfile=dump.log writes output to a log file. Daemon mode alternates between directories 0 and 1, updating the last_dump symlink after each snapshot.

```bash
# mydumper --daemon --snapshot-interval=30 --logfile=dump.log

```

--------------------------------

### Install MyDumper development dependencies on macOS with MacPorts

Source: https://mydumper.github.io/mydumper/docs/html/installing.html

Installs development dependencies for compiling MyDumper on macOS (<= 10.13 High Sierra < 13 Ventura) using MacPorts.

```shell
sudo port install cmake pkgconfig
sudo port install glib2 pcre

```

--------------------------------

### Options to set for custom compression

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

To change the executable location, parameters, or use different compression software, both options must be set.

```bash
--exec-per-thread
--exec-per-thread-extension

```

--------------------------------

### Ubuntu resolute APT source list entry

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Adds the MyDumper APT repository for Ubuntu resolute. The main line is active; the testing line is commented out.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu resolute main
#deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu resolute testing

```

--------------------------------

### Dump only mysql and test databases with schema-create.sql

Source: https://mydumper.github.io/mydumper/docs/html/regex.html

Dumps only the 'mysql' and 'test' databases, including the -schema-create.sql files.

```bash
# mydumper --regex '^(mysql|test)(\.|$)'

```

--------------------------------

### mydumper with OpenSSL encryption

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Run mydumper with the two extra flags to encrypt each backup file using OpenSSL.

```bash
# mydumper \
      --defaults-extra-file=.my.cnf \
      --verbose 3 \
      --exec-per-thread "openssl enc -aes-256-cbc -salt -pbkdf2 -pass file:${ENCKEY}" \
      --exec-per-thread-extension=.enc \
      -o dump

```

--------------------------------

### Configuration file sections for mydumper and myloader

Source: https://mydumper.github.io/mydumper/docs/html/usage.html

Shows the section names in /etc/mydumper.cnf where defaults for mydumper, myloader, and their session variables are kept. Use this as a template for the configuration file structure.

```ini
[mydumper]
[mydumper_session_variables]
[myloader]
[myloader_session_variables]

```

--------------------------------

### Ansible APT source list entry with dynamic variables

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Uses Ansible variables for distribution and release. The main line uses /etc/apt/keyrings/mydumper.asc; the commented testing line uses /etc/apt/trusted.gpg.d/mydumper.asc.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/{{ ansible_distribution | lower }} {{ ansible_distribution_release }} main
#deb [signed-by=/etc/apt/trusted.gpg.d/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/{{ ansible_distribution | lower }} {{ ansible_distribution_release }} testing

```

--------------------------------

### Configure MyDumper YUM repository

Source: https://mydumper.github.io/mydumper/docs/html/YUM.html

Creates the YUM repository configuration file /etc/yum.repos.d/mydumper.repo. Enables the stable mydumper repository and disables the mydumper-testing repository by default.

```bash
echo "[mydumper]
name=MyDumper
baseurl=https://mydumper.github.io/mydumper/repo/yum/$releasever
enabled=1
gpgcheck=1

[mydumper-testing]
name=MyDumper
baseurl=https://mydumper.github.io/mydumper/repo/yum/$releasever/testing/
enabled=0
gpgcheck=1" > /etc/yum.repos.d/mydumper.repo

```

--------------------------------

### Set sync thread lock mode to NO_LOCK

Source: https://mydumper.github.io/mydumper/docs/html/locks.html

Disables FLUSH TABLE WITH READ LOCK and LOCK TABLE, which may cause inconsistent backups. Modified for Percona Server to retry up to 3 times after START TRANSACTION WITH CONSISTENT SNAPSHOT.

```bash
--sync-thread-lock-mode=NO_LOCK

```

--------------------------------

### Ubuntu focal APT source list entry

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Adds the MyDumper APT repository for Ubuntu focal. The main line is active; the testing line is commented out.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu focal main
#deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu focal testing

```

--------------------------------

### Dump only mysql and test databases without schema-create.sql

Source: https://mydumper.github.io/mydumper/docs/html/regex.html

Dumps only the 'mysql' and 'test' databases, but excludes the -schema-create.sql files.

```bash
# mydumper --regex '^(mysql\.|test\.)'

```

--------------------------------

### Ubuntu jammy APT source list entry

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Adds the MyDumper APT repository for Ubuntu jammy. The main line is active; the testing line is commented out.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu jammy main
#deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu jammy testing

```

--------------------------------

### Ubuntu noble APT source list entry

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Adds the MyDumper APT repository for Ubuntu noble. The main line is active; the testing line is commented out.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu noble main
#deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/ubuntu noble testing

```

--------------------------------

### Generate random backup key for encryption

Source: https://mydumper.github.io/mydumper/docs/html/exec_per_thread.html

Commands to create a random key for encrypting backups. A different key is used for each backup.

```bash
# NOW=`date +"%Y%m%d"`
# ENCKEY=$(mktemp)
# openssl rand -base64 32 > ${ENCKEY}

```

--------------------------------

### Grant all necessary privileges to MyDumper backup user

Source: https://mydumper.github.io/mydumper/docs/html/requirements.html

Simplest privilege setting for MyDumper: grants SHOW VIEW, EVENT, TRIGGER, and SELECT on all databases.

```sql
GRANT SHOW VIEW, EVENT, TRIGGER, SELECT ON *.* TO 'backup_user'@'localhost';

```

--------------------------------

### Debian trixie APT source list entry

Source: https://mydumper.github.io/mydumper/docs/html/APT.html

Adds the MyDumper APT repository for Debian trixie. The main line is active; the testing line is commented out.

```deb
deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/debian trixie main
#deb [signed-by=/etc/apt/keyrings/mydumper.asc] https://mydumper.github.io/mydumper/repo/apt/debian trixie testing

```

--------------------------------

### mydumper-lint check --mydumper-version v1.0.5-1

Source: https://mydumper.github.io/mydumper/docs/html/mydumper_lint.html

Run the same lint check from a terminal using the mydumper-lint command-line tool. Use --fix to apply safe fixes and --diff to show them.

```shell
mydumper-lint check --mydumper-version v1.0.5-1 backup.cnf
```

--------------------------------

### Grant import privileges for myloader on new_sakila

Source: https://mydumper.github.io/mydumper/docs/html/requirements.html

Grants the privileges needed to successfully import a database into new_sakila using myloader.

```sql
GRANT INSERT, CREATE, DROP, REFERENCES, ALTER, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON `new_sakila`.* TO 'backup_user'@'localhost';

```

--------------------------------

### Create MySQL backup user for MyDumper

Source: https://mydumper.github.io/mydumper/docs/html/requirements.html

Creates a dedicated MySQL user for MyDumper backups. Replace 'StrongPassword' with a strong password.

```sql
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword';

```

--------------------------------

### Line of spaces in mydumper config

Source: https://mydumper.github.io/mydumper/docs/html/mydumper_lint.html

A line with only spaces is displayed in the editor. mydumper's pre-processor turns it into `·= 1`, a key/value pair with an empty key, which GLib rejects, causing mydumper to ignore the entire configuration file and run with defaults (exit 0).

```text

1
2
3
4
5
6
7
8
9
10


```

--------------------------------

### Restore a dump with myloader

Source: https://mydumper.github.io/mydumper/docs/html/examples.html

Restores a dump from a directory, overwriting existing tables. Requires a user with restore privileges.

```bash
# myloader --directory=export-20110614-094953 --overwrite-tables --user=root

```
Tokens:
Raw