手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL主从复制读写分离与高可用配置

[日期:2018-04-14] 来源:Linux社区  作者:dyc2005 [字体: ]

一、说明

前面我们说了MySQL的安装配置(并提供一键安装脚本),MySQL语句使用以及备份恢复MySQL数据;本次要介绍的是MySQL的主从复制,读写分离;及高可用MHA。

环境如下:
master:CentOS7_x64 mysql5.721 172.16.3.175 db1
slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2
slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3
proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql

架构图:

说明:
配置测试时为了方便关闭了防火墙头,selinux安全策略;
现实中请开放防火墙策略;myslqdb的安装已经有脚本一键安装并配置好;这里就不在重复配置;只对对应的角色贴出对应的配置或安装与之相关的软件;

二、主从复制配置

一台主数据库,N从节点;从节点开启两个线程,通过Slave_IO_Running线程和主节点上有权限的账号从 主数据库节点复制binlog日志到本地,能过Slave_SQL_Running线程在本地执行binlog日志,达到主从节点内容同步;

master配置:
egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld]
datadir=/data1/mysqldb
socket=/tmp/mysql.sock
key_buffer_size        = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size      = 8
query_cache_limit      = 1M
query_cache_size        = 64M
query_cache_type        = 1
symbolic-links=0
innodb_file_per_table=ON
skip_name_resolve=ON

server-id      = 1
log_bin        = /data1/mysqldb/mysql-bin.log
[mysqld_safe]
log-error=/usr/local/mysql/logs/error.log
pid-file=/data1/mysqldb/mysql.pid
!includedir  /usr/local/mysql/etc/my.cnf.d

创建从节点同步账号:

mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass';
mysql > flush privileges;
mysql >show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |      622 |

主节点上的binlog日志文件及位置;请记下;从节点第一次同步时需要用;

slave节点:
egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf

[mysqld]
datadir=/data1/mysqldb
socket=/data1/mysqldb/mysql.sock
key_buffer_size        = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size      = 8
query_cache_limit      = 1M
query_cache_size        = 64M
query_cache_type        = 1
symbolic-links=0

innodb_file_per_table=ON
skip_name_resolve=ON

server-id              = 11        #从节点标识ID  各从节点均不一样
relay_log = relay-log
read_only=ON

[mysqld_safe]
log-error=/usr/local/mysql/log/error.log
pid-file=/var/run/mysql/mysql.pid
!includedir /usr/local/mysql/etc/my.cnf.d


启动mysq数据库
注意:两台从节点的server-id 值不一样;其他的都一样;因此从节点只展示一个配置文件;
登录数据库并同步数据启动slave
两台slave均要同步并启动

mysql > CHANGE MASTER TO MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622;
mysql > start slave;    #启动从节点()

#查看从节点状态
mysql > SHOW SLAVE STATUS;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.3.175                  #主节点
                  Master_User: repluser                          #同步账号
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
              Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 582
        Relay_Master_Log_File: mysql-bin.000001
            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              #最后同步的错误 0表示正常同步
                  Last_Error:
                Skip_Counter: 0
          Exec_Master_Log_Pos: 622
              Relay_Log_Space: 615
              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
                  Master_UUID: 57017c43-36e3-11e8-ac76-080027393fc7
            Master_Info_File: /data1/mysqldb/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
        Replicate_Rewrite_DB:
                Channel_Name:
          Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified


测试主从同步
在master导入测试数据;修改数据并查看slave 中的数据是否一致;

[root@db1 ~]# mysql < Testdb.sql
登录数据库
[root@db1 ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
 study              |
+--------------------+
5 rows in set (0.00 sec)
###study测试数据库导入成功
mysql> use study;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class          |
| course          |
| part            |
| score          |
| student        |
| tb31            |
| tb32            |
| teacher        |
| test1          |
| test2          |
| user_info      |
+-----------------+
11 rows in set (0.00 sec)
#删除test1 test2表


slave从节点上查看

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class          |
| course          |
| part            |
| score          |
| student        |
| tb31            |
| tb32            |
| teacher        |
| user_info      |
+-----------------+
9 rows in set (0.00 sec)


数据已经可以正常同步;注意主从同步只需要第一次手动启动;之后都随mysql服务自动启动;主从同步架构只方便了数据的同步,此时如果没有 第三方工具介入想做到读写分离就需要在程序中去做,难免出错;而出错了,就需要手动同步数据;这里通过proxysql来做读写分离;

三、proxysql之读写分离

以上已经完成了主从复制配置;然而这只是一个基本配置,加上一个proxysql实现mysql读写分离,proxysql类似haproxy七层代理路由功能且支持MySQL 协议的的数据库代理;是dba开发给dba使用的;用户请求发向proxysql,如果是写请求发往主节点;读请求发下从节点组中;以此实现读写分离;一定程序上减轻了主数据库的io压力;
下载安装proxysql
目前最新版本是1.4.7-1(由于最新版本有问题)
我们这里下载使用1.3.6-1的基于CentOS7的rpm包;下载到本地并yum安装

[root@proxysql ~]# yum install  proxysql-1.3.6-1-centos7.x86_64.rpm -y
[root@proxysql ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf      #主配置文件
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl


配置如下:
在配置proxysql之前需要在主节点配置授权账号以作proxysql对主从节点操作;另外proxysql上的mysql客户端工具需要和主从节点上的保持一致;
在主节点master上授权登录账号:

mysql > GRANT ALL ON *.* TO 'myadmin'@'172.16.3.%' identified by 'mypass';

proxysql.cnf配置
[root@proxysql ~]# egrep -v '(^$|^#)' /etc/proxysql.cnf

datadir="/var/lib/proxysql"
admin_variables=

    admin_credentials="admin:admin"              #proxysql自己的管理用户名密码
    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
    threads=4                        #线程数,建议和cpu核心数一致
    max_connections=2048      #最大连接
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:3306;/tmp/proxysql.sock"            #对外接口
    default_schema="information_schema"
    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
    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 = "172.16.3.175" # 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          # 设置组号
        status = "ONLINE"    # default: ONLINE
        weight = 1            # default: 1
        compression = 0      # default: 0
        max_connections = 200            ###定义最大的连接
    },
    {
        address = "172.16.3.235" # 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 = 2          # no default, required
        status = "ONLINE"    # default: ONLINE
        weight = 1            # default: 1
        compression = 0      # default: 0
        max_connections=1000
    },
    {
        address = "172.16.3.241" # 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 = 2          # no default, required
        status = "ONLINE"    # default: ONLINE
        weight = 1            # default: 1
        compression = 0      # default: 0
        max_connections=1000
    }
)
mysql_users:
(
    {
        username = "myadmin" # no default , required
        password = "mypass" # default: ''
        default_hostgroup = 1 # default: 0
        max_connections=1000
        default_schema="test"
        active = 1            #是否激活
    }
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
        {
                writer_hostgroup=1        #定义写组号1
                reader_hostgroup=2      #定义读组号2
                comment="test repl 1"  #注释内容
      }
)


