MySQL BACKUP, RESTORE Database

You are strongly advised to check corresponding Youtube video.

How to backup MySQL database

1. Open the Command Prompt or PowerShell on your Windows 10 machine.

2. Navigate to the directory where the MySQL installation is located. 

3. Once you are in the MySQL installation directory, execute the following command to backup the database to a SQL file: 

   mysqldump -u [username] -p [database_name] > [backup_file_name.sql]
   

   Replace [username] with your MySQL username, [database_name] with the name of the database you want to backup, and [backup_file_name.sql] with the desired name of the backup file.

   You will be prompted to enter your MySQL password after executing the command.

4. The backup file will be created in the MySQL installation directory. You can copy the backup file to a different location to store it. 

Backup, Step by Step

We will backup "corp" database.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| corp               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

In CMD, go to location where MySQL is installed. 

Microsoft Windows [Version 10.0.18363.959]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>cd "C:\Program Files\MySQL Server 8.0\bin\"

C:\Program Files\MySQL Server 8.0\bin>mysqldump -u root -p corp > CORPARCH.sql
Enter password: *****

C:\Program Files\MySQL Server 8.0\bin>

cd "C:\Program Files\MySQL Server 8.0\bin\": This command changes the current directory to C:\Program Files\MySQL Server 8.0\bin\ where the mysqldump utility is located.

mysqldump -u root -p corp > CORPARCH.sql: This command runs the mysqldump utility with the username root and the database name corp. It then redirects the output to a file named CORPARCH.sql. This creates a backup of the corp database as a SQL script file.

Enter password: *****: This prompts the user to enter the password for the root user. The password is not visible when typing for security reasons.

C:\Program Files\MySQL Server 8.0\bin>: This is the command prompt after the mysqldump command has completed. 

C:\Program Files\MySQL Server 8.0\bin>dir CORP*
 Volume in drive C is New Volume

 Directory of C:\Program Files\MySQL Server 8.0\bin

07/24/2020  05:22 PM             5,920 CORPARCH.sql
               1 File(s)          5,920 bytes
               0 Dir(s)  78,735,020,032 bytes free

C:\Program Files\MySQL Server 8.0\bin>

Directory of C:\Program Files\MySQL Server 8.0\bin : This line indicates that the directory of the command prompt is "C:\Program Files\MySQL Server 8.0\bin".

07/24/2020 05:22 PM 5,920 CORPARCH.sql : This line shows the details of the file "CORPARCH.sql" which was created on July 24, 2020 at 5:22 PM and has a size of 5,920 bytes. 

How to Drop and restore MySQL Database 

mysql> DROP DATABASE Corp;
Query OK, 4 rows affected (1.04 sec)

mysql>

The code is dropping the "Corp" database in MySQL. 

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

mysql>

Database "Corp" is no more. 

Restoring Database "Corp"

mysql> CREATE DATABASE Corp;
Query OK, 1 row affected (0.13 sec)

mysql>

Previous code creates a new database called "Corp" in MySQL. 

mysql> USE Corp;
Database changed
mysql> SHOW TABLES FROM Corp;
Empty set (0.00 sec)

mysql>

The code above switches to the database named "Corp" using the command USE Corp.

Then, it tries to display the tables in the currently selected database using the command SHOW TABLES FROM Corp.

Since the database is newly created and no tables have been created yet, the output is an empty set. 

C:\Program Files\MySQL Server 8.0\bin>mysql -u root -p corp < CORPARCH.sql
Enter password: *****

C:\Program Files\MySQL Server 8.0\bin>

This code runs the mysql client from the command line and connects to a MySQL server as the root user, prompting for the user's password.

It then takes a backup file called "CORPARCH.sql" located in the same directory and uses it to restore the "corp" database.

The "<" character is a shell command for input redirection, which means the contents of the backup file will be used as input for the mysql command.

Database Restored 

mysql> SHOW TABLES FROM Corp;
+------------------------+
| Tables_in_corp         |
+------------------------+
| documents              |
| internalcontrol        |
| prices_higher_than_100 |
| stores                 |
+------------------------+
4 rows in set (0.04 sec)

mysql>

Restoring worked. The command SHOW TABLES FROM Corp is used to display all the tables in the "Corp" database. 

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+-----------------+
| s_id | city   | store_name | product         | available | price | ShipCorp        |
+------+--------+------------+-----------------+-----------+-------+-----------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | Speedy Gonzales |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | Speedy Gonzales |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | Speedy Gonzales |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | Speedy Gonzales |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | Speedy Gonzales |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | Speedy Gonzales |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | Speedy Gonzales |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | Speedy Gonzales |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | Speedy Gonzales |
|   10 | xxx    | yyy        | ppp             |      5421 |   500 | Speedy Gonzales |
|   11 | bla    | bla        | bla             |      1234 |   350 | Daffy Duck      |
+------+--------+------------+-----------------+-----------+-------+-----------------+
11 rows in set (0.00 sec)

mysql>

All data preserved.

You are strongly advised to check corresponding Youtube video: