MySQL all backup and recovery

I. mysqldump backup combined with Binlog log recovery MySQL backups generally take the form of full-Library 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 binary log records the database of all additions and deletions of the operation log (if you want to open binlog on your own server), but also includes 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

Turn on binary log function

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

Log-bin [=mysql-bin]

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,

View generated binary log Note: View Binlog content to recover data

Bin-log because it is a binary file, you cannot open the view directly through the file content view command.

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"//empty all binaries starting from 00001

#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:

Viewing binary logs on MySQL Server

Mysql> show binary logs;

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

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.

(query)# cd/usr/local/mysql/data/

# mysqlbinlog-v (two V can also) mysql-bin.000002

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;

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 the log events from the binary log before the specified timestamp or local computer time.

--start-position starts by reading the specified position event location from the binary log.

--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 Export Object Description for database: 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 tes

# mysqldump -uroot-p --flush-logs Test >/opt/test.sql//--flush-logs This option will be fully backed up

Import of databases

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

For example, delete a library and restore:

Backup

( recovery ) (if an error means creating a database you want to create)

To delete a table:

(Backup table )

(recovery table)

In the previous we introduced the MySQL Binlog and mysqldump tools, let's learn how to implement Mysqldump

Data recovery for full-Library backup +binlog

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;

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 (test_db_ ' Date +%y%m%d_%h%m%s ')

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

Mysql-uroot-p-E "select * from Test_db.tb1"

From the results you can see the status of restoring data to a backup using a mysqldump backup, the data you just deleted (id=2) is back, but the data generated after the backup is lost so you have to take advantage of binlog further restore because the deletion occurred after the full-library backup, and mysqldump The--flush-logs option is used when backing up a full library, so you only need to analyze binlog that is mysql-bin.000002 after the full library backup.

Mysql> show binary logs;

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

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

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

View recovery results: # MYSQL-UROOT-P-E "select * from Test_db.tb1"

MySQL all backup and recovery