Data loss is a terrible scenario for any organization that relies on databases to hold a crucial information. Whether it’s due to an unintentional loss, hardware failure, or software corruption, a backup solution is essential to ensure the data integrity and speedy recovery. MySQL and MariaDB offer robust data storing and retrieval capabilities. AutoMySQLBackup provides a dependable and effective solution to automate the backup and restore process to protect our databases.
Let us discuss how to use the AutoMySQLBackup to create the automated backups of our MySQL and MariaDB databases, as well as restore them when necessary.
Creating the Database and Table in MySQL
Before doing anything, we have to install MySQL in our Linux system. After that, follow the further steps.
We can create the database in MySQL or MariaDB. Here, we create the database in MySQL.
Create the database first:
Now, let us make a table named “lipi_tbl”.
-> UserName VARCHAR(50),
-> IsActive BOOL);
After creating the table, we insert the values into the column and then select the table name for show.
-> UserName VARCHAR(50),
-> IsActive BOOL);
mysql> INSERT INTO lipi_tbl (username,isactive) VALUES ('Lipi', 1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO lipi_tbl (username,isactive) VALUES ('Ram', 2);
Query OK, 1 row affected (0.15 sec)
mysql> select * from lipi_tbl;
+--------+----------+----------+
| UserID | UserName | IsActive |
+--------+----------+----------+
| 1 | Lipi | 1 |
| 2 | Ram | 2 |
+--------+----------+----------+
2 rows in set (0.00 sec)
Installing AutoMySQLBackup in our Linux System
Let us install AutoMySQLBackup at first.
In order to download and run the installation script, let’s start by making a working directory inside of “/opt”:
# cd /opt/automysqlbackup
# wget http://ufpr.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
# tar zxf automysqlbackup-v3.0_rc6.tar.gz
# ./install.sh
The AutoMySQLBackup configuration file named “myserver.conf” is found at “/etc/automysqlbackup”. Let’s look at the most important configuration directives:
# ls
automysqlbackup.conf '*.conf_converted' LICENSE myserver.conf README
# vim myserver.conf
After that, start configuring AutoMySQLBackup as per our need. Once we are complete with the configuration, just save it. It is strongly recommended to read the README file located in “/etc/automysqlbackup/README”.
Backup of MySQL Database
When we’re ready, execute the program with the configuration file as an argument:
Output:
Parsed config file " /etc/automysqlbackup/automysqlbackup.conf"
# Checking for permissions to write to folders:
base folder /var ... exists ... ok.
backup folder /var/backup ... creating ... success.
Now, navigate to the “/var/backup” backup directory to inspect the backup file.
A quick look at the daily directory reveals that AutoMySQLBackup is successful:
# ls -lR daily
Set the crontab entry as shown in the following to take an AutoMySQLBackup every day at a certain time:
Restore or Bring Back a MySQL Backup
Now, let’s intentionally delete the “mariadb_db” database:
Enter password:
MariaDB [(none)]> show databases
+--------------------+
| Database |
+--------------------+
| information_schema |
| maria_ |
| maria_db |
| mysql |
| postgresql_db |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> DROP DATABASE maria_db
Query OK, 1 row affected (0.06 sec)
MariaDB [(none)]> show databases
+--------------------+
| Database |
+--------------------+
| information_schema |
| maria_ |
| mysql |
| postgresql_db |
+--------------------+
5 rows in set (0.00 sec)
Let us recreate it and restore the backup. Enter the following command into the MariaDB prompt:
Query OK, 1 row affected (0.00 sec)
Then, specify where the backup must be restored.
# ls
daily_maria_db_2023-06-11_11h08m_Sunday.sql.gz
The backup file should then be restored into the “maria_db” database.
MariaDB [(none)]> SELECT * FROM lipi_tb1;
Output:
//Enter your Password
Enter password:
MariaDB [(none)]> show databases
+--------------------+
| Database |
+--------------------+
| information_schema |
| maria_ |
| maria_db |
| mysql |
| postgresql_db |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use maria_db
Database changed
//Showing our table which we created before
MariaDB [maria_db]> select * from lipi_tbl;
+--------+----------+----------+
| UserID | UserName | IsActive |
| 1 | Lipi | 1 |
Conclusion
AutoMySQLBackup automates the backup and restore, ensuring the data availability while minimizing the human error. We can protect our MySQL and MariaDB databases with this excellent approach. Integrate AutoMySQLBackup into our routine to improve the data protection and lower the chance of data loss. Secure our databases right away to ensure that our critical data is safely backed up and is rapidly recoverable.
