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

Http://xiaorenwutest.blog.51cto.com

Disaster recovery and backup of MySQL Database

The database is a top priority for the company; it records the company's huge data, related to the company's property, as well as customer information, if lost will cause incalculable losses for the company.

But if you do a good backup work can avoid this situation; so as a qualified DBA personnel to master database backup and recovery skills is essential. In addition, the company will also make some plans at work, such as database disaster and recovery testing. What I bring to you today is about MySQL backup and recovery.

1: we use the mysqldump tool for backup here:

Mysqldump backup combined with binlog log recovery

What we need to know here is that mysql backup generally adopts the method of full database backup plus log backup, for example, performing a full database backup once a week and a binary backup once a day, so that when the MySQL fails, it can be restored to any location and time before the failure.

1) binlog log:

I believe we all know that binlog log is used to record the log of database changes, such as add, change, delete and other sql statements, in addition, you also need to open this log when master-slave replication.

How to enable binlog log:

/ etc/my.cnf is opened in the main configuration file:

Then save the file and restart the MySQL service

Let's see if the binlog service has been enabled.

The filename parameter specifies the file name of the second-tier file, which is 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. In addition to generating the above file, a file named filename.index is also generated. The list of all binary log files stored in this file is also known as the index of binary files

Let's take a look:

Here's a summary of the binlog log:

1: sql statement that records changes to the database

2: can be copied from master to slave

3: the most important thing is that lost data can be recovered.

Because bin-log is a binary file, it cannot be opened and viewed directly through the file content view command. Mysql provides two ways to view it. Before the introduction, let's add, delete and modify the database, otherwise the data in log will be empty.

# mysql-uroot-p-e "reset master" = > Delete all binaries and generate a new binary file

# mysql-uroot-p-e "create database test" = > create a test database

# mysql-uroot-p-e "use test;create table tb1 (id int primary key auto_increment,name varchar (20))" = > create a new table in the test database tb1;ID for automatic growth and name

# mysql-uroot-p-e "insert into test.tb1 (name) values ('lisi')" = > insert user lisi into the tb1 table

# mysql-uroot-p-e "insert into test.tb1 (name) values ('zhangsan')" = > insert user zhangsan again

Let's see if the above operation is successful.

Next we re-generate a log file for binlog: then delete the user whose previous user ID is 2 (zhangsan), and then create a person whose user is Tom.

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

Now let's see who else exists in the database:

Next let's take a look at the contents of our binary log file and how to recover:

You can see that now we only have two binary log files:

Next, let's look at the information in the binary log file.

Mysql > show binlog events

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

Format_desc | / / this event is described in format

Query / / is the query event

Table_map / / Mapping events for the table

Write_rows / / insert events executed for us

Xid / / Xid time is an action that automatically commits a transaction

Rotate / / rotation events for logs are caused when we execute flush logs to open a new log file.

What you just looked at is that the default binary is 000001; next let's look at the second binary

Mysql > show binlog events in 'mysql-bin.000002'

In addition, you can view the data from 219 to 301 through the show binlog events in 'mysql-bin.000002' from 219 limit 1 sentence. This is not a demonstration. I will introduce some sql statements to you at the end of the article.

Next, let's start the data recovery {restore the users of the previously deleted ID=2}

Whether it is a local binary log file or a binary log file on a remote server, whether it is a binary log file in line mode, statement mode or mixed mode, it can be directly applied to MySQL Server for recovery based on point-in-time, location or database after being parsed by the mysqlbinlog tool.

Restore steps:

First look at the binlog file: find the statement delete from test.tb1 where id=2

# cd / usr/local/mysql/data/

# mysqlbinlog-v mysql-bin.000002

It can be seen that the delete event occurrence position is 287 and the event end position is 416.

Restore process: directly use the bin-log log to restore the database to delete location 287, then skip the point of failure, and then restore all of the following operations, the command is as follows

Since you have not done a full library backup before, you need to use all binlog logs to recover, so it takes a long time to recover in the production environment and export the relevant binlog files.

# 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

Then it's time to witness miracles; then move on.

Delete test database

Mysql > drop database test

Using binlog to recover data

# mysql-uroot-p

