Backup MySQL

This article is a very bare bones "dummy's guide" set of information. If you'd like to learn more check out the MySQL page on using MySQL dump: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html Or this article on database backup methods if you'd like to look into other options. Plus there's a whole world of internet to search.

MySQL dump

The mysqldump program bundled with MySQL is one of the best ways that has been found to backup a MySQL database.

Because Retain exclusively uses the innodb table space you'll want to use the --single-transaction and possibly the --quick (*See notes blow) switch Check out the MySQL documentation on those switches here.

Using the --quick switch may depend on the size of your database, the bigger it is the more important it may become to use it for your dump script.

*Note: There can be some confusion on the "quick" switch since it's included in the default MySQL 5.5 files (See the install section). That switch is not included in the 5.6 options file and may not work automatically. It won't hurt your dump if you include it on the command line if it's in the configuration file.

Examples

Linux

At the command line type

Server:/ # mysqldump --single-transaction --quick retain > retain.date.sql -u root -p

Explanation of the command:

mysqldump: The command to get started telling MySQL you want to make a dump file

--single-transaction and --quick: switches you want to apply, read up on it in the documentation mentioned above if you'd like to fiddle with it some more. There's a lot you can tweak.

retain: This part is the database you want to export/dump. Whatever you named your Retain database this is what you'll call it. If you have a separate database for your configuration remember to export that too.

>: This is the "export to" pointer, I.E. direction you're pushing the data. In theory you could reverse them and put the location firs and then the name of the database, but the above example for sure works.

retain.date.sql: This is the name of the file you'll be creating with the mysqldump command. You have the power to name it whatever you like. Like cheesepuffs.txt or OutOfGum.rip. You can also tell it to place the file in a specific location, so /mysqldump/file.sql will put the file.sql in the /mysqldump/ directory. The freedom is given to put whatever extension you want on it, typically recommended you put .sql on the end so that you don't have to think what kind of file it is. As far as "best practices" go: RetainDatabase.Date.sql would probably make the most sense, substituting "Date" for the day, and possibly the time, you created the dump file.

The rest is logging into MySQL the way you would any other time. Root makes sure that you won't have any permissions issues, but it can be done with the same user that has full rights to the database.

Windows

The commands are the same. For MySQL 5.5, a MySQL command window icon was made available under the program group. Launch that and run the same MySQL commands as you would for Linux. For newer versions of MySQL, they seem to require using the MySQL Workbench, which has an option for backing up the database. More information about this should be supplied here by anyone that has experience using Workbench and knows how to use it.

Dumping Each Table As A File

Ernie created a script for automating this. Click here for the instructions on using it and where to obtain it.

Database Import

Once you have a dump you may need to re-import it back in if your system goes down. Also useful for if you're migrating your Retain Setup to a new server. If using this method, you first have to create the Retain database if this is a new MySQL system (e.g., you moved MySQL to another server so the new server doesn't have the Retain database yet).

mysql -u root -p[pw] retain < [/path/dumpfile.sql]