MySQL backup and recovery skills

1. Why back up the database?

Backup is the most important and easy to ignore for databases. Unexpected events may cause serious losses.

The more important the data is, the more frequently the data changes, and the more frequently backup is required.

The backup cycle can be adjusted according to the needs of different services, but backup cannot be ignored.

It is best to back up my. CNF or my. ini during backup so that you can save your previous configuration parameters.

2. MyISAM

Table backup/recovery policy 2.1. Hot file backup

2.1.1. Copy an object

Because the MySQL table is saved as a file, it is easy to back up. To maintain the consistency of the backup, execute the lock tables operation on the relevant table, and then execute the flush tables operation on the table.

You only need to read the lock; in this way, when you copy files in the database directory, other customers are allowed to continue querying the table. The flush tables statement is required to ensure that all activated index pages are written to the hard disk before the backup starts.

Standard Process: Lock table-Refresh table to disk-copy file-unlock.

2.1.2. use SQL

Statement backup

If you want to back up an SQL table, you can use select into... OUTFILE or backup table. For select into... OUTFILE, the output file cannot exist first.

This is also true for backup tables, because overwriting the entire file poses a security risk.

The two backup methods, if the output file has a duplicate name, it is best to remove the duplicate file.

During backup table backup, pay attention to the permissions of the output directory. The modification method is to back up MYD and frm files without backing up indexes.

2.1.3. Use mysqlhotcopy

Backup

Mysqlhotcopy
Is
Perl script, originally written and provided by Tim Bunce. It uses lock tables, flush tables, and CP or SCP to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database directory is located.Mysqlhotcopy
Only used to back up MyISAM. It runs in UNIX and Netware.

shell> mysqlhotcopy db_name [/path/to/new_directory]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Back up tables that match regular expressions in a given database:

Shell> mysqlhotcopy db_name./RegEx/

Add a note ('~ ') Prefix. The regular expression of the table name can be denied:

Shell> mysqlhotcopy db_name ./~ RegEx/

Mysqlhotcopy supports the following options:

· --- Help ,-?

Displays the help message and exits.

· -- Allowold

If the target exists, do not discard it (add a _ old suffix to rename it ).

· -- Checkpoint = db_name.tbl_name

Insert a checkpoint entry in the specified db_name.tbl_name.

· --- Debug

Enable debugging output.

· -- Dryrun,-n

Report actions without executing them.

· -- Flushlog

Refresh the log after locking all tables.

· -- Keepold

After completion, the previous (renamed) targets are not deleted.

· -- Method = command

COPY method (CP or SCP ).

· -- Noindices

The backup does not include all index files. In this way, the backup is smaller and faster. You can use myisamchk-RQ to re-build the index later.

· -- Password = password,-P Password

The password used to connect to the server. Note that the password value of this option is optional, unlike other MySQL programs.

· -- Port = port_num,-P port_num

The TCP/IP Port used to connect to the local server.

· -- Quiet,-Q

In addition to silence when an error occurs.

· -- Regexp = expr

Copy the database that matches the regular expression given by all database names.

· -- Socket = path,-s path

The UNIX socket file used for connection.

· -- Suffix = Str

The suffix of the copied database name.

· -- Tmpdir = path

Temporary directory (instead of/tmp ).

· -- User = user_name,-u user_name

The MySQL user name used to connect to the server.

Mysqlhotcopy reads the [client] and [mysqlhotcopy] consumer groups from the option files.

Because mysqlhotcopy is generally used for full backup, we recommend that you use the-flushlog option to generate incremental update logs.

2.1.4. Use mysqldump

Backup

You can back up table structures and data, and support both MyISAM and InnoDB Engine databases.

Mysqldump can back up a single table, a single database, or all databases.

Mysqldump
You can also export only the table structure.

Mysqldump is a logical backup, an SQL statement file, and a format compatible with other databases.

There are three methods to call mysqldump:

Shell> mysqldump [Options] db_name [Tables]

Shell> mysqldump [Options] --- database db1 [DB2 DB3. ..]

Shell> mysqldump [Options] -- all -- Database

If no table is specified or the --- database or -- all -- database option is used, the whole database is dumped.

To obtain the options supported by mysqldump in your version, run mysqldump --- help.

If mysqldump does not have the -- quick or -- opt option, mysqldump loads the entire result set into the memory before dumping the result. If you dump a large database, problems may occur. This option is enabled by default, but can be disabled with -- skip-OPT.

Mysqldump supports the following options:

· --- Help ,-?

Displays the help message and exits.

· -- Add-drop -- Database

Add the drop database statement before each create database statement.

· -- Add-drop-tables