< /opt/mysql-bin.000001.sql #mysql -uroot -p< /opt/287.sql # 恢复完成后，我们检查下表的数据是否完整 zhangsan用户已经成功的恢复了，说明这次备份成功了，在这里为大家介绍几个命令让大家参考一下 mysqlbinlog 选项示例 常见的选项有以下几个： --start-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。 --stop-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。 --start-position 从二进制日志中读取指定position 事件位置作为开始。 --stop-position 从二进制日志中读取指定position 事件位置作为事件截至 刚才我们使用的mysqlbinlog记下来为大家接单的介绍下; 语法格式： mysqlbinlog [options] log_file ... 输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。 mysqlbinlog的可用选项可参考man手册。 二进制日志文件的格式包含行模式、语句模式和混合模式（也即有服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。 为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如 先切换到binlog所在的目录下 #mysqlbinlog mysql-bin.000001 #mysqlbinlog -v mysql-bin.000001 #mysqlbinlog -vv mysql-bin.000001 另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了--read-from-remote-server后有效。 另外其他的一些参数可以通过mysqlbinlog --help查看如果看更详细的可以使用man手册 2）mysqldump工具的介绍： 主要是用来备份和数据转移的工具，主要产生一系列的sql语句，可以分装到文件，而分装的这个文件主要用于重建数据库所需的sql命令，可以用来实现轻量级的快速迁移或恢复数据库。mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 mysqldump主要用于数据量很小的时候可以备份，当数据量庞大的时候就显得力不存心了，就不建议使用mysqldump工具进行备份，后续会为大家带来新的备份工具。 mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作 #mysqldump [options] db_name [tbl_name ...] //导出指定数据库或单个表 #mysqldump [options] --databases db_name ... //导出多个数据库 #mysqldump [options] --all-databases //导出所有 mysqldump -uroot -p --flush-logs test >

/ opt/test.sql /-- flush-logs this option will restart a new binlog when a full backup occurs

Import of database

Earlier, we introduced mysql's binlog and mysqldump tools, so let's learn how to implement mysqldump full database backup + binlog data recovery.

Environment preparation and backup restore:

Check to turn on binlog

Create some raw data first

Mysql > reset master;= clears all previous binaries and generates a new binary

Mysql > create database test_db;= create a library of test_db

Mysql > use test_db;= enter the test_ DB library

Mysql > create table tb1 (id int primary key auto_increment,name varchar (20)); = create tb1 table

Mysql > insert into tb1 (name) values ('tom1'); = insert data tom1 in the table

Mysql > insert into tb1 (name) values ('tom2'); = insert data tom2 in the table

Mysql > commit;= complete

View the contents in the following table:

The preparatory work is ready, and now we are going to work on the backup:

Solution: mysqldump full library backup + binlog restore

1. Mysqldump backup scheme:

Full library backup at 1: 00 a. M. every Monday

2. Backup steps

(1) create a backup directory

# mkdir / opt/mysqlbackup

# mkdir / opt/mysqlbackup/daily

(2) full database backup

Here we simulate the full database backup task on Monday

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

[root@localhost data] # ls-l / opt/mysqlbackup/

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

Backup binlog log text before mysqldump full library backup (Note: there may be more than one binlog file in a production environment)

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

# mysql-uroot-p-e "purge binary logs to 'mysql_bin.000002'"

Next, simulate the operation error and modify the data:

Mysql > insert into tb1 (name) values ('tom3')

Mysql > commit

Back up binlog log files since mysqldump

The misoperation of the above simulation is to delete the record of id=1.

(3) now we use mysqldump's full library backup and binlog to recover the data.

Full library recovery using backup of mysqldump

# mysql-uroot-p test_db

< /opt/mysqlbackup/test_db_2017_06_24.sql 查询一下数据 [root@localhost ~]# mysql -uroot -p -e "select * from test_db.tb1" 从显示结果可以看到使用mysqldump备份将数据还原到了备份时的状态，刚才删除的数据（id=2）恢复回来了，但备份后产生的数据却丢失了（tom3）所以还得利用binlog进一步还原 因为删除是在全库备份后发生的，而mysqldump全库备份时使用--flush-logs选项，所以只需要分析全库备份后的binlog即mysql_bin.000002。 先查看下binlog的信息： 查看mysql-bin.000002中的事件，可以看到有删除事件 mysql>

Show binlog events in 'mysql_bin.000002'

Use the mysqlbinlog command to view the detailed events of the backed up binlog file.

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

If you want to see it in more detail, you can go through

# check the mysqlbinlog-v / opt/mysqlbackup/daily/mysql_bin.000002 statement. If you don't know much about it, you can demonstrate it again:

As can be seen from the results displayed by the mysqlbinlog command, the start postion of the misoperation delete is 219 and the end position is 422.

Read the specified position=219 event location from the binary log as the deadline, that is, before the data is restored to delete deletion

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

Read the specified position=422 event location from the binary log as a start, that is, skip the delete event and resume the normal operation of the data after the delete event

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

View the recovery results:

#

It can be seen from the above that the data has returned to normal.

A summary of today's mysql disaster backup and recovery:

1) it introduces the function of binlog log file and how to open it. In addition, it includes our modification sql statement to the database, which is stored in each individual event.

2) mysqlbinlog tool, which is mainly used to open binlog logs, you can view more detailed information through the-vv option; you can also back up binlog log binaries

3) mysqldump tool; can be used to back up binary files, but only suitable for a small amount of data, a large amount of data is not suitable

4) in fact, recovery is to look up the previous command in the binary file, skip over the event of deleting or operating the wrong command, and execute other sql statements that have no problem.

The backup of the Mysql database in the production environment is a periodic repetitive operation, so it is usually necessary to write a script to implement the backup script periodically through the scheduled task of crond. This will be brought to you next time. If there are any deficiencies, I hope you can give us more advice.

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.