Mysqldump backup combined with Binlog log recovery

http://hongge.blog.51cto.com/

MySQL backups generally take the form of full-database backups plus log backups, such as performing a full backup every day and a binary log backup every hour. This allows you to recover data to any location or time prior to the last binary log backup using full backup and log backups after a MySQL failure.

1,Binlog Introduction

MySQL's binary logs record all the additions and deletions to the database (provided the Binlog is turned on on its own server) and the execution time of these operations. In order to display these binary contents, we can use the Mysqlbinlog command to view.

Use of Binlog

1: Master-Slave synchronization

2: Recovering the database

Open binary Log function

You can open the binary log by editing the Log-bin option in the My.cnf form as follows:

Log-bin [=dir/[filename]]

Where the dir parameter specifies the storage path of the binary file, the filename parameter specifies the file name of the binary file, in the form of Filename.number,number 000001, 000002, and so on. Each time you restart the MySQL service or run mysql> flush logs, a new binary log file is generated, and the number of these log files is incremented continuously. In addition to generating the above file, a file named Filename.index is generated. The list of all binary log files stored in this file is also known as the index of the binary file

Configure the server to restart MySQL after saving, use mysql> show variables like ' log_bin '; see if Bin-log is turned on,

650) this.width=650; "title=" image "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/88/F3/wKiom1gB_ B6cjm7caaawwzkvxsu279.png "" 380 "height="/>

View the resulting binary Log Note: View binlog content to recover data
Bin-log because it is a binary file, the file content can not be viewed directly open view, MySQL provides two ways to view the way, before the introduction, we first to make additions and deletions to the database operation, or log inside the data is a bit empty.

#mysql-uroot-p-E "Reset Master"

#mysql-uroot-p-E "CREATE DATABASE Test"

#mysql-uroot-p-E "Use Test;create table tb1 (ID int primary KEY auto_increment,name varchar (20))"

#mysql-uroot-p-E "insert into TEST.TB1 (name) VALUES (' Lisi ')"

#mysql-uroot-p-E "insert into TEST.TB1 (name) VALUES (' Zhangsan ')"

Start a new log file again

#mysql-uroot-p-E "flush Logs"

#mysql-uroot-p-E "delete from test.tb1 where id=2"

#mysql-uroot-p-E "insert into TEST.TB1 (name) VALUES (' Tom ')"

# mysql-uroot-p-E "select * from Test.tb1"

Enter Password:

+----+------+

| ID | name |

+----+------+

| 1 | Lisi |

| 3 | Tom |

+----+------+

Viewing binary logs on MySQL server

Mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 1087 |

| mysql-bin.000002 | 673 |

+------------------+-----------+

To view the binary log information command:

Syntax format:SHOW BINLOG EVENTS [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]

Viewing events in the binary log

Mysql> Show Binlog events;

The default displays the events in the first binary log file that can be found, including the log file name, the start location of the event, the type of event, the end location, the information, and so on

+------------------+------+----------------+-----------+-------------+----------------------------------------- -----------------------------------------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+------------------+------+----------------+-----------+-------------+----------------------------------------- -----------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver:5.7.13-log, Binlog ver:4 | This event describes the event as a format

| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000001 | 154 | Anonymous_gtid | 1 | 219 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000001 | 219 | Query | 1 | 313 | Create DATABASE Test | To query for events

| mysql-bin.000001 | 313 | Anonymous_gtid | 1 | 378 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000001 | 378 | Query | 1 | 520 | Use ' test '; CREATE TABLE TB1 (ID int primary KEY auto_increment,name varchar (20)) |

| mysql-bin.000001 | 520 | Anonymous_gtid | 1 | 585 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000001 | 585 | Query | 1 | 653 | BEGIN | For querying events, the transaction begins

| mysql-bin.000001 | 653 | Table_map | 1 | 702 | table_id:110 (TEST.TB1) | Mapping events for tables

