This is my first article on getting started

preface

Well, recently the company has a lot of testing partners, sometimes people directly modify the database in the test environment, then some problems occurred, before using Oracle can use its own flashback data recovery, now the company is using MySQL, mainly using third-party tools to operate binglog log for processing.

1. MySQL flashback principle

First, there are several types of logs in mysql: Redo logs, undo logs, binary logs, error logs, Slow Query logs, general Logs, and relay logs. Among them, MySQL flashback technology mainly uses binlog log for data recovery.

2, about the binlog log

A binlog is a binary format file that records SQL statements updated by users to the database. Binlog contains events that describe changes to the database, such as table creation, data changes, etc., as well as potential changes, such as DELETE FROM ran WHERE bing = luan, WHERE no data is deleted. Unless roy-based logging is used, the SQL Statement contains all the changed data. Obviously, we do not log binlogs when we execute statements such as SELECT that do not design data changes, but when data updates are involved. Note that for transaction engines such as InnoDB, the transaction must be committed before the Binlog is recorded. The Binlog is written before the transaction is finally committed. When the Binlog is flushed to disk depends on the parameter sync_binlog. If set to 0, MySQL does not control the flush of the binlog, and the file system controls the flush of its cache. If set to a value other than 0, MySQL calls the flush of the file system to flush the binlog to disk every sync_binlog transaction. A value of 1 is the safest, with a maximum loss of one transaction update in the event of a system failure, but it has a performance impact. Typically, a value of 100 or 0 is set, sacrificing consistency for better performance. By default, binlog logs are in binary format and cannot be viewed using the view text tool commands (for example, cat, vi, etc.). Instead, mysqlBinlog is parsed.

3. Enable binlog

Configure /etc/my.cnf and add the following configuration under [mysqld]

[mysqld] server_id=1918 # log_bin = mysql-bin binlog_format = ROWCopy the code

Save the Settings and restart the MySQL service. MySQL > show variables like ‘%log_bin%’

Mysql Flashback tool

MyFlash is an open source tool developed by Meituan to roll back DML operations. The tool is github at github.com/Meituan-Dia… With this tool, you can flashback the MySQL database.

4.1 Download the source code:

git clone https://github.com/Meituan-Dianping/MyFlash.git

4.2 Installing Dependency Packages:

yum install -y gcc pkg-config glib2 libgnomeui-devel

4.3 Enter MyFlash and compile:

GCC -w 'pkg-config --cflags --libs glib-2.0' source/ binlogparseglib. c -o binary/flashbackCopy the code
  1. MyFlash syntax: –databaseNames: Specifies the name of the database to flash back to. If there are multiple databases, separate them with commas (,).
  2. –tableNames: Specifies the name of the table to be flashback. If there are multiple tables, separate them with commas (,).
  3. –start-position: indicates the start position of the flashback. If not specified, the file is rolled back from the start.
  4. –stop-position: Stop position of the flashback. If not specified, roll back to the end of the file.
  5. –start-datetime: indicates the start time of the flashback.
  6. –stop-datetime: The end time of the flashback.
  7. –sqlTypes: Specifies the SQL types to be rolled back. INSERT, UPDATE, DELETE are supported. Use commas (,) to separate multiple TYPES.
  8. –maxSplitSize: The file is cut to a fixed size to prevent pressure on the line from applying a large binlog size at a time.
  9. –binlogFileNames: Specifies the binlog files that need to be rolled back. Meituan’s documentation says that currently only a single binlog file is supported. Tests have supported multiple binlog files flashback at the same time.
  10. –outBinlogFileNameBase: Specifies the prefix of the output binlog file. If not specified, the default value is binlog_output_base. Flashback.
  11. LogLevel: Used by developers only. The default level is Error. Do not change this level in a production environment, or the output will be excessive.
  12. Included-gtids: specifies the gtid to be rolled back. The gtid can be a single gTID or a range gtids.
  13. Exworld-gtids: specifies the gtid that does not need to be rolled back. The usage is the same as included-gtids.

/flashback –databaseNames= database name –tableNames= table name –sqlTypes=’DELETE’ –binlogFileNames=/mysql/binlog/master-bin.000001

4.4 Application Flashback Logs:

mysqlbinlog binlog_output_base.flashback | mysql -uroot -p123456
Copy the code

5. Other common commands

  1. Mysql > show variables like ‘%log_bin%’;
  2. Show variables like ‘binlog_format’;
  3. Show binary logs;
  4. Current log write status: show master status;
  5. To clear binlog logs, run the reset master command.