How to backup and restore a MySQL (MariaDB) database in CentOs Linux

Mastering MariaDB: The Essential Guide to Backup and Restore on CentOS

Welcome back to Darren’s Tech Tutorials! If you manage databases, you know that the single most important habit you can cultivate is regular, reliable backup. Data loss is a nightmare, but armed with the right commands, you can ensure your MySQL or MariaDB data is safe and sound.

In this comprehensive guide, we’re walking through the precise steps needed to back up a database (specifically, our Moodle database on a CentOS system) using the mighty mysqldump utility, and then restoring it flawlessly from the resulting SQL file. This is a foundational skill for any system administrator or developer!

Getting Started: Accessing Your Database

Before we can create a backup, we need to access the MariaDB environment and identify the database we plan to secure.

Step 1: Log into MariaDB

Open your terminal and use the MySQL client to log in as your administrative user (we’ll use root here, but substitute your own user if necessary).

mysql -u root -p

You will be prompted to enter your password. Once successful, you’ll be at the MariaDB [(none)]> prompt.

Step 2: Identify the Target Database

Let’s confirm the databases available and identify the one we want to secure.

SHOW DATABASES;

In our example, we are targeting the Moodle database. We can take a quick peek inside to verify its contents:

USE Moodle;
SHOW TABLES;

If you see a long list of tables, you know your database is live and ready for backup! Now, exit the SQL shell to return to the Linux command prompt.

exit

Creating a Database Backup with mysqldump

The mysqldump utility is the standard tool for creating logical backups of MySQL and MariaDB databases. It exports the database structure and data into a text file containing SQL commands.

Step 3: Execute the Backup Command

From your Linux terminal prompt, use the following syntax. We specify the database name (moodle) and use the redirection operator (>) to pipe the output directly into a new file named moodle_back.sql.

mysqldump -u root -p moodle > moodle_back.sql

When prompted, enter your database password. The process will run silently, which means if you don’t see any error messages, the backup was successful!

Step 4: Verify the Backup File

We should always verify that the backup file was actually created and contains data.

First, check if the file exists:

ls

You should see moodle_back.sql listed.

Next, let’s examine the last 10 lines of the file just to confirm it contains SQL content:

tail -n 10 moodle_back.sql

If the output shows lines of SQL insert statements or other definitions, your backup is good to go!

Preparing for the Database Restore (The Test)

To prove our restore process works flawlessly, we’re going to simulate a disaster: dropping the database and then rebuilding it from our backup file.

Step 5: Drop and Recreate the Database Shell

Log back into your MariaDB shell:

mysql -u root -p

Now, we will drop the existing Moodle database:

DROP DATABASE Moodle;

If we run SHOW DATABASES;, Moodle will be gone.

Before restoring, we need to create an empty shell for the data to be poured into:

CREATE DATABASE Moodle;
SHOW DATABASES;

The database structure is empty, confirmed by trying to use it:

USE Moodle;
SHOW TABLES;

This should show an empty set. Excellent! Exit the MariaDB shell once more.

exit

Restoring the Database from the Backup File

Now for the critical step: restoring all that lost data back into the fresh Moodle database we just created.

Step 6: Execute the Restore Command

Unlike mysqldump, which uses the greater-than sign (>) to output data, the restore process uses the less-than sign (<) to input data from the SQL file into the running MySQL service.

Execute the restore command from your Linux prompt:

mysql -U root -P moodle < moodle_back.sql

Note on Hosts: We omitted the -H localhost here as it often defaults correctly, but if you are restoring to a remote host, you would specify the IP address or hostname using -H.

Type your database password when prompted. The restoration process will now run. Depending on the size of your database, this may take a moment.

Step 7: Final Verification

Once the restore command finishes, let’s log back in one last time to confirm that all our data and tables are back where they belong.

mysql -u root -p

Inside the MariaDB shell:

USE Moodle;
SHOW TABLES;

If you see all your tables magically reappear, congratulations! You have successfully mastered the MariaDB backup and restore process.

Summary and Next Steps

You’ve done it! You learned how to use mysqldump to create a secure backup file and how to use the input redirection operator to restore that data into a fresh database instance. This is a robust and fundamental skill that guarantees the safety of your data.

Now that you know how easy it is to manage your data, put this knowledge to the test! Try backing up a few test databases on your server.

If you found this tutorial helpful, be sure to Like this post and Subscribe to Darren’s Tech Tutorials for more clear, actionable guides! Drop a comment below if you have any questions about MySQL or MariaDB backups!