| mysql-bin.000001 | 702 | Write_rows | 1 | 747 | table_id:110 Flags:stmt_end_f | The Insert event that was executed for us

| mysql-bin.000001 | 747 | Xid | 1 | 778 | COMMIT/* xid=2052 */| XID time is the act of automatically committing a transaction

| mysql-bin.000001 | 778 | Anonymous_gtid | 1 | 843 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000001 | 843 | Query | 1 | 911 | BEGIN |

| mysql-bin.000001 | 911 | Table_map | 1 | 960 | table_id:110 (TEST.TB1) |

| mysql-bin.000001 | 960 | Write_rows | 1 | 1009 | table_id:110 Flags:stmt_end_f |

| mysql-bin.000001 | 1009 | Xid | 1 | 1040 | COMMIT/* xid=2055 */|

| mysql-bin.000001 | 1040 | Rotate | 1 | 1087 | mysql-bin.000002;pos=4 | For log rotation events, it is caused by our execution of the flush logs to open a new log file.

To view the events in the specified binary log

Mysql> show Binlog events in ' mysql-bin.000002 ';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver:5.7.13-log, Binlog ver:4 |

| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000002 | 154 | Anonymous_gtid | 1 | 219 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000002 | 219 | Query | 1 | 287 | BEGIN |

| mysql-bin.000002 | 287 | Table_map | 1 | 336 | table_id:110 (TEST.TB1) |

| mysql-bin.000002 | 336 | Delete_rows | 1 | 385 | table_id:110 Flags:stmt_end_f |

| mysql-bin.000002 | 385 | Xid | 1 | 416 | COMMIT/* xid=2068 */|

| mysql-bin.000002 | 416 | Anonymous_gtid | 1 | 481 | SET @ @SESSION. gtid_next= ' ANONYMOUS ' |

| mysql-bin.000002 | 481 | Query | 1 | 549 | BEGIN |

| mysql-bin.000002 | 549 | Table_map | 1 | 598 | table_id:110 (TEST.TB1) |

| mysql-bin.000002 | 598 | Write_rows | 1 | 642 | table_id:110 Flags:stmt_end_f |

| mysql-bin.000002 | 642 | Xid | 1 | 673 | COMMIT/* xid=2071 */|

The command also contains additional options for flexible viewing

Mysql> show Binlog events in ' mysql-bin.000002 ' from 219 limit 1, 3;

+------------------+-----+-------------+-----------+-------------+---------------------------------+

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

+------------------+-----+-------------+-----------+-------------+---------------------------------+

| mysql-bin.000002 | 287 | Table_map | 1 | 336 | table_id:110 (TEST.TB1) |

| mysql-bin.000002 | 336 | Delete_rows | 1 | 385 | table_id:110 Flags:stmt_end_f |

| mysql-bin.000002 | 385 | Xid | 1 | 416 | COMMIT/* xid=2068 */|

Show BINARY LOGS equivalent to show MASTER LOGS
PURGE binary logs is used to delete binary logs, such as:
PURGE BINARY LOGS to ' mysql-bin.00010 '; Remove any other files that were previously in this file.
PURGE binary LOGS before ' 2016-08-28 22:46:26 ';//delete a binary file before the specified time
Reset MASTER and Reset SLAVE
The former empties all binary logs listed in the index file, resets the index file to null, and creates a new binary log file, typically used when master first starts. The latter causes slave to forget its copy location in the master binary log file, it deletes Master.info, Relay-log.info, and all the trunk log files and starts a new trunk log file so that a clean copy can begin. The slave replication thread must be closed before using the reset slave.

This way you can see the binary log files and the events in the files that exist on the server, but want to see what is in the file and should be able to recover the scene with the help of the Mysqlbinlog tool.

Syntax format: mysqlbinlog [options] log_file ...

The output is slightly different depending on the format of the log file and the options used by the Mysqlbinlog tool.

