Month: June 2010

Backing data up in MySQL is super important. The site content is often replaceable, however your board posts, ecommerce orders, etc. are far far harder to replace, if at all possible. The other nice thing is that with a lot of database driven web sites you retain most content even if you only back up the databases. You can still lose things like images, but by and large your posts will still be there even in event of a crash. Since bad code, etc. happens there are a few reasons I would recommend doing backups of the databases.

• Making server side changes, including configuration tweaks that can influence system stability.
• Making software changes, even adding plugins etc. to your site. I have seen odd things in the past caused by plugins and they can interact together with not always predictable results.
• Periodical backups as part of a data protection plan. I would do more than less, by and large most databases are not huge for the data they contain. I have seen forums with about 100,000 posts have about 2GB of database; not really that much if you think about it.

There are a few ways to do backups, but the most common for people who don’t use command line interface is probably going to be PHPMyAdmin. Quite frankly, I do not like using it for database backup and restorations. There are two reasons for this pertaining to reliability

• If the session timeout of PHP or Apache is too low, you will get an incomplete dump. This means that backup you may have of your databases is all of a sudden found to be truncated at a few megabytes. This is not a big deal for a lot of people with small DBs however if you’re rocking out with 50+ megabyte DBs this can be a huge issue.
• Restoration can be dependent on if the person has large uploads and large timeouts enabled. Since most people have slower upload than down, it can be a real nuisance to get the DB back up to the server and restored. Not only that but If you have a server with low upload size limits and timeouts, it may be impossible to get that DB back up and restored to the system before you hit one of these limits.

The way I honestly prefer to back my databases up is mysqldump; this allows one to have a lot of control over what gets dumped, do a full backup without worrying about doing it individually etc. It also allows us to make a nice easy cron job to do these backups too if we want.

The way we back a particular DB is up is:

mysqldump dbname > backup.sql

This will put the database named dbname into backup.sql. I believe you can separate multiple DBs with a space as well, however I don’t normally use that functionality. Another thing you can do is you can back up everything with:

mysqldump --all-databases > backup.sql

and this will do everything on the server. Handy for migrations, etc. Definitely way easier than trying to do this via a web interface and having 1,000 DBs.

How we restore these is fairly simple as well, we just run the command as follows:

mysql dbname < backup.sql

If you have a full DB dump you wouldn’t specify anything for the name when restoring. Notice how the carrat points in the opposing direction though. Don’t screw this up otherwise you will likely end up with a blank file and have to re-dump or re-move the file. I usually make 2 copies on the destination server especially when it’s not easy to get a second copy. There are other ways of moving the DBs over, such as copying the raw MYI files but that will be another blog.