Backup and Recovery - MariaDB - Databases - Software - Computers


Backup and Recovery
Prev Next

Backup and Recovery

Table of Contents

Backup and Recovery Types
Database Backup Methods
Example Backup and Recovery Strategy
Establishing a Backup Policy
Using Backups for Recovery
Backup Strategy Summary
Using mysqldump for Backups
Dumping Data in SQL Format with mysqldump
Reloading SQL-Format Backups
Dumping Data in Delimited-Text Format with mysqldump
Reloading Delimited-Text Format Backups
mysqldump Tips
Point-in-Time (Incremental) Recovery Using the Binary Log
Point-in-Time Recovery Using Event Times
Point-in-Time Recovery Using Event Positions
MyISAM Table Maintenance and Crash Recovery
Using myisamchk for Crash Recovery
How to Check MyISAM Tables for Errors
How to Repair MyISAM Tables
MyISAM Table Optimization
Setting Up a MyISAM Table Maintenance Schedule

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake. Backups are also essential as a safeguard before upgrading a MariaDB installation, and they can be used to transfer a MariaDB installation to another system or to set up replication slave servers.

MySQL offers a variety of backup strategies from which you can choose the methods that best suit the requirements for your installation. This chapter discusses several backup and recovery topics with which you should be familiar:

Additional Resources

Resources related to backup or to maintaining data availability include the following:

Backup and Recovery Types

This section describes the characteristics of different types of backups.

Physical (Raw) Versus Logical Backups

Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.

Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture.

Physical backup methods have these characteristics:

Logical backup methods have these characteristics:

Online Versus Offline Backups

Online backups take place while the MariaDB server is running so that the database information can be obtained from the server. Offline backups take place while the server is stopped. This distinction can also be described as "hot" versus "cold" backups; a "warm" backup is one where the server remains running but locked against modifying data while you access database files externally.

Online backup methods have these characteristics:

Offline backup methods have these characteristics:

A similar distinction between online and offline applies for recovery operations, and similar characteristics apply. However, it is more likely that clients will be affected for online recovery than for online backup because recovery requires stronger locking. During backup, clients might be able to read data while it is being backed up. Recovery modifies data and does not just read it, so clients must be prevented from accessing data while it is being restored.

Local Versus Remote Backups

A local backup is performed on the same host where the MariaDB server runs, whereas a remote backup is done from a different host. For some types of backups, the backup can be initiated from a remote host even if the output is written locally on the server. host.

Snapshot Backups

Some file system implementations enable "snapshots" to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. (For example, the implementation may use copy-on-write techniques so that only parts of the file system modified after the snapshot time need be copied.) MariaDB itself does not provide the capability for taking file system snapshots. It is available through third-party solutions such as Veritas, LVM, or ZFS.

Full Versus Incremental Backups

A full backup includes all data managed by a MariaDB server at a given point in time. An incremental backup consists of the changes made to the data during a given time span (from one point in time to another). MariaDB has different ways to perform full backups, such as those described earlier in this section. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.

Full Versus Point-in-Time (Incremental) Recovery

A full recovery restores all data from a full backup. This restores the server instance to the state that it had when the backup was made. If that state is not sufficiently current, a full recovery can be followed by recovery of incremental backups made since the full backup, to bring the server to a more up-to-date state.

Incremental recovery is recovery of changes made during a given time span. This is also called point-in-time recovery because it makes a server's state current up to a given time. Point-in-time recovery is based on the binary log and typically follows a full recovery from the backup files that restores the server to its state when the backup was made. Then the data changes written in the binary log files are applied as incremental recovery to redo data modifications and bring the server up to the desired point in time.

Table Maintenance

Data integrity can be compromised if tables become corrupt. For InnoDB tables, this is not a typical issue. For programs to check MyISAM tables and repair them if problems are found, see , "MyISAM Table Maintenance and Crash Recovery".

Backup Scheduling, Compression, and Encryption

