MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Backup and Restore Data in MySQL

Data Backup and Restore in MySQL

In this lesson of the MySQL tutorial, you will learn...
  1. To use the mysqldump utility to back up tables in a single or multiple databases.
  2. To use the backup files with mysql monitor to restore databases or tables.
  3. To use various mysqldump Options.
  4. To use binary logs to update restored databases.
  5. To understand InnoDB-specific such as checkpoints.

In this lesson of the MySQL tutorial, we learn about backup and restore mechanisms available in MySQL.



Managing Backup and Recovery

Print PDF

Despite the steps you take to secure and protect your databases, events such as power failures, natural disasters, and equipment failure can lead to the corruption and loss of data.

As a result, one of the most important steps that you can take to protect your data is to make certain that you maintain backup copies of your databases in a reliable and safe location. Ensuring that MySQL databases are backed up regularly should be part of any maintenance routine.

These backup files will contain the database and table definitions necessary to re-create your database structure as well as the instructions necessary to repopulate your tables. Using these backup files, you can recover your data to the state it was in at the time you performed the last backup. Further, you can use the binary log files to recover your data to post-backup current state. To summarize, backup and restore process involves:

  • Use the mysqldump utility to back up tables in a single database or multiple databases.
  • Use backup files with mysql monitor to reload databases.
  • Use binary log files to update the databases after reloading.

Backing up or Exporting Databases Using mysqldump

The mysqldump program is the primary method in MySQL for backing up tables and databases and it can back up individual databases, tables in those databases, or multiple databases. When you run mysqldump, the utility creates a text file that contains the SQL statements necessary to create your database and tables safely and add data to those tables. This file is referred to as a backup file or dump file.

This section describes the usage of mysqldump utility.

Copying Data Directory

It is possible to back up databases simply by copying the data directory to a backup location. Most installations routinely take disk snapshot backups at various points. This method has several limitations, though. For example, if data is being accessed and updated during file copy, you might be copying tables that are in an inconsistent state. In addition, file-level copying may help MyISAM tables but InnoDB tables can be more complicated at file level.

Portability

It is simpler to use mysqldump which saves data as portable text files versus OS-level data directory copies.

Using mysqldump

There are three syntax variants of mysqldump where you can backup a single database, several enumerated databases, or all databases managed by MySQL.

Using the single database variant, the backup may be done for chosen tables. As a rule, the output of mysqldump is sent to a file with > backupfile.sql.

mysqldump - Syntax

mysqldump [options] dbname [tables]
mysqldump [options] --databases [moreoptions] dbname1 [dbname2 ...]
mysqldump [options] --all-databases [moreoptions]
mysqldump --tab=/path/to/some/dir --opt dbname1

mysqldump - Options

The table lists some useful options that apply to mysqldump command.

mysqldump - Options

--add-drop-table Inserts a DROP TABLE command before every CREATE TABLE; when tables are read in, existing tables are deleted. This option is part of --opt and holds by default.
--add-locks Inserts LOCK TABLE before the first INSERT command and UNLOCK after the last INSERT command; generally speeds up reading in a database; this option is part of --opt and holds by default. However, the option should not be used with InnoDB tables.
--all Specifies all the detailed MySQL-specific options in the CREATE TABLE command.
-A --all-databases Saves all databases managed by MySQL; CREATE DATABASE and USE are placed in the backup file.
-B --databases Stores several databases.
--compatible=name Determines with what database system or standard the backup should be compatible. Allowable settings are ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options. It is allowed to have more than one key word, separated by commas.
--complete-inserts Generates for each data record a separate INSERT command.
--create-options Includes all MySQL-specific options in CREATE commands. This option is part of -- opt and holds by default.
--default-character-set

=name

