mysql主从分离技术_MySQL的主从复制与读写分离技术

摘要

一、案例前置知识点分析

二、案例拓扑图

三、案例实施主从复制

3.1、MySQL(主)

3.2、MySQL2(从)

3.3、MySQL3(从)

3.4、测试

四、案例实施读写分离

摘要

在企业应用中，成熟的业务通常数据量都比较大，单台MySQL在安全性、高可用性和高并发方面都无法满足实际的要求，

配置多台主从数据库服务器以实现读写分离

一、案例前置知识点分析

1.1、MySQL主从复制原理

1.1.1、MySQL的复制类型

①基于语句的复制

②基于行的复制

③混合类型的复制

1.1.2、MySQL主从复制的工作过程

1.2、MySQL读写分离原理

1.2.1、只在主服务器上写，只在从服务器上读

1.2.2、主数据库处理事务性查询，从数据库处理SELECT查询

1.2.3、数据库复制用于将是事务性查询的变更同步到集群中的从数据库

1.2.4、读写分离方案

①基于程序代码内部实现

②基于中间代理层实现

MySQL-Proxy

Amoeba

1.3、复制的基本过程如下

①Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中；

②Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；

③Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

④Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚地告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；

⑤Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行

二、案例拓扑图

三、案例实施主从复制

3.1、MySQL(主)

3.1.1、配置NTP服务器

1 yum -y install ntp ntpdate

2 ntpdate ntp.aliyun.com

3 date

4 which ntpdate

5 crontab -e

6 */30 * * * * /usr/sbin/ntpdate ntp.aliyun.com

7 vi /etc/ntp.conf

8 restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap #17行修改

9 21-24行删除

10 server 127.127.1.0 #在删除的位置添加

11 fudge 127.127.1.0 stratum 8

12 systemctl start ntpd

13 systemctl enable ntpd14 systemctl start crond15 systemctl enable crond16 crontab -l #查看任务计划

3.1.2、手工编译安装MySQL(之前的博客上有关于MySQL的具体安装步骤)

3.1.3、配置master主服务器的MySQL

1 vi /etc/my.cnf

2 server-id = 1

3 log_bin=master_bin #在server-id下面添加两行

4 log_slave_updates=true

5 systemctl restart mysqld.service

6 mysql -u root -p123456

7 mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'abc123';

8 mysql> show grants for 'myslave'@'20.0.0.%';

9 mysql> flush privileges;

10 mysql> show master status; #查完要记住position列的值

3.2、MySQL2(从)

3.2.1、配置时间同步

1 yum -y install ntp ntpdate

2 ntpdate 20.0.0.10

3 date

4 which ntpdate

5 crontab -e

6 */30 * * * * /usr/sbin/ntpdate 20.0.0.10

7 systemctl start ntpd

8 systemctl enable ntpd

9 systemctl start crond

10 systemctl enable crond11 crontab -l #查看任务计划

3.2.2、配置MySQL从服务器

1 vi /etc/my.cnf

2 server-id = 2

3 relay_log=relay-log-bin #添加日志

4 relay_log_index=slave-relay-bin.index

5 systemctl restart mysqld

6 mysql -uroot -p123456

7 mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master_bin.000002',master_log_pos=154;

8 mysql> start slave;

9 mysql> show slave status \G;

10 ......

11 Slave_IO_Running: Yes

12 Slave_SQL_Running: Yes

13 ##如果显示NO，①主服务器数据库更新重启一下，再进入数据库，打show master status;记录日志和Position值②再在从服务器上打mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master_bin.000002',master_log_pos=154;

3.3 MySQL3(从)

3.3.1、配置时间同步

1 yum -y install ntp ntpdate

2 ntpdate 20.0.0.10

3 date

4 which ntpdate

5 crontab -e

6 */30 * * * * /usr/sbin/ntpdate 20.0.0.10

7 systemctl start ntpd

8 systemctl enable ntpd

9 systemctl start crond

10 systemctl enable crond

11 crontab -l #查看任务计划

3.2.2、配置MySQL从服务器

1 vi /etc/my.cnf

2 server-id = 3

3 relay_log=relay-log-bin #添加日志

4 relay_log_index=slave-relay-bin.index

5 systemctl restart mysqld

6 mysql -uroot -p123456

7 mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master_bin.000002',master_log_pos=154;

8 mysql> start slave;

9 mysql> show slave status \G;

10 ......

11 Slave_IO_Running: Yes

12 Slave_SQL_Running: Yes

13 ##如果显示NO，①主服务器数据库更新重启一下，再进入数据库，打show master status;记录日志和Position值②再在从服务器上打mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master_bin.000002',master_log_pos=154;

