Diary of A Webmaster Part 4 - Backing Up With MySQLDump

Method 2

mysqldump --opt mydatabase > sql.dump

Specifying the --opt argument when we back 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. This deletes 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)  
) TYPE=MyISAM;  
 
#  
# 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);  
UNLOCK TABLES;

Method 3

mysqldump --host=host1 --opt mydatabase | mysql --host=host2  
-C newdatabase

One excellent feature of the mysqldump utility is that it allows you to back up a database from one MySQL server to another with a single command. In the example above, I chose to backup the "mydatabase" database from the server named host1 to the server named host2. Both these servers must allow MySQL connections from wherever I run this command, or the procedure won't work. 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 the tables from the "mydatabase" database on host1 should be created in a new database on the host2 server named "newdatabase".

In the example above I used host2 as the name of the remote MySQL server that I'll send the backup data to. You can replace this with the hostname or IP address of any other computer on your network or the Internet.

Note that there is one catch to using this method: the target database must already exist on the remote server. In our example, we would use the following command at the MySQL console application before we ran the mysqldump utility:

create database newdatabase;

Method 4

mysqldump ---user admin --password=password mydatabase |  
gzip > /usr/local/mydatabase.gz

Yet another great feature of MySQL is the ability to backup databases to gzipped (Linux's answer to ZIP) files. Note that this can be done on Unix/Linux servers only. As you can see in the example above, I have specified the name of the database I would like to backup (mydatabase), followed by a pipe, the insertion symbol, the "gzip" keyword, and finally the path to the gzip file that mysqldump should stream the backup to.