Lesson number one: never fully trust the hardware your database is running on. If there’s ever a crash or some level of corruption you lose your data, which is bad, bad bad.

How do we avert this calamity? Frequent backups – using the MySQLDump Utility. This is also really useful to test on your off-network machine before moving new scripts to your production server – think of it as real-world dummy data.

The Basics 

MySQLDump is a little utility that is packaged with your MySQL installation. It can be found in the installation path, in the bin folder (C:\Program Files\MySQL\MySQL Server 5.0\bin).

Open the Windows Command Line prompt (start, run, cmd) and use the following format:

mysqldump -u [user name] -p [database name] > [dump file]

Some people will tell you to enter both your username and password here, but I like to omit the plaintext password as the utility will then prompt you in the next screen and use the ****** mask, so the person walking behind you doesn’t get your root password. The “-p” flag just tells the export utility to expect a password for authentication.

Break it down 

[user name]: I usually export using my root account, so “root” would go here. You can use any account that has high enough privileges.

[database name]: Here’s where things get fun. You can set all kinds of parameters if you wan to export a single database, all databases, just the data, just the schema, etc. Here we’ll list a single database for simplicity’s sake.

[dump file]: This is the full path (file name included) that you want to export to. I find it’s really easy just to dump right into the C: drive using “C:/output.sql” or something of that form. The greater than arrow just tells the utility we are dumping the left argument into the right argument, much like a funnel.

Let’s Review Our final query should look like the following:

cmd> mysqldump -u root –p students_db > C:/output.sql cmd> Enter Password: *************

Cross your fingers, hit enter, and see what happens! You’ll need to open your local disk through My Computer to check if the file was exported.

Troubleshooting If there doesn’t seem to be any response from the command window and no file is outputted, try using the full file path the function likeso:

cmd> “C:/Program Files/MySQL/MySQL Server 5.0/bin/mysqldump” -u root –p students_db > C:/output.sql

You need the quotation marks since there are spaces in the filename. Otherwise the command line will interpret separations as different arguments and won’t work properly.

If you still aren’t getting a file, make sure that your password and username are valid, and that the database name you gave actually exists.

Conclusion 

You can open the outputted file right in notepad, it looks like the following (not to scary, eh?)

- MySQL dump 10.11 -- -- Host: localhost Database: students_db -- ------------------------------------------------------ -- Server version 5.0.51a-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

…SOME CODE OMITTED…

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-02-25 9:12:38

If the basic export doesn’t cover your needs, check out http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html to see what flags you can set. I’ll be covering importing your data back into MySQL soon – there are several different ways to do this, some better than others.

Labels: backup, data, Databases, MySQL, mysqldump, relational databases