Mysqldump is a popular logical backup tool for MySQL which was originally written by Igor Romanenko. Show Mysqldump performs logical backups (set of SQL statements). By default, mysqldump does not dump information_schema tables and it never takes performance_schema. But the major drawback of mysqldump is that it uses only one thread while doing backup and restore. (Even your server has 64 cores). To overcome this drawback, MySQL introduced new utilities on the Shell client. In this blog I am going to explain these new backup utilities. Overview of MySQL ShellThe MySQL shell is a powerful and advanced client and code editor for MySQL server. MySQL shell 8.0.21 includes some exciting new utilities to create a logical dump and do a logical restore for the entire database instance, including users. MySQL shell 8.0.22 included a logical backup of specific tables and restore. Utilities
util.dumpInstance()The dumpInstance() utility will dump all the databases which are presented in MySQL data directory. It will exclude the information_schema, mysql, ndbinfo, performance_schema, and sys schemas while taking the dump. Syntax
It will dump to the local filesystem, outputUrl is a string specifying the path to a local directory where the dump files are to be placed. You can specify the absolute path or a path relative to the current working directory. In this utility, there is a dry run option to inspect the schemas and view the compatibility issues, then run the dump with the appropriate compatibility options applied to remove the issues. OptionsLet’s look at some important options for this dumputility. ocimds : [True | False]When this option is set to true, It will check the data dictionary, index dictionary, and encryption options in CREATE TABLE statements are commented out in the DDL files, to ensure that all tables are located in the MySQL data directory and use the default schema encryption. And it will check any storage engines in CREATE TABLE statements other than InnoDB, for grants of unsuitable privileges to users or roles, and for other compatibility issues. If any non-conforming SQL statement is found, an exception is raised and the dump is halted. So we are suggesting to use the dryRun option to list out all of the issues with the items in the dump before the dumping process is started. Use the compatibility option to automatically fix the issues in the dump output. Note: This option only has support for Instance dump utility and schema dump utility. Example 1
So we have a myisam table in my cart database.The dry run option clearly throws the error. If you want to fix these errors automatically in your dump file, pass the compatibility option as an argument in your command. Example 2
Now the dry run is fine and there are no exceptions. Lets run the dump instance command to take an instance backup. The target directory must be empty before the export takes place. If the directory does not yet exist in its parent directory, the utility creates it. Example 3
Above we have used a compatibility option. So while taking the dump, it will convert myisam tables into innodb and store them to file. Logs
If you are using mysqldump, it will store the output into a single file. But here it generates more files as we will explain below. These are the available files in the backup directory.
util.dumpSchemas()It will dump the specific schemas that you mention in the arguments for this utility. Syntax
Example
util.dumpTablesIf you want to dump specific tables we can use dumpTables utility. For the larger tables, mysqldump will take more time. Use the dumpTables utility to reduce the time. Syntax
Example
Dump Loading UtilityThe dump loading utility provides data streaming to remote storage, parallel loading of tables or table chunks, progress state tracking, resume and reset capability, and the option of concurrent loading while the dump is still taking place. Note: The dump loading utility uses the LOAD DATA LOCAL INFILE statement, so we need to enable this local_infile parameter globally while importing. The dump loading utility checks whether the sql_require_primary_key system variable is set to ON, and if it is, returns an error if there is a table in the dump files with no primary key. Syntax
Example
By default, fulltext indexes for a table are created only after the table is completely loaded, which speeds up the import. You can also choose to disable index creation during the import, and create the indexes afterwards. The dump loading utility imports across multiple threads to maximize the parallelism. If the dump files were compressed by MySQL Shell’s dump utilities, the dump loading utility handles decompression. You can select individual tables or schemas to import or to exclude from the import. 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. ConclusionThis is one of the powerful utilities in MySQL 8.0. It is now possible to dump from MySQL 5.6 and load these dumps into MySQL 5.7 or 8.0. But dumping of user accounts is not supported when dumping from MySQL 5.6. In my next blog, we shall compare the backup/restoration speed of MySQLdump and shell utility. |