mysql router 中间件体验
chenzuoqing Lv3
  2018-03-02 15:41:26 2018-03-02 15:41   2023-10-29 15:04:47      

mysql router 中间件

mysql router 通过两个不同的端口做读写分离（这点不是很方便，仅体验下，生产没需求），不对执行的 SQL 做判断是读写。另外mysql router在master 挂了的时候不会将 slave 节点的 master 指向新的 slave，和 mysqlfailover 有很大区别（当然它们是两个用途的工具）。可以自动剔除故障节点，并且恢复后自动上线。

环境用已有的 mysql 集群拓扑，带级联的复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
shell> mysqlrplshow --master=root:111111@"192.168.122.66:3307" \
             --discover-slaves-login=root:111111 -r -v         
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.122.66: ... connected.
# Finding slaves for master: 192.168.122.66:3307
# master on 192.168.122.70: ... connected.
# Finding slaves for master: 192.168.122.70:3307
# master on 192.168.122.70: ... connected.
# Finding slaves for master: 192.168.122.70:3306
# master on 192.168.122.80: ... connected.
# Finding slaves for master: 192.168.122.80:3307

# Replication Topology Graph
192.168.122.66:3307 (MASTER)                                   【hostname：centos-66】
   |
   +--- 192.168.122.70:3307 [IO: Yes, SQL: Yes] - (SLAVE + MASTER)
   |   |
   |   +--- 192.168.122.70:3306 [IO: Yes, SQL: Yes] - (SLAVE)  【hostname：debian-70】
   |
   +--- 192.168.122.80:3307 [IO: Yes, SQL: Yes] - (SLAVE)      【hostname：sl-80】

安装

前往官网下载
当前版本MySQL Router 2.1.5

  1. 安装Linux二进制包 
    1
    2
    3
    4
    5
    6
    7
    8
    shell> cd /usr/src
    shell> wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.1.5-linux-glibc2.12-x86-64bit.tar.gz
    shell> tar xf mysql-router-2.1.5-linux-glibc2.12-x86-64bit.tar.gz  -C /usr/local
    shell> cd /usr/local
    shell> ln -sv /usr/local/mysql-router-2.1.5-linux-glibc2.12-x86-64bit/ mysql-router
    shell> cd mysql-router
    shell> mkdir etc
    shell> cp share/doc/mysqlrouter/sample_mysqlrouter.conf etc/mysqlrouter.conf

配置

  1. 修改配置文件，选项文档
  2. 编辑/usr/local/mysql-router/etc/mysqlrouter.conf 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    # MySQL Router sample configuration
    #
    # The following is a sample configuration file which shows
    # most of the plugins available and most of their options.
    #
    # The paths used are defaults and should be adapted based
    # on how MySQL Router was installed, for example, using the
    # CMake option CMAKE_INSTALL_PREFIX
    #
    # The logging_folder is kept empty so message go to the
    # console.
    #
    [DEFAULT]
    logging_folder = /usr/local/mysql-router/logs  # 日志目录，需要创建
    plugin_folder = /usr/local/mysql-router/lib/mysqlrouter  # 插件目录
    config_folder = /usr/local/mysql-router/etc    # 配置文件目录
    runtime_folder = /var/run
    data_folder = /usr/local/mysql-router/data     # 程序数据文件目录
    #keyring_path = /var/lib/keyring-data          # 认证密钥地址，不用它
    #master_key_path = /var/lib/keyring-key

    [logger]
    level = INFO                                   # 日志级别

    [routing:read_write]                           # 添加一个routing段，read_write为自定义名称
    # To be more transparent, use MySQL Server port 3306
    bind_address= 192.168.122.66                   # 此配置段监听地址
    bind_port = 7001                               # 监听端口
    mode = read-write                              # 读写模式(只有读写和只读两种)
    destinations = 192.168.122.66:3307, 192.168.122.70:3307  # 第一个为当前的master，逗号分隔的为备选master，若当前master不可用将读写请求发送到备选master

    [routing:read_only]                            # 定义一个只读配置段
    bind_address= 192.168.122.66                   # 读请求监听地址
    bind_port = 7002
    connect_timeout = 3
    max_connections = 1024                         # 最大连接
    destinations = 192.168.122.70:3307,192.168.122.70:3306,192.168.122.80:3307
    mode = read-only                               # 只读模式，destinations的地址会轮询得到请求，会自动下线关闭的服务器

    # If no plugin is configured which starts a service, keepalive
    # will make sure MySQL Router will not immediately exit. It is
    # safe to remove once Router is configured.
    [keepalive]
    interval = 60

测试

  • 启动mysql router： 
    1
    2
    shell> cd /usr/local/mysql-router
    shell> nohup ./bin/mysqlrouter -c etc/mysqlrouter.conf &

  1. 写入是一直由一个节点承担，除非down掉才换

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    # 测试写操作端口的
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7001 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | centos-66 |
    +---------------+-----------+

    # 测试读操作的端口，是否轮询转发
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |   -- 这里因为看host没端口，这台机有3306和3307两个实例在的
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |   -- 另外一个端口的
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | hostname      | sl-80 |       -- sl-80轮询
    +---------------+-------+

  2. 此时停止sl-80的slave实例，测试是否自动剔除故障节点

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    [root@sl-80 ~]# mysqld_multi stop 3307
    [root@sl-80 ~]# mysqld_multi report   
    Reporting MySQL servers
    MySQL server from group: mysqld3307 is not running

    # 已经剔除了sl-80的读节点
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+

  3. 测试自动上线

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    # 启动读节点
    [root@sl-80 ~]# mysqld_multi start 3307
    [root@sl-80 ~]# mysqld_multi report
    Reporting MySQL servers
    MySQL server from group: mysqld3307 is running

    # 已经自动上线
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | hostname      | sl-80 |
    +---------------+-------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
    [root@centos-66 ~]# mysql -uroot -p'111111' -P7002 -h192.168.122.66 -e "show variables like 'hostname'"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | hostname      | debian-70 |
    +---------------+-----------+
mysql router 中间件体验
2018/03/02/mysql-router-中间件体验/
Author
chenzuoqing
Published
2018-03-02 15:41
License
 Comments
  1. mysql router 中间件
    1. 安装
    2. 配置
    3. 测试