MySQL 主从复制与读写分离

一、案例概述

• 在企业应用中，成熟的业务通常数据量都比较大
  
• 单台MySQL在安全性、高可用性和高并发方面都无法满意实际的需求
  
• 配置多台主从数据库服务器以实现读写方法
  

二、案例前置知识点

1、MySQL 主从复制原理

• MySQL 的复制类型
  
  
  
  • 基于语句的复制
    
    
    
    • 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。
      
    • MySQL 默认接纳基于语句的复制，服从比较高。
      
    
    
  • 基于行的复制
    
    
    
    • 把改变的内容复制已往，而不是把命令在从服务器上执行一遍。
      
    
    
  • 混淆类型的复制
    
    
    
    • 默认接纳基于语句的复制，一旦发现基于语句无法精确复制时，就会接纳基于行的复制。
      
    
    
  
  
• MySQL 主从复制的工作过程
  
  
  
  2、MySQL读写分离原理
  
• 只在主服务器上写，只在从服务器上读
  
• 主数据库处理事务性查询，从数据库处理SELECT查询
  
• 数据库复制用于将事务性查询的变动同步到集群中的数据库
  
• 读写分离方案
  
  
  
  • 基于步调代码内部实现
    
  • 基于中间署理层实现
    
    
    
    • MySQL-Proxy
      
    • Amoeba
      
      
      
      
    
    
  
  

实验报告

资源列表

主机操纵系统IP配置masterCentOS7192.168.72.1312C4Gslave1CentOS7192.168.72.1322C4Gslave2CentOS7192.168.72.1332C4GamoebaCentOS7192.168.72.1342C4GclientCentOS7192.168.72.1302C4G 基础情况

• 关闭防火墙
  

1. systemctl stop firewalld
2. systemctl disable firewalld

复制代码

• 关闭内核安全机制
  

1. setenforce 0
2. sed -i "s/^SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config

复制代码

• 修改主机名
  

1. hostnamectl set-hostname master
2. hostnamectl set-hostname slave1
3. hostnamectl set-hostname slave2
4. hostnamectl set-hostname amoeba
5. hostnamectl set-hostname client

复制代码

一、搭建 MySQL 主从复制

1、安装 MySQL 数据库

1. # 上传脚本
2. [root@master ~]# tar zxf auto-install-mysql57-glibc.tar.gz
3. [root@master ~]# cd auto-install-mysql57-glibc
4. [root@master auto-install-mysql57-glibc]# ls
5. auto-install-mysql57-glibc.sh  my.cnf  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
6. [root@master auto-install-mysql57-glibc]# ./auto-install-mysql57-glibc.sh
8. set password=password('123');

复制代码

2、配置 master 主服务器

master

1. # 开启二进制日志
2. [root@master ~]# vi /etc/my.cnf
3. [mysqld]
4. log-bin=master-bin
5. binlog-format = MIXED
6. server-id = 1
7. # 重启 MySQL 服务
8. [root@master ~]# systemctl restart mysqld
10. [root@master ~]# ls /usr/local/mysql/data/
11. auto.cnf         client-key.pem  ib_logfile1        master.err  mysql.sock.lock     server-cert.pem
12. ca-key.pem       ib_buffer_pool  ibtmp1             master.pid  performance_schema  server-key.pem
13. ca.pem           ibdata1         master-bin.000001  mysql       private_key.pem     sys
14. client-cert.pem  ib_logfile0     master-bin.index   mysql.sock  public_key.pem
16. # 登录 MySQL 程序，给从服务器授权
17. mysql> grant replication slave on *.* to 'myslave'@'192.168.72.%' identified by '123456';
18. Query OK, 0 rows affected, 1 warning (0.02 sec)
19. # 刷新权限
20. mysql> flush privileges;
21. Query OK, 0 rows affected (0.03 sec)
23. # MySQL 返回了主服务器的二进制日志（binary log）的当前状态
24. mysql> show master status;
25. +-------------------+----------+--------------+------------------+-------------------+
26. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
27. +-------------------+----------+--------------+------------------+-------------------+
28. | master-bin.000001 |      603 |              |                  |                   |
29. +-------------------+----------+--------------+------------------+-------------------+
30. 1 row in set (0.03 sec)

复制代码

3、配置 slave 从服务器

slave1

