mysql读写分离

【centos6.5】

一、Amoeba 实现mysql读写分离

1.0 实验环境

mysql   192.168.1.138 /24

slave      192.168.1.139/24

slave2     192.168.1.140/24

amoeba     192.168.1.141/24

client  192.168.1.142 /24

以上都执行以下操作

[root@amoeba ~]# systemctl stop firewalld

[root@amoeba ~]# setenforce 0

1.1 先实现mysql数据库的主从复制

 

1.1.0所有服务器关闭firewalld或者进行规制设置

建立时间同步环境，在主服务器上安装配置NTP时间同步服务器

 

[root@mysql ~]# rpm -qa ntp

ntp-4.2.6p5-19.el7.centos.x86_64

[root@mysql ~]# cp -p /etc/ntp.conf /etc/ntp.conf.origin

[root@mysql ~]# systemctl restart ntpd

[root@mysql ~]# vim /etc/ntp.conf

15 server 127.127.1.0

 16 fudge 127.127.1.0 startum 8

[root@mysql ~]# chkconfig ntpd on

 

.1.1.1【在从服务器上同步时间】

[root@slave ~]# yum -y install ntpdate

[root@slave ~]# ntpdate 192.168.1.138

15 May 17:08:48 ntpdate[4062]: step time server 192.168.1.138 offset -101377.548968 sec

 

1.1.2配置mysql mysql主服务器

[root@mysql ~]# vim /etc/ntp.conf

20 log-bin=mysql-bin

 21 log-slave-updates=true

 22 server-id       =11

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

mysql>  grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123123';

Query OK, 0 rows affected (0.01 sec)

 

mysql>  flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000044 |      337 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

 

1.1.3配置从服务器slave 【slave2和slave一样】

[root@slave ~]# vim /etc/my.cnf

57 server-id       = 12【此数不可一样】

 58 relay-log=relay-log-bin

 59 relay-log-index=slave-relay-bin.index

