How to Backup and Restore MySQL Database on Fedora16, Centos and Redhat

• Tweet

• Tweet

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.

 Mysql-server info

Source RPM  : mysql-5.5.20-1.fc16.src.rpm
 Build Date  : Fri 27 Jan 2012 09:17:21 AM CET
 Build Host  : x86-04.phx2.fedoraproject.org
 Relocations : (not relocatable)
 Packager    : Fedora Project
 Vendor      : Fedora Project
 URL         : http://www.mysql.com
 Summary     : The MySQL server and related files

1- Start  mysql  server

[pirat9@Fedora16 ~]$ sudo service mysqld   start

Connect  to the  Server   with

[pirat9@Fedora16 ~]$ mysql -u root -p
Output
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 2
 Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

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

mysql>

2- Create new database

In my case i created a new database called unixmen

mysql> create database unixmen;

Query OK, 1 row affected (0.00 sec)
-Show databases with

mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | test               |
 | test1              |
 | unixmen            |
 +--------------------+
 6 rows in set (0.00 sec)

mysql>

3- Backup  mysql database

Backup MySQL database to <databasebackupfiles>.sql

<databasename> = Target existing database name
<databasebackupfiles> = Preferred backed up file name

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

i will back up the database named <unixmen> to mylbackup file. This will taking backed up all the tables including the data :

# mysqldump -u root -p unixmen > backup.sql
 Enter password:

 View the content on mysqlbackup.sql

more mysqlbackup.sql

4- Restore MySQL database

To restore the backed up database named “mbackup.sql” to new database named “new” 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

Create the new database mysqlnew :

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

As example, i will restore the mysqlbackup.sql backed up file to database named “new” :

# mysql -u root -p password new < mysqlbackup.sql

or

mysql -u root -p new < mysqlbackup.sql

Related posts:

1. Script to backup your MySQL tables and send them to your email
2. multiple MySQL instance on Fedora/CentOS/Redhat/Scientific Linux
3. How to import and export a mysql database using ssh.
4. CPIO command to backup and restore in Linux
5. MySQL recover root Password