江苏龙网

  1. 主页 > 生活百科 > >

MaxScale 关于Linux下MySQL主备集群负载均衡之读写分离( 七 )

读写分离


登录测试
┌──[root@vms153.liruilongs.github.io]-[~]└─$mysql -uliruilong -pliruilongWelcome to the MariaDB monitor. Commands end with ; or g.Your MariaDB connection id is 51Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.MariaDB [(none)]> show grants;| Grants for liruilong@% || GRANT USAGE ON *.* TO 'liruilong'@'%' IDENTIFIED BY PASSWORD '*73CA7DD1B0BD11DCA665AB9C635C2188533331B3' || GRANT ALL PRIVILEGES ON `liruilong_db`.* TO 'liruilong'@'%' |2 rows in set (0.00 sec)MariaDB [(none)]>
从库查看是否同步
┌──[root@vms154.liruilongs.github.io]-[~]└─$mysql -uroot -pliruilong -e'select user,host from mysql.user;'| user | host || liruilong | % || maxscalemon | % || maxscaleroute | % || tom | % || root | 127.0.0.1 || root | ::1 || root | localhost || root | vms154.liruilongs.github.io |┌──[root@vms154.liruilongs.github.io]-[~]└─$
代理机器上查看路由端口
┌──[root@vms152.liruilongs.github.io]-[~]└─$ maxctrl list listeners┌─────────────────────┬──────┬──────┬─────────┬────────────────────┐│ Name │ Port │ Host │ State │ Service │├─────────────────────┼──────┼──────┼─────────┼────────────────────┤│ Read-Write-Listener │ 4006 │ :: │ Running │ Read-Write-Service │└─────────────────────┴──────┴──────┴─────────┴────────────────────┘┌──[root@vms152.liruilongs.github.io]-[~]└─$
客户机连接MaxScale代理，查到登录用户测试库等信息
┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'show grants'| Grants for liruilong@% || GRANT USAGE ON *.* TO 'liruilong'@'%' IDENTIFIED BY PASSWORD '*73CA7DD1B0BD11DCA665AB9C635C2188533331B3' || GRANT ALL PRIVILEGES ON `liruilong_db`.* TO 'liruilong'@'%' |┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'show databases;'| Database || information_schema || liruilong_db || test |┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;show tables;'| Tables_in_liruilong_db || user |
客户机连接MaxScale代理，通过代理插入一条数据
┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;insert into user values(2,now());'┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;select * from user;'| id | create_date || 1 | 2022-09-29 00:22:36 || 1 | 2022-09-29 11:08:38 || 1 | 2022-09-29 13:43:09 || 1 | 2022-09-29 13:51:33 || 1 | 2022-09-29 13:54:41 || 2 | 2022-09-30 13:29:59 |┌──[root@vms155.liruilongs.github.io]-[~]└─$
可以正常读取，下面在从(读)库插入一条数据
┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.154 -uliruilong -pliruilong -e'use liruilong_db;insert into user values(154,now());'┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.154 -uliruilong -pliruilong -e'use liruilong_db;select * from user;'| id | create_date || 1 | 2022-09-29 00:22:36 || 1 | 2022-09-29 11:08:38 || 1 | 2022-09-29 13:43:09 || 1 | 2022-09-29 13:51:33 || 1 | 2022-09-29 13:54:41 || 2 | 2022-09-30 13:29:59 || 154 | 2022-09-30 13:32:18 |
主库没有数据，但是通过代理读到了数据，说明读是通过从读取 。
┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.153 -uliruilong -pliruilong -e'use liruilong_db;select * from user;'| id | create_date || 1 | 2022-09-29 00:22:36 || 1 | 2022-09-29 11:08:38 || 1 | 2022-09-29 13:43:09 || 1 | 2022-09-29 13:51:33 || 1 | 2022-09-29 13:54:41 || 2 | 2022-09-30 13:29:59 |┌──[root@vms155.liruilongs.github.io]-[~]└─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;select * from user;'| id | create_date || 1 | 2022-09-29 00:22:36 || 1 | 2022-09-29 11:08:38 || 1 | 2022-09-29 13:43:09 || 1 | 2022-09-29 13:51:33 || 1 | 2022-09-29 13:54:41 || 2 | 2022-09-30 13:29:59 || 154 | 2022-09-30 13:32:18 |
通过代理插入数据，主从库数据同时存在，说明写是在主库，然后同步给从库


推荐阅读


上一篇：Macbook同时访问内外网

下一篇：聊聊软件开发，Java介绍和运行机制