How to Dump and Restore Database in MYSQL on Rocky Linux 8.6

To Dump and Restore Database in MYSQL on Rocky Linux 8.6

Introduction:

MySQL is a relational database management system (RDBMS) developed by Oracle. It is based on structured query language (SQL) that provides numerous Unix utilities in a single executable file.

Installation procedure:

Step 1: Check the OS version by using the below command

[root@linuxhelp ~]# cat /etc/os-release 
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky Linux"
ROCKY_SUPPORT_PRODUCT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

Step 2: Need to login the MySQL shell by using the below command

[root@linuxhelp ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.30 Source distribution

Copyright (c) 2000, 2022, 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 '\h' for help. Type '\c' to clear the current input statement.

Step 3: List the Databases by using the below command

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

Step 4: Get in to the directory which need to take dump in the particular location by using the below command

[root@linuxhelp ~]# cd /tmp/

**Step 5: Need to take dump by using the below command **

[root@linuxhelp tmp]# mysqldump test_db > test_db.sql

Step 6: List and see whether dump taken in the particular location by using the below command

[root@linuxhelp tmp]# ls -la
total 239668
drwxrwxrwt. 17 root root      4096 Nov  5 01:25 .
dr-xr-xr-x. 17 root root       224 Oct  1 00:47 ..
drwx------.  2 root root        20 Nov  4 22:33 .esd-0
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .font-unix
drwxrwxrwt.  2 root root        30 Nov  4 22:33 .ICE-unix
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-colord.service-B6wwP7
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-fwupd.service-ov7CMI
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-geoclue.service-yOxt9h

drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-ModemManager.service-aCJBjD
drwx------.  3 root root        17 Nov  4 22:39 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-mysqld.service-PRePlW
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-rtkit-daemon.service-6bst2W
drwx------.  2 root root         6 Nov  2 05:09 Temp-a736c5a7-ff69-45f9-9090-0d316ca61e6b
-rw-r--r--.  1 root root 245404974 Nov  5 01:26 test_db.sql
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .Test-unix
drwx------.  2 root root         6 Nov  4 22:34 tracker-extract-files.0
drwx------.  2 root root         6 Nov  4 22:33 vmware-root_920-2731086625
-r--r--r--.  1 root root        11 Nov  4 22:33 .X0-lock
-r--r--r--.  1 gdm  gdm         11 Nov  4 22:33 .X1024-lock
drwxrwxrwt.  2 root root        29 Nov  4 22:33 .X11-unix
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .XIM-unix

Step 7: Next login MySQL shell by using the below command

[root@linuxhelp tmp]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.30 Source distribution

Copyright (c) 2000, 2022, 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 '\h' for help. Type '\c' to clear the current input statement.

Step 8: List the databases by using the below command

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

Step 9: Drop the databases by using the below command

mysql> drop database test_db;
Query OK, 179 rows affected (2.16 sec)

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

Step 10: Create the Databases to restore the database which we have taken dump by using the below command

mysql> create database sample;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

Step 11: After exiting from MySQL shell and go to the tmp directory which we have taken dump in the particular location by using the below command

[root@linuxhelp tmp]# ls -la
total 239668
drwxrwxrwt. 17 root root      4096 Nov  5 01:25 .
dr-xr-xr-x. 17 root root       224 Oct  1 00:47 ..
drwx------.  2 root root        20 Nov  4 22:33 .esd-0
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .font-unix
drwxrwxrwt.  2 root root        30 Nov  4 22:33 .ICE-unix
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-colord.service-B6wwP7
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-fwupd.service-ov7CMI
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-geoclue.service-yOxt9h
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-ModemManager.service-aCJBjD
drwx------.  3 root root        17 Nov  4 22:39 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-mysqld.service-PRePlW
drwx------.  3 root root        17 Nov  4 22:33 systemd-private-ea2736f3ff7d4cf3a4a142973437a160-rtkit-daemon.service-6bst2W
drwx------.  2 root root         6 Nov  2 05:09 Temp-a736c5a7-ff69-45f9-9090-0d316ca61e6b
-rw-r--r--.  1 root root 245404974 Nov  5 01:26 test_db.sql
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .Test-unix
drwx------.  2 root root         6 Nov  4 22:34 tracker-extract-files.0
drwx------.  2 root root         6 Nov  4 22:33 vmware-root_920-2731086625
-r--r--r--.  1 root root        11 Nov  4 22:33 .X0-lock
-r--r--r--.  1 gdm  gdm         11 Nov  4 22:33 .X1024-lock
drwxrwxrwt.  2 root root        29 Nov  4 22:33 .X11-unix
drwxrwxrwt.  2 root root         6 Nov  4 22:33 .XIM-unix

Step 12: Restore the Database by using the below command

[root@linuxhelp tmp]# mysql sample < test_db.sql

Step 13: After restoring login to the MySQL shell by using the below command

[root@linuxhelp tmp]# mysql;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22

Server version: 8.0.30 Source distribution

Copyright (c) 2000, 2022, 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 '\h' for help. Type '\c' to clear the current input statement.

Step 14: List the databases and viewing whether the database restored by using the below command

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

mysql> use sample;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Step 15: See inside the database and list the tables inside the database by using the below command

mysql> show tables;
+----------------------------------------+
| Tables_in_sample                       |
+----------------------------------------+
| access_tokens                          |

| activities                             |
| voice_validations                      |
| voices                                 |
| votes                                  |             
| youtube_jobs                           |
+----------------------------------------+
179 rows in set (0.00 sec)

Step 16: List the one single table for checking the database restored correctly by using the below command

mysql> select * from votes;
+-----+---------+----------+----------------------------+-----------+---------------------+---------------------+---------------------+
| id  | user_id | model_id | model_type                 | is_upvote | created_at          | updated_at          | deleted_at          |
+-----+---------+----------+----------------------------+-----------+---------------------+---------------------+---------------------+
|   1 |       1 |      721 | App\Model\Voice            |         1 | 2019-12-11 10:06:01 | 2020-03-03 12:53:54 | NULL                |
|   2 |       1 |      800 | App\Model\Voice            |         1 | 2019-12-11 10:59:31 | 2019-12-19 08:54:55 | NULL                |
|   3 |       1 |   138046 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 10:59:58 | 2019-12-21 09:42:51 | 2019-12-21 09:42:51 |
|   4 |       1 |   138188 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 11:00:19 | 2019-12-11 14:43:52 | 2019-12-11 14:43:52 |
|   5 |       1 |   138233 | App\Model\WordPhrasePiviot |         0 | 2019-12-11 11:01:35 | 2021-01-05 09:26:26 | NULL                |
|   6 |  117693 |        7 | App\Model\WordPhrasePiviot |       127 | 2019-12-11 13:55:19 | 2019-12-13 04:05:24 | NULL                |
|   7 |  117693 |   138141 | App\Model\WordPhrasePiviot |         0 | 2019-12-11 14:27:25 | 2019-12-13 04:42:40 | NULL                |
|   8 |  117693 |   138144 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 14:32:11 | 2020-03-03 13:59:53 | NULL                |
|   9 |  117693 |   138143 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 14:34:10 | 2019-12-16 12:03:52 | NULL                |
|  10 |  117693 |        6 | App\Model\WordPhrasePiviot |       127 | 2019-12-11 14:34:31 | 2019-12-13 04:58:27 | NULL                |
|  11 |  117693 |   138229 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 14:34:31 | 2019-12-11 14:34:34 | NULL                |

|  12 |  117693 |   138222 | App\Model\WordPhrasePiviot |         0 | 2019-12-11 14:43:24 | 2019-12-13 04:57:57 | 2019-12-13 04:57:57 |
|  13 |  117693 |   138150 | App\Model\WordPhrasePiviot |         1 | 2019-12-11 14:48:31 | 2019-12-11 14:48:33 | NULL                |
|  14 |       1 |   138158 | App\Model\WordPhrasePiviot |         1 | 2019-12-12 12:47:17 | 2021-04-12 15:23:16 | 2021-04-12 15:23:16 |
|  15 |       1 |   138045 | App\Model\WordPhrasePiviot |         0 | 2019-12-13 03:56:15 | 2019-12-21 10:19:39 | NULL                |
|  16 |       1 |   138044 | App\Model\WordPhrasePiviot |         1 | 2019-12-13 04:28:51 | 2019-12-17 11:59:08 | NULL                |
| 556 |    NULL |    64962 | App\Model\Word             |         1 | 2021-06-10 16:05:42 | 2021-06-10 16:05:42 | NULL                |
| 557 |    NULL |    64962 | App\Model\Word             |         1 | 2021-06-10 16:06:04 | 2021-06-10 16:06:04 | NULL                |
| 558 |    NULL |    64962 | App\Model\Word             |         1 | 2021-06-10 16:06:25 | 2021-06-10 16:06:25 | NULL                |
| 559 |    NULL |    64962 | App\Model\Word             |         2 | 2021-06-11 12:25:00 | 2021-06-11 12:25:00 | NULL                |
| 560 |    NULL |    64962 | App\Model\Word             |         3 | 2021-06-11 12:32:29 | 2021-06-11 12:32:29 | NULL                |
| 561 |    NULL |    64962 | App\Model\Word             |         4 | 2021-06-11 12:34:37 | 2021-06-11 12:34:37 | NULL                |
| 562 |    NULL |    64962 | App\Model\Word             |         4 | 2021-06-11 15:47:54 | 2021-06-11 15:47:54 | NULL                |
| 563 |    NULL |    82779 | App\Model\Word             |         5 | 2021-06-12 15:10:12 | 2021-06-12 15:10:12 | NULL                |
| 564 |    NULL |    64962 | App\Model\Word             |         1 | 2021-06-15 20:44:31 | 2021-06-15 20:44:31 | NULL                |
+-----+---------+----------+----------------------------+-----------+---------------------+---------------------+---------------------+
562 rows in set (0.01 sec)

mysql> Exit

Conclusion:

From this how to Dump and Restore Database in MYSQL on Rocky Linux 8.6 has come to an end.