How to use mysqlpump for faster MySQL logical backup ?

MySQL 5.7.8 introduced much improved version of mysqldump, It’s called “mysqlpump”, mysqlpump is much faster than mysqldump with parallel threads capabilities, There are many other compelling reasons for choosing mysqlpump over mysqldump, This blog is about how mysqlpump can be used for good. mysqlpump is relatively a new utility of MySQL and we are confident that Oracle MySQL will invest more to make mysqlpump efficient, we haven’t recommended mysqlpump in production for any of our customers till date, considering several concerns. The following below are mysqlpump features we are really excited about:

  • Supports parallel MySQL logical backup, The resource usage efficiency and high performance backups (we love it !)
  • Much better orchestration possible – You can backup selected databases, tables, stored programs and user accounts etc.
  • By default mysqlpump will not backup performance_schema, sys schema, ndbinfo by default, You have to name them with –databases or –include-databases option
  • mysqlpump does not dump INFORMATION_SCHEMA schema.
  • Faster secondary indexes creation, The indexes created only after inserting rows ! 

mysqlpump examples 

Plain simple backup using mysqlpump

[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees > employeebakup$(date '+%Y-%m-%H-%M-%S').sql 
Enter password: 
Dump progress: 1/4 tables, 0/630999 rows
Dump progress: 2/6 tables, 541250/3919384 rows
Dump progress: 4/6 tables, 1306627/3919384 rows
Dump progress: 5/6 tables, 2128435/3919384 rows
Dump progress: 5/6 tables, 3081685/3919384 rows
Dump completed in 5309 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#

Using mysqlpump based backup with 6 threads: 

[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees --default-parallelism=6  > employeebakup$(date '+%Y-%m-%H-%M-%S').sql 
Enter password: 
Dump progress: 0/5 tables, 250/3477363 rows
Dump progress: 2/6 tables, 606250/3919384 rows
Dump progress: 3/6 tables, 1272103/3919384 rows
Dump progress: 5/6 tables, 2028185/3919384 rows
Dump progress: 5/6 tables, 2932185/3919384 rows
Dump progress: 5/6 tables, 3864185/3919384 rows
Dump completed in 5503 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#

Using mysqlpump to backup only selected databases, spawned 5 threads to backup employee and sakila database:

[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees --parallel-schemas=5:employees,sakila --default-parallelism=6  > bakup$(date '+%Y-%m-%H-%M-%S').sql 
Enter password: 
Dump progress: 1/6 tables, 0/3919384 rows
Dump progress: 2/6 tables, 635250/3919384 rows
Dump progress: 3/6 tables, 1354353/3919384 rows
Dump progress: 5/6 tables, 2219935/3919384 rows
Dump progress: 5/6 tables, 3066185/3919384 rows
Dump completed in 5279 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#

Using mysqlpump to backup selected database and schema:

[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p --databases employees.titles > emp.titles$(date '+%Y-%m-%H-%M-%S').sql
Enter password: 
Dump completed in 437 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#

Restore backup from mysqlpump

Both mysqldump and mysqlpump generate MySQL logical backup in .SQL file so restoration is quiet an straightforward process.

The post How to use mysqlpump for faster MySQL logical backup ? appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569