mysql 主从复制搭建
-------------------------Mysql主从复制--------------------------------------
环境
master 192.168.100.150
slave01 192.168.100.160
slave02 192.168.100.170
amoeba 192.168.100.130
客户端mysql 192.168.100.120
--------------------------主服务器
master 192.168.100.150
1.修改/etc/my.cnf配置文件，增加服务器id，配置二进制日志选项
systemctl stop firewalld
set enforce 0
vim /etc/my.cnf
server-id = 11
log-bin=master-bin
log-slave-updates=true
systemctl restart mysqld.service
mysql -uroot -pabc123
mysql> grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123456';
mysql> flush privileges;
mysql> show master status;
--------------------------从服务器-----------------------
slave01 192.168.100.160
vim /etc/my.cnf
'//mysqld字段下面修改'
server-id = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
mysql> change master to master_host='192.168.100.150',master_user='myslave',master_password='123456',master_log_file='master-bin.000013',master_log_pos=154;
flush privileges;
flush privileges;
mysql> start slave;
mysql> show slave status\G;
slave02 192.168.100.170
vim /etc/my.cnf
'//mysqld字段下面修改'
server-id = 23
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
systemctl restart mysqld.service
mysql -uroot -pabc23
Enter password:
mysql> change master to master_host='192.168.100.150',master_user='myslave',master_password='123456',master_log_file='master-bin.000013',master_log_pos=154;
flush privileges;
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes '//发现IO是正常的'
Slave_SQL_Running: Yes '//SQL是正常的'
----------------------------------读写分离----------------------------------
###读写分离的前提是已部署主从服务
cd /opt
上传jdk-6u14-linux-x64.bin amoeba-mysql-binary-2.2.0.tar.gz两个文件
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
Do you agree to the above license terms? [yes or no]
yes
mv jdk1.6.0_14/ jdk1.6
vim /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 /etc/profile
mkdir /usr/local/amoeba
cd /opt
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
显示amoeba start|stop //说明安装成功
***三台mysql服务器添加权限和用户开放给amoeba访问
mysql> grant all on *.* to 'test'@'192.168.100.%' identified by '123.com';
mysql> flush privileges;
show grants for 'test'@'192.168.100.%';
cd /usr/local/amoeba/conf
****编辑amoeba主配置文件
vim amoeba.xml
//第30行修改
30 <property name="user">amoeba</property> //客户访问amoeba账户
31
32 <property name="password">123456</property> //使用123456密码访问amoeba服务器'
//第115行修改
//开启读写功能池设定'
115 <property name="defaultPool">master</property> //修改默认地址池为master
116
117 <!-- --> '//117行取消注释'
118 <property name="writePool">master</property> //修改写为master
119 <property name="readPool">slaves</property> //修改读为slaves
120 //120行删除-->注释符号
'//编辑数据库配置文件'
vim dbServers.xml
22 <!-- mysql schema -->
23 <property name="schema">mysql</property> '//amoeba默认读取的库将test修改为mysql;23行test修改为mysql' 5.5版本这里是test,原本有这个库,5.7没有自带这个test库,所以要修改
24
//'设置amoeba访问mysql数据库的用户和密码'
25 <!-- mysql user -->
26 <property name="user">test</property> '//26行修改用户名'
27
28 <!-- mysql password --> '//28行-30行取消注释'
29 <property name="password">123.com</property> '//29行修改密码'
45 <dbServer name="master" parent="abstractServer"> '//45行主mysql服务器名称修改为master'
46 <factoryConfig>
47 <!-- mysql ip -->
48 <property name="ipAddress">192.168.100.150</property> '48行//修改主服务器IP'
49 </factoryConfig>
50 </dbServer>
51
52 <dbServer name="slave1" parent="abstractServer"> '//52行修改从服务器1的名称'
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">192.168.100.160</property> '//修改从服务器1的地址'
56 </factoryConfig>
57 </dbServer>
58 '将上面从服务器配置的7行复制下来修改名称slave2和ip地址'
59 <dbServer name="slave2" parent="abstractServer"> '//修改从服务器2的名称'
60 <factoryConfig>
61 <!-- mysql ip -->
62 <property name="ipAddress">192.168.100.170</property> '//修改从服务器1的地址
63 </factoryConfig>
64 </dbServer>
65 '//修改数据库从服务器池'
66 <dbServer name="slaves" virtual="true"> //修改服务器的名称为slaves'
67 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
68 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
69 <property name="loadbalance">1</property>
70
71 <!-- Separated by commas,such as: server1,server2,server1 -->
72 <property name="poolNames">slave1,slave2</property> '//添加两个从服务器名称slave1,slave2'
73 </poolConfig>
74 </dbServer>
开启amoeba软件，放到后台运行
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &
[3] 11060
[root@amoeba conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-08-27 00:26:27,797 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-08-27 00:26:27,916 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-08-27 00:26:27,916 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:5790.
'//此时处于持续监控状态，无法输入命令,但是不要关闭,在连接一个窗口看端口有没有开启'
[root@amoeba ~]# netstat -ntap | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 11060/java
--------------------------------------------客户端安装mysql验证--------------------------------------------[root@localhost ~]# yum install -y mysql
systemctl stop firewalld.service
setenforce 0
yum install mysql -y
mysql -u amoeba -pabc123 -h 192.168.100.130 -P8066
***测试读写分离，关闭主从复制功能
两台从服务器关闭slave功能
stop slave;
show slave status\G;
...省略内容
Slave_IO_Running: No '//发现功能已被关闭'
Slave_SQL_Running: No
...省略内容
***从服务器配置数据，验证读写分离
客户机
MySQL [(none)]> create database accp;
Query OK, 1 row affected (0.02 sec)
mysql> show databases; //主服务器能看见accp库
+--------------------+
| Database |
+--------------------+
| information_schema |
| accp |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show databases; //两台从服务器都看不见accp库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
'//会发现主服务器有，但是从服务器没有，说明主从复制功能已经关闭'
***验证读分离
mysql> use mysql;
Database changed
mysql> create table info (id int not null,name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into info values(1,'zhangsan'); '//添加zhangsan记录'
Query OK, 1 row affected (0.02 sec)
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
'//slave1从服务器设置：'
mysql> use mysql;
Database changed
mysql> create table info(id int not null,name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into info values(2,'lisi'); '//添加lisi记录'
Query OK, 1 row affected (0.02 sec)
mysql> select * from info;
+----+------+
| id | name |
+----+------+
| 2 | lisi |
+----+------+
1 row in set (0.00 sec)
****客户端测试
MySQL [(none)]> use mysql;
MySQL [mysql]> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
MySQL [mysql]> select * from info;
+----+------+
| id | name |
+----+------+
| 2 | lisi |
+----+------+
1 row in set (0.01 sec)
'//发现每一次查询都会轮询在slave1和slave2上查询数据，如果开启主从复制，则数据都会相同'
'//读写分离试验成功'