mysql主从复制和读写分离

最新推荐文章于 2024-03-28 19:28:40 发布

森森的森森

最新推荐文章于 2024-03-28 19:28:40 发布

阅读量85

点赞数

本文链接：https://blog.youkuaiyun.com/m0_55637617/article/details/118512483

版权

本文详细介绍了在MySQL 5.6环境下，如何配置和测试主从复制以及实现读写分离。通过设置不同服务器的角色，授权，配置日志和position，确保数据同步。同时利用Amoeba进行读写分离，测试了在主从复制基础上的数据一致性，并展示了读请求的负载均衡效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL版本：5.6

jdk版本：1.6

amoeba版本：2.2

Host	IP
master	192.168.218.5
slave1	192.168.218.6
slave2	192.168.218.7
amoeba	192.168.218.4

主从复制

主服务器

修改MySQL配置文件my.cnf，添加以下配置

log_bin = master-bin
log_slave_updates = true
server_id = 11

注：在MySQL5.5版本的配置文件中，下划线_要改为-

进入MySQL数据库

# 配置账号，用于slave向master同步数据库
mysql> grant replication slave on *.* to 'mysql_slave'@'192.168.218.%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 查看日志文件和position
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      850 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在两台从服务器上

修改MySQL配置文件my.cnf，添加以下配置

server_id = 22
relay_log = relay_log_bin
relay_log_index = slave_relay_bin.index

进入MySQL数据库

# 定义master，添加用于同步的账号和密码以及日志文件、position
# 根据在master上看到的日志文件和position填写，不能乱填
mysql> change master to master_host='192.168.218.5', master_user='mysql_slave',master_password='password',master_log_file='master-bin.000001',master_log_pos=850;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
# 开启slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# 查看slave状态是否开启
mysql> show slave status\G;
*************************** 1. row ***************************
……省略部分内容……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
……省略部分内容……

在从服务器上查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

测试主从复制

在主服务器创建新的数据库，以测试主从复制效果

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test_database1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_database1     |
+--------------------+
5 rows in set (0.00 sec)

在从服务器上查看是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_database1     |
+--------------------+
5 rows in set (0.00 sec)

已成功同步！

读写分离

到存放jdk包的目录下

chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
mv jdk1.6.0_14/ /usr/local/jdk1.6

安装amoeba

mkdir /usr/local/amoeba
tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba

添加环境变量到/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

别忘了source一下

source /etc/profile

测试是否安装成功

[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop

出现上面的提示则安装成功

到三台MySQL服务器上，配置amoeba访问MySQL的账号

mysql> GRANT ALL ON *.* TO amoeba2db@'192.168.218.%' IDENTIFIED BY '123abc';
Query OK, 0 rows affected (0.36 sec)

修改amoeba配置文件

vim /usr/local/amoeba/conf/amoeba.xml

在这里插入图片描述

vim /usr/local/amoeba/conf/dbServers.xml

在这里插入图片描述
配置amoeba连接MySQL服务器的账号密码

配置master、slave1、slave2
在这里插入图片描述

开启amoeba

/usr/local/amoeba/bin/amoeba start &

到客户机上，连接amoeba测试，使用用于连接amoeba的账号登录

mysql -u client2amoeba -p 123abc -h 192.168.218.4 -P8066

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_database1     |
+--------------------+
5 rows in set (0.00 sec)

写入测试数据

MySQL [(none)]> use test_database1;
Database changed
MySQL [test_database1]> create table book (
    -> id int primary key auto_increment,
    -> name varchar(16));
Query OK, 0 rows affected (0.45 sec)
MySQL [test_database1]> insert into book (name) value ("it");
Query OK, 1 row affected (0.00 sec)
MySQL [test_database1]> insert into book (name) value ("cook");
Query OK, 1 row affected (0.00 sec)
MySQL [test_database1]> select * from book;
+----+------+
| id | name |
+----+------+
|  1 | it   |
|  2 | cook |
+----+------+
2 rows in set (0.11 sec)

分别到三台MySQL服务器上查看是否存在测试数据

在这里插入图片描述

因为已配好主从复制，故数据已同步过来

测试读写分离

到两台从服务器上输入

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

查看slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
……省略部分……
             Slave_IO_Running: No
            Slave_SQL_Running: No
……省略部分……

这时再回到客户端写入数据，并查看

MySQL [test_database1]> insert into book (name) value ("Java");
Query OK, 1 row affected (0.00 sec)
MySQL [test_database1]> select * from book;
+----+------+
| id | name |
+----+------+
|  1 | it   |
|  2 | cook |
+----+------+
2 rows in set (0.00 sec)

并没有我们刚刚写入的"Java"这本书

到slave1上，插入测试数据

mysql> insert into book (name) value ("Slave1");
Query OK, 1 row affected (0.00 sec)

到slave2上，插入测试数据

mysql> insert into book (name) value ("Slave2");
Query OK, 1 row affected (0.00 sec)

再回到客户端查看，会发现很神奇的事情

MySQL [test_database1]> select * from book;
+----+--------+
| id | name   |
+----+--------+
|  1 | it     |
|  2 | cook   |
|  3 | Slave1 |
+----+--------+
3 rows in set (0.00 sec)
MySQL [test_database1]> select * from book;
+----+--------+
| id | name   |
+----+--------+
|  1 | it     |
|  2 | cook   |
|  3 | Slave2 |
+----+--------+
3 rows in set (0.00 sec)
MySQL [test_database1]> select * from book;
+----+--------+
| id | name   |
+----+--------+
|  1 | it     |
|  2 | cook   |
|  3 | Slave1 |
+----+--------+
3 rows in set (0.00 sec)