Managing MySQL
From Wiki99
↑ Computers ↑
← prev: Installing MySQL
next: WordPress →
Contents |
Dealing with Corruption
Like a file system, a database manager is wonderful when it works correctly, and can reduce a grown man to tears when something goes wrong. It's best to have some feeling for what can go wrong and how to cope when it's just toy data you are experimenting with rather than real data.
There are two aspects to protecting yourself:
- There is backup which builds on our earlier backup discussion. The point of note is that there are some non-obvious aspects to backing up a database that we need to be aware of if the backup is not to be corrupt.
-
There is coping with corruption in the database (which most usually occurs as a result of unexpected loss of power, but which can be present in your backup if you are unlucky).
The structure of databases
Recall that we said that, for the most part, MySQL stores a database as a directory on your hard drive. (As I mentioned, the database contents are protected from prying eyes, so you have to become superuser before you can look at these directories.)
sudo su
cd /opt/local/var/db/mysql5/
ls -la
You are now in the mysql directory and you will see a directory named mysql. This directory represents MySQL's database of itself, mainly holding its list of all the users it knows about, all their permissions, and all the databases it knows about.
If you run WordPress, you will create a new database which will live in a directory named wordpress. And so on.
You can see the contents of such a directory by
cd mysql
ls -la
What you will see is groups of three files, each group having the form:
columns_priv.MYD columns_priv.MYI columns_priv.frm
What's going on here?
- A database consists of a number of tables which can be thought of as arrays of records. Each table is represented by these three files.
-
The structure of any particular table is held in the .frm file. This is data that, under normal circumstances, is written out once when the table is first defined, and never again.
The .frm file is vital and if it is lost or corrupted you are doomed.
The good news is that since, under normal circumstances, the .frm file doesn't change, if worst comes to worst, you can re-create it the way you first created it. (For example if one of your WordPress .frm tables became corrupt you could run WordPress again, have it recreate the tables it uses, and then copy over the .frm files.) -
The actual data of the database can be stored using one of various storage engines with names like MyISAM, BDB and InnoDB. These engines have different strengths and different performance characteristics. They determine how data is actually stored on the disk.
The only thing you really need to know is that standard web apps (eg things like WordPress) almost always use MyISAM by default. This is nice because MyISAM has the best characteristics with respect to backup and moving the database around.
The one exception is MediaWiki which, by default, uses the InnoDB format. This is somewhat unfortunate for small-time users like us because, while this provides a speed boost to large scale sites, InnoDB tables are cached substantially more aggressively in RAM than are MyISAM tables, meaning that it is more likely than with MyISAM that a simple file copy of a database folder will result in a corrupt database. -
The MyISAM storage engine stores the data for each table (ie the stuff you actually care about) in the .MYD file. If something goes wrong with this file, you are truly doomed, but fortunately this is unlikely to happen.
-
The MyISAM storage engine stores indices for each table in the .MYI file. Indices help the database find data rapidly. The index files are by far the most likely to become corrupted, but this is no real problem because they also don't contain anything essential, and than can be recreated from scratch if necessary.
(The index files do contain statistics on how the database has been used in the past, and these statistics are used to improve performance, so you don't want to throw away and recreate index files unless you really have to, but the statistics are simply a hint, not essential.) -
The indices point to offsets in the .MYD files.
Given the points above, some nice consequences of MyISAM databases are:
-
If your MySQL demon shut down and wrote out all its data to disk cleanly,
then a database on disk is self contained. You can copy it to another
computer, set up a MySQL user on that computer with the appropriate
permissions, and start using the database right away.
There are (as far as I know) no guarantees that this will work with InnoDB, but in my very limited experience it also works there. -
This even works across different endian-ness, so that you can move a database from a PPC to an Intel machine. (In theory, I've never tried this.)
Recovering from corrupted databases
Now let's supppose that something has gone wrong, or that you copied a database from a backup onto your main machine, or perhaps you've upgraded your server to a new computer and copied over the databases. You try to start up MediaWiki or WordPress and they can't read the database, or perhaps they can read, but when you try to make changes like edit an article or submit a new post, they report a database error. What do you do?
Permissions type errors
A lot of the stuff below won't make much sense to you until you have installed WordPress and/or MediaWiki, so don't worry too much about not fully understanding it. The main point is to read it, remember that it exists, then read it again after you've installed these software packages, at which point it should be rather more clear.
UNIX level permissions
The UNIX level permissions have to be correct, meaning that the database
directory has to be owned by mysql:mysql with permissions of
rwx for the owner, and nothing for anyone else, and the
directory contents have to owned by mysql:mysql with permissions of
rw- for the owner and group, and nothing for anyone else.
If you try to copy a mysql database while you are not logged in as root, the copy will fail because you don't have read permission on the database. That is, perhaps, obvious. What is less obvious is that if you try to copy the database directory while you are superuser, and don't use the right flags, the copy will be owned by root, not by mysql, and you will then have to screw around with chmod. The correct way to copy a database directory is
The -R means copy recursively copy all the items in the database, and the p means retain the pre-existing user and permissions for the copy.
|
If you are still using -r for recursive copies, you should be aware that it is obsolete and not recommended, and that -R is the option that works properly if various types of special files exist in the copied directory tree. |
Note that copying a database while mysql is running is very likely to cause problems because you will only copy information mysql has propagated to the file system, not information that mysql is caching in memory. Before copying a database directory, you need to
- flush all cached information from mysql to the file system
- lock the database so that nothing changes while you are copying
For our simple environment, and with small databases, by far the easiest way to do this is to make sure that mysql is always shut down before a database is copied, so using a fragment of code like this
mysqladmin shutdown -u root -p
sudo cp -Rp theDatabaseDir theNewDatabaseDir
sudo mysqld_safe5
^Z
bg
This looks like more of a hassle than simply copying the directory, but a copy of a corrupted database is useless to everyone.
Location of the database directory
Obviously the database directory has to be in the place where MySQL looks for databases (/opt/local/var/db/mysql5/ by default for a Darwin Ports install of MySQL).
You probably won't screw this up when you are simply copying a database directory to make a quick backup before you change something, but this may be an issue if you are copying a database from one computer to a different computer which has a MySQL installed expects its databases to be placed somewhere else.
MySQL permissions
The installation of MySQL has to have had the appropriate MySQL user created, with the appropriate permissions, to access the database. Remember that when you utilize a web application like MediaWiki or WordPress, the web app and MySQL have to agree on three things
- the name of the user that will be accessing the database. This is usually set to the name of the relevant application, eg mediawiki or wordpress
-
the password of this user
-
the name of the database that will be accessed. Again this is usually a name related to the application, eg wikidb or wpdb
-
AND, as we mentioned above, web application and MySQL must agree on the location of the MySQL socket in the file system
quick example
Suppose, for example, that you are thinking of upgrading your wiki software.
Following what I have said above, you make a local copy of your wiki database,
and call it something like wikidbX.
Next you put a copy of the new wiki software in /Library/WebServer/Documents,
and configure it to use the database copy, named as we said, wikidbX,
instead of the old wikidb, but you keep everything else the same, in
particular the same mysql username, say wikiuser.
When you now run your new wiki software, you will get a permissions error.
Oh dear.
The problem is that MySQL was earlier told, when you set up the wiki software,
that user wikiuser, had permission to read and modify database wikidb,
but was given no other permissions. In particular, user wikiuser has no
permissions to interact with any database named wikidbX.
The command line you need, if you have this sort of permission error, is
mysql5 -u root -p GRANT ALL PRIVILEGES ON wikidbX.* TO wikiuser@localhost IDENTIFIED BY '99merry';
Real corruption
If, after all the above, there are still problems, then you probably have
database corruption.
http://dev.mysql.com/doc/refman/5.0/en/check.html will tell you in more detail
how to cope with this, but the simplified version of what the manual says is:
prepare to detect corruption
Shut down the mysql server:
Move to the directory of the corrupted database:
sudo su
cd /opt/local/var/db/mysql5/
cd theDatabaseDir
detect corruption
Run the table checker app:
The -e means an extended check which is slower, but our tables are so small that there's no point in trying to cut corners. If you get overwhelmed by the output, you can run the command again as
where the -s option means suppress non-essential info.
repair corruption
the easy fixes
If you learn that there is damage, you need to repair it.
http://dev.mysql.com/doc/refman/5.0/en/repair.html
covers this in detail, but again the essential points are:
First try the fastest, least intrusive recovery scheme:
In my (limited) experience of corrupted databases, this works about half the time.
If that doesn't work, next, on whatever tables were not fixed, try
This time we omitted the -q (quick recovery) flag, and we only
specify the tables that still have corruption.
You can, if your tables are small, and you don't mind waiting a few minutes,
jump here directly and specify all your tables, ie
If that still doesn't work, move on to
Once again, if your tables are small and you have a few minutes, you can jump here directly and specify all your index files.
the more complicated fix
If the above doesn't work, the index file is irretrievably broken and needs to
be recreated from scratch, which is rather more of a hassle.
Start the MySQL demon as usual:
sudo mysqld_safe5
^Z, bg
Start the MySQL commandline app (the last argument specifies the database we plan to work with, in this case the database named wikidb):
Now you enter some specific commands; in particular you essentially delete the broken index, namely the index for the table named searchindex, in such a way that it will then be rebuilt
SET AUTOCOMMIT=1; TRUNCATE TABLE searchindex; EXIT;
Now shutdown the MySQL demon to force everything to flush to disk,
start the demon again,
sudo mysqld_safe5
^Z, bg
and, with luck, everything is now fine.
Backup
You should already have read how we perform an backup (using rsync) of the
entire filesystem.
It is important to note that backing up the entire file system
is not good enough for backing up your databases. The reason for this is that
the database manager maintains a lot of state in memory, state that the file
system is unaware of. When you perform a file system backup, all you get is
a replica of what the database manager has currently written to disk, not the
database records and other material that the database manager has in memory and
not yet committed to disk. (This is, of course, a generic problem. Any program
has the potential to have cached material in memory that is not committed to
disk and is not captured by a file system backup. Windows 2000 and higher have
a partial solution to this. Sadly the UNIX/MacOS X world does not yet have such
a solution and is basically in denial that such a solution is necessary. Even
Leopard, with Time Machine, still hasn't got this issue resolved yet.)
To perform a database backup, one should
- tell the database manager to lock the relevant database to allow only reads, so that any attempts to write to the database are delayed until after the backup
-
tell the database manager to flush any cached data relevant to the database to disk
-
copy the disk files
-
tell the database manager to remove the lock on the database
There are various different strategies for how to do these tasks, and which to use depends on issues like whether you want to create a backup that could be restored to a different type of computer from the one that created the backup, and how large and busy your database is. Since our databases are small and infrequently used, we'll be using the simplest strategy, using the program mysqldump5 that comes with mysql. mysqldump5 generates the SQL code that would create a particular database, thus what it generates is essentially a large text file. This has the disadvantage of being large, but the advantage of being completely portable, the file can even be read and modified manually if necessary. mysqldump5 takes care of all the locking and unlocking mentioned above.
A script showing how to use mysqldump5
This is an example of a script you might want to run that backs up your databases. This particular script actually backs up both my databases and the email folders of my users, and runs once an hour on my server. Since our databases are really small, and the email directories are not that large, the backup drive for this task is a good way to use an old 10 or 20GB drive you might have sitting around that doesn't really have any other use.
In Leopard, if you are using Time Machine for your server, you're probably getting an adequate backup of your email, but you will probably still want to use a modified version of this script to backup your databases, at least until the whole issue of database interaction with Time Machine is resolved.
Create this script and give it some appropriate name (I call it bakPartial99.sh) and store it somewhere relevant like ~/bin.
#!/bin/bash
#This script performs a partial backup.
# The specified email directories and mysql databases are backed up.
#
# The variables you'd need to set to modify it for you needs are clustered
# below.
#Two non-obvious things you should be aware of are:
# * The script must be run as superuser, ie sudo backupScript
# * It's not a good idea to run two instances of this script at the same
# time if they write to the same hard drive. This is because the diskutil
# stage of the backup, where the destination drive is checked for file
# system consistency, requires the drive to be unmounted, which you don't want
# to happen if the other script is buy writing to it.
# In theory two copies of the script that write to different drives should
# run in parallel without a problem, but I've never had a reason to try
# that so I can't promise anything.
#===============================================================================
HOME_DIR=/Users/mjh
RSYNC=$HOME_DIR/bin/rsync
MYSQL_DUMP=/opt/local/bin/mysqldump5
POWERSHIFT=$HOME_DIR/bin/powershift
MAIL_ADDR=mjh@bluecloud.com
LOG_FILE=$HOME_DIR/Library/Logs/backup.log
#Using $$ below uses the process ID in the file name and thus makes it unique.
TMP_FILE=/tmp/partialBak.$$.txt
SRC_VOL=/
SRC_DIR_DATABASES_IN_TMP=/tmp/mysqlbak-$$.d/
DATABASES="mysql
wordpress
wikidb"
SRC_DIR_USERS=/Users/
SRC_DIR_MAIL=/Library/Maildir_for_IMAP_server/
MAIL_USERS="mjh
fred"
DST_VOL=/Volumes/BackupSmall
DST_DIR=$DST_VOL/Backups/
BAK_NAME=Server_Partial
#===============================================================================
ReportErrorAndExit()
{
#This function reports an error.
# It takes a compulsory argument, $1, a string that describes the error and
# and optional argument, $2.
# If $2 is anything, the error string is only logged to stderr, otherwise
# it is also logged to the log file.
# The error string is also mailed to $MAIL_ADDR.
#Set bash "word" separator to newline only.
# (If we didn't do this, the string argument passed in would not be
# treated as a single $1 argument.)
# Normally you'd want to restore this after you're done, but we're exiting
# at the end of this function so that's not necessary.
IFS=$'\n'
ERROR_STRING="BACKUP $BAK_NAME: $1"
if [[ $2 ]]; then
echo $ERROR_STRING >&2
else
echo $ERROR_STRING >&2
echo $ERROR_STRING >> $LOG_FILE
fi
mail -s $ERROR_STRING $MAIL_ADDR </dev/null &> /dev/null
exit 1
}
#===============================================================================
#Test user is root
if [[ `id -u` != 0 ]]; then
ReportErrorAndExit "user is not root" DONT_LOG
fi
#...............................................................................
echo "============================================================" >> $LOG_FILE
echo `date` >> $LOG_FILE
echo "Start backup $BAK_NAME" >> $LOG_FILE
#...............................................................................
#Test dst directory exists
if [[ ! -d $DST_DIR ]]; then
ReportErrorAndExit "$DST_DIR does not exist"
fi
if [[ ! -d $DST_DIR/0 ]]; then
mkdir $DST_DIR/0
fi
#...............................................................................
#Force the backup drive to have permissions enabled
#Sometimes, for who knows what reason, the backup disk is spontaneously unomunted.
diskutil mount $DST_VOL
#This (helpfully non-documented, no-built in help --- thanks Apple) command will
# force permissions to be enabled for the backup drive.
# http://www.macosxhints.com/article.php?story=20020925051644480
vsdbutil -a $DST_VOL
#Test that the backup drive has permissions enabled (otherwise not only do we
# have the obvious problem of permissions not stored correctly), we also have
# the dest-link stuff won't generate correct links for anything with ownership
# that's not the current user.
#(At boot time race conditions mean this test may fail. Sleep and try again.)
PERMISSIONS_ENABLED_ON_BACKUP=`diskutil info $DST_VOL | grep "Owners" | awk '{print $2}'`
if [[ $PERMISSIONS_ENABLED_ON_BACKUP != "Enabled" ]]; then
sleep 60
fi
PERMISSIONS_ENABLED_ON_BACKUP=`diskutil info $DST_VOL | grep "Owners" | awk '{print $2}'`
if [[ $PERMISSIONS_ENABLED_ON_BACKUP != "Enabled" ]]; then
ReportErrorAndExit "backup drive does not have permissions enabled " >> $LOG_FILE
fi
#===============================================================================
#Dump the databases into /tmp
#
# We want this to run as fast as possible (minimal downtime for the database).
# The subsequent backup then syncs against the files in /tmp and can be more
# leisurely.
rm -rf $SRC_DIR_DATABASES_IN_TMP >& /dev/null
mkdir $SRC_DIR_DATABASES_IN_TMP >& /dev/null
cd $SRC_DIR_DATABASES_IN_TMP
for database in $DATABASES; do
$MYSQL_DUMP -u root -p99merry $database >./${database}.sql
ERR=$?
if [[ $ERR != 0 ]]; then
echo " BACKUP $BAK_NAME: mysqldump5 reported error for $database" >> $LOG_FILE
mail -s " BACKUP $BAK_NAME: mysqldump5 reported error for $database" $MAIL_ADDR </dev/null &> /dev/null
# exit 1
fi
done
#===============================================================================
#Do the actual backup. If a prior backup exists, use that as a link source.
INITIAL_SIZE=`df -k $DST_VOL | grep "^/" | awk '{print $4}'`
INITIAL_SECONDS=`date "+%s"`
RSYNC_ERROR_CODE=0
#...............................................................................
#Backup all the databases
mkdir $DST_DIR/0/Databases/
for database in $DATABASES; do
#Use pgo flags, not -a. Specifically we do not want the -a flag checking
# times, because the times are always different since the .sql files
# are always constructed from scratch in the code above.
# (We don't need any of the other flags of -a.)
$RSYNC -pgoE --delete --delete-after \
--ea-checksum \
--link-dest=$DST_DIR/1/Databases/${database}.sql \
--stats \
$SRC_DIR_DATABASES_IN_TMP/${database}.sql \
$DST_DIR/0/Databases/${database}.sql \
>>$LOG_FILE 2>&1
ERR=$? ; if [[ $ERR != 0 ]]; then RSYNC_ERROR_CODE=$ERR; fi
echo "$database ^^^" >>$LOG_FILE
done
#...............................................................................
#Backup all the mail directories
mkdir $DST_DIR/0/Mail/
for user in $MAIL_USERS; do
$RSYNC -aE --delete --delete-after \
--ea-checksum \
--link-dest=$DST_DIR/1/Mail/$user/ \
--stats \
$SRC_DIR_USERS/$user/$SRC_DIR_MAIL/ \
$DST_DIR/0/Mail/$user/ \
>>$LOG_FILE 2>&1
ERR=$?
# An error code of 24 (some files vanished before they could be transferred)
# is common for mail and not worth treating as an error.
if [[ $ERR == 24 ]]; then ERR=0; fi
if [[ $ERR != 0 ]]; then RSYNC_ERROR_CODE=$ERR; fi
echo "$user ^^^" >>$LOG_FILE
done
echo >>$LOG_FILE
#...............................................................................
FINAL_SIZE=`df -k $DST_VOL | grep "^/" | awk '{print $4}'`
FINAL_SECONDS=`date "+%s"`
let CHANGE_IN_SIZE=$(($INITIAL_SIZE - $FINAL_SIZE ))
let DURATION_SECONDS=$(($FINAL_SECONDS - $INITIAL_SECONDS))
let DURATION_HOURS=$(($DURATION_SECONDS/3600))
let DURATION_SECONDS=$(($DURATION_SECONDS-$DURATION_HOURS*3600))
let DURATION_MINUTES=$(($DURATION_SECONDS/60))
let DURATION_SECONDS=$(($DURATION_SECONDS-$DURATION_MINUTES*60))
echo
echo "Backup Duration hr min s =" $DURATION_HOURS $DURATION_MINUTES $DURATION_SECONDS >> $LOG_FILE
echo "Backup Change in size KB MB GB =" $CHANGE_IN_SIZE \
$(( ($CHANGE_IN_SIZE+512)/1024 )) \
$(( ($CHANGE_IN_SIZE+512*1024)/1024/1024 )) >> $LOG_FILE
if [[ $RSYNC_ERROR_CODE != 0 ]]; then
ReportErrorAndExit "*** rsync reported error $RSYNC_ERROR_CODE"
fi
#===============================================================================
#Proactively repair the backup disk
#1 Get the device node for the backup volume.
# We will need this later.
DST_VOLUME_DEV=`diskutil info $DST_VOL | grep "Device Identifier: " | awk '{ print $3 }'`
#2 Loop trying to unmount the backup volume.
# This may take a few tries because Spotlight may be busy indexing the volume.
COUNTER=0
while [[ $COUNTER < 3 ]]; do
diskutil unmount $DST_VOL &> /dev/null
UNMOUNT_ERROR_CODE=$?
if [[ $UNMOUNT_ERROR_CODE == 0 ]]; then
let COUNTER=3;
else
let COUNTER=$COUNTER+1
echo "Could not unmount. Waiting 60 seconds. Attempt $COUNTER of 3."
sleep 60
fi
done
#3 Once we unomunted successfully, remount the drive
# We should now be cleared to run diskutil repairVolume without problems
# when the repair tries to unmount the volume.
diskutil mount $DST_VOLUME_DEV &> /dev/null
#...............................................................................
INITIAL_SIZE=`df -k $DST_VOL | grep "^/" | awk '{print $4}'`
INITIAL_SECONDS=`date "+%s"`
rm $TMP_FILE &> /dev/null
touch $TMP_FILE
tail $TMP_FILE&
echo "dm rv"
diskutil repairVolume $DST_VOL &> $TMP_FILE
REPAIR_ERROR_CODE=$?
kill %1 #Kill the tail command above.
if [[ $REPAIR_ERROR_CODE != 0 ]]; then
cat $TMP_FILE >> $LOG_FILE
rm $TMP_FILE &> /dev/null
ReportErrorAndExit "*** diskutil reported error $REPAIR_ERROR_CODE"
else
rm $TMP_FILE &> /dev/null
fi
sync
#...............................................................................
FINAL_SIZE=`df -k $DST_VOL | grep "^/" | awk '{print $4}'`
FINAL_SECONDS=`date "+%s"`
let CHANGE_IN_SIZE=$(($INITIAL_SIZE - $FINAL_SIZE ))
let DURATION_SECONDS=$(($FINAL_SECONDS - $INITIAL_SECONDS))
let DURATION_HOURS=$(($DURATION_SECONDS/3600))
let DURATION_SECONDS=$(($DURATION_SECONDS-$DURATION_HOURS*3600))
let DURATION_MINUTES=$(($DURATION_SECONDS/60))
let DURATION_SECONDS=$(($DURATION_SECONDS-$DURATION_MINUTES*60))
echo "Repair Duration hr min s =" $DURATION_HOURS $DURATION_MINUTES $DURATION_SECONDS >> $LOG_FILE
echo "Repair Change in size KB MB =" $CHANGE_IN_SIZE \
$(( ($CHANGE_IN_SIZE+512)/1024 )) >> $LOG_FILE
#===============================================================================
#Shift all the backup names down by 1.
$POWERSHIFT $DST_DIR 12 >>$LOG_FILE 2>&1
echo "============================================================" >> $LOG_FILE
#===============================================================================
Running the backup software longterm
At this point you now have the command line you need to backup your databases. You now need to think about your long-term backup strategy:
- how often will you back up?
- where will you save the backups?
- how to automate the process?
My answer to these questions, as I've already said, is to backup once an hour to a separate hard drive. To automate this we need, of course, a launchd file: Create the following text file, name it something like backupMailAndDBs.plist, and store it in /Library/LaunchDaemons.
<?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>backupMailAndDBs</string> <key>LowPriorityIO</key> <true/> <key>Nice</key> <integer>10</integer> <key>Program</key> <string>/Users/mjh/bin/bakPartial99.sh</string> <key>ProgramArguments</key> <array> <string>/Users/mjh/bin/bakPartial99.sh</string> </array> <key>RunAtLoad</key> <true/> <key>ServiceDescription</key> <string>Hourly backup of local MySQL data and mail directories</string> <key>StartInterval</key> <integer>3600</integer> </dict> </plist>
You will need to edit this file, changing the string /Users/mjh/bin/bakPartial99.sh (which appears twice) to whatever the relevant string is for your system.
You'll note that this launchd file tells the system to perform the backup using both low priority IO and a very high nice value (ie very low CPU usage), which together mean that the backup will happen basically during idle time on your server, rather than slowing down anything else.
Recovery
If something dreadful happens and you need to access your backups, what do you do?
The easiest route is to hope that a filesystem backup you have (through Time Machine or my backup scripts) works properly. Copy the files over and try out your WordPress and MediaWiki. Be sure to try not just reading but also writing the data. If things look right, maybe you're OK.
If things don't look right, or you want to be sure you're OK, you will want to
use the most recent version of the .sql files that the above backup script is
creating every hour.
For each database that you care about, you need a command like
mysql5 -u root -p databaseName < /volume/Backups/1/Databases/databaseName.sql
which will create the named database from the relevant sql file.
