目录
一、主从复制
1.1 MySQL的复制类型
- 基于语句的复制
- 基于行的复制
- 混合类型的复制
二、MySQL 读写分离
- 读写分离建立在主从复制之上,先有读写分离,才有主从复制
- 只在主服务器上写,只在从服务器上读
- 主数据库处理事务性查询,从数据库处理SELECT查询
- 数据库复制用于将事务性查询的变更同步到集群中的从数据库
- 读写分离方案
基于程序代码内部实现
基于中间代理层实现 MySQL-Proxy Amoeba
三、主从复制与读写分离实验
3.1 实验环境
-
服务器版本:CentOS7.6
-
数据库版本: mysql 5.7.17
-
主服务器master : 192.168.233.100
-
从服务器slave1 : 192.168.233.200
-
从服务器slave2 : 192.168.233.180
-
Amoeba服务器:192.168.233.50
-
为了方便实验 ,关闭所有服务器防火墙和核心防护 以免出现干扰
每台服务器都输入 iptables -F setenforce 0
3.2 主从复制
3.2.1 主服务器master
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id = 11
log-bin=master-bin ## 开启二进制日志
log_slave-updates=ture ## 允许同步
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p
mysql> grant replication slave on *.* to 'myslave'@'192.168.233.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec) ## 授权允许复制的账号
mysql> flush privileges; # 刷新
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; ## 展示主服务器此时的信息 不要在对主服务器有任何操作 不然Position会动
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 604 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.2.2 从服务器
- 从服务slave1
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id = 21 ## 不能一样 是唯一的
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -uroot -p
mysql> change master to master_host='192.168.233.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604; # #指定主服务地址 用户名 密码 日志名字 开始复制位置
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave; ## 开始从服务器功能
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.233.100
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 604
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes ## 出现这两个Yes 表示成功
Slave_SQL_Running: Yes
……………………省略一部分
- slave2
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server-id = 31
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# mysql -uroot -p
mysql> change master to master_host='192.168.233.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.233.100
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 604
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.3 读写分离
- 准备好 jdk-6u14-linux-x64.bin 压缩包 amoeba服务器上准备JDK环境
- amoeba-mysql-binary-2.2.0.tar ## amoeba 压缩包
3.3.1 配置Amoeba服务器
[root@amoeba ~]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba ~]# chmod -R 755 /usr/local/jdk-6u14-linux-x64.bin
[root@amoeba ~]# cd /usr/local/
[root@amoeba local]# ./jdk-6u14-linux-x64.bin
yes
enter
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6
[root@amoeba local]# vim /etc/profile ##将 JAVA 环境加入到全局变量
export JAVA_HOME=/usr/local/jdk1.6
export CLSSPATH=$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 local]# source /etc/profile ## 重新读取 使刚刚的配置生效
[root@amoeba opt]# mkdir /usr/local/amoeba
[root@amoeba opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ ## 解压到刚刚创建的文件夹
[root@master opt]# chmod -R 755 /usr/local/amoeba/ ## 基于 文件权限
[root@amoeba opt]# /usr/local/amoeba/bin/amoeba ## 检测amoeba是否安装成功 出现下列显示信息表示安装成功
amoeba start|stop
3.3.2 主从服务开权限账号
- 主从服务器上都输入
mysql> grant all on *.* to test@'192.168.233.%' identified by '123.com';
3.3.3 修改Amoeba 服务器配置文件
[root@amoeba opt]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
[root@amoeba opt]# cd /usr/local/amoeba/
[root@amoeba amoeba]# vim conf/amoeba.xml
<property name="user">amoeba</property> ## 30行 ## 设置使用amoeba的账号密码
<property name="password">123456</property> ## 32行
115 <property name="defaultPool">master</property>
118 <property name="writePool">master</property> ## 这两行去掉注释
119 <property name="readPool">slaves</property>
[root@amoeba amoeba]# vim conf/dbServers.xml
22 <!-- mysql schema -->
23 <property name="schema">mysql</property> ##这里原本是test 一定要主要修改成数据库已
有的一个库,mysql 5.5版本自带一个test 这里用的5.7 没有这个库,不改成已有的库,连接的时候会报错
24
25 <!-- mysql user -->
26 <property name="user">test</property>
27
28 <!-- mysql password -->
29 <property name="password">123.com</property>
45 <dbServer name="master" parent="abstractServer">
46 <factoryConfig>
47 <!-- mysql ip -->
48 <property name="ipAddress">192.168.233.100</property>
49 </factoryConfig>
50 </dbServer>
51
52 <dbServer name="slave1" parent="abstractServer">
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">192.168.233.200</property>
56 </factoryConfig>
57 </dbServer>
58 <dbServer name="slave2" parent="abstractServer">
59 <factoryConfig>
60 <!-- mysql ip -->
61 <property name="ipAddress">192.168.233.180</property>
62 </factoryConfig>
63
64 <dbServer name="slaves" virtual="true">
65 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
66 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
67 <property name="loadbalance">1</property>
68
69 <!-- Separated by commas,such as: server1,server2,server1 -->
70 <property name="poolNames">slave1,slave2</property>
71 </poolConfig>
72 </dbServer>
[root@amoeba amoeba]# yum -y install mysql ## 在amoeba服务器安装一个mysql
[root@amoeba amoeba]# /usr/local/amoeba/bin/amoeba & ## 开启amoba服务
3.4 测试
- 主服务器
mysql> use school
Database changed
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)
从服务器
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
主服务器
mysql> insert into zang values(1,'zhangsan','this is master');
从服务器
从服务器1
mysql> insert into zang values(2,'lisi','this is slave1');
Query OK, 1 row affected (0.00 sec)
从服务器2
mysql> insert into zang values(3,'wangwu','this is slave2');
Query OK, 1 row affected (0.01 sec)
- 客户端查看zang 表 ## 两个从服务器轮流读取
Database changed
MySQL [school]> select *from zang;
+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | lisi | this is slave1 |
+------+------+----------------+
1 row in set (0.01 sec)
MySQL [school]> select *from zang;
+------+--------+----------------+
| id | name | address |
+------+--------+----------------+
| 3 | wangwu | this is slave2 |
+------+--------+----------------+
1 row in set (0.02 sec)
- 客户端写入数据
MySQL [school]> insert into zang values(4,'zhaoliu','this is client');
Query OK, 1 row affected (0.03 sec)
只能在主服务器看到
mysql> select * from zang;
+------+----------+----------------+
| id | name | address |
+------+----------+----------------+
| 1 | zhangsan | this is master |
| 4 | zhaoliu | this is client |
+------+----------+----------------+
2 rows in set (0.00 sec)
从服务器只能看到自己写入的数据
mysql> select *from zang;
+------+--------+----------------+
| id | name | address |
+------+--------+----------------+
| 3 | wangwu | this is slave2 |
+------+--------+----------------+
1 row in set (0.00 sec)