MySQL之主从复制的读写分离

2018-11-04 本文已影响3人任总

一、数据库读写分离器

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;

MySQL之主从复制的读写分离

一、数据库读写分离器

1、 ProxySQL读写分离解决方案

2、其他读写分离解决方案

3、读写分离应用环境：

二、ProxySQL应用实例

三、测试proxysql

四、proxysql的管理接口

猜你喜欢

热点阅读