Backup scheduling is valuable for automating backup procedures. Compression of backup output reduces space requirements, and encryption of the output provides better security against unauthorized access of backed-up data. MariaDB itself does not provide these capabilities. The MariaDB Enterprise Backup product can compress InnoDB backups, and compression or encryption of backup output can be achieved using file system utilities. Other third-party solutions may be available.

Database Backup Methods

This section summarizes some general methods for making backups.

Making a Hot Backup with MariaDB Enterprise Backup

Customers of MariaDB Enterprise Edition can use the MySQL Enterprise Backup product to do physical backups of entire instances or selected databases, tables, or both. This product includes features for incremental and compressed backups. Backing up the physical database files makes restore much faster than logical techniques such as the mysqldump command. InnoDB tables are copied using a hot backup mechanism. (Ideally, the InnoDB tables should represent a substantial majority of the data.) Tables from other storage engines are copied using a warm backup mechanism. For an overview of the MariaDB Enterprise Backup product, see , MySQL Enterprise Backup.

Making Backups by Copying Table Files

For storage engines that represent each table using its own files, tables can be backed up by copying those files. For example, MyISAM tables are stored as files, so it is easy to do a backup by copying files (*.frm, *.MYD, and *.MYI files). To get a consistent backup, stop the server or lock and flush the relevant tables: 

FLUSH TABLES tbl_list WITH READ LOCK;

You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The flush is needed to ensure that the all active index pages are written to disk before you start the backup. See , "LOCK TABLES and UNLOCK TABLES Syntax", and , "FLUSH Syntax".

You can also create a binary backup simply by copying all table files, as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that table file copying methods do not work if your database contains InnoDB tables. mysqlhotcopy does not work for InnoDB tables because InnoDB does not necessarily store table contents in database directories. Also, even if the server is not actively updating data, InnoDB may still have modified data cached in memory and not flushed to disk.

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MariaDB server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See , "SELECT Syntax". This method works for any kind of data file, but saves only table data, not the table structure.

Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option. See , "Dumping Data in Delimited-Text Format with mysqldump".

To reload a delimited-text data file, use LOAD DATA INFILE or mysqlimport.

Making Backups with mysqldump or mysqlhotcopy

The mysqldump program and the mysqlhotcopy script can make backups. mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines. (See , "Using mysqldump for Backups", and , "mysqlhotcopy - A Database Backup Program".)

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump. See , "Establishing a Backup Policy".

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see , "The Binary Log". The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in , "Point-in-Time (Incremental) Recovery Using the Binary Log". The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See , "mysqldump - A Database Backup Program", and , "mysqlhotcopy - A Database Backup Program".

Making Backups Using Replication Slaves

If you have performance problems with your master server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See , "Using Replication for Backups".

If you are backing up a slave replication server, you should back up its master.info and relay-log.info files when you back up the slave's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the slave's data. If your slave is replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The location of this directory is the value of the --slave-load-tmpdir option. If the server was not started with that option, the directory location is the value of the tmpdir system variable.

Recovering Corrupt 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, see , "MyISAM Table Maintenance and Crash Recovery".

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.
  2. From another shell, execute mount vxfs snapshot.
  3. From the first client, execute UNLOCK TABLES.
  4. Copy files from the snapshot.
  5. Unmount the snapshot.

Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.

Example Backup and Recovery Strategy

Establishing a Backup Policy
Using Backups for Recovery
Backup Strategy Summary

This section discusses a procedure for performing backups that enables you to recover data after several types of crashes:

The example commands do not include options such as --user and --password for the mysqldump and mysql client programs. You should include such options as necessary to enable client programs to connect to the MariaDB server.

Assume that data is stored in the InnoDB storage engine, which has support for transactions and automatic crash recovery. Assume also that the MariaDB server is under load at the time of the crash. If it were not, no recovery would ever be needed.

