Home arrow MySQL arrow Page 3 - Backing Up Your MySQL Databases With MySQLDump

Backing Up Your MySQL Databases With MySQLDump

MySQL includes several ways to backup your important data. In this article Mitchell shows us how to backup our databases using the mysqldump utility that comes with MySQL. He looks at several examples including how to backup your database to a file, another server, and even a compressed gzip file.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 126
January 30, 2002
  1. · Backing Up Your MySQL Databases With MySQLDump
  2. · The mysqldump utility
  3. · More examples

print this article

Backing Up Your MySQL Databases With MySQLDump - More examples
(Page 3 of 3 )

Example 2:

mysqldump --opt mydatabase > sql.dump

Specifying the --opt argument when backing up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "DROP TABLE IF EXISTS" statement to delete the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.

Here's a sample of the sql.dump file that was generated when I backed up the same database with the -opt argument:


# Table structure for table 'tbl_contactemails'


DROP TABLE IF EXISTS tbl_contactemails;

CREATE TABLE tbl_contactemails (

pk_ceId int(11) NOT NULL auto_increment,

ceEmail varchar(250) NOT NULL default '',

ceType int(11) default NULL,

PRIMARY KEY (pk_ceId),

UNIQUE KEY id (pk_ceId)



# Dumping data for table 'tbl_contactemails'


LOCK TABLES tbl_contactemails WRITE;

INSERT INTO tbl_contactemails VALUES (18,'mitchell@devarticles.com',1),(17,'mitchell_harper@hotmail.com',1),(16,'mytch@dingoblue.net.au',1);


Example 3:

mysqldump --opt mydatabase | mysql --host=localhost -C newdatabase

One excellent feature of the mysqldump utility is that it supports backing up a database from one MySQL server to another with just one command. In the example above, I have chosen to backup the "mydatabase" database to the server named localhost (which is the net bios name of my local machine). I have used the C argument to tell the mysqldump utility to enforce data compression between my MySQL server and the destination server if they both support it. Lastly, I have specified that all of the tables from the "mydatabase" database should be created in a new database on the remote server named "newdatabase".

In the example above I have used localhost as the name of the remote MySQL server to send the backup data to. You can replace this with the net bios name of any other computer on your network, or the I.P. address of any local or remote computer if you want to send the database to a MySQL server over your network or the Internet.

There's one catch to using this method: You must have already created the database on the remote server. In our example we are simply backing up the "mydatabase" database on the same machine, so we would use the following command at the MySQL console application before we ran the mysqldump utility:

create database newdatabase;
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials