此操作是在阿里云服务器上执行的,Linux下mysql主从配置,实现主从配置,需要两台机器。
MySQL主从配置
修改文件mysqld.cnf
该文件目录 /etc/mysql/mysql.cnf.d/mysqld.cnf,找到bind_address,将其注释掉,这是mysql默认的ip为127.0.0.1
在阿里云服务器上添加安全组规则
规则方向:入方向
授权策略:允许
协议类型:MySQL
端口范围:3306/3306
授权类型:地址段访问
授权对象:0.0.0.0/0
主机服务器master配置
1. 修改MySQL配置文件 sudo vi /etc/mysql/mysql.cnf.d/mysqld.cnf
在[mysqld]添加一下配置(默认是被注释的)
server-id =1
log-bin = /var/log/mysql/mysql-bin.log
binlog_do_db = tt_test
binlog_ignore_db = mysql
binlog_do_db = tt_test(服务器要连接的数据库的名称)
2.创建一个新用户用来远程连接使用
命令 :CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例如:CREATE USER 'admin'@'%' IDENTIFIED BY '123456';
"username"你将要创建的用户名,
"host"指定该用户可以在哪个主机上登录,如果是本地用户可用localhost,如果想要该用户可以从任意远程主机登录,可以使用通配符"%",
"password",创建用户的登录密码,密码可以为空,为空则该用户不需要密码登录服务器
3.授权
GRANT PRIVILEGES ON databasename.tablename TO 'username'@'host';
例:GRANT SELECT,INSERT ON tt_test.test TO 'admin'@'%';
用以上命令授权的用户不能给其他用户授权,如果想让该用户可以授权,可以使用一下命令
GRANT PRIVILEGES ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
4.授予从机复制表权限
GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';
5.重启mysql服务器
sudo /etc/init.d/mysql restart
6.登录MySQL
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_ignore_db
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 154 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MySQL,防止主服务器状态值发生变化,记录下file和position的值,从机服务器要用
`
从机服务器slave配置
1. 修改MySQL配置文件 sudo vi /etc/mysql/mysql.cnf.d/mysqld.cnf
server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
2 .重启slave从服务器MySQL
sudo /etc/init.d/mysql restart
3.登录slave服务器,连接master主服务器
命令:change master to master_host='120.79.18.137',master_port=3306,master_user='admin',master_password='123456',master_log_file='mysql-bin.0000018',master_log_pos=3125;
master_host对应主服务器的外网IP地址,master_port对应主服务器的端口(3306),master_log_file对应show master status显示的File列:mysql-bin.000001,master_log_pos对应Position列:154,否则有可能出现同步失败
4.创建从数据库tt_test
5.重启MySQL服务器
6.登录MySQL状态下,启动slave的数据同步
start slave;
如果后面连接不到主服务器master,先停止slave,再连接master,最后启动slave数据同步
MySQL读写分离
主从配置(见上)
amoeba(变形虫)配置
- 下载jdk for linux
本人下载版本为jdk-8u144-linux-x64.tar.gz
2.解压jdk-8u144-linux-x64.tar.gz 命令:
tar -zxvf jdk-8u144-linux-x64.tar.gz 到目录/usr/share/jdk1.8
接下来配置环境变量,vim /etc/profile
在profile文件末尾加入:
export JAVA_HOME=/usr/share/jdk1.8
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
如果使用apt安装的 open jdk,只需要在~/.bashrc文件中配置一下环境变量:
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
export JRE_HOME=${JAVA_HOME}/jre
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export CLASSPATH=$CLASSPATH:.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
环境变量配置好,重启服务器或者使用source命令
3.下载amoeba,本人使用的版本是:amoeba-mysql-3.0.5-RC,将其解压到/usr/share/amoeba目录下,修改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>
<!-- mysql schema数据库名称 -->
<property name="schema">test</property>
<!-- mysql 用户和密码-->
<property name="user">admin</property>
<property name="password">123456</property>
</factoryConfig>
<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<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="server1" parent="abstractServer">
<factoryConfig>
<!--Master mysql ip 主机服务器的linux的ip -->
<property name="ipAddress">112.74.33.243</property>
</factoryConfig>
</dbServer>
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<!--slave mysql ip从机服务器的ip地址,linux上或者阿里云的 -->
<property name="ipAddress">119.23.247.195</property>
</factoryConfig>
</dbServer>
<dbServer name="multiPool" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">server1,server2</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>
修改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>
<!-- bind ipAddress -->
<!--
<property name="ipAddress">127.0.0.1</property>
-->
<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">
<!—代理服务器的访问用户和密码-->
<property name="user">root</property>
<property name="password">root</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">server1</property>
<!--主机写,从机读 -->
<property name="writePool">server1</property>
<property name="readPool">server2</property>
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration>
4.修改/usr/share/amoeba/jvm.proerties,将JVM_OPTION后面的值中的 -Xss196k 修改为 -Xss256k
5.修改/usr/share/amoba/bin目录执行权限: chmod +x bin/*
6.在安全组中添加8066端口的外网访问权限
7.进入/usr/share/amoeba/bin目录下,启动amoeba
./launch start
8.修改自己的测试项目的链接数据库的dbinfo.properties文件,将链接数据库的URL中的ip改为amoeba的服务器的ip,端口号由3306改为8066,用户名和密码改为amoeba服务器中amoeba.xml中配置的amoeba的用户名和密码(以上的root、root)
9.启动项目测试数据查询和添加,然后端口slave,在测试查询和添加,看看是否实现了读写分离
实现读写分离的过程中常出现的错误:
1. 如果提示 Unknown system variable ‘language’ 错误,将mysql的包换成5.1.24试试。
2. 提示无法打开console.log,就将logs文件夹权限修改一下,比如:chmod 777 logs。
3. 提示Adress in Use,直接重启服务器好了,reboot