For cases of operating system crashes or power failures, we can assume that MySQL's disk data is available after a restart. The InnoDB data files might not contain consistent data due to the crash, but InnoDB reads its logs and finds in them the list of pending committed and noncommitted transactions that have not been flushed to the data files. InnoDB automatically rolls back those transactions that were not committed, and flushes to its data files those that were committed. Information about this recovery process is conveyed to the user through the MariaDB error log. The following is an example log excerpt: 

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 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 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

For the cases of file system crashes or hardware problems, we can assume that the MariaDB disk data is not available after a restart. This means that MariaDB fails to start successfully because some blocks of disk data are no longer readable. In this case, it is necessary to reformat the disk, install a new one, or otherwise correct the underlying problem. Then it is necessary to recover our MariaDB data from backups, which means that backups must already have been made. To make sure that is the case, design and implement a backup policy.

Establishing a Backup Policy

To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MariaDB with several tools. For example, MySQL Enterprise Backup can perform a physical backup of an entire instance, with optimizations to minimize overhead and avoid disruption when backing up InnoDB data files; mysqldump provides online logical backup. This discussion uses mysqldump.

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low: 

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

It was assumed earlier that the tables to back up are InnoDB tables, so --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change. (Changes made by other clients to InnoDB tables are not seen by the mysqldump process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the MyISAM tables in the MariaDB database, there must be no administrative changes to MariaDB accounts during the backup.

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MariaDB server should always be started with the --log-bin option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MariaDB server that was started with the --log-bin option and that has been running for some days, we find these MariaDB binary log files: 

-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index

Each time it restarts, the MariaDB server creates a new binary log file using the next number in the sequence. While the server is running, you can also tell it to close the current binary log file and begin a new one manually by issuing a FLUSH LOGS SQL statement or with a mysqladmin flush-logs command. mysqldump also has an option to flush the logs. The .index file in the data directory contains the list of all MariaDB binary logs in the directory.

The MariaDB binary logs are important for recovery because they form the set of incremental backups. If you make sure to flush the logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup. Let's modify the previous mysqldump command a bit so that it flushes the MariaDB binary logs at the moment of the full backup, and so that the dump file contains the name of the new current binary log: 

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
 --all-databases > backup_sunday_1_PM.sql

After executing this command, the data directory contains a new binary log file, gbichot2-bin.000007, because the --flush-logs option causes the server to flush its logs. The --master-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file includes these lines: 

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Because the mysqldump command made a full backup, those lines mean two things:

On Monday at 1 p.m., we can create an incremental backup by flushing the logs to begin a new binary log file. For example, executing a mysqladmin flush-logs command creates gbichot2-bin.000008. All changes between the Sunday 1 p.m. full backup and Monday 1 p.m. will be in the gbichot2-bin.000007 file. This incremental backup is important, so it is a good idea to copy it to a safe place. (For example, back it up on tape or DVD, or copy it to another machine.) On Tuesday at 1 p.m., execute another mysqladmin flush-logs command. All changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the gbichot2-bin.000008 file (which also should be copied somewhere safe).

The MariaDB binary logs take up disk space. To free up space, purge them from time to time. One way to do this is by deleting the binary logs that are no longer needed, such as when we make a full backup: 

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
 --all-databases --delete-master-logs > backup_sunday_1_PM.sql
Note

Deleting the MariaDB binary logs with mysqldump --delete-master-logs can be dangerous if your server is a replication master server, because slave servers might not yet fully have processed the contents of the binary log. The description for the PURGE BINARY LOGS statement explains what should be verified before deleting the MariaDB binary logs. See , "PURGE BINARY LOGS Syntax".

Using Backups for Recovery

Now, suppose that we have a catastrophic crash on Wednesday at 8 a.m. that requires recovery from backups. To recover, first we restore the last full backup we have (the one from Sunday 1 p.m.). The full backup file is just a set of SQL statements, so restoring it is very easy: 

shell> mysql < backup_sunday_1_PM.sql

At this point, the data is restored to its state as of Sunday 1 p.m.. To restore the changes made since then, we must use the incremental backups; that is, the gbichot2-bin.000007 and gbichot2-bin.000008 binary log files. Fetch the files if necessary from where they were backed up, and then process their contents like this: 

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

We now have recovered the data to its state as of Tuesday 1 p.m., but still are missing the changes from that date to the date of the crash. To not lose them, we would have needed to have the MariaDB server store its MariaDB binary logs into a safe location (RAID disks, SAN, ...) different from the place where it stores its data files, so that these logs were not on the destroyed disk. (That is, we can start the server with a --log-bin option that specifies a location on a different physical device from the one on which the data directory resides. That way, the logs are safe even if the device containing the directory is lost.) If we had done this, we would have the gbichot2-bin.000009 file (and any subsequent files) at hand, and we could apply them using mysqlbinlog and mysql to restore the most recent data changes with no loss up to the moment of the crash: 

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

For more information about using mysqlbinlog to process binary log files, see , "Point-in-Time (Incremental) Recovery Using the Binary Log".

Backup Strategy Summary

In case of an operating system crash or power failure, InnoDB itself does all the job of recovering data. But to make sure that you can sleep well, observe the following guidelines:

Using mysqldump for Backups

Dumping Data in SQL Format with mysqldump
Reloading SQL-Format Backups
Dumping Data in Delimited-Text Format with mysqldump
Reloading Delimited-Text Format Backups
mysqldump Tips

This section describes how to use mysqldump to produce dump files, and how to reload dump files. A dump file can be used in several ways:

mysqldump produces two types of output, depending on whether the --tab option is given:

Dumping Data in SQL Format with mysqldump

This section describes how to use mysqldump to create SQL-format dump files. For information about reloading such dump files, see , "Reloading SQL-Format Backups".

By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file: 

shell> mysqldump [arguments] > file_name

To dump all databases, invoke mysqldump with the --all-databases option: 

shell> mysqldump --all-databases > dump.sql

To dump only specific databases, name them on the command line and use the --databases option: 

shell> mysqldump --databases db1 db2 db3 > dump.sql

The --databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as a database name and those following as table names.

With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database. This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came. If you want to cause the dump file to force a drop of each database before recreating it, use the --add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

To dump a single database, name it on the command line: 

shell> mysqldump --databases test > dump.sql

In the single-database case, it is permissible to omit the --databases option: 

shell> mysqldump test > dump.sql

The difference between the two preceding commands is that without --databases, the dump output contains no CREATE DATABASE or USE statements. This has several implications:

To dump only specific tables from a database, name them on the command line following the database name: 

shell> mysqldump test t1 t3 t7 > dump.sql

Reloading SQL-Format Backups

To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client. If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data: 

shell> mysql < dump.sql

Alternatively, from within mysql, use a source command: 

mysql> source dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary): 

