Maxscale-充当Binlog Server(4)

HH MySQLMaxscale-充当Binlog Server(4)已关闭评论13,0716字数 11328阅读37分45秒阅读模式

前言

有兴趣的朋友可以看看Binlog Server的作用架构的Blog:http://blog.booking.com/mysql_slave_scaling_and_more.html

个人认为 Maxscale 作为Binlog Server的想法是非常好的,但是还是不够灵活:文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

  1. 需要Master的所有Binlog文件从 mysql-bin.000001 到最后都有。
  2. 还不支持GTID。
  3. 如果是双master,Maxscale会认为两个都是slave,需要用手动去切换。

配置

这边就演示一下配置和使用,我们将Maxscale的Binlog文件放在/u01/maxscale/logs/binlog/目录下文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

  1. /etc/maxscale.cnf 主要配置
[Replication]
type=service
router=binlogrouter
version_string=5.6.31-77.0-log
user=maxscale
passwd=8ADDE5625C666B83FB6774159C51423D
router_options=server_id=5308001233,user=maxscale,password=123456,master_id=5308001233,heartbeat=30,binlogdir=/u01/maxscale/logs/binlog/,transaction_safety=1,master_version=5.6.31-77.0-log,master_hostname=192.168.1.233,mariadb10-compatibility=1

[Replication Listener]
type=listener
service=Replication
protocol=MySQLClient
port=5308

注意:上面router_options配置的都是Maxscale作为Binlog Server的配置,不是指定哪个Master的配置文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

  1. master.ini 文件

该文件主要记录了Maxscale的Binlog是从哪个Master传输过来的文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

[root@normal_11 tmp]# cat /u01/maxscale/logs/binlog/master.ini
[binlog_configuration]
master_host=192.168.137.21
master_port=3306
master_user=maxscale
master_password=123456
filestem=mysql-bin
  1. 完整的配置文件
[root@normal_11 tmp]# cat /etc/maxscale.cnf
###################################################
# CREATE USER maxscale@'%' IDENTIFIED BY "123456";
# GRANT replication slave, replication client ON *.* TO maxscale@'%';
# GRANT SELECT ON mysql.* TO maxscale@'%';
# GRANT ALL ON maxscale_schema.* TO maxscale@'%';
# GRANT SHOW DATABASES ON *.* TO maxscale@'%';
# groupadd maxscale
# useradd -g maxscale maxscale
# cd /opt
# tar -zxf maxscale-2.0.1.rhel.7.tar.gz
# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale
# chown -R maxscale:maxscale /usr/local/maxscale
# mkdir -p /u01/maxscale/{data,cache,logs,tmp}
# mkdir -p /u01/maxscale/logs/{binlog,trace}
# chown -R maxscale:maxscale /u01/maxscale
# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/
# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456
###################################################
[maxscale]
# 开启线程个数,默认为1.设置为auto会同cpu核数相同
threads=auto
# timestamp精度
ms_timestamp=1
# 将日志写入到syslog中
syslog=1
# 将日志写入到maxscale的日志文件中
maxlog=1
# 不将日志写入到共享缓存中,开启debug模式时可打开加快速度
log_to_shm=0
# 记录告警信息
log_warning=1
# 记录notice
log_notice=1
# 记录info
log_info=1
# 不打开debug模式
log_debug=0
# 日志递增
log_augmentation=1
 
# 相关目录设置
basedir=/usr/local/maxscale/
logdir=/u01/maxscale/logs/trace/
datadir=/u01/maxscale/data/
cachedir=/u01/maxscale/cache/
piddir=/u01/maxscale/tmp/
 
[server1]
type=server
address=192.168.137.21
port=3306
protocol=MySQLBackend
serv_weight=1
 
[server2]
type=server
address=192.168.137.22
port=3306
protocol=MySQLBackend
serv_weight=3
 
