Home arrow MySQL arrow Page 3 - Backing Up and Restoring Your MySQL Database

Easy Restore - MySQL

Do you need to change your web host or switch your database server? This is probably the only time when you really think of backing up your MySQL data. If you've got a website with a database or your custom database running for your applications, it is imperative that you make regular backups of the database. In this article, I will outline two easy ways of backing up and restoring databases in MySQL.

TABLE OF CONTENTS:
  1. Backing Up and Restoring Your MySQL Database
  2. What about Multiple Databases?
  3. Easy Restore
  4. PHPMyAdmin
  5. Backing Up and Restoring Your Database with PHPMyAdmin
By: Vinu Thomas
Rating: starstarstarstarstar / 479
June 15, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Now that you've got backups of your database, let's learn how to restore your backup in case your database goes down. Here's how you can restore your backed up database using the mysql command.

Restore using mysql

If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.

Here's how you would restore your custback.sql file to the Customers database.

mysql -u sadmin -p pass21 Customers < custback.sql

Easy isn't it ? Here's the general format you would follow:

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

Now how about those zipped files? You can restore your zipped backup files by first uncompressing its contents and then sending it to mysql.

gunzip < custback.sql.sql.gz | mysql -u sadmin -p pass21 Customers

You can also combine two or more backup files to restore at the same time, using the cat command. Here's how you can do that.

cat backup1.sql backup.sql | mysql -u sadmin -p pass21

Moving Data Directly Between Databases

How would you like to replicate your present database to a new location? When you are shifting web hosts or database servers, you can directly copy data to the new database without having to create a database backup on your machine and restoring the same on the new server. mysql allows you to connect to a remote database server to run sql commands. Using this feature, we can pipe the output from mysqldump and ask mysql to connect to the remote database server to populate the new database. Let's say we want to recreate the Customers database on a new database server located at 202.32.12.32, we can run the following set of commands to replicate the present database at the new server.

mysqldump -u sadmin -p pass21 Customers | mysql --host=202.32.12.32 -C Customers



 
 
>>> More MySQL Articles          >>> More By Vinu Thomas
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: