使用Mysql读写分离和主从复制的原因:
*单台MySQL服务器的安全性,高可用性,高并发都是不满足实际的生产需要。
介绍:
1.MySQL-Proxy:通过自带的lua脚本进行SQL判断;
2.Amoeba:陈思儒开发,运用java开发不支持事务和存储过程。
MySQL读写分离:
1)基于程序代码内部实现:根据select,insert进行路由分类,这类方法也是目前生产环境应用最为广泛的,优点是性能好;
2)基于中间代理层实现:代理一般位于客户端和服务器之间,代理服务器街道客户端请求后通过判断厚赚翻到后端数据库。
MySQL支持的复制类型:
1)基于语句复制:在主服务器上执行MySQL语句,在从服务器上执行同样的语句;
2)基于行的复制:把改变内容复制过去;
3)混合类型的复制:默认采用语句的复制,一旦发现基于语句无法精确复制是,就会采用基于行的复制。
实验环境:
一台Master服务器,两台Slavel服务器(已经安装Mysql服务),一台Anoeba服务器,一台客户端,安装MySQL服务
实验步骤:
1.主从复制
主服务器)
[root@Master ~]# yum -y install ntp #建立时间同步
[root@Master ~]# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
:wq!
[root@Master ~]# service ntpd restart
[root@Master ~]# systemctl restart ntpd
[root@Master ~]# systemctl enabled ntpd
[root@Slave ~]# yum -y install ntpdate
[root@Slave ~]# ntpdate 192.168.3.1
###开始配置主服务器
[root@Master ~]# vim /etc/my.cnf
server-id = 11 #修改
log_bin = master-bin #修改
log-slave-updates = true #增加
:wq!
[root@Master ~]# systemctl restart mysqld
[root@Master ~]# mysqladmin -u root password 'pwd123'
[root@Master ~]# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.3.%' IDENTIFIED BY '123456'; #登陆MySQL程序,给从服务器授权
mysql> FLUSH PRIVILEGES;
mysql> show master status;
从服务器)
[root@Slave1 ~]# vim /etc/my.cnf
server_id = 22
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq!
[root@Slave1 ~]# systemctl restart mysqld.service
[root@Slave1 ~]# mysql -u root -p
#下面配置要与主服务器保持一致#
mysql> change master to master_host='192.168.3.1',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;
mysql> start slave;
mysql> show slave status\G
...
Slave_IO_Running: Yes #全为Yes即为配置成功
Slave_SQL_Running: Yes
测试:
在主服务器上创建一个库,到从服务器上查看。
2.读写分离
[root@Amoeba ~]# chmod +x jdk-6u14-linux-x64.bin #修改java版本
[root@Amoeba ~]# ./jdk-6u14-linux-x64.bin
[root@Amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@Amoeba ~]# 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
:wq!
[root@Amoeba ~]# source /etc/profile
[root@Amoeba ~]# java -version
[root@Amoeba ~]# mkdir /usr/local/amoeba
[root@Amoeba ~]# tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@Amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@Amoeba ~]# /usr/local/amoeba/bin/amoeba #安装Amoeba
####显示“amoeba start|stop”表示安装成功
---------------------------------------
[root@Master ~]# mysql -u root -p
mysql> GRANT ALL ON *.* TO test@'192.168.3.%' identified by '123.com'; #在每台MySQL服务器上,开放权限给Amoeba访问
---------------------------------------
[root@Amoeba ~]# cd /usr/local/amoeba/
[root@Amoeba amoeba]# vim conf/amoeba.xml
....
<property name="user">amoeba</property> #修改
<property name="password">123456</property> #修改
....
<property name="defaultPool">master</property> #修改
<property name="writePool">master</property> #修改
<property name="readPool">slaves</property>
....
:wq!
[root@Amoeba ~]# vim conf/dbServers.xml
....
<!-- 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">192.168.3.1</property> #修改
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer"> #修改
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.3.2</property> #修改
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer"> #修改
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.3.3</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,slave2</property> #修改
</poolConfig>
</dbServer>
</amoeba:dbServers>
[root@Amoeba ~]# cd /usr/local/amoeba/bin/
[root@Amoeba bin]# ./amoeba start &
[root@Amoeba ~]# netstat -tlunp | grep java
测试:
*1.先在slave(主从复制)开启的状态下,创建一个库"benet",在该库中创建一个表"zang";
--- Master ---
[root@Master ~]# mysql -u root -p
mysql> create database benet;
mysql> use benet;
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
*2.在两台Slave服务器中关掉slave,在Master中插入一条数据,并且分别在两台Slave服务器中插入两个不同的数据
--- Slave2 ---
[root@Slave1 ~]# mysql -u root -p
mysql> stop slave;
mysql> use benet;
mysql> insert into zang values('2','zhang','Slave1');
--- Slave2 ---
[root@Slave2 ~]# mysql -u root -p
mysql> stop slave;
mysql> use benet;
mysql> insert into zang values('3','zhang','Slave2');
--- Master ---
[root@Master ~]# mysql -u root -p
mysql> use benet;
mysql> insert into zang values('1','zhang','Master');
*3.在客户端上远程登陆到Amoeba,进入到"benet"库中,多次查看表"zang"的数据,如果是内容是两台Slave中的数据,则已经实现读分离;
[root@Client ~]# yum -y install mysql
[root@Client ~]# mysql -u amoeba -p123456 -h 192.168.3.4 -P8066
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| appc |
| benet |
| mysql |
| performance_schema |
| test |
+--------------------+
MySQL [(none)]> use benet;
MySQL [benet]> show tables;
+-----------------+
| Tables_in_benet |
+-----------------+
| zang |
+-----------------+
MySQL [benet]> select * from zang;
+------+-------+---------+
| id | name | address |
+------+-------+---------+
| 2 | zhang | Slave1 |
+------+-------+---------+
MySQL [benet]> select * from zang;
+------+-------+---------+
| id | name | address |
+------+-------+---------+
| 3 | zhang | Slave2 |
+------+-------+---------+
*4.在客户端操作,在库"benet"中的表"zang"中插入一条数据,之后在Master和Slave中查看各自表"zang"中的内容,如果只有Master中的表"zang"有Client端插入的数据,则表明实现写分离。
--- Client ---
MySQL [benet]> insert into zang values('4','zang','Write');
--- Master ---
mysql> use benet;
mysql> select * from zang;
+------+-------+---------+
| id | name | address |
+------+-------+---------+
| 1 | zhang | Master |
| 4 | zang | Write |
+------+-------+---------+
--- Slave1,2 ---
mysql> use benet;
mysql> select * from zang;
+------+-------+---------+
| id | name | address |
+------+-------+---------+
| 3 | zhang | Slave2 |
+------+-------+---------+