Learning MySQL (2007)

Part III. Advanced Topics

Chapter 10. Backups and Recovery

If you suffer a crippling attack, or your server has technical problems, you should have backups that allow you to quickly get a server up and running with relatively up-to-date data. The simplest way to create backups is to shut down the MySQL server and make a copy of the data directory (we listed common locations for the data directory in Server Doesn’t Start” in Chapter 2) to a secure location, and copy it back if required.

With a Windows system, you can right-click on the data directory folder and select the menu option to create a compressed folder. On a Linux or Mac OS X system, you can make a compressed package of all the databases on the server by typing:

# tar zcf /tmp/`date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.tgz mysql_data_directory

The backup file is created in the /tmp directory. The segment `date +"%Y.%m.%d.%H.%M"` is a trick to include a timestamp in the filename. The resulting compressed file will have a name like 2006.08.16.06.08.MySQL_Backup.tgz; an explicit record of the backup date and time is very useful when you need to recover data from a particular point in time.

The MySQL server must be stopped when you make a backup in this way, since you want the files on disk to be up-to-date and consistent. For a home user, this is inconvenient; for a production database, such downtime can be very disruptive and should be avoided when possible.

In this chapter, we explain alternative approaches to backing up and restoring your MySQL databases, and how to configure regular automatic backups. We also explain how to check and repair damaged database tables. Finally, we show how you can recreate a damaged mysql database.

Dumping a Database as SQL Statements

You can make a database backup by generating a file of all the SQL commands necessary to re-create the existing database structure from scratch, and (if you want) the SQL commands to insert all the data. Note that this is different from exporting table contents using the SELECT INTO OUTFILE syntax that we saw in Writing Data into Comma-Delimited Files” in Chapter 8 since we get the actual SQL INSERT statements, rather than just the raw values.

SQL statements are an excellent form of backup. One of the easiest ways to safely back up your data is to export it from MySQL, write it to stable media (such as a high-quality recordable CD or DVD), and store it in a safe location. Since the file of SQL statements contains just text, it can becompressed to a fraction of its original size using a compression program. Suitable compression programs on Linux or Mac OS X are gzip, bzip2, or zip; you can also use the StuffIt program under Mac OS X. Under Windows, you can compress a file by right-clicking on the file icon and selecting “Send To” and then “Compressed (zipped) Folder.” You can also use third-party tools such as WinZip and PKZIP.

Let’s try a simple example to back up the music database. To do this, we’ll run the mysqldump utility and save the output to the file music.sql:

$ mysqldump --user=root --password=the_mysql_root_password \

--result-file=music.sql music

This tries to create the file music.sql in the current directory. If you don’t have permission to write to the current directory, specify a path to another location—for example, /tmp/music.sql under Linux or Mac OS X, or C:\music.sql under Windows.

Now open this music.sql file using a text editor; if you’re unsure about how to do this, see the instructions in Using a Text Editor.” In the file, you’ll see something like this:

-- MySQL dump 10.10

-- Host: localhost Database: music

-- ------------------------------------------------------

-- Server version 5.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `album`

DROP TABLE IF EXISTS `album`;

