Example of myloader restoring MySQL database

Example of myloader restoring MySQL database

Mydumper is a lightweight third-party open-source tool for mysql database backup. The backup mode is logical backup. It supports multithreading, And the backup speed is much higher than the original mysqldump and many outstanding features. The corresponding recovery tool is myloader, which is mainly used to restore dump SQL statements in parallel. This document describes how to use myloader and provides an example.

1. Single-database backup and recovery
[Root @ app ~] # Mydumper-u leshami-p xxx-B sakila-o/tmp/bak
[Root @ app ~] # Mysql-urobin-pxxx-e "show databases" | grep restoredb
[Root @ app ~] # Mysql-urobin-pxxx \
>-E "create table sakila. tb like sakila. actor; ### create a test table
> Insert into sakila. tb select * from sakila. actor"

### Restore the backup database to a new database, such as restoredb
[Root @ app ~] # Myloader-u leshami-p xxx-B restoredb-d/tmp/bak
[Root @ app ~] # Mysql-urobin-pxxx-e "show databases" | grep restoredb
Restoredb

### Restore to the original database
[Root @ app ~] # Myloader-u leshami-p xxx-B sakila-d/tmp/bak
** (Myloader: 3642): CRITICAL **: Error restoring sakila. category from file sakila. category-schema. SQL: Table 'category 'already exists

--- Add the-o parameter for overwrite recovery
[Root @ app ~] # Myloader-u leshami-p xxx-o-B sakila-d/tmp/bak

2. Single Table recovery
[Root @ app ~] # Mysql-urobin-pxxx-e "drop table sakila. tb"
[Root @ app ~] # Mysql-urobin-pxxx-e "select count (*) from sakila. tb"
Warning: Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'sakila. tb' doesn' t exist

### Directly call the backup schema and data file for execution
[Root @ app ~] # Mysql-urobin-pxxx \
>-E "use sakila;
> Source/tmp/bak/sakila. tb-schema. SQL
> Source/tmp/bak/sakila. tb. SQL"

### Verification results
[Root @ app ~] # Mysql-urobin-pxxx-e "select count (*) from sakila. tb"
Warning: Using a password on the command line interface can be insecure.
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 200 |
+ ---------- +

3. instance-level backup and recovery
[Root @ app ~] # Rm-rf/tmp/bak /*
[Root @ app ~] # Mydumper-u leshami-p xxx -- regex '^ (?! (Mysql | test) '-o/tmp/bak

### Try to delete some databases
[Root @ app ~] # Mysql-urobin-pxxx \
>-E "drop database tempdb; drop database sakila"

### Restore based on all backup files
[Root @ app ~] # Myloader-u leshami-p xxx-o-d/tmp/bak

4. Get help
[Root @ app ~] # Myloader -- help
Usage:
Myloader [OPTION...] multi-threaded MySQL loader

Help Options:
-?, -- Help Show help options

Application Options:
-D, -- directory Directory of the dump to import
-Q, -- queries-per-transaction Number of queries per transaction, default 1000
The number of inserts per transaction during restoration. The default value is 1 k.
-O, -- overwrite-tables Drop tables if they already exist (the table exists and overwrites it)
-B, -- database An alternative database to restore
-E, -- enable-binlog Enable binary logging of the restore data
-H, -- host The host to connect
-U, -- user Username with privileges to run the dump
-P, -- password User password
-P, -- port TCP/IP port to connect
-S, -- socket UNIX domain socket file to use for connection
-T, -- threads Number of threads to use, default 4
-C, -- compress-protocol Use compression on the MySQL connection
-V, -- version Show the program version and exit
-V, -- verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

This article permanently updates the link address: