Tuesday, September 1, 2020

How to do selective schema Restore Using “Load Dump” Features Introduced in MySQL 8.0.21 ?

 How to do selective schema Restore Using “Load Dump” Features Introduced in MySQL 8.0.21 ?

In this Blog I will cover below topic:-

1.      What is Dump Load features all about.

2.      How to take Restore particular database from Complete Backup?

3.      Conclusion.

What is Dump Load features all about?

Dump Load Utility introduced in 8.0.21 version of MySQL Shell , which will do Import of backup files to MySQL Instance.

Make sure back up files should be created with utility called Instance Dump/Schema Dump Utility.

More Info :- https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html

 

In case if you wanted to know how to take backup using utility Instance Dump

refer my another blog:-

Features of Load Dump

·       You can customize the import with further options in the dump loading utility:

 ·       You can select individual tables or schemas to import or to exclude from the import.

 ·       Users and their roles and grants are excluded by default, but you can choose to import them.

 ·       You can choose to skip binary logging on the target MySQL instance during the course of the import using a SET sql_log_bin=0 statement.

 ·       You can carry out a dry run with your chosen set of dump loading options to show what actions would be performed when you run the utility for real with those options.

Load Dump Examples

util.loadDump("/opt/packages/OnpremisesInstanc/", {dryRun: true})

 

Prerequisite

'local_infile' global system variable must be set to ON

FIX:- MySQL  localhost:33060+ ssl  JS > \sql set global local_infile=1;

Run the Load Dump Commands

MySQL  localhost:33060+ ssl  JS > util.loadDump("/opt/packages/OnpremisesInstanc/",{excludeSchemas:['sakila','sales']})

 

d

Conclusion

 

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/













No comments:

Post a Comment