Should I make use of mysqldump or mysqlhotcopy for MySQL database backups?
I have a WordPress blog working on a MySQL database that I intend to support consistently. Would certainly it be far better to make use of the mysqldump program or the mysqlhotcopy manuscript to make backups? What are the benefits and drawbacks of each? Exist any kind of various other alternatives I should take into consideration?
I can inform you that I presently make use of mysqldump to keep a back-up of my database. I do this due to the fact that my objective is to maintain once a week backups to make sure that in case a person eliminates their page (s), I can recoup it from at the majority of a week earlier.
The good idea concerning this is that it is a standard message documents so I can import it over to any kind of computer system with MySQL, load it up, and also search around for the missing out on information. Keep in mind : so as to get what I desired, the command I'm making use of in my manuscript is this:
mysqldump --databases databaseName > /file/path
--databases is what makes mysqldump unload the
CREATE DATABASE IF NOT EXIST headers to make sure that I can import it anywhere I desire.
While this method benefits me, I'm certain that there are far better approaches around for various other objectives. I can additionally claim that, when it comes to any kind of back-up remedy, you need to store it at the very least on an additional computer system, and also ideally offsite (our in the cloud) if you can.
mysqlhotcopy is a Perl manuscript circa '05. So it includes dependences, for Perl and also numerous Perl. I would certainly hand down this set.
mysqldump is a C program being dispersed with MySQL and also proactively kept. If you add the - x alternative it secures the tables so you're gold.
I directly have this script in cron (you'll intend to remove those 'runs away if you intend to run it by hand) and also it functions like a beauty for me, supporting every one of my data sources to a neighborhood documents, along with a complete dump of all data sources.
And also if you have greater than one database there, you'll additionally intend to unload the gives for them. Maatkit will certainly aid you there, and also you'll simply
mk-show-grants -uroot -pPASSWORD > mygrantsdatabase.sql to have your gives tables supported and also all set to import once more.
Depends on your storage space engine for your tables. It works only for ARCHIVE and MyISAM tables accordinto this MySQL Manual Page and also has to be worked on the very same equipment (ie not making use of neighborhood outlet or TCP/IP)
If you are doing a backup on the web server and also your tables all are MyISAM tables, take into consideration making use of the mysqlhotcopy rather due to the fact that it can complete much faster backups and also faster recovers.
I would certainly take into consideration making use of a WordPress plugin called "WordPress Database Backup (WP - DB - Backup)" which is additionally detailed on the Wordpress Database backup guide. This plugin can backup the database on a timetable and also email it to you (thinking its a tiny database). You can additionally simply back it up in your area. You can exclude comment spam and also alterations to make the DB smaller sized
Less control yet less complicated to implement.
MySQL unloads offer you the most control . Your organizing solution can additionally be a restricting variable. Some hosts do not offer you accessibility to run cron work or run the mysqldump command.
I advise making use of among the several pre - existing mySQL back-up manuscripts to take the discomfort out of obtaining it right.
I make use of "automysqlbackup" which is an open - resource covering manuscript that can be set as a cron work on the web server. It covers virtually every imaginable back-up scenario : http://sourceforge.net/projects/automysqlbackup/. It has actually functioned faultlessly for me for numerous years - extensively advised.
I additionally recognize that a variety of web server monitoring devices such as Interworx and also Ensim make use of mysqldump to backup the data sources and afterwards a straightforward mysql command to recover. From this it would certainly be my hunch that this is just one of the most effective means.