MySQL backup and recovery strategy and tutorials

Data backup is part of data disaster protection, and all data backup systems are designed based on these five elements, backup source, backup destination, transport network, backup engine, and backup strategy. The user develops a backup strategy as needed, executes a backup script using a timed task, and uses the backup engine to transfer the data that needs to be backed up from the backup source over the transport network to the backup destination.

Backing up five tuples:

1. Backup source

Data that needs to be backed up is called a backup source, which can be text data, audio and video data, database data, and so on.

2. Backup target

Where the backup data is stored, it is usually recommended to store the backup data in a different machine, or a further data center, where the backup target can be an online disk, a disk array enclosure, or a tape library or a virtual band. The location of the backup target can be in the same data center or disaster room. www.gogoqq.cc

3. Transmission network

The transport link used to back up the data can be a leased line, Ethernet, Internet,vpn, and so on, as long as the route between the backup source and destination is guaranteed to be reached.

4. Backup engine

Data to be able to flow from the source to the target, there must be power, like water to flow, this source of power is the backup engine, like Mysqldump,nvbu, and a large number of backup software is the backup engine. www.mekka.cc

5. Backup strategy

In order to effectively backup and reduce human operations, a sound backup strategy should be developed. Usually the full-standby and the combination of the difference and the backup, the time point should be avoided as far as possible business Takahoko period, usually in the evening, through the implementation of timed tasks.

MySQL Data backup principle

MySQL data backup is actually the form of SQL statements to dump the data, as a file to save, and the exported file is editable, and the Oracle database Rman backup is very different, MySQL more like a logical backup from the library to extract SQL statements, This includes building a library, connecting libraries, building tables, inserting, and so on, as if we were to redo our previous operations through SQL statements again. www.cooke360.cc

Log type for MySQL

1. Binary log (Log-bin)

Binary logs are important, and the binary logs record all changes to the MySQL database, in fact, like Oracle's Redolog log principle, because it records all the operations, so we can use the binary log after a certain point in time to do roll forward operation, to incrementally recover data.

MySQL's binary logs can be viewed and filtered using mysqlbinlog, filtered to the data we want and then imported into the database, but it's also very handy, but it's especially important to strictly follow the order in which the binary logs are generated. Www.lslz.net

Purpose: Records all change operations for incremental backups

Configuration: Add in MY.CNF

[Mysqld]

Log-bin =mysql-bin

Log-bin-index =mysql-bin.index

2. Relay log (Relay-log)

As the name implies, the delivery log, mainly used in the master-slave replication of the schema, only in the library has a trunk log (except for multi-level replication) in the library from the repository copied from the main library from the binary log to the trunk log, used to reconstruct the data from the library.

Configuration: Add in MY.CNF

[Mysqld]

Relay-log =relay-log

relay_log_index= Relay-log.index

3. Slow query log (slow_query_log)

The slow query log is mainly used for MySQL optimization, from the database to find out which SQL statements are relatively slow, put them into a file, you can use the Mysqlsla tool to analyze the slow query statements, the results of the analysis submitted to the development of SQL optimization. Www.iistone.net

Purpose: Find out slow queries for optimization

Configuration: Add in MY.CNF

[Mysqld]

Slow_query_log= 1

Long-query-time= 2

Slow_query_log_file=/data/3306/slow.log

4. General Inquiry Log

All the industries that access MySQL are logged, so a lot of logs are generated, which is generally recommended to close.

Configuration: Add in MY.CNF

[Mysqld]

General_log = 1

Log_output =file

General_log_file=/home/mysql/mysql/log/mysql.log

5. Error log

Log errors generated by MySQL, which is quite useful when troubleshooting, and is generally recommended.

Configuration: Add in MY.CNF

[Mysqld]

Log-warnings =1

Log-error =/home/mysql/mysql/log/mysql.err

6. Transaction log

Caches the data submitted by the transaction and implements the conversion of random IO into sequential io.

Configuration: Add in MY.CNF

[Mysqld]

Innodb_log_buffer_size= 16M

Innodb_log_file_size= 128M

Innodb_log_files_in_group= 3

MySQL backup and recovery strategy and tutorials