Specifies the character set in which the backup file is to be created (by default UTF8).
--delayed-inserts Creates INSERT commands with the option DELAYED.
-K --disable-keys Inserts ALTER TABLE ... DISABLE KEYS or ... ENABLE KEYS before or after INSERT commands; indexes are thereby not updated until the end of the insertion process, which is faster.
-e --extended-insert Generates few INSERT commands with which several records can be inserted simultaneously (more efficient and reduces the size of the backup file). This option is part of --opt and holds by default.
-F --flush-logs Updates the logging files before the backup is begun.
-f --force Continues even after errors.
--hex-blob Outputs the content of binary fields (BLOBs) in hexadecimal format.
-x --lock-all-tables Executes LOCK TABLE READ for the entire database. This ensures that during the entire backup no table can be changed. Note that --lock-tables does not offer this protection, but prevents changes only during the time in which a table is locked. At this time another table can be changed.
-l --lock-tables Executes a LOCK TABLE READ for every table before the data are read; this ensures that no data can be changed while mysqldump is running. This option is part of -- opt and holds by default. The obvious draw-back is that all write processes are blocked until mysqldump is done, which can take a while with large databases (with InnoDB tables, you should use --single-transaction instead of --lock-tables).
--master-data [=n] Adds a comment at the end of the output containing a CHANGE MASTER command. This command is suitable for replication on a slave system. If n=1 is passed as parameter, then the CHANGE MASTER command is added in a normal fashion (not as a comment), so that it is automatically executed when the backup is restored. This option also activates --lock-all- tables if --single-transaction is not used at the same time.
--no-create-db Creates no CREATE DATABASES commands. (These commands are created only if the option --all-databases or --databases is used.)
--no-create-info Creates no CREATE TABLE commands, but only the INSERT commands.
--no-data Creates no INSERT commands (but only CREATE TABLE commands in order to restore the database schema).
--opt Shorthand for the following options: --add-drop-table, --create-options, --add-locks, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset. In most cases, this is an optimal setting, for which reason it holds by default. If you don't wish this, you must use --skip-opt.
-q --quick Outputs results record by record without internal intermediate storage. This option is part of --opt and holds by default. Without this option, first the entire table is moved into RAM and then output; the advantage of --quick is the lower memory requirement; the disadvantage is that the MySQL server is generally blocked for a longer period of time; --quick should definitely be used for very large tables (when the entire table cannot be held in RAM of the local computer).
-Q --quote-names Encloses table and column names in single quotes (e.g., 'name').
--set-charset Changes the active character set at the start of mysqldump output, and at the end, the previously valid character set is restored. This option is part of --opt and holds by default. It can be deactivated with --skip-char-set.
--skip-opt Deactivates the default option --opt.
--single-transaction Results in all tables being read within a single transaction; this makes sense only when InnoDB tables are used, in which Backup this option ensures that no data are changed during output. This option deactivates --lock-tables.
-T dir --tab=dir Writes the result directly into the specified directory, whereby for each table two files are created, one with the table structure (*.sql) and the second with the stored data in the format of the command SELECT ... INTO OUTFILE (*.txt).
-w cnd --where=condition Considers only data records that satisfy the WHERE condition cnd or condition; the entire option must be placed in quotation marks, e.g., "-wprice>5" or "--where=ID=3".
-X --xml Creates an XML file with the contents of the table (without information on the table structure).

If you are using --tab, then the second file (tablename.txt) contains the contents of the table directly (that is, not in the form of INSERT commands). This has several advantages: The resulting file is somewhat more compact, and a later importation can be executed significantly more quickly. (However, the operation is more complex, and only a single table can be handled.)

Formatting Options

See Export/Import lesson for formatting options.

The options --fields and --lines should each be set in quotation marks. The following example shows how you can pass the double quote itself as a character to the option:

> mysqldump -u root -p --tab /tmp "--fields-enclosed-by=\"" ...

To reinput the file thus generated (*.txt) with mysqldump, you can use either the program mysqlimport, discussed in the following section, or the SQL command LOAD DATA.

Backing up a Single Database

MySQL allows you to back up all tables in a database or only specific tables in that database. In both cases, you use the mysqldump client utility and you specify the name of the database. When backing up only specific tables, you must specify those table names as well. In this section, you learn how to perform both types of backups.

Backing up the Entire Database

The first form of the mysqldump command that you examine backs up all the tables in a database. The database is backed up to a backup file that includes the table definitions and the INSERT statements necessary to repopulate the tables. To use this form of the command, you must specify the name of the database and the path and filename of the backup file, as shown in the following syntax:

mysqldump  > 

As you can see, your command includes the mysqldump utility name, followed by the database name. The path and filename are then introduced by a right arrow (>) that tells the mysqldump utility to send the backed-up definitions and data to the specified file. If you do not include the right arrow and path and filename, the backup output would merely be displayed in your command line.

Here are a number of examples of mysqldump.

Code Sample:

mysqldump -usakila_admin -psakila sakila > c:\mysql\backups\sakila_080311.sql
Code Explanation

Entire backup of sakila.

All tables in the database will be backed up.

The script includes both the CREATE TABLE and INSERT statements.

The output is saved in c:\mysql\backups\sakila_080311.sql.

Code Sample:

