理论+实验·MySQL主从复制与读写分离

最新推荐文章于 2024-05-15 22:52:13 发布

weixin_47153668

最新推荐文章于 2024-05-15 22:52:13 发布

阅读量131

点赞数 1

分类专栏： mysql数据库文章标签： MySQL 主从复制读写分离数据库 Linux

本文链接：https://blog.csdn.net/weixin_47153668/article/details/108252185

版权

mysql数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

理论+实验·MySQL主从复制与读写分离

知识点

MySQL主从复制原理

MySQL的复制类型
- 基于语句的复制
- 基于行的复制
- 混合类型的复制【日志】
MySQL主从复制的工作过程

Binary log：主数据库的二进制日志。

Relay log：从服务器的中继日志。

**第一步：**master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

**第二步：**salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

**第三步：**SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

MySQL读写分离原理

旨在主服务器上写,只在从服务器上读
主数据库处理事务性查询,从数据库处理SELECT查询
数据库复制用于将事务性查询的变更同步到集群中的从数据库
读写分离方案
- 基于程序代码内部实现
- 基于中间代理层实现
  - MySQL-Proxy
  - Amoeba

实验环境

master 20.0.0.10
slave01 20.0.0.11
slave02 20.0.0.12
amoeba 20.0.0.13
client 20.0.0.14

实验步骤

1、时间同步

//主服务器设置:20.0.0.10//
[root@master ~]# yum -y install ntp
[root@master ~]# vim /etc/ntp.conf
...
server 127.127.195.0					//本地时钟源//
fudge 127.127.195.0 stratum 8			//设置时间层级为8//
[root@master ~]# systemctl start ntpd
[root@master ~]# systemctl stop firewalld
[root@master ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multiuser.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbusorg.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
[root@master ~]# vim /etc/selinux/config
...
SELINUX=disabled
...
//从服务器设置:20.0.0.11;20.0.0.12//
[root@slave01 ~]# yum -y install ntp ntpdate
[root@slave01 ~]# systemctl start ntpd
[root@slave01 ~]# systemctl stop firewalld
[root@slave01 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave01 ~]# setenforce 0
[root@slave01 ~]# vim /etc/selinux/config
...
SELINUX=disabled
...
[root@slave01 ~]# /usr/sbin/ntpdate 20.0.0.10		//与master服务器同步//

2、master服务器配置

[root@master ~]# vim /etc/my.cnf
...
server-id = 10					//id不能相同//
log-bin=master-bin				//主服务器日志文件//
log-slave-updates=true			//从服务器更新二进制日志//
...
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -u root -p
Enter password: 
...
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;			//file与position后面需要用到//
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3、slave01与slave02服务器配置

[root@slave01 ~]# vim /etc/my.cnf
...
server-id = 11								//slave02改成12//
relay-log=relay-log-bin						//从主服务器上同步日志文件记录到本地//
relay-log-index=slave-relay-bin.index		//定义relay-log的位置和名称//
...
[root@slave01 ~]# systemctl restart mysqld
[root@slave01 ~]# mysql -u root -p
Enter password: 
...
mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;					//查看状态//
...
             Slave_IO_Running: Yes			//Yes就表示正常//
            Slave_SQL_Running: Yes			//Yes就表示正常//
...
//slave02同样的方法设置好//

4、amoeba服务器配置

//将jdk与amoeba放入tmp目录下//
[root@amoeba tmp]# systemctl stop firewalld

[root@amoeba tmp]# systemctl disable firewalld

[root@amoeba tmp]# setenforce 0

[root@amoeba tmp]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

[root@amoeba tmp]# cp jdk-6u14-linux-x64.bin /usr/local/

[root@amoeba tmp]# 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
按Enter

[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6

[root@amoeba local]# 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

[root@amoeba local]# source /etc/profile

[root@amoeba local]# mkdir /usr/local/amoeba

[root@amoeba local]# tar zxvf /tmp/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					//显示这个说明安装成功//

5、在主从服务器上添加权限开放给amoeba访问===>利用amoeba来进行读写操作

mysql> grant all on *.* to 'test'@'20.0.0.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6、回到amoeba服务器修改配置文件

[root@amoeba ~]# cd /usr/local/amoeba/

[root@amoeba amoeba]# vim conf/amoeba.xml
...
                <property name="user">amoeba</property>					//30行//
 
                <property name="password">123456</property>				//32行//
...
 				<property name="defaultPool">master</property>			//115行//
    
                    //将注释去掉//
                <property name="writePool">master</property>			
                <property name="readPool">slaves</property>
					//将注释去掉//

[root@amoeba amoeba]# vim conf/dbServers.xml
...
                       <!-- mysql schema -->
                       <property name="schema">mysql</property>//默认的数据库mysql//

                       <!-- mysql user -->
                       <property name="user">test</property>	

                       <!--  mysql password -->
                       <property name="password">123.com</property>
                        
...
        <dbServer name="master"  parent="abstractServer">		//主服务器//
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.10</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave01"  parent="abstractServer">		//从服务器01//
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.11</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave02"  parent="abstractServer">		//从服务器02//
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.12</property>
                </factoryConfig>
        </dbServer>
...
        <dbServer name="slaves" virtual="true">					//slaves//
...
 <property name="poolNames">slave01,slave02</property>			//指定两个从服务器//
...

7、client客户端配置

[root@client ~]# yum -y install mysql
[root@client ~]# mysql -u amoeba -p123456 -h 20.0.0.13 -P8066
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

8、master服务器配置

mysql> use school;
Database changed
mysql> create table test (id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)

9、slave01服务器操作

mysql> stop slave;	
Query OK, 0 rows affected (0.00 sec)

mysql> use school;
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> insert into test values (2,'slave01');
Query OK, 1 row affected (0.00 sec)

11、slave02服务器操作

mysql> stop slave;	
Query OK, 0 rows affected (0.00 sec)

mysql> use school;
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> insert into test values (3,'slave02');
Query OK, 1 row affected (0.01 sec)

12、client客户端操作

mysql> select * from school.test;
+------+---------+
| id   | name    |
+------+---------+
|    3 | slave02 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from school.test;
+------+---------+
| id   | name    |
+------+---------+
|    2 | slave01 |
+------+---------+
1 row in set (0.01 sec)
//可以从slave01与slave02服务器上取读取信息//轮询读取//

mysql> use school;
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> insert into test values (5,'write_test');
Query OK, 1 row affected (0.04 sec)

13、master服务器操作

mysql> select * from test;
+------+------------+
| id   | name       |
+------+------------+
|    5 | write_test |
+------+------------+
1 row in set (0.00 sec)
//只能在master服务器上查询到客户端写入的信息,说明实验成功//

实验结果

可以通过client客户端进行读写分离

master服务器进行写入操作

slave服务器进行读取操作

weixin_47153668

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
理论+实验·MySQL主从复制与读写分离

理论+实验·MySQL主从复制与读写分离知识点MySQL主从复制原理MySQL的复制类型基于语句的复制基于行的复制混合类型的复制【日志】MySQL主从复制的工作过程Binary log：主数据库的二进制日志。Relay log：从服务器的中继日志。**第一步：**master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。**第二步：**salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump pr
复制链接

扫一扫