MySQL Shell – Create and Restore a MySQL backup

MySQL Shell is an advanced client and code editor for MySQL. In addition to the SQL functionality, similar to mysql client, MySQL Shell provides scripting capabilities for JavaScript and Python. It also includes APIs for working with MySQL.

X DevAPI: enables you to work with both relational and document data.
AdminAPI: enables you to work with InnoDB Cluster, see Chapter 6, Using MySQL AdminAPI.

MySQL Shell 8.x is highly recommended for use with MySQL Server 8.0 and 5.7. Download site is at https://dev.mysql.com/downloads/shell/.

MySQL Shell provides new utilities to create and restore a logical dump/backup for the entire database instance, including MySQL users. These utilities are listed below:

Name	Description
util.dumpInstance()	Dump an entire database instance, including users.
util.dumpSchemas()	Dump a set of schemas.
util.loadDump()	A dump loading utility that can import schemas dumped using dumpInstance() utility and dumpSchemas() utility.
util.dumpTables()	Dump specific tables and views.

Examples

Recently I was working on a project where I used MySQL Shell’s dumpSchemas() utility. Commands I used to create the dump were:


#-- Dry run
mysqlsh JS> util.dumpSchemas(["schema_name_1","schema_name_2"], "/backup/mysql",{dryRun: true ,ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

#-- Actual backup
mysqlsh JS> util.dumpSchemas(["schema_name"], "/backup/mysql",{dryRun: false ,ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

1

2

3

4

5

6

7

8

#-- Dry run

mysqlsh JS> util.dumpSchemas(["schema_name_1","schema_name_2"], "/backup/mysql",{dryRun: true ,ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

#-- Actual backup

mysqlsh JS> util.dumpSchemas(["schema_name"], "/backup/mysql",{dryRun: false ,ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

By default util.dumpSchemas() will use chunking to speed up the dump creation. Unfortunately I hit the following error while backup was being taken although the dry run did not warn me of any issues.


ERROR: [Worker002]: get_uint(9): field type is String
ERROR: [Worker001]: get_uint(9): field type is String

1

2

3

4

5

ERROR: [Worker002]: get_uint(9): field type is String

ERROR: [Worker001]: get_uint(9): field type is String

Fix was to use chunking:false option:


mysqlsh JS> util.dumpSchemas(["schema_name"], "/backup/mysql",{dryRun: false, chunking: false, ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

1

2

3

4

mysqlsh JS> util.dumpSchemas(["schema_name"], "/backup/mysql",{dryRun: false, chunking: false, ocimds: true,compatibility: ["strip_restricted_grants","force_innodb","strip_definers"],threads :12})

I haven’t seen any bug report for this yet, but it does look like one and I am sure it will be fixed in the upcoming releases of MySQL Shell. I have had a chance to speak to Oracle MySQL Development team to let them know of this issue.

MySQL Shell 8.x

MySQL Shell – Create and Restore a MySQL backup

Examples

Recent Posts

Categories

Tag cloud

Archives