myloader恢复mysql数据库示例

╰+哭是因爲堅強的太久メ 2022-08-02 04:55 210阅读 0赞

mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的mysqldump以及众多优异特性。与其相配套的恢复工具则是myloader，主要用于将dump出来的sql以并行的方式进行恢复。本文主要描述myloader的使用方法并给出示例。

有关mydumper的相关参考  
        [mydumper备份mysql数据库示例][mydumper_mysql]    
        [mydumper安装及安装故障汇总][mydumper]    
   
1、单库的备份与恢复  
\[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;             \#\#\#创建测试表  
>      insert into sakila.tb select \* from sakila.actor"

\#\#\#将备份库恢复到一个新数据库，如restoredb  
\[root@app ~\]\# myloader  -u leshami -p xxx  -B restoredb -d /tmp/bak  
\[root@app ~\]\# mysql -urobin -pxxx   -e "show databases"|grep restoredb  
restoredb

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

\---增加-o参数进行覆盖恢复  
\[root@app ~\]\# myloader  -u leshami -p xxx   -o -B sakila -d /tmp/bak

2、单表恢复  
\[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

\#\#\#直接调用备份的schema以及数据文件执行即可  
\[root@app ~\]\# mysql -urobin -pxxx   \\  
>  -e "use sakila;  
>      source /tmp/bak/sakila.tb-schema.sql  
>      source /tmp/bak/sakila.tb.sql"

\#\#\#验证结果  
\[root@app ~\]\# mysql -urobin -pxxx   -e "select count(\*) from sakila.tb"  
Warning: Using a password on the command line interface can be insecure.  
\+----------+  
| count(\*) |  
\+----------+  
|      200 |  
\+----------+

3、实例级别的备份与恢复  
\[root@app ~\]\# rm -rf /tmp/bak/\*  
\[root@app ~\]\# mydumper -u leshami -p xxx   --regex '^(?!(mysql|test))' -o /tmp/bak

\#\#\#尝试删除部分数据库  
\[root@app ~\]\# mysql -urobin -pxxx   \\  
>  -e "drop database tempdb;drop database sakila"

\#\#\#基于全部备份文件进行恢复  
\[root@app ~\]\# myloader  -u leshami -p xxx   -o -d /tmp/bak

4、获取帮助  
\[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  
                   还原期间每个事务insert的数目，缺省是1k  
  -o, --overwrite-tables            Drop tables if they already exist(表存在即覆盖)  
  -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

[mydumper_mysql]: http://blog.csdn.net/leshami/article/details/46815553
[mydumper]: http://blog.csdn.net/leshami/article/details/46740831