mysql主从+读写

开启主库binlog,从库不用开,配置主从库server-id(不能相同)

yum -y install mysql mysql-server  vim /etc/my.cnf  [mysqld] server-id=1 log-bin=mysql-bin  /etc/init.d/mysqld restart

主库创建同步的用户

mysql -uroot -p123456
mysql> grant replication slave on *.* to ‘rep‘@‘10.0.0.%‘ identified by ‘123456‘; mysql> flush privileges;

查看主服务器状态

#记录下日志文件名和位置，备用 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 467 | | | | +------------------+----------+--------------+------------------+-------------------+  或者 mysql> show master status\G

配置mysql从服务器

yum -y install mysql mysql-server  vim /etc/my.cnf  [mysqld] server-id=2 relay-log=mysql-relay  /etc/init.d/mysqld restart

关闭从服务器的复制功能

mysql -uroot -p123456 mysql> stop slave;

配置从服务器指向master

change master to master_host=‘192.168.15.129‘, master_user=‘rep‘, master_password=‘123456‘, master_log_file=‘mysql-bin.000003‘, master_log_pos=467;

开启从服务器的复制功能

mysql> start slave;

检查从服务器的复制功能状态

#检查画横线的两行是否变为yes
mysql> show slave status\G

mysql读写分离部署配置

上传jdk-8u60-linux-x64.tar.gz配置jdk环境

[root@ amoeba ~]# tar -zxvf jdk-8u60-linux-x64.tar.gz -C /usr/local/ [root@ amoeba ~]# vim /etc/profile #添加如下三行到全局环境变量 export JAVA_HOME=/usr/local/jdk1.8.0_60 export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH export CLASSPATH=.$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$JAVA_HOME/lib/tools.jar  [root@ amoeba ~]# source /etc/profile [root@ amoeba ~]# java -version java version "1.8.0_60" Java(TM) SE Runtime Environment (build 1.8.0_60-b27) Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

下载安装Amoeba

[root@ amoeba ~]# yum -y install unzip

 #解压并改名 [root@ amoeba ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/ [root@ amoeba ~]# mv /usr/local/amoeba-mysql-3.0.5-RC /usr/local/amoeba

配置Amoeba

[root@ amoeba ~]# cd /usr/local/amoeba/conf/  [root@ amoeba conf]# cat dbServers.xml <?xml version="1.0" encoding="gbk"?>  <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd"> <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">   <!--  Each dbServer needs to be configured into a Pool,  If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:  add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig  such as ‘multiPool‘ dbServer  -->   <dbServer name="abstractServer" abstractive="true">  <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">  <property name="connectionManager">${defaultManager}</property>  <property name="sendBufferSize">64</property>  <property name="receiveBufferSize">128</property>   <!-- mysql port -->  <property name="port">3306</property> #设置Amoeba要连接的mysql数据库的端口，默认是3306   <!-- mysql schema -->  <property name="schema">testdb</property> #设置缺省的数据库，当连接amoeba时，操作表必须显式的指定数据库名，即采用dbname.tablename的方式，不支持 use dbname指定缺省库，因为操作会调度到各个后端mysql   <!-- mysql user -->  <property name="user">amoeba</property> #设置amoeba连接后端数据库服务器的账号   <property name="password">123456</property> #设置amoeba连接后端数据库服务器的密码，因此需要在所有后端数据库上创建该用户，并授权amoeba服务器可连接  </factoryConfig>   <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">  <property name="maxActive">500</property> #最大连接数，默认500  <property name="maxIdle">500</property> #最大空闲连接数  <property name="minIdle">1</property> #最小空闲连接数  <property name="minEvictableIdleTimeMillis">600000</property>  <property name="timeBetweenEvictionRunsMillis">600000</property>  <property name="testOnBorrow">true</property>  <property name="testOnReturn">true</property>  <property name="testWhileIdle">true</property>  </poolConfig>  </dbServer>   <dbServer name="master" parent="abstractServer"> #设置一个后端可写的mysql，这里定义为master，这个名字可以任意命名，后面还会用到  <factoryConfig>  <!-- mysql ip -->  <property name="ipAddress">10.0.0.21</property> #设置后端可写mysql地址  </factoryConfig>  </dbServer>   <dbServer name="slave" parent="abstractServer"> #设置后端可读mysql  <factoryConfig>  <!-- mysql ip -->  <property name="ipAddress">10.0.0.22</property>  </factoryConfig>  </dbServer>   <dbServer name="myslave" virtual="true"> #设置定义一个虚拟的dbserver，实际上相当于一个dbserver组，这里将可读的数据库ip统一放到一个组中，将这个组的名字命名为myslave  <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">  <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  <property name="loadbalance">1</property> #选择调度算法，1表示复载均衡，2表示权重，3表示HA， 这里选择1   <!-- Separated by commas,such as: server1,server2,server1 -->  <property name="poolNames">slave</property> #myslave组成员  </poolConfig>  </dbServer>  </amoeba:dbServers>

