Amoeba+Mysql实现数据库读写分离

### 一、Amoeba 是什么

Amoeba(变形虫)项目，专注分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。

主要解决：

- 降低数据切分带来的复杂多数据库结构

- 提供切分规则并降低数据切分规则给应用带来的影响

- 降低db 与客户端的连接数

- 读写分离

Amoeba+Mysql实现数据库读写分离

基本的原理：让“主”数据库处理事务增，删，改（INSERT，UPDATA，DELETE），“从”数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致变更同步到集群中的从数据库

部署环境:

|hostname|ip地址|备注|

|-------|-------|-------|

|k8s-master|192.168.0.135|mysql-master|

|node1|192.168.0.136|slave1-mysql|

|node2|192.168.0.137|slave2-mysql|

|node3|192.168.0.138|amoeba(代理服务器)|

1. 主数据库

- mysql-master:主节点

```

vim /etc/my.cnf

log-bin=mysql-bin #必须]启用二进制日志

server-id=1 #必须服务器唯一ID(唯一即可)

```

2. 创建数据同步的用户并授权

```

GRANT ALL ON . to 'amoeba'@'%' identified by 'zhangyou1.'; #创建授权用户为amoeba提供登录

GRANT REPLICATION SLAVE ON . to 'savle'@'192.168.0.%' identified by '123456';#主从模式

上面SQL的作用是创建一个用户 savle ，密码为 123456，并且给savle用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制

mysql> show master status\G

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

File: mysql-bin.000001 #从库加入主库时需要配置的二进制文件名

Position: 740 # 二进制日志从 pos 740 开始被slave

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

```

二、二个从数据库

1. slave1-mysql:从节点

```

vim /etc/my.cnf

server-id=2 #必须服务器唯一ID

relay-log=relay-log #必须

GRANT ALL ON . to 'amoeba'@'%' identified by 'zhangyou1.'; #创建授权用户为amoeba提供登录

```

2. slave2-mysql:从节点

```

vim /etc/my.cnf

server-id=3 #必须服务器唯一ID

relay-log=relay-log #必须

GRANT ALL ON . to 'amoeba'@'%' identified by 'zhangyou1.'; #创建授权用户为amoeba提供登录

```

3. 重启两个从节点的mysql。然后两个从节点都需要连接到主节点，分别进入mysql中执行相同命令

```

change master to master_host='192.168.0.135',master_user='savle',master_password='123456.',master_log_file='mysql-bin.000001',master_log_pos=740;

start slave; #开启从节点

```

命令说明

```

master_host : 主库的IP地址

master_user : 访问主库进行主从复制的用户名(上面在主库创建的)

master_password : 访问主库进行主从复制的用户名对应的密码

master_log_file : 从哪个日志文件开始同步(上述查询master状态中展示的有)

master_log_pos : 从指定日志文件的哪个位置开始同步(上述查询master状态中展示的有)

```

