mysql主从复制和读写分离

一、主从复制

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.