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

The Ultimate Guide to Backing Up and Restoring Your MySQL/MariaDB Databases

Welcome back to Darren’s Tech Tutorials! If you run any application that relies on a database—from a simple WordPress blog to a massive enterprise system—you know that data is your most valuable asset. Losing it is simply not an option.

That’s why learning how to properly backup and restore your MySQL or MariaDB databases is a non-negotiable, fundamental skill. Luckily, the tools included with these database systems are incredibly powerful and surprisingly easy to use right from your command line.

In this comprehensive guide, we’re going to walk through the exact, step-by-step commands you need to safely dump your database to an external file and then how to reliably restore it when disaster strikes (or when you just need to migrate environments).

Ready to level up your database game? Let’s dive in!

Why Backup? The Power of the Database Dump

Whether you are using MySQL or MariaDB (MariaDB is a popular community-developed fork of MySQL), the process for backing up data is identical. We rely on the built-in command-line tool called mysqldump. This tool creates a clean, plain-text SQL file that contains all the necessary commands to recreate your database structure (tables, indices, etc.) and repopulate all the data.

Prerequisites

Before starting, ensure you have:

Access to your server’s command line (via SSH or terminal).
The username and password for a database user who has read access to the database you want to back up.
The exact name of the database you intend to dump.

Step 1: How to Backup (Dump) Your Database using `mysqldump`

The mysqldump command is designed to be run from your server’s command line, not inside the MySQL client itself.

The process involves telling mysqldump which database to target, providing credentials, and redirecting the output to a specified file.

Command Syntax

To dump a database, use the following structure:

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

Action Steps for Database Backup

Open your terminal and navigate to the directory where you want the backup file to be saved (e.g., your /home/user/backups directory).
Execute the backup command. Replace the bracketed placeholders with your actual details:
```
mysqldump -u darren_user -p tech_tutorials_db > tech_tutorials_backup_01_2024.sql
```
Enter your password. When you run the command, the system will prompt you for the password associated with darren_user. Type it in and press Enter.
Wait for completion. If the database is small, the command will complete instantly. If the database is large, it may take several minutes, and your terminal cursor will simply reappear when finished.

Congratulations! You now have a complete, portable SQL file that contains everything needed to reconstruct your database. Make sure you move this file off-site (to cloud storage or another machine) for true safety!

Step 2: Restoring Your Database from a Backup File

When it comes time to restore—either because you are migrating to a new server or you need to recover from an error—you will use the standard mysql client utility.

Restoring requires a slightly different approach than backing up. Instead of having the database tool output data, we need it to input data from the SQL file. We achieve this using the command-line redirection operator (<).

Prerequisite: Create the Destination Database

If you are restoring to a server where the database doesn’t already exist (like a brand new machine), you must create an empty database first.

Log into your MySQL client:
```
mysql -u root -p
```
Create the empty database (using the original name or a new name):
```
CREATE DATABASE tech_tutorials_db_new;
EXIT;
```

Action Steps for Database Restore

Ensure the SQL file is accessible. Make sure the backup file (tech_tutorials_backup_01_2024.sql) is on the server you are restoring to.
Execute the restore command. We use the mysql command directly, pointing it toward the destination database and using the redirection operator (<) to feed the SQL file content into the client:
```
mysql -u darren_user -p tech_tutorials_db_new < tech_tutorials_backup_01_2024.sql
```
- Note: We use the < symbol, which means “take the contents of this file and pipe it into the execution of the mysql command.”
Enter your password. When prompted, provide the password for the database user.
Wait for completion. The restore process will begin immediately, executing every command within the SQL file. Just like the backup, the terminal cursor will return when the restoration is fully complete.

Your database, including all its tables and data, is now fully restored to its state at the time of the backup!

Conclusion: Keep Your Data Safe and Sound!

Learning these two simple command-line operations (mysqldump and mysql with redirection) is the key to managing your own database safety. Whether you’re a developer, a system administrator, or a hobbyist running a server, regular, reliable backups are the foundation of a stable environment.

We hope this tutorial made the process crystal clear. Now go practice backing up your non-critical environments so you’re ready when it truly counts!

For more command-line tips and tricks—especially useful if you’re managing databases on Linux—don’t forget to grab your free copy of our Linux cheat sheet!

If this tutorial helped you keep your data safe, please let us know! Give this post a share, drop a comment with your favorite backup tip, and don’t forget to like the video and subscribe to Darren’s Tech Tutorials for more easy-to-follow guides! Happy coding!