Mysql Logical Backup using mysqlsh – Import

February 22, 2021
()

Mysql Logical Backup using mysqlsh – Import

This blog post is a continuation of the previous blog post Mysql Logical Backup using mysqlsh – Export Click here to read more.

In the previous blog, we saw How to perform the export of the Database and Table using mysqlsh. Now in this post, we will see How to perform an import of the same dump files using mysqlsh.

Below are backup folders we created using mysqlsh backup utility.

[root@test-machine01 backup4]# ls -ltr
total 32
drwxr-xr-x. 2 root root 4096 Feb 16 22:40 fulldb_excludesch
drwxr-xr-x. 2 root root 4096 Feb 16 22:44 fulldb_excludetab
drwxr-xr-x. 2 root root 4096 Feb 16 22:48 comfulldb
drwxr-xr-x. 2 root root 4096 Feb 16 23:15 multischemabkp
drwxr-xr-x. 2 root root 4096 Feb 16 23:17 schema_excludetab
drwxr-xr-x. 2 root root 4096 Feb 18 09:18 fulldb
drwxr-xr-x. 2 root root 4096 Feb 18 09:20 schemabkp
drwxr-xr-x. 2 root root 4096 Feb 18 09:25 tablebkp
[root@test-machine01 backup4]#

To perform import we have created a 2nd mysql instance in the same server Click here to read more.

[root@test-machine01 fulldb]# mysqlsh root@localhost:3307
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3307'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 213
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3307 ssl  JS >
 MySQL  localhost:3307 ssl  JS > \sql select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.0122 sec)
 MySQL  localhost:3307 ssl  JS >
 MySQL  localhost:3307 ssl  JS > \sql show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0553 sec)
 MySQL  localhost:3307 ssl  JS >


Before we begin import we need to add parameter local_infile=on. Otherwise we will get below error.

 MySQL  localhost:3307 ssl  JS > util.loadDump("/u01/backup4/fulldb", {progressFile :"/u01/backup4/fulldb/log.json",threads :4})
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (RuntimeError)
 MySQL  localhost:3307 ssl  JS >


mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.03 sec)

mysql> set global local_infile = 'on';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>


Full DB Instance Backup Import: Use below commands to perform full DB instance import.

 MySQL  localhost:3307 ssl  JS > util.loadDump("/u01/backup4/fulldb", {progressFile :"/u01/backup4/fulldb/log.json",threads :4})
Loading DDL and Data from '/u01/backup4/fulldb' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Executing common preamble SQL
Re-executing DDL script for schema `repl_test`
[Worker002] Re-executing DDL script for `repl_test`.`repl_test5`
Executing DDL script for schema `employees`
[Worker000] Executing DDL script for `repl_test`.`repl_test4`
[Worker001] Executing DDL script for `employees`.`titles`
[Worker003] Executing DDL script for `employees`.`dept_emp`
[Worker002] Executing DDL script for `employees`.`employees`
[Worker002] Executing DDL script for `employees`.`dept_manager`
[Worker000] Executing DDL script for `employees`.`salaries`
[Worker002] Executing DDL script for `employees`.`departments`
[Worker001] Executing DDL script for `employees`.`current_dept_emp` (placeholder for view)
[Worker001] Executing DDL script for `employees`.`dept_emp_latest_date` (placeholder for view)
Executing DDL script for view `employees`.`current_dept_emp`
Executing DDL script for view `employees`.`dept_emp_latest_date`
[Worker002] employees@employees@@0.tsv.zst: Records: 300024  Deleted: 0  Skipped: 0  Warnings: 0


 MySQL  localhost:3307 ssl  JS > \sql show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+


Resuming aborted Import Job: With mysqlsh utility, you can even start the aborted import. Since we have already executed the below command and was successful hence we can see the message: “There was no remaining data left to be loaded.”

 MySQL  localhost:3307 ssl  JS > util.loadDump("/u01/backup4/fulldb", {progressFile :"/u01/backup4/fulldb/log.json",threads :4})
Loading DDL and Data from '/u01/backup4/fulldb' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Executing common preamble SQL
ERROR: [Worker002] While executing DDL script /u01/backup4/fulldb/repl_test@repl_test3.sql: Unknown database 'repl_test'
ERROR: [Worker003] While executing DDL script /u01/backup4/fulldb/repl_test@repl_test2.sql: Unknown database 'repl_test'
ERROR: Aborting load...
ERROR: [Worker000] While executing DDL script /u01/backup4/fulldb/repl_test@repl_test1.sql: Unknown database 'repl_test'
ERROR: [Worker001] While executing DDL script /u01/backup4/fulldb/repl_test@repl_test5.sql: Unknown database 'repl_test'

There was no remaining data left to be loaded.
4 errors and 0 warnings messages were reported during the load.
Util.loadDump: Error loading dump (RuntimeError)


Exclude specific Schema/DB from Full DB Instance Backup Import: Use option excludeSchemas to exclude any schema from full instance backup while doing the import.

 MySQL  localhost:3307 ssl  JS > util.loadDump("/u01/backup4/fulldb", {progressFile :"/u01/backup4/fulldb/log.json",excludeSchemas: ["employees"] ,threads :4})