[server3]
type=server
address=192.168.137.23
port=3306
protocol=MySQLBackend
serv_weight=3
 
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时,maxscale仍然可用,将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟,可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true
 
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
router_options=slave
# 允许root用户登录执行
enable_root_user=1
# 查询权重
weightby=serv_weight
filters=dbfw-blacklist
 
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s)
max_slave_replication_lag=3600
filters=Hint
 
[Replication]
type=service
router=binlogrouter
version_string=10.1.8-MariaDB-log
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
router_options=server_id=5308137011,user=maxscale,password=123456,master_id=5308137011,heartbeat=30,binlogdir=/u01/maxscale/logs/binlog/,transaction_safety=1,master_version=10.1.8-MariaDB-log,master_hostname=192.168.137.11,mariadb10-compatibility=1
 
[MaxAdmin Service]
type=service
router=cli
 
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
 
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
 
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=/u01/maxscale/tmp/maxadmin.sock
port=6603
 
[Replication Listener]
type=listener
service=Replication
protocol=MySQLClient
port=5308
  1. 启动Maxscale查看日志以
2016-11-05 15:46:35.223   info   : (createInstance): Replication: /u01/maxscale/logs/binlog//master.ini parse result is 0
2016-11-05 15:46:35.229   info   : (get_users): Replication: User maxscale@% for database maxscale_schema added to service user table.
2016-11-05 15:46:35.229   info   : (get_users): Replication: User maxscale@% for database mysql added to service user table.
2016-11-05 15:46:35.229   info   : (get_users): Replication: User HH@% for database ANY added to service user table.
2016-11-05 15:46:35.231   info   : (createInstance): Replication: Service has transaction safety option set to ON
2016-11-05 15:46:35.231   notice : (createInstance): Validating binlog file 'mysql-bin.000003' ...
2016-11-05 15:46:35.233   notice : (blr_print_binlog_details): 1478311475 @ 249, GTID List Event, (Sat Nov  5 10:04:35 2016), First EventTime
2016-11-05 15:46:35.234   notice : (blr_print_binlog_details): 1478317696 @ 66056, Query Event, (Sat Nov  5 11:48:16 2016), Last EventTime
2016-11-05 15:46:35.235   notice : (blr_read_events_all_events): Transaction Summary for binlog 'mysql-bin.000003'
                        Description                    Total          Average              Max
                        No. of Transactions               86
                        No. of Events                    258              3.0                3
                        No. of Bytes                   15.0k           179.0B           179.0B
2016-11-05 15:46:35.236   info   : (createInstance): Current binlog file is mysql-bin.000003, safe pos 66125, current pos is 66125 
2016-11-05 15:46:35.236   info   : (session_alloc): Started session [0] for Replication service 
2016-11-05 15:46:35.237   notice : (load_module): Loaded module MySQLBackend: V2.0.0 from /usr/local/maxscale/lib/maxscale/libMySQLBackend.so
2016-11-05 15:46:35.238   notice : (blr_start_master): Replication: attempting to connect to master server 192.168.137.21:3306, binlog mysql-bin.000003, pos 66125
2016-11-05 15:46:35.238   notice : (dcb_listen): Listening connections at 0.0.0.0:5308 with protocol MySQL
2016-11-05 15:46:35.239   info   : (session_alloc): Started session [0] for Replication service 
2016-11-05 15:46:35.239   info   : (ModuleInit): Initialise MaxScaled Protocol module.
2016-11-05 15:46:35.239   notice : (load_module): Loaded module maxscaled: V2.0.0 from /usr/local/maxscale/lib/maxscale/libmaxscaled.so
2016-11-05 15:46:35.240   notice : (dcb_listen): Listening connections at 0.0.0.0:6603 with protocol MaxScale Admin
2016-11-05 15:46:35.243   info   : (session_alloc): Started session [0] for MaxAdmin Service service 
2016-11-05 15:46:35.243   notice : (dcb_listen): Listening connections at /u01/maxscale/tmp/maxadmin.sock with protocol MaxScale Admin
2016-11-05 15:46:35.244   info   : (session_alloc): Started session [0] for MaxAdmin Service service 
2016-11-05 15:46:35.244   notice : (main): MaxScale started with 1 server threads.
2016-11-05 15:46:35.246   notice : (log_flush_cb): Started MaxScale log flusher.
2016-11-05 15:46:35.258   notice : (blr_master_response): Replication: Request binlog records from mysql-bin.000003 at position 66125 from master server 192.168.137.21:3306
2016-11-05 15:46:35.259   notice : (blr_log_identity): Replication: identity seen by the master: server_id: 1013169715, uuid: f9dbc544-a32b-11e6-ba0d-080027e7d114
2016-11-05 15:46:35.259   notice : (blr_log_identity): Replication: identity seen by the slaves: server_id: 1013169715, hostname: 192.168.137.11, MySQL version: 10.1.8-MariaDB-log
  1. 查看Binlog Server作为slave的状态
