mysql读写 slave stop_Linux下MySQL主从复制（Master-Slave）与读写分离（Amoeba）实践

五、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配置文件

8066

127.0.0.1

true

${clientConnectioneManager}

20

30

500

${amoeba.home}/conf/dbServers.xml

${amoeba.home}/conf/rule.xml

${amoeba.home}/conf/ruleFunctionMap.xml

${amoeba.home}/conf/functionMap.xml

1500

master

master

slaves

true

编辑dbServers.xml配置文件  同样是以下带下划线的内容

${defaultManager}

64

128

3306

test

test

123.com

500

500

10

600000

600000

true

true

true

42.51.153.157

42.51.155.219

42.51.157.217

1

slave1,slave2

配置无误后 启动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..