• 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..

  • 相关阅读:
    工作中遇到的java 内存溢出,问题排查
    java线上内存溢出问题排查步骤
    性能测试-java内存溢出问题排查
    164 01 Android 零基础入门 03 Java常用工具类01 Java异常 04 使用try…catch…finally实现异常处理 04 终止finally执行的方法
    163 01 Android 零基础入门 03 Java常用工具类01 Java异常 04 使用try…catch…finally实现异常处理 03 使用多重catch结构处理异常
    162 01 Android 零基础入门 03 Java常用工具类01 Java异常 04 使用try…catch…finally实现异常处理 02 使用try-catch结构处理异常
    161 01 Android 零基础入门 03 Java常用工具类01 Java异常 04 使用try…catch…finally实现异常处理 01 try-catch-finally简介
    160 01 Android 零基础入门 03 Java常用工具类01 Java异常 03 异常处理简介 01 异常处理分类
    159 01 Android 零基础入门 03 Java常用工具类01 Java异常 02 异常概述 02 异常分类
    158 01 Android 零基础入门 03 Java常用工具类01 Java异常 02 异常概述 01 什么是异常?
  • 原文地址:https://www.cnblogs.com/yangxiaofei/p/5057207.html
Copyright © 2020-2023  润新知