mysql主从复制读写分离

一、环境

      1.主服务器操作系统：Mac OS

         MySQL版本：5.1.6

       2.从服务器操作系统：Centos 6.5

         MySQL版本：5.1.6

 

二、实战

       2.1MySQL主从复制，读写分离示意图

       

 

       MySQL 复制的工作方式很简单，一台服务器作为主机，一台或多台服务器作为从机。主机会把数据库的变化记录到日志。一旦这些变化被记录到日志，就会立刻（或者以设定的时间间隔）被送到从机。

        

 

        2.2 主服务器IP：172.16.151.1

             从服务器IP：172.16.151.130

             在两台服务器分别安装好MySQL

       

         2.3 修改主服务器的MySql配置文件，Mac默认在 /etc/my.cnf下，加入如下配置  

               官方说明：为了使用事务的InnoDB在复制中最大的持久性和一致性，你应该指定innodb_flush_log_at_trx_commit=1,sync_binlog=1选项。）

[mysqld]

log-bin=mysql-bin #slave会基于此log-bin来做replication 

server-id=1           #master的标示,1表示master 

innodb_flush_log_at_trx_commit=1 

sync_binlog=1

          2.4 在主服务器创建一个数据库用户账号，用于从服务器读取bin_log

grant replication slave on *.* to  copy_user@172.16.151.130 identified by 'copy_pwd'

        2.5 启动主服务器

sudo mysqld_safe

        2.6 登录主服务器Mysql，进入test数据库，查看master状态

mysql -u root

mysql> use test;

mysql> show master status;

          

 

         2.7 登录从服务器(Centos 6.5)，修改mysql配置文件 ,加入一行配置

[mysqld]

server-id=2 #标示从服务器

       2.8 重启从服务器的Mysql

sudo mysqld_safe --defaults-file=/home/card/my.cnf  #如果不想使用默认my.cnf文件，可以使用指定目录下的my.cnf文件

       2.9 登录到mysql，停止slave，配置master数据库的信息，然后启动slave

       注意：以下参数

        master_host：主服务器IP地址

        master_user：主服务器创建的用于同步数据的用户

        master_password：主服务器创建的用于同步数据的用户密码

        master_log_file：在主服务器里，通过show master status里显示的File的内容

        master_log_pos：在主服务器里，通过show master status里显示的Position的内容

mysql -u root

mysql> use test;

mysql> slave stop;

mysql> change master to master_host='172.16.151.1',master_user='copy_user',master_password='copy_pwd',master_log_file='mysql-bin.000006',master_log_pos=617;

mysql> start slave;

mysql> show slave status\G;

    

             如果出现下面的情况，说明主从同步已经成功！

             Slave_IO_Running: Yes

             Slave_SQL_Running: Yes

        经过以上步骤，然后你在主服务器里创建一张表，插入一条数据，再进入到从服务器看一下，会发现表和数据都已经同步过来了。

 

        <本节完>

 

一、为什么需要读写分离

    1.提高应用整体性能

    2.数据备份

    3.数据库可以水平扩展

 

二、MySQL读写分离原理

    1.基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

    

三、如何实现读写分离

    1.Amoeba百科：Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。

Amoeba相当于一个SQL请求的路由器， 目的是为负载均衡、读写分离、高可用性提供机制，而不是完全实现它们。用户需要结合使用MySQL的 Replication等机制来实现副本同步等功能。amoeba对底层数据库连接管理和路由实现也采用了可插拨的机制，第三方可以开发更高级的策略类来 替代作者的实现。这个程序总体上比较符合KISS原则的思想。

    2.Amoeba优势：

        a). 数据切分后复杂数据源整合

        b). 提供数据切分规则并降低数据切分规则给数据库带来的影响

        c). 降低数据库与客户端连接

        d). 读写分离路由

     3.Amoeba不足：

        a)、目前还不支持事务b)、暂时不支持存储过程（近期会支持）

        c)、不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多数据的场合）

        d)、暂时不支持分库分表，amoeba目前只做到分数据库实例，每个被切分的节点需要保持库表结构一致

     4.官方文档：http://docs.hexnova.com/amoeba/index.html

 

四、Amoeba安装

    1.下载Amoba，解压至目录，执行 ${Amoeba_Home}/bin/amoeba

    2.如果报以下错误：Error: JAVA_HOME environment variable is not set.  

        明明设置了JAVA_HOME的，他还是会报这个错误，所以我门就直接修改启动的脚本，vi ${Amoeba_Home}/bin/amoeba 

JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home  #加上这一行，相当于在启动脚本里加上JAVA_HOME的配置