mysqldump -usakila_admin -psakila -X sakila actor > c:\mysql\backups\actor_080311.xml
Code Explanation

Create an XML output file for the actor table.

Note: The filenames in our examples contain a date/time. It's a good idea to use some sort of consistent naming convention for your backup files to distinguish and locate the right one.

Contents of the Backup SQL Script

Once the file has been created, you can view its contents by using a text editor. The contents of the sakila_080311.sql file looks like:

Set Variables

MySQL saves some system values in user-defined variables to restore the original system settings should they be changed by any of the statements while executing the backup file, thereby ensuring that your environment is left in the same state after the restore via execution of the statements in the backup file.

The SET statement saves the current value associated with the character_set_client system variable to the @old_character_set_client user-defined variable.

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

We then use SET at the end of the backup file to restore character_set_client system variable from @old_character_set_client we had saved before the statements:

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;

The following table describes the system variables that are used to assign values to user-defined variables in the backup file.

System variable

Description

character_set_client The character set that MySQL uses to process SQL statements sent by a client application. By default, MySQL uses the latin1 character set.
character_set_results The character set that MySQL uses to return query results to a client application. By default, MySQL uses the latin1 character set.
collation_connection The collation associated with the character set used for the connection. By default, MySQL uses the latin1_swedish_ci collation.
unique_checks Specifies whether MySQL checks for uniqueness in a column configured with a unique index. By default, MySQL checks for uniqueness.
foreign_key_checks Specifies whether MySQL checks foreign key constraints in a column configured as a foreign key. By default, MySQL checks foreign key constraints.
sql_mode Specifies the SQL mode in which MySQL should operate. The mode determines what SQL syntax is supported and how data should be validated. By default, no mode is set.

Enclosing Characters /*! and */

Note that the SET statements begin with the /*! symbols and end with the */ symbols. The symbols ensure the statements are executed by MySQL but ignored if they are executed in another database management system. This allows your basic SQL statements in the backup file to be used by other database systems, while preventing errors on statements that are important and unique to MySQL.

These symbols at the beginning of the statements are also followed by a number which represents a MySQL server version. This number tells MySQL to execute the statement only if that version or a later version is being used. For example, 40101 indicates that the statement should be executed only on MySQL version 4.01.01 or above.

Set Names

Now take a look at one other SET statement that is included at the beginning of a backup file:

/*!40101 SET NAMES utf8 */;

The SET NAMES statement specifies the name of the character set that should be used during the execution of the statements in the backup file. The statement applies to current connection only. In this case, the SET NAMES statement causes utf8 character set to be used, same as when the backup file was created.

The contents of the backup file that we have seen so far is common to backup files created with the mysqldump utility.

Tables

The other information in the file is specific to the tables that you have backed up. For each table, the file includes a table definition and an INSERT statement. The table definition is introduced by comments similar to the following:

--
-- Table structure for table 'actor'
--

In this case, the comments tell you that the information that follows applies to the actor table. The comments are then followed by a DROP TABLE statement followed by the CREATE TABLE statement then defines the table as it was at time of backup.

After the CREATE TABLE statement, the backup file includes the section that inserts data in the actor table:

--
-- Dumping data for table 'actor'
--
LOCK TABLES 'actor' WRITE;
/*!40000 ALTER TABLE 'actor' DISABLE KEYS */;
INSERT INTO 'actor' VALUES (1,'PENELOPE','GUINESS','2006-02-15 09:34:33'),...;
/*!40000 ALTER TABLE 'actor' ENABLE KEYS */;
UNLOCK TABLES;

An ALTER TABLE statement that tells MySQL to disable the indexes precedes each INSERT statement. The ALTER TABLE statement at the end of this group of statements re-enables the indexes. MySQL does this to improve the performance of the insert operations where the indexes created after the inserts are done.

Warning: This process, however, works only for MyISAM table types and is ignored by other table types.

A LOCK TABLES statement also precedes the INSERT statement, placing a WRITE lock on the table so that no other values can be inserted in the table until after this INSERT statement has been executed. After the INSERT statement runs, the table is then unlocked.

The INSERT statement in the backup file provides values for all columns in the table as they were at time of backup. In usual Inserts, we do not specify several columns such as auto- increments, last-updates.

Ignoring Foreign Key Constraints

Both the INSERT statement and the CREATE TABLE statement that precedes it is that they create a table and insert data in a table that includes foreign key constraints.

If you were to try to create the same table manually before referenced tables are in place, you would encounter an error. MySQL, however, allows all tables to be created and values to be inserted when done through a backup file, regardless of the foreign key constraints.

