理论+实验·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服务器进行读取操作