MySQL主从复制--mysql-proxy实现MySQL-5.6读写分离
首先连接管理端口查看下状态# mysql -uadmin -h 172.16.1.102 -padmin --port=4041
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM backends;
+-------------+-------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+---------+------+------+-------------------+
| 1 | 172.16.1.101:3306 | unknown | rw | NULL | 0 |
| 2 | 172.16.1.105:3306 | unknown | ro | NULL | 0 |
+-------------+-------------------+---------+------+------+-------------------+
2 rows in set (0.01 sec)
可以看到主节点和从节点都为unknown，因为我们没有发起任何读或者写操作
在另一个节点上向mysql-proxy发起一个写操作
# mysql -uroot -h 172.16.1.102 -p --port=4040 -e 'CREATE DATABASE hellodb;'
Enter password:
再来通过管理接口查看状态信息，可以看到主节点状态已经为up
mysql> SELECT * FROM backends;
+-------------+-------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+---------+------+------+-------------------+
| 1 | 172.16.1.101:3306 | up | rw | NULL | 0 |
| 2 | 172.16.1.105:3306 | unknown | ro | NULL | 0 |
+-------------+-------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
接下来我们再发起一个读请求
# mysql -uroot -h 172.16.1.102 -p --port=4040 -e 'SELECT user,host,password FROM mysql.user;'
Enter password:
+---------+-------------------+-------------------------------------------+
| user | host | password |
+---------+-------------------+-------------------------------------------+
| root | localhost | |
| root | slave.network.com | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | slave.network.com | |
| repuser | 172.16.%.% | *304A91F0E46BBB1E641D3D95E225E9AAA27077CE |
| root | 172.16.%.% | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+---------+-------------------+-------------------------------------------+
再来通过管理接口查看状态信息，可以看到从节点状态已经为up
mysql> SELECT * FROM backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.16.1.101:3306 | up | rw | NULL | 0 |
| 2 | 172.16.1.105:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
页:
[1]