How to Backup and Restore MySQL Database on CentOS/RHEL

In this guide, we will delve into the process of backing up and restoring a MySQL database on a Linux server, specifically focusing on CentOS and RHEL servers. This tutorial is based on practical experience and thorough testing, ensuring that the steps provided are reliable and effective.

The central tool we will be using is MySQLDump, a database backup program originally developed by Igor Romanenko. MySQLDump is a versatile utility that can be used to dump a database or a collection of databases for backup or transfer purposes. While it’s primarily used with MySQL servers, it can also work with other SQL servers. The dump typically contains SQL statements to create the table, populate it, or both. However, MySQLDump can also generate files in CSV, other delimited text, or XML format. You can learn more about MySQLDump on the official documentation page.

To use MySQLDump, you need to log on to the system running the MySQL Database. This can be done remotely using Telnet if you don’t have physical access to the machine. In this tutorial, we will assume that the MySQL server has been installed with the following version/release:

• Version: 5.1.52
• Release : 1.el6_0.1

Name        : mysql
Arch        : i686
Version     : 5.1.52
Release     : 1.el6_0.1

Before we dive into the steps, it’s important to understand the terminology we will be using:

: This refers to the existing database that you want to back up.
: This is the name of the backup file you will create. This is optional and can be defined by the user.
Now, let’s move on to the steps for backing up and restoring your MySQL database.

Backing Up Your MySQL Database

The first step is to log in to MySQL to check the existing databases. You can do this by entering the following command:

mysql -u root -p

You will be prompted to enter your password. After logging in, you can view the available databases by entering the following command:

show databases;

Example:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phpmyadmin         |
+--------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

To back up a specific database to a .sql file, use the following command:

mysqldump -u root -p <databasename> > <databasebackupfiles>.sql

You will be prompted to enter your MySQL password. For example, if you want to back up a database named “mysql” to a file named “mysql19022012.sql”, you would use the following command:

mysqldump -u root -p mysql > mysql19022012.sql

You can view the backed-up file by using the ls command:

ls | grep mysql

You can also view the content of the mysql19022012.sql file by using the more command:

more mysql19022012.sql

Restoring Your MySQL Database

To restore the backed-up database to a new database, use the following command:

mysql -u root -p <mysqlpassword> <databasename> < <databasebackupfiles>.sql

In this command, is your MySQL password, is the name of the database that will be restored, and is the backed-up database file, which is normally a .sql file.

First, create a new database. Forexample, if you want to create a new database named “mysqlnew”, you would use the following command:

CREATE DATABASE mysqlnew;

To restore the mysql19022012.sql backed-up file to the database named “mysqlnew”, you would use the following command:

mysql -u root -p password mysqlnew < mysql19022012.sql

Or, if you prefer to be prompted for your password, you can use the following command:

mysql -u root -p mysqlnew < mysql19022012.sql

Commands Mentioned

• mysql -u root -p – This command is used to log in to your MySQL server.
• show databases; – This command is used to display the databases available on your MySQL server.
• mysqldump -u root -p <databasename> > <databasebackupfiles>.sql – This command is used to back up a specific database to a `.sql` file.
• ls | grep mysql – This command is used to display the backed-up file.
• more mysql19022012.sql – This command is used to view the content of the backed-up file.
• CREATE DATABASE mysqlnew; – This command is used to create a new database.
• mysql -u root -p password mysqlnew < mysql19022012.sql – This command is used to restore the backed-up database to the new database.

Conclusion

Backing up and restoring a MySQL database on a Linux server is a crucial task for any webmaster or website administrator. By using the MySQLDump utility and following the steps outlined in this guide, you can ensure that your data is safely backed up and can be restored when needed.

Remember, regular backups are an essential part of maintaining a robust and reliable database system. Whether you’re running a small blog or a large e-commerce site, having up-to-date backups can save you from potential data loss and downtime.

For more in-depth information about MySQL and other web server technologies, check out our guides on the best web servers, Apache, Nginx, and LiteSpeed. If you’re interested in different hosting options, we also have articles on dedicated server, VPS server, cloud hosting, and shared hosting.

FAQ

1. What is MySQLDump?

   MySQLDump is a database backup program originally developed by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer purposes. It can work with MySQL servers and other SQL servers, and it can generate files in CSV, other delimited text, or XML format.

2. How do I back up a MySQL database?

   You can back up a MySQL database using the MySQLDump utility. The command for this is ‘mysqldump -u root -p <databasename> > <databasebackupfiles>.sql’, where <databasename> is the name of the database you want to back up, and <databasebackupfiles> is the name of the backup file you want to create.

3. How do I restore a MySQL database?

   To restore a MySQL database, you first need to create a new database using the ‘CREATE DATABASE <databasename>’ command. Then, you can restore the backed-up database to the new database using the ‘mysql -u root -p <mysqlpassword> <databasename> < <databasebackupfiles>.sql’ command, where <mysqlpassword> is your MySQL password, <databasename> is the name of the new database, and <databasebackupfiles> is the backed-up database file.

4. What is the purpose of the ‘show databases’ command?

   The ‘show databases’ command is used to display the databases available on your MySQL server. This can be useful to confirm the existence of a specific database before attempting to back it up or restore it.

5. What does the ‘ls | grep mysql’ command do?

   The ‘ls | grep mysql’ command is used to display the backed-up file. The ‘ls’ command lists the files in the current directory, and the ‘grep mysql’ part filters the output to only show files that contain the word ‘mysql’.