MySQL主从复制与读写分离配置及实操

MySQL主从复制与读写分离配置及实操

前言

MySQL主从复制与读写分离需要配置三个用户

用户1：在主服务器上设置，允许从服务器使用该用户访问

用户2：在主服务器和从服务器上设置，允许amoeba服务器使用该用户访问

用户3：在amoeba服务器的amoeba.xml中设置，允许客户端使用该用户访问

一、MySQL主从复制配置

1.1、概述

在企业网站中，为了保证数据的安全和稳定，后端的mysql数据库需要进行备份，形成主备，通过主从复制的方式使主服务器和从服务器数据同步。

1.2、主从复制的过程

1.3、实验环境

• 在vmware虚拟机中开2台linux虚拟机，一台master（主），一台slave（从）。
• 主机IP：192.168.5.135
• 从机IP：192.168.5.153

1.4、主服务器配置

• 手工编译安装mysql5.7 前面博客有详细过程，点此跳
• 安装ntp时间服务器，同步主从服务器的时间

[root@master ~]# yum install ntp -y  ##安装ntp时间源

[root@master ~]# vim /etc/ntp.conf
server 127.127.5.0 ##本地时间源127代表192.168
fudge 127.127.5.0 stratum 8 //设置时间层级8

• 关闭防火墙，启动ntp服务

[root@master ~]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@master ~]# systemctl stop firewalld.service
[root@master ~]# setenforce 0

• 修改数据库配置文件，修改后重启mysql服务

[root@master ~]# vim /etc/my.cnf
server-id  =11  ##id号自己定义
log-bin=master-bin     ###主服务器日志文件，生成二进制日志
log-slave-updates=true   ##允许从服务器更新二进制
[root@master ~]# service mysqld restart

• 进入mysql数据库，设置数据库访问权限

[root@master ~]# mysql -uroot -pabc123     ###abc123为自己设置的数据库密码
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.5.%' IDENTIFIED BY '123456';
###允许从服务器使用myslave这个用户名去复制主服务器数据
mysql> FLUSH PRIVILEGES;        ##刷新数据库
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;    ##查看数据库状态，二进制文件已经生成，位置为602
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      602 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

1.5、从服务器配置

• 手工编译安装mysql5.7 前面博客有详细过程，点此跳
• 安装ntp时间服务器

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

• 关闭防火墙，启动从服务器的时间同步

[root@slave ~]# systemctl stop firewalld.service
[root@slave ~]# setenforce 0
[root@master ~]# /usr/sbin/ntpdate 192.168.5.135   ##时间同步到主服务器
13 Jan 14:14:36 ntpdate[4525]: the NTP socket is in use, exiting

• 修改数据库配置，修改完成后重启数据库

[root@slave ~]# vim /etc/my.cnf
server-id  =2   ##id号自己定义
##开启中继日志，同步主服务器日志到本地
relay-log=relay-log-bin  
##通过索引定位中继日志位置
relay-log-index=slave-relay-bin.index 
[root@slave ~]# service mysqld restart   ##重启服务

• 进入从服务器的数据库，开启数据同步

[root@slave ~]# mysql -uroot -pabc123     ###abc123为自己设置的数据库密码

mysql> change master to master_host=‘192.168.5.135’,master_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000001’,master_log_pos=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
##同步主服务器的二进制文件，使用的账号密码是主服务器授权的myslave用户

mysql> start slave; ## 开启同步
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G; ##查看是否开启同步
Slave_IO_Running: Yes
Slave_SQL_Running: Yes ##已同步

二、MySQL主从复制验证

• 在主服务器中创建一个“abcd”数据库，发现从服务器自动同步

主服务器：

mysql> create database abcd;    ##创建abcd库
Query OK, 1 row affected (0.00 sec)

mysql> show databases; ##查看，发现已经创建
±-------------------+
| Database |
±-------------------+
| information_schema |
| abcd |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.00 sec)

从服务器：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abcd               |                     ###abcd库自动同步存在
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

三、MySQL读写分离配置

3.1、概述

• mysql读写分离通过amoeba服务器的代理功能，实现后台的读写分离
• mysql读写分离即数据的写入在主服务器上进行，读取在从服务器上进行。

3.2、实验环境

• 在上面主从复制的基础上，添加一台amoeba服务器，一台测试客户端。
• 主机IP：192.168.5.135
• 从机IP：192.168.5.153
• amoebaIP：192.168.5.154
• 客户端IP：192.168.5.152

3.3、amoeba服务器配置

• amoeba是java开发的，所以需要安装jdk
• 在官网下载JDK，复制JDK到/usr/local目录下，进入/usr/local中执行jdk

[root@amoeba abcd]# cp jdk-6u14-linux-x64.bin /usr/local/ 
[root@amoeba abcd]# cd /usr/local/
[root@amoeba local]# cd /usr/local/
[root@amoeba local]# ./jdk-6u14-linux-x64.bin 
Do you agree to the above license terms? [yes or no]
yes   ##选择yes进行安装
Press Enter to continue.....          ##按回车
Done.    
[root@amoeba local]# ls
 jdk1.6.0_14         

