Difference between revisions of "CSC220 Lab 9: Automatic MySql Database Backup"
(→Backing up your 220a-xx database) |
(→Backing up your 220a-xx database) |
||
| Line 42: | Line 42: | ||
* 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'''. | * 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: | ||
| + | |||
| + | mysqldump --opt --host=xgridmac.xxxxx.xxx --user=220a-xx \ | ||
| + | --password=xxxxxx 220a-xx | '''bzip2 -c''' > 220a-xx_dump.sql.'''bz2''' | ||
| + | |||
<br /> | <br /> | ||
Revision as of 12:09, 21 November 2010
--D. Thiebaut 14:22, 21 November 2010 (UTC)
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
Contents
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
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 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:
mysqldump --opt --host=xgridmac.xxxxx.xxx --user=220a-xx \
--password=xxxxxx 220a-xx | bzip2 -c > 220a-xx_dump.sql.bz2
