Difference between revisions of "Backup MySQL"
| Line 4: | Line 4: | ||
The mysqldump program bundled with MySQL is one of the best ways that has been found to backup a MySQL database. | 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 switch [http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction 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. | 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. | ||
Revision as of 23:31, 9 January 2015
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.
Contents |
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 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.
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 You can put whatever extension you want on it, 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 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.
