MaxScale binlog server2.2.6与读写分离失败实践-每日运维

MaxScale是一个动态数据的路由平台，位于数据库层和该数据库的客户端之间。但是，这里描述的binlog路由器与原始概念有所不同。在传统的复制中，有一个主和多个从，但添加主机或者恢复主机，需要在从执行一些操作才能成为主

在之前的mha中binlog日志是mha通过ssh识别最新的中继日志，配合配置中最新的主节点，同步到其他从站，并且能让从站重新开始复制而在maxscale中是在主从之间引入代理层解决，从只知道代理服务器，并不需要知道实际的主服务器位置，以此来简化复制环节中更换故障主服务器的过程。

从服务器只知道中间服务器，也就是代理层。因此将一个现有的从库提升到主只涉及到从服务器和中间主服务器，一旦中间服务器更改完成，从站便是新的主节点。但是需要将中间服务器设置为新服务器日志中的正确的点，才可以继续复制

但是，如果中间服务器本身发生故障，就会回到开始的问题。如果用多个中间服务器有会出现二进制日志不能一致的问题。还有一个问题是，如果中间节点故障，从节点是不能从故障节点转移到另外一个新的中间主节点,也就是说如果你双主，则会被认为是从

maxscale接受主服务器的二进制日志缓存到本地，并将二进制日志提供给从服务器。这就意味着从站始终获得与主站写入的二进制事件相关的二进制日志。并与从站并行保持。

事实上上面一大堆文字都是翻译的(有误处请指正)，我本想它是可以这样实现的，如下：maxscale不单单是binlog-server而且还是读写分离，借鉴：https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing文章中说是测试版本，我测试发现不行，只能做为binlog-server使用，有些扯犊子

参考：

参考：http://maxscale.readthedocs.io/en/stable/Documentation/Routers/Binlogrouter/
      https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-as-a-binlog-server/#binlog-router-compatibility
参考：https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing

1，maxscale安装

