• Linux下Mysql主从复制(Master-Slave)与读写分离(Amoeba)实践


    、为什么要做Mysql的主从复制(读写分离)?
    通俗来讲,如果对数据库的读和写都在同一个数据库服务器中操作,业务系统性能会降低。
    为了提升业务系统性能,优化用户体验,可以通过做主从复制(读写分离)来减轻主数据库的负载。
    而且如果主数据库宕机,可快速将业务系统切换到从数据库上,可避免数据丢失。

    二、MySQL主从复制 Mysql主从复制(读写分离)和集群的区别

    我对MySQL也是刚开始研究,不是很专业。我的理解是:
    1、主从复制(读写分离):一般需要两台及以上数据库服务器即可(一台用于写入数据,一台用于同步主的数据并用于数据查询操作)。
    局限性:
    (1)配置好主从复制之后,同一张表,只能对一个服务器写操作。如果在从上执行了写操作,而之后主也操作了这张表,或导致主从不同步;据说可以配置成主主方式,但我还没有研究到。
    (2)主数据库服务器宕机,需要手动将业务系统切换到从数据库服务器。无法做到高可用性(除非再通过部署keepalive做成高可用方案)。
    2、集群是由N台数据库服务器组成,数据的写入和查询是随机到任意一台数据库服务器的,其他数据库服务器会自动同步数据库的操作。
    任何一台数据库宕机,不会对整个集群造成大的影响。
    局限性:我经过测试才知道目前mysql集群版本(MySQL Cluster)只能对NDB存储引擎的数据进行集群同步,如果是INNODB或其他的MySQL存储引擎是不行的。这个也导致了我放弃了在业务系统中应用这种方案。

    三、回归正题,接下来开始MySQL5.5.12的主从复制教程:
    1、MySQL5.5开始主从复制有两种方式:基于日志(binlog);基于GTID(全局事务标示符)。
    需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.5.12的GTID复制还是不支持临时表的。
    所以此篇教程主要是告诉大家如何通过日志(binlog)方式做主从复制!

    2、MySQL官方提供的MySQL Replication教程:
    http://dev.mysql.com/doc/refman/5.6/en/replication.html
    这个官方教程强烈建议大家阅读(需要一定的英语阅读能力哦!不行就google翻译后再阅读吧~)。

    因此,一般来说都是通过 主从复制(Master-Slave)的方式来同步数据,再通过读写分离(MySQL-Proxy)来提升数据库的并发负载能力 这样的方案来进行部署与实施的。

    如下图所示:

    三、MySQL主从复制
    场景描述:

    主数据库服务器:42.51.153.157,MySQL已经安装,并且无应用数据。
    从数据库服务器:42.51.155.219,MySQL已经安装,并且无应用数据。

    系统:CentOS6.4 32位

    Mysql:5.5版本

    selinux关闭 防火墙关闭

    setenforce 0
    service iptables stop

     

    2.就是要确定服务器上的Mysql

    yum install mysql mysql-server -y

    3.确认时间同步

    [root@master ~]# yum install ntp -y
    [root@master ~]# vi /etc/ntp.conf    #添加以下2行
    server 127.127.1.0
    fudge 127.127.1.0 stratum 8
    [root@master ~]# /etc/init.d/ntpd restart   #重启ntpd服务

    2)在节点服务器上进行时间同步

    [root@slave1 ~]# /etc/init.d/iptables stop
    [root@slave1 ~]# chkconfig iptables off
    [root@slave1 ~]# yum install -y ntpdate
    [root@slave1 ~]# /usr/sbin/ntpdate 42.51.153.157

     

    四:配置Mysql 

    1.首先修改Mysql主配置的配置文件(Master)

    [root@master ~]# cp /etc/my.cnf /etc/my.cnf.old        ##修改之前备份
    [root@master ~]# vi /etc/my.cnf                        ##修改
    server-id       = 11                                     //修改
    
    log-bin=master-bin                                         //修改
    
             log-slave-updates=true                                  //增加
    
    [root@master ~]# /etc/init.d/mysqld restart           ##重启Mysql
    [root@master ~]# mysql -u root –ppassword                  //登录数据库,给从服务器授权
    mysql> create user 'yanghongfei'@'localhost' identified by '123456'; mysql> grant replication slave on *.* to 'yanghongfei'@'42.51.155.219' identified by '123456'; mysql> flush privileges; mysql> show master status; ##这个日志后面会用的到 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 343 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

     

     

    2.配置从服务器  (Slave)

     

    [root@master ~]# cp /etc/my.cnf /etc/my.cnf.old        ##修改之前备份

    [root@salve shell]# vi /etc/my.cnf
    server-id       = 33                                                        //修改     此处ID只要比Master大就可以
    
    relay-log=relay-log-bin                                                     //增加
    
    relay-log-index=slave-relay-bin.index                          //增加
    
    修改完成之后重启mysql服务
    
    登录Mysql,配置同步
    
    [root@salve shell]# service mysqld restart
    
    [root@slave1 ~]# mysql -u root –ppassword
    
    mysql> stop slave;                                          ##首先停止
    
    mysql>  change master to master_host='42.51.153.157',master_user='yanghongfei',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=343;
    
    mysql> start slave;
    
    mysql> show slave status G;

     

    查看是否成功~

     


     

    上面2处都为Yes的时候表示已经成功了~~

     

    3. 测试主从是否复制成功,主服务器创建数据库看是不是被同步过去了

    Master:

     

    Slave:

     

    下面我来装一个Discuz论坛 我们来测试下数据表会不会被同步进去,这里安装网站不再详细说了 我这面用的环境是lnmp   首先下载一个Discuz的包 网上有很多

    [root@master ~]# unzip Discuz_7.2_FULL_SC_UTF8.zip      
    
    [root@master ~]# mv upload/ /usr/share/nginx/html/
    
    [root@master ~]# cd /usr/share/nginx/html/
    
    [root@master html]# mv upload/ Discuz
    
    [root@master html]# chmod 777 -R ./Discuz/
    
    [root@master ~]# service nginx restart

    Master:

    Slave:

    上面可以看出主从复制成功~~

     

     

     

    Mysql主从复制常用命令

    1.停止主节点复制

    mysql> stop master;

    2.清除主节点复制文件

    mysql> reset master;

    3.停止从节点复制

    mysql> stop slave;

    4.清除从节点复制文件

    mysql> reset slave;

    5.开启从节点复制

    mysql> start slave;

    6.查看主数据库的Master复制信息

    mysql> show master status;

    7.查看主节点的复制状态

    mysql> show master status G;

    8.查看从节点的复制状态

    mysql> show slave status G;

     

     

    五、MySQL读写分离的实现

     

    搭建MySQL读写分离

    环境:主数据库服务器:42.51.153.157

            从1数据库服务器:42.51.155.219

            从2数据库服务器:42.51.157.217

            代理Amoeba服务器:42.51.152.166  

     software: amoeba-mysql-binary-2.2.0.tar.gz  

                    jdk-6u45-linux-x64.bin

    下载地址为:ftp://42.51.152.2/linux环境搭建

        

    1)在Amoeba主机安装Java环境  

    [root@Amoeba ~]# ./jdk-6u45-linux-x64.bin 
    [root@Amoeba ~]# mv jdk1.6.0_45/ /usr/local/jdk1.6
    [root@amoeba /]# vim /etc/profile                //增加以下配置
    export JAVA_HOME=/usr/local/jdk1.6
    export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
    export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
    export AMOEBA_HOME=/usr/local/amoeba/
    export PATH=$PATH:$AMOEBA_HOME/bin
    [root@amoeba /]# source /etc/profile
    [root@amoeba /]# java -version
    java version "1.6.0_14"
    Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
    Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

    2)安装并配置Amoeba软件  修改以下带下划线的内容

    [root@Amoeba ~]# mkdir /usr/local/amoeba
    [root@Amoeba ~]# tar zxvf  amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
    [root@Amoeba ~]# chmod -R 755 /usr/local/amoeba/
    [root@Amoeba ~]# /usr/local/amoeba/bin/amoeba
    
    The stack size specified is too small, Specify at least 160k
    Could not create the Java virtual machine.

    3)配置Amoeba读写分离,两个Slave读负载均衡

    Master、Slave1、Slave2中开放权限给Amoeba访问

    Master:

    mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
     

    Slave1:

    mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
     

    Slave2:

    mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
     

    编辑amoeba.xml配置文件

    <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
    <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
    
            <proxy>
    
                    <!-- service class must implements com.meidusa.amoeba.service.Service -->
                            <!-- port -->
                            <property name="port">8066</property>
    
                            <!-- bind ipAddress -->
                            <!--
                            <property name="ipAddress">127.0.0.1</property>
                            <property name="connectionFactory">
                                            <property name="sendBufferSize">128</property>
                                            <property name="receiveBufferSize">64</property>
                                    </bean>
                            </property>
    
    
                                            <property name="user">amoeba</property>
    
                                            <property name="password">123456</property>
    
                                            <property name="filter">
                                                    </bean>
                                            </property>
                                    </bean>
                            </property>
    
                    </service>
    
                    <!-- server class must implements com.meidusa.amoeba.service.Service -->
                            <!-- 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">
                            </property>
    
                    </service>
    
                    <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
                            <!-- proxy server net IO Read thread size -->
                            <property name="readThreadPoolSize">20</property>
                            <!-- mysql server data packet process thread size -->
                            <property name="serverSideThreadPoolSize">30</property>
    
                            <!-- per connection cache prepared statement size  -->
                            <property name="statementCacheSize">500</property>
    
            </proxy>
    
            <!--
                    Each ConnectionManager will start as thread
                    manager responsible for the Connection IO read , Death Detection
            -->
            <connectionManagerList>
                            <!--
                              default value is avaliable Processors
                            <property name="processors">5</property>
                             -->
                    </connectionManager>
    
                            <!--
                              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>
                    <property name="defaultPool">master</property>
    
                    <property name="writePool">master</property>
                    <property name="readPool">slaves</property>
                    <property name="needParse">true</property>
            </queryRouter>
    </amoeba:configuration>

    编辑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,
                             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 port -->
                            <property name="port">3306</property>
    
                            <!-- mysql schema -->
                            <property name="schema">test</property>
    
                            <!-- mysql user -->
                            <property name="user">test</property>
    
                            <property name="password">123.com</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>
    
            <dbServer name="master"  parent="abstractServer">
                    <factoryConfig>
                            <!-- mysql ip -->
                            <property name="ipAddress">42.51.153.157</property>
                    </factoryConfig>
            </dbServer>
    
            <dbServer name="slave1"  parent="abstractServer">
                    <factoryConfig>
                            <!-- mysql ip -->
                            <property name="ipAddress">42.51.155.219</property>
                    </factoryConfig>
            </dbServer>
    
            <dbServer name="slave2"  parent="abstractServer">
                    <factoryConfig>
                            <!-- mysql ip -->
                            <property name="ipAddress">42.51.157.217</property>
                    </factoryConfig>
            </dbServer>
    
            <dbServer name="slaves" 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>
    
    </amoeba:dbServers>

    配置无误后 启动Amoeba软件 默认的TCP端口号:8066.  只有看到Amoeba软件监听到了其他的Mysql主机才算是成功了~

    [root@Amoeba conf]# netstat -antp | grep 'java'
    tcp        0      0 ::ffff:127.0.0.1:60672      :::*                        LISTEN      5121/java           
    tcp        0      0 :::8066                     :::*                        LISTEN      5121/java           
    tcp        0      0 ::ffff:42.51.152.166:55874  ::ffff:42.51.153.157:3306   ESTABLISHED 5121/java           
    tcp        0      0 ::ffff:42.51.152.166:36135  ::ffff:42.51.157.217:3306   ESTABLISHED 5121/java           
    tcp        0      0 ::ffff:42.51.152.166:40687  ::ffff:42.51.155.219:3306   ESTABLISHED 5121/java           

    4).客户端测试  通过代理来访问Mysql

    [root@squid_node1 ~]# mysql -u amoeba -p123456 -h 42.51.152.166 -P8066
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 414297791
    Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Community Server (GPL) by Atomicorp
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yanghongfei        |
    +--------------------+
    5 rows in set (0.02 sec)
    
    mysql> 

    在Master创建一个表,同步到其他的2个从服务器上,然后关闭从服务器的Slave功能,再插入其他的区别语句。

    Master:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yanghongfei        |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> 
    mysql> use yanghongfei;
    Database changed
    mysql> create table yang (id int (10), name varchar(10),address varchar(20));
    Query OK, 0 rows affected (0.26 sec)
    mysql> desc yang;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(10)     | YES  |     | NULL    |       |
    | name    | varchar(10) | YES  |     | NULL    |       |
    | address | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)

    Slave1;  可以看出来同步了

    mysql> use yanghongfei;
    Database changed
    mysql> 
    mysql> show tables;
    +-----------------------+
    | Tables_in_yanghongfei |
    +-----------------------+
    | yang                  |
    +-----------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> desc yang;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(10)     | YES  |     | NULL    |       |
    | name    | varchar(10) | YES  |     | NULL    |       |
    | address | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)

    Slave2:   同上

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yanghongfei        |
    +--------------------+
    5 rows in set (0.06 sec)
    
    mysql> use yanghongfei;
    Database changed
    mysql> show tables;
    +-----------------------+
    | Tables_in_yanghongfei |
    +-----------------------+
    | yang                  |
    +-----------------------+
    1 row in set (0.03 sec)
    
    mysql> desc yang;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(10)     | YES  |     | NULL    |       |
    | name    | varchar(10) | YES  |     | NULL    |       |
    | address | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    3 rows in set (0.04 sec)

    关闭从1和从2 的Slave功能 

    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)

    关闭之后在主服务器上插入区别语句 Master操作:

    mysql> insert into yang values('1','yang','this_is_master');
    Query OK, 1 row affected (0.01 sec)

    刚才上面操作是我们关闭了Slave功能 所有从服务器只是同步了表,没有同步到插入的区别语句,我们在2个从数据库上分别创建区别语句;

    Slave1:

    mysql> insert into yang values('2','yang','this_is_slave1');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    2 | yang | this_is_slave1 |
    +------+------+----------------+
    1 row in set (0.00 sec)

    Slave2:

    mysql> insert into yang values ('3','yang','this_is_slave2');
    Query OK, 1 row affected (0.05 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    3 | yang | this_is_slave2 |
    +------+------+----------------+
    1 row in set (0.02 sec)
    
    mysql> 

    最后:在刚才连接上的客户端上操作

    第一次查询操作为:

    mysql> use yanghongfei;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +-----------------------+
    | Tables_in_yanghongfei |
    +-----------------------+
    | yang                  |
    +-----------------------+
    1 row in set (0.00 sec)
    
    mysql> desc yang;
    +---------+-------------+------+-----+---------+-------+
    | Field   | Type        | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id      | int(10)     | YES  |     | NULL    |       |
    | name    | varchar(10) | YES  |     | NULL    |       |
    | address | varchar(20) | YES  |     | NULL    |       |
    +---------+-------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    2 | yang | this_is_slave1 |
    +------+------+----------------+
    1 row in set (0.01 sec)

    第二次查询操作为:

    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    3 | yang | this_is_slave2 |
    +------+------+----------------+
    1 row in set (0.01 sec)

    第三次查询操作为:

    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    2 | yang | this_is_slave1 |
    +------+------+----------------+
    1 row in set (0.00 sec)

    从上面可以看出查询操作是 都是在Slave1和Slave2执行的  还可以看出来我们的2台Slave起到了负载均衡

    接下来我们在Client上再写一条insert语句

    Client 写操作      下面可以看出我们插入了一条写操作  查询的话还是查不到的.证明是只能在Master才能查询到~

    mysql> insert into yang values ('4','yang','This_is_wirte_test');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> 
    mysql> 
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    3 | yang | this_is_slave2 |
    +------+------+----------------+
    1 row in set (0.01 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    2 | yang | this_is_slave1 |
    +------+------+----------------+
    1 row in set (0.01 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    3 | yang | this_is_slave2 |
    +------+------+----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from yang;
    +------+------+----------------+
    | id   | name | address        |
    +------+------+----------------+
    |    2 | yang | this_is_slave1 |
    +------+------+----------------+
    1 row in set (0.00 sec)

    Master查询刚才Client的Write Test

    mysql> 
    mysql> select * from yang;
    +------+------+--------------------+
    | id   | name | address            |
    +------+------+--------------------+
    |    1 | yang | this_is_master     |
    |    4 | yang | This_is_wirte_test |
    +------+------+--------------------+
    2 rows in set (0.00 sec)
    
    mysql> 

       Mysql读写分离常用命令

    1.切换数据库

    mysql> use yanghongfei;

    2.查看数据表

    mysql> show tables;

    3.查看表结构

    mysql> desc yang;

    4.查看表内容

    mysql> select * from yang;

    5.插入一条表语句

    mysql> insert into yang values ('4','yang','This_is_wirte_test');

    6.删除一条表语句

    mysql> delete from yang where id=4;

    实现结果: 可以看出Mysql的读写分离,目前所有的写操作都在Master数据库服务器上,所有的写操作都在Slave1和Slave2 数据库服务器上。从而实现了MySql读写分离,负载均衡。

    备注:i/o线程的端口号是没办法锁定的,若是要开启iptables的话、就在Cilent执行insert到Master上,然后在Slave 用tcpdum抓Master的数据包,记录下来tcp端口号,添加iptables ACCEPT即可。

    重启后端口号会跟这变化,这时候需要根据tcpdump抓到的包分析来修改iptables的策略~ Amoeba的iptables上只开启Java的8066端口号就OK..

  • 相关阅读:
    Nexus3.0私服搭建
    JavaScript
    Spring基础
    Hibernate注解
    HTML5
    Apache Tomcat
    Java安装(Ubuntu)
    C++ 日期 & 时间
    C++ 引用
    C++ 指针
  • 原文地址:https://www.cnblogs.com/yangxiaofei/p/5057207.html
Copyright © 2020-2023  润新知