Myloader Recovering MySQL Database example

Source: Internet
Author: User
Tags unix domain socket


Mydumper is a lightweight third-party, open Source tool for MySQL database backup, backed up in logical backup mode. It supports multi-threading, and backups are much faster than the mysqldump of the original ecosystem and many of its outstanding features. The recovery tool that is associated with it is Myloader, which is used primarily to restore a dump of SQL in a parallel way. This article mainly describes the use of Myloader and gives examples.

    related references to Mydumper
         mydumper Backup MySQL database example   
        mydumper Installation and installation failure summary   

[[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;             # # #创建测试表
>      INSERT INTO SAKILA.TB select * from "

# # #将备份库恢复到一个新数据库, 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

# # #恢复到原库
[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

---to increase the-O parameter 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
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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Myloader Recovering MySQL Database example

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.