noJavaHome=false

if [ -z "$JAVA_HOME" ] ; then

    noJavaHome=true

fi

if $cygwin ; then

    [ -n "$JAVA_HOME" ] &&

        JAVA_HOME=`cygpath -u "$JAVA_HOME"`

fi

if [ ! -e "$JAVA_HOME/bin/java" ] ; then

    noJavaHome=true

fi

if $noJavaHome ; then

    echo "Error: JAVA_HOME environment variable is not set."

    exit 1

fi

     继续启动，可能会报一个错误：the stack size specified is too small, Specify at least 160k  Error: Could not create the Java Virtual Machine.继续修改启动脚本

DEFAULT_OPTS="-server -Xms256m -Xmx512m -Xss512k"        #加上这句

#DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"       #注释掉这句

# DEFAULT_OPTS="$DEFAULT_OPTS -XX:+HeapDumpOnOutOfMemoryError -XX:+AggressiveOpts -XX:+UseParallelGC -XX:+UseBiasedLocking -XX:NewSize=64m"

DEFAULT_OPTS="$DEFAULT_OPTS -Damoeba.home=\"$AMOEBA_HOME\""

DEFAULT_OPTS="$DEFAULT_OPTS -Dclassworlds.conf=\"$AMOEBA_HOME/bin/amoeba.classworlds\""

     继续启动，执行 ${Amoeba_Home}/bin/amoeba start , 启动成功，显示：

log4j:WARN log4j config load completed from file:/usr/programe/amoeba/conf/log4j.xml

2015-04-17 08:37:52,689 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0

log4j:WARN ip access config load completed from file:/usr/programe/amoeba/conf/access_list.conf

2015-04-17 08:37:53,036 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.

2015-04-17 08:37:53,038 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:43757.

 

五、使用Amoeba做代理实现读写分离实战

    1.环境介绍

         1.1 IP：172.16.151.1   （MySQL Master服务器）

               操作系统：Mac OS

               软件：JDK，MySQL

         1.1 IP：172.16.151.130   （MySQL Slave服务器）

               操作系统：Centos 6.5

               软件：JDK，MySQL

         1.1 IP：172.16.151.131   （Amoeba服务器）

               操作系统：Centos 6.5

               软件：JDK，MySQL，Amoeba

 

    2.Amoeba配置

        首先搭建好MySQL主从复制的环境，详情参见MySQL第一讲。

 

        2.1给Mysql的Master，Slave服务器创建一个Amoeba使用的账号密码：

 

GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'%'IDENTIFIED BY 'amoeba' ;

FLUSH PRIVILEGES;

 

          创建后使用该账号在本地登录一下，看能否登录成功：

           

mysql -uamoeba -pamoeba

 

          我在使用的时候这里报错了：Access denied for user 'amoeba'@'localhost' (using password: YES)

          解决方案：

 

delete from user where user is null;

delete from user where user='';

FLUSH PRIVILEGES;

        重新登录，成功！

        

        2.2配置amoeba.xml文件，该文件配置amoeba服务器相关配置信息。

<?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.net.ServerableConnectionManager">

            <!-- port amoeba代理的端口号-->

            <property name="port">8066</property>

            <!-- bind ipAddress 绑定IP地址 -->

            <!--<property name="ipAddress">127.0.0.1</property>-->

            <property name="manager">${clientConnectioneManager}</property>

            <property name="connectionFactory">

                <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">

                    <property name="sendBufferSize">128</property>

                    <property name="receiveBufferSize">64</property>

                </bean>

            </property>

     

            <!— amoeba客户端的mysql账号 -->

            <property name="authenticator">

                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                    <property name="user">root</property>

                    <property name="password"></property>

                    <property name="filter">

                        <bean class="com.meidusa.amoeba.server.IPAccessController">

                            <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

                        </bean>

                    </property>

                </bean>

            </property>

        </service>

        <!-- server class must implements com.meidusa.amoeba.service.Service -->

        <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">

            <!-- port -->

            <!--  default value: random number <property name="port">9066</property>-->

            <!-- bind ipAddress -->

            <property name="ipAddress">127.0.0.1</property>

            <property name="daemon">true</property>

            <property name="manager">${clientConnectioneManager}</property>

            <property name="connectionFactory">

                <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>

            </property>

        </service>

        <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">

            <!-- proxy server net IO Read thread size -->

            <property name="readThreadPoolSize">20</property>

            <!-- proxy server client process thread size -->

            <property name="clientSideThreadPoolSize">30</property>

            <!-- mysql server data packet process thread size -->

            <property name="serverSideThreadPoolSize">30</property>

            <!-- per connection cache prepared statement size  -->

            <property name="statementCacheSize">500</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="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">

            <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>

            <!--

                default value is avaliable Processors

            <property name="processors">5</property>

            -->

        </connectionManager>

 

        <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">

            <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

            <!--

            default value is avaliable Processors

            <property name="processors">5</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>

        <!— 默认使用的数据库服务器 配置信息在dbServer.xml -->

        <property name="defaultPool">server1</property>

        <!— 写入的数据库服务器 配置信息在dbServer.xml-->

        <property name="writePool">server1</property> 

        <!— 读取的数据库服务器 配置信息在dbServer.xml-->

        <property name="readPool">multiPool</property>

        <property name="needParse">true</property>

    </queryRouter>