CREATE TABLE `album` (

`artist_id` smallint(5) NOT NULL default '0',

`album_id` smallint(4) NOT NULL default '0',

`album_name` char(128) default NULL,

PRIMARY KEY (`artist_id`,`album_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `album`

/*!40000 ALTER TABLE `album` DISABLE KEYS */;

LOCK TABLES `album` WRITE;

INSERT INTO `album` VALUES (2,1,'Let Love In'),(1,1,'Retro - John McCready FAN'),

(1,2,'Substance (Disc 2)'),(1,3,'Retro - Miranda Sawyer POP'),

(1,4,'Retro - New Order / Bobby Gillespie LIVE'),(3,1,'Live Around The World'),

(3,2,'In A Silent Way'),(1,5,'Power, Corruption & Lies'),

(4,1,'Exile On Main Street'),(1,6,'Substance 1987 (Disc 1)'),

(5,1,'Second Coming'),(6,1,'Light Years'),(1,7,'Brotherhood');

UNLOCK TABLES;

/*!40000 ALTER TABLE `album` ENABLE KEYS */;

-- Table structure for table `artist`

DROP TABLE IF EXISTS `artist`;

CREATE TABLE `artist` (

`artist_id` smallint(5) NOT NULL default '0',

`artist_name` char(128) default NULL,

PRIMARY KEY (`artist_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `artist`

/*!40000 ALTER TABLE `artist` DISABLE KEYS */;

LOCK TABLES `artist` WRITE;

INSERT INTO `artist` VALUES (1,'New Order'),(2,'Nick Cave & The Bad Seeds'),

(3,'Miles Davis'),(4,'The Rolling Stones'),(5,'The Stone Roses'),

(6,'Kylie Minogue');

UNLOCK TABLES;

/*!40000 ALTER TABLE `artist` ENABLE KEYS */;

-- Table structure for table `played`

DROP TABLE IF EXISTS `played`;

CREATE TABLE `played` (

`artist_id` smallint(5) NOT NULL default '0',

`album_id` smallint(4) NOT NULL default '0',

`track_id` smallint(3) NOT NULL default '0',

`played` timestamp NOT NULL default CURRENT_TIMESTAMP on update

CURRENT_TIMESTAMP,

PRIMARY KEY (`artist_id`,`album_id`,`track_id`,`played`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `played`

/*!40000 ALTER TABLE `played` DISABLE KEYS */;

LOCK TABLES `played` WRITE;

INSERT INTO `played` VALUES

(1,3,0,'2006-08-14 00:21:03'),(1,3,1,'2006-08-14 00:25:22'),

(1,3,2,'2006-08-14 00:30:25'),(1,3,3,'2006-08-14 00:36:54'),

(1,3,4,'2006-08-14 00:41:43'),(1,3,5,'2006-08-14 00:43:37'),

(1,3,6,'2006-08-14 00:47:21'),(1,3,7,'2006-08-14 00:54:02'),

(3,1,0,'2006-08-15 04:00:03'),(3,1,1,'2006-08-15 04:26:12'),

(3,1,2,'2006-08-15 04:33:57');

UNLOCK TABLES;

/*!40000 ALTER TABLE `played` ENABLE KEYS */;

-- Table structure for table `track`

DROP TABLE IF EXISTS `track`;

CREATE TABLE `track` (

`track_id` smallint(3) NOT NULL default '0',

`track_name` char(128) default NULL,

`artist_id` smallint(5) NOT NULL default '0',

`album_id` smallint(4) NOT NULL default '0',

`time` time default NULL,

PRIMARY KEY (`artist_id`,`album_id`,`track_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Dumping data for table `track`

/*!40000 ALTER TABLE `track` DISABLE KEYS */;

LOCK TABLES `track` WRITE;

INSERT INTO `track` VALUES (0,'Do You Love Me?',2,1,'00:05:95'),

(1,'Nobody's Baby Now',2,1,'00:03:87'),(2,'Loverman',2,1,'00:06:37'),

(3,'Jangling Jack',2,1,'00:02:78'),(4,'Red Right Hand',2,1,'00:06:18'),

(5,'I Let Love In',2,1,'00:04:25'),(6,'Thirsty Dog',2,1,'00:03:81'),

...

UNLOCK TABLES;

/*!40000 ALTER TABLE `track` ENABLE KEYS */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

As we explained in Chapter 5 in Exploring Databases and Tables with SHOW and mysqlshow,” the text between the /*! ... */ symbols contains MySQL-specific instructions. Notice several features in this dump file:

§ CREATE TABLE statements for all tables in the database that are identical to the output of SHOW CREATE TABLE.

§ DROP TABLE statements that precede each CREATE TABLE statement. These allow you to load the file into your MySQL database without error, even when the tables already exist; of course, you’ll lose any data that may already be on the server in this table of the database.

§ INSERT statements that add all of the data to the tables. There’s only such statement per table, that is, the rows are each parenthesized and comma-separated.

§ LOCK TABLES and UNLOCK TABLES statements. These ensure that you’re the only user modifying or using a table when you’re inserting the data, and they also speed up the inserts. We discuss locking briefly in Transactions and Locking” in Chapter 7.

You’ll also notice two missing features:

§ There’s no CREATE DATABASE statement to set up the database.

§ There’s no USE statement that selects the database.

Fortunately, you can use command-line parameters to customize what mysqldump does. We’ll show you some examples next. You might find that your mysqldump output doesn’t exactly match what we’ve stated here, but don’t worry; the defaults change over time, and everything can be customized.

mysqldump Options

The mysqldump program has options to control whether tables should be locked when making the dump, whether restoring a dump should overwrite any existing tables, and so on. These options can be appended as parameters, just like the user and password options for the username and password, respectively. Here’s a list of the most useful options, but the default settings should be sufficient for most cases:

add-drop-table

Includes a DROP TABLE statement for each table, ensuring that any existing table data is removed before the dump is restored.

add-locks

Includes a LOCK TABLES statement before each data INSERT statement, and a corresponding UNLOCK TABLES statement afterward. Helps speed up data restoration from the dump file.

all-databases

Creates a dump of all databases on the server. This means you don’t have to supply any database names on the command line. We’ll show you an example of this later in this section.

create-options

Includes MySQL-specific information such as ENGINE and CHARSET in the table creation statements.

databases

Create a dump of the specified databases. This also ensures—even if you list only one database—that CREATE DATABASE and USE statements are added to the output.

disable-keys

Tells MySQL to disable index updates during the INSERT operations for MyISAM tables; the index is created after all the data has been loaded, which is more efficient.

extended-insert

Combines INSERT statements so that each statement inserts multiple table rows; this helps speed up data restoration.

flush-logs

Flushes the server logs before dumping the data. This is useful in conjunction with incremental backups, as described later in The Binary Log.”

lock-tables

Locks all the tables in a database for the duration of the dump so that the dump is a consistent snapshot.

no-data

Dumps only the information necessary to re-create the database structure and leaves out the data; the dump file will have no INSERT statements.

opt

This option, which is enabled by default from MySQL version 4.1 onwards, enables the options add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, and set-charset. You can disable all these by using the skip-optoption, or you can disable individual options by adding the prefix skip- in front of them; for example, to disable add-locks, you’d write skip-add-locks. However, they’re all sensible defaults that you’re likely to want in most cases.

quick

Prevents mysqldump from buffering tables in memory before writing to the file; this speeds up dumps from large tables.

result-file

Specifies the name of the output dump file, where the SQL commands are stored.

set-charset

Specifies the character set—for example, latin1 or utf8—used by the database.

tables

Creates a dump of the specified database tables.

where

Dumps only records meeting a specified WHERE clause.

You can use mysqldump in four main ways (assume you want to get the database dump in the file outputfile.sql):

§ To make a backup of all the databases on a MySQL server, use the command:

§ $ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql --all-databases

This dumps CREATE DATABASE, USE, CREATE TABLE, and INSERT statements for all data in all databases that are accessible by the user root. If you specify a user other than root, the output is affected by the privileges of that user.

§ To make a backup of specific databases, use the command:

§ $ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql --databases database_name

This dumps CREATE DATABASE, CREATE TABLE, and INSERT statements for only the specified databases. Use this if you want a CREATE DATABASE statement, in preference to the variant we showed you at the beginning of this section.

You can list several databases one after the other in the command. For example, to dump the music and wedding databases, you would type:

$ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql --databases music wedding

§ To make a backup of specific tables from a database, use the command:

§ $ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql database_name table_name

You can list several tables, one after the other, in the command.

§ To make a backup of specific data from a table in a database, use the command:

§ $ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql database_name table_name where=where_clause

For example, to use the artist table of the music database, and dump SQL statements for all the artists having a name beginning with “N”, you would write:

$ mysqldump --user=root --password=the_mysql_root_password \

--result-file=outputfile.sql \

--where="artist_name like 'N%'" \

music artist

Loading Data from an SQL Dump File

The previous section showed you how to back up your databases. Let’s see how to restore them from those backups.

To load the structures and data in a dump file, you can tell the MySQL monitor to read in the SQL commands from the file:

mysql> SOURCE dumpfile.sql

Alternatively, you can simply run the MySQL monitor in batch mode and execute the instructions in the dump file:

$ mysql mysql_options < dumpfile.sql

We don’t recommend this approach, as it’s a little less portable than the SOURCE command; more importantly, it doesn’t show you any error and warning messages as the SQL statements are processed.

If the backup file doesn’t have CREATE DATABASE and USE statements, you’ll need to type these into the monitor before you read in the dump file, or add them to the dump file if you want to run the monitor in batch mode. A good step prior to carrying out a restore operation is to inspect the backup file with a text editor. Once you’ve inspected the file, you can decide whether you need to drop and re-create databases, use databases whether you need to take any other steps prior to a restore operation. Of course, you can use the mysqldump options to control what’s written to the dump file when it’s created.

You previously backed up the music database to the file music.sql. The way you did this didn’t include any CREATE DATABASE and USE statements in the dump file, so you need to use the monitor to enter these yourself.

Start the monitor as the root user:

$ mysql --user=root --password=the_mysql_root_password

Now, drop the existing music database:

mysql> DROP DATABASE music;

and create a new (empty) database with the same name:

mysql> CREATE DATABASE music;

Then select the music database as the active database:

mysql> USE music;

Now you can restore the data by reading in the music.sql dump file:

mysql> SOURCE music.sql;

If your music.sql file isn’t in the current directory, you should specify the full path. If you used our earlier suggestions in Dumping a Database as SQL Statements,” this path would be /tmp/music.sql under Linux or Mac OS X, and C:\music.sql under Windows.

mysqlhotcopy

If you want to create a copy of a database on the same host as the server, and all the tables in your database are of the MyISAM (or the older ISAM) type, then you may find mysqlhotcopy handy. This is a Perl script file that’s in the scripts directory, and differs from mysqldump in that it’s abinary copy, so you get the MySQL database files, not a text file of SQL statements, after copying. It’s also faster.

You may wonder why you need a special command to copy the database files. After all, they’re already there in the data directory, and you could use the operating system copy command (e.g., cp or copy) to copy them. The problem is that if the server is running, what you have on disk is not always consistent with the status according to the MySQL server. The mysqlhotcopy command takes care of the locking needed to ensure that the copies are consistent, even if the server is running.

Let’s look at an example that copies the database music to the database music_bak:

# mysqlhotcopy --user=root --password=the_mysql_root_password music music_bak

Locked 4 tables in 0 seconds.

Flushed tables (`music`.`album`, `music`.`artist`, `music`.`played`, `music`.`track`)

in 0 seconds.

Copying 13 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 4 tables (13 files) in 1 second (1 seconds overall).

There are two things worth mentioning here. First, the server has to be running when you run mysqlhotcopy. Second, you must have operating-system-level access to the database files. For example, you would need to be logged in as the user who owns the MySQL data directory (this could be you, or the mysql user), or as the system root user.

Note that mysqlhotcopy is a Perl script, and you’ll need to follow the instructions in Installing Perl modules under Windows” in Chapter 2 to use this on Windows. Linux and Mac OS X users should be able to use this script without problems.

To restore a database from the backup copy, you should stop the server, copy the backup directory to the MySQL data directory, and restart the server. To restore all databases on a server, you’ll need backups of all the individual databases, as well as the mysql grants database.

Scheduling Backups

We all forget to do backups, and as Murphy’s Law would have it: “The hard drive on your computer will crash only when it contains vital information that has not been backed up” (for this and other interesting variations on Murphy’s Law, see http://www.murphys-laws.com). In this section, we’ll describe how you can configure automatic, regular backups using mysqldump; you can also use mysqlhotcopy if you wish.

Linux and Mac OS X

Under Linux and Mac OS X, you can list the commands you want to be executed in a crontab file; commands in the crontab file are run at the times you specify. First, you have to edit a crontab file:

$ crontab -e

This opens the crontab file for the current user for editing; the default editor on most systems is vi. If you’re not comfortable with this editor, you can specify your preferred editor by setting the EDITOR variable to the name of your favorite editor. For example, many novice users find thepico editor somewhat easier to use:

$ export EDITOR=pico

$ crontab -e

The general format of a crontab entry is:

MINUTE HOUR DAY MONTH DAYOFTHEWEEK COMMAND

If you want a dump to be created from a particular database using the mysqldump command at 4:45 A.M. every Sunday, you can add the line:

45 4 * * sun /usr/local/mysql/bin/mysqldump \

--user=root \

--password=the_mysql_root_password \

--result-file=path_to_backup_file \

database_to_dump

Note that each entry must be on one line, and you must specify full paths to executables; the cron program might not inherit your path settings.

SQL files have a lot of repeating information that can be highly compressed. You can create compressed SQL files by passing the mysqldump output to the gzip compression program:

45 4 * * sun /usr/local/mysql/bin/mysqldump \

--user=root \

--password=the_mysql_root_password \

database_to_dump \

| gzip --best --to-stdout \

> dump_directory/`date +"%Y.%m.%d.%H.%M"`.MySQL_Backup.sql.gz

Here, we’ve left out the result-file option so that the mysqldump output is passed directly to the standard output (normally the screen), rather than to a file. The pipe symbol (|) then sends this output to the gzip compression program. The best option tells gzip to compress the data as much as possible, while the to-stdout option tells gzip to pass its own output to the standard output. Finally, the greater-than symbol (>) redirects this compressed data into a file. We’ve included the string:

`date +"%Y.%m.%d.%H.%M"`

as part of the result filename so that the filename includes a timestamp. The resulting compressed SQL dump file will be given a name like 2006.08.16.06.08.MySQL_Backup.sql.gz;

Check that your changes have been saved by typing crontab -l (the "l" stands for list). It’s also useful to first test the command yourself from the shell prompt. When entering the command in the crontab file, use a time that’s near so that you can monitor that things are working as you expect. There are few things more depressing than finding that your regular backups weren’t being done properly, and that you can’t recover your lost data. You can edit the file again later and set the regular backup times you actually require.

Any output messages from the automatic execution are generally emailed to the crontab owner; you can specify a different address by defining the MAILTO variable at the top of your crontab file:

MAILTO=your_email_address

Windows XP

Under Windows XP, you can add a scheduled task by selecting Scheduled Tasks by opening the Windows Control Panel, selecting the “Performance and Maintenance” entry, and choosing Scheduled Tasks. If you have Classic View enabled, you can choose Scheduled Tasks directly from the Windows Control Panel. Select Add Scheduled Task, browse to the MySQL bin directory, and select mysqldump.exe. Select how frequently you want to run this program; at the end of the configuration process, select the checkbox for “Open advanced properties for this task when I click Finish,” and then click the Finish button. In the Run tab, type in the full command below. When prompted to specify your password, enter your Windows password. Note that scheduled tasks don’t run if you don’t have a password set for your Windows account:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe" \

--user=root \

--password=the_mysql_root_password \

--result-file=C:\outputfile.sql \

database_name

Under Windows Vista, take the following steps. Open the Windows Control Panel, and select the “System and Maintenance” entry, and then select the Administrative Tools. If you have Classic View enabled, choose Administrative Tools directly from the Control Panel. From the Administrative Tools, choose the Task Scheduler entry. Windows may prompt you for authorization—click Continue.

From the Task Schedule window, choose the Create Task entry from the Actions menu on the right. A dialog box will open with several tabs at the top. On the first tab (General), enter a name for the new task—for example, “MySQL daily dumps.”

Select the next tab (Triggers); click the “New...” button, and select the backup schedule you want—for example, “Daily at 3.20 AM”—and click the OK button. Select the next tab (Actions); click the “New...” button, and ensure that the Action drop-down list is set to “Start a program.” In the“Program/script” text box, type in the full command from before; you can use the Browse button to find select the mysqldump.exe program, and then you can add the user, password, database, and result-file options yourself. Click the OK button to close the New Action dialog box, and then again to close the Create Task window. Your new task should now appear in the list of Active Tasks.

General Backup Tips

The frequency of your backups depends on how often data updates occur in your application, and how valuable those updates are to your organization. For example, you might be able to absorb the loss of some or all user comments on your hobby blog, but not the sales data for your high-throughput online store, or a university student-marks database.

When you’re backing up a database, it’s wise to ensure that MySQL isn’t being used by other users. This allows you to get a consistent backup, where all operations that have been intended to run have completed. You can ensure single-user access by having mysqldump lock the tables using the lock-tables or the opt option. If for some reason you can’t lock the database, don’t be overly concerned: having a near-perfect backup is usually much better than no backup at all.

Make sure that the backup ends up on stable media—such as flash memory or a high-quality writable CD or DVD—and that the stable media isn’t stored with the computer. There’s little point in storing a backup on the same disk as the MySQL databases, since the backup would disappear with the databases in case of a disk failure. There’s also little point in storing the backup on a computer, CD, or flash memory device nearby, since theft or destruction would result in the loss of everything. Get in the habit of storing your backup offsite; we sometimes swap backups with family members who live nearby, and often leave a home backup at work. Alternatively, copy your backups to a trustworthy online storage site; a simple web search for “online storage service” turns up many low-cost and free services.

Take care to regularly check that your backups are occurring correctly and are usable. Often, the only time people look at their backups is when they need them, and there are few things as frustrating as finding that the backups you desperately need have not been generated correctly due to a problem such as a full backup device.

Finally, remember to treat the security of your backups with the same seriousness as you do the server; an attacker could get access to your company’s sensitive data by simply stealing a backup DVD from your home. Think carefully before you trust any person or organization with your valuable data.

The Binary Log

An update log contains all the information needed to re-create any changes to the database since the server was started or the logs were flushed; this feature allows you to always have an up-to-date backup of your database. You can keep a list of every SQL query that changes data on the server by passing the log-bin option to the MySQL server (mysqld_safe, mysqld-nt.exe, or mysqld).

If no preferred name and directory is specified for the logfile, the server will use the file <hostname>-bin in the MySQL data directory. Individual logfiles will have the extensions .000001, .000002, and so on; any extensions you specify to the log-bin option are ignored. For example, on a machine with the hostname eden, the binary logfiles are typically named eden-bin.000001, eden-bin.000002, and so on. It’s also common to see the word mysql used in place of the hostname. The update log is saved in a compact binary format; prior to MySQL version 5.0, the log-updateoption would save an update log in text format. However, the text format is deprecated and is treated the same as log-bin in MySQL 5.0 and later.

When the server is shut down, it ensures that all modifications to data have been written (flushed) to the binary log. The next time the server is started, it opens a new logfile alongside the old one with an incremented number in the extension. For example, the current binary logfile might be called eden-bin.000012; after the server is restarted, it creates the new logfile eden-bin.000013 to log all modifications to the database since the restart. The logs can be manually flushed at any time using the FLUSH LOGS command in the monitor, or the mysqladmin flush-logscommand from the command line.

You can view the SQL statements in the binary log by using the msqlbinlog command and specifying the full path to the binary logfile. For example, if on this system the MySQL data directory is /usr/lib/mysql/data, you can view the contents of the binary logfile eden-bin.000002 by typing:

# mysqlbinlog /usr/lib/mysql/data/eden-bin.000002

You’ll need to have the necessary permissions to access the MySQL data directory and to read the binary logfile on your host system. You might see something like this when you open a logfile:

...

use music;

SET TIMESTAMP=1151221361;

SET @@session.foreign_key_checks=0, @@session.unique_checks=0;

SET @@session.sql_mode=524288;

/*!\C utf8 */;

SET @@session.character_set_client=33,@@session.collation_connection=33,

@@session.collation_server=8;

DROP TABLE IF EXISTS `artist`;

# at 30551

#060625 17:42:41 server id 1 end_log_pos 30794 Query thread_id=168

exec_time=0 error_code=0

SET TIMESTAMP=1151221361;

CREATE TABLE `artist` (

`artist_id` smallint(5) NOT NULL default '0',

`artist_name` char(128) default NULL,

PRIMARY KEY (`artist_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# at 30794

#060625 17:42:41 server id 1 end_log_pos 30903 Query thread_id=168

exec_time=0 error_code=0

SET TIMESTAMP=1151221361;

...

Note that the text between the /*! ... */ symbols contains MySQL-specific instructions, as described in Exploring Databases and Tables with SHOW and mysqlshow” in Chapter 5.

You should create regular dumps of the database using mysqldump with the flush-logs option. In the event of a disaster, you can follow the instructions described earlier in Loading Data from an SQL Dump File” to restore the database to the state it was at the time you generated the dump file. You can then use mysqlbinlog to extract the SQL statements from all the binary logs, and the pipe symbol (|) to pass them to the monitor in batch mode:

# mysqlbinlog hostname-bin.* | mysql

The asterisk wildcard character (*) tells the operating system to read all the files that have names starting with <hostname-bin>.

Checking and Repairing Corrupted Tables

Problems such as running out of disk space or a power failure could cause your databases files to be corrupted; in these cases, the server will often not have written all transactions to disk. It’s a good idea to check the tables before you start to use them again. Repairing tables will not guarantee that no data will be lost, but it does allow you to use the database again without losing any more data.

One way to check and repair tables is to use the CHECK TABLE and REPAIR TABLE commands from the monitor. For example, to check the artist table in the music database, you would write:

mysql> CHECK TABLE music.artist;

+--------------+-------+----------+------------------------------+

+--------------+-------+----------+------------------------------+

+--------------+-------+----------+------------------------------+

2 rows in set (0.00 sec)

In this example, the table is damaged; you can repair it using the REPAIR TABLE command:

mysql> REPAIR TABLE music.artist;

+--------------+--------+----------+----------+

+--------------+--------+----------+----------+

+--------------+--------+----------+----------+

1 row in set (0.00 sec)

If the music database was previously selected with the USE music command, you can write artist instead of music.artist.

The mysqlcheck and mysqlisamchk programs allow you to check and repair tables from the command line.

mysqlcheck

mysqlcheck allows you to check and repair tables from the command line. In practice, the most important options you’ll need are:

all-databases

Performs operation on all tables in all databases on the server.

repair

Tries to repair any corrupted tables.

extended

Tries harder to repair any corrupted tables (slower than just repair).

For example, to check and repair all tables in the music database, you would write:

$ mysqlcheck --user=root --password=the_mysql_root_password --repair music

music.album OK

music.artist

warning : Number of rows changed from 1 to 0

status : OK

music.played OK

music.track OK

To check and attempt to repair all databases on the server, you would write:

$ mysqlcheck --user=root --password=the_mysql_root_password --extended --all-databases

myisamchk

This tool operates directly on the MyISAM database files, and so does not require the server to be shut down. However, you need to ensure that the server is not using the tables while you’re trying to repair them; if you can’t stop queries to the server, it’s probably a good idea to shut down the server before using myisamchk.

To use this utility, you need to specify the table or index file you want to check or repair. For example, to check the artist table in the music database, give the path to the artist.MYI file:

$ myisamchk --check /var/lib/mysql/music/artist.MYI

Checking MyISAM /var/lib/mysql/music/artist.MYI

Data records: 87 Deleted blocks: 0

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check data record references index: 1

myisamchk: error: Can't read indexpage from filepos: 1024

- check record links

myisamchk: error: Found wrong record at 0

MyISAM-table '/var/lib/mysql/music/artist.MYI' is corrupted

Fix it using switch "-r" or "-o"

Let’s try to repair the table:

$ myisamchk --recover /var/lib/mysql/music/artist.MYI

- recovering (with sort) MyISAM-table '/var/lib/mysql/music/artist.MYI'

Data records: 87

- Fixing index 1

Key 1 - Found wrong stored record at 0

Found block with too small length at 3060; Skipped

Found block that points outside data file at 19024

Found block that points outside data file at 19824

Found block with too small length at 20052; Skipped

Found block with too small length at 20636; Skipped

Found block that points outside data file at 22860

Found block that points outside data file at 23344

Found block that points outside data file at 30836

Found block with too small length at 30980; Skipped

Found block that points outside data file at 32628

Found block that points outside data file at 32868

Found block that points outside data file at 33660

Found block that points outside data file at 33752

Data records: 0

Now, let’s see if this had the desired effect:

$ myisamchk --check /var/lib/mysql/music/artist.MYI

Checking MyISAM /var/lib/mysql/music/artist.MYI

Data records: 0 Deleted blocks: 0

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check data record references index: 1

- check record links

The error has been fixed, but, of course, some data could have been lost as a result of the problem.

Re-Creating Damaged Grant Tables

If you cannot restore your mysql grants database from backup, you will need to create a fresh one. With Windows, you can extract the mysql directory from the installation package and place it under the MySQL data directory.

Under Linux or Mac OS X, you can use the mysql_install_db script to regenerate the mysql database and the privilege tables in it. This is particularly handy if your mysql database has somehow become corrupted. Note that if the user table has to be created, the root password for the server will be reset to the default value (blank).

If mysql_install_db isn’t already in your system path, you can generally find it in the scripts directory under your MySQL installation directory. Run mysql_install_db the same way you ran it in Chapter 2. If you’re not sure how to run the script, try logging in as the system superuser and running it with no parameters:

# mysql_install_db

or optionally with the user=mysql parameter so that MySQL is configured to run under the mysql system user account:

# mysql_install_db --user=mysql

Resources

To learn more about backing up MySQL databases, see the “Database Backups” section of the MySQL manual (http://dev.mysql.com/doc/mysql/en/backup.html).

Exercises

1. SQL dump files are often very large; why is this generally not a cause for worry?

2. Set up a weekly backup of all databases on your server.

3. For a production server, what time would you choose for your regular backups?

4. How can you recover modifications that have been made to your data since the last dump?

5. For an application where any loss of data is unacceptable, how would you choose the location of your binary logfile?