![image-1679745445482](https://zmzycc.top/upload/2023/03/image-1679745445482.png)

然后通过状态信息中的 Slave_IO_running 和 Slave_SQL_running 可以看出主从同步是否就绪，如果这两个参数全为Yes，表示主从同步已经配置完成。

4. 主库测试：创建一个测试库并添加创建表和添加数据，查看从库是否同步

```

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test;

Database changed

mysql> create table test (id int);

Query OK, 0 rows affected (0.14 sec)

mysql> insert into test values (1);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

```

分别去从库查看数据是否同步过来了

```

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

| zabbixproxy |

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

6 rows in set (0.00 sec)

mysql> use test;

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> select * from test.test;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

```

从数据库同步正常，主从数据库部署完成，接下来部署 Amoeba

### 二、安装java环境[jdk安装](https://zmzycc.top/archives/linux-pei-zhi-jdk)

1.安装Amoeba

可以从https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/3.x/下载最新版本的Amoeba，我这里下载的是amoeba-mysql-3.0.5-RC-distribution.zip。Amoeba安装非常简单，直接解压即可使用，这里将Amoeba解压到/usr/local/amoeba目录下，这样就安装完成了

cd /usr/local/amoeba/conf

vim 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,

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

-->

<property name="connectionManager">${defaultManager}</property>

<property name="port">3306</property> #设置Amoeba 连接数据库的端口，默认 3306

<property name="schema">mysql</property> #设置缺省的数据库，我这里使用的MySQL版本为5.7版本，默认是没有test库的，会报所以找不到默认的库，我之前搭建的MySQL版本是5.5是不需要修改的，没注意到这一点

<property name="user">amoeba</property> #设置amoeba连接后端数据库服务器的账号和密码，上面已为 amoeba授权和设置了密码

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

</factoryConfig>

<property name="maxActive">500</property> #最大连接数，默认为500

<property name="maxIdle">500</property> #最大空闲连接数

<property name="minIdle">10</property> #最新空闲连接数

</poolConfig>

</dbServer>

<dbServer name="master" parent="abstractServer"> #设置一个空间名称，可任意命名，这里定义为：master，顾名思义就是为master库创建一个命名空间，后面会用到

<property name="ipAddress">192.168.0.135</property> #设置 master的IP，功能可写

</factoryConfig>

</dbServer>

<dbServer name="slave1" parent="abstractServer"> #设置slava1 命名空间名称

<property name="ipAddress">192.168.0.136</property> # 设置slave1的IP，功能可写

</factoryConfig>

</dbServer>

<dbServer name="slave2" parent="abstractServer"> #设置slava2 命名空间名称

<property name="ipAddress">192.168.0.137</property> #设置 slave的ip，功能可写

</factoryConfig>

</dbServer>

<dbServer name="slaves" virtual="true"> #设置定义一个虚拟的dbserver，实际上相当于一个dbserver组，这里将可读的数据库ip统一放到一个组中，将这个组的名字命名为slaves

<property name="loadbalance">1</property> #选择调度算法，1表示复制均衡，2表示权重，3表示HA，这里选择1

<property name="poolNames">slave1,slave2</property> #slave组成员，把slave1，slave2 加入成员里，可实现 “读” 负载

</poolConfig>

</dbServer>

</amoeba:dbServers>

```

2.vim amoeba.xml

```

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<proxy>

<property name="port">8066</property> #设置amoeba默认是8066

<!--

-->

</bean>

</property>

<property name="user">root</property> #提供客服端连接amoeba是需要使用设定的账号，账号密码可任意设置和上面授权的数据服务器里的密码无关(自定义即可)

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

</bean>

</property>

</bean>

</property>

</service>

</runtime>

</proxy>

<!--

Each ConnectionManager will start as thread

manager responsible for the Connection IO read , Death Detection

-->

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

</connectionManager>

</connectionManagerList>

<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>

</dbServerLoader>

<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="defaultPool">master</property> #设置amoeba默认是的池，这里设置为master（在dbServers.xml文件中，我们定义了一会master的空间名称以及对应的服务器IP）

<property name="writePool">master</property> #设置写的池，master作为主数据库，处理的业务为增，删，改都是写操作，所以这设置为master

<property name="readPool">slaves</property> #设置读的池 slave 作为从库，负责查（SELECT，因为我们有两个slave，在定义命名空间时，我们配置了一个slaves组，里面有slave1，slave2两个成员，这样就可以达到读的负载

</queryRouter>

</amoeba:configuration>

```

3.启动Amoeba

/usr/local/amoeba/bin/launcher &

![image-1679793597125](https://zmzycc.top/upload/2023/03/image-1679793597125.png)

4.查看端口

![image-1679793642992](https://zmzycc.top/upload/2023/03/image-1679793642992.png)

5.Amoeba 登录测试

```

[root@k8s-master ~]# mysql -uroot -p123456 -h 192.168.0.138 -P 8066

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1930870900

Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL)

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> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.00 sec)

mysql> select database();

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

| database() |

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

| mysql |

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

1 row in set (0.00 sec)

mysql> select * from test.test;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> use test;

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> insert into test values (2);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test.test;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.02 sec)

```

测试结果：登录后所在的库是“mysql”库，可读取，可写入，到这里数据库读写分离，负载部署完成！！！

测试读写分离，负载效果

### 三、读写分离测试：

测试1 ：mysql-master down机，写入报错，读正常

客服端：

```

mysql> use test;

Database changed

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

mysql> insert into test values (3);

ERROR 1044 (42000): Amoeba could not connect to MySQL server[192.168.0.135:3306],Connection refused #写入失败

```

测试2 ：关闭两台slave，读取失败，插入成功

mysql-master

[root@k8s-master ~]# systemctl start mysqld

mysql-slave1

[root@k8s-node1 ~]# systemctl stop mysqld

mysql-slave2

[root@k8s-node2 ~]# systemctl stop mysqld

客服端：

```

mysql> use test;

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> insert into test values (3);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

ERROR 1044 (42000): poolName=slaves, no valid pools #查讯失败

```

测试3：开启slava1，save2上的mysql，查看数据是否自动同步

[root@k8s-node1 ~]# systemctl start mysqld

[root@k8s-node2 ~]# systemctl start mysqld

客服端：

```

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.02 sec)

mysql> insert into test values (4);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.06 sec)

```

读写分离测试完毕

4.slave “读” 负载测试：

mysql-slave1：在test表插入值：50

```

mysql> use test;

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> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00 sec)

mysql> insert into test values (50);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 50 |

+------+

5 rows in set (0.00 sec)

```

mysql-slave2：在test表插入值：100

```

mysql> use test;

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> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00 sec)

mysql> insert into test values (100);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 100 |

+------+

5 rows in set (0.00 sec)

```

客服端：

```

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 50 |

+------+

5 rows in set (0.01 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 100 |

+------+

5 rows in set (0.02 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 50 |

+------+

5 rows in set (0.02 sec)

mysql> select * from test;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 100 |

+------+

5 rows in set (0.06 sec)

```

分别在 slave1，slava2 上对test表插入不同的数据，通过客户端查询会发现，每一次查询和上一次不同，这里就是完全体系了 “读 ”负载的功能。到这里对数据库的高可用架构实验到此结束！！！