This article is mainly to share with you the configuration process of mysql multi-master multi-slave, theory part less, you need to understand the principle of mysql master-slave, myCAT middleware, ok, no more talk, let’s start.

  • The preparatory work

    When looking at the following configuration steps, you’d better read the following article carefully first to better understand the following operation steps

    Mysql – Primary/secondary replication

    Mysql – Multithreaded replication

    Mycat – Environment installation

  • Presentation Environment

    -centos7-mysql8-192.168.188.159 (master-1) -centos7-mysql8-192.168.188.156 (master-2) -centos7-mysql8 - - Centos7-mysql8-192.168.188.156 (master-2) -centos7-mysql8 - 192.168.188.160 (slave-1) -centos7-jdk1.7-192.168.188.158 (mycat-vip)Copy the code

  • The configuration steps

    # configure primary server (1) CNF gtid_mode = on # enabling enforce_gtid_consistency = on # Enforcing GTID consistency Auto_increment_offset = 1 # Self-increasing the starting value of the primary key Auto_increment_increment = 2 # server id = 1 # server id = 1 # Innodb_log_file_size = 512M # Log size Innodb_log_buffer_size =. Innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 0 # synchronization mode # another primary server is configured the same, only need to change the server-id = 2 #, this must be unique in the cluster, Auto_increment_offset = 2; auto_increment_increment = 2; auto_increment_increment = 2; Mysql > create user '@' identified by 'password '; Mysql > grant replication slave on database name (*= all). Table name (*= all) to 'account '@' server IP'; mysql> flush privileges; GTID mysql> show master status; # Executed_Gtid_Set b6AC33e3-7994-11ea-8c39-000c29474b11:1-1 then ID is 1 # configure slave server (3) : Modify /etc/my. CNF gtid_mode = on enforce_gtid_consistency = on master_info_repository = table relay_log_info_repository = Table slave-skip-errors = all server-id = 3 (4): mysql> stop slave; mysql> reset master all; Mysql > change master to master_host='192.168.188.159',master_user=' account ',master_password=' password ',master_auto_position=ID for channel '1'; Mysql > change master to master_host='192.168.188.156',master_user=' account ',master_password=' password ',master_auto_position=ID for channel '2'; mysql> start slave; Mysql > show slave status\G; # configure routing server (mycat) # configure routing server (mycat) Modify mycat system configuration information (/ usr/local/mycat/conf/server XML) <! DOCTYPE mycat:server SYSTEM "server.dtd"> <! -- 0 indicates that password login is required, and 1 indicates that password login is not required. The default value is 0. <property name="nonePasswordLogin">0</property> < Property name="useHandshakeV10">1</property> <! <property name="useSqlStat">0</property> <! -- 1: enable the consistency check for full working hours, 0: disable --> <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property> <! -- If the sub-query contains associated query, check whether the associated field contains fragment fields. The default false - > < property name = "subqueryRelationshipCheck" > false < / property > <! -- <property name="useCompression">1</property>--> <! --1 to enable mysql compression protocol --> <! -- <property name="processorBufferChunk">40960</property> --> <! -- <property name="processors">1</property> --> <! -- <property name="processorExecutor">32</property> --> <property name="processorBufferPoolType">0</property> <! -- Default is 65535 64K maximum text length for SQL parsing --> <! -- <property name="maxStringLiteralLength">65535</property> --> <property name="sequnceHandlerType">0</property> <! -- <property name="backSocketNoDelay">1</property> --> <! -- <property name="frontSocketNoDelay">1</property> --> <! -- <property name="processorExecutor">16</property> --> <property name="serverPort">3308</property> <property name="managerPort">9066</property> <! -- <property name="idleTimeout">300000</property> --> <! - < property name = "bindIp" > 0.0.0.0 < / property > -- > <! -- <property name="frontWriteQueueSize">4096</property> --> <! -- <property name="processors">32</property> --> <! - handleDistributedTransactions 0 for distributed transaction switches don't filter distributed transaction 1 for distributed transaction (if a distributed transaction involves only within the global table, Not filter) for not filter 2 is a distributed transaction, but distributed transaction log records - > < property name = "handleDistributedTransactions" > 0 < / property > <! -- off heap for merge/order/group/limit 1 on 0 off --> <property name="useOffHeapForMerge">1</property> <! <property name="memoryPageSize"> 64K </property> <! <property name="useStreamOutput">0</property> <! < <property name="useStreamOutput">0</property> <! Property name="systemReserveMemorySize"> 384M </property> <! <property name="useZKSwitch">false</property> <! -- XA Recovery Log Log path --> <! --<property name="XARecoveryLogBaseDir">./</property>--> <! -- XA Recovery Log --> <! --<property name="XARecoveryLogBaseName">tmlog</property>--> </system> Schemas = Database name and rule name <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">pvcat</property> </user> <user name="root_2"> <property name="password">root</property> <property Name = "schemas" > pvcat < / property > < / user > < / mycat: server > (6) : modify the rules file (/ usr/local/mycat/conf/schema. The XML) <? The XML version = "1.0"? > <! DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="pvcat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="pvcat" /> <dataHost name="localhost1" maxCon="20000" minCon="10" balance="0" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" Url ="192.168.188.159:3306" user=" Remote account name "password=" Remote password "> <readHost host="hostS1" URL ="192.168.188.160:3306" user=" Remote account name" Password =" Remote password "/> </writeHost> <writeHost host="hostM2" URL ="192.168.188.156:3306" user=" remote account name" password=" remote password "/> < / dataHost > < / mycat: schema > (7) : Start the mycat localhost @ root > / usr/local/mycat/bin/mycat start # everybody try effect with navicat connection database host = 192.168.188.158 port = 3308 user = root password = 123456Copy the code