Tuesday, September 1, 2020

How to take MySQL Enterprise Edition 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!!!

5 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
  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. I wish to show thanks to you just for bailing me out of this particular trouble. As a result of checking through the net and meeting techniques that were not productive, Same as your blog I found another one Oracle Cloud Applications .Actually I was looking for the same information on internet for Oracle Cloud Applications Consultant and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.

    ReplyDelete
  5. It is amazing to visit your site. Thanks for sharing this information, this is useful to me...
    Workday Studio Training
    Workday Studio Online Training

    ReplyDelete