MySQL5.7.19 GTID master master replication && mycat
MySQL5.7.19 GTID master master replication && mycat
雁南飞丶 发表于6个月前
MySQL5.7.19 GTID master master replication && mycat
  • 发表于 6个月前
  • 阅读 17
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

 

hostname ip
zqdd 192.168.0.171
agent 192.168.0.181

 

 

 

系统环境
root@agent:/root#cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Kernel \r on an \m



wget https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm --no-check-certificate

root@agent:/root#rpm -ivh mysql57-community-release-el6-11.noarch.rpm 
warning: mysql57-community-release-el6-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql57-community-relea########################################### [100%]

root@agent:/etc/yum.repos.d#rpm -ql mysql57-community-release-el6-11.noarch
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo

安装mysql

root@agent:/root#yum install mysql mysql-server
Loaded plugins: product-id, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.19-1.el6 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.19-1.el6.x86_64
---> Package mysql-community-server.x86_64 0:5.7.19-1.el6 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.19-1.el6 for package: mysql-community-server-5.7.19-1.el6.x86_64
--> Running transaction check
---> Package mysql-community-common.x86_64 0:5.7.19-1.el6 will be installed
---> Package mysql-community-libs.x86_64 0:5.7.19-1.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.1.71-1.el6 will be obsoleted
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:5.7.19-1.el6 will be obsoleting
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                                        Arch                      Version                          Repository                            Size
======================================================================================================================================================
Installing:
 mysql-community-client                         x86_64                    5.7.19-1.el6                     mysql57-community                     23 M
 mysql-community-libs                           x86_64                    5.7.19-1.el6                     mysql57-community                    2.1 M
     replacing  mysql-libs.x86_64 5.1.71-1.el6
 mysql-community-libs-compat                    x86_64                    5.7.19-1.el6                     mysql57-community                    1.6 M
     replacing  mysql-libs.x86_64 5.1.71-1.el6
 mysql-community-server                         x86_64                    5.7.19-1.el6                     mysql57-community                    152 M
Installing for dependencies:
 mysql-community-common                         x86_64                    5.7.19-1.el6                     mysql57-community                    328 k

Transaction Summary
======================================================================================================================================================
Install       5 Package(s)

Total download size: 179 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): mysql-community-client-5.7.19-1.el6.x86_64.rpm                                                                          |  23 MB     00:39     
(2/5): mysql-community-common-5.7.19-1.el6.x86_64.rpm                                                                          | 328 kB     00:00     
(3/5): mysql-community-libs-5.7.19-1.el6.x86_64.rpm                                                                            | 2.1 MB     00:02     
(4/5): mysql-community-libs-compat-5.7.19-1.el6.x86_64.rpm                                                                     | 1.6 MB     00:02     
(5/5): mysql-community-server-5.7.19-1.el6.x86_64.rpm                                                                          | 152 MB     07:12     
------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                 383 kB/s | 179 MB     07:57     
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid : MySQL Release Engineering <mysql-build@oss.oracle.com>
 Package: mysql57-community-release-el6-11.noarch (installed)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : mysql-community-common-5.7.19-1.el6.x86_64                                                                                         1/6 
  Installing : mysql-community-libs-5.7.19-1.el6.x86_64                                                                                           2/6 
  Installing : mysql-community-client-5.7.19-1.el6.x86_64                                                                                         3/6 
  Installing : mysql-community-server-5.7.19-1.el6.x86_64                                                                                         4/6 
  Installing : mysql-community-libs-compat-5.7.19-1.el6.x86_64                                                                                    5/6 
  Erasing    : mysql-libs-5.1.71-1.el6.x86_64                                                                                                     6/6 
  Verifying  : mysql-community-server-5.7.19-1.el6.x86_64                                                                                         1/6 
  Verifying  : mysql-community-common-5.7.19-1.el6.x86_64                                                                                         2/6 
  Verifying  : mysql-community-libs-compat-5.7.19-1.el6.x86_64                                                                                    3/6 
  Verifying  : mysql-community-libs-5.7.19-1.el6.x86_64                                                                                           4/6 
  Verifying  : mysql-community-client-5.7.19-1.el6.x86_64                                                                                         5/6 
  Verifying  : mysql-libs-5.1.71-1.el6.x86_64                                                                                                     6/6 

Installed:
  mysql-community-client.x86_64 0:5.7.19-1.el6    mysql-community-libs.x86_64 0:5.7.19-1.el6    mysql-community-libs-compat.x86_64 0:5.7.19-1.el6   
  mysql-community-server.x86_64 0:5.7.19-1.el6   

Dependency Installed:
  mysql-community-common.x86_64 0:5.7.19-1.el6                                                                                                        

Replaced:
  mysql-libs.x86_64 0:5.1.71-1.el6                                                                                                                    

Complete!

授权初始化

chown mysql.mysql /var/lib/mysql

root@agent:/root#mysqld --initialize --datadir=/var/lib/mysql --user=mysql

查看临时密码

grep "temporary password" /var/log/myqsld.log

启动登录

