理解 MySQL 主从复制及实现读写分离的重要性
发布时间：2023-07-24 14:43:23 所属栏目：MySql教程 来源：
导读：实验介绍
在实际生产环境中，如果对数据库的读和写都在同一个数据库上操作，无论是在安全性、高可用性还是高并发性等各方面都是完全不能满足实际需求的，所以一般来说都是通过主从复制来同步数据，再通过读写分离
在实际生产环境中，如果对数据库的读和写都在同一个数据库上操作，无论是在安全性、高可用性还是高并发性等各方面都是完全不能满足实际需求的，所以一般来说都是通过主从复制来同步数据，再通过读写分离
|
实验介绍 在实际生产环境中，如果对数据库的读和写都在同一个数据库上操作，无论是在安全性、高可用性还是高并发性等各方面都是完全不能满足实际需求的，所以一般来说都是通过主从复制来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案进行部署和实施。 MySQL主从复制原理 1、在每个事务更新数据前，Master服务器在二进制日志中记录这些改变。写入二进制文件完成后，Master服务器通知存储引擎提交事务。 2、Slave服务器将Master的Binary log复制到其中继日志（Relay log）。首先Slave开始一个工作线程——I/O线程，I/O线程在Master上打开一个普通的连接，然后开始Binary log dump process。Binary log dump process从Master的二进制日志中读取时间，如果已经跟上Master，它会睡眠等待Master产生新的事件。I/O线程将这些事件写入中继日志中。 3、SQL slave thread（SQL从线程）处理最后一步。SQL线程从中继日志中读取事件，并重放其中的事件从而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于系统的缓存中，所以中继日志开销很小。 MySQL读写分离原理 读写分离就是在主服务器上写，只在从服务器上读。基本原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的数据库。 基于中间代理层实现：代理一般位于客户端和服务端之间，代理服务器接到客户端请求通过判断转发到后端数据库，这部分通过Amoeba实现。 案例环境 实验过程 搭建MySQL主从复制 1、建立时间同步环境，在主节点上搭建时间源服务器。 [root@promote ~]# yum install ntp -y [root@promote ~]# vim /etc/ntp.conf #在配置文件最后加两行 server 127.127.58.0 #设置本地时钟源 fudge 127.127.58.0 stratum 8 #设置时间层级为8（限制在15内） [root@promote ~]# service ntpd restart #重启服务 [root@promote ~]# systemctl stop firewalld.service #关闭防火墙 [root@promote ~]# setenforce 0 #关闭增强安全功能 2、在两台从节点上分别进行时间同步。 [root@localhost ~]# yum install ntp -y [root@localhost ~]# /usr/sbin/ntpdate 192.168.58.131 #和时间源服务器同步 10 Jul 11:07:16 ntpdate[28695]: the NTP socket is in use, exiting [root@loaclhost ~]# systemctl stop firewalld.service #关闭防火墙 [root@localhost ~]# setenforce 0 #关闭增强安全功能 3、安装MySQL，这步在前面讲过，就省略掉了。 4、配置MySQL Master主服务器。 [root@promote ~]# vim /etc/my.cnf server-id = 11 #修改server-id，注意三台服务器id不能重复 log-bin=master-bin #修改主服务器日志文件 log-slave-updates=true #增加开启主从同步功能 [root@promote ~]# systemctl restart mysqld.service #重启MySQL服务器 5、登录MySQL服务，给从服务器授权。 [root@promote ~]# mysql -u root -p Enter password: mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.58.%' IDENTIFIED BY '123456'; #授予192.168.58.0网段的主机分别以myslave，123456为用户名，密码的用户REPLICATION SLAVE权限。 mysql> FLUSH PRIVILEGES; #刷新权限设置 mysql> show master status; #查看主服务器状态，file,position两个值很重要，后面要用到。 +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000002 | 2614 | | | | +-------------------+----------+--------------+------------------+-------------------+ 6、配置MySQL Slave从服务器,在两台从服务器上都执行相同的操作，只有server-id不同 [root@localhost ~]# vim /etc/my.cnf server-id = 22 #设置server-id，三台服务器不能一样 relay-log=relay-log-bin #从主服务器上同步日志文件记录到本地 relay-log-index=slave-relay-bin.index #定义relay-log的位置和名称 [root@localhost ~]# service mysqld restart [root@localhost ~]# mysql -u root -p Enter password: mysql> change master to master_host='192.168.58.131',master_user='myslave',master_password='123456'',master_log_file='master-bin.000002',master_log_pos=2614; #这条命令就是用来指定主服务器，master_log_file和master_log_pos参数和上面对应。 mysql> start slave; #开启同步 mysql> show slave status\G; #查看slave状态，确保 Slave_IO_Running，Slave_SQL_Running都是yex *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.131 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 2614 Relay_Log_File: relay-log-bin.000005 Relay_Log_Pos: 1955 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2614 Relay_Log_Space: 2535 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 7、验证主从同步效果。通过在主服务器上创建一个新数据库，然后查看是否同步成功来判断。 主服务器中的数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test1 | +--------------------+ 6 rows in set (0.23 sec) 从服务器中的数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test1 | | wq | | yxxx | +--------------------+ 8 rows in set (0.31 sec) 下面，在在主服务器上创建一个新的数据库test02，进而查看是否同步成功。 mysql> create database test02; Query OK, 1 row affected (3.86 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | +--------------------+ 7 rows in set (0.01 sec) 查看从服务器的数据库。同步成功！ mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | | wq | | yxxx | +--------------------+ 9 rows in set (0.15 sec) 搭建MySQL读写分离 1、在Amoeba代理服务器上安装Java环境，因为Amoeba服务是基于Java1.5开发的。 systemctl stop firewalld.service #关闭防火墙 setenforce 0 #关闭增强性安全功能 cp jdk-6u14-linux-x64.bin /usr/local/ #将软件包复制到指定目录 ./jdk-6u14-linux-x64.bin #执行安装脚本 mv jdk1.6.0_14/ /usr/local/jdk1.6 #为了方便，修改文件名 vim /etc/profile #将Java添加到环境变量中 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 source /etc/profile #刷新文件，使改动生效 2、安装并配置Amoeba软件。 [root@promote ~]#mkdir /usr/local/amoeba #为Amoeba创建工作目录 [root@promote ~]#tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ #解压 [root@promote ~]#chmod -R 755 /usr/local/amoeba/ #修改文件权限 [root@promote ~]#/usr/local/amoeba/bin/amoeba #执行Amoeba服务 amoeba start|stop 说明amoeba安装成功 3、配置Amoeba读写分离，两个Slave读负载均衡。 mysql> grant all on *.* to test@'192.168.58.%' identified by '123.com'; #在三台mysql服务器上添加权限开放给amoeba访问 4、回到Amoeba服务器上，首先配置amoeba.xml配置文件。 [root@promote ~]# vim /usr/local/amoeba/conf/amoeba.xml <property name="authenticator"> <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"> <property name="user">amoeba</property> #客户端用来登录Amoeba服务器的用户名 <property name="password">123456</property> #客户端用来登录Amoeba服务器的密码 <property name="filter"> <bean class="com.meidusa.amoeba.server.IPAccessController"> <property name="ipFile">${amoeba.home}/conf/access_list.conf</property> </bean> </property> </bean> </property> <property name="defaultPool">master</property>#默认服务器池 <property name="writePool">master</property>#master服务器用于写 <property name="readPool">slaves</property>#slaves服务器用于读 配置dbServers.xml文件 [root@promote ~]# vim /usr/local/amoeba/conf/dbServers.xml <!-- mysql schema <property name="schema">test</property>#将这句话注释掉，否则无法用客户端通过amoeba代理服务器访问mysql服务器池 --> <!-- mysql user --> <property name="user">test</property> #修改为用于登录服务器池的用户名 <property name="password">123.com</property> #修改为用于登录服务器池的密码 <dbServer name="master" parent="abstractServer">#配置主服务器 <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.58.131</property>#主服务器的IP </factoryConfig> </dbServer> <dbServer name="slave1" parent="abstractServer">#从服务器1的mysql服务器 <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.58.144</property> #从服务器1的mysql服务器IP </factoryConfig> </dbServer> <dbServer name="slave2" parent="abstractServer">#从服务器2的mysql服务器 <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.58.145</property> #从服务器2的mysql服务器IP </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> 5、配置好后，启动Amoeba软件，其默认端口为tcp 8066. [root@promote ~]# /usr/local/amoeba/bin/amoeba start & [1] 69919 [root@promote ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2018-07-10 16:53:55,472 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0 log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2018-07-10 16:53:55,925 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066. 2018-07-10 16:53:55,925 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:54818. ^C [root@promote ~]# netstat -ntap | grep java #8066端口已经开启 tcp6 0 0 127.0.0.1:54818 :::* LISTEN 69919/java tcp6 0 0 :::8066 :::* LISTEN 69919/java tcp6 0 0 192.168.58.136:41992 192.168.58.145:3306 ESTABLISHED 69919/java tcp6 0 0 192.168.58.136:33236 192.168.58.144:3306 ESTABLISHED 69919/java tcp6 0 0 192.168.58.136:48134 192.168.58.131:3306 ESTABLISHED 69919/java 6、到client主机上进行测试。 [root@yx 桌面]# mysql -u amoeba -p123456 -h 192.168.58.136 -P8066 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | +--------------------+ 7 rows in set (0.22 sec) mysql> 在Master上创建一个表，同步到各从服务器。 mysql> use test1; Database changed mysql> create table zang (id int(10),name varchar(10),address varchar(20)); 关闭两台从服务器的同步功能。 mysql> stop slave; ----在主服务器上---内容不会同步 use test1 insert into zang values('1','zhang','this_is_master'); ----从服务器1---- use test1; insert into zang values('2','zhang','this_is_slave1'); ----从服务器2---- use test1; insert into zang values('3','zhang','this_is_slave2'); ------在客户端上测试----第一次会向从服务器1读数据-第二次会向从服务器2读取 mysql> select * from test1.zang; ------+-------+----------------+ | id | name | address | +------+-------+----------------+ | 3 | zhang | this_is_slave2 | +------+-------+----------------+ 3 rows in set (0.03 sec) mysql> select * from test1.zang; +------+-------+----------------+ | id | name | address | +------+-------+----------------+ | 2 | zhang | this_is_slave1 | +------+-------+----------------+ 3 rows in set (0.25 sec) ------在通过客户端连接数据库后写入的数据只有主服务器会记录 mysql> insert into zang values('5','zhang','write_test'); 到主服务器中查看表，会发现有两条记录，而从服务器中只有一条记录，说明实现了读写分离 +------+-------+----------------+ | id | name | address | +------+-------+----------------+ | 1 | zhang | this_is_master | | 5 | zhang | write_test | +------+-------+----------------+ 3 rows in set (0.01 sec) （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
