MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. The X DevAPI enables you to work with both relational and document data, and MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7.
MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. These are the utilities to take a backup; let’s see some basic commands.
- util.dumpTables – Dump one or more tables from single database
- util.dumpSchemas – Dump one or more databases
- util.dumpInstance – Dump full instance
- util.loadDump – Restore dump
1. Single table dump
The below command is to take a dump of the table sbtest1 from the sysbench database and store the backup on the destination directory sysbench_dumps. The utility will create the directory when the destination directory does not exist. By default, compression, and chunking are enabled. When chunking is enabled, the table dump will be spitted onto multiple files based on size. Dialect:”csv gives the extension of the dump file, and by default, the file will be created with the tsv (Table separated value) extension.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dialect:"csv"}) Acquiring global read lock Global read lock acquired Initializing - done 1 tables and 0 views will be dumped. Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Running data dump using 6 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 101% (1000.00K rows / ~986.40K rows), 317.96K rows/s, 63.91 MB/s Dump duration: 00:00:03s Total duration: 00:00:04s Schemas dumped: 1 Tables dumped: 1 Data size: 198.89 MB Rows written: 1000000 Bytes written: 198.89 MB Average throughput: 60.96 MB/s MySQL localhost JS > |
These are the files created for the above dump command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@centos12 sysbench_dumps]# ls -lrth total 190M -rw-r-----. 1 root root 869 Jun 21 13:08 @.json -rw-r-----. 1 root root 240 Jun 21 13:08 @.sql -rw-r-----. 1 root root 240 Jun 21 13:08 @.post.sql -rw-r-----. 1 root root 231 Jun 21 13:08 sysbench.json -rw-r-----. 1 root root 638 Jun 21 13:08 sysbench@sbtest1.json -rw-r-----. 1 root root 474 Jun 21 13:08 sysbench.sql -rw-r-----. 1 root root 789 Jun 21 13:08 sysbench@sbtest1.sql -rw-r-----. 1 root root 1.5K Jun 21 13:08 sysbench@sbtest1.csv.idx -rw-r-----. 1 root root 190M Jun 21 13:08 sysbench@sbtest1.csv -rw-r-----. 1 root root 233 Jun 21 13:08 @.done.json [root@centos12 sysbench_dumps]# pwd /home/vagrant/sysbench_dumps |
| @.json | Complete information about dump options, servername, and username used for the dump and binlog file and position, etc. |
| @.sql, @.post.sql. | Shows server version and dump version details. |
| sysbench.json | Database and table details involved in the dump. |
| [email protected] | Details about the table sbtest1, including column names, indexes, triggers, characterset, and partitions. |
| sysbench.sql | Create a statement for the database sysbench. |
| [email protected] | Create a statement for the table sbtest1. |
| @.done.json | End time of the dump and dump file size. |
| [email protected] | Table dump file. |
2. Backup only table structure
Option ddlOnly:true is used to take only the table structures. The below command is to take the table structure of sbtest1 from the sysbench database and store it in the sysbench_dumps path.
1 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, ddlOnly:true}) |
3. Dump only table data
Option dataOnly:true to take the dump of only data. The below command is to take table data of sbtest1 from the sysbench database and store it in the sysbench_dumps path.
1 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true}) |
4. Dump only selected data
This “where”: {“databasename.tablename”: “condition”} option is used to take a dump of selected data. The below command is to take a dump of table sbtest1 from id 1 to 10.
1 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6 ,chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10"}}) |
It’s also possible to take a dump of multiple tables with their conditions in a single command.
Syntax:
1 | "where" : {"databasename1.tablename1": "condition for databasename1.tablename1", "databasename2.tablename2": "condition for databasename2.tablename2"} |
The below command is to take a dump of table sbtest1 from id 1 to 10 and dump of sbtest2 from id 100 to 110.
1 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10", "sysbench.sbtest2": "id between 100 and 110"}}) |
5. Dump data from partitions
The option partitions is to take a dump from selected partitions.
Syntax:
1 | "partitions" : {"db1.table1": ["list of partitions"],"db2.table1": ["list of partitions"]} |
The below command is to take a dump from only partitions p1 and p2 and dump of sbtest2 table from partitions p4 and p5.
1 | MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "partitions" : {"sysbench.sbtest1": ["p1", "p2"],"sysbench.sbtest2": ["p4", "p5"]}}) |
6. Taking Schemas dump
When taking schemas dump, by default, events, triggers, and routines will be taken. Those are stored in the database_name.sql file. The below command is to take a dump of the percona and sakila databases.
1 | MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {"compression":"none", "threads":6, chunking:false}) |
The below command is to skip the events, routines, and triggers.
1 | MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {events:false, routines:false, triggers:false, "compression":"none", "threads":6, chunking:false}) |
We can also use these options to include and exclude the events, routines, and triggers
Syntax:
1 2 3 4 5 6 7 | includeEvents : [db1.include_event1,db2.include_event2...] includeRoutines : [db1.include_procedure1,db2.include_function2...] includeTriggers : [db1.include_trigger1,db2.include_trigger2...] excludeEvents : [db1.exclude_event1,db2.exclude_event2...] excludeTriggers : [db1.exclude_trigger1,db2.exclude_trigger2...] excludeRoutines : [db1.exclude_procedure1,db2.exclude_function2...] |
7. Taking specified tables from different databases
Sometimes we may need to take selected tables from different schemas. We can achieve this using the option includeTables.
Syntax:
1 | includeTables:["db1.table1", "db2.table2"....] |
Below is the command to take a dump of table users from the percona database and a dump of the actor table from the sakila database.
1 | MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {includeTables:["percona.users", "sakila.actor"], "compression":"none", "threads":6, chunking:false}) |
8. Instance dump
The command util.dumpInstance takes the dump of a complete instance and stores it in /backup/instance_dump path. The system databases (mysql, sys, information_schema, performance_schema) are excluded, and by default, a dump of all the users from the instance is taken and stored in the file @.users.sql. This user dump file has the create and grant statements of all the users.
1 | MySQL localhost JS > util.dumpInstance("/backup/instance_dump", {"compression":"none", "threads":6, chunking:false}) |
Some more options in the instance dump.
1 2 3 4 5 6 7 | users: false - Skip users dump excludeUsers : [‘user1’,’user2’] - Execute particular users includeUsers : [‘user1’,’user2’]. - Include particular users excludeSchemas : [“db1”,”db2”] - Exclude particular schemas includeSchemas : [“db1”,”db2”]. - Include particular schemas excludeTables : [“db1.table1”,”db2.table2”] - Exclude particular tables includeTables : [“db1.table1”,”db2.table2”] - Include particular tables |
9. Restore the dump into a single database
The command util.loadDump is used to restore the dumps. The variable local_infile should be enabled to load the dumps.
Syntax :
1 | util.loadDump("/path/of/the/dump", {options}) |
The below command is to restore the dump into database test_restore. When we need to restore on a different schema, we have to use this option schema: “test_restore”. Otherwise, it will be restored on the source schema where it was taken.
1 2 3 4 5 | MySQL localhost SQL > create database test_restore; Query OK, 1 row affected (0.3658 sec) MySQL localhost SQL > js Switching to JavaScript mode... MySQL localhost JS > util.loadDump("/home/vagrant/schema_dump", {schema:"test_restore", progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true}) |
10. Restore the full instance dump and configure replication
Here, we just loaded the full instance dump from /home/vagrant/instance_dump path with eight parallel threads.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MySQL localhost JS > util.loadDump("/home/vagrant/instance_dump", {progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true}) Loading DDL and Data from '/home/vagrant/instance_dump' using 8 threads. Opening dump... Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.32-24 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 8 thds loading / 100% (19.18 MB / 19.18 MB), 541.36 KB/s, 6 / 23 tables and partitions done Recreating indexes - done Executing common postamble SQL 23 chunks (100.00K rows, 19.18 MB) for 11 tables in 2 schemas were loaded in 11 sec (avg throughput 2.53 MB/s) 0 warnings were reported during the load. MySQL localhost JS > |
I got the binlog file and position from the file @.json and configured the replication.
1 2 3 4 | [root@centos12 instance_dump]# cat @.json | grep -i binlog "binlogFile": "centos12-bin.000006", "binlogPosition": 760871466, [root@centos12 instance_dump]# |
1 2 | MySQL localhost SQL > CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.33.12", SOURCE_USER="bhuvan",SOURCE_PASSWORD="Bhuvan@123", SOURCE_LOG_FILE='centos12-bin.000006',SOURCE_LOG_POS=760871466; Query OK, 0 rows affected, 2 warnings (0.1762 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | MySQL localhost SQL > START REPLICA; Query OK, 0 rows affected (0.1156 sec) MySQL localhost SQL > show replica statusG *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 192.168.33.12 Source_User: bhuvan Source_Port: 3306 Connect_Retry: 60 Source_Log_File: centos12-bin.000006 Read_Source_Log_Pos: 823234119 Relay_Log_File: centos11-relay-bin.000002 Relay_Log_Pos: 1152129 Relay_Source_Log_File: centos12-bin.000006 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 762023266 Relay_Log_Space: 62363195 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 718 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 100 Source_UUID: f46a1600-045e-11ee-809f-0800271333ce Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Waiting for dependent transaction to commit Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.1470 sec) |
I hope the above examples help to understand the backup and restore using MySQL Shell. It has many advantages over native mysqldump. I personally feel that we are missing insert statements here, as we used to see the insert statements in dump files; apart from that, it looks good. Logical backup is good only when the dataset is small. When the dataset is big, the logical backup takes longer, and we have to go for physical backup using Percona XtraBackup.
Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.
