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.

References

https://mysqlserverteam.com/mysql-shell-8-0-21-speeding-up-the-dump-process/

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

https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/

Thank you for using MySQL!!!