另一个配置文件amoeba.xml

[root@ amoeba conf]# cat amoeba.xml <?xml version="1.0" encoding="gbk"?>  <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd"> <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">   <proxy>   <!-- service class must implements com.meidusa.amoeba.service.Service -->  <service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">  <!-- port -->  <property name="port">8066</property> #设置amoeba监听的端口，默认是8066   <!-- bind ipAddress -->  <!--  <property name="ipAddress">127.0.0.1</property> #下面配置监听的接口，如果不设置，默认监听所以的IP  -->   <property name="connectionFactory">  <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">  <property name="sendBufferSize">128</property>  <property name="receiveBufferSize">64</property>  </bean>  </property>   <property name="authenticateProvider">  <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">  # 提供客户端连接amoeba时需要使用这里设定的账号 (这里的账号密码和amoeba连接后端数据库服务器的密码无关)  <property name="user">root</property>   <property name="password">123456</property>   <property name="filter">  <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">  <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>  </bean>  </property>  </bean>  </property>   </service>   <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">   <!-- proxy server client process thread size -->  <property name="executeThreadSize">128</property>   <!-- per connection cache prepared statement size -->  <property name="statementCacheSize">500</property>   <!-- default charset -->  <property name="serverCharset">utf8</property>   <!-- query timeout( default: 60 second , TimeUnit:second) -->  <property name="queryTimeout">60</property>  </runtime>   </proxy>   <!--  Each ConnectionManager will start as thread  manager responsible for the Connection IO read , Death Detection  -->  <connectionManagerList>  <connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">  <property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>  </connectionManager>  </connectionManagerList>   <!-- default using file loader -->  <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">  <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>  </dbServerLoader>   <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">  <property name="ruleLoader">  <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">  <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>  <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>  </bean>  </property>  <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>  <property name="LRUMapSize">1500</property>  <property name="defaultPool">master</property>  #这两个选项默认是注销掉的，需要取消注释，这里用来指定前面定义好的俩个读写池   <property name="writePool">master</property> #设置amoeba默认的可写的db池，这里设置为master  <property name="readPool">slave</property> #设置amoeba默认的可读的db池，这里设置为slave   <property name="needParse">true</property>  </queryRouter> </amoeba:configuration>

在masterdb(10.0.0.21)上创建数据库testdb

[root@ mysql-master ~]# mysql -uroot -p123456 mysql> create database testdb; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testdb | +--------------------+

查看slavedb(10.0.0.22)是否复制成功

[root@ mysql-slave ~]# mysql -uroot -p123456 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testdb | +--------------------+

分别在masterdb(10.0.0.21)和slavedb(10.0.0.22)上为amoedb用户授权

mysql> GRANT ALL ON *.* TO ‘amoeba‘@‘10.0.0.23‘ IDENTIFIED BY ‘123456‘;
mysql> flush privileges;

启动amoeba

[root@ amoeba conf]# /usr/local/amoeba/bin/launcher Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0  The stack size specified is too small, Specify at least 228k Error: Could not create the Java Virtual Machine. Error: A fatal exception has occurred. Program will exit.

