《Centos7——MYSQL主从复制+读写分离》
程序员文章站
2024-03-21 18:49:40
...
MYSQL主从复制+读写分离
环境：
三台虚拟机
主从复制两台：
192.168.194.130
192.168.194.131
读写分离一台：
192.168.194.132
一、MYSQL主从复制
1. 使用yum安装部署mysql两台
服务器A
[aaa@qq.com ~]# yum -y install mariadb mariadb-server #安装mariadb
服务器B
[aaa@qq.com ~]# yum -y install mariadb mariadb-server #安装mariadb
2. 关闭防火墙和selinux
服务器A
[aaa@qq.com ~]# systemctl stop firewalld #关闭防火墙
[aaa@qq.com ~]# setenforce 0
服务器B
[aaa@qq.com ~]# systemctl stop firewalld #关闭防火墙
[aaa@qq.com ~]# setenforce 0
3. 查看mysql的版本信息
[aaa@qq.com ~]# mysql --version #查看mysql版本信息
mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1
4. 启动mysql服务，查看系统中是否存在mysql进程，并查看mysql的端口号
[aaa@qq.comcalhost ~]# systemctl start mariadb #启动mysql服务
[aaa@qq.com ~]# ps -ef |grep mysql #查看mysql进程
mysql 2505 1 0 13:47 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 2667 2505 3 13:47 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 2702 2279 0 13:47 pts/1 00:00:00 grep --color=auto mysql
[aaa@qq.com ~]# netstat -lptnu|grep mysql #查看mysql端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2667/mysqld
5. 修改配置两台mysql的主配置文件
服务器A
[aaa@qq.com ~]# vim /etc/my.cnf #修改配置文件
[aaa@qq.com ~]# cat /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin #二进制日志
[aaa@qq.com ~]# systemctl restart mariadb #重启mariadb
服务器B
[aaa@qq.com ~]# vim /etc/my.cnf #修改配置文件
[aaa@qq.com ~]# cat /etc/my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay #二进制日志
[aaa@qq.com ~]# systemctl restart mariadb #重启mariadb
6. 在mysql中新建数据库名
服务器A
[aaa@qq.com ~]# mysql #进入数据库
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database wg character set utf8 collate utf8_bin; #创建数据库wg
Query OK, 1 row affected (0.00 sec)
服务器B
[aaa@qq.com ~]# mysql #进入数据库
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database wg character set utf8 collate utf8_bin; #创建数据库wg
Query OK, 1 row affected (0.00 sec)
7. 在mysql中新建用户名并授权
服务器A
MariaDB [(none)]> grant all on *.* to aaa@qq.com'%' identified by '123'; #在主服务器建立账号tom并授权
或(两个方法都可以)
MariaDB [(none)]> grant replication slave on *.* to aaa@qq.com'%' identified by '123'; #在主服务器建立账号tom并授权
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges; #刷新权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status; #查看服务器A主的状态(记录下日志文件名和位置[从服务用])
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 482 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
服务器B
MariaDB [(none)]> stop slave; #关闭从服务器复制功能
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.194.130',master_user='tom',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=482; #做和主同步的操作
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start slave; #开启从服务器复制功能
Query OK, 0 rows affected (0.00 sec)
grant all on *.* to aaa@qq.com'%' identified by '123'; -----新建用户并授权
>服务器A
```powershell
grant all on *.* to aaa@qq.com'%' identified by '123'; -----新建用户并授权
stop slave; -----关闭从服务器复制功能
change master to master_host='192.168.179.129',master_user='tom',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=386; ------做主同步的操作
slave start; -----开启从服务器复制功能
8. 查看从服务器的状态
服务器A
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status \G; #检查从服务器复制功能
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.194.130
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 482
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #输入输出同步读写
Slave_SQL_Running: Yes
出现双Yes主从同步成功！！！
二、 读写分离操作
1. 关闭防火墙和selinux
[aaa@qq.com ~]# systemctl stop firewalld #关闭防火墙
[aaa@qq.com ~]# setenforce 0
2. 上传jdk包和amoeba包
jdk包
链接: https://pan.baidu.com/s/1HY5TUEuAlYSSibkC4gLXjw 提取码: h9t3 复制这段内容后打开百度网盘手机App，操作更方便哦
amoeba包
链接: https://pan.baidu.com/s/1xUBwMaJEoaHoJZZkCxykxQ 提取码: xad3 复制这段内容后打开百度网盘手机App，操作更方便哦
3. 安装jdk
[aaa@qq.com ~]# rpm -ivh jdk-8u20-linux-x64.rpm
准备中... ################################# [100%]
正在升级/安装...
1:jdk1.8.0_20-2000:1.8.0_20-fcs ################################# [100%]
Unpacking JAR files...
rt.jar...
jsse.jar...
charsets.jar...
tools.jar...
localedata.jar...
jfxrt.jar...
vim /etc/profile #编辑文件(在下面添加下面内容)
export JAVA_HOME=/usr/java/jdk1.8.0_20/
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
[aaa@qq.com ~]# source /etc/profile #重新加载
[aaa@qq.com ~]# java -version #查看java版本
java version "1.8.0_20"
Java(TM) SE Runtime Environment (build 1.8.0_20-b26)
Java HotSpot(TM) 64-Bit Server VM (build 25.20-b23, mixed mode)
4. 安装amoeba
[aaa@qq.com ~]# mkdir /usr/local/amoeba #创建目录amoeba
[aaa@qq.com ~]# tar xzf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ #解包到/usr/local/amoeba目录中
[aaa@qq.com ~]# cd /usr/local/amoeba/bin/ #进入执行目录
[aaa@qq.com bin]# vim amoeba #编辑文件
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k" #58行128k改为256k
[aaa@qq.com bin]# ./amoeba #安装amoeba成功
amoeba start|stop
5. 分别给主和从服务器开放权限给amoeba访问
服务器A
MariaDB [(none)]> grant all on *.* to aaa@qq.com'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
服务器B
MariaDB [(none)]> grant all on *.* to aaa@qq.com'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
6. 修改amoeba配置文件
[aaa@qq.com ~]# vim /usr/local/amoeba/conf/amoeba.xml
<property name="user">amoeba</property> #修改30行连接amoeba代理服务器的用户名(自定义)
<property name="password">123456</property> #修改32行连接amoeba代理服务器的密码(自定义)
[aaa@qq.com ~]# vim /usr/local/amoeba/conf/amoeba.xml
<property name="defaultPool">master</property> #修改115行默认地址池名字
<property name="writePool">master</property> #修改118行默认地址池写的名字(取消注释)
<property name="readPool">slaves</property> #修改119行默认地址池读的名字
<property name="needParse">true</property>
</queryRouter>
[aaa@qq.com ~]# vim /usr/local/amoeba/conf/dbServers.xml #修改配置文件(对应后端mysql服务器配置)
<property name="user">test</property> #开放给amoeba的用户
<property name="password">123</property> #开放给amoeba的密码(删除注释)
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.194.130</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.194.131</property>
</factoryConfig>
</dbServer>
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3= HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1</property>
</poolConfig>
7. 启动amoeba
[aaa@qq.com ~]# cd /usr/local/amoeba/bin/
[aaa@qq.com bin]# ./amoeba start& #启动amoeba
[1] 4905
[aaa@qq.com bin]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-11-04 15:09:59,655 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-11-04 15:10:00,849 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-11-04 15:10:00,863 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:11663.
[aaa@qq.com bin]# netstat -lptnu|grep 8066 #查看amoeba是否启动
tcp6 0 0 :::8066 :::* LISTEN 4905/java
8. 在amoeba服务器登录mysql
[aaa@qq.com bin]# mysql -uamoeba -p123456 -h 192.168.194.132 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1532793943
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MariaDB Server
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)]>
测试
- 在amoeba上创建数据库wu,主从服务器都能同步
amoeba服务器
MySQL [(none)]> create database wu;
Query OK, 1 row affected (0.03 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wg |
| wu |
+--------------------+
6 rows in set (0.02 sec)
服务器A
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wg |
| wu |
+--------------------+
6 rows in set (0.00 sec)
服务器B
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wg |
| wu |
+--------------------+
6 rows in set (0.00 sec)
- 在从服务器插入数据,amoeba服务器可以同步
服务器B
MariaDB [(none)]> use wu;
Database changed
MariaDB [wu]> create table stu(id int(10),name varchar(10));
Query OK, 0 rows affected (0.01 sec)
MariaDB [wu]> insert into stu values(1,'tom');
Query OK, 1 row affected (0.01 sec)
MariaDB [wu]> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
1 row in set (0.00 sec)
amoeba服务器
MySQL [(none)]> use wu;
Database changed
MySQL [wu]> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
1 row in set (0.02 sec)
关闭从服务器同步，在amoeba插入数据，在主可以查看(主写从读),从和amoeba数据都为空
上一篇： Java多态性
下一篇： centos7.4安装redis