1. # 开启二进制日志
2. [root@slave1 ~]# vi /etc/my.cnf
3. [mysqld]
4. log-bin=slave1-bin
5. binlog-format = MIXED
6. server-id = 2
7. # 重启 MySQL 服务
8. [root@slave1 ~]# systemctl restart mysqld
9. [root@slave1 ~]# ls /usr/local/mysql/data/
10. auto.cnf         client-key.pem  ib_logfile1  mysql.sock.lock     server-cert.pem    slave1.err
11. ca-key.pem       ib_buffer_pool  ibtmp1       performance_schema  server-key.pem     slave1.pid
12. ca.pem           ibdata1         mysql        private_key.pem     slave1-bin.000001  sys
13. client-cert.pem  ib_logfile0     mysql.sock   public_key.pem      slave1-bin.index
15. # MySQL复制环境中用于配置从服务器（slave）以连接到主服务器（master）并开始复制过程
16. mysql> change master to master_host='192.168.72.131',        # master 主机的IP
17.     -> master_user='myslave',        # master 主机设置的用户名
18.     -> master_password='123456',        # master 主机设置的密码
19.     -> master_log_file='master-bin.000001',
20.         # master 主机的日志文件
21.     -> master_log_pos=603;
22.         # master 主机的偏移量 当前二进制日志的位置
23. Query OK, 0 rows affected, 2 warnings (0.04 sec)
25. mysql> start slave;
26. Query OK, 0 rows affected (0.01 sec)
28. # 检查从服务器的复制状态
29. mysql> show slave status\G;
30. *************************** 1. row ***************************
31. ...... # 省略部分代码
32.              Slave_IO_Running: Yes
33.             Slave_SQL_Running: Yes
34.                      
35. ...... # 省略部分代码
36. 1 row in set (0.00 sec)
38. # 如果出现错误，执行重置重新写
39. # 关闭   
40. stop slave;
41. # 重置
42. reset slave;

复制代码

slave2

1. # 开启二进制日志
2. [root@slave2 ~]# vi /etc/my.cnf
3. [mysqld]
4. log-bin=slave2-bin
5. binlog-format = MIXED
6. server-id = 3
7. # 重启 MySQL 服务
8. [root@slave2 ~]# systemctl restart mysqld
10. [root@slave2 ~]# ls /usr/local/mysql/data/
11. auto.cnf         client-key.pem  ib_logfile1  mysql.sock.lock     server-cert.pem    slave2.err
12. ca-key.pem       ib_buffer_pool  ibtmp1       performance_schema  server-key.pem     slave2.pid
13. ca.pem           ibdata1         mysql        private_key.pem     slave2-bin.000001  sys
14. client-cert.pem  ib_logfile0     mysql.sock   public_key.pem      slave2-bin.index
16. # MySQL复制环境中用于配置从服务器（slave）以连接到主服务器（master）并开始复制过程
17. mysql> change master to master_host='192.168.72.131',        # master 主机的IP
18.     -> master_user='myslave',        # master 主机设置的用户名
19.     -> master_password='123456',        # master 主机设置的密码
20.     -> master_log_file='master-bin.000001',
21.         # master 主机的日志文件
22.     -> master_log_pos=603;
23.         # master 主机的偏移量    当前二进制日志的位置
24. Query OK, 0 rows affected, 2 warnings (0.04 sec)
26. mysql> start slave;
27. Query OK, 0 rows affected (0.01 sec)
29. # 检查从服务器的复制状态
30. mysql> show slave status\G;
31. *************************** 1. row ***************************
32. ...... # 省略部分代码
33.              Slave_IO_Running: Yes
34.             Slave_SQL_Running: Yes
35.                      
36. ...... # 省略部分代码
37. 1 row in set (0.00 sec)
39. # 如果出现错误，执行重置重新写
40. # 关闭   
41. stop slave;
42. # 重置
43. reset slave;
44.    

复制代码

4、验证主从复制效果

1. # 在主、从服务器上登录 MySQL
2. mysql -uroot -p123
3. # 在主服务器上新建数据库 kgc
4. mysql> create database kgc;
5. Query OK, 1 row affected (0.01 sec)
8. # 在两台从服务器上分别查看数据库，显示数据库相同，则主从复制成功
9. mysql> show databases;
10. +--------------------+
11. | Database           |
12. +--------------------+
13. | information_schema |
14. | kgc                |
15. | mysql              |
16. | performance_schema |
17. | sys                |
18. +--------------------+
19. 5 rows in set (0.00 sec)

复制代码

二、搭建 MySQL 读写分离

1、在 Amoeba 主机上安装 Java 情况

