Tuesday, September 1, 2020

How to take MySQL backup using Instance Dump Features Introduced in MySQL 8.0.21?

 Using Instance Dump and Schema Dump features introduced in MySQL 8.0.21 Version.

In this Blog I will cover below topic:-

1.       What is Instance Dump/Schema Dump features all about?

2.       What are advantage?

3.       What is Disadvantage of using Instance Dump?

4.       Performance Benchmarks?

5.       Conclusion

What is Instance Dump/Schema Dump features all about?

MySQL Instance Dump is another logical back up option where backup can be processed in multi-threaded with file compress which will help users to improve performance of overall backup process and also save disk space.

This features is introduced in MySQL 8.0.21, which means to utilize these features you must use client MySQL SHELL 8.0.21 version.

To perform instance Dump there is utility called util.dumpInstance(outputUrl[, options])

In short, util.dumpInstance(): dump an entire database instance, including users.

MySQL Schema Dump is another customize options where users can perform back up of choice, let’s say if you wanted to take one single/multiple databases or else particular table  then we use utility called “Schema Dump Utility” à util.dumpSchemas

In short, util.dumpSchemas(): dump a set of schemas.

The dumps created by MySQL Shell's instance dump utility and schema dump utility comprise DDL files specifying the schema structure, and tab-separated .tsv files containing the data.

By default, the dump utilities chunk table data into multiple data files and compress the files.

 

What are things can be done with Instance Dump

1.       Backing to Oracle Cloud Infrastructure Object Storage.

2.       Backing Up to local machine directory.

3.       Helpful in provisioning new Instance in on-premises as well as on Cloud.

 

 

Requirements apply to dumps using the instance dump utility and schema dump utility:

 - MySQL 5.7 or later is required for both the source MySQL instance and the destination MySQL instance.

·       Object names in the instance or schema must be in the latin1 or utf8 characterset.

·       Data consistency is guaranteed only for tables that use the InnoDB storage engine.

·       The upload method used to transfer files to an Oracle Cloud Infrastructure Object Storage bucket has a file size limit of 1.2 TiB.

More info: - https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

What is Advantage of using Instance Dump?

·       It is parallel dumping with multiple threads.

·       Automatic file compression – zstd algorithm. The alternatives are to use gzip compression (gzip) or no compression (none).

·       Improved Performance than mysqldump.

·       Flexibility to choose, what we wanted to backup.

·       Options to push backup to Oracle Cloud Infrastructure.

·       Options to get problem statement/error in advance  with features called dryRun.

What is Disadvantage of using Instance Dump?

·       It is Logical Backup. so challenges of mysqldump will still applies.

·       While taking backup it does take LOCK on the schemas.

·       Debugg is not an easy as files is divided into chunks.







MySQL  localhost:3306 ssl  JS > util.dumpInstance("opt/packages/worlddump", {dryRun: true, ocimds: true})

 

Error:-

 Checking for compatibility with MySQL Database Service 8.0.21


FIX:-

util.dumpInstance("C:/Users/hanna/worlddump", {dryRun:true,ocimds: true, compatibility: ["strip_definers", "strip_restricted_grants"]})




Use Case #01 :- To dump complete MySQL Instance.

shell-js> util.dumpInstance("/opt/packages/onpremisesdump", {ocimds: true, compatibility: ["strip_definers", "strip_restricted_grants"]})



 Use Case #02 :- Take Backup of only single database using Schema Dump Utility

MySQL  localhost:33060+ ssl  JS > util.dumpSchemas(["customerDB"],"/opt/packages/customerDB123",{threads:20})

  O/P

4 thds dumping - 98% (10.57M rows / ~10.69M rows), 170.72K rows/s, 16.87 MB/s uncompressed, 1.64 MB/s 4 thds dumping - 98% (10.58M rows / ~10.69M rows), 170.72K rows/s, 16.87 MB/s uncompressed, 1.64 MB/s 4 thds dumping - 99% (10.58M rows / ~10.69M rows), 170.72K rows/s, 16.87 MB/s uncompressed, 1.64 MB/s 4 thds dumping - 99% (10.59M 

Duration: 00:01:47s

Schemas dumped: 1

Tables dumped: 922

Uncompressed data size: 1.30 GB

Compressed data size: 125.10 MB

Compression ratio: 10.4

Rows written: 10844660

Bytes written: 125.10 MB

Average uncompressed throughput: 12.17 MB/s

Average compressed throughput: 1.17 MB/s

 MySQL  localhost:33060+ ssl  JS >


Performance Benchmarks?

Conclusion

MySQL Shell makes MySQL easier to use, by providing an interactive MySQL client supporting SQL, Document Store, JavaScript & Python interface with support for writing custom extensions.

And with dumpInstance(), dumpSchemas() and loadDump() Shell now provides powerful logical dump and load functionality.


Thank you for using MySQL!!!

6 comments:

  1. How can I use API for dupmSchemas?
    I could do dump of bookstackbb database using -e option:
    mysqlsh login@localhost --password=pass -e 'util.dumpSchemas(["bookstackdb"], "bookstack")'

    But can't do dump using clear API, I get the error:
    mysqlsh login@localhost --password=pass -- util dump-schemas ["bookstackdb"] bookstack
    ...
    ERROR: Util.dumpSchemas: Argument #1 is expected to be an array

    (I could do dumpInstance using API.)

    ReplyDelete
    Replies
    1. Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download Now

      >>>>> Download Full

      Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download LINK

      >>>>> Download Now

      Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download Full

      >>>>> Download LINK YP

      Delete
  2. You can user https://www.percona.com/software/mysql-database/percona-xtrabackup - opensource

    ReplyDelete
  3. https://www.percona.com/software/mysql-database/percona-xtrabackup

    opensource backup without lock.

    ReplyDelete
  4. Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download Now

    >>>>> Download Full

    Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download LINK

    >>>>> Download Now

    Mysql Solutions - On Premises And Cloud.: How To Take Mysql Enterprise Edition Backup Using Instance Dump Features Introduced In Mysql 8.0.21? >>>>> Download Full

    >>>>> Download LINK qV

    ReplyDelete
  5. Hi, I know it's a old post but trying my luck. I am running instance backup where the instance has 100+ schemas. Is there a way I can create folder for each schema while dumping. Otherwise everything is mixed in the folder, and it is taking up long time to see the content inside the folder. I appreciate if you share your thoughts. Thnx!

    ReplyDelete