shell> mysqladmin create db1

Then specify the database name when you load the dump file: 

shell> mysql db1 < dump.sql

Alternatively, from within mysql, create the database, select it as the default database, and load the dump file: 

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

Dumping Data in Delimited-Text Format with mysqldump

This section describes how to use mysqldump to create delimited-text dump files. For information about reloading such dump files, see , "Reloading Delimited-Text Format Backups".

If you invoke mysqldump with the --tab=dir_name option, it uses dir_name as the output directory and dumps tables individually in that directory using two files for each table. The table name is the basename for these files. For a table named t1, the files are named t1.sql and t1.txt. The .sql file contains a CREATE TABLE statement for the table. The .txt file contains the table data, one line per table row.

The following command dumps the contents of the db1 database to files in the /tmp database: 

shell> mysqldump --tab=/tmp db1

The .txt files containing table data are written by the server, so they are owned by the system account used for running the server. The server uses SELECT ... INTO OUTFILE to write the files, so you must have the FILE privilege to perform this operation, and an error occurs if a given .txt file already exists.

The server sends the CREATE definitions for dumped tables to mysqldump, which writes them to .sql files. These files therefore are owned by the user who executes mysqldump.

It is best that --tab be used only for dumping a local server. If you use it with a remote server, the --tab directory must exist on both the local and remote hosts, and the .txt files will be written by the server in the remote directory (on the server host), whereas the .sql files will be written by mysqldump in the local directory (on the client host).

