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
