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

MYSQL backup recovery

MySQL backup generally adopts the way of full database backup and log backup.

1 、 binlog

Mysql's binary log records all additions, deletions and modifications to the database, which can be viewed using the mysqlbinlog command.

Restart is enabled on / etc/my.conf when it is off by default

Specify the path

The use of Binlog Master-Slave synchronous recovery Database

View the resulting binary log Note: view the binlog content to restore data

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. Let's add, delete and modify the database first, otherwise the data in log will be empty.

Restart to start a new log

View binary logs on MySQL Server

Command to view binary log information:

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

View specified binary log events

SLAVE replication thread.

To see the specific contents of the file and restore the scene also need to use the tool mysqlbinlog.

Syntax format: mysqlbinlog [options] log_file.

The available options for mysqlbinlog can be found in the man manual.

The format of the binary log file includes line mode, statement mode and mixed mode, the event information in the statement-based log contains the executed statements, and the event information in the row-based log contains line change information.

2 it is convenient to query SQL statements using the mysqlbinlog tool-v (--verbose) option. For more detailed information, you can give this option to-vv twice.

First change to the directory where binlog is located.

View the mysqlbinlog 000001 directory

Mysqlbinlog and can read binary log files from the remote server through the-- read-from-remote-server option, which requires some external connection parameters, such as-HQ read-from-remote-server, etc. These parameters are valid only if-log is specified.

Binary log files, whether in row mode, statement mode or mixed mode, can be parsed by the mysqlbinlog tool and directly applied to MySQL Server for point-in-time, location-based, or database-based recovery.

You can see that the delete event occurrence position is 287 and the event end position is 416.

Recovery process: directly use the bin-log log to restore the database to delete location 287, then skip the point of failure, and then restore

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.

Delete test database

Using binlog to recover data

Recovery succeeded

-- start-datetime

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

-- stop-datetime

Reads log events before the specified timestamp or local computer time from the binary log.

-- start-position

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

-- stop-position

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

Mysqldump introduction

Mysqldump can generally be used for backups when the amount of data is small (a few gigabytes). When the amount of data is relatively large, it is not recommended to use the mysqldump tool for backup.

2) mysqldump can export a single table, multiple tables, a single database, multiple databases, and all databases

# mysqldump [options] db_name [tbl_name...] / / Export a specified database or single table

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

# mysqldump [options]-- all-databases / / Export all

Export database test

Full backup restarts the new binlog

Database import

Realize mysqldum full database backup and binlog data recovery

Check to enable binlog to create raw data

Solution: mysqldump full library backup + binlog restore

1. Mysqldump backup scheme:

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

2. Backup steps

Simulate a full database backup

Backup binlog log files before mysqldump full library backup

Simulation error to delete data

Create tom3

The data just deleted (id=2) has been restored, but the data generated after the backup has been lost, so you have to use binlog to further restore because the deletion occurs after the full database backup, while the mysqldump full database backup uses the-- flush-logs option, so you only need to analyze the binlog after the full database backup, that is, mysql-bin.000002.

Check the log_bin event to see the delete log

Before data recovery and deletion

After data recovery and deletion

View the final recovery result

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.