MySQL——主从复制与读写分离

最新推荐文章于 2024-07-02 23:25:48 发布

老臣、

最新推荐文章于 2024-07-02 23:25:48 发布

阅读量1.4k

点赞数

分类专栏：架构文章标签： MySQL 主从复制读写分离

本文链接：https://blog.csdn.net/vae1314chuanchen/article/details/89473702

版权

架构专栏收录该内容

7 篇文章 0 订阅

订阅专栏

什么是主从复制

主从复制是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

主从复制的作用

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、有利于架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

主从复制的原理

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志（Relay log）中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

简言之可分为下面几个步骤：

一、主库的更新事件会被写到bin log日志中。
二、从库启用slave服务，发起连接，连接到主库。
三、从库创建一个I/O线程，从主库读取bin log日志的内容并写入到relay log日志中。
四、从库创建一个SQL线程，从relay log里面读取内容，将更新内容写入到从库。

主从复制需要的配置

主服务器：
1、开启二进制日志
2、配置唯一的server-id
3、获得master二进制日志文件名及位置
4、创建一个用于slave和master通信的用户账号

从服务器：
1、配置唯一的server-id
2、使用master分配的用户账号读取master二进制日志
3、启用slave服务

主从复制实践

准备

两台虚拟机：
centos1：192.168.1.109 主库，同时安装了Mycat进行读写分离
centos2：192.168.1.100 从库

主服务器配置

一、修改my.conf文件
在 /etc/my.cnf文件中的[mysqld]下面添加：

[mysqld]
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=109

二、重启MySQL服务

service mysqld restart

三、建立账号并授权slave

mysql>GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by '123456';

注：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.1.110，加强安全。

四、刷新权限

mysql> FLUSH PRIVILEGES;

五、查询master的状态

mysql> show master status;

master状态

从服务器配置

一、修改my.conf文件
在 /etc/my.cnf文件中的[mysqld]下面添加：

[mysqld]
#服务器唯一ID，一般取IP最后一段
server-id=110

二、删除UUID文件
删除/var/lib/mysql/auto.cnf文件，重新启动服务。

三、配置从服务器

mysql>change master to master_host='192.168.1.109',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=2678;

注：语句中间不要断开。
master_port为mysql服务器端口号(无引号)。
master_user为执行同步操作的数据库账户。
“2678”无单引号
此处的2678就是show master status 中看到的position的值，mysql-bin.000001就是file对应的值。

四、启动从服务器的复制功能

mysql>start slave;

五、检查从服务器复制功能状态

mysql> show slave status;

六、从服务器配置完成，连接数据库进行测试。

读写分离

MySQL的主从复制，只会保证主机对外提供服务，而从机是不对外提供服务的，只是在后台为主机进行备份。
数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置。
Mycat的读写分离是建立在MySQL的主从复制基础上的。对MySQL进行读写分离控制的方法和中间件有很多，如MySQL-Proxy、Mycat等，这里介绍利用Mycat对MySQL数据库的访问进行的读写分离。
Mycat读写分离

Mycat介绍

官方网站：http://www.mycat.org.cn/

【MyCat】简介

Mycat安装

MyCat安装及启动

默认数据端口为8066，管理端口为9066

Mycat启动报错java.net.BindException: Address already in use
杀掉正在执行的相关java进程

ps -ef|grep java
kill -9 xxx

Mycat读写分离

利用Mycat对MySQL数据库的访问进行的读写分离，只需要配置两个文件即可。分别是schema.xml文件和server.xml。

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<!-- 逻辑数据库配置 -->
	<schema name="USER_DB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn"></schema>
	<!-- 配置存储节点 -->
	<dataNode name="dn" dataHost="node" database="user_db" />
	<!-- 配置数据库主机 -->
	<dataHost name="node" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<!--心跳检测 -->
		<heartbeat>select user()</heartbeat>
		<!-- 配置读、写数据库 -->
		<writeHost host="192.168.1.109" url="192.168.1.109:3306" user="root"
				   password="123456">
			<readHost host="192.168.1.110" url="192.168.1.110:3306" user="root" password="123456" />
		</writeHost>
		<!-- 写数据库宕机后的备份数据库 -->
		<writeHost host="192.168.1.110" url="192.168.1.110:3306" user="root"
				   password="123456" />
	</dataHost>
</mycat:schema>

(1) 设置 balance="1"与writeType=“0”
balance参数设置：
balance=“0”, 所有读操作都发送到当前可用的writeHost上。
balance=“1”，所有读操作都随机的发送到readHost。
balance=“2”，所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置：
writeType=“0”, 所有写操作都发送到可用的writeHost上。
writeType=“1”，所有写操作都随机的发送到readHost。
writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。
readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”

(2) 设置 switchType=“2” 与slaveThreshold=“100”
switchType 目前有三种选择：
-1：表示不自动切换
1 ：默认值，自动切换
2 ：基于MySQL主从同步的状态决定是否切换
“Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType=“2” 与slaveThreshold=“100”，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 “Seconds_Behind_Master”, “Slave_IO_Running”, “Slave_SQL_Running” 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“

配置server.xml

server.xml几乎保存了所有mycat需要的系统配置信息。最常用的是在此配置用户名、密码及权限。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="nonePasswordLogin">0</property> 
		<property name="useHandshakeV10">1</property>
		<property name="useSqlStat">0</property>  
		<property name="useGlobleTableCheck">0</property> 
		<property name="sequnceHandlerType">2</property>
		<property name="subqueryRelationshipCheck">false</property> 
		<property name="processorBufferPoolType">0</property>
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">64k</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>
		<property name="useZKSwitch">false</property>
	</system>
	
	<!-- 配置客户端连接Mycat的用户名、密码、逻辑数据库 -->
	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">USER_DB</property>
	</user>

</mycat:server>