This way, MySQL can ignore the order of appearance of tables in the backup file.

Flush Logs

The option --flush-logs flushes your log files and a new binary log file is created. This is important when creating a backup because binary log files allow you to restore your database fully.

By flushing the logs, you get an exact starting point for using the binary logs when refreshing your restored database. It is recommended that you use the --flush- logs option whenever you back up your data.

To flush the logs, see following example:

Code Sample:

mysqldump --flush-logs -u sakila_admin -psakila sakila > c:\mysql\backups\sakiladb_080311.sql
Code Explanation

Logs are flushed before the backup starts.

A new log file is created with an incremented suffix (discussed in another lesson).

Now when you need to restore the database, it will be easier locating the proper log(s) to read.

Backing up Individual Tables

To use the mysqldump client utility to back up individual tables in a database, add the applicable table names after the database name in your mysqldump command:

mysqldump  [ [
...]] >

Backing up Multiple Databases

You can back up multiple databases with the mysqldump client utility. The backup file will include not only tables but also the statements necessary to create the databases that are being backed up.

You can use two formats of the mysqldump utility to back up multiple databases. In the first format you specify the databases by adding the --databases option followed by at least one database name, and the second format allows you to back up all databases that are currently stored on your MySQL server.

 

Code Explanation

Back up the sakila and sakila1i databases.

Database Information in the Backup File

--
-- Current Database: 'sakila'
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'sakila';
USE 'sakila';

The file first includes comments indicating that the statements that follow apply to the sakila database. After the comments, the file contains a CREATE DATABASE statement, which will add the sakila database to your system. Notice that the statement includes the clause /*!32312 IF NOT EXISTS*/, which is not executed unless the statement runs against a MySQL server, version 3.23.12 or later.

Note: Using the multi-db form of the mysqldump command is useful if you want the backup file to contain the necessary database definition, which makes the file more comprehensive. If the backup file doesn't include the database definition, you must first manually create the database before restoring the backup.

Backing up all Databases

If you plan to back up all the databases on your system, use mysqldump as shown:

mysqldump --all-databases > 

The backup file will contain all the necessary database and table information for all the tables in all the databases.

Warning: With all-databases option, mysqldump will back up the mysql administrative database as well, which could be a security risk if the backup file is not properly secured.

Warning: mysqldump does not dump the INFORMATION_SCHEMA database, even if you name that database explicitly in your command - mysqldump quitely ignores the request.

Restoring Your Database

Despite your best efforts to protect your databases, disasters can occur, and you might find it necessary to restore one or more of your databases. If you have backed up your files regularly and enabled binary logging, restoring your database consists of two steps:

  1. Use the mysql monitor to execute the backup script to reload a MySQL database.
  2. Use the appropriate binary logs to update the database.

These two steps restore a database to the point of database errors, thus preventing any significant data loss in case of a faulure or a disaster.

Reloading Your Database

If you are restoring data from a backup file that does not include a database definition, the database must exist before restoring. For example, to restore a database from the sakila_041031.sql file, which does not include a database definition, you would use:

mysqldump --flush-logs -usakila_admin -psakila --databases sakila1 sakila > 
c:\mysql\backups\sakila_twodb_080311.sql

 

Code Explanation

Create database sakilaktsatfu.

Restore the sakila backup into sakilaktsatfu.

If you are restoring a database from a backup file that includes the necessary database definition, you need not specify a database with mysql monitor:

 

Code Explanation

Restore database sakila1 with database information.

You can also use the mysql client utility in interactive mode, as seen before:

source 

Code Sample:

DROP DATABASE IF EXISTS sakilaktsatfu;
CREATE DATABASE sakilaktsatfu;
USE sakilakutsatfu;
source c:\mysql\backups\sakiladb_080311.sql
Code Explanation

Create database sakilaktsatfu and restore the backup into sakilaktsatfu.

Creating a backup file that includes the necessary database definitions makes restoring your databases a simpler process. In addition, if you're restoring multiple databases from a single file, the file must contain the necessary database definitions.

Updating the Restored Database From Binary Log Files

Once database is reloaded, the data is only as current as your last backup, which is where binary logging comes in.

After you reload your database into your system, you will most likely want to get the database to its most current state since it was backed up. You will use binary logs which track all data modifications that occur in your databases.

MySQL provides two methods for applying updates from a binary log - restoring data directly from the binary log file or exporting binary log data to a text file and then restoring it from that file.