1. [root@master ~]# chmod +x jdk-6u14-linux-x64.bin
2. [root@master ~]# ./jdk-6u14-linux-x64.bin        //根据提示按 Enter 键完成即可
3. [root@master ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
6. [root@master ~]# vi /etc/profile
7. cat >> /etc/profile << 'EOF'
8. export JAVA_HOME=/usr/local/jdk1.6
9. export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
10. export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
11. export AMOEBA_HOME=/usr/local/amoeba/
12. export PATH=$PATH:$AMOEBA_HOME/bin
13. EOF
14. # 刷新环境变量
15. source /etc/profile
17. # 查看版本号
18. [root@master ~]# java -version
19. java version "1.6.0_14"
20. Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
21. Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

复制代码

2、安装并配置 Amoeba 软件

1. mkdir /usr/local/amoeba
2. tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
3. chmod -R 755 /usr/local/amoeba/
5. # 显示此内容说明 Amoeba 安装成功
6. [root@amoeba ~]# amoeba
7. amoeba start|stop

复制代码

3、配置 Amoeba 读写分离，两个 Slave 读负载均衡

1. SHOW GRANTS;    # 查看所有权限
2. select user from mysql.user;    # 查看所有用户
4. delete from mysql.user where user='test';    # 删除指定test用户
6. # (1)master、slave1、slave2 中开放权限给 Amoeba 访问
8. # 在 master 节点操作
9. # 登录 MySQL 程序，给从服务器授权
10. grant all on *.* to test@'192.168.72.%' identified by '123.com';
11. # 刷新权限
12. flush privileges;
13. # 创建 test 数据库
14. create database test;
16. # 在 amoeba 节点操作
17. # (2)编辑 amoeba.xml 配置文件
18. [root@master ~]# cd /usr/local/amoeba/conf
19. [root@master conf]# vi amoeba.xml
20. # 在 30 行左右修改 user 和 password
21. ...... # 省略部分代码
22. <property name="user">amoeba</property>
23. <property name="password">123456</property>
24. ...... # 省略部分代码
25. # 在 115 行左右修改 defaultPool、writePool、readPool
26. ...... # 省略部分代码
27. <property name="defaultPool">master</property>
29. <property name="writePool">master</property>
30. <property name="readPool">slaves</property>
31. ...... # 省略部分代码
33. # (3)编辑 dbServers.xml 配置文件
34. [root@master conf]# vi dbServers.xml
35. # 在 26 行左右修改 user 和 password 写在master数据库给权限的用户和密码
36. ...... # 省略部分代码
37. <!-- mysql user -->
38. <property name="user">test</property>
39. <!--  mysql password
40. -->
41. <property name="password">123.com</property>
42. ...... # 省略部分代码
43. # 在 45 行左右修改以下内容
44. ...... # 省略部分代码
45.         <dbServer name="master"  parent="abstractServer">
46.                 <factoryConfig>
47.                         <!-- mysql ip -->
48.                         <property name="ipAddress">192.168.72.131</property>
49.                 </factoryConfig>
50.         </dbServer>
52.         <dbServer name="slave1"  parent="abstractServer">
53.                 <factoryConfig>
54.                         <!-- mysql ip -->
55.                         <property name="ipAddress">192.168.72.132</property>
56.                 </factoryConfig>
57.         </dbServer>
59.         <dbServer name="slave2"  parent="abstractServer">    # 若无slave2 段，手动添加
60.                 <factoryConfig>
61.                         <!-- mysql ip -->
62.                         <property name="ipAddress">192.168.72.133</property>
63.                 </factoryConfig>
64.         </dbServer>
65. ...... # 省略部分代码
66. # 在 66 行左右修改以下内容
67. ...... # 省略部分代码
68.         <dbServer name="slaves" virtual="true">
69.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
70.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
71.                         <property name="loadbalance">1</property>
72.         
73.                         <!-- Separated by commas,such as: server1,server2,server1 -->
74.                         <property name="poolNames">slave1,slave2</property>
75.                 </poolConfig>
76.         </dbServer>        
77. ...... # 省略部分代码
79. # (4)配置无误后，可以启动 Amoeba 软件，其默认端口为 tcp 8066
80. [root@amoeba ~]# amoeba start &
81. [1] 9705
82. [root@amoeba ~]# netstat -anpt | grep 8066
83. tcp6       0      0 :::8066                 :::*                    LISTEN      9705/java              

复制代码

4、测试

1. # (1)在 client 主机上安装mariadb
2. yum -y install mariadb
4. # (2)登录
5. # IP地址和端口是 amoeba 的地址和端口
6. mysql -u amoeba -p123456 -h 192.168.72.134 -P 8066
8. # (3)在 Master 上创建一个表，同步到各从服务器上，然后关掉各从服务器的 Slave功能，再插入区别语句。
9. # 在 master节点创建表
10. use test;
11. CREATE TABLE `kgc` (
12.     `id` int(10) NOT NULL,
13.     `name` VARCHAR(10) DEFAULT NULL
14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15. # slave节点执行
16. stop slave;
17. # master节点执行
18. insert into kgc values(1,'张三');
19. # slave1节点执行
20. insert into kgc values(2,'李四');
21. # slave2节点执行
22. insert into kgc values(3,'王五');
24. # (4)测试读操作
25. # 第一次查询
26. MySQL [(none)]> select * from test.kgc;
27. +----+--------+
28. | id | name   |
29. +----+--------+
30. |  3 | 王五   |
31. +----+--------+
32. 1 row in set (0.03 sec)
33. # 第二次查询
34. MySQL [(none)]> select * from test.kgc;
35. +----+--------+
36. | id | name   |
37. +----+--------+
38. |  2 | 李四   |
39. +----+--------+
40. 1 row in set (0.01 sec)
41. # 第三次查询
42. MySQL [(none)]> select * from test.kgc;
43. +----+--------+
44. | id | name   |
45. +----+--------+
46. |  3 | 王五   |
47. +----+--------+
48. 1 row in set (0.00 sec)
50. # (5)测试写操作
51. # 在client主机上插入一条数据
52. insert into kgc values(4,'张涛');
53. # 但在 Client 上查询不到，最终只有在 Master 上才能查看到这条语句内容，说明写操作在 Master 服务器上
54. MySQL [(none)]> select * from test.kgc;
55. +----+--------+
56. | id | name   |
57. +----+--------+
58. |  2 | 李四   |
59. +----+--------+
60. 1 row in set (0.00 sec)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。