For mysqldump --tab, the server by default writes table data to .txt files one line per row with tabs between column values, no quotation marks around column values, and newline as the line terminator. (These are the same defaults as for SELECT ... INTO OUTFILE.)

To enable data files to be written using a different format, mysqldump supports these options:

Depending on the value you specify for any of these options, it might be necessary on the command line to quote or escape the value appropriately for your command interpreter. Alternatively, specify the value using hex notation. Suppose that you want mysqldump to quote column values within double quotation marks. To do so, specify double quote as the value for the --fields-enclosed-by option. But this character is often special to command interpreters and must be treated specially. For example, on Unix, you can quote the double quote like this: 

--fields-enclosed-by='''

On any platform, you can specify the value in hex: 

--fields-enclosed-by=0x22

It is common to use several of the data-formatting options together. For example, to dump tables in comma-separated values format with lines terminated by carriage-return/newline pairs (\r\n), use this command (enter it on a single line): 

shell> mysqldump --tab=/tmp --fields-terminated-by=,
 --fields-enclosed-by=''' --lines-terminated-by=0x0d0a db1

Should you use any of the data-formatting options to dump table data, you will need to specify the same format when you reload data files later, to ensure proper interpretation of the file contents.

Reloading Delimited-Text Format Backups

For backups produced with mysqldump --tab, each table is represented in the output directory by an .sql file containing the CREATE TABLE statement for the table, and a .txt file containing the table data. To reload a table, first change location into the output directory. Then process the .sql file with mysql to create an empty table and process the .txt file to load the data into the table: 

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt

An alternative to using mysqlimport to load the data file is to use the LOAD DATA INFILE statement from within the mysql client: 

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

If you used any data-formatting options with mysqldump when you initially dumped the table, you must use the same options with mysqlimport or LOAD DATA INFILE to ensure proper interpretation of the data file contents: 

shell> mysqlimport --fields-terminated-by=,
 --fields-enclosed-by=''' --lines-terminated-by=0x0d0a db1 t1.txt

Or: 

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
 -> FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '''
 -> LINES TERMINATED BY '\r\n';

mysqldump Tips

Making a Copy of a Database
Copy a Database from one Server to Another
Dumping Stored Programs
Dumping Table Definitions and Content Separately
Using mysqldump to Test for Upgrade Incompatibilities

This section surveys techniques that enable you to use mysqldump to solve specific problems:

Making a Copy of a Database

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

Do not use --databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line.

Copy a Database from one Server to Another

On Server 1: 

shell> mysqldump --databases db1 > dump.sql

Copy the dump file from Server 1 to Server 2.

On Server 2: 

shell> mysql < dump.sql

Use of --databases with the mysqldump command line causes the dump file to include CREATE DATABASE and USE statements that create the database if it does exist and make it the default database for the reloaded data.

Alternatively, you can omit --databases from the mysqldump command. Then you will need to create the database on Server 2 (if necessary) and specify it as the default database when you reload the dump file.

On Server 1: 

shell> mysqldump db1 > dump.sql

On Server 2: 

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

You can specify a different database name in this case, so omitting --databases from the mysqldump command enables you to dump data from one database and load it into another.

Dumping Stored Programs

Several options control how mysqldump handles stored programs (stored procedures and functions, triggers, and events):

The --triggers option is enabled by default so that when tables are dumped, they are accompanied by any triggers they have. The other options are disabled by default and must be specified explicitly to dump the corresponding objects. To disable any of these options explicitly, use its skip form: --skip-events, --skip-routines, or --skip-triggers.

Dumping Table Definitions and Content Separately