The available options for Mysqlbinlog refer to the man manual.

The format of the binary log file contains the row pattern, the statement pattern, and the mixed mode (that is, the server determines what type of log is logged), the event information in the statement-based log contains the executed statements, and so on, the event information in the row-based log contains the change information of the row. Both types of event information are logged in a mixed-mode log.

To make it easier to see the events that recorded the row change information, the SQL statement that was executed at that time could use the-V (--verbose) option of the Mysqlbinlog tool, which would re-construct the row event into a pseudo-SQL statement that was commented out. If you want to see more detailed information, you can give this option two times, such as-VV, which can contain comments about data types and meta-information, such as

First switch to the directory where the Binlog is located

#mysqlbinlog mysql-bin.000001

#mysqlbinlog-V mysql-bin.000001

#mysqlbinlog-VV mysql-bin.000001

In addition Mysqlbinlog and can read the binary log file from the remote server through the--read-from-remote-server option, which requires some connection parameters, such as-h,-p,-p,-u, etc. These parameters are valid only after--read-from-remote-server has been specified.
Whether it is a local binary log file or a binary log file on a remote server, whether it is a row mode, a statement mode, or a mixed-mode binary log file, the Mysqlbinlog tool can be applied directly to MySQL server for Point-in-time, location, or database-based recovery.

Let's show you how to delete data before using Binlog recovery (id=2 that record)

Note: In the actual production environment, if you encounter the need to recover the database, do not let the user access to the database to avoid new data insertion, and in the master-slave environment, shut down the master and slave.

View Binlog file and find out from Delete from test.tb1 where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog-v mysql-bin.000002

Show results

# at 219

#160913 20:59:51 Server ID 1 end_log_pos 287 CRC32 0x1a97741b Query thread_id=42 exec_time=0 error_code=0

SET timestamp=1473771591/*!*/;

SET @ @session. pseudo_thread_id=42/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=1075838976/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

BEGIN

/*!*/;

# at 287

#160913 20:59:51 Server ID 1 end_log_pos 336 CRC32 0x930ab248 table_map: ' Test '. ' tb1 ' mapped to number 125

# at 336

#160910 23:17:43 Server ID 1 end_log_pos 385 CRC32 0xdede3eb7 delete_rows:table ID-flags:stmt_end_f

BINLOG '

fytuvxmbaaaamqaaafabaaaaag4aaaaaaaeabhrlc3qaa3rimqacaw8cpaac2t7udq==

fytuvyabaaaamqaaaiebaaaaag4aaaaaaaeaagac//wcaaaachpoyw5nc2futz7e3g==

‘/*!*/;

# # # DELETE from ' Test '. ' Tb1 '

# # WHERE

# # @1=2

# # # @2= ' Zhangsan '

# at 385

#160910 23:17:43 Server ID 1 end_log_pos 416 CRC32 0x7881c9da Xid = 2068

commit/*!*/;

You can see that the Delete event occurs position is 287, the event ends position is 416

Recovery process: Directly use the Bin-log log to restore the database to delete location 287 before, and then skip the point of failure, and then restore all the following operations, the command is as follows

Since no full-library backup has been done before, all Binlog log recoveries are used, so it takes a long time to recover from the production environment and export the relevant Binlog file

#mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 >/opt/mysql-bin.000001.sql

#mysqlbinlog--stop-position=287/usr/local/mysql/data/mysql-bin.000002 >/opt/287.sql

#mysqlbinlog--start-position=416/usr/local/mysql/data/mysql-bin.000002 >/opt/416.sql

Delete Test Database

Mysql>drop database test;

Recovering data with Binlog

#mysql-uroot-p123456</opt/mysql-bin.000001.sql

#mysql-uroot-p123456</opt/287.sql

#mysql-uroot-p123456</opt/416.sql

After the recovery is complete, we check that the data in the following table is complete

Mysql> select * from TEST.TB1;

+----+----------+