• 简化jdk文件名，设置环境变量，使java和阿米巴的命令都能用

[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba local]# vim /etc/profile
###添加以下内容
export JAVA_HOME=/usr/local/jdk1.6  ##家目录
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib ##class环境变量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin   ##环境变量
export AMOEBA_HOME=/usr/local/amoeba   ##amoeba家目录
export PATH=$PATH:$AMOEBA_HOME/bin   ##环境变量
[root@amoeba local]# source /etc/profile      ##刷新配置文件

• 安装amoeba
• 创建amoeba 文件夹，在官网下载amoeba安装包并解压到该目录中。

[root@amoeba local]# mkdir /usr/local/amoeba 
[root@amoeba loca]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba  ##解压
[root@amoebaloca]# chmod -R 755 /usr/local/amoeba/   ##给其执行权限

• 修改amoeba配置文件

[root@amoeba local]# cd /usr/local/amoeba/conf
[root@amoeba conf]# vim amoeba.xml
###第30行 ，修改客户端访问amoeba的用户为amoeba
 <property name="user">amoeba</property> 
####第32行，修改该用户密码为123456
<property name="password">123456</property>
###将117行去掉注释
115                 <property name="defaultPool">master</property>##默认池子改为master
116                 <!--
117                 -->
118                 <property name="writePool">master</property>##写入池子改为master
119                 <property name="readPool">serves</property>##读取池子改为slaves
120                 <property name="needParse">true</property>

• 修改amoeba的数据库配置

[root@amoeba conf]# vim /usr/local/amoeba/conf/dbServers.xml 
###第23行test修改为mysql
 22                         <!-- mysql schema -->
 23                         <property name="schema">mysql</property>

###去掉26行注释
25 <!-- mysql user -->
26 <property name=“user”>test</property>
27 ####修改访问用户为text，密码为123.com
28 <!-- mysql password -->
29 <property name=“password”>123.com</property>

• 去掉注释后，其中的用户test和密码会变为黑色

###第44行
server 改为master

44         <dbServer name="master"  parent="abstractServer">

###47行 主服务器ip 192.168.5.135

 46           <!-- mysql ip -->
 47           <property name="ipAddress">192.168.5.135</property>

###51行server改为slaves
####54行 第一台从服务器 ip 192.168.5.153
####58行multipool改为servers
####64行server1改为slave1

• 更改完成后保存退出，关闭防火墙，启动amoeba服务器

[root@amoeba conf]# systemctl stop firewalld.service
[root@amoeba conf]# setenforce 0
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start& 

• 此时amoeba处于运行状态，重新开个终端标签查看8806端口是否开启

[root@amoeba conf]# netstat -anpt | grep java 
tcp6       0      0 :::8066                 :::*                    LISTEN      9342/java           
tcp6       0      0 127.0.0.1:8870          :::*                    LISTEN      9342/java           
tcp6       0      0 192.168.5.135:36992     192.168.5.153:3306      ESTABLISHED 9342/java           
tcp6       0      0 192.168.5.135:43362     192.168.5.154:3306      ESTABLISHED 9342/java  

• 在主服务器和从服务器中开放权限给amoeba访问

mysql> grant all on *.* to test@'192.168.5.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

四、验证读写分离

4.1、验证写入功能

• 新开一台客户端，链接amoeba的数据库

[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.5.154 -P8066 

• 在数据库中创建一个abc库，在库中创建一个abc01表

mysql> create database abc;
Query OK, 1 row affected (0.01 sec)
mysql> use abc;
Database changed
mysql> create table abc01 (id int not null primary key, name char(10) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| abc01         |
+---------------+
1 row in set (0.01 sec)

• 进入从服务器，关闭从服务器的同步功能

mysql> stop slave;   ##关闭同步
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
Slave_IO_Running: No
Slave_SQL_Running: No

• 回到客户端，在abc01表中插入相关数据

mysql> insert into abc01 (id,name) values (1,'zhangsan');
Query OK, 1 row affected (0.14 sec)

• 分别进入主服务器和从服务器，查看abc01表信息

主服务器：

mysql> use abc;
Database changed
mysql> select * from abc01;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

从服务器：

mysql> use abc;           ###表中没有任何信息
Database changed
mysql> select * from abc01;
Empty set (0.00 sec)

所以，写入是写在主服务器中。

4.2、验证读取功能

• 在从服务器的abc01表中插入相关数据

mysql> use abc;
mysql> insert into abc01 (id,name) values (2,'wangwu');
Query OK, 1 row affected (0.02 sec)

• 在客户端中发现可以直接读取到从服务器的abc01表内容

mysql> use abc;
Database changed
mysql> select * from abc01;
+----+----------+
| id | name     |
+----+----------+
|  2 | wangwu |
+----+----------+row in set (0.00 sec)

所以，客户端读取的是从服务器中的数据，在生产环境中，从服务器的主从同步是开启的，即可以在客户端中读取主从服务器的所有数据。