Add the drop TABLE statement before each create table statement.

· -- Add-locking

Use the lock tables and unlock tables statements to reference each table dump. It is faster to insert a heavy-duty dump file.

· -- All -- database,-

Dump all tables in all databases. Like using the --- database option, name all databases in the command line.

· -- Allow-keywords

You can create a keyword column name. The table name prefix should be added before each column name.

· --- Comments [= {0 | 1}]

If it is set to 0, other information in the dump file is prohibited, such as the program version, server version, and host. -- Skip-Comments and --- comments = 0 are the same. The default value is 1, which includes additional information.

· -- Compact

Produce a small amount of output. This option disables annotation and enables the -- skip-add-drop-tables, -- no-set-names, -- skip-Disable-keys, and -- skip-add-locking options.

· -- Compatible = Name

Generate more compatible output with other database systems or old MySQL servers. The values can be ANSI, mysql323, mysql40, PostgreSQL, Oracle, MSSQL, DB2, MaxDB, no_key_options, no_tables_options, or no_field_options. Use commas to separate the values. These values have the same meaning as the corresponding options for setting the server SQL mode.

This option does not guarantee compatibility with other servers. It only enables SQL mode values that can make dump output more compatible currently. For example, -- compatible = Oracle does not map the Oracle type or the data type that uses the Oracle annotation syntax.

· -- Complete-insert,-C

Use the complete insert statement that includes the column name.

· -- Compress,-C

Compress all information sent between the client and the server (if both support compression ).

· -- Create-Option

The create table statement contains all MySQL table options.

· --- Database,-B

Dump several databases. In general, mysqldump regards the 1st name parameters in the command line as the database name, followed by the table name. This option is used to treat all name parameters as database names. The create database if not exists db_name and use db_name statements are included in the output before each new database.

· --- Debug [= debug_options],-# [debug_options]

Write debugging logs. The debug_options string is usually 'd: T: O, file_name '.

· -- Default-character-set = charset

Use the charsetas default character set. If not specified, mysqldump uses utf8.

· -- Delayed-insert

Use the insert delayed statement to insert rows.

· -- Delete-master-logs

On the master replication server, after the dumping operation is completed, the binary log is deleted. This option automatically enables -- master-data.

· -- Disable-keys,-K

For each table, use /*! 40000 alter table tbl_name disable keys */; And /*! 40000 alter table tbl_name enable keys */; the statement references the insert statement. In this way, the dump file can be loaded faster, because the index is created after all rows are inserted. This option is only applicable to MyISAM tables.

· -- Extended-insert,-e

Use the multiline insert syntax that includes several values lists. In this way, the dump file is smaller, and the insertion can be accelerated when the file is overloaded.

· -- Fields-terminated-by = ..., -- fields-enclosed-by = ..., -- fields-optionally-enclosed-by = ..., -- fields-escaped-by = ..., -- Row-terminated-by =...

These options are used in combination with the-T option, which has the same meaning as the corresponding clause of load data infile.

· -- First-slave,-x

I do not agree to use it. I will rename it -- lock-all-tables.

· -- Flush-logs,-F

Refresh the MySQL server log file before starting the dump. This option requires the reload permission. Note that if you use this option with the -- all -- database (or-a) option, refresh the log based on the database of each dump. The exception is that when -- lock-all-tables or -- master-data is used: In this case, the log is refreshed only once, and the log is refreshed after all the tables are locked. If you want to dump and refresh logs at the same time, use -- flush-logs together with -- lock-all-tables or -- master-data.

· -- Force,-F

During table dump, the process continues even if an SQL error occurs.

· -- Host = host_name,-H host_name

Dump data from the MySQL server of the given host. The default host is localhost.

· -- Hex-blob

Dump binary string columns with hexadecimal symbols (for example, 'abc' is changed to 0x616263 ). The affected columns include binary, varbinary, and blob.

· -- Lock-all-tables,-x

Lock all tables in all databases. Global read locks are used in the overall dump process. This option automatically disables -- Single-transaction and -- lock-tables.

· -- Lock-tables,-l

Lock all tables before starting the dump. Use read local to lock tables to allow Concurrent Insertion of MyISAM tables. For transaction tables such as InnoDB and bdb, -- Single-transaction is a better option because it does not need to lock the table at all.

When dumping multiple databases, -- lock-tables locks the tables for each database. Therefore, this option does not guarantee the logical consistency of the tables in the dump file between databases. The dump status of different database tables can be completely different.

· -- Master-data [= value]

