How to Backup and Restore MySQL Database on CentOS/RHEL

Share this Article :

In this post, i will share on how to backup and restore MySQL database on Linux server. I have tested this on my CentOS and RHEL servers.

MySQLDump — A Database Backup Program, Click for more info.
The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

What is MySQLDump and how to use it :
The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. To use the MySQLDump utility it is required to logon to the System running the MySQL Databse. You can use Telnet to remotely logon to the system if you don’t have the physical access to the machine.

Also Read :   How to Secure your MySQL On VPS or Dedicated Server

Assumed that the MySQL server has been installed with the version/release as below :

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

Login to mysql to check the existing databases :

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Show available databases :

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

mysql> exit

1. Backup MySQL database to <databasebackupfiles>.sql

Also Read :   How to Install and Securing MySQL Database Server on CentOS 6.3

<databasename> = Target existing database name
<databasebackupfiles> = Preferred backed up file name (Optional and user defined)

# mysqldump -u root -p <databasename> > <databasebackupfiles>.sql
Enter password: <Type your mysql password and press enter>

1.1 As Example, i will back up the database named “mysql” to mysql19022012.sql file. This will taking backed up all the tables including the data :

[[email protected] ~]# mysqldump -u root -p mysql > mysql19022012.sql
Enter password:

1.2 View the backed up file :

[[email protected] ~]# ls | grep mysql

1.3 View the content on mysql19022012.sql :

[[email protected] ~]# more mysql19022012.sql

2. Restore the backed up database named “mysql19022012.sql” to new database named “mysqlnew” as below :

Also Read :   How to Install Adminer on CentOS 6.2 Server

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

<mysqlpassword> = MySQL password
<databasename> = Database name that will be restore
<databasebackupfiles> = Backed up database file, normally .sql file

2.1 Create the new database mysqlnew :

mysql> CREATE DATABASE mysqlnew;
Query OK, 1 row affected (0.01 sec)

2.2 As example, i will restore the mysql19022012.sql backed up file to database named “mysqlnew” :

[[email protected] ~]# mysql -u root -p password mysqlnew < mysql19022012.sql


[[email protected] ~]# mysql -u root -p mysqlnew < mysql19022012.sql
Enter password:

Leave a Reply