以上启动报错，由于stack size太小，导致JVM启动失败，修改/usr/local/amoeba/jvm.properties文件JVM_OPTIONS参数。

[root@ amoeba amoeba]# vim /usr/local/amoeba/jvm.properties 原来： JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" 改为： JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss512k -XX:PermSize=16m -XX:MaxPermSize=96m"

再次启动

[root@ amoeba amoeba]# /usr/local/amoeba/bin/launcher
2019-08-26 14:30:22,677 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2019-08-26 14:30:23,005 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0  2019-08-26 14:33:57 [INFO] Project Name=Amoeba-MySQL, PID=24645 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2019-08-26 14:33:57,761 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2019-08-26 14:33:58,103 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066. Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0  2019-08-26 14:34:34 [INFO] Project Name=Amoeba-MySQL, PID=24675 , starting... log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2019-08-26 14:34:34,959 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2019-08-26 14:34:35,250 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

以上输出无Error报错，即是启动成功。

查看端口

[root@ amoeba amoeba]# ss -lntp|grep 8066 LISTEN 0 128 :::8066 :::* users:(("java",24675,56))

mysql读写分离测试

[root@ amoeba conf]# yum -y install mysql [root@ amoeba conf]# mysql -h10.0.0.23 -uroot -p123456 -P8066  mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | testdb | | wg | +--------------------+

在testdb库中创建表test_table并插入数据

mysql> use testdb;  mysql> create table test_table(id int,password varchar(40) not null);   mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test_table | +------------------+ 1 row in set (0.00 sec)  mysql> insert into test_table(id,password) values(‘1‘,‘test1‘); Query OK, 1 row affected (0.01 sec)  mysql> select * from test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | +------+----------+ 1 row in set (0.00 sec)

分别登陆masterdb和slavedb查看数据

查看masterdb
[root@ mysql-master ~]# mysql -uroot -p123456  mysql> select * from testdb.test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | +------+----------+  查看slavedb [root@ mysql-slave ~]# mysql -uroot -p123456  mysql> select * from testdb.test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | +------+----------+

停掉masterdb，然后在客户端分别执行插入和查询功能

[root@ mysql-master ~]# /etc/init.d/mysqld stop Stopping mysqld: [ OK ]  [root@ amoeba conf]# mysql -h10.0.0.23 -uroot -p123456 -P8066  mysql> insert into test_table(id,password) values(‘2‘,‘test2‘); ERROR 1044 (42000): Amoeba could not connect to MySQL server[10.0.0.21:3306],Connection refused  mysql> select * from testdb.test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | +------+----------+ 1 row in set (0.01 sec)

可以看到，关掉masterdb和写入报错，读正常。

开启masterdb上的msyql 关闭slave上的mysql

masterdb：

[root@ mysql-master ~]# /etc/init.d/mysqld start Starting mysqld: [ OK ]

slavedb：

[root@ mysql-slave ~]# /etc/init.d/mysqld stop Stopping mysqld: [ OK ]

客户端再次尝试插入和查询功能

mysql> insert into test_table(id,password) values(‘2‘,‘test2‘);
 mysql> select * from testdb.test_table; ERROR 1044 (42000): poolName=myslave, no valid pools

可以看到插入成功，读取失败。

开启slavedb上的mysql，查看数据是否自动同步

slavedb:

[root@ mysql-slave ~]# /etc/init.d/mysqld start Starting mysqld: [ OK ]

客户端：

[root@ amoeba conf]# mysql -h10.0.0.23 -uroot -p123456 -P8066  mysql> select * from test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | | 2 | test2 | +------+----------+

接着客户端插入数据：

mysql>  insert into test_table(id,password) values(‘3‘,‘test3‘);  mysql> select * from test_table; +------+----------+ | id | password | +------+----------+ | 1 | test1 | | 2 | test2 | | 3 | test3 | +------+----------+

OK 一切正常，到此表明amoeba实现了mysql的读写分离。