The --no-data option tells mysqldump not to dump table data, resulting in the dump file containing only statements to create the tables. Conversely, the --no-create-info option tells mysqldump to suppress CREATE statements from the output, so that the dump file contains only table data.

For example, to dump table definitions and data separately for the test database, use these commands: 

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

For a definition-only dump, add the --routines and --events options to also include stored routine and event definitions: 

shell> mysqldump --no-data --routines --events test > dump-defs.sql

Using mysqldump to Test for Upgrade Incompatibilities

When contemplating a MariaDB upgrade, it is prudent to install the newer version separately from your current production version. Then you can dump the database and database object definitions from the production server and load them into the new server to verify that they are handled properly. (This is also useful for testing downgrades.)

On the production server: 

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

On the upgraded server: 

shell> mysql < dump-defs.sql

Because the dump file does not contain table data, it can be processed quickly. This enables you to spot potential incompatibilities without waiting for lengthy data-loading operations. Look for warnings or errors while the dump file is being processed.

After you have verified that the definitions are handled properly, dump the data and try to load it into the upgraded server.

On the production server: 

shell> mysqldump --all-databases --no-create-info > dump-data.sql

On the upgraded server: 

shell> mysql < dump-data.sql

Now check the table contents and run some test queries.

Point-in-Time (Incremental) Recovery Using the Binary Log

Point-in-Time Recovery Using Event Times
Point-in-Time Recovery Using Event Positions

Point-in-time recovery refers to recovery of data changes made since a given point in time. Typically, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made. (The full backup can be made in several ways, such as those listed in , "Database Backup Methods".) Point-in-time recovery then brings the server up to date incrementally from the time of the full backup to a more recent time.

Point-in-time recovery is based on these principles:

If you have more than one binary log to execute on the MariaDB server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe: 

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using different connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports "unknown table."

To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do so: 

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file: 

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e 'source /tmp/statements.sql'

Point-in-Time Recovery Using Event Times

To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime options for mysqlbinlog, in DATETIME format. As an example, suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL statement was executed that deleted a large table. To restore the table and data, you could restore the previous night's backup, and then execute the following command: 

shell> mysqlbinlog --stop-datetime='2005-04-20 9:59:59' \
 /var/log/mysql/bin.123456 | mysql -u root -p

This command recovers all of the data up until the date and time given by the --stop-datetime option. If you did not detect the erroneous SQL statement that was entered until hours later, you will probably also want to recover the activity that occurred afterward. Based on this, you could run mysqlbinlog again with a start date and time, like so: 

shell> mysqlbinlog --start-datetime='2005-04-20 10:01:00' \
 /var/log/mysql/bin.123456 | mysql -u root -p

In this command, the SQL statements logged from 10:01 a.m. on will be re-executed. The combination of restoring of the previous night's dump file and the two mysqlbinlog commands restores everything up until one second before 10:00 a.m. and everything from 10:01 a.m. on.

To use this method of point-in-time recovery, you should examine the log to be sure of the exact times to specify for the commands. To display the log file contents without executing them, use this command: 

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

Then open the /tmp/mysql_restore.sql file with a text editor to examine it.

Excluding specific changes by specifying times for mysqlbinlog does not work well if multiple statements executed at the same time as the one to be excluded.

Point-in-Time Recovery Using Event Positions

Instead of specifying dates and times, the --start-position and --stop-position options for mysqlbinlog can be used for specifying log positions. They work the same as the start and stop date options, except that you specify log position numbers rather than dates. Using positions may enable you to be more precise about which part of the log to recover, especially if many transactions occurred around the same time as a damaging SQL statement. To determine the position numbers, run mysqlbinlog for a range of times near the time when the unwanted transaction was executed, but redirect the results to a text file for examination. This can be done like so: 

shell> mysqlbinlog --start-datetime='2005-04-20 9:55:00' \
 --stop-datetime='2005-04-20 10:05:00' \
 /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