[root@normal_11 tmp]# mysql -umaxscale -p123456 -h192.168.137.11 -P5308
Logging to file '/u01/mysql_history/query.log'
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 18874
Server version: 5.5.5-10.1.8-MariaDB-log MariaDB Server
 
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, 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.
 
maxscale@192.168.137.11 03:47:50 [(none)]>show master status;
+------------------+----------+--------------+------------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Execute_Gtid_Set |
+------------------+----------+--------------+------------------+------------------+
| mysql-bin.000003 | 82190    |              |                  |                  |
+------------------+----------+--------------+------------------+------------------+
1 row in set (0.00 sec)
 
maxscale@192.168.137.11 03:48:03 [(none)]>show slave status \G 
*************************** 1. row ***************************
               Slave_IO_State: Binlog Dump
                  Master_Host: 192.168.137.21
                  Master_User: maxscale
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 82190
               Relay_Log_File: mysql-bin.000003
                Relay_Log_Pos: 82190
        Relay_Master_Log_File: mysql-bin.000003
             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: 82190
              Relay_Log_Space: 82190
              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: 1013169715
                  Master_UUID: f9dbc544-a32b-11e6-ba0d-080027e7d114
             Master_Info_File: /u01/maxscale/logs/binlog//master.ini
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave running
           Master_Retry_Count: 1000
                  Master_Bind: 
      Last_IO_Error_TimeStamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 
1 row in set (0.00 sec)

这边复制的Binlog位点永远是和Master一样的。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

  1. 停止 [server3]192.168.137.23 slave 转成是 Maxscale的Slave
root@(none) 15:51:18>show global variables like 'server_id';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| server_id     | 3306137023 |
+---------------+------------+
1 row in set (0.00 sec)
 
root@(none) 15:50:38>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
root@(none) 15:50:45>reset slave;
Query OK, 0 rows affected (0.00 sec)
 
root@(none) 15:51:23>CHANGE MASTER TO
    ->     MASTER_HOST='192.168.137.21',
    ->     MASTER_USER='maxscale',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_PORT=5308,
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=82190;
Query OK, 0 rows affected (0.01 sec)
 
root@(none) 15:55:24>start slave;
Query OK, 0 rows affected (0.00 sec)
 
root@(none) 15:55:45>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.137.11
                  Master_User: maxscale
                  Master_Port: 5308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 82190
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 541
        Relay_Master_Log_File: mysql-bin.000003
             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: 82190
              Relay_Log_Space: 841
              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: 1013169715
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)

哈哈这样就成功的让 [server3]变成了[maxscale] 的slave节点了文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

昵称: HH文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

QQ: 275258836文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207(新) 微信公众号: ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

感觉本文内容不错,读后有收获?文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

逛逛衣服店,鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
HH
  • 本文由 发表于 10/12/2016 00:56:43
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/maxscale-became-binlog-server/