How to Dump and Restore Database in MYSQL on Rocky Linux 8.6
7487
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.
Comments ( 0 )
No comments available