This command creates a small text file in the /tmp directory that contains the SQL statements around the time that the deleterious SQL statement was executed. Open this file with a text editor and look for the statement that you do not want to repeat. Determine the positions in the binary log for stopping and resuming the recovery and make note of them. Positions are labeled as log_pos followed by a number. After restoring the previous backup file, use the position numbers to process the binary log file. For example, you would use commands something like these: 

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
 | mysql -u root -p
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
 | mysql -u root -p

The first command recovers all the transactions up until the stop position given. The second command recovers all transactions from the starting position given until the end of the binary log. Because the output of mysqlbinlog includes SET TIMESTAMP statements before each SQL statement recorded, the recovered data and related MariaDB logs will reflect the original times at which the transactions were executed.

MyISAM Table Maintenance and Crash Recovery

Using myisamchk for Crash Recovery
How to Check MyISAM Tables for Errors
How to Repair MyISAM Tables
MyISAM Table Optimization
Setting Up a MyISAM Table Maintenance Schedule

This section discusses how to use myisamchk to check or repair MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes). For general myisamchk background, see , "myisamchk - MyISAM Table-Maintenance Utility". Other table-repair information can be found at , "Rebuilding or Repairing Tables or Indexes".

You can use myisamchk to check, repair, or optimize database tables. The following sections describe how to perform these operations and how to set up a table maintenance schedule. For information about using myisamchk to get information about your tables, see , "Obtaining Table Information with myisamchk".

Even though table repair with myisamchk is quite secure, it is always a good idea to make a backup before doing a repair or any maintenance operation that could make a lot of changes to a table.

myisamchk operations that affect indexes can cause MyISAM FULLTEXT indexes to be rebuilt with full-text parameters that are incompatible with the values used by the MariaDB server. To avoid this problem, follow the guidelines in , "myisamchk General Options".

MyISAM table maintenance can also be done using the SQL statements that perform operations similar to what myisamchk can do:

For additional information about these statements, see , "Table Maintenance Statements".

These statements can be used directly or by means of the mysqlcheck client program. One advantage of these statements over myisamchk is that the server does all the work. With myisamchk, you must make sure that the server does not use the tables at the same time so that there is no unwanted interaction between myisamchk and the server.

Using myisamchk for Crash Recovery

This section describes how to check for and deal with data corruption in MariaDB databases. If your tables become corrupted frequently, you should try to find the reason why. See "What to Do If MariaDB Keeps Crashing".

For an explanation of how MyISAM tables can become corrupted, see , "MyISAM Table Problems".

If you run mysqld with external locking disabled (which is the default), you cannot reliably use myisamchk to check a table when mysqld is using the same table. If you can be certain that no one will access the tables through mysqld while you run myisamchk, you only have to execute mysqladmin flush-tables before you start checking the tables. If you cannot guarantee this, you must stop mysqld while you check the tables. If you run myisamchk to check tables that mysqld is updating at the same time, you may get a warning that a table is corrupt even when it is not.

If the server is run with external locking enabled, you can use myisamchk to check tables at any time. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.

If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if external locking is disabled). If you do not stop mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously.

When performing crash recovery, it is important to understand that each MyISAM table tbl_name in a database corresponds to the three files in the database directory shown in the following table.

File Purpose
tbl_name.frm Definition (format) file
tbl_name.MYD Data file
tbl_name.MYI Index file

Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.

myisamchk works by creating a copy of the .MYD data file row by row. It ends the repair stage by removing the old .MYD file and renaming the new file to the original file name. If you use --quick, myisamchk does not create a temporary .MYD file, but instead assumes that the .MYD file is correct and generates only a new index file without touching the .MYD file. This is safe, because myisamchk automatically detects whether the .MYD file is corrupt and aborts the repair if it is. You can also specify the --quick option twice to myisamchk. In this case, myisamchk does not abort on some errors (such as duplicate-key errors) but instead tries to resolve them by modifying the .MYD file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case, you should at least make a backup of the table before running myisamchk.

How to Check MyISAM Tables for Errors

