MySQL Backup/Restore a database using mysqldump

MySQL - backup & restore a database using mysqldump

In this blog, we will some basic commands to manage MySQL database. In addition to the actual objective which is backup & restore a single mysql db using mysqldump utility.

Platform: Ubuntu

MySQL commands:

to stop the mysql db:

service mysql status
service mysql stop
service mysql status

to start the mysql db:

service mysql status
service mysql start
service mysql status

To connect to the db:

mysql --user=root --password
>>enter password in prompt

To create db:

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

To list the dbs:

show databases;

Output:

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

To create a table:

mysql> use test20apr23
Database changed

mysql> create table test1 (id integer,name char);
Query OK, 0 rows affected (0.02 sec)

mysql>

To show the tables:

mysql> show tables;
+-----------------------+
| Tables_in_test20apr23 |
+-----------------------+
| test1                 |
+-----------------------+
1 row in set (0.00 sec)

To load records to table:

mysql> insert into test1 values(1,'r'),(2,'q');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

To perform commit:

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

To select the table:

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | r    |
|    2 | q    |
+------+------+
2 rows in set (0.00 sec)
mysql>

To backup the db using mysqldump [the native one]

Command: 

mysqldump -u root -p test20apr23 >./test20apr23_backup.sql

vagrant@vubuntu-mysql-sa1:~/dba/mysqldmptest$ mysqldump -u root -p test20apr23 >./test20apr23_backup.sql
Enter password:
vagrant@vubuntu-mysql-sa1:~/dba/mysqldmptest$

View the content of the backup:

vagrant@vubuntu-mysql-sa1:~/dba/mysqldmptest$ cat test20apr23_backup.sql
-- MySQL dump 10.13  Distrib 5.7.33, for Linux (x86_64)
--
-- Host: localhost    Database: test20apr23
-- ------------------------------------------------------
-- Server version       5.7.33-0ubuntu0.16.04.1
...
--
-- Table structure for table `test1`
--
DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test1`
--
LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES (1,'r'),(2,'q');
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
...

Now let us drop and restore the db:

Drop routine:

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

mysql> drop database test20apr23;
Query OK, 1 row affected (0.00 sec)

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

Restore Steps:

create the db:

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

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

Restore the database:

Command: 

mysql -u root -p test20apr23 <./test20apr23_backup.sql

vagrant@vubuntu-mysql-sa1:~/dba/mysqldmptest$ mysql -u root -p test20apr23 <./test20apr23_backup.sql
Enter password:
vagrant@vubuntu-mysql-sa1:~/dba/mysqldmptest$
Verify the database:

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

mysql> use test20apr23;
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

mysql> show tables;
+-----------------------+
| Tables_in_test20apr23 |
+-----------------------+
| test1                 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | r    |
|    2 | q    |
+------+------+
2 rows in set (0.00 sec)
mysql>

The DB is restored successfully.

This completes this blog.

Thank you!