How To Backup and restore MySQL database on Debian 11.3

To Backup and restore MySQL database on Debian 11.3

Introduction:

A simple and easy method for creating MySQL backups is to use the MySQL dump command. This command will create a simple. SQL file of an existing database, which can then be restored to any other empty MySQL database.

Step 1: Install MariaDB database by using the below command

root@linuxhelp:~# apt install mariadb-server -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following package was automatically installed and is no longer required:
  libonig5
Use 'apt autoremove' to remove it.
The following NEW packages will be installed:
  mariadb-server
0 upgraded, 1 newly installed, 0 to remove and 10 not upgraded.
Need to get 35.3 kB of archives.
After this operation, 72.7 kB of additional disk space will be used.
Get:1 http://deb.debian.org/debian bullseye/main amd64 mariadb-server all 1:10.5.15-0+deb11u1 [35.3 kB]
Fetched 35.3 kB in 0s (269 kB/s)          
Selecting previously unselected package mariadb-server.
(Reading database ... 286857 files and directories currently installed.)
Preparing to unpack .../mariadb-server_1%3a10.5.15-0+deb11u1_all.deb ...
Unpacking mariadb-server (1:10.5.15-0+deb11u1) ...
Setting up mariadb-server (1:10.5.15-0+deb11u1) ...

Step 2: Start the MariaDB server by using the below command

root@linuxhelp:~# systemctl start mariadb

Step 3: Enable the MariaDB server by using the below command

root@linuxhelp:~# systemctl enable mariadb
Synchronizing state of mariadb.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mariadb
root@linuxhelp:~# systemctl status mariadb
● mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor prese>
     Active: active (running) since Thu 2022-05-26 21:27:24 IST; 4h 36min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 833 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 9 (limit: 4620)
     Memory: 111.6M
        CPU: 10.176s
     CGroup: /system.slice/mariadb.service
             └─833 /usr/sbin/mariadbd


May 26 21:27:24 linuxhelp mariadbd[833]: 2022-05-26 21:27:24 0 [Note] Added new>
May 26 21:27:24 linuxhelp mariadbd[833]: 2022-05-26 21:27:24 0 [Note] /usr/sbin>
May 26 21:27:24 linuxhelp mariadbd[833]: Version: '10.5.15-MariaDB-0+deb11u1'  >
May 26 21:27:24 linuxhelp systemd[1]: Started MariaDB 10.5.15 database server.
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[871]: Looking for 'mysql' as:>
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[871]: Looking for 'mysqlcheck>
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[871]: This installation of Ma>
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[871]: There is no need to run>
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[871]: You can use --force if >
May 26 21:27:24 linuxhelp /etc/mysql/debian-start[1022]: Checking for insecure >

Step 4: Install MySQL secure installation by using the below command

root@linuxhelp:~# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Step 5: Open the MariaDb Database by using the below command

root@linuxhelp:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

Step 6: Create a new MySQL database by using the below command

MariaDB [(none)]> CREATE DATABASE TEST;
Query OK, 1 row affected (0.000 sec)

Step 7: check the available database by using the below command

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| MYSQL              |
| TEST               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

Step 8: Use the create database by using the below command

MariaDB [(none)]> USE TEST;
Database changed
MariaDB [TEST]>
MariaDB [TEST]>

Step 9: Create new table in database by using the below command

MariaDB [TEST]> create table sample (a int,b int,c int);
Query OK, 0 rows affected (0.011 sec)

Step 10: Check the value from tables by using the below command

MariaDB [TEST]> select * from sample;
Empty set (0.001 sec)

Step 11: Insert the value from tables by using the below command

MariaDB [TEST]> insert into sample (a,b,c) values (1,2,3);
Query OK, 1 row affected (0.001 sec)

Step 12: Check the value from tables by using the below command

MariaDB [TEST]> select * from sample;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.000 sec)

Step 12: Exit the database by using the below command

MariaDB [TEST]> exit
Bye

Step 13: Backup the MySQL database by using the below command

root@linuxhelp:~# mysqldump -u root -p TEST > test.sql
Enter password:

Step 14: List the MySQL database backup by using the below command

root@linuxhelp:~# ls
test.sql

Step 15: Delete the MySQL database by using the below command

root@linuxhelp:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11


MariaDB [TEST]> DROP DATABASE TEST;
Query OK, 1 row affected (0.002 sec)

Step 16: To verify the database use the below command

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| MYSQL              |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> EXIT
Bye

Step 17: Restore MySQL database by using the below command

root@linuxhelp:~# mysql -u root -p TEST < test.sql
Enter password:

Step 18: Open database and check the Restore database by using the below command

root@linuxhelp:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

MariaDB [(none)]> USE TEST
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

MariaDB [TEST]> SELECT * FROM TEST;
MariaDB [TEST]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| MYSQL              |
| TEST               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [TEST]> show tables;
+----------------+
| Tables_in_TEST |
+----------------+
| sample         |
+----------------+
1 row in set (0.000 sec)

MariaDB [TEST]> select * from sample;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.000 sec)

We have reached the end of this article. In this guide, we have walked you through the steps required to. Your feedback is much welcome.