To check a MyISAM table, use the following commands:

In most cases, a simple myisamchk command with no arguments other than the table name is sufficient to check a table.

How to Repair MyISAM Tables

The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD).

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See , "CHECK TABLE Syntax", and , "REPAIR TABLE Syntax".

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

To get more information about the error, run perror nnn, where nnn is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table: 

shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed MariaDB error code 127 = Record-file is crashed MariaDB error code 132 = Old database file MariaDB error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file MariaDB error code 136 = No more room in index file MariaDB error code 141 = Duplicate unique key or constraint on write or update MariaDB error code 144 = Table is crashed and last repair failed MariaDB error code 145 = Table was marked as crashed and should be repaired

Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values: 

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

If you do not know the current table option values, use SHOW CREATE TABLE.

For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

This section is for the cases where a table check fails (such as those described in , "How to Check MyISAM Tables for Errors"), or you want to use the extended features that myisamchk provides.

The myisamchk options used for table maintenance with are described in , "myisamchk - MyISAM Table-Maintenance Utility". myisamchk also has variables that you can set to control memory allocation that may improve performance. See , "myisamchk Memory Usage".

If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still available for a while after mysqladmin returns, until all statement-processing has stopped and all index changes have been flushed to disk.

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as "checked."

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try myisamchk -r -q tbl_name (-r -q means "quick recovery mode"). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.
  2. Use myisamchk -r tbl_name (-r means "recovery mode"). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.
  3. If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).
Note

If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

  1. Move the data file to a safe place.
  2. Use the table description file to create new (empty) data and index files: 

    shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit
  3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)
Important

If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.

Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See , "REPAIR TABLE Syntax".

Stage 4: Very difficult repair

You should reach this stage only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r.
  2. If you do not have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, and then move the .frm description and .MYI index files from the other database to your crashed database. This gives you new description and index files, but leaves the .MYD data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

MyISAM Table Optimization

To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run myisamchk in recovery mode: 

shell> myisamchk -r tbl_name

You can optimize a table in the same way by using the OPTIMIZE TABLE SQL statement. OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE. See , "OPTIMIZE TABLE Syntax".

myisamchk has a number of other options that you can use to improve the performance of a table:

For a full description of all available options, see , "myisamchk - MyISAM Table-Maintenance Utility".

Setting Up a MyISAM Table Maintenance Schedule

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. One way to check and repair MyISAM tables is with the CHECK TABLE and REPAIR TABLE statements. See , "Table Maintenance Statements".

Another way to check tables is to use myisamchk. For maintenance purposes, you can use myisamchk -s. The -s option (short for --silent) causes myisamchk to run in silent mode, printing messages only when errors occur.

It is also a good idea to enable automatic MyISAM table checking. For example, whenever the machine has done a restart in the middle of an update, you usually need to check each table that could have been affected before it is used further. (These are "expected crashed tables.") To cause the server to check MyISAM tables automatically, start it with the --myisam-recover-options option. See , "Server Command Options".

You should also check your tables regularly during normal system operation. For example, you can run a cron job to check important tables once a week, using a line like this in a crontab file: 

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

This prints out information about crashed tables so that you can examine and repair them as necessary.

To start with, execute myisamchk -s each night on all tables that have been updated during the last 24 hours. As you see that problems occur infrequently, you can back off the checking frequency to once a week or so.

Normally, MariaDB tables need little maintenance. If you are performing many updates to MyISAM tables with dynamic-sized rows (tables with VARCHAR, BLOB, or TEXT columns) or have tables with many deleted rows you may want to defragment/reclaim space from the tables from time to time. You can do this by using OPTIMIZE TABLE on the tables in question. Alternatively, if you can stop the mysqld server for a while, change location into the data directory and use this command while the server is stopped: 

shell> myisamchk -r -s --sort-index --sort_buffer_size=16M */*.MYI
Copyright 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Next
MySQL Server Administration Home Chapter 7. Optimization