</amoeba:configuration>

    2.3 配置dbServer.xml，该文件配置实际的MySQL服务器相关信息，以及读写的服务器，访问策略等

<?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="manager">${defaultManager}</property>

        <property name="sendBufferSize">64</property>

        <property name="receiveBufferSize">128</property>

        <!-- mysql 服务器的端口，Schema，账号，密码 -->

        <property name="port">3306</property>

        <property name="schema">test</property>

        <property name="user">amoeba</property>

        <property name="password">amoeba</property>

    </factoryConfig>

    <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">

        <property name="maxActive">500</property>

        <property name="maxIdle">500</property>

        <property name="minIdle">10</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>

 

<!— server1 为Master -->

    <dbServer name="server1"  parent="abstractServer">

        <factoryConfig>

            <property name="ipAddress">172.16.151.1</property>

        </factoryConfig>

</dbServer>

 

<!— server2 为Slave -->

<dbServer name="server2"  parent="abstractServer">

    <factoryConfig>

        <property name="ipAddress">172.16.151.130</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 -->

                        <!— 这里是配置相当于访问策略的信息

                              1.如配置：server1,server2则会按相同权重随机访问其中一个服务器

                              2.如配置：server2则只会访问server2服务器

                              3.如配置：server1，server2，server2则会访问server1，server2（权重较大，访问到的几率更高）

                        -->     

        <property name="poolNames">server2</property>

    </poolConfig>

</dbServer>

</amoeba:dbServers>

 

    2.4简单的读写分离，只要进行上述的配置即可

         进入amoeba服务器，重启amoeba。

         使用amoeba登录客户端： mysql -uroot -p -h172.16.151.131 --port 8066   #-h后面跟的是amoeba服务器IP地址

         开始验证之前，可以打开Master和Slave的数据库日志，tail -1000f  ${MYSQL_HOME}/data/mysql.log

         在登录客户端时，我们会发现Master和Slave的日志都出现以下日志信息：

         Connect amoeba@172.16.151.131 on test    （172.16.151.131的IP使用amoeba登录，并使用test数据库）

         接下来开始验证读和写：

             2.4.1 创建一个表：

            create table syn_amoeba

            (

                id int(10) primary key,

                name varchar(20)

             );

           发现Master和Slave的log都创建了这张表，因为主库创建完会同步到从库，那怎么样验证amoeba只是在Master里创建了表呢，而不是同时往两个库创建呢，继续往下看。

       

            2.4.2 插入一条数据 

            insert into syn_amoeba values (1,'Minutch');

            Master和Salve也都插入了数据，原因与创建表相同。

    

            2.4.3 查询一条数据

            select * from syn_amoeba;

            我们发现，仅仅是Slave库出现了查询日志，在Master并没有查询，也就是说，amoeba并不是同时在两个库里进行SQL操作。

问题一只：当使用join做关联查询时，直接访问master了，和我想象的不一样，可能是有规则配置的问题（等研究amoeba的SQL路由配置时再看这歌问题）。

 

             <本节完> 

 

 

一、监控mysql执行的所有sql

        1.编辑my.cnf文件，在[mysqld]下面加上一行配置，重启mysql服务，跟踪下面配置制定的文件，之后执行的sql都会在该文件里

log = /usr/local/mysql/data/mysql_file.log  #文件位置自定义

        注意：据我目前测试，这个文件必须在${MYSQL_HOME}/data/目录下(放在和mysql-bin.0000N同一个目录下，该目录存放msyql的binlog)，如果在其他地方都不会生成这个日志文件，跟踪日志发现报以下错误：[ERROR] Could not use /home/card/mysql.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.  可能是用户的权限问题，有兴趣的可以自行研究。

        

         之后在在进入msyql，执行sql的时候，所有的sql语句都会进入到mysql_file.log。