MySQL Shell is an advanced command-line client and code editor for MySQL. Besides being able to run SQL commands, MySQL Shell provides scripting capabilities for JavaScript and Python from within Shell. But, you can also use shell to run JavaScript outside of the command-line code editor. In other words, you can execute a JavaScript like this:
mysqlsh -uUser -pPassword < script_name.js
Here are two example JavaScript scripts. The first script uses util.dumpSchemas to dump a database from MySQL version 5.7.44, which is on a compute instance in Oracle Cloud Infrastructure (OCI). The second uses util.loadDump to import the dump file into MySQL HeatWave version 8.0.40 on OCI.
MySQL Shell’s instance dump utility util.dumpInstance() and schema dump utility util.dumpSchemas(), introduced in MySQL Shell 8.0.21, support the export of all schemas or a selected schema from an on-premise MySQL instance into an Oracle Cloud Infrastructure Object Storage bucket or a set of local files. In this example, we are going to dump a single schema (database) to a local file on an Oracle Cloud compute instance, which is running MySQL 5.7.44. You will need to change the MySQL database credentials inside of both scripts. Here is a dump script to dump a single database named “test_database”.
// dump.js - dumped from 5.7.44
// Connect to the server - change the credentials
const session = mysql.getSession("root:PassWord123!@localhost");
// Set the session as the active global session
shell.setSession(session);
// Perform a schema dump with options
try {
// Perform the schema dump
print("Starting dump of schema(s): test_database");
util.dumpSchemas(['test_database'], '~/test_databasedump', {
threads: 2,
ocimds: true // Enable OCI MDS metadata
});
// If successful, print a confirmation message
print("Dump completed successfully.");
} catch (err) {
// If an error occurs, print the error and exit the script
print("Error during dump: " + err.message);
shell.exit(1); // Exit with a non-zero status to indicate failure
}
// Close the session when done
session.close();
Then, to run the script, you simply execute this command from the command line (assuming you have installed MySQL Shell), and you will get an output similiar to what is shown below. Since I will be exporting this to a new version of MySQL, and since the new version is MySQL HeatWave on OCI, MySQL Shell will check for compatibility with the MySQL HeatWave Service as I set the variable ocimds to equal “true”. (The dump output is truncated)
$ mysqlsh -uroot -p < dump.js NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping. Acquiring global read lock Global read lock acquired Initializing - done 1 schemas will be dumped and within them 1 table, 0 views. Gathering information - done All transactions have been started Global read lock has been released NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell. Checking for compatibility with MySQL HeatWave Service 8.0.40 NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. Checking for potential upgrade issues. The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.44-enterprise-commercial-advanced-log - MySQL Enterprise Server - Advanced Edition (Commercial), will now be checked for compatibility issues for upgrade to MySQL 8.0.40... 1) MySQL 8.0 syntax check for routine-like objects No issues found 2) Usage of db objects with names conflicting with new reserved keywords No issues found 3) Usage of utf8mb3 charset No issues found 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 5) Partitioned tables using engines with non native partitioning No issues found 6) Foreign key constraint names longer than 64 characters No issues found 7) Usage of obsolete MAXDB sql_mode flag No issues found 8) Usage of obsolete sql_mode flags No issues found 9) ENUM/SET column definitions containing elements longer than 255 characters No issues found 10) Usage of removed functions No issues found 11) Usage of removed GROUP BY ASC/DESC syntax No issues found 12) Zero Date, Datetime, and Timestamp values No issues found 13) Tables recognized by InnoDB that belong to a different engine No issues found 14) New default authentication plugin considerations Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients, you may encounter compatibility issues after upgrading unless newly created accounts are created to use 'mysql_native_password'. More information: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication 15) Columns which cannot have default values No issues found 16) Check for invalid table names and schema names used in 5.7 No issues found 17) Check for orphaned routines in 5.7 No issues found 18) Check for deprecated usage of single dollar signs in object names No issues found 19) Check for indexes that are too large to work on higher versions of MySQL Server than 5.7 No issues found 20) Check for deprecated '.<table>' syntax used in routines. No issues found 21) Check for columns that have foreign keys pointing to tables from a diffrent database engine. No issues found Errors: 0 Warnings: 1 Notices: 0 NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. Compatibility checks finished.ice compatibility \ 0 / 2 Validating MySQL HeatWave Service compatibility - done Writing global DDL files Running data dump using 2 threads. NOTE: Progress information uses estimated values and may not be accurate. Writing schema metadata - done Writing DDL - done Writing table metadata - done Starting data dump ... Dump completed successfully.
The dump files will be dumped to a directory as named within the script – “~/test_database“:
drwxr-x---. 2 opc opc 4096 Nov 25 23:47 test_database
And a listing of the directory would look something like this:
$ ls -l test_databasedump/ total 40 -rw-r-----. 1 opc opc 224 Nov 25 23:47 @.done.json -rw-r-----. 1 opc opc 885 Nov 25 23:47 @.json -rw-r-----. 1 opc opc 240 Nov 25 23:47 @.post.sql -rw-r-----. 1 opc opc 299 Nov 25 23:47 test_database.json -rw-r-----. 1 opc opc 542 Nov 25 23:47 test_database.sql -rw-r-----. 1 opc opc 9 Nov 25 23:47 test_database@users2@@0.tsv.zst -rw-r-----. 1 opc opc 8 Nov 25 23:47 test_database@users2@@0.tsv.zst.idx -rw-r-----. 1 opc opc 657 Nov 25 23:47 test_database@users2.json -rw-r-----. 1 opc opc 799 Nov 25 23:47 test_database@users2.sql ... -rw-r-----. 1 opc opc 240 Nov 25 23:47 @.sql
And here is the JavaScript script to load the dump files into a MySQL HeatWave 8.0.40 instance – which has a different IP address. But this would work on another 5.7.X or 8.4.X instance as well.
// load.js - loaded the dump file into 8.0.40
// Connect to the server - change the credentials
const session = mysql.getSession("admin:PassWord123!@10.0.1.13");
// Set the session as the active global session
shell.setSession(session);
// Load the dump with the `ocimds` option enabled
try {
util.loadDump('~/test_database', {
threads: 2,
resetProgress: true,
ignoreVersion: true
});
// If successful, print a confirmation message
print("Schema load completed successfully.\n\n");
} catch (err) {
// If an error occurs, print the error and exit the script
print("Error during load: " + err.message);
shell.exit(1); // Exit with a non-zero status to indicate failure
}
// Close the session when done
session.close();
Since I am importing the local dump file to a remote server, I will need to connect to that IP address with Shell. Here is how I would run the load script: (The load output is truncated)
mysqlsh -uadmin -p -h10.0.1.123 < load.js Loading DDL and Data from '~/test_database' using 2 threads. Opening dump... Target is MySQL 8.0.40-u1-cloud (MySQL HeatWave Service). Dump was produced from MySQL 5.7.44-enterprise-commercial-advanced-log WARNING: Destination MySQL version is newer than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway. NOTE: Load progress file detected for the instance but 'resetProgress' option was enabled. Load progress will be discarded and the whole dump will be reloaded. Scanning metadata - done Checking for pre-existing objects... Executing common preamble SQL Executing DDL - done Executing view DDL - done Starting data load .... 0 warnings were reported during the load. Schema load completed successfully.
MySQL Shell has more utilities for working with MySQL. To access the utilities from within MySQL Shell, you may use the util global object, which is available in JavaScript and Python modes, but not in SQL mode. Here is the link for the MySQL Shell Utilities.