一、主从复制
1、本机安装mysql
scripts/mysql_install_db --user=mysql --basedir=/data/study/mysql/mysql1 --datadir=/data/study/mysql/mysql1/data
2、配置my.cnf
basedir = /data/study/mysql/mysql1
datadir = /data/study/mysql/mysql1/data
port =3307
server_id =1
socket=/data/study/mysql/mysql1/mysql.sock
general_log=ON
general_log_file=/tmp/mysql.log
#long_query_time=2
#log-slow-queries= /data/study/mysql/mysql1/logs/slowquery.log
#pid-file=/data/study/mysql/mysql1/data/mysql.pid
log-bin=mysql-bin
binlog-do-db = amoeba_study
log_slave_updates
binlog_format=row
#配置自增偏移量
auto_increment_increment=3
auto_increment_offset=1
3、启动mysql
sudo bin/mysqld_safe --defaults-file=/data/study/mysql/mysql1/my.cnf --user=root
bin/mysql -u root -p --protocol=tcp --host=localhost --port=3307
bin/mysql -uroot -p -S mysql.sock --port 3307
bin/mysqladmin -uroot -p password 123456 -S mysql.sock --port 3307
4、分别安装mysql_slave1(3308),mysql_slave2(3309)
5、配置master(3307)
首先编辑/etc/my.cnf,添加以下配置:
log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
binlog-do-db = amoeba_study #用于master-slave的具体数据库
然后添加专门用于replication的用户:
mysql> GRANT REPLICATION SLAVE ON *.* TO root@127.0.0.1 IDENTIFIED BY '123456';
6、配置slave_3308,slave_3309
change master to master_host='127.0.0.1',master_user='root',master_port=3307,master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=120;
start slave;
查看日志:
141208 14:45:14 7 Query CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'root' MASTER_PASSWORD = <secret> MASTER_PORT = 3307 MASTER_LOG_FILE = 'mysql-bin.000006' MASTER_LOG_POS = 120
141208 14:45:56 7 Query start slave
8 Connect Out root@127.0.0.1:3307
141208 14:45:57 9 Query create database amoeba_study
9 Query GRANT REPLICATION SLAVE ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
9 Query flush privileges
表示配置成功.
二、配置amoeba读写分离
下载amoeba,配置
<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">amoeba_study</property>
<!-- mysql user -->
<property name="user">root</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="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">127.0.0.1</property>
<property name="port">3307</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<property name="ipAddress">127.0.0.1</property>
<property name="port">3308</property>
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<property name="ipAddress">127.0.0.1</property>
<property name="port">3309</property>
</factoryConfig>
</dbServer>
<dbServer name="masterPool" 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">master</property>
</poolConfig>
</dbServer>
<dbServer name="slavePool" 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">slave1,slave2</property>
</poolConfig>
</dbServer>
conf/amoeba.xml
<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">masterPool</property>
<property name="writePool">masterPool</property>
<property name="readPool">slavePool</property>
<property name="needParse">true</property>
</queryRouter>
用配置的代理用户名密码登录。
mysql -u root -p --protocol=tcp --host=localhost --port=8066
执行mysql语句,查看各个mysql的日志,是否查询语句在mysql_slave.
读写在mysql_master.