[root@Linuxea-VM-Node145 ~]# wget https://downloaLinuxea.mariadb.com/MaxScale/2.2.6/rhel/7/x86_64/maxscale-2.2.6-1.rhel.7.x86_64.rpm
[root@Linuxea-VM-Node145 ~]# yum localinstall maxscale-2.2.6-1.rhel.7.x86_64.rpm
[root@Linuxea-VM-Node145 ~]# mkdir /data/maxscale/data -p
[root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/logs/trace/
[root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/cache/
[root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/{data,cache}
[root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/data
[root@Linuxea-VM-Node145 ~]# maxpasswd /data/maxscale/data password
63B4C3058D940CD68A6E8E0E11ABE2D7
[root@Linuxea-VM-Node145 ~]# chown -R maxscale.maxscale /data/maxscale/

2，在主库上授权

这里授权的是maxscale的用户，作为链接后端主机的用户，后面在创建一个用户作为主从同步，也可以使用此用户

GRANT SELECT  ON *.* TO 'maxscale'@'%' identified by 'password';  
GRANT SHOW DATABASES ON *.* TO maxscale@'%';
GRANT REPLICATION SLAVE on *.* to maxscale@'%';
GRANT REPLICATION CLIENT on *.* to maxscale@'%';
flush privileges;

slave用户作为复制用户

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';
flush privileges;

主节点：

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       467 |
+------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]>

3,在maxscale创建配置文件

[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306
Welcome to the MariaDB monitor.  CommanLinuxea end with ; or \g.
Your MySQL connection id is 4
Server version: 10.2.12 2.2.6-maxscale

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
ERROR 1234 (42000): Can not set MASTER_LOG_POS to 467: Permitted binlog pos is 4. Specified master_log_file=mysql-bin.000001
【这里MASTER_LOG_POS=4设置成4即可】
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

此时目录下产生master.ini

[root@Linuxea-VM-Node145 /data/maxscale]# ll
总用量 20
drwxr-xr-x  2 maxscale maxscale 4096 6月   8 21:50 cache
drwxr-xr-x 13 maxscale maxscale 4096 6月   9 09:23 data
-rw-r--r--  1 maxscale maxscale 4096 6月   9 09:25 gtid_maps.db
drwxr-xr-x  3 maxscale maxscale   37 6月   8 20:21 logs
-rw-------  1 maxscale maxscale  177 6月   9 09:25 master.ini
-rw-r--r--  1 maxscale maxscale  467 6月   9 09:25 mysql-bin.000001

(可以直接用手撸一串配置)

[root@Linuxea-VM-Node145 /data/maxscale]# cat master.ini 
[binlog_configuration]
master_host=10.10.240.202
master_port=3306
master_user=slave
master_password=password
filestem=mysql-bin
master_heartbeat_period=2
master_connect_retry=5
[root@Linuxea-VM-Node145 /data/maxscale]#

4,从库同步

(两个从库都同步，同步的Ip是maxsclae blog的IP地址)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.240.145
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 467
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 535
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5，查看节点信息

[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
binlog_router_master_host | 10.10.240.202   |  3306 |           1 | Running
server1            | 10.10.240.202   |  3306 |           0 | Master, Running
server2            | 10.10.240.203   |  3306 |           0 | Slave, Running
server3            | 10.10.240.146   |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

节点详情查看

[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show servers|egrep "Server|Status"
Server 0xf95420 (binlog_router_master_host)
    Server:                              10.10.240.202
    Status:                              Running
    Server Version:                      10.0.33-MariaDB-wsrep
Server 0xf5dc60 (server1)
    Server:                              10.10.240.202
    Status:                              Master, Running
    Server Version:                      10.0.33-MariaDB-wsrep
    Server Parameters:
Server 0xf5c840 (server2)
    Server:                              10.10.240.203
    Status:                              Slave, Running
    Server Version:                      10.0.33-MariaDB-wsrep
    Server Parameters:
Server 0xf5b420 (server3)
    Server:                              10.10.240.146
    Status:                              Slave, Running
    Server Version:                      10.0.33-MariaDB-wsrep
    Server Parameters:
[root@Linuxea-VM-Node145 /data/maxscale]#

在maxscale上查看从节点信息

[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306
Welcome to the MariaDB monitor.  CommanLinuxea end with ; or \g.
Your MySQL connection id is 4
Server version: 10.2.12 2.2.6-maxscale

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show slave hosts;
+-----------+------+------+-----------+------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+------------+
| 4         |      | 3306 | 100       |            |
| 2         |      | 3306 | 100       |            |
+-----------+------+------+-----------+------------+
2 rows in set (0.00 sec)

6，添加机器

添加一台机器就如第4部分同步从节点一样，备份到新从节点，而后接入进来

7，模拟一次切换

登陆maxscale

[root@Linuxea-VM-Node145 /data/maxscale]# mysql -uslave -ppassword -h10.10.240.145 -P3306 
Welcome to the MariaDB monitor.  CommanLinuxea end with ; or \g.
Your MySQL connection id is 14
Server version: 10.2.12 2.2.6-maxscale MariaDB Server, wsrep_25.21.rc3fc46e

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
ERROR 1234 (42000): Can not set MASTER_LOG_POS to 1195: Permitted binlog pos is 1340. Current master_log_file=mysql-bin.000001, master_log_pos=1340
这里会提示pos节点
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1340,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.09 sec)

在将新节点加入进来

[root@Linuxea-VM-Node202 ~]# mysql -uroot -plookback
Welcome to the MariaDB monitor.  CommanLinuxea end with ; or \g.
Your MariaDB connection id is 86
Server version: 10.0.33-MariaDB-wsrep MariaDB Server, wsrep_25.21.rc3fc46e

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.240.145
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1340
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 680
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在查看节点数

[root@Linuxea-VM-Node145 ~]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show services|grep Slave_host_port
        Slave_host_port:                         [10.10.240.202]:21694
        Slave_host_port:                         [10.10.240.145]:27544
        Slave_host_port:                         [10.10.240.203]:2640
        Slave_host_port:                         [10.10.240.146]:51492

MaxScale binlog server2.2.6与读写分离失败实践

1，maxscale安装

2，在主库上授权

3,在maxscale创建配置文件

4,从库同步

5，查看节点信息

6，添加机器

7，模拟一次切换

相关文章

发布评论取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

MaxScale binlog server2.2.6与读写分离失败实践

1，maxscale安装

2，在主库上授权

3,在maxscale创建配置文件

4,从库同步

5，查看节点信息

6，添加机器

7，模拟一次切换

相关文章

发布评论 取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

发布评论取消回复