View source for CSC220 Lab 9: Automatic MySql Database Backup

--[[User:Thiebaut|D. Thiebaut]] 14:22, 21 November 2010 (UTC)
----

<bluebox>
The goal of this lab is to setup a simple '''cron'''-based scheme to automatically backup your database every 24 hours.  It requires the use of '''mysqldump'''  which is standard on most linux system
</bluebox>


=Where should you login?=

* The computer you are going to login to will be the one that will be doing the automatic backup.  Because the setup will be saved on that server,  it is important to pick one that is reliable and won't be restarted in Windows mode by somebody else (which could be the case with any of the hadoop1xx machines).  This means that beowulf or grendel should be our candidates.  Unfortunately, '''mysqldump''', the command we need to run to backup our databases is not installed on these machines.  

* So pick one of the hadoop1XX  machines to login to, but remember that these servers are not as reliable because they can be rebooted at any time.

=Running MySqlDump from the Command Line=

==Main Reference==

* http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

==Is it installed?==
* Login to your 220a-xx account on one of the hadoop1XX servers
* Open a terminal (no need to ssh anywhere)
* run the following command

   mysqldump

* and verify that the command is installed and is giving you its USAGE.
* Figure out what the full path of the command is using '''which''', and write it down:

   which mysqldump

==Backing up your 220a-xx database==

* Run the following command


   mysqldump --opt --host=xgridmac.xxxxx.xxx  --user=220a-xx  --password=xxxxxx  220a-xx  > 220a-xx_dump.sql

:where you need to replace the '''xxx''' parts with the proper information.  

* The --opt option is documented in [http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html dev.mysql.com], as follows:

:::''Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.'''

* Verify that you have a new file in your working directory, called '''220a-xx_dump.sql'''.  Take a look at it with '''less'''.  You should recognize its format as that generated by '''phpmyadmin'''.
 
==Backing-up plus Compression==

* Because the sql dump is in text form, it will be huge if you have a lot of data in your database.  
* You should compress the output.  Bzip2 is a compression utility that is pretty efficient at squeezing the juice out of sql dumps:

  mysqldump --opt --host=xgridmac.xxxxx.xxx  --user=220a-xx  \
         --password=xxxxxx  220a-xx | '''bzip2 -c'''  > 220a-xx_dump.sql.'''bz2'''

* Look at the new file in your working directory, and compare the size of the dump.sql file to the dump.sql.bz2 file.   On my system I get almost a 1-to-9 ratio in size between the compressed and uncompressed files.

==Decompression==

* First rename the 220a-xx_dump.sql file to something like '''220a-xx_dump.sql.org'''

* Then simply decompress the '''bz2''' file  with '''bunzip2'''

  bunzip2   220a-xx_dump.sql.bz2

:and you should have two dump.sql files of identical size in your working directory.

<br />

<br />

<br />

<br />

<br />

<br />

<br />
[[Category:CSC220]][[Category:MySql]][[Category:Bash]]