mysql(mariadb)读写分离部署

我还能再学点

已于 2023-06-29 08:49:11 修改

阅读量712

点赞数

文章标签：运维 linux centos

于 2023-06-28 20:50:15 首次发布

本文链接：https://blog.csdn.net/youcan_doit/article/details/131349768

版权

3.登录mysql创建replication

4.从服务器登录验证

5.获得master服务器 DB的相关信息

6.备份master原有数据

7.修改slave1、slave2配置

8. 进入msql执行下列命令，slave1、slave2开启主从复制，

四、部署mysql读写分离

1.在amoeba服务器（192.168.1.138）安装java 1.6版本过高的版本，amoeba无法兼容

2.下载amoeba tar包

3.在master、slave1、slave2服务器中配置amoeba的访问权限

一、原理

一台主服务器，多台从数据服务器，主服务器开启日志功能，且建立一个允许从数据服务器访问的账号，从数据服务器开启从服务，主服务器所以产生的数据记录在日志文件里保存到本地磁盘，当日志文件发生变化时，从数据服务器通过IO线程同步变化的数据，把变化的二进制文件传到从数据服务器并告知主数据服务数据没有问题然后保存到本地磁盘，从数据服务器和主数据服务有一样的数据，所以当读取数据时通过SQL线程调用从数据服务器，而主服务器只负责写，减轻主数据服务器压力。

二、准备环境

五台服务器

master服务器192.168.1.12、slave1服务器192.168.1.133、slave2服务器192.168.1.134

amoeba服务器192.168.1.138、localhost服务器192.168.1.141

五台服务器都关闭防火墙，同步时间

[root@localhost ~] systemctl stop firewalld
[root@localhost ~] setenforce 0
[root@localhost ~] iptables -F
[root@localhost ~] yum -y install ntp
[root@master ~] systemctl start ntpd
[root@master ~] ntpdate 192.168.1.12
28 Jun 14:26:12 ntpdate[8815]: the NTP socket is in use, exiting

生产环境中最好将ntpdate 192.168.1.12 写入周期计划任务每天执行一下，时间不同步会造成数据缺失！

三、部署mysql主从复制

1.五台服务器下载mariadb

[root@localhost ~] yum -y install mariadb mariadb-server

2.修改master配置文件，重启数据库

[root@master ~] vim /etc/my.cnf
server-id=1  #编号
log-bin=mysql-binlog   #启动日志
log-slave-updates=true  #开启主从复制

3.登录mysql创建replication（主从复制功能），将slave作为从服务器

[root@master ~] mysql
MariaDB [(none)]> grant replication slave on*.* to 'myslave'@'192.168.1.%'identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;  #刷新
Query OK, 0 rows affected (0.00 sec)

4.从服务器登录验证

[root@slave1 ~] mysql -umyslave -p123456 -h192.168.1.12
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-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)]>

5.获得master服务器 DB的相关信息

MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000003 |  1029164 |              |                  |
+---------------------+----------+--------------+------------------+

6.备份master原有数据

[root@master ~] mysqldump -uroot --all-databases >/root/alldbbackup.sql #因为主从同步从开启同步的节点开始同步，之前的数据没有
[root@master ~] scp /root/alldbbackup.sql root@192.168.1.133: /root/
alldbbackup.sql                                                  100%  503KB  68.1MB/s   00:00   #通过scp传输给slave，slave服务器导入到 本地数据库
[root@master ~] mysql -uroot -p </root/alldbbackup.sql    #将数据库还原

7.修改slave1、slave2配置

[root@slave1 ~] vim /etc/my.cnf
[mysqld]
server-id=2  #编号
relay-log=relay-log-bin #中继日志
relay-log-index=slave-relay-bin.index #定义日志索引

[root@slave2 ~] vim /etc/my.cnf
[mysqld]
server-id=3  #编号
relay-log=relay-log-bin #中继日志
relay-log-index=slave-relay-bin.index #定义日志索引

8. 进入msql执行下列命令，slave1、slave2开启主从复制，

#slave1、slave2开启主从复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> change master to master_host="192.168.1.12", master_user="myslave", master_password="123456", master_log_file="mysql-binlog.000003", master_log_pos=1029164;
Query OK, 0 rows affected (0.01 sec)


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.12
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000003
          Read_Master_Log_Pos: 1029164
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#主从复制成功开启

四、部署mysql读写分离

1.在amoeba服务器（192.168.1.138）安装java 1.6版本，过高的版本，amoeba无法兼容

[root@amoeba ~] java -version  #确保服务器未安装java
bash: java: 未找到命令

Creating jdk1.6.0_31/jre/lib/ext/localedata.jar
Creating jdk1.6.0_31/jre/lib/plugin.jar
Creating jdk1.6.0_31/jre/lib/javaws.jar
Creating jdk1.6.0_31/jre/lib/deploy.jar

Java(TM) SE Development Kit 6 successfully installed.  #java1.6安装完成
[root@amoeba ~] mkdir /usr/local/jkd1.6   
[root@amoeba ~] mv jdk1.6.0_31/ /usr/local/jkd1.6/  #将文件全部移到jdk1.6目录中