Loading DDL and Data from '/u01/backup4/fulldb' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `repl_test`
[Worker003] Executing DDL script for `repl_test`.`repl_test1`
[Worker002] Executing DDL script for `repl_test`.`repl_test5`
[Worker001] Executing DDL script for `repl_test`.`repl_test3`
[Worker000] Executing DDL script for `repl_test`.`repl_test2`
[Worker002] Executing DDL script for `repl_test`.`repl_test4`
[Worker002] repl_test@repl_test4.tsv.zst: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] repl_test@repl_test5.tsv.zst: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] repl_test@repl_test2.tsv.zst: Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] repl_test@repl_test1.tsv.zst: Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] repl_test@repl_test3.tsv.zst: Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL

5 chunks (42 rows, 555 bytes) for 5 tables in 1 schemas were loaded in 1 sec (avg throughput 555.00 B/s)
0 warnings were reported during the load.
 MySQL  localhost:3307 ssl  JS >
 MySQL  localhost:3307 ssl  JS >


Schema Import: Use the below command to perform schema import.

 MySQL  localhost:3307 ssl  JS > util.loadDump("/u01/backup4/schemabkp", {progressFile :"/u01/backup4/schemabkp/log.json",threads :4})
Loading DDL and Data from '/u01/backup4/schemabkp' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `employees`
[Worker000] Executing DDL script for `employees`.`dept_emp`
[Worker003] Executing DDL script for `employees`.`titles`
[Worker002] Executing DDL script for `employees`.`employees`
[Worker001] Executing DDL script for `employees`.`dept_manager`
[Worker002] Executing DDL script for `employees`.`salaries`
[Worker000] Executing DDL script for `employees`.`departments`
[Worker003] Executing DDL script for `employees`.`current_dept_emp` (placeholder for view)
[Worker003] Executing DDL script for `employees`.`dept_emp_latest_date` (placeholder for view)
Executing DDL script for view `employees`.`current_dept_emp`
Executing DDL script for view `employees`.`dept_emp_latest_date`
[Worker000] employees@employees@@0.tsv.zst: Records: 300024  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] employees@dept_manager@@0.tsv.zst: Records: 24  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] employees@departments@@0.tsv.zst: Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] employees@dept_emp@@0.tsv.zst: Records: 331603  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] employees@titles@@0.tsv.zst: Records: 443308  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] employees@salaries@@0.tsv.zst: Records: 1850198  Deleted: 0  Skipped: 0  Warnings: 0 - flushed sub-chunk 1
[Worker001] employees@salaries@@0.tsv.zst: Records: 993849  Deleted: 0  Skipped: 0  Warnings: 0 - loading finished in 2 sub-chunks
Executing common postamble SQL

6 chunks (3.92M rows, 141.50 MB) for 6 tables in 1 schemas were loaded in 35 sec (avg throughput 4.04 MB/s)
0 warnings were reported during the load.
 MySQL  localhost:3307 ssl  JS >


Table Import: Use the below command to perform table import.

 MySQL  localhost:3307 ssl  JS >  util.loadDump("/u01/backup4/tablebkp", {progressFile :"/u01/backup4/tablebkp/log.json",threads :4})
Loading DDL and Data from '/u01/backup4/tablebkp' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
Checking for pre-existing objects...
ERROR: Schema `employees` already contains a table named employees
ERROR: Schema `employees` already contains a table named salaries
ERROR: One or more objects in the dump already exist in the destination database. You must either DROP these objects or exclude them from the load.

Util.loadDump: Duplicate objects found in destination database (RuntimeError)
 MySQL  localhost:3307 ssl  JS >


 MySQL  localhost:3307 ssl  JS >  util.loadDump("/u01/backup4/tablebkp", {progressFile :"/u01/backup4/tablebkp/log.json",ignoreExistingObjects: true ,threads :4})
Loading DDL and Data from '/u01/backup4/tablebkp' using 4 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
Checking for pre-existing objects...
NOTE: Schema `employees` already contains a table named employees
NOTE: Schema `employees` already contains a table named salaries
NOTE: One or more objects in the dump already exist in the destination database but will be ignored because the 'ignoreExistingObjects' option was enabled.
Executing common preamble SQL
Executing DDL script for schema `employees`
[Worker000] Executing DDL script for `employees`.`salaries`
[Worker001] Executing DDL script for `employees`.`employees`
[Worker003] employees@employees@@0.tsv.zst: Records: 300024  Deleted: 300024  Skipped: 0  Warnings: 0
[Worker002] employees@salaries@@0.tsv.zst: Records: 1850198  Deleted: 0  Skipped: 0  Warnings: 0 - flushed sub-chunk 1
[Worker002] employees@salaries@@0.tsv.zst: Records: 993849  Deleted: 0  Skipped: 0  Warnings: 0 - loading finished in 2 sub-chunks
Executing common postamble SQL

2 chunks (3.14M rows, 112.60 MB) for 2 tables in 1 schemas were loaded in 50 sec (avg throughput 2.25 MB/s)
0 warnings were reported during the load.
 MySQL  localhost:3307 ssl  JS >


Part I : Mysql Logical Backup using mysqlsh – Export

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *