前言
有兴趣的朋友可以看看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/
- 需要Master的所有Binlog文件从 mysql-bin.000001 到最后都有。
- 还不支持GTID。
- 如果是双master，Maxscale会认为两个都是slave，需要用手动去切换。
配置
这边就演示一下配置和使用，我们将Maxscale的Binlog文件放在/u01/maxscale/logs/binlog/目录下文章源自运维生存时间-https://www.ttlsa.com/mysql/maxscale-became-binlog-server/
- /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/
- 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
- 完整的配置文件
[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
- 启动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
- 查看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/
- 停止 [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/
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
评论