Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Mysql Backing Up and Restoring A MySQL Database

Related Tutorials


 
 

Share on Google+Share on Google+

Backing Up and Restoring A MySQL Database

Advertisement
This tutorial explains the how to backup and restore the MySQL Database. Databases are used to store large amount of precious data and it becomes very important to Backup your data. In case case of some hardware or software failures backup data can be used to restore the Database.

     

This tutorial explains the how to backup and restore the MySQL Database. Databases are used to store large amount of precious data and it becomes very important to Backup your data. In case case of some hardware or software failures  backup data can be used to restore the Database.

Backing Up MySQL Database

MySQL database backup can be accomplished in two ways:

a) Copying the raw mysql database files &
b) Exporting tables to text files

Copying the MySQL database files

MySQL uses the same table format on different platforms, so it's possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).

Exporting tables to text files

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.

The syntax for the command is as follows.

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup

Let's discuss the example of backing up MySQL Database named "accounts" into text file accounts.sql. Here are the scenarios of taking the backup assuming that both user name and password of the database is "admin".

a) Taking the full backup of all the tables including the data.
Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql

b) Taking the backup of table structures only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-data accounts > accounts.sql

c) Taking the backup data only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql

Restoring MySQL Database

Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.

mysql - u admin -p admin accounts < accounts.sql

In this tutorial you learned how to take the backup of your MySQL Database and restore the same in the event of some database crash or on some other machine.

 

Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: December 24, 2004

Related Tutorials

Discuss: Backing Up and Restoring A MySQL Database   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:7
Abhishek Dixit
October 8, 2011
unable to do so

I tried the commands you mentioned as follows mysqldump -u root -p accounts > accounts.sql Enter password: mysqldump: Got error: 1049: Unknown database 'accounts' when selecting the database
Alejandro Arauz
October 10, 2011
Using a third party tool

If what you want is to backup your database in an easy way without running scripts I would recommend MySqlBackupFTP (http://mysqlbackupftp.com/). It has a fully functional free version that allows you to schedule backups.
Fatima
December 23, 2011
Thank you

Thank you for the article
Devendra Yadav
January 18, 2012
Java Mysql Problem

I want to take the backup of my all the table with data. It happened but it does not carrying the table and it's data. I m making the project in java with mysql plz email me the solution to keep backup with table and it's tables
Rajus
February 20, 2012
Good Job

Its a very nice article.
How to take schema level backup in mySQL database at OS level. And also how to find the size of a schema in MySQL database.
April 30, 2012
Schema_level_backup

Please help me. Thanks in advance. Regards, Thirumalai
faisal
June 27, 2012
thanks

thanking you from faisal wincom pvt ltd.
DMCA.com