Scripting

How to Back Up and Restore MySQL and MariaDB Using Automysqlbackup

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:

CREATE DATABASE maria_db;

Now, let us make a table named “lipi_tbl”.

CREATE TABLE lipi_tbl (UserID INT AUTO_INCREMENT PRIMARY KEY,

-> UserName VARCHAR(50),

-> IsActive BOOL);

After creating the table, we insert the values into the column and then select the table name for show.

mysql> CREATE TABLE lipi_tbl (UserID INT AUTO_INCREMENT PRIMARY KEY,

-> 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.

# aptitude install automysqlbackup

In order to download and run the installation script, let’s start by making a working directory inside of “/opt”:

# mkdir /opt/automysqlbackup

# 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:

# cd /etc/automysqlbackup/

# 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:

# automysqlbackup /etc/automysqlbackup/myserver.conf

Output:

# automysqlbackup /etc/automysqlbackup/myserver.conf

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.

# cd /var/backup/db/automysqlbackup

A quick look at the daily directory reveals that AutoMySQLBackup is successful:

# pwd

# ls -lR daily

Set the crontab entry as shown in the following to take an AutoMySQLBackup every day at a certain time:

30 05 * * * /usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

Restore or Bring Back a MySQL Backup

Now, let’s intentionally delete the “mariadb_db” database:

# mysql -u root -p

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:

MariaDB [(none)]> CREATE DATABASE maria_db

Query OK, 1 row affected (0.00 sec)

Then, specify where the backup must be restored.

# cd /var/backup/daily/maria_db

# 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)]> USE maria_db;

MariaDB [(none)]> SELECT * FROM lipi_tb1;

Output:

# mysql -u root -p maria_db < daily_maria_db_2023-06-11_11h08m_Sunday.sql.gz

//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.

Similar Posts