我要评论我们用三个节点来做实验：
node1节点：172.16.251.132 主节点
node2节点：172.15.252.32 从节点
node3节点：172.16.251.49 配置proxysql
先配置主从复制：
node节点主节点：
#ntpdate 172.16.0.1 同步时间 #vim /etc/my.cnf.d/server.cnf [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id=1 log-bin=master-log #systemctl start mariadb
node2节点从节点：
#ntpdate 172.16.0.1 同步时间 #vim /etc/my.cnf.d/server.cnf [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id=11 relay_log=relay-log read_only=ON #systemctl start mariadb
接下来数据库授权：
node1节点：
#mysql >grant replication client,replication slave on *.* to 'repluser'@'172.16.252.%' identified by 'replpass'; >grant ALL on *.* to 'myadmin'@'172.16.251.%' identified by 'mypass'; （授权一个用户便于实验检测读写分离） >FLUSH PRIVILEGES; >show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000001 | 498 | | | +-------------------+----------+--------------+------------------+
node2节点：
#mysql
>change master to master_host='172.16.251.132',master_user='repluser',master_password='replpass',master_port=3306,master_log_file='master-log.000001',master_log_pos= 498;
>start slave;
>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.251.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 498
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 498
Relay_Log_Space: 818
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
node3节点上配置proxysql：
#lftp 172.16.0.1/pub 下载1.3.6-1版本的proxysql，也可以从网上直接下载
-->cd Sources/7.x86_64/proxysql/
-->mget proxysql-1.3.6-1-centos7.x86_64.rpm
#yum install ./proxysql-1.3.6-1-centos7.x86_64.rpm 安装
#vim /etc/proxysql.cnf 编辑配置文件
mysql_variables=
{
............
interfaces="0.0.0.0:3306;/tmp/proxysql.sock"
default_schema="base"
}
mysql_servers =
(
{
address = "172.16.251.132"
port = 3306
hostgroup = 0
status = "ONLINE"
weight = 1
compression = 0
}，
{
address = "172.16.252.32"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
}
）
mysql_users:
(
{
username = "myadmin"
password = "mypass"
default_hostgroup = 0
default_schema="base"
active = 1
}
）
mysql_replication_hostgroups=
(
{
writer_hostgroup=0 写组，组号为0的可写
reader_hostgroup=1 读组，组号为1的可读
comment="test repl 1"
}
）
#systemctl start proxysql
#ss -tnl 查看端口，6032和3306端口监听了
# mysql -umyadmin -pmypass -h172.16.251.49
>USE base;
>CREATE TABLE tbl1 (id INT);
>SHOW TABLES;
+----------------+
| Tables_in_base |
+----------------+
| tbl11 |
+----------------+
node1节点上：
#mysql; >USE base; >SHOW TABLES; +----------------+ | Tables_in_base | +----------------+ | tbl11 | +----------------+
node2节点上：
#mysql; >USE base; >SHOW TABLES; +----------------+ | Tables_in_base | +----------------+ | tbl11 | +----------------+
对于node1和node2节点来说，node3对base数据库的任何操作都能查看；node1作为写组成员，对base数据库的写操作，node2和node3也都能看到；作为读组成员node2对base数据库的操作，node1和node3都不能查看到。（node1、node2是主从复制，node3是proxysql）
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论