This option writes the location and file name of the binary log to the output. This option requires reload permission and binary logs must be enabled. If the option value is equal to 1, the location and file name are written to the dump output in the form of the change master statement. If you use this SQL statement to dump the master server to set the slave server, start from the correct location of the binary log of the master server. If the option value is 2, the change master statement is written as an SQL comment. If the value is omitted, This is the default action.

The -- master-data option enables -- lock-all-tables, unless -- Single-transaction is also specified (in this case, the global read lock is obtained only for a short time when the dump is started. See also -- Single-transaction. In either case, log-related actions occur during dump. This option automatically disables -- lock-tables.

· -- No-create-DB,-n

This option disables create database /*! 32312 if not exists */db_name statement, if the --- database or -- all -- database option is provided, it is included in the output.

· -- No-create-Info,-T

Do not re-create the create table statement for each dump table.

· -- No-data,-d

Do not write any row information of the table. This is useful if you only want to dump the table structure.

· -- OPT

This option is stenographer; it is equivalent to specifying -- add-drop-tables -- add-locking -- create-option -- disable-keys -- extended-insert -- lock-tables -- quick -- Set-charset. It provides a fast dump operation and generates a dump file that can be quickly loaded into the MySQL server. This option is enabled by default, but can be disabled with -- skip-OPT. To disable only the option enabled with-opt, use the -- Skip format. For example, -- skip-add-drop-tables or -- skip-quick.

· -- Password [= PASSWORD],-P [Password]

The password used to connect to the server. If you use the short option format (-P), there cannot be a space between the option and password. If the password value after the -- password or-P option is ignored in the command line, you are prompted to enter one.

· -- Port = port_num,-P port_num

The TCP/IP Port used for connection.

· -- Protocol = {tcp | socket | pipe | memory}

The connection protocol used.

· -- Quick,-Q

This option is used to dump large tables. It forces mysqldump to retrieve the rows in the table from the server one row at a time, instead of retrieving all rows and caching them to the memory before the output.

· -- Quote-names,-Q

Use the '''' character to reference databases, tables, and column names. If the server SQL mode includes the ansi_quotes option, use the '"' character to reference the name. This option is enabled by default. You can disable it with -- skip-quote-names, but this option should be followed by other options. For example, you can enable -- quote-names -- compatible.

· -- Result-file = file,-r File

Redirects the output to the specified file. This option is applied in Windows because it prohibits the conversion of the new line '/N' character into'/R/N' carriage return and return/New Line sequence.

· -- Routines,-R

Dump stored programs (functions and programs) in the dumped database ). The output produced using --- routines contains the create procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subprogram definers or create or modify timestamps. This indicates that when the subprograms are overloaded, the user should be set as the overload user when they are created, and the timestamp is equal to the overload time.

If you want to create a subroutine that uses the original definer and timestamp attributes, -- routines is not used. Instead, use a MySQL account with the corresponding permissions of the MySQL database to directly dump and reload the contents of the mysql. Proc table.

This option is added in MySQL 5.1.2. Before that, the storage program does not dump.

· -- Set-charset

Add set names default_character_set to the output. This option is enabled by default. To disable the set names statement, use -- skip-set-charset.

· -- Single-transaction

This option issues a begin SQL statement before dumping data from the server. It only applies to transaction tables, such as InnoDB and bdb, because it will then dump consistent database states when a begin is issued without blocking any applications.

When using this option, remember that only InnoDB tables can be dumped in a consistent state. For example, when this option is used, any dumped MyISAM or heap table can still change the status.

The -- Single-transaction option and -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.

To dump a large table, use -- quick to use this option.

· -- Socket = path,-s path

The socket file used when connecting to localhost (the default host.

· -- Skip -- Comments

See the description of the --- comments option.

· -- Tab = path,-T Path

Generate a tab-separated data file. For each dump table, mysqldump creates a tbl_name. SQL file containing the CREATE TABLE statement of the table to be created, and a tbl_name.txt file containing its data. The option value is the directory where the file is written.

The format of the. txt data file is to use a Tab character between the column value and the new line after each line. You can use the -- fields-xxx and -- Row -- XXX options to explicitly specify the format.

Note: This option is only applicable when the mysqldump and mysqld servers run on the same machine. You must have the file permission and the server must have the permission to write files in the directory you specified.

· -- Tables

Overwrite the --- database or-B option. All parameters following the option are treated as table names.

· -- Triggers

Dump triggers for each dump table. This option is enabled by default, and -- skip-triggers is used to disable it.

· -- TZ-UTC

Add set time_zone = '+ 00:00' to the dump file so that the timestamp column can be dumped and reloaded between servers with different time zones. (If this option is not used, the timestamp column is dumped and reloaded between the source and target servers with the local time zone ). -- TZ-UTC can also protect the changes caused by timeout. -- TZ-UTC is enabled by default. To disable it, use -- skip-tz-UTC. This option is added to MySQL 5.1.2.

· -- User = user_name,-u user_name

The MySQL user name used to connect to the server.

· -- Verbose,-V

Lengthy mode. Prints detailed information about program operations.

· -- Version,-V

Display version information and exit.

· -- Where = 'where-condition ',-W' where-condition'

Only records selected by the where condition are dumped. Note that if the condition contains special spaces or characters for the command interpreter, you must reference the condition.

2.2. Cold file backup

When the MySQL server is stopped, the MyISAM database is backed up by copying all table files (*. frm, *. MYD, and *. myi files.

2.3. MyISAM

Table recovery

1. If mysqldump is used for backup, run: mysql-u root <backup file name.

2. If mysqlhotcopy or file cold/hot copy is used for backup, stop the MySQL service and overwrite the existing files with the backup file.

3. If backup table is used, use restore table for restoration. Because the backup table does not back up the index file, you must re-create the index when restoring the table.

4. If you back up data using select into... OUTFILE, you can use the mysqlimport command instead of the load data command to restore data.

3. Log backup

3.1. Benefits of log backup and how to enable it

Log backup supports MyISAM and InnoDB, which are different from some backup tools that only support MyISAM.

In addition, logs can be incrementally backed up, which is not supported by other methods.

Start log backup: Use the -- log-bin [= file_name] Option to start the MySQL service.

3.2. Incremental Backup

First, execute flush logs to refresh the log, synchronize the log to the disk, disable the current log, and generate a new log file.

Copy one or more log files after the last full backup or Incremental backup to a safe place.

In this way, an incremental backup is created.

3.3. Log Recovery

You can choose to recover a database, some operation points, or time range for log recovery, which is flexible.

You can use the mysqlbinlog tool to restore binary logs and multiple log files at a time. The command is as follows:

Shell>Mysqlbinlog [Options] hostname-bin. [0-9] * | MySQL

Mysqlbinlog Command Options

· --- Help ,-?

Displays the help message and exits.

· --- Database = db_name,-D db_name

Only list entries of the database (only local logs are used ).

· -- Force-read,-F

This option is used. If mysqlbinlog reads binary log events that it cannot recognize, it prints a warning, ignores the event, and continues. This option is not available. If mysqlbinlog reads this type of event, it stops.

· -- Hexdump,-H

The log hexadecimal dump is displayed in the comment. This output helps debugging During the replication process. This option is added to MySQL 5.1.2.

· -- Host = host_name,-H host_name

Obtain the binary log of the MySQL server on the specified host.

· -- Local-load = path,-l pat

Pre-processes local temporary files for the load data infile in the specified directory.

· -- Offset = N,-o n

Skip the first n entries.

· -- Password [= PASSWORD],-P [Password]

The password used to connect to the server. If you use the short option format (-P), there must be no space between the option and password. If there is no password value after the -- password or-P option in the command line, a prompt is displayed for entering a password.

· -- Port = port_num,-P port_num

The TCP/IP Port number used to connect to the remote server.

· -- Position = N,-J N

-- Start-position should be used if it is not in favor of use.

· -- Protocol = {tcp | socket | pipe |-Position

The connection protocol used.

· -- Read-from-remote-server,-R

Read Binary logs from the MySQL server. If this option is not provided, any connection Parameter options will be ignored. These options are -- Host, -- password, -- Port, -- protocol, -- socket, and -- user.

· -- Result-file = Name,-r name

Point the output to the specified file.

· -- Short-form,-S

Only the statements contained in the log are displayed, and other information is not displayed.

· -- Socket = path,-s path

The socket file used for connection.

· -- Start-datetime = datetime

Read data from 1st events whose date time is equal to or later than the datetime parameter in binary logs. The datetime value is relative to the local time zone on the machine that runs mysqlbinlog. The value format must be datetime or timestamp data type. For example:

Shell> mysqlbinlog -- start-datetime = "2004-12-25 11:25:56" binlog.000003

This option can help point-to-point recovery.

· -- Stop-datetime = datetime

Stop reading from 1st events whose date time is equal to or later than the datetime parameter in the binary log. For descriptions of datetime values, see the -- start-datetime option. This option can be used for timely recovery.

· -- Start-position = N

Read from the event where the second position in the binary log is equal to n.

· -- Stop-position = N

Stop reading from the event where 1st locations are equal to or greater than N in the binary log.

· -- To-last-logs,-T

The end of the binary log requested by the MySQL server is not stopped, but is printed until the end of the last binary log. If the output is sent to the same MySQL server, an infinite loop occurs. This option requires -- read-from-remote-server.

· -- Disable-logs-bin,-d

Disable binary logs. If you use the -- to-last-logs option to send the output to the same MySQL server, you can avoid infinite loops. This option is also useful for crash recovery, avoiding copying recorded statements. Note: This option requires the super permission.

· -- User = user_name,-u user_name

The MySQL user name used to connect to the remote server.

· -- Version,-V

Display version information and exit.

You can also use the -- var_name = value option to set the following variables:

· Open_files_limit

Specifies the number of open file descriptors to be retained.

4. InnoDB

Table backup/recovery policy 4.1. Use Commercial Online Hot Backup tools

InnoDB hotbackup
It is an online backup tool that you can use to back up your InnoDB database when the InnoDB database is running.InnoDB
Hot Backup tools
You are not required to shut down the database, and it does not set any locks or interfere with normal database processing.InnoDB
Hot Backup tools
Non-free (commercial) additional software. Its annual certificate fee is 390 Euro per MySQL server.

For more information about how to use this tool, see the relevant documentation. I have never used this tool.

4.2. Use mysqldump

Hot Backup

Mysqldump provides non-physical online logical hot backup for InnoDB.

The method is the same as when backing up MyISAM.

4.3. Use select

Hot Backup single/multiple tables

You can use select into to back up one or more tables in the same usage as the MyISAM table.

4.4. Binary cold backup

If you can shut down your MySQL server, you can generate a binary backup of all the files in the table that InnoDB uses to manage it. Follow these steps:

1. Shut down the MySQL server and make sure it is disabled without errors.

2. Copy all your data files (ibdata files and. IBD files) to a safe place.

3. Copy all your ib_logfile files to a safe place.

4. Copy the my. CNF configuration file or file to a safe place.

5. Copy the. frm file to a safe place for your InnoDB table.

4.5. InnoDB

Table recovery

1. First try to use the log auto-recovery function of InnoDB by restarting the MySQL service.

2. In some cases, it is obvious that the database is damaged because the operating system damages its own File Cache, and the data on the disk may be intact. It is best to restart the computer first. It can eliminate errors that appear to be damaged by database pages.

If not, use the following method for restoration.

3. If mysqldump is used for full backup, recover the full backup first, and then restore the incremental log backup after the full backup.

4. If the select into backup table is used, load data or mysqlimport is used for restoration.

5. If you use binary cold backup for full backup, stop the MySQL service first, overwrite the backup binary file, and then perform incremental log backup after the last full backup.

6. Note: The InnoDB binary file is not as good as MyISAM, and must be transplanted on the CPU machine with the same floating point number.

5. MyISAM/InnoDB

Backup/recovery policies

Because both MyISAM and InnoDB engines may be used in a database, their backup/recovery policies should be considered in a unified manner.

1. The full backup uses mysqldump.

2. Incremental Backup uses bin-log logs.

3. Select into is used for single-Table backup.

4,

6. Regular table maintenance

Although MySQL provides multiple backup/recovery methods, regular maintenance of tables greatly reduces the possibility of table destruction and improves query performance.

1. For the InnoDB engine, you can use innodb_tablespace_monitor to check the integrity of file space management in space files.

2. For the MyISAM engine, you can use SQL statements orMyisamchk
Tools to maintain tables
.

In many cases, you may find that using SQL statements to maintain MyISAM tables is easier than executing myisamchk:

· To check or maintain the MyISAM table, use check table or repair table.

· To optimize the MyISAM table, use optimize table.

· To analyze the MyISAM table, use analyze table.

These statements are more advantageous than myisamchk because the server can do anything. To use myisamchk, you must ensure that the server does not use tables at the same time. Otherwise, an unexpected interference occurs between the myisamchk and the server.

We recommend that you run myisamchk when you stop the MySQL service. If it is online, you should first flush tables (write all updates to the disk ).

7. Backup policy summary

1. You must use the -- log-bin or even -- log-bin = log_name option to run the MySQL server. The log file name is located on a security medium, different from the drive where the data directory is located. If you have such a security medium, it is best to perform Disk Load Balancing (this can improve performance ).

2. Perform regular full backup and use the mysqldump command to perform online non-block backup.

When the load ratio is low, we recommend that you use the -- Single-transaction parameter to ensure transaction data consistency without affecting the normal reading and writing of other users.

3. Flush logs is used for full backup to generate Incremental Backup logs.

4. Use flush logs orMysqladmin flush-logs
Refresh logs for regular Incremental backup.

5. regularly maintain tables to improve performance and reduce the possibility of data loss and errors.

6. You can back up the key tables as needed.