[root@amoeba ~] vim /etc/profile  #添加变量，让jdk1.6生效
#在文件最下面添加
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin
export PATH=$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin


[root@amoeba ~] source /etc/profile
[root@amoeba ~] java -version
java version "1.6.0_31"  #成功下载
Java(TM) SE Runtime Environment (build 1.6.0_31-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)

2.下载amoeba tar包

[root@amoeba ~] ll
总用量 86672
-rw-r--r--. 1 root root  3161433 2月  29 2016 amoeba-mysql-binary-2.2.0.tar.gz
[root@amoeba ~] mkdir /usr/local/amoeba  #给amoeba创建一个目录
[root@amoeba ~] tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/  #解压到目录里
[root@amoeba ~] chmod -R 755 /usr/local/amoeba/  #递归的更改目录所有文件权限
[root@amoeba ~] vim /etc/profile   #在文件最下面添加变量
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~] source /etc/profile

3.在master、slave1、slave2服务器中配置amoeba的访问权限

#进入mysql执行下列命令
MariaDB [(none)]> grant all on *.* to 'test'@'192.168.1.138'identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

4.修改amoeba配置文件

[root@amoeba ~] vim /usr/local/amoeba/conf/amoeba.xml
 30                 <property name="user">amoeba</property>
 31 
 32                 <property name="password">123456</property>
115                 <property name="defaultPool">master</property>
116                 
117                 
118                 <property name="writePool">master</property>
119                 <property name="readPool">slaves</property>
120                 -->

5.修改数据服务配置文件

[root@amoeba ~] vim /usr/local/amoeba/conf/dbServers.xml 
 20    <property name="port">3306</property>  #修改端口  

 23    <property name="schema">test</property>   #mysql授权用户  
 26    <property name="user">test</property>  #登录用户和授权用户一致
 29    <property name="password">123.com</property>   #mysql授权的密码

 45    <dbServer name="master"  parent="abstractServer">
        
 48         <property name="ipAddress">192.168.1.12</property>  #定义masterIP地址
             


 51   #定义slave1 和 slave2
 52         <dbServer name="slave1"  parent="abstractServer">
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
 55                         <property name="ipAddress">192.168.1.133</property>
 56                 </factoryConfig>
 57         </dbServer>
 58         
 59         <dbServer name="slave2"  parent="abstractServer">
 60                 <factoryConfig>
 61                         <!-- mysql ip -->
 62                         <property name="ipAddress">192.168.1.134</property>
 63                 </factoryConfig>
 64        </dbServer>

#定义读的池slave
 65         <dbServer name="slaves" virtual="true">

 68          <property name="loadbalance">1</property> #轮询为1
 69                         
 70          <!-- Separated by commas,such as: server1,server2,server1 -->
 71          <property name="poolNames">slave1,slave2</property>  #slave1和slave2间轮询

6.放入后台启动

[root@amoeba ~] cd /usr/local/amoeba/bin/
[root@amoeba bin] ./amoeba start &
[2] 13599
[root@amoeba bin] remote application= .Amoeba:39059 response OK
amoeba server is running with port=39059
[root@amoeba bin] netstat -anptl |grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      13566/java

7.测试amoeba

[root@slave2 ~] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066  
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 926681939
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用户登录

8.测试读写分离

master创建一个数据库 slave1和slave2 自动同步

MariaDB [(none)]> create database ceshi;
Query OK, 1 row affected (0.01 sec)


MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

关闭 slave1和slave2的主从复制功能，再向master和slave1、slave2 的ceshi数据库插入表

MySQL [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

MariaDB [(none)]> create database  shiyan;
MariaDB [(none)]> use shiyan;
Database changed

在表中分别插入不同数据

MariaDB [shiyan]> CREATE TABLE customer (
    ->        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->        name VARCHAR(30) NOT NULL,
    ->        email VARCHAR(50),
    ->        created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ->    );
Query OK, 0 rows affected (0.01 sec)

MariaDB [shiyan]>  insert into customer (name,email) values ('master master','master.master@example.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | master master | master.master@example.com | 2023-06-28 20:17:16 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [shiyan]> insert into customer (name,email) values ('slave1 slave1','slave1,slave1@example.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [shiyan]> insert into customer (name,email) values ('slave2 slave2','slave2,slave2@example.com');
Query OK, 1 row affected (0.00 sec)

MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave2 slave2 | slave2,slave2@example.com | 2023-06-28 20:19:25 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

用amoeba用户登录mysql，查看表发现只能看见slave1和slave2的轮询

[root@slave2 ~]# mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066
MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave2 slave2 | slave2,slave2@example.com | 2023-06-28 20:19:25 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.01 sec)

MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

在aomeba服务器里进入数据库，插入表来测试

[root@amoeba bin] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066

MySQL [shiyan]> insert into customer (name,email) values ('slave1 slave1','slave1,slave1@example.com');
Query OK, 1 row affected (0.00 sec

#主master查看 写入了数据库中
MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | master master | master.master@example.com | 2023-06-28 20:17:16 |
|  2 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:31:56 |
+----+---------------+---------------------------+---------------------+

#slave1和slave2 查看 ，无变化 没有写入权限
MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+