| ID | name |

+----+----------+

| 1 | Lisi |

| 2 | Zhangsan |

| 3 | Tom |

+----+----------+

OK, the whole is back.

Mysqlbinlog Option Example

The following are some common options:

--start-datetime

Reads the log events from the binary log for the specified timestamp or after the local machine time.

--stop-datetime

Reads a log event from the binary log, either a specified timestamp or before the local computer time.

--start-position

Reads the specified position event location from the binary log as the start.

--stop-position

Reads the specified position event location from the binary log as an event.

2, Mysqldump Introduction

Mysqldump is a tool used by MySQL for backup and data transfer. It mainly produces a series of SQL statements that can be encapsulated into a file that contains all the SQL commands needed to rebuild your database, such as CREATE database,create Table,insert, and so on. Can be used to implement a lightweight fast migration or recovery database.

Mysqldump is the most common method of backing up data tables into SQL script files that are relatively appropriate when upgrading between different MySQL versions.

Mysqldump generally can be used for backup when the amount of data is small (several grams). When the amount of data is relatively large, it is not recommended to use the Mysqldump tool for backup.

Export of databases

Export Object Description:

Mysqldump can be exported for a single table, multiple tables, a single database, multiple databases, all databases

# mysqldump [Options] db_name [tbl_name ...]//export a specified database or a single table

# mysqldump [options]--databases db_name ...//Export multiple databases

#mysqldump [Options]--all-databases//Export All

Export Database Test

# mysqldump-uroot-p--flush-logs Test >/opt/test.sql//--flush-logs This option will be fully backed up when re-opening a new Binlog

Import of databases

# Mysql-uroot-p Test </opt/test.sql

In the previous we introduced MySQL Binlog and mysqldump tools, let's learn how to implement mysqldump full-Library backup +binlog data recovery

Environment readiness and backup restore:

Check open Binlog

Create some raw data first

mysql> Reset Master;

mysql> CREATE DATABASE test_db;

mysql> use test_db;

Mysql> CREATE TABLE tb1 (ID int primary KEY auto_increment,name varchar (20));

mysql> INSERT into TB1 (name) VALUES (' tom1 ');

mysql> INSERT into TB1 (name) VALUES (' tom2 ');

Mysql> commit;

Mysql> select * from TB1;

+----+------+

| ID | name |

+----+------+

| 1 | tom1 |

| 2 | Tom2 |

+----+------+

Scenario: mysqldump Full Library backup +binlog restore

1,mysqldump Backup solution:

Every Monday 1 o'clock in the morning full-Library backup

2. Backup steps

(1) Create a backup directory

# Mkdir/opt/mysqlbackup

# mkdir/opt/mysqlbackup/daily

(2) Full library backup

Here we simulate the Monday full backup Database task

#mysqldump-uroot-p--flush-logs test_db >/opt/mysqlbackup/test_db_2016_09_12.sql

[Email protected] data]# ls-l/opt/mysqlbackup/

-rw-r--r--. 1 root root 1871 Sep 21:06 Test_db_2016_09_12.sql

Backing up the Binlog log text before the mysqldump full-Library backup (note: There may be more than one Binlog file in the production environment)

# cp/usr/local/mysql/data/mysql-bin.000001/opt/mysqlbackup/daily/

# mysql-uroot-p-E "purge binary logs to ' mysql-bin.000002 '"

The operation error under the simulation, the data modification is wrong.

mysql> use test_db;

Mysql> Delete from tb1 where id=1;

Mysql> commit;

mysql> INSERT into TB1 (name) VALUES (' tom3 ');

Mysql> commit;

Back up the Binlog log file since mysqldump

cp/usr/local/mysql/data/mysql-bin.000002/opt/mysqlbackup/daily/

The above simulation error is the deletion of id=1 records.

(3) We now use Mysqldump's full-Library backup and Binlog to recover the data.

Full-Library recovery with mysqldump backup

