Mysql Logical Backup using myloader – Import

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

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

Below are backup folders we created using mydumper backup utility.

[root@test-machine01 backup3]# ls -ltr
total 60
drwxr-xr-x. 2 root root 20480 Feb 13 15:59 fulldb
drwxr-xr-x. 2 root root  4096 Feb 14 12:00 test
drwxr-xr-x. 2 root root  4096 Feb 14 12:02 singledb
drwxr-xr-x. 2 root root  4096 Feb 14 12:10 compdb
drwxr-xr-x. 2 root root  4096 Feb 14 12:37 tablebkp
drwxr-xr-x. 2 root root  4096 Feb 14 13:03 regtablebkp
drwxr-xr-x. 2 root root  4096 Feb 14 13:10 schemaonlydb
drwxr-xr-x. 2 root root  4096 Feb 14 13:13 dataonly
drwxr-xr-x. 2 root root  4096 Feb 14 13:44 test1
drwxr-xr-x. 2 root root  4096 Feb 15 11:15 multidb
[root@test-machine01 backup3]#

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

[root@test-machine01 backup3]#  mysql -u root -p -S /u01/mysql-2/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 142
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.13 sec)

mysql>

Before we start importing dumpfile we need to create a new user account as we will get the below error while using root. We will create a user admin.

[root@test-machine01 backup3]# myloader -u root -p -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/fulldb  -v 3

** (myloader:44443): CRITICAL **: 10:43:28.410: Error connection to database: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
[root@test-machine01 backup3]#

mysql> create user 'admin'@'localhost' identified by 'Root@1234';
Query OK, 0 rows affected (1.05 sec)

mysql> grant all privileges on *.* to  'admin'@'localhost' ;
Query OK, 0 rows affected (0.25 sec)

mysql>
mysql> alter user 'admin'@'localhost' identified with mysql_native_password BY 'Root@1234';
Query OK, 0 rows affected (0.11 sec)

Full DB Backup Import: Use the below command to import full DB backup.

[root@test-machine01 backup3]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/fulldb  -v 3
** Message: 10:44:15.766: 4 threads created
** Message: 10:44:15.833: Creating database `employees`
** Message: 10:44:15.915: Creating table `employees`.`departments`
** Message: 10:44:16.592: Creating table `employees`.`dept_emp`
** Message: 10:44:16.880: Creating table `employees`.`employees`
** Message: 10:44:17.096: Creating table `employees`.`titles`
** Message: 10:44:17.340: Creating table `mysql`.`columns_priv`

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+
6 rows in set (0.07 sec)

mysql>

Multi DB backup Import: Use the below command to do a multi DB backup import.

[root@test-machine01 multidb]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/multidb   -v 3
** Message: 11:05:23.011: 4 threads created
** Message: 11:05:23.023: Creating database `employees`
** Message: 11:05:23.038: Creating table `employees`.`departments`
** Message: 11:05:23.083: Creating table `employees`.`dept_emp`
** Message: 11:05:23.098: Creating table `employees`.`dept_manager`
** Message: 11:05:23.189: Creating database `repl_test`
** Message: 11:05:23.208: Creating table `repl_test`.`repl_test2`
** Message: 11:05:23.240: Creating table `repl_test`.`repl_test1`
** Message: 11:05:23.248: Creating table `repl_test`.`repl_test3`
** Message: 11:05:23.256: Creating table `repl_test`.`repl_test4`

Single DB Backup Import in Same or Different DB Name: You can choose single DB to restore from full DB backup using –source-db option. Or you can restore from a single DB backup. You can also choose to restore DB backup in different DB names using options –source-db –database.

[root@test-machine01 backup3]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/fulldb  --source-db employees -v 3
** Message: 10:54:45.596: 4 threads created
** Message: 10:54:45.651: Creating database `employees`
** Message: 10:54:45.666: Creating table `employees`.`departments`
** Message: 10:54:45.743: Creating table `employees`.`dept_emp`
** Message: 10:54:45.884: Creating table `employees`.`employees`
** Message: 10:54:45.985: Creating table `employees`.`titles`


