How to Back Up & Restore a MySQL Database

Data is critical to any organization, making the data security a primary issue. MySQL, a widely used and trusted relational database technology, is critical in data management. As databases get more complex and massive, the importance of implementing the solid backup and restore techniques cannot be overstated.

This article provides a step-by-step instructions and necessary commands to efficiently backup and restore a MySQL database. With MySQL as the foundation and a solid backup and restoration strategy in place, the organizations can confidently manage their data, knowing that their information is secure and is quickly recoverable if necessary.

Backup from the Command-Line with Mysqldump

Before performing any operation, we should install MySQL in Linux.

1. Understanding the Database Backup

It’s important to understand the various backup options before diving into the backup process. Logical backups and physical backups are MySQL’s two main options. Physical backups record the database’s binary representation, whereas logical backups produce a text-based version of the database.

2. Performing a Logical Backup

The mysqldump software is frequently used to make a logical backup of a MySQL database.

The basic syntax:

sudo mysqldump -u [user] -p [database_name] > [filename].sql

“mysqldump”: Mysqldump is a command-line tool in MySQL that is used to make logical backups by dumping a database’s contents into a file. Using this tool, we can make a portable SQL-format representation of the database.
“[database_name]”: Enter the database name to backup. The name of our MySQL database should be substituted with [database_name].
The “>” indicates the output.
The “[filename]” is the file name of the output file.

Here are some more examples:

Use the following command to back up a whole database management system:

mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

Multiple databases can be included in the output “sql” file.

sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql

3. File Restoration from a Physical Backup

Restoring from a physical backup require extracting the backup file and uploading it to the MySQL server. Run the following command to restore a physical backup:

tar -zxvf [backup_file.tar.gz] -C [target_directory]

innobackupex --apply-log [target_directory]

systemctl stop mysql

rm -rf /var/lib/mysql/*

innobackupex --copy-back [target_directory]

chown -R mysql:mysql /var/lib/mysql/

systemctl start mysql

How to Use Mysqldump to Restore MySQL

1. Create a New Database on the System

Create a new MySQL database on the system that hosts the database.

This generates the foundation file that mysqldump utilizes to load the data. Because the dump file contains the rebuilding procedures, we simply need to create an empty database.

2. Restore MySQL Dump

The MySQL command-line client is used to recover a logical backup.

mysql -u [username] -p [database_name] < [backup_file.sql]

This command imports the backup file’s data into the selected database.

MySQL Backup and Restore with phpMyAdmin

1. Make a Backup of Our MySQL Database

Launch “phpMyAdmin”. Click the database in the directory tree on the left.
The directory structure should now be visible in the right-hand pane. Also note that all of the assets under the primary database are highlighted.
Now, select “Export”.
There will be a section entitled “Export Method.” Select “Custom” to add the individual tables or other specific features. Leave the “Format” field set to SQL.
Press the “Go” button. If we choose “Quick”, a copy of database will be downloaded in the user-mentioned path. We should save it somewhere secure.

2. Clear the Old Database Information

It is better to remove the old data. When we restore, any existing data is not overwritten. This can result in duplicate tables which can lead to errors and conflicts.

Start the “phpMyAdmin” program. After that, select the desired database to restore.
Near the bottom, click the “Check All” box. Then, select “Drop” in the menu.
The tool should ask us to confirm if we want to proceed. This deletes all current data, making room for our repair.

3. Restore Our Backup MySQL Database

Select “Import” from the menu at the top.
The “File to import” part is the first one. There is a button which is labelled as “Choose File” next to the “Browse our computer” line a few lines down. Toggle that switch.
Locate the export file that we wish to recover using the dialogue box using the location where we saved it. Leave every set at its default value.

Conclusion

Backing up and restoring a MySQL database is a must-have skill for any database administrator or developer. We may assure the safety and recoverability of our valuable data by following the steps and utilizing the right commands provided in this detailed tutorial to save our organization from potential loss and downtime.