[root@slave ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL. SUCCESS!

[root@slave ~]# mysql -uroot -p123123

MySQL [(none)]> slave stop;

Query OK, 0 rows affected (0.02 sec)

 

MySQL [(none)]> change master to master_host='192.168.1.138',master_user='myslave',master_password='123123',master_log_file='mysql-bin.000044',master_log_pos=337;

Query OK, 0 rows affected (0.01 sec)

  MySQL [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

 

MySQL [(none)]> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.138

                  Master_User: myslave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000044

          Read_Master_Log_Pos: 337

               Relay_Log_File: relay-log-bin.000002

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000044

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 337

              Relay_Log_Space: 407

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 11

1 row in set (0.00 sec)

 

1.2 安装amoeba 上安装Java环境

[root@amoeba ~]# ls

amoeba-mysql-binary-2.2.0.tar.gz  anaconda-ks.cfg  jdk-6u14-linux-x64.bin

[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin

[root@amoeba ~]# ./jdk-6u14-linux-x64.bin

一路空格  yes

[root@amoeba ~]# ls

amoeba-mysql-binary-2.2.0.tar.gz  jdk1.6.0_14

anaconda-ks.cfg                   jdk-6u14-linux-x64.bin

[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

[root@amoeba ~]# cp  /etc/profile{,.bak}

[root@amoeba ~]# vim /etc/profile

在最后添加

export JAVA_HOME=/usr/local/jdk1.6

export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin

export AMOEBA_HOME=/usr/local/amoeba/

export PATH=$PATH:$AMOEBA_HOME/bin

[root@amoeba ~]# .  /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)

 

1.3 安装配置Amoeba

[root@amoeba ~]# mkdir /usr/local/amoeba

[root@amoeba ~]# ll /usr/local/amoeba/

总用量 52

drwxr-xr-x 2 root root    60 5月  17 16:24 benchmark

drwxr-xr-x 2 root root   125 2月  29 2012 bin

-rwxr-xr-x 1 root root  3976 8月  29 2012 changelogs.txt

drwxr-xr-x 2 root root  4096 5月  17 16:24 conf

drwxr-xr-x 3 root root  4096 5月  17 16:24 lib

-rwxr-xr-x 1 root root 34520 8月  29 2012 LICENSE.txt

-rwxr-xr-x 1 root root  2031 8月  29 2012 README.html

[root@amoeba ~]# /usr/local/amoeba/bin/amoeba

amoeba start|stop【出现此行用法，证明配置无误】

如果出现以下报错：

specify at least 160k

解决如下：

[root@amoeba ~]# vi /usr/local/amoeba/bin/amoeba

   58 DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"【128改为256】

1.4  配置amoeba实现mysql-slave、slave2读负载均衡

在三台mysql服务器上对amoeba授权

[root@mysql ~]# mysql -uroot -p123123

mysql> grant all on *.* to 'admin'@'192.168.1.%' identified by '123';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

[root@slave ~]# mysql -uroot -p123123

MySQL [(none)]> grant all on *.* to 'admin'@'192.168.1.%' identified by '123';

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

[root@slave2 ~]# mysql  -uroot -p123123

MySQL [(none)]> grant all on *.* to 'admin'@'192.168.1.%' identified by '123';

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

编辑amoeba.xml配置文件，设置读写分离

[root@amoeba ~]# cd /usr/local/amoeba/conf/

[root@amoeba conf]# cp amoeba.xml{,.origin}

[root@amoeba conf]# vim amoeba.xml

10                         <!-- port -->

 11                        <property name="port">8066</property>【默认端口8066，无需修改】

30                                         <property name="user">amoeba</property>【设置一个用户，后面客户端访问时使用】

 31

 32                                         <property name="password">123456</property>【设置登录密码】

115                 <property name="defaultPool">master</property>【默认服务池】

116【把注释<--   -->删掉，】

117                 <property name="writePool">master</property>【写服务器池】

118                 <property name="readPool">slaves</property>【读服务器池】

编辑dbserver.xml配置文件，设置登录mysql服务器的用户及密码、mysql服务器节点的ip地址、负载均衡算法

[root@amoeba conf]# cp dbServers.xml{,.origin}

[root@amoeba conf]# vim dbServers.xml

19                         <property name="port">3306</property>【设置mysql连接端口，默认3306】

 20

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

 22

 23                         <property name="user">admin</property>【设置访问mysql服务器所用的用户名】

 24

 25                         <property name="password">123</property>【设置访问mysql服务器所用的密码】

 

38         </dbServer>

 39

 40         <dbServer name="master"  parent="abstractServer">【设置mysql服务器及ip地址】

 41                 <factoryConfig>

 42                         <property name="ipAddress">192.168.1.138</property>

 43                 </factoryConfig>

 44         </dbServer>

 45

 46         <dbServer name="slave"  parent="abstractServer">

 47                 <factoryConfig>

 48                         <property name="ipAddress">192.168.1.139</property>

 49                 </factoryConfig>

 50         </dbServer>

 51

 52         <dbServer name="slave2"  parent="abstractServer">

 53                 <factoryConfig>

 54                         <property name="ipAddress">192.168.1.140</property> 55                 </factoryConfig>

 56         </dbServer>【添加这几行】

57         <dbServer name="slaves" virtual="true">【在amoeba.xml文件中设置的readPool】

 58                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

 59                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

 60                         <property name="loadbalance">1</property>

 61

 62                         <property name="poolNames">slave,slave2</property>【在前面定义的服务器节点】

 63                 </poolConfig>

 64         </dbServer>

 

1.5 启动amoeba

[root@amoeba ~]# /usr/local/amoeba/bin/amoeba start &

[1] 2960

[root@amoeba ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2019-05-17 18:02:14,405 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0

[root@amoeba ~]# jobs【执行这个时在复制通道不会显示，必须在本窗口执行】

[1]+  运行中               /usr/local/amoeba/bin/amoeba start &

[root@amoeba ~]# log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf【这个是自己蹦出来的，不晓得什么情况】

2019-05-17 18:02:29,817 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.

2019-05-17 18:02:29,827 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:12358.

[root@amoeba ~]# jobs

[1]+  运行中               /usr/local/amoeba/bin/amoeba start &

[root@amoeba ~]# jobs

[1]+  运行中               /usr/local/amoeba/bin/amoeba start &

[root@amoeba ~]# netstat -anpt|grep java

tcp6       0      0 :::8066                 :::*                    LISTEN      2960/java          

tcp6       0      0 127.0.0.1:12358         :::*                    LISTEN      2960/java          

tcp6       0      0 192.168.1.141:41938     192.168.1.138:3306      ESTABLISHED 2960/java          

tcp6       0      0 192.168.1.141:54812     192.168.1.139:3306      ESTABLISHED 2960/java          

tcp6       0      0 192.168.1.141:41937     192.168.1.138:3306      ESTABLISHED 2960/java          

tcp6       0      0 192.168.1.141:55861     192.168.1.140:3306      ESTABLISHED 2960/java          

tcp6       0      0 192.168.1.141:55862     192.168.1.140:3306      ESTABLISHED 2960/java          

tcp6       0      0 192.168.1.141:54810     192.168.1.139:3306      ESTABLISHED 2960/java 

1.6 测试

此时的三台mysql服务器是主从备份的：

在mysq-master创建db_test库及该库下的test表

mysql> create database db_test0;

Query OK, 1 row affected (0.00 sec)

 

mysql> use db_test0;

Database changed

mysql> create table test(id int(10),name varchar(20));

Query OK, 0 rows affected (0.03 sec)

 

可以在slave和slave2上查看到该库

MySQL [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| auth               |

| client             |

| db_test            |

| db_test0           |

| haoli              |

| imployee_salary    |

| libai              |

| mysql              |

| performance_schema |

| test               |

| xiaoyun            |

| yunjisuan          |

+--------------------+

13 rows in set (0.43 sec)

在slave、slave2服务器上停止主从备份

MySQL [(none)]> stop slave;

Query OK, 0 rows affected (0.39 sec)

在mater上添加表内容

mysql> insert into test values(1,'master');

Query OK, 1 row affected (0.00 sec)

此前在服务器上同步了表，所以在从服务器上可以直接手动插入其他内容。

在slave上：

MySQL [(none)]> use db_test0

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 [db_test0]> insert into test values(2,'slave');

Query OK, 1 row affected (0.01 sec)

 

在slave2上：

MySQL [(none)]> use db_test0

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 [db_test0]> insert into test values(3,'slave2');

Query OK, 1 row affected (0.01 sec)

 

在客户机上进行读测试：

[root@localhost ~]# hostnamectl set-hostname client

[root@localhost ~]# bash

[root@client ~]# mount /dev/sr0 /var/ftp/centos7/

mount: /dev/sr0 写保护，将以只读方式挂载

[root@client centos7]# yum -y install *mysql*

[root@client ~]# mysql -uamoeba  -p123456 -h192.168.1.141 -P8066

读测试：

 

MySQL [(none)]> select * from db_test0.test;

+------+--------+

| id   | name   |

+------+--------+

|    3 | slave2 |

|    1 | master |

+------+--------+

2 rows in set (0.01 sec)

 

MySQL [(none)]> select * from db_test0.test;

+------+--------+

| id   | name   |

+------+--------+

|    2 | slave  |

|    1 | master |

+------+--------+

2 rows in set (0.02 sec)

此时读取数据库数据时，轮询的方式从slave、slave2上读取

 

写测试：

在客户端录入数据：

MySQL [(none)]> insert into db_test0.test values(5,'clien5t');

Query OK, 1 row affected (0.02 sec)

在master服务器上查看是否写入：

mysql> select * from db_test0.test;

+------+---------+

| id   | name    |

+------+---------+

|    1 | master  |

|    4 | client  |

|    5 | clien5t |

+------+---------+

3 rows in set (0.00 sec

在从服务器上查看

MySQL [(none)]> select * from db_test0.test;【没有5】

+------+--------+

| id   | name   |

+------+--------+

|    2 | slave  |

|    1 | master |

|    4 | client |

+------+--------+

3 rows in set (0.00 sec)

此时写入数据到数据库时，会写入到主服务器，不会写到从服务器上，实现了读写分离

至此，可以验证，通过amoeba实现了mysql的读写分离，写入数据仅会写入到mysql-master服务器上，读取数据，则以轮询的方式从slave、slave2俩台服务器上读取数据，实现了负载均衡。

 

 

 

boss：补充：

1、克隆的克隆机，在做此实验过程中，经常出现ip丢失，不知道什么原因

2.期间Java安装完成后找不到命令。是因为jdk包安装不完整导致，后来换了台机子重做就好了

还有一次是因为在notepad++编写的脚本直接复制到vi编辑器里导致失败的原因是格式不对导致的，直接在vi编辑器里编辑一遍就好了

3、远程连接报错

113是端口的问题

110是用户密码的问题