Maxscale Binlog Server Practice

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/8A/FD/wKioL1hBB47ilTJoAAA4pG6yXEQ736.jpg "title=" icon 2. JPG "alt=" Wkiol1hbb47iltjoaaa4pg6yxeq736.jpg "/>

Maxscale Binlog Server Practice Brief

Part1 : write at the top

In the previous blog post about Maxscale, as a middleware, with MHA use or master-slave use can achieve read and write separation and load balancing, today a brief introduction of Maxscale as Binlog server to reduce the problem of master-slave delay; MySQL's master-slave architecture, The architecture of a chained topology is more prone to master-slave latency issues. This article focuses on how Maxscale as a Binlog server reduces the master-slave delay.

Maxscale with MHA please move to:

http://suifu.blog.51cto.com/9167728/1869520

Part2 :

he1:192.168.1.248 slave

HE3:192.168.1.250 Master

he4:192.168.1.251 Maxscale

Architecture Demo

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8B/01/wKiom1hBDgqiaSonAABgUFOcZ7o048.png "title=" Wkiol1g-cnxrcnmeaadft_c5wik163.png "alt=" Wkiom1hbdgqiasonaabgufocz7o048.png "/>

Actual combat

Part1 : install Maxscale

[Email protected] ~]# yum-y Install maxscale-2.0.1-2.centos.6.x86_64.rpm

[Email protected] ~]# mkdir-p/data/binlog

[Email protected] ~]# Useradd Maxscale

[Email protected] ~]# chown-r Maxscale. /data/binlog

[Email protected] ~]# CAT/ETC/MAXSCALE.CNF

[maxscale]threads=1# #根据CPU核数设置 [replication]type=servicerouter=binlogrouteruser=mysyncpasswd=manager#  Use REPL on the main library to copy accounts #  permissions: #   grant replication slave,replication client on  *.* TO  ' repl ' @ '% '  IDENTIFIED BY    ' repl '; router_options=server_id= 1251,heartbeat=30,binlogdir=/data/binlog,transaction_safety=1,mariadb10-compatibility=1,send_slave_heartbeat=1 # server_id set Maxscale, remember not to and master from the library to repeat, to unique #   heartbeat=30 seconds, This means that when Maxscale has not received the Binlog log of the main library push within 30 seconds, the heartbeat check #   binlogdir set the storage path of the receive Binlog, directory properties chown -r  Maxscale.maxscale   /data/binlog# transaction_safety=1 This parameter is used to enable incomplete transaction detection in the Binlog log.     when Mariadb maxscale is started, an error message may appear if the current Binlog file is corrupted or if an incomplete transaction is found.     during normal operation, the Binlog event is not assigned to the slave library until the transaction has been committed.   The default value is off, set Transaction_safety = on to enable incomplete transaction detection. #   send_slave_heartbeat=1 Open Heartbeat Check [Replication   listener]type=listenerservice=replicationprotocol=mysqlclientport=5308#  backend from the library change   master to this port, Default 5308[cli]type=servicerouter=cli[cli listener]type=listenerservice=cliprotocol=maxscaledport=6603

Part2: Start Maxscale

[[email protected] ~]#/etc/init.d/maxscale start

Starting Maxscale:maxscale (PID 16680) is running ... [OK]

[Email protected] ~]#/etc/init.d/maxscale status

Checking maxscale Status:maxscale (PID 16680) is running. [OK]

from library configuration

[[email protected] ~]# mysql -umysync -pmanager -h192.168.1.251 - P5308welcome to the mariadb monitor.  commands end with ; or  \g.your mysql connection id is 3196server version: 10.0.0 2.0.1- maxscalecopyright  (c)  2000, 2016, 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= ' 192.168.1.250 ', master_user= ' Mysync ', master_password= ' MANAGER ', master_port=3306,master_log_file= ' mysql-bin.000005 ', master_log_pos=20; error 1234  (42000):  can not set master_log_pos to 20: permitted  binlog pos is 4. specified master_log_file=mysql-bin.000005mysql&nbsp [(None)]>  change master to master_host= ' 192.168.1.250 ', master_user= ' Mysync ', Master_password= ' MANAGER ', master_port=3306,master_log_file= ' mysql-bin.000005 ', master_log_pos=4; mysql [(None)]> start slave; query ok, 0 rows affected  (0.00 SEC)

As can be seen here, Maxscale Binlog Server can only be configured from location 4

After configuration, the Binlog file generated under/data/binlog

[Email protected] ~]# cd/data/binlog/

[[Email protected]e4 binlog]# ls

Cache Master.ini mysql-bin.000003

Part2: Main Library Configuration

[[email protected] ~]# mysql -uroot -penter password: welcome to  The mariadb monitor.  commands end with ; or \g.your mariadb  connection id is 7Server version: 10.1.16-MariaDB MariaDB  servercopyright  (c)  2000, 2016, oracle, mariadb corporation ab and  others. type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement. mariadb [(None)]> show master status;+------------------+----------+--------------+---- --------------+| file             |  position | binlog_do_db | binlog_ignore_db |+------------------+----------+---- ----------+------------------+| mysql-bin.000005 |      652 |              |                   |+------------ ------+----------+--------------+------------------+1 row in set  (0.00 sec) MariaDB  [(None)]>  grant replication client,replication slave on *.*  to  ' Mysync ' @ ' 192.168.1.% '  identified by  ' MANAGER '; mariadb [(None)]>flush privileges;

Part3: master-Slave configuration

Point from library to Binlogserver[[email protected] ~]# mysql -uroot -pmanagerwelcome to the  mariadb monitor.  commands end with ; or \g.your mariadb  connection id is 5Server version: 10.1.16-MariaDB MariaDB  servercopyright  (c)  2000, 2016, 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= ' 192.168.1.251 ', master_user= ' Mysync ', master_password= ' MANAGER ', master_port=5308,master_log_file= ' mysql-bin.000005 ', master_log_pos=652; query ok, 0 rows affected  (0.02 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: 192.168.1.251                   Master_User:  mysync                   Master_Port: 5308                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           read_master_log_pos: 652               relay_log_file:  mysql-relay-bin.000002                 relay_log_pos: 537        relay_master_log_file :  mysql-bin.000005             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: 652               Relay_Log_Space: 835               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0           &nbsP Master_ssl_allowed: no           master_ssl_ca_ file:            master_ssl_ca_path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0Master_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: 1250                Master_SSL_Crl:             master_ssl_crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        replicate_do_domain_ids:   replicate_ignore_domain_ids:                  parallel_mode: conservative1 row  in set  (0.00 SEC)

-- summary --

production environment, most of the use of a master multi-slave architecture, such as Star-like topology and Chain topology, star topology in the case of too many from the library, the main library will increase the IO pressure, and the chain topology although the main library of the network IO pressure, but the disadvantage is: Level Two slave get the latest data, A second layer of replication is required before it arrives, and the latency is larger than a master multiple from the schema. The use of Maxscale Binlog server avoids such problems. Because The level of the pen is limited, the writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, the wrong place to ask readers to criticize correct.

This article is from the "He Lei Technology Blog" blog, make sure to keep this source http://suifu.blog.51cto.com/9167728/1878847

Maxscale Binlog Server Practice