MySQL 读写分离( 三 ) _读写分离

为了区分查询结果到底是哪台节点上的，我们先把S1 和S2 上的数据进行标识。
mysql> update tb_user set name='Jack-S1' where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 |# 查询结果为Jack-S1 |说明来自S1 。。。。mysql> update tb_user set name='Jack-S2' where id=1;Query OK, 1 row affected (0.02 sec)Rows matched: 1Changed: 1Warnings: 0mysql> select * from tb_user;+----+---------+------+| id | name| sex|+----+---------+------+|1 | Jack-S2 | 1|# 查询结果为Jack-S2 |说明来自S2 。。。。。我们切换会mycat进行查询
mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 |# 从节点 S1| 2 | Tony | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 |+----+---------+------+5 rows in set (0.00 sec)mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 |# 从节点 S1| 2 | Tony | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 |+----+---------+------+5 rows in set (0.01 sec)mysql> select * from tb_user;+----+------+------+| id | name | sex |+----+------+------+| 1 | Jack | 1 || 2 | Tony | 1 |# 主节点M2，因为M1负责写操作，我们配了balance="1"| 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 |+----+------+------+5 rows in set (0.03 sec)mysql> select * from tb_user;+----+------+------+| id | name | sex |+----+------+------+| 1 | Jack | 1 || 2 | Tony | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 |+----+------+------+5 rows in set (0.00 sec)插入一条数据看看4个节点同步情况
mysql> insert into tb_user values(6,'Baky','2');
Query OK, 1 row affected (0.33 sec)

文章插图

mysql> update tb_user set name='SZ-马' where id=2;
update 更新也是没有问题的。

Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from tb_user;+----+--------+------+| id | name | sex |+----+--------+------+| 1 | Jack | 1 || 2 | SZ-马 | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 || 6 | Baky | 2 |+----+--------+------+6 rows in set (0.01 sec)mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 || 2 | SZ-马 | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 || 6 | Baky | 2 |+----+---------+------+6 rows in set (0.02 sec)mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 || 2 | SZ-马 | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 || 6 | Baky | 2 |+----+---------+------+6 rows in set (0.25 sec)

【MySQL 读写分离】模拟M1节点宕机，看看会不会切换M2及能否执行写入操作
systemctl stop mysqld

mysql> select * from tb_user;+----+---------+------+| id | name | sex |+----+---------+------+| 1 | Jack-S1 | 1 || 2 | SZ-马 | 1 || 3 | Mack | 2 || 4 | Lucy | 2 || 5 | Mely | 2 || 6 | Baky | 2 |+----+---------+------+6 rows in set (0.01 sec)mysql> select * from tb_user;+----+---------+------+| id | name| sex|+----+---------+------+|1 | Jack-S2 | 1||2 | SZ-马| 1||3 | Mack| 2||4 | Lucy| 2||5 | Mely| 2||6 | Baky| 2|mysql> insert into tb_user values(7,'www','1');Query OK, 1 row affected (0.01 sec)mysql> select * from tb_user;+----+--------+------+| id | name| sex|+----+--------+------+|1 | Jack| 1||2 | SZ-马| 1||3 | Mack| 2|# M2|4 | Lucy| 2||5 | Mely| 2||6 | Baky| 2||7 | www| 1|+----+--------+------+7 rows in set (0.00 sec)mysql> select * from tb_user;+----+---------+------+| id | name| sex|+----+---------+------+|1 | Jack-S2 | 1||2 | SZ-马| 1||3 | Mack| 2||4 | Lucy| 2||5 | Mely| 2||6 | Baky| 2||7 | www| 1|+----+---------+------+7 rows in set (0.00 sec)mysql> select * from tb_user;+----+---------+------+| id | name| sex|+----+---------+------+|1 | Jack-S1 | 1||2 | Tony| 1||3 | Mack| 2||4 | Lucy| 2||5 | Mely| 2||6 | Baky| 2|+----+---------+------+6 rows in set (0.00 sec

OK，读写分离讲完啦，你学废了吗。