Myloader Recovery MySQL Database Demo sample

?

Mydumper is a lightweight third-party open source tool for MySQL database backup. The backup method is a logical backup. It supports multithreading. The backup speed is much higher than the mysqldump of the original ecology and many excellent features. The recovery tool that is matched with it is myloader. Mainly used to restore the dump out of SQL in a parallel manner.

This paper mainly describes the usage of Myloader and gives a demonstration example.

    related to Mydumper
         mydumper Backup MySQL database Demo sample   
        mydumper Installation and installation failure summary   
 
1, library backup and restore
[[email  Protected] ~]# mydumper-u leshami-p xxx-b sakila-o/tmp/bak
[[email protected] ~]# MYSQL-UROBIN-PXXX &N Bsp -E "Show Databases" |grep restoredb
[[email protected] ~]# mysql-urobin-pxxx   \
> -E " CREATE table SAKILA.TB like sakila.actor;             # # #创建測试表
>      INSERT INTO SAKILA.TB select * from Sakila.actor "

# # #将备份库恢复到一个新数据库, such as RestoreDB
[Email protected] ~]# myloader-u leshami-p xxx-b restoredb-d/tmp/bak
[Email protected] ~]# mysql-urobin-pxxx-e "show Databases" |grep RestoreDB
RestoreDB

# # #恢复到原库
[Email protected] ~]# 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 ' Al Ready exists

---Add-o-parameters for overwrite recovery
[Email protected] ~]# myloader-u leshami-p xxx-o-B sakila-d/tmp/bak

2. Single-table recovery
[[email protected] ~]# mysql-urobin-pxxx-e "drop table SAKILA.TB"
[[email protected] ~]# mysql-urobin-pxxx-e "SELECT COUNT (*) from SAKILA.TB"
Warning:using a password on the command line interface can is insecure.
ERROR 1146 (42S02) at line 1:table ' sakila.tb ' doesn ' t exist

# # #直接调用备份的schema以及数据文件运行就可以
[Email protected] ~]# mysql-urobin-pxxx \
>-E "Use Sakila;
> Source/tmp/bak/sakila.tb-schema.sql
> Source/tmp/bak/sakila.tb.sql "

# # #验证结果
[[email protected] ~]# mysql-urobin-pxxx-e "SELECT COUNT (*) from SAKILA.TB"
Warning:using a password on the command line interface can is insecure.
+----------+
| COUNT (*) |
+----------+
| 200 |
+----------+

3. Instance-level backup and recovery
[Email protected] ~]# rm-rf/tmp/bak/*
[[email protected] ~]# mydumper-u leshami-p xxx--regex ' ^ (?

! (mysql|test)) ' -o/tmp/bak

# # #尝试删除部分数据库
[Email protected] ~]# mysql-urobin-pxxx \
>-E "drop database Tempdb;drop database Sakila"

# # #基于所有备份文件进行恢复
[Email protected] ~]# myloader-u leshami-p xxx-o-d/tmp/bak

4. Get Help
[Email protected] ~]# 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
Number of inserts per transaction during restore, default is 1k
-O,--overwrite-tables Drop tables if they already exist (table presence is overwrite)
-B,--database an alternative database-to-restore into
-E,--enable-binlog enable binary logging of the restore data
-H,--host the host to connect to
-U,--user Username with privileges to run the dump
-P,--password User password
-P,--port TCP/IP port to connect to
-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

Myloader Recovery MySQL Database Demo sample