Shulou(Shulou.com)06/01 Report--

There are several ways for us to back up data in MySQl

Hot backup warm backup cold backup

Hot backup means that when the database is backed up, the read and write operations of the database are not affected.

Warm backup means that when the database is backed up, the database read operation can be performed, but the write operation cannot be performed.

Cold backup means that when the database is backed up, the database cannot be read or written, that is, the database will be offline.

Here we talk about an enterprise case of backup and recovery of MYSQL database.

Conditions:

1. Full backup (mysqldump) is available.

two。 In addition to full backups, there are all binlog incremental logs generated after full backups.

First create the environment

# create a database named oldboy

CREATE DATABASE oldboy

# cut to the oldboy library

Use `oldboy`

# key table

CREATE TABLE `oldboy` (

`id`int (4) NOT NULL AUTO_INCREMENT

`name` char (20) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

INSERT INTO `oldboy` VALUES (1), (2)), (3) (3), (4)), (5)

# check the tables in the oldboy library

Mysql > select * from oldboy

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldgirl |

| | 3 | inca |

| | 4 | zuma |

| | 5 | kaka |

+-+ +

5 rows in set (0.01 sec)

Mysql > quit

# change the time to 12:00 in the evening and prepare a directory for backup files

Mkdir / data/backup-p

Date-s "2017-06-22"

# backup Library

Mysqldump-uroot-poldboy123-B-- master-data=2-- single-transaction oldboy | gzip > / data/backup/oldboy_$ (date +% F) .sql.gz

# at this time, our binlog log has been split.

# add two tables

Mysql-uroot-poldboy123-e "use oldboy;insert into oldboy values"

Mysql-uroot-poldboy123-e "use oldboy;insert into oldboy values"

# check it out

Mysql-uroot-poldboy123-e "select * from oldboy.oldboy;"

+-+ +

| | id | name |

+-+ +

| | 1 | oldboy |

| | 2 | oldgirl |

| | 3 | inca |

| | 4 | zuma |

| | 5 | kaka |

| | 6 | bingbing |

| | 7 | xiaoting |

+-+ +

# simulate erroneous deletion of database

Date-s "11:40 on 2017-06-22"

Mysql-uroot-poldboy123-e "drop database oldboy;show databases;"

# 10 minutes after the problem, the problem was found and the database was deleted.

# start recovery preparation

# use iptables firewall to block all application writes.

[root@oldboy] # iptables-I INPUT-p tcp-- dport3306!-s 172.16.1.51-j DROP # oldboy_2017-06-22.sql

# check under which binlog the commands executed after backup are placed

Sed-n '22p' oldboy_2017-06-22.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000003',MASTER_LOG_POS=2623

# talk about binlog conversion format

Mysqlbinlog-d oldboy--start-position=2623 oldboy-bin.000003-r bin.sql

# here should correspond to the above view

# start recovery

# start to restore everything. The recovery order is important.

[root@db02 backup] # mysql-uroot-poldboy123

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.