一、数据库读写分离器
1、 ProxySQL读写分离解决方案
- ProxySQL是一个高性能、高可用性、协议清楚代理MySQL和叉(如Percona服务器和MariaDB)。主页地址http://www.proxysql.com/；
- 下载地址：https://github.com/sysown/proxysql/releases
MaxScale
2、其他读写分离解决方案
- cobar, gizzard解决方案：采用分片机制，大的数据集分片在各个节点上，各个节点有自己的主从节点
- AliSQL：阿里的开源sql项目
- OneProxy
- amoeba for MySQL
- MaxScale
3、读写分离应用环境：
- 双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs, ...
- pxc解决方案：Percona XtraDB Cluster
-
MariaDB Cluster
实验环境
二、ProxySQL应用实例
- 主节点配置：
#安装mariadb同步时间
[root@mysql-19 ~]# yum install mariadb-server ntpdate -y
[root@mysql-19 ~]# ntpdate time1.aliyun.com
#配置mysql配置文件
[root@mysql-19 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=1
log_bin=master-log
#启动mysql
[root@mysql-19 ~]# systemctl start mariadb
#配置从节点授权用户
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.01 sec)
#配置读写分离器授权用户
MariaDB [(none)]> GRANT ALL ON *.* TO 'myadmin'@'192.168.1.%' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.01 sec)
#刷新授权
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#备份当前数据库
[root@mysql-19 ~]# mysqldump -uroot --all-databases -R -E --triggers -x --master-data=2 > /var/lib/mysql/alldb.sql
#拷贝到新加入的从节点中
[root@mysql-19 ~]# scp /var/lib/mysql/alldb.sql 192.168.1.21:/root
- 从节点2配置：
#安装mariadb同步时间
[root@mysql-slave-21 ~]# yum install mariadb-server ntpdate -y
[root@mysql-slave-21 ~]# ntpdate time1.aliyun.com
#配置mysql配置文件
[root@mysql-slave-21 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=15
relay_log=relay-log
read_only=ON
#恢复数据库并查看二进制日志位置
[root@mysql-slave-21 ~]# mysql < alldb.sql
[root@mysql-slave-21 ~]# less alldb.sql
FILE='master-log.000004', MASTER_LOG_POS=497;
#启动mysql
[root@mysql-slave-21 ~]# systemctl start mariadb
#配置从节点
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.19',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000004',MASTER_LOG_POS=497;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
#启动从节点复制线程
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.19
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000004
Read_Master_Log_Pos: 497
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000004
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: 497
- 读写分离器配置：
#下载proxysql并安装
[root@proxysql-60 ~]# ls
anaconda-ks.cfg proxysql-1.4.12-1-centos7.x86_64.rpm
[root@proxysql-60 ~]# yum install ./*.rpm
#设置配置文件
[root@proxysql-60 ~]# vim /etc/proxysql.cnf
datadir="/var/lib/proxysql" #proxy自己的数据目录
admin_variables=
{
admin_credentials="admin:admin" #登录proxysql时候用户名密码
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="172.0.0.1:6032" #m管理地址端口
# refresh_interval=2000
# debug=true
}
mysql_variables=
{
threads=4 #启用4个线程
max_connections=2048 #最大并发连接数
default_query_delay=0 #默认延迟时间
default_query_timeout=36000000 #默认超时时间
have_compress=true #是否启用压缩
poll_timeout=2000 #轮询超时时间
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:3306" #mysql接口
default_schema="mydb" #默认登录后操作数据库
stacksize=1048576 #站大小
server_version="5.5.30" #服务器版本
connect_timeout_server=3000 #连接服务器超时时间
monitor_username="monitor" #监控用户名
monitor_password="monitor" #监控密码
monitor_history=600000 #监控历史
monitor_connect_interval=60000
monitor_ping_interval=10000 #ping时间
monitor_read_only_interval=1500 #监控从节点
monitor_read_only_timeout=500 #监控从节点超时时间
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{
address = "192.168.1.19" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections=200
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
},
{
address = "192.168.1.20" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 1 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections=500
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
},
{
address = "192.168.1.21" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 1 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections=500
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
}
）
mysql_users:
(
{
username = "myadmin" # no default , required
password = "mypass" # default: ''
default_hostgroup = 0 # default: 0
active = 1 # default: 1
default_schema="mydb"
}
)
#语句分离配置段
mysql_query_rules:
(
# {
# rule_id=1 #规则1
# active=1 #有效的
# match_pattern="^SELECT .* FOR UPDATE$" #规则内容
# destination_hostgroup=0 #调度到那个组
# apply=1
# },
# {
# rule_id=2
# active=1
# match_pattern="^SELECT"
# destination_hostgroup=1
# apply=1
# }
)
#定义读写组配置段
mysql_replication_hostgroups=
(
{
writer_hostgroup=0 #写组
reader_hostgroup=1 #读组
comment="test repl 1" #测试组
}
# {
# writer_hostgroup=50
# reader_hostgroup=60
# comment="test repl 2"
# }
)
#启动proxysql
[root@proxysql-60 ~]# service proxysql start
Starting ProxySQL: 2018-10-29 07:54:14 [INFO] Using config file /etc/proxysql.cnf
DONE!
#查看端口已启动4个线程
[root@proxysql-60 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:3306 *:*
LISTEN 0 128 *:3306 *:*
LISTEN 0 128 *:3306 *:*
LISTEN 0 128 *:3306 *:*
三、测试proxysql
#连接proxysql
[root@proxysql-60 ~]# mysql -umyadmin -pmypass -h192.168.1.19 -P3306
#此时连接，对数据库执行写操作是对主节点操作。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
#创建mydb库
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.01 sec)
#使用mydb库
MariaDB [(none)]> use mydb;
Database changed
#在mydb库中创建tbl1表
MariaDB [mydb]> create table tbl1(name char(40),age int(10),class int(10));
Query OK, 0 rows affected (0.05 sec)
#查看tbl1表
MariaDB [mydb]> desc tbl1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| class | int(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#查看从服务器是否同步主服务器
[root@mysql-20 ~]# mysql
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> desc tbl1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| class | int(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#使用抓包工具查看与proxysql和主节点通信
[root@mysql-20 ~]# tcpdump -i ens33 -nn tcp port 3306
四、proxysql的管理接口
[root@proxysql-60 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SHOW DATABASES;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
#查看服务器组
MySQL [(none)]> USE monitor;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#运行时候，使用语句insert，可以在此表中添加或删除后端数据库服务器
MySQL [monitor]> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.1.19 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.1.21 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.1.20 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
#查询当前读写组
MySQL [monitor]> SELECT * FROM runtime_mysql_group_replication_hostgroups;
网友评论