[root@test-machine01 tablebkp]#  myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/singledb  -v 3
** Message: 11:21:26.682: 4 threads created
** Message: 11:21:26.702: Creating database `employees`
** Message: 11:21:26.704: Creating table `employees`.`departments`
** Message: 11:21:26.778: Creating table `employees`.`dept_emp`
** Message: 11:21:26.811: Creating table `employees`.`dept_manager`
** Message: 11:21:26.822: Creating table `employees`.`employees`



[root@test-machine01 multidb]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/multidb  --source-db employees --database emp_test -v 3
** Message: 11:07:07.659: 4 threads created
** Message: 11:07:07.669: Creating database `emp_test`
** Message: 11:07:07.676: Creating table `emp_test`.`departments`
** Message: 11:07:07.798: Creating table `emp_test`.`dept_emp`
** Message: 11:07:07.867: Creating table `emp_test`.`dept_manager`
** Message: 11:07:07.879: Creating table `emp_test`.`employees`

Table Backup Import: Use below command to import table backup dumpfile.

[root@test-machine01 tablebkp]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/tablebkp --overwrite-tables  -v 3
** Message: 11:24:10.750: 4 threads created
** Message: 11:24:10.753: Dropping table or view (if exists) `employees`.`departments`
** Message: 11:24:10.835: Creating table `employees`.`departments`
** Message: 11:24:10.940: Dropping table or view (if exists) `employees`.`employees`
** Message: 11:24:10.989: Creating table `employees`.`employees`
** Message: 11:24:11.037: Dropping table or view (if exists) `employees`.`salaries`
** Message: 11:24:11.120: Creating table `employees`.`salaries`
** Message: 11:24:11.129: Thread 4 restoring `employees`.`departments` part 0
** Message: 11:24:11.129: Thread 3 restoring `employees`.`employees` part 0
** Message: 11:24:11.129: Thread 2 restoring `employees`.`salaries` part 0

Only Schema no data Import: Use the below command to import metadata only backup dumpfile.

[root@test-machine01 tablebkp]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/schemaonlydb  -v 3
** Message: 11:31:36.075: 4 threads created
** Message: 11:31:36.090: Creating database `employees`
** Message: 11:31:36.112: Creating table `employees`.`dept_emp`
** Message: 11:31:36.213: Creating table `employees`.`departments`
** Message: 11:31:36.239: Creating table `employees`.`dept_manager`
** Message: 11:31:36.274: Creating table `employees`.`employees`
** Message: 11:31:36.299: Creating table `employees`.`salaries`
** Message: 11:31:36.313: Creating table `employees`.`titles`
** Message: 11:31:36.327: Creating table `employees`.`dept_emp_latest_date`
** Message: 11:31:36.330: Creating table `employees`.`current_dept_emp`
** Message: 11:31:36.372: Thread 4 shutting down
** Message: 11:31:36.372: Thread 3 shutting down
** Message: 11:31:36.372: Thread 2 shutting down
** Message: 11:31:36.372: Thread 1 shutting down
** Message: 11:31:36.383: Creating table `employees`.`dept_emp_latest_date`
** Message: 11:31:36.475: Creating table `employees`.`current_dept_emp`
[root@test-machine01 tablebkp]#

Only data no schema Import : Use below command to import data only backup dumpfile.

[root@test-machine01 tablebkp]# myloader -u admin -p Root@1234 -S /u01/mysql-2/mysql.sock -t 4 --directory /u01/backup3/dataonly  -v 3
** Message: 11:33:32.793: 4 threads created
** Message: 11:33:32.793: Thread 1 restoring `employees`.`departments` part 0
** Message: 11:33:32.793: Thread 2 restoring `employees`.`dept_emp` part 0
** Message: 11:33:32.802: Thread 4 restoring `employees`.`dept_manager` part 0
** Message: 11:33:32.803: Thread 3 restoring `employees`.`employees` part 0
** Message: 11:33:32.950: Thread 1 restoring `employees`.`salaries` part 0

Part I : Mysql Logical Backup using mydumper – 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.