: Should I use mysqldump or mysqlhotcopy for MySQL database backups? I have a WordPress blog running on a MySQL database that I want to back up regularly. Would it be better to use the mysqldump

@Fox8124981

SUGGESTION #1

If you do any backups of MySQL Instance it is best to setup MySQL Replicaton so that any impose server load or increase disk I/O does not impact your production database is any way.

Once you setup MySQL Replication, you can script a mysqldump in different ways (Please see my post on the DBA StackExchange for the variety of techniques), as long as you run those mysqldumps on a Slave.

SUGGESTION #2

If you cannot setup another server for MySQL Replication and all your data is InnoDB, you will have to perform a point-in-time mysqldump. This is done using --single-transaction option with the mysqldump. Thus, if you ran a mysqldump starting at midnight and the backup ended at 12:15AM, the mysqldump's output when restored reflect the data as of 12:00 AM.

SUGGESTION #3

Another program available for MySQL Backups is XtraBackup from Percona.

XtraBackup is a comprehensive tool that behaves like rsync but has directed purpose in life. It can start off copying all InnoDB data and tablespaces. It has the ability to create checkpoints internally and perform in-place an InnoDB crash recovery to help get perfect point-in-time backup. XtraBackup also has an extra feature which allows for the creation of incremental backups. Another additional feature is the creation of InnoDB log files, also built by an in-place crash recovery. There is also some wrapper software to provide frozen copying of MyISAM tables as well.

It performs a back of the MySQL Instance, but with a distinct difference. The point-in-time of the backup is based on when the backup completed rather than when it started. Thus, if you ran XtraBackup starting at midnight and the backup ended at 12:15AM, the mysqldump's output when restored reflect the data as of 12:15 AM.

Vote Up Vote Down

@Cooney921

mysqlhotcopy is a Perl script circa '05. So it adds dependencies, for Perl and various Perl. I'd pass on this one.

mysqldump is a C program being distributed with MySQL and actively maintained. If you add the -x option it locks the tables so you're golden.

I personally have this script in cron (you'll want to remove those ` escapes if you want to run it by hand) and it works like a charm for me, backing up each one of my databases to a local file, as well as a full dump of all databases.

And if you have more than one database there, you'll also want to dump the grants for them. Maatkit will help you there, and you'll just mk-show-grants -uroot -pPASSWORD > mygrantsdatabase.sql to have your grants tables backed up and ready to import again.

Vote Up Vote Down

@Samaraweera270

I also know that a number of server management tools such as Interworx and Ensim use mysqldump to backup the databases and then a simple mysql command to restore. From this it would be my guess that this is one of the best ways.

Vote Up Vote Down

@Twilah146

I recommend using one of the many pre-existing mySQL backup scripts to take the pain out of getting it right.

I use "automysqlbackup" which is an open-source shell script that can be set as a cron job on the server. It covers almost every conceivable backup situation: sourceforge.net/projects/automysqlbackup/. It has worked flawlessly for me for several years - thoroughly recommended.

Vote Up Vote Down

@Cody1181609

I would consider using a WordPress plugin called "WordPress Database Backup (WP-DB-Backup)" which is also listed on the Wordpress Database backup guide. This plugin can backup the database on a schedule and email it to you(assuming its a small database). You can also just back it up locally. You can exclude comment spam and revisions to make the DB smaller

Less control but easier to implement.

MySQL dumps give you the most control. Your hosting service can also be a limiting factor. Some hosts don't give you access to run cron jobs or run the mysqldump command.

Vote Up Vote Down

@Shelton105

Depends on your storage engine for your tables. It works only for ARCHIVE and MyISAM tables accordinto this MySQL Manual Page and must be run on the same machine (ie not using local socket or TCP/IP)

MySQL also says


If you are doing a backup on the
server and your tables all are MyISAM
tables, consider using the
mysqlhotcopy instead because it can
accomplish faster backups and faster
restores.

Vote Up Vote Down

@BetL925

I can tell you that I currently use mysqldump to maintain a backup of my database. I do this because my goal is to keep weekly backups so that in the event someone removes their webpage(s), I can recover it from at most a week ago.

The good thing about this is that it is a basic text file so I can import it over to any computer with MySQL, load it up, and search around for the missing data. Note: in order to get what I wanted, the command I'm using in my script is this:

mysqldump --databases databaseName > /file/path


The --databases is what makes mysqldump dump the CREATE DATABASE IF NOT EXIST headers so that I can import it anywhere I want.

While this method works for me, I'm sure that there are better methods out there for other purposes. I can also say that, as for any backup solution, you should store it at least on another computer, and preferably offsite (our in the cloud) if you can.

Vote Up Vote Down