mysql主主复制+amoeba框架的搭建
一、环境说明
三台win7虚拟机 mysql-installer-community-5.7.16.0
jdk1.8 amoeba-mysql-binary-2.2.0
ip:10.1.23.198 (安装mysql和jdk)
ip:10.1.23.154 (安装mysql和jdk)
ip:10.1.23.155 (安装mysql客户端和amoeba、jdk)
二、主主复制(要关闭防火墙,3台机器都可以相互ping通)
(mysql_A:3306host:10.1.23.198,mysql_B:3307host:10.1.23.154)
1)、分别修改mysql的配置文件开启binlog:
ip:10.1.23.198修改mysql配置文件my.ini(在C:\ProgramData\MySQL\MySQLServer5.7)
(ProgramData在C盘的隐藏文件夹)
添加配置:server-id= 1
binlog_checksum=none #如果两个mysql的版本不一样则加入此行
log-bin=C:\whw\Mysql-binlog\mysql-bin
replicate-do-db= world //指定数据库(可写可不写)
auto-increment-increment=2 # 应设为整个结构中服务器的总数
auto-increment-offset= 1 # 设定数据库中自动增长的起点,避免两台服务器数据同步时出现主键冲突
ip:10.1.23.154 (在my.ini中修改其端口号为3307)
server-id= 2
log-bin=C:\whw\Mysql-binlog\mysql-bin
replicate-do-db= world //指定数据库(可写可不写)
auto-increment-increment=2
auto-increment-offset= 2
2)、mysql_B指定mysql_A为主库
(1)在10.1.23.198(主库)中创建一个10.1.23.154(从库)中可以登录的
MySQL用户,并赋予权限。
GRANTREPLICATION SLAVE,RELOAD,SUPER ON *.*TOwhx@'10.1.%'IDENTIFIEDBY '123456';
FLUSHPRIVILEGES; 刷新权限
(用户名whx密码123456赋予权限:可供10.1.%的客户端实现远程登录)
(2)在主库中查询当前主库的状态
RESETMASTER show MASTER STATUS
显示如下:file:mysql-bin.000001
position:154
(3)mysql_B链接主库mysql_A
stopSLAVE (在10.1.23.154(从库)中)
#这里是要连接主库(mysql_A)的端口号,默认是3306,
CHANGEMASTER TO master_host = '10.1.23.198',
master_user= 'whx',
master_password= '123456',
master_log_file= 'mysql-bin.000001',
master_log_pos= 154;
(4)查看当前是否连接成功
STARTSLAVE (开启slave) showSLAVE STATUS \G;
如果上图中的两个yes存在则成功(必须2个都为yes)
slave_lo_running:yes
slave_sql_running:yes
3)、mysql_A指定mysql_B为主库
(1)在10.1.23.154(主库)中创建一个10.1.23.198(从库)中可以登录的MySQL用户,并赋予权限。
GRANTREPLICATION SLAVE,RELOAD,SUPER ON *.*TO whx@'10.1.%'IDENTIFIED BY'123456';
FLUSHPRIVILEGES; 刷新权限
(用户名whx密码123456赋予权限:可供10.1.%的客户端实现远程登录)
(2)在主库中查询当前主库的状态
RESETMASTER show MASTER STATUS
显示如下:file:mysql-bin.000001
position:154
(3)mysql_A链接主库mysql_B
stopSLAVE (在10.1.23.198(从库)中)
这里是要连接主库(mysql_B)的端口号,默认是3306,如果你的主库端口不是3306则要加上这一行
CHANGEMASTER TO master_host = '10.1.23.198',
master_user= 'whx',
master_port=3307, // mysql_B的端口号
master_password= '123456',
master_log_file= 'mysql-bin.000001',
master_log_pos= 154;
(4)查看当前是否连接成功
STARTSLAVE (开启slave) showSLAVE STATUS \G;
如果上图中的两个yes存在则成功(必须2个都为yes)
slave_lo_running:yes
slave_sql_running:yes
只要mysql_A、mysql_B都出现上面2个yes,说明主主搭配成功。
三、amoeba框架的搭建
1)、amoeba的下载及解压
打开cmd,进入amoeba的bin目录,运行输入命令amoebastart
出现这代表安装成功
2)、amoeba的配置
conf文件夹下的amoeba.xml
<?xmlversion="1.0" encoding="gbk"?>
<!DOCTYPEamoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configurationxmlns:amoeba="http://amoeba.meidusa.com/">
<proxy>
<!--service class must implements com.meidusa.amoeba.service.Service -->
<servicename="Amoeba for Mysql"class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!--port --> 2)、amoeba的配置
conf文件夹下的amoeba.xml
<?xmlversion="1.0" encoding="gbk"?>
<!DOCTYPEamoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configurationxmlns:amoeba="http://amoeba.meidusa.com/">
<proxy>
<!--service class must implements com.meidusa.amoeba.service.Service -->
<servicename="Amoeba for Mysql"class="com.meidusa.amoeba.net.ServerableConnect
<propertyname="port">8066</property>
<!--bind ipAddress -->
<!-- →
//安装amoeba的ip地址
<propertyname="ipAddress">10.1.23.155</property>
<propertyname="manager">${clientConnectioneManager}</property>
<propertyname="connectionFactory">
<beanclass="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<propertyname="sendBufferSize">128</property>
<propertyname="receiveBufferSize">64</property>
</bean>
</property>
<propertyname="authenticator">
<beanclass="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
//通过amoeba进行登录是的用户名和密码
<propertyname="user">root</property>
<propertyname="password">123456</property>
<propertyname="filter">
<beanclass="com.meidusa.amoeba.server.IPAccessController">
<propertyname="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
</service>
<!--server class must implements com.meidusa.amoeba.service.Service -->
<servicename="Amoeba Monitor Server"class="com.meidusa.amoeba.monitor.MonitorServer">
<!--port -->
<!-- default value: random number
<propertyname="port">9066</property>
-->
<!--bind ipAddress -->
<propertyname="ipAddress">127.0.0.1</property>
<propertyname="daemon">true</property>
<propertyname="manager">${clientConnectioneManager}</property>
<propertyname="connectionFactory">
<beanclass="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
</property>
</service>
<runtimeclass="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
<!--proxy server net IO Read thread size -->
<propertyname="readThreadPoolSize">20</property>
<!--proxy server client process thread size -->
<propertyname="clientSideThreadPoolSize">30</property>
<!--mysql server data packet process thread size -->
<propertyname="serverSideThreadPoolSize">30</property>
<!--per connection cache prepared statement size -->
<propertyname="statementCacheSize">500</property>
<!--query timeout( default: 60 second , TimeUnit:second) -->
<propertyname="queryTimeout">60</property>
</runtime>
</proxy>
<!--
EachConnectionManager will start as thread
managerresponsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
<connectionManagername="clientConnectioneManager"class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<propertyname="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
<!--
default value is avaliable Processors
<propertyname="processors">5</property>
-->
</connectionManager>
<connectionManagername="defaultManager"class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<propertyname="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
<!--
default value is avaliable Processors
<propertyname="processors">5</property>
-->
</connectionManager>
</connectionManagerList>
<!--default using file loader -->
<dbServerLoaderclass="com.meidusa.amoeba.context.DBServerConfigFileLoader">
<propertyname="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>
<queryRouterclass="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<propertyname="ruleLoader">
<beanclass="com.meidusa.amoeba.route.TableRuleFileLoader">
<propertyname="ruleFile">${amoeba.home}/conf/rule.xml</property>
<propertyname="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<propertyname="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<propertyname="LRUMapSize">1500</property>
<propertyname="defaultPool">master1</property>
<propertyname="writePool">master1</property>
<propertyname="readPool">master2</property>
<propertyname="needParse">true</property>
</queryRouter>
</amoeba:configuration>
conf文件夹下的dbServers.xml
<?xmlversion="1.0" encoding="gbk"?>
<!DOCTYPEamoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServersxmlns:amoeba="http://amoeba.meidusa.com/">
<dbServername="abstractServer1"abstractive="true">
<factoryConfigclass="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<propertyname="manager">${defaultManager}</property>
<propertyname="sendBufferSize">64</property>
<propertyname="receiveBufferSize">128</property>
//配置所要连接的DBserver的用户名和密码,及所连接的数据库和其相应的端口号
<!--mysql port -->
<propertyname="port">3307</property>
<!--mysql schema -->
<propertyname="schema">world</property>
<!--mysql user -->
<propertyname="user">whx</property>
<!-- mysql password -->
<propertyname="password">123456</property>
</factoryConfig>
<poolConfigclass="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<propertyname="maxActive">500</property>
<propertyname="maxIdle">500</property>
<propertyname="minIdle">10</property>
<propertyname="minEvictableIdleTimeMillis">600000</property>
<propertyname="timeBetweenEvictionRunsMillis">600000</property>
<propertyname="testOnBorrow">true</property>
<propertyname="testOnReturn">true</property>
<propertyname="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<dbServername="server1" parent="abstractServer1">
<factoryConfig>
<!--mysql ip -->
<propertyname="ipAddress">10.1.23.154</property>
</factoryConfig>
</dbServer>
<!--这后面是server2的配置,复制上面的内容,server1的-->
<dbServername="abstractServer2"abstractive="true">
<factoryConfigclass="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<propertyname="manager">${defaultManager}</property>
<propertyname="sendBufferSize">64</property>
<propertyname="receiveBufferSize">128</property>
<!--mysql port -->
<propertyname="port">3306</property>
<!--mysql schema -->
<propertyname="schema">world</property>
<!--mysql user -->
<propertyname="user">whx</property>
<!-- mysql password-->
<propertyname="password">123456</property>
</factoryConfig>
<poolConfigclass="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<propertyname="maxActive">500</property>
<propertyname="maxIdle">500</property>
<propertyname="minIdle">10</property>
<propertyname="minEvictableIdleTimeMillis">600000</property>
<propertyname="timeBetweenEvictionRunsMillis">600000</property>
<propertyname="testOnBorrow">true</property>
<propertyname="testOnReturn">true</property>
<propertyname="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<dbServername="server2" parent="abstractServer2">
<factoryConfig>
<!--mysql ip -->
<propertyname="ipAddress">10.1.23.198</property>
</factoryConfig>
</dbServer>
<dbServername="master1"virtual="true">
<poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">
<propertyname="loadbalance">1</property>
<propertyname="poolNames">server1,server2</property>
</poolConfig>
</dbServer>
<dbServername="master2"virtual="true">
<poolConfigclass="com.meidusa.amoeba.server.MultipleServerPool">
<propertyname="loadbalance">1</property>
<propertyname="poolNames">server1,server2</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>
四、amoeba启动与其测试
1)、测试其是否可以实现远程登录
mysql-uwhx -p123456 -h 10.1.23.154 -P3307
mysql-uwhx -p123456 -h 10.1.23.198
如可以
2)、输入mysql-uroot -h 10.1.23.155 -P8066 -p123456 登录amoeba,如可正常登录
3)、可对数据库进行操作
在10.1.23.198上的mysql创建数据库
createdatabase data2;
在存在amoeba的mysql客户端:showdatabases; 如下图
关闭10.1.23.154上的msyql的服务器
在存在amoeba的mysql客户端:showdatabases; 如下图,则搭建成功。