启动proxysql服务
[root@proxysql ~]# service proxysql start

测试proxysql

模拟通过proxysql使用数据库
[root@proxysql ]# mysql -h172.16.3.175 -umyadmin -pmypass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17406
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

mysql > show databases;
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
###删除study数据库user_info中6 -12之间的数据
删除之前:
mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|  1 | san  |  20 | 男    |        1 |
|  2 | dong  |  29 | 男    |        2 |
|  4 | Ling  |  28 | 男    |        4 |
|  5 | ling  |  28 | 男    |        3 |
|  6 | dong  |  30 | 男    |        1 |
|  7 | b    |  11 | 女    |        1 |
|  8 | c    |  12 | 女    |        1 |
|  9 | d    |  18 | 女    |        4 |
|  10 | e    |  22 | 男    |        3 |
|  11 | f    |  23 | 男    |        2 |
|  12 | dongy |  22 | 男    |        1 |
+-----+-------+------+--------+----------+
11 rows in set (0.00 sec)
删除之后:
mysql> delete from user_info where nid >6 and nid <12;
Query OK, 5 rows affected (0.03 sec)

mysql> select * from user_info;
+-----+-------+------+--------+----------+
| nid | name  | age  | gender | part_nid |
+-----+-------+------+--------+----------+
|  1 | san  |  20 | 男    |        1 |
|  2 | dong  |  29 | 男    |        2 |
|  4 | Ling  |  28 | 男    |        4 |
|  5 | ling  |  28 | 男    |        3 |
|  6 | dong  |  30 | 男    |        1 |
|  12 | dongy |  22 | 男    |        1 |
+-----+-------+------+--------+----------+
6 rows in set (0.00 sec)


到主从节点上查看,会发现以上的查和修改数据都被proxysql正确的代理到后端处理了;
以上看了并不直观;为了查看proxysql与各主从节点通讯我们在主从节点上安装tcpdump并过滤包

主节点:
类似如下:
[root@db1 ~]# tcpdump -i enp0s3 -nn tcp port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes
18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [S], seq 3385407732, win 29200, options [mss 1460,sackOK,TS val 17576713 ecr 0,nop,wscale 7], length 0
18:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [S.], seq 1579426335, ack 3385407733, win 28960, options [mss 1460,sackOK,TS val 29413673 ecr 17576713,nop,wscale 7], length 0
18:04:34.680902 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [.], ack 1, win 229, options [nop,nop,TS val 17576715 ecr 29413673], length 0
18:04:34.681264 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [P.], seq 1:83, ack 1, win 227, options [nop,nop,TS val 29413675 ecr 17576715], length 82
....
从节点:
类似如下:
[root@db2 data1]# tcpdump -i enp0s3 -nn tcp port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes
18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: Flags [S], seq 76520456, win 29200, options [mss 1460,sackOK,TS val 17479189 ecr 0,nop,wscale 7], length 0
...........


proxysql命令行管理接口:支持运行时修改

 [root@proxysql]#  mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> show databases;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0  | main    |                              |
| 2  | disk    | /var/lib/proxysql/proxysql.db |
| 3  | stats  |                              |
| 4  | monitor |                              |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)

以上stats,monitor,main都从配置文件中获取的数据库;可以通过类似mysql运行时修改;而不需要重启;更多命令行的配置请参考github;

至此我们基于proxysql主从复制读写分离架构已经完成;
双主或多主模型是无须实现读写分离,仅需要负载均衡:haproxy, nginx, lvs等;
proxysql并没有解决,当主数据岩机时的问题;此时就需要量MHA来解决 ;后续再介绍;

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-04/151893.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款