# Mysql-uroot-p test_db </opt/mysqlbackup/test_db_2016_09_12.sql

Check the data

[Email protected] ~]# mysql-uroot-p-E "select * from Test_db.tb1"

Enter Password:

+----+------+

| ID | name |

+----+------+

| 1 | tom1 |

| 2 | Tom2 |

+----+------+

From the display results can be seen using mysqldump backup to restore data to the state of the backup, the data just deleted (id=2) back, but the data generated after the backup is lost, so you have to use Binlog further

Because the deletion occurs after a full-library backup, and the--flush-logs option is used when mysqldump a full-library backup, only the Binlog after the full-library backup is analyzed-mysql-bin.000002.

Mysql> show binary logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000002 | 1853 |

+------------------+-----------+

Viewing events in mysql-bin.000002, you can see that there are delete events

Mysql> show Binlog events in ' mysql-bin.000002 ';

| mysql-bin.000002 | 219 | Query | 1 | 294 | BEGIN |

| mysql-bin.000002 | 294 | Table_map | 1 | 346 | table_id:118 (TEST_DB.TB1) |

| mysql-bin.000002 | 346 | Delete_rows | 1 | 391 | table_id:118 Flags:stmt_end_f |

| mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT/* xid=2739 */

Use the Mysqlbinlog command to view detailed events for the backed up binlog file.

Recovery process: We directly use the Bin-log log to restore the database to the deletion location, then skip the point of failure, and then restore all operations after the deletion.

# mysqlbinlog-v/opt/mysqlbackup/daily/mysql-bin.000002

We'll use the Mysqlbinlog command to find the location of the DELETE statement.

# at 219

#160911 17:19:55 Server ID 1 end_log_pos 294 CRC32 0x84590493 Query thread_id=66 exec_time=0 error_code=0

SET timestamp=1473585595/*!*/;

SET @ @session. pseudo_thread_id=66/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=1075838976/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

BEGIN

/*!*/;

# at 294

#160911 17:19:55 Server ID 1 end_log_pos 346 CRC32 0x5cdccf9e table_map: ' test_db '. ' tb1 ' mapped to number 118

# at 346

#160911 17:19:55 Server ID 1 end_log_pos 391 CRC32 0x320c4935 delete_rows:table ID 118 flags:stmt_end_f

BINLOG '

uyhvvxmbaaaanaaaafobaaaaahyaaaaaaaeab3rlc3rfzgiaa3rimqacaw8cpaacns/cxa==

Uyhvvyabaaaalqaaaicbaaaaahyaaaaaaaeaagac//wbaaaabhrvbte1sqwy

‘/*!*/;

# # # DELETE from ' test_db '. ' Tb1 '

# # WHERE

# # @1=1

# # # @2= ' tom1 '

# at 391

#160911 17:19:55 Server ID 1 end_log_pos 422 CRC32 0x5e4a6699 Xid = 2739

commit/*!*/;

The results shown by the Mysqlbinlog command show that the start of the postion delete is 219 and the end position is 422.

Reads the specified position=219 event location from the binary log as up to, that is, to restore the data to delete before deleting

# Mysqlbinlog--stop-position=219/opt/mysqlbackup/daily/mysql-bin.000002 | Mysql-u root-p

Reads the specified position=422 event location from the binary log as a starting point, skipping the delete event and resuming normal operation of the data after the delete event

#mysqlbinlog--start-position=422/opt/mysqlbackup/daily/mysql-bin.000002 | Mysql-u root-p

To view the recovery results:

# mysql-uroot-p-E "select * from Test_db.tb1"

Enter Password:

+----+------+

| ID | name |

+----+------+

| 1 | tom1 |

| 2 | Tom2 |

| 3 | Tom3 |

+----+------+

The data can be recovered to a normal state from the above display.

http://hongge.blog.51cto.com/

Mysqldump backup combined with Binlog log recovery