Maxscale-充当Binlog Server(4)

前言

有兴趣的朋友可以看看Binlog Server的作用架构

Maxscale 作为Binlog Server的想法是非常好的，但是还是不够灵活：

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

配置

这边就演示一下配置和使用，我们将Maxscale的Binlog文件放在/u01/maxscale/logs/binlog/目录下

1. /etc/maxscale.cnf 主要配置

1 2 3 4 5 6 7 8 9 10 11 12 13

[ 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的配置

1. master.ini 文件

该文件主要记录了Maxscale的Binlog是从哪个Master传输过来的

1 2 3 4 5 6 7

[ 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. 完整的配置文件

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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146

[ 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查看日志以

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

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的状态

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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

[ 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一样的。

1. 停止 [server3]192.168.137.23 slave 转成是 Maxscale的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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

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 )

 

 

文章转载来自：trustauth.cn