You must have binary logging enabled on your system to be able to use it to update a restored database, covered in other lessons.

Restoring Data Directly From a Binary Log

To apply updated data to the database that you've reloaded, you must know which log files apply. By comparing the log file timestamps to the backup file timestamp, you should be able to figure out easily which logs apply.

In addition, if you used the --flush-logs option when you backed up the database, you can start right at the beginning of the file instead of struggling to know where to look for data starting in the middle of a log file.

After you identify the log files that you should apply, you can use the mysqlbinlog client utility to execute the statements in the log file. For example, the following mysqlbinlog command executes the SQL statements in the -bin.000028 log file:

mysqlbinlog "c:\program files\mysql\mysql server 5.0\data\-bin.000028" | mysql

The mysqlbinlog command is followed by the path and the name of the log file. In addition, the command sends (pipes) the SQL statements to the mysql client utility to be executed as necessary. If you plan to execute multiple log files, you should start with the oldest one first and work your way through to the most current file.

If you want to apply the change in the log files to only one database, you can specify the --one-database option with the database name:

mysqlbinlog "c:\program files\mysql\mysql server 5.0\data\-bin.000028" |
mysql --one-database sakila1

Notice that you must include the name of the database after the --one-database option. When you do this, MySQL processes only those logged statements that apply to the specified database.

Selective Changes from Log Files

In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements).

The log file may contain statements that you don't want executed. For example, the log file might contain DROP DATABASE statements or CREATE TABLE statements that you don't want executed. There are really not many options to pick and choose which statements are executed except for specifying statements pertaining to a specific database. You can get around this issue by exporting the contents of the binary log file to a text file.

Restoring Binary Log Data From a Text File

The mysqlbinlog client utility allows you to export data to a text file. From there, you can sort through the text file to remove any statements that you don't want to execute. Of course, the larger the log file, the more difficult this process can be, but there might be times when this is the only way you can ensure that your database is fully restored. After you're satisfied that the text file contains only the correct statements, you can use the mysql client utility to execute the statements.

The first step, then, is to export the data in the log file to the text file. For example, the following mysqlbinlog command exports the mysql_bin.log.000028 log file to the binlog000028.txt file:

mysqlbinlog "\mysql_bin.log.000028" >
c:\mysql\backups\binlog000028.txt

After editing the text file as necessary, execute the statements in the text file:

mysql < \binlog000028.txt

All SQL statements that are saved in the text file are executed. If you want to run only statements related to a specific database, you can use the --one-database option in the same way you saw earlier, as shown in the following example:

mysql --one-database sakila < \binlog000028.txt

As the command shows, you specify the --one-database option and the name of the database, followed by the left arrow and the path and filename of the text file. Any updates that were recorded in the binary log file - and exported to the text file - are applied to the database.

Enabling and Disabling Binary Logging

When restoring databases and applying log file statements, you might find that you want to execute a statement that you don't want logged. For example, suppose that you want to drop a database before you restore it. If you run the DROP DATABASE statement, that statement is logged to the binary log file. You can manually turn off logging in a session by using a SET statement to set the sql_log_bin system variable, as shown in the following syntax:

SET SQL_LOG_BIN={0 | 1}

If sql_log_bin is set to 0, logging is disabled, generally before executing a statement that should not be logged. If set to 1, logging is enabled.

This allows you to control which statements are logged, which can be critical to restoring your database effectively.

As this section demonstrates, restoring a database is as simple as retrieving a backup file from your hard disk and then applying the statements in the applicable binary logs. In the following exercise, you restore the database that was backed up in the previous Try It Out section. To restore the database, you first remove the original database from your system and then use the source command in the mysql client utility to execute the SQL statement in the backup file.

Though the process of restoring a database is straightforward; it can be a very time-consuming process leading to substantial downtime for your database.

Recovering Corrupt MyISAM Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, we will follow the usual Restore progress described in the lesson.

mysqlhotcopy - A Database Backup Program

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make fast database or table backups, but there are some constraints.

  • Runs only on the same machine where the database directories are located.
  • Works only for backing up MyISAM and ARCHIVE tables.
  • Runs on limited operating systems like Unix and NetWare.

Read more about it at http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Backing Up and Recovering an InnoDB Database

If you can afford to shut down your MySQL server, you can make a binary backup that consists of all files used by InnoDB to manage its tables, using the following procedure:

  1. Shut down your MySQL server, ensure shut down proceeds without errors.
  2. Copy all your data files (ibdata files and .ibd files) into a secure and reliable location.
  3. Copy all your ib_logfile files.
  4. Copy your configuration file(s) (my.cnf or similar).
  5. Copy all the .frm files for your InnoDB tables.

