前置条件
目前已经完成了mysql的主备模式的搭建。
Mysql 主备模式
- STATEMENT模式（SBR） 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
- ROW模式（RBR） 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
- MIXED模式（MBR） 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
查看目前使用的模式
mysql> show global variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
执行一条语句
delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 2847 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'master.000005';
查看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'
切换为MIXED模式并查看效果
为什么要切换为MIXED模式？
row模式非常费空间，statement模式因为执行索引不一定相同可能会导致主备不一致问题，由此产生了row格式。所以线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。
如何切换
vi /etc/my.cnf # 根据自身情况调整位置
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.01 sec)
而且由于拥有statement模式的特色，我们在数据恢复上可以很方便
我们用测试数据库testKeep分别测试增、删、改的恢复方法
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 set (0.00 sec)
增：
mysql> insert into t values(8,8,'2021-6-4');
Query OK, 1 row affected (0.00 sec)
mysql> 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)
我们发现，日志行数比以前ROW 少特别多，而且完整的记录了sql语句，可以根据sql语句对误增的数据直接删除。
改
mysql> update t set a = 99 where id = 8;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> 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)
它会修改事件前后的区别，可以根据这些区别而还原数据
删
mysql> delete from t where id = 8;
Query OK, 1 row affected (0.01 sec)
mysql> 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 */ |
| 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)
很明显我们查出了 这条sql被创建之后发生的所有事情，被新增过，修改过，后来又被删除了。
读写分离
从节点开启只读模式
set global read_only是全局级别的,置为1之后root用户还可以写，其他用户不能写
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 |
+-----------------------+-------+
#建立只读账户并分配权限
CREATE USER '{账户名称}'@'%' IDENTIFIED BY '{账户密码}';
grant SELECT on *.* to '{账户名称}'@'%';
切换到只读用户,并进行测试
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in 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
结果显示可以查，不可以增删改。
参考文章
成文时间比较长（老存货了），已经找不到参考文献。
