目录
1. 主从复制
1.1 环境
四台centos7服务器
一台做client 192.168.152.12
三台做mysql服务器：
主服务器 5.6版本 192.168.152.130
从服务器1 5.7版本 192.168.152.129
从服务器2 5.7版本 192.168.152.128
1.2 防火墙设置
systemctl stop firewalld.service
setenforce 0
1.3 更改主机名+配置时钟同步
192.168.152.130：
[root@server ~]# hostnamectl set-hostname master
[root@server ~]# su
192.168.152.129：
[root@client ~]# hostnamectl set-hostname slave1
[root@client ~]# su
192.168.152.128：
[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# su
三台都做相同的配置，达到时间同步：
yum install ntp -y
yum -y install ntpdate ntp
ntpdate ntp.aliyun.com
crontab -e
crontab -l
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@master ~]# date
2021年 07月 20日 星期二 14:56:58 CST
[root@slave1 ~]# date
2021年 07月 20日 星期二 14:57:02 CST
[root@slave2 ~]# date
2021年 07月 20日 星期二 14:57:05 CST
1.4 配置主从复制
1.4.1 主服务器
[root@master ~]# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
#这两段可以增加也可以不加，我是设置的三台都同步的阿里云时钟服务器
[root@master ~]# vim /etc/my.cnf
30 log_bin=master-bin
31 log_slave=updates=true
32 server_id = 11
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@master ~]# mysql -u root -p
Enter password:
mysql> grant replication slave on *.* to 'myslave'@'192.168.152.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 412 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.4.2 从服务器1
[root@slave1 ~]# vim /etc/my.cnf
19 server-id = 22
20 log-bin=master-bin
21 relay-log=relay-log-bin
22 relay-log-index=slave-relay-bin.insex
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -u root -p
Enter password:
mysql> change master to master_host='192.168.152.130',master_user='myslave',master_password='123456',master_log_file='master-bin.000003',master_log_pos=412;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
1.4.3 从服务器2
[root@slave1 ~]# vim /etc/my.cnf
19 server-id = 33
20 log-bin=master-bin
21 relay-log=relay-log-bin
22 relay-log-index=slave-relay-bin.insex
[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# mysql -u root -p
Enter password:
mysql> change master to master_host='192.168.152.130',master_user='myslave',master_password='123456',master_log_file='master-bin.000003',master_log_pos=412;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
1.5 实现主从复制
在主服务器上创建一个数据库，查看从服务器上是否存在
主服务器：
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
从服务器1：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
从服务器2：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
若是在slave1服务器上面更改，master主服务器与slave2并没有同步更改
2. 读写分离
2.1 环境
五台centos7服务器
一台做client 192.168.152.12
一台mysql-amoeb 192.168.152.127
三台做mysql服务器：
主服务器 5.6版本 192.168.152.130
从服务器1 5.7版本 192.168.152.129
从服务器2 5.7版本 192.168.152.128
事先都要把防火墙给关闭
2.2 安装amoeb服务器
2.2.1 安装jdk环境
[root@localhost ~]# hostnamectl set-hostname amoeba
[root@localhost ~]# su
[root@amoeba ~]# cd /opt
[root@amoeba opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin rh
[root@amoeba opt]# mv jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin
[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]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba local]# ls
bin games jdk1.6 lib libexec share
etc include jdk-6u14-linux-x64.bin lib64 sbin src
[root@amoeba local]# java -version
openjdk version "1.8.0_181"
[root@amoeba local]# vim /etc/profile
75 unset i
76 unset -f pathmunge
77 export JAVA_HOME=/usr/local/jdk1.6
78 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
79 export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
80 export AMOEBA_HOME=/usr/local/amoeba
81 export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba local]# source /etc/profile
[root@amoeba local]# java -version
java version "1.6.0_14"
2.2.2 安装amoeba服务
[root@amoeba local]# mkdir amoeba
[root@amoeba local]# tar zvxf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba local]# chmod -R 755 /usr/local/amoeba/
[root@amoeba local]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
2.2.3 配置amoeba服务
#在主从服务器中的数据库中开放amoeba的访问权限
mysql> grant all on *.* to 'test'@'192.168.152.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[root@amoeba local]# cd /usr/local/amoeba/conf
[root@amoeba conf]# ls
access_list.conf dbserver.dtd functionMap.xml rule.dtd
amoeba.dtd dbServers.xml log4j.dtd ruleFunctionMap.xml
amoeba.xml function.dtd log4j.xml rule.xml
[root@amoeba conf]# vim amoeba.xml #进入主配置文件
#30行，修改账户名
<property name="user">amoeba</property>
#32行，修改账户密码（amoeba访问数据库的密码）
<property name="password">abc123</property>
#115行，修改默认池
<property name="defaultPool">master</property>
#117行---120行的注释合并到到117行后
<!-- -->
#118行，修改写池
<property name="writePool">master</property>
#119行，修改读池
<property name="readPool">slaves</property>
[root@amoeba conf]# vim dbServers.xml #进入数据库配置文件
#23行，将test改为mysql
<property name="schema">mysql</property>
#26行，将root改为test（amoebe访问三台数据库的用户）
<property name="user">test</property>
#29行，修改test用户登录数据库的密码
<property name="password">123.com</property>
#45行，修改数据库主服务器名
<dbServer name="master" parent="abstractServer">
#48行，修改主服务器IP地址
<property name="ipAddress">192.168.152.130</property>
#52行，修改数据库从服务器名，和IP地址
##复制52行到57行，粘贴到58行下
<dbServer name="slave1" parent="abstractServer">
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">192.168.152.129</property>
56 </factoryConfig>
57 </dbServer>
58
59 <dbServer name="slave2" parent="abstractServer">
60 <factoryConfig>
61 <!-- mysql ip -->
62 <property name="ipAddress">192.168.152.128</property>
63 </factoryConfig>
64 </dbServer>
#66行，修改多个服务器池的名称
<dbServer name="slaves" virtual="true">
#72行，添加两个从服务器名称
<property name="poolNames">slave1,slave2</property>
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start #启动amoeba服务
#另开中端
[root@amoeba conf]# netstat -natp | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 70400/java
2.3 实现读写分离
2.3.1 测试主从同步及amoeba服务的开启是否成功
client客户端：
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# su
[root@client ~]# yum -y install mariadb
[root@client ~]# mysql -u amoeba -pabc123 -h 192.168.152.127 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1196121930
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> create database zwh;
Query OK, 1 row affected (0.01 sec)
主服务器：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
| zwh |
+--------------------+
6 rows in set (0.00 sec)
两台从服务器：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
| zwh |
+--------------------+
6 rows in set (0.00 sec)
2.3.2 测试mysql的读写分离
2台从服务器关闭主从同步，在客户端数据库的数据表内插入数据验证amoeba读写分离
#2台从服务器关闭主从同步，查询状态都是no
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
client客户端：
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
| zwh |
+--------------------+
6 rows in set (0.01 sec)
MySQL [(none)]> use zwh;
Database changed
MySQL [zwh]> create table aaa(id int(10));
Query OK, 0 rows affected (0.03 sec)
MySQL [zwh]> insert into aaa values(1);
Query OK, 1 row affected (0.01 sec)
#分别查看主服务器跟从服务器
主服务器：master处理写的任务，可以查询到数据表
mysql> use zwh;
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 aaa;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
从服务器：从服务器处理读的任务，查询看不到数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
| zwh |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql> use zwh;
Database changed
mysql> select *from aaa;
ERROR 1146 (42S02): Table 'zwh.aaa' doesn't exist
2.3.3 在从服务器上更改表数据，使用客户端验证从服务器读，和轮询的方式读
由于之前创建表的时候是关闭主从同步的状态，所以这里先开启一下服务，把表现同步过来，然后再关闭两台从服务器的同步操作，分别在两个从服务器上进行写入内容记录，在客户端上进行查看
从服务器1：
mysql> use zwh;
Database changed
mysql> show tables;
+---------------+
| Tables_in_zwh |
+---------------+
| aaa |
+---------------+
1 row in set (0.00 sec)
mysql> insert into aaa values(2);
Query OK, 1 row affected (0.01 sec)
从服务器2：
mysql> use zwh;
Database changed
mysql> show tables;
+---------------+
| Tables_in_zwh |
+---------------+
| aaa |
+---------------+
1 row in set (0.00 sec)
mysql> insert into aaa values(3);
Query OK, 1 row affected (0.00 sec)
client客户端：
MySQL [zwh]> select *from aaa;
+------+
| id |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)
MySQL [zwh]> select *from aaa;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
3. 小结
MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离
MySQL支持的复制类型有三种：基于语句的复制、基于行的复制和混合类型的复制
984
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
