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:<\/p>\n

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

  mysqlpump examples\u00a0<\/strong><\/p>\n

  Plain simple backup using\u00a0mysqlpump<\/strong>:\u00a0<\/em><\/p>\n

  [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees > employeebakup$(date '+%Y-%m-%H-%M-%S').sql \r\nEnter password: \r\nDump progress: 1\/4 tables, 0\/630999 rows\r\nDump progress: 2\/6 tables, 541250\/3919384 rows\r\nDump progress: 4\/6 tables, 1306627\/3919384 rows\r\nDump progress: 5\/6 tables, 2128435\/3919384 rows\r\nDump progress: 5\/6 tables, 3081685\/3919384 rows\r\nDump completed in 5309 milliseconds\r\n[root@localhost mysqlpump2018-06-23-25-49]#<\/pre>\n
  Using mysqlpump<\/strong> based backup with 6 threads:\u00a0<\/em><\/p>\n
  [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees --default-parallelism=6  > employeebakup$(date '+%Y-%m-%H-%M-%S').sql \r\nEnter password: \r\nDump progress: 0\/5 tables, 250\/3477363 rows\r\nDump progress: 2\/6 tables, 606250\/3919384 rows\r\nDump progress: 3\/6 tables, 1272103\/3919384 rows\r\nDump progress: 5\/6 tables, 2028185\/3919384 rows\r\nDump progress: 5\/6 tables, 2932185\/3919384 rows\r\nDump progress: 5\/6 tables, 3864185\/3919384 rows\r\nDump completed in 5503 milliseconds\r\n[root@localhost mysqlpump2018-06-23-25-49]# \r\n<\/pre>\n
  Using mysqlpump<\/strong> to backup only selected databases, spawned 5 threads to backup employee<\/strong>\u00a0and sakila<\/strong>\u00a0database:<\/em><\/p>\n
  [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 \r\nEnter password: \r\nDump progress: 1\/6 tables, 0\/3919384 rows\r\nDump progress: 2\/6 tables, 635250\/3919384 rows\r\nDump progress: 3\/6 tables, 1354353\/3919384 rows\r\nDump progress: 5\/6 tables, 2219935\/3919384 rows\r\nDump progress: 5\/6 tables, 3066185\/3919384 rows\r\nDump completed in 5279 milliseconds\r\n[root@localhost mysqlpump2018-06-23-25-49]# \r\n<\/pre>\n
  Using mysqlpump<\/strong> to backup selected database and schema: <\/em><\/p>\n
  [root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p --databases employees.titles > emp.titles$(date '+%Y-%m-%H-%M-%S').sql\r\nEnter password: \r\nDump completed in 437 milliseconds\r\n[root@localhost mysqlpump2018-06-23-25-49]# \r\n<\/pre>\n
  Restore backup from mysqlpump<\/strong><\/p>\n
  Both mysqldump and mysqlpump generate MySQL logical backup in .SQL<\/em> file so restoration is quiet an straightforward process:<\/p>\n
  mysql -u root -p < backup.SQL<\/p>\n","protected":false},"excerpt":{"rendered":"
  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 […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,2,15],"tags":[5,16,139],"yoast_head":"\n