MySQL Backup and Recovery essentials

Data backup schematic diagram

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/59/93/wKiom1TYWLvjFG6QAADdLOzjiWQ351.jpg "alt=" Wkiom1tywlvjfg6qaaddlozjiwq351.jpg "/>

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.

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

To be able to flow from source to target, the power is like water to flow, this power source is the backup engine, like Mysqldump, Nvbu, and a lot of backup software is the backup engine.

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.

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.

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.

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

First, the use of Mysqldump|mysql|mysqlbinlog tools

Mysqldump

Command Description:

Mysqldump is a command line-based MySQL data Backup tool provided by MySQL, providing a rich selection of parameters for backup in various requirements forms, such as single-Library backup, multi-Library backup, single-table and multi-table backup, full-database backup, backup table structure, backup table data, etc.

Syntax format:

1. Library Backup

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock Library Name >/opt/backup/mysql_bak_db.sql

2. Multi-Library Backup

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock-b Library 1 Library 2 Library 3 >/opt/backup/mysql_bak_db.sql

Separate multiple libraries with spaces

3. Single-table backup

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock Library Name Table name >/opt/backup/mysql_bak_db.sql

4. Multi-table Backup

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock Library Name table 1 table 2>/opt/backup/mysql_bak_db.sql

5. Full Library Backup

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock-a >/opt/backup/mysql_bak_db.sql

Common parameter parsing:

1,-B

If you want to back up more than one library at a time to add a b parameter, the b parameter adds the CREATE database and use statement to the backup data

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/59/93/wKiom1TYWPGSusuPAAEaOgmGTr0309.jpg "alt=" Wkiom1tywpgsusupaaeaogmgtr0309.jpg "/>

2,-F

The log is refreshed before the backup, and you can see that the binaries roll forward to produce the new binary files.

3 、--Master-data

There are two values of 1 or 2, equal to 1 will add the following statement to the backup data:

Change MASTER to master_log_file= ' mysql-bin.000040 ', master_log_pos=4543;

Equals 2 Adds the following statement to the backup data:

--Change MASTER to master_log_file= ' mysql-bin.000040 ', master_log_pos=4543;

The only difference is that there is no "--" commented out, if the backed up data for slave, equal to 1, at this time from the library will know where to start reading the binary log, if only for the backup to identify the current binary is which one and the location point is equal to 2 appropriate.

4,-X

Lock table, when backing up the lock table to ensure data consistency.

5,-D

Backing up only the table structure does not back up data.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/59/93/wKiom1TYWRmxtwMQAAFj8PwvVpQ937.jpg "alt=" Wkiom1tywrmxtwmqaafj8pwvvpq937.jpg "/>

6,-t

Backs up table data only, without backing up the table structure.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/59/90/wKioL1TYWiCSugQfAAE1KSDWmdo248.jpg "alt=" Wkiol1tywicsugqfaae1ksdwmdo248.jpg "/>

7 、--Compact

For testing, the backup results are printed on the screen, which is used in a few examples, but is not used for real backups.

5 、--Single-transaction

The INNODB engine guarantees data consistency parameters, and the session's security isolation level after using this parameter is set to Repeatble-read, at which time the data submitted by other sessions is not considered, thus ensuring data consistency.

Please refer to help for more parameters

Mysql

Command Description:

MySQL is usually used to log on to the MySQL server, and then perform the operation, in fact, MySQL also provides non-interactive execution of MySQL command parameters, and MySQL command is also a very important tool for data recovery.

Syntax format:

1. Log in to MySQL server

Mysql-uroot-p123456-s/data/3306/data/mysql.sock

Plus s parameter is considered in case of multiple instances, the following similar

2. Non-interactive execution of MySQL statements

Mysql-uroot-p123456-s/data/3306/data/mysql.sock-e "Use Capinfo; Showtables; "

3. Data recovery

Mysql-uroot-p123456-s/data/3306/data/mysql.sock <mysql_bak_capinfo.sql

4. Recovering data Using Source

After logging in to the MySQL server, you can also recover data using the source command.

Mysqlbinlog

Command Description:

Process binary log tools, which can be used to view, recover data from binary logs, and filter the data needed to export to a file.

Common parameters:

1,-D or–database

Filtering out the required database data;

2 、--start-position--stop-position

Filter out the start and end position data;

3 、--start-datetime--stop-datetime

Filter out the start time and end time period data;

Function:

