How to install Backup and Restore MySQL Database on CentOS/RedHat

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.
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 :

[root@centos64 ~]# 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
Bye

1. Backup MySQL database to <databasebackupfiles>.sql
<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 :

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

1.2 View the backed up file :

[root@centos64 ~]# ls | grep mysql
mysql19022012.sql

1.3 View the content on mysql19022012.sql :

[root@centos64 ~]# more mysql19022012.sql

2. Restore the backed up database named “mysql19022012.sql” to new database named “mysqlnew” as below :
# 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” :

[root@centos64 ~]# mysql -u root -p password mysqlnew < mysql19022012.sql

or

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