3.4、测试

3.4.1、MySQL(主)服务器上创建一个数据库

1 mysql -u root -p123456

2 mysql> create database abc;

3 mysql> show databases;

3.4.2、MySQL2(从)和MySQL3(从)上查看

1 mysql -u root -p123456

2 mysql> show databases;

四、案例实施读写分离

4.1、在三台MySQL数据库中为amoeba授权

1 mysql> grant all privileges on *.* to 'test'@'20.0.0.%' identified by 'abc123';

2 mysql> show grants for test@'20.0.0.%';

3 mysql> flush privileges;

4.2、在amoeba服务器上安装JDK

1 tar zxf jdk-8u91-linux-x64.tar.gz

2 mv jdk1.8.0_91/ /usr/local/java

3 vi /etc/profile

4 export JAVA_HOME=/usr/local/java #末尾添加

5 export JRE_HOME=/usr/local/java/jre

6 export AMOEBA_HOME=/usr/local/amoeba

7 export PATH=$PATH:$JAVA_HOME/bin:$AMOEBA_HOME/bin

8 source /etc/profile

9 java -version

4.3、在amoeba服务器上安装amoeba

1 unzip amoeba-mysql-3.0.5-RC-distribution.zip

2 mv amoeba-mysql-3.0.5-RC /usr/local/amoeba

3 chmod -R 755 /usr/local/amoeba/

4 vi /usr/local/amoeba/jvm.properties

5 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPerSize=96m" #32行添加

6 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

7 cd /usr/local/amoeba/bin/

8 launcher

9 netstat -anpt | grep 8066

4.4、修改amoeba配置文件

1 vi /usr/local/amoeba/conf/amoeba.xml

2 test #28行修改

3 123456 #30行修改

4 ......

5

6 ${amoeba.home}/conf/functionMap.xml

7 1500

8 master #修改

9 master #修改

10 slaves #修改

11 true

12

13 vi /usr/local/amoeba/conf/dbServers.xml

14 ......

15 test #26行修改

16 abc123 #28行修改

17 ......

18 #修改部分参数

19

20

21 20.0.0.10

22

23

24

25

26

27

28 20.0.0.20

29

30

31

32

33

34

35 20.0.0.30

36

37

38

39

40

41

42

43 1

44

45

46 slave1,slave2

47

48

49 cd /usr/local/amoeba/bin/

50 launcher

4.5、在客户机上测试

1 yum -y install mariadb*

2 systemctl start mariadb

3 netstat -anpt | grep 3306

4 mysql -utest -p123456 -h 20.0.0.40 -P 8066

4.6、测试

4.6.1、在master新建库和表

1 mysql> create database test;

2 mysql> use test;

3 mysql> create table abc(id int(2) not null primary key, name varchar(48), score char(48));

4 mysql> show tables;

4.6.2、在两台从服务器上看是否有库及表生成

1 mysql> show databases;

2 mysql> use test;

3 mysql> show tables;

4 mysql> select * from abc;

4.6.3、在两台从服务器上停止服务

1 mysql> stop slave;

4.6.4、在主服务上插入内容不会同步(通过amoeba操作)

主服务器

1 mysql> insert into abc values(2,'lisi',89);

从服务器

1 mysql> select * from abc;

4.6.5、在从服务器1上直接插入内容

1 mysql> insert into abc values(3,'wangwu',60);

2 mysql> select * from abc;

4.6.6、在从服务器2上直接插入内容

1 mysql> insert into abc values(4,'bb',68);

2 mysql> select * from abc;

4.6.7、在客户端上测试(第一次向从服务器1读数据，第二次向从服务器2读数据)

1 MySQL [test]> select * from abc;

2 MySQL [test]> select * from abc;

4.6.8、在通过客户端连接数据库后写入的数据只有主会记录，然后同步给从-从服务器，从而实现读写分离

1 MySQL [test]> insert into abc values(5,'cc',90); #客户机

2 mysql> select * from abc; #主

3 mysql> select * from abc; #从1

4 mysql> select * from abc; #从2

4.6.9、在客户端上看不到新插入的数据，因为同步没有开启，只有主服务器上可以看到数据

1 MySQL [test]> select * from abc；

2 MySQL [test]> select * from abc；

4.6.10、在开启同步后，主服务器上的数据会同步到各从服务器上中，但从服务器上的自己增加的数据不会同步，只有本地保存

1 #从1

2 mysql> start slave;

3 mysql> select * from abc;

4 #从2

5 mysql> start slave;

6 mysql> select * from abc;

7 客户端：

8 MySQL [test]> select * from abc;