1. View the binary log

Mysqlbinlog Binary Log Name

2. Filter the specified database data and export to a file

MYSQLBINLOG–D Library name binary log name > Capinfo.log

3. incremental recovery using binary log

Example: Incremental recovery for a specified location

mysqlbinlog-d capinfo--start-position=98--stop-position=768 mysql-bin.000038 |mysql-uroot-p123456-s/data/3306/ Data/mysql.sock

Incremental recovery for a specified time period

mysqlbinlog-d capinfo--start-datetime= "2015-02-27 09:30:00"--stop-datetime= "2015-02-27 12:30:00" mysql-bin.000038| Mysql-uroot-p123456-s/data/3306/data/mysql.sock

Note: Usually we will only filter out the required data, put the its first into a file, and then you can filter the file two times, or directly edit the file and then import, this is the more appropriate practice.

Second, backup parameter optimization

1, MyISAM engine backup parameter optimization

Mysqldump-uroot-p123456-f-a-b-x--master-data=2| Gzip >/opt/backup/mysql_bak_db_full.sql.gz

2, InnoDB engine backup parameter optimization

Mysqldump-uroot-p123456-f-a-b--single-transaction--master-data=2| Gzip >/opt/backup/mysql_bak_db_full.sql.gz

The only difference: the MyISAM engine uses the X-parameter lock table backup, while the InnoDB engine uses-single-transaction to modify the isolation level of the session to Repeatble-read to ensure data consistency, and the InnoDB engine is clearly more granular in data control.

Third, sub-database sub-table backup script implementation

1, sub-Library backup implementation script

Scripting ideas:

If you have fewer libraries, you can use Forin db1 DB2 db3;do;d one to instance

If the library is large, you should first remove the library name with Showdatabases, as a list of values for the For loop variable, and then perform the backup operation:

Fetch Library Name:

Mysql-uroot-p123456-s/data/3306/data/mysql.sock-e "showdatabases;" | Egrep-v "Database|information_schema|mysql|performance_schema"

Back up a library:

Mysqldump-uroot-p123456-s/data/3306/data/mysql.sock--compact-b-F--master-data=2--single-transaction–events\ Capinfo|gzip>/opt/backup/mysql_bak_db_capinfo.sql.gz

Understand the script idea the script is very good to write, the following is a sample script written by yourself:

#!/bin/sh cmdpath= "/usr/local/mysql/bin" my_user= "root" my_pass= "123456" bakdir= "/opt/backup" socketfile= "/data/ 3306/data/mysql.sock "dblist= ' mysql-u$my_user-p$my_pass-s $socketfile-E" show Databases "|egrep-v" Database|inf Ormation_schema|mysql|performance_schema "' for DB in $dbListdo $cmdPath/mysqldump-u$my_user-p$my_pass-s $socketf Ile-b-F--master-data=2--single-transaction--events $db | gzip > $bakDir/mysql_bak_db_${db}_$ (date+%y%m%d). Sql.gzdone

2, sub-table backup implementation script

Scripting ideas:

The general idea is to back up the tables in the library, which will use the For loop nesting, the outer for loop is the library, the inner for loop is the table, and the tables in each library are stored under the directory named under their respective library names.

Fetch the table name from the library:

Mysql-uroot-p123456-s/data/3306/data/mysql.sock-e "Showtables from Capinfo" |sed ' 1d '




示例脚本：

#!/bin/sh cmdpath= "/usr/local/mysql/bin" my_user= "root" my_pass= "123456" bakdir= "/opt/backup" socketfile= "/ Data/3306/data/mysql.sock "  dblist= ' mysql-u$my_user -p$my_pass -s  $socketfile  -e   "show databases"         |egrep -v "Database|information _schema|mysql|performance_schema "'   fordb in  $dbListdo          table= ' mysql -u$my_user -p$my_pass -s$socketfile -e  ' Showtables from   $db "|sed  ' 1d '         for tb in  $table          do                 mkdir -p  $bakDir/$db                   $cmdPath/mysqldump -u$my_user-p$my_pass -s $ socketfile -f                 -- master-data=2--single-transaction --events  $db   $TB  | gzip > $bakDir/${db}/ mysql_bak_tb_${tb}_$ (date+%y%m%d). Sql.gz        donedone







This article from "Flat Light is true" blog, please be sure to keep this source http://codings.blog.51cto.com/10837891/1735201

MySQL Backup and Recovery essentials