root@agent:/var/lib#/etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
root@agent:/var/lib#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19

Copyright (c) 2000, 2017, 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.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set PASSWORD=PASSWORD('redhat11');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>  SET GLOBAL  validate_password_policy='LOW';   #降低密码策略
Query OK, 0 rows affected (0.08 sec)

mysql> set PASSWORD=PASSWORD('redhat11');
Query OK, 0 rows affected, 1 warning (0.06 sec)

192.168.0.171  my.cnf

[root@zqdd:/root]#grep -v ^# /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 1  
auto_increment_increment=2
auto_increment_offset=1 
log-bin=mysql-bin


replicate-ignore-db = mysql
replicate-ignore-db = sys
gtid_mode = on  
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
log-slave-updates
validate-password=OFF

192.168.0.181 my.cnf

root@agent:/var/lib#grep -v ^# /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
auto_increment_increment=2
auto_increment_offset=2
user=mysql
log-bin=mysql-bin


replicate-ignore-db = mysql 
replicate-ignore-db = sys
gtid_mode = on  
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
log-slave-updates
validate_password=OFF

授权2台MySQL上的复制用户

mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by 'musingtec';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.09 sec)

GTID同步数据不用再记录对方的log文件和位置了,用master_auto_position=1就行,不过你用老的方法查看master的logfile和logpos,同步也是可以的。

mysql> change master to master_host='192.168.0.181', master_user='repl',master_password='musingtec',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.28 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.181
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: zqdd-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,sys
           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: 154
              Relay_Log_Space: 573
              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: 2
                  Master_UUID: cf8a59df-863c-11e7-a879-000c29c2e944
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

mycat

[root@zqdd:/usr/local/mycat/conf]#cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="musingtec" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
	<schema name="jeecg" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"></schema>
	<schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"> </schema>
		<!-- auto sharding by id (long) -->
	<!--		<table name="travelrecord" dataNode="dn1" rule="auto-sharding-long" /> -->
	<!--	<table name="travelrecord" dataNode="dn1"/>
		    <table name="test1" dataNode="dn1"/>
-->
		<!-- global table is auto cloned to all defined data nodes ,so can join
			with any table whose sharding node is in the same data node -->
	<!--	<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> -->
		<!-- random sharding using mod sharind rule -->
	<!--	<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
			   rule="mod-long" /> -->
		<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
			needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
			rule="mod-long" /> -->
	<!--	<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile" />
		<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile">
			<childTable name="orders" primaryKey="ID" joinKey="customer_id"
						parentKey="id">
				<childTable name="order_items" joinKey="order_id"
							parentKey="id" />
			</childTable>
			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
						parentKey="id" />
		</table> -->
		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
			/> -->
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="zqdd" database="musingtec" /> 
	<dataNode name="dn2" dataHost="zqdd" database="jeecg" /> 
	<dataNode name="dn3" dataHost="zqdd" database="testdb" /> 
	<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
	 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
	<dataHost name="zqdd" maxCon="1000" minCon="10" balance="2"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		 <writeHost host="hostM1" url="192.168.0.171:3306" user="root"
			password="redhat1"> 
		</writeHost>
			<!-- can have multi read hosts -->
		<!--	<readHost host="hostS2" url="192.168.0.181:3306" user="root" password="redhat1" /> -->
			<writeHost host="hostS2" url="192.168.0.181:3306" user="root" password="redhat1" />
			<!-- <writeHost host="hostS3" url="192.168.0.85:3306" user="root" password="redhat1" /> -->
            <!--  <writeHost host="hostS1" url="localhost:3316" user="root" 
				   password="123456" /> -->
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<!--
		<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
		<heartbeat> 		</heartbeat>
		 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" 	password="jifeng"></writeHost>
		 </dataHost>

	  <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" 	dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
		<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
		<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" 	password="123456" > </writeHost> </dataHost>

		<dataHost name="jdbchost" maxCon="1000" 	minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
		<heartbeat>select 	user()</heartbeat>
		<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

		<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
		<heartbeat> </heartbeat>
		 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" 	password="jifeng"></writeHost> </dataHost> -->

	<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
		dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
		url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
		</dataHost> -->
</mycat:schema>

server.xml

[root@zqdd:/usr/local/mycat/conf]#cat server.xml 
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

		<property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
		<property name="processorBufferPoolType">0</property>
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<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">8066</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> -->
		<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		
			<!--
			off heap for merge/order/group/limit      1开启   0关闭
		-->
		<property name="useOffHeapForMerge">1</property>

		<!--
			单位为m
		-->
		<property name="memoryPageSize">1m</property>

		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>

		<property name="useStreamOutput">0</property>

		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">true</property>


	</system>
	
	<!-- 全局SQL防火墙设置 -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->
	
	<user name="root">
		<property name="password">redhat1</property>
		<property name="schemas">musingtec,jeecg,testdb</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">redhat1</property>
		<property name="schemas">musingtec,jeecg,testdb</property>
		<property name="readOnly">true</property>
	</user>

</mycat:server>

 

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 37
博文 120
码字总数 213456
×
雁南飞丶
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: