2012年11月29日 星期四

How To Backup And Restore MySQL Database On Fedora16, Centos And Redhat


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

沒有留言:

張貼留言

歡迎熱愛 Puzzle and Dragons 的玩家一起上來討論及研究各種降臨打法。

進擊的 Puzzle and Dragons Facebook 專頁現已開幕 ~ 歡迎大家上去追查各種新舊貼。 Enjoy your Puzzle and Dragons