Replication works with InnoDB tables, so you can use MySQL replication capabilities to keep a copy of your database at database sites requiring high availability.

In addition to making binary backups, you should also regularly make dumps of your tables with mysqldump. The reason for this is that a binary file might be corrupted with no visible signs. Dumped tables are stored into text files that are simpler and human-readable, so spotting table corruption becomes easier. mysqldump also has a --single- transaction option that you can use to make a consistent snapshot without locking out other clients.

In case your MySQL server crashes or shuts down abnormally, normally a restart will cause InnoDB to automatically check the logs and performs a roll-forward of the database to the present. InnoDB automatically rolls back uncommitted transactions at the time of the crash. While recovering, mysqld output looks like:

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 11239631
...
InnoDB: Doing recovery: scanned up to log sequence number 0 19864819
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 9287
InnoDB: Rolling back of trx no 9287 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

In some cases, apparent database page corruption may actually be only in the OS file cache and the data on disk may be fine. An OS-level restart may eliminate some of the perceived database errors.

In case of a serious data corruption or a disk failure, the recovery will have to performed from a backup. You must locate and use a backup that has no corruption. After restoring the base backup, do the recovery from the binary log files using mysqlbinlog as before.

In some corruption situations it may be sufficient to dump, drop, and re-create just the few corrupt tables. Use CHECK TABLE SQL statement to identify if a table is corrupt. Please note that CHECK TABLE cannot detect every possible corruption. You can also use innodb_tablespace_monitor to check the file integrity of the tablespace files. Find more on this in MySQL documentation.

In database page corruption situations, exporting your tables with SELECT INTO OUTFILE may get most of the data intact.

Stop Background Processes - Forced Recovery

It is possible that the corruption may cause the SELECT statements or InnoDB background operations to crash or lead to a InnoDB roll-forward recovery which then leads to a real crash. You can force the InnoDB engine to start without the background operations to dump your tables without headaches. Add the following option to the [mysqld] section of your configuration file before restarting the server:

[mysqld]
innodb_force_recovery = 4

The value innodb_force_recovery is bit- ored, so the larger values of this setting includes all precautions with smaller values. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

  • 1 (SRV_FORCE_IGNORE_CORRUPT)

    Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  • 2 (SRV_FORCE_NO_BACKGROUND)

    Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

  • 3 (SRV_FORCE_NO_TRX_UNDO)

    Do not run transaction rollbacks after recovery.

  • 4 (SRV_FORCE_NO_IBUF_MERGE)

    Prevent also insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

    Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

  • 6 (SRV_FORCE_NO_LOG_REDO)

    Do not do the log roll-forward in connection with recovery.

You can SELECT from tables to dump them, or DROP or CREATE tables even if forced recovery is used. If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

The database must not otherwise be used with any non-zero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

Recovery using Checkpoints

InnoDB implements a checkpoint mechanism known as fuzzy checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. Flushing pool in a single batch would halt processing of user SQL statements when the checkpointing is running.

During crash recovery, InnoDB looks for a checkpoint label written to the log files and the engine knows that all modifications to the database before the label are already present in the database disk image. Then InnoDB scans and applies modifications forward from the checkpoint, saving precious recovery time.

Note: Having very large log files saves disk I/O in checkpointing but the crash recovery can take longer due to larger logged information to apply. But, then how often are we planning on doing crash recovery?

InnoDB Hot Backup

InnoDB Hot Backup is mentioned on MySQL website as a commercial add-on online backup tool you can use to backup your InnoDB database while it is running, without needing a shut down or any locks or disturbing your normal database processing. Go to http://www.innodb.com/hot-backup for more information.

Data Backup and Restore in MySQL Conclusion

As this lessons shows, MySQL supports several methods to protect data loss. By backing up your data, you are recording changes made to your database offline. If you need to restore your database, you can use a backup file along with the applicable binary log files, to restore your system to its original state.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
mysql -u root -p --execute "CREATE DATABASE sakilaktsatfu;GRANT ALL 
ON sakilaktsatfu.* TO sakila_admin"
mysql -u sakila_admin -psakila sakilaktsatfu < c:\mysql\backups\sakiladb_080311.sql
mysql -u sakila_admin -psakila < c:\mysql\backups\sakila_withdb_080302.sql
 


mysql tools

How create data source in Visual Studio with data from My SQL tables with FK. more...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...