precondition
The mysql active/standby mode has been set up.
Mysql Active/standby mode
- STATEMENT mode (SBR) Each SQL STATEMENT that modifies data is recorded in the binlog. The advantage is that it is not necessary to record every SQL statement and every row of data changes, reducing the amount of binlog log, saving IO, and improving performance. The downside is that in some cases the data in the master-slave is inconsistent (such as sleep(), last_insert_id(), and user-defined functions(UDF)).
- The ROW mode (RBR) does not record the context of each SQL statement, but only records which data has been modified and how. And there are no specific cases where the call and trigger of a stored procedure, or function, or trigger cannot be copied correctly. The disadvantage is that a large number of logs are generated, especially when ALTER table is used.
- MIXED mode (MBR) Is a mixture of the above two modes. The STATEMENT mode is used to store the binlog. For operations that cannot be copied in STATEMENT mode, the ROW mode is used to store the binlog.
View the current mode in use
mysql> show global variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
Copy the codeExecute a statement
delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
Copy the codemysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 2847 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > show binlog events in 'master.000005';Copy the codeCheck the binog
| mysql-bin.000005 | 2627 | Query | 1 | 2714 | BEGIN |
| mysql-bin.000005 | 2714 | Table_map | 1 | 2768 | table_id: 118 (testKeep.t) |
| mysql-bin.000005 | 2768 | Write_rows | 1 | 2816 | table_id: 118 flags: STMT_END_F |
| mysql-bin.000005 | 2816 | Xid | 1 | 2847 | COMMIT /* xid=1507 */ |
| mysql-bin.000005 | 2847 | Anonymous_Gtid | 1 | 2926 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
Copy the codeSwitch to MIXED mode and see the effect
Why switch to MIXED mode?
The ROW mode is very space-intensive, and the Statement mode may cause primary/standby inconsistency because the indexes are not necessarily the same. So if MySQL sets the binlog format as statement, it’s basically considered an unreasonable setting. At the very least, you should set the binlog format to mixed.
How to switch
Log-bin =mysql-bin binlog_format=MIXED mysql> show global variables like "%binlog_format%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.01sec)Copy the codeAnd because of the characteristics of statement mode, we can be very convenient in data recovery
We used the test database testKeep to test the recovery methods of adding, deleting, and changing
mysql> select * from t; +----+------+---------------------+ | id | a | t_modified | +----+------+---------------------+ | 1 | 1 | 2018-11-13 00:00:00 | | 2 | 2 | 2018-11-12 00:00:00 | | 3 | 3 | 2018-11-11 00:00:00 | | | | 4 4 2018-11-10 00:00:00 | | | | 7 7 2018-11-15 00:00:00 | + - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the codeAdd:
Mysql > insert into t values(8,8,'2021-6-4'); Query OK, 1 row affected (0.00 SEC)Copy the codemysql> show binlog events in 'mysql-bin.000007'; +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- ---+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- -- -- -- + | mysql - bin. 000007 | | 4 Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 | | mysql-bin.000007 | 125 | Previous_gtids | 1 | 156 | | | mysql-bin.000007 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 235 | Query | 1 | 333 | BEGIN | | mysql-bin.000007 | 333 | Query | 1 | 462 | use `testKeep`; Insert into t values (8, 8, '2021-6-4') | | mysql - bin. 000007 | 462 | Xid | 1 | 493 | COMMIT / * Xid = 58 * / | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- --+ 6 rows in set (0.00 SEC)Copy the codeWe found that the number of log rows is much less than the previous ROW, and the SQL statement is completely recorded, according to the SQL statement, the misadded data can be directly deleted.
改
mysql> update t set a = 99 where id = 8; Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0Copy the codemysql> show binlog events in 'mysql-bin.000007'; +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- ---+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- -- -- -- + | mysql - bin. 000007 | | 4 Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 | | mysql-bin.000007 | 125 | Previous_gtids | 1 | 156 | | | mysql-bin.000007 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 235 | Query | 1 | 333 | BEGIN | | mysql-bin.000007 | 333 | Query | 1 | 462 | use `testKeep`; Insert into t values (8, 8, '2021-6-4') | | mysql - bin. 000007 | 462 | Xid | 1 | 493 | COMMIT / * Xid = 58 * / | | mysql-bin.000007 | 493 | Anonymous_Gtid | 1 | 572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 572 | Query | 1 | 671 | BEGIN | | mysql-bin.000007 | 671 | Query | 1 | 797 | use `testKeep`; update t set a = 99 where id = 8 | | mysql-bin.000007 | 797 | Xid | 1 | 828 | COMMIT /* xid=69 */ | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------- --+ 10 rows in set (0.00 SEC)Copy the codeIt modifies the differences before and after the event and can restore data based on those differences
delete
mysql> delete from t where id = 8; Query OK, 1 row affected (0.01sec)Copy the codemysql> show binlog events in 'mysql-bin.000007'; +------------------+------+----------------+-----------+-------------+-------------------------------------------------- ----+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+-------------------------------------------------- 000007 | | - + mysql - bin. 4 | Format_desc | 1 | 125 | Server ver: 8.0.25, Binlog ver: 4 | | mysql-bin.000007 | 125 | Previous_gtids | 1 | 156 | | | mysql-bin.000007 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 235 | Query | 1 | 333 | BEGIN | | mysql-bin.000007 | 333 | Query | 1 | 462 | use `testKeep`; Insert into t values (8, 8, '2021-6-4') | | mysql - bin. 000007 | 462 | Xid | 1 | 493 | COMMIT / * Xid = 58 * / | | mysql-bin.000007 | 493 | Anonymous_Gtid | 1 | 572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 572 | Query | 1 | 671 | BEGIN | | mysql-bin.000007 | 671 | Query | 1 | 797 | use `testKeep`; update t set a = 99 where id = 8 | | mysql-bin.000007 | 797 | Xid | 1 | 828 | COMMIT /* xid=69 */ | | mysql-bin.000007 | 828 | Anonymous_Gtid | 1 | 907 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000007 | 907 | Query | 1 | 997 | BEGIN | | mysql-bin.000007 | 997 | Query | 1 | 1108 | use `testKeep`; delete from t where id = 8 | | mysql-bin.000007 | 1108 | Xid | 1 | 1139 | COMMIT /* xid=88 */ | +------------------+------+----------------+-----------+-------------+-------------------------------------------------- ----+ 14 rows in set (0.00 SEC)Copy the codeObviously, we found out everything that happened after this SQL was created, and it was added, changed, and deleted.
Reading and writing separation
The secondary node is enabled in read-only mode
Set global read_only indicates the global level. After set to 1, the root user can still write data, but other users cannot write data
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
Copy the code#Create a read-only account and assign permissionsCREATE USER '{username}'@'%' IDENTIFIED BY '{username}'; Grant SELECT on *.* to '{username}'@'%';Copy the codeSwitch to a read-only user and test
mysql> select count(*) from t;+ -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | | 6 + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)
mysql>Insert into t values (15, 15,'2021-6-4');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> delete from t where id = 2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> update t set a = 99 where id = 2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
Copy the codeThe results show that can be checked, can not be added or deleted.
Refer to the article
Written time is relatively long (old inventory), no reference has been found.