|
|
|
|
| |
Using the Server's Auto-Recovery Capabilities
One of your first lines of defense in maintaining database integrity is the MySQL server's ability to perform automatic recovery at startup time. When the server begins executing, it can perform certain types of table checking to help deal with problems resulting from an earlier server or machine crash. MySQL is designed to recover from a variety of problems, so if you do nothing more than restart the server normally, it will make the necessary corrections for you in most cases. The possible actions taken by the server include the following:
If the InnoDB table handler is enabled, it can check for a variety of problems automatically. Committed transactions that are present in the redo logs but not yet flushed to tables are rolled forward (redone). Uncommitted transactions in progress at the time of the crash are rolled back (discarded) using the undo logs. The result is to leave your InnoDB tables in a consistent state, so that their contents reflect all transactions that had been committed up to the point of the crash.
The BDB table handler, if enabled, also attempts auto-recovery based on the contents of its log files.
The MyISAM handler can perform automatic checking and repair operations. This is controlled by the server's --myisam-recover=level option, where level can be empty to disable checking or a comma-separated list of one or more of the following values: DEFAULT (same as specifying no option), BACKUP (create a backup of the table if it is changed), FORCE (force recovery even if more than a row of data will be lost), or QUICK (quick recovery). The --myisam-recover option is available as of MySQL 3.23.25.
If InnoDB or BDB auto-recovery fails due to a non-recoverable problem, the server exits after writing a message to the error log. To force the server to start up anyway so that you can attempt a manual recovery procedure, see the "Recovering the InnoDB Tablespace or BDB Tables" section later in the chapter.
No automatic table startup timetable checking is available for ISAM tables. Nor is it likely there ever will be; ISAM support in MySQL is essentially frozen because MyISAM tables are preferable. I encourage you to consider converting your ISAM tables to MyISAM tables. To convert a table to MyISAM format, use an ALTER TABLE statement:
ALTER TABLE tbl_name TYPE = MYISAM;
You can also use the mysql_convert_table_format utility to convert all tables in a database with a single command. This script is written in Perl and requires that you have DBI installed. To see how to use it, invoke it with the --help option.
If you don't want to convert your ISAM tables, you can arrange to check them by invoking isamchk before the server starts up. Also, if your server is older than 3.23.25 (prior to the introduction of --myisam-recover), you can check your MyISAM tables by invoking myisamchk before the server starts up. The "Scheduling Preventive Maintenance" section later in this chapter discusses how to arrange for table checking with these utilities before the server starts up.
Backing Up and Copying Databases
It's important to back up your databases in case tables are lost or damaged. If a serious system crash occurs, you want to be able to restore your tables to the state they were in at the time of the crash with as little data loss as possible. Likewise, a user who issues an unwise DROP DATABASE or DROP TABLE will likely show up at your door requesting that you perform data recovery. Sometimes it's even the MySQL administrator who inadvertently causes the damage, for example, by trying to edit a table file directly using an editor, such as vi or emacs. This is certain to do bad things to the table!
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
