October 25, 2014 | Home | What is Wiki | Adding or editing content | All documents | Disclaimer | My Lab
Recently viewed: Home > MySQL backups
Document: MySQL backups | Last modified: December 28, 2005
MYSQL Backups
SP, Aug 05

Also see - MySQL on Mac

The raw data stored in MySQL databases is in a form that is not legible to us. However, once accessed by a MySQL client application (after being served by a MySQL server), the data becomes legible. MySQL client applications use a user account/password to connect to the a MySQL server. Such applications include those that appear like a regular application with a graphic interface, such as Navicat and Mac SQL, as well as those that are 'command-line' applications, such as the mysql application and the MySQL API's that are part of web programming languages like PHP.

The raw MySQL data is stored on Mac (running on OS X) in /usr/local/mysql/data. One way to backup the data is to, thus, simply copy this folder. The second method is to use the mysqldump command-line application to dump the data to a file at a certain location. Applications such as Navicat have proprietary methods to back up the data. Whichever method is used, one has to do the backups periodically. Applications such as Navicat allow one to set up scheduled backups.

Periodic backups using shell scripts that are run on schedule

Shell scripts are command-line applications that when run perform a series of tasks. For example, the bash shell script below runs the mysqldump command and gzip-compresses the dumped data and stores it in a file with the day-date in the file name. (With little knowledge, one may write scripts to backup using the first way described above. The mysqlhotcopy command-line application is used by many of these scripts. Such scripts may be found by searching on the internet.)

#!/bin/bash
cd xxx
/usr/local/mysql/bin/mysqldump --user=abcd --password=efgh --opt --all-databases|gzip > $(date +%d).gz

Xxx is the full path to the folder where backups are to be made. E.g., /Applications/backups/mysql. Abcd and efgh are the MySQL user account username and password. By adding '--all-databases,' one makes sure that data from all databases are dumped. The '|gzip' part 'pipes' the dumped data for compression to a new file that is named as the 'day-date'.gz (such as 13.gz - if it is the 13th, 01.gz - if it is the 1st, etc.). For the various mysqldump options, see http://dev.mysql.com/doc/mysql/en/mysqldump.html

Many operating systems, such as Mac OX and UNIX, run such shell scripts. Use a plain text editor to type out the above and save it with a .sh extension. Then, make the file 'executable.' One way to do so is to use the Terminal application in Mac OS X to browse to the folder that has the file and then type 'chmod +x filename.sh'. (Atleast on Mac OS 10.4.2, I had to change group ownership of both the shell script and the plist to wheel for the automatic backup to work).

On Mac OS X, UNIX, etc., a number of methods are available to make this script run periodically. For example, one can have the script above run every midnight. That way, one will have nightly backups that span over the last 30-31 days. Launchd (currently, Mac OS 10.4 only) and cron are two command-line applications for such scheduling. A utility named Cronnix eases the task of setting 'cron jobs' in Mac OS X - http://www.abstracture.de/projects-en/cronnix.

To set launchd (Mac OS 10.4 only, currently) to periodically run the backup script above, create a plain text file named com.mysql.dailybackup.plist inside Users/username/Library/Launchagents. Type in the following and save. You can change the '/Personal folders/Laboratory/Computer/mysql_backups' part as per your needs - this is the directory where 'mysql_daily_backup.sh' - the file for the shell script, above, is located. Note that you need to set the filepaths as per your setup. Once you have made the text-file and saved it, reopen it with Property list editor, check and save again.

<?xml version="1.0" encoding="UTF-8"?>
<DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/Propertylist-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>com.mysql.backup</string>
<key>Lowpriorityio</key>
<true/>
<key>Program</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/mysql_daily_backup.sh</string>
<key>Programarguments</key>
<array>
<string>/Personal\ folders/Laboratory/Computer/mysql_backups/mysql_daily_backup.sh</string>
</array>
<key>Rootdirectory</key>
<string>/Personal folders/Laboratory/Computer/mysql_backups/</string>
<key>Runatload</key>
<true/>
<key>Runondemand</key>
<true/>
<key>servicedescription</key>
<string>Daily backup of all of local MySQL data</string>
<key>Startinterval</key>
<integer>86400</integer>
<key>Workingdirectory</key>
<string>/Personal\ folders/Laboratory/Computer/mysql_backups/</string>
</dict>
</plist>

Then, log out and log back in, or, in Terminal application, type -

launchctl load ~/Library/Launchagents/com.mysql.dailybackup.plist

From now on, every 86,400 seconds (every day), a MySQL backup will be created and compressed and saved as the 'day-date'.gz in the /Personal folders/Laboratory/Computer/mysql_backups folder.

Restoring from backups

One can use the backups to restore a MySQL database to a former state. This is done following, e.g., a server crash, vandalism, accidental data deletion, etc. Parts of a table of a database to all databases can be restored. Applications such as Navicat have commands on their menu for such restoration. Command-line applications such as mysql are also used.

In case of MySQL server upgrades or reinstallations, the entire .sql file (generated by double clicking the .gz file) is loaded. For partial restoration (selectively restoring particular tables), edit the .sql file in a plain text editor (not MS Word).

Start Terminal application and do these

0. Start MySQL by typing

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

1. If you had a server upgrade or fresh installation, type the following, with 123456 or anything else as password. This is needed to set the MySQL root password (empty after fresh installs)

/usr/local/mysql/bin/mysqladmin -u root password 123456

2. Then, type

/usr/local/mysql/bin/mysql -u root -p < /path/to/file.sql

Because the .sql file has MySQL user account tables too, they too will be loaded (unless you have edited the .sql file).

3. If you had a server upgrade or fresh installation, type the following for the old user accounts to become active

/usr/local/mysql/bin/mysql -u root -p
(enter MySQL root password and at MySQL prompt type following)
flush privileges [return key]

Useful links to learn more

1. http://dev.mysql.com/doc/mysql/en/backup.html
2. http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
3. http://builder.com.com/5100-6388-5259660.html
4. http://www.macosx.com/articles/backing-up-and-restoring-your-mysql-database.html

PHPMYADMIN

The phpMyAdmin free, web-based application is a useful utility for backing and restoring MySQL data. Besides, it is useful for many other database manipulations. See http://www.phpmyadmin.net/home_page/ for more.
∑ accuracy, clarity, cost, ease, logic | 74 wiki pages served since a while | Admin login