8.MySQL主从复制、读写分离

已于 2024-05-15 10:19:45 修改
阅读量834 收藏 4
点赞数 9
分类专栏： 1、MySQL数据库 文章标签： mysql 数据库
于 2024-05-13 10:50:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010198709/article/details/138785300
版权

MySQL主从复制、读写分离

一、MySQL主从复制

1、作用

  • 避免数据库的单点故障
  • 实现多个MySQL间的数据一致

2、工作原理

  • 核心依赖：二进制日志

1、主库将写操作记录到二进制日志
2、从库产生IO线程，连接主库，读取二进制日志内容，记录到本地的中继日志
3、从库产生SQL线程，读取中继日志的操作，在本地执行
在这里插入图片描述
存在问题：数据复制延迟，关注延迟时间

  1. 增加网络带宽
  2. 使用高IO的磁盘， SSD固态硬盘

3、常见架构

  • 一主一从
  • 一主多从
  • 双主复制

二、主从复制配置

1、环境描述

192.168.140.10 主库
192.168.140.11 从库

2、关闭SELinux、时间同步

3、防火墙放行数据库服务

[root@zabbix_agent ~]# firewall-cmd --permanent --add-port=3306/tcp
success
[root@zabbix_agent ~]# firewall-cmd --reload
success
[root@zabbix_agent ~]#

4、主库配置

4.1 开启二进制日志

server_id=10
log_bin=/mysql/log/master

4.2 创建允许从库连接的复制用户

mysql> CREATE USER 'repluser'@"192.168.140.11" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT replication slave ON *.* TO 'repluser'@'192.168.140.11';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4.3 主库上进行数据备份，将备份文件拷贝到从库

[root@localhost ~]# mysqldump -uroot -pWWW.1.com --lock-all-tables --master-data=2 --all-databases > /opt/data.sql

[root@localhost ~]# scp /opt/data.sql  root@192.168.140.11:/opt/

5、从库配置

5.1 安装MySQL

5.2 配置server_id、二进制日志

[root@zabbix_agent ~]# vim /etc/my.cnf
[mysqld]
server_id=11
log_bin=master

[root@zabbix_agent ~]# systemctl restart mysqld

5.3 恢复主库的备份

[root@zabbix_agent ~]# mysql -uroot -pWWW.1.com < /opt/data.sql

5.4 指定连接主库的信息

mysql> CHANGE MASTER TO
    -> MASTER_HOST="192.168.140.10",
    -> MASTER_USER="repluser",
    -> MASTER_PASSWORD="WWW.1.com",
    -> MASTER_LOG_FILE="master.000010",
    -> MASTER_LOG_POS=781;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5.5 启动复制线程

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.6 验证复制线程启动成功

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.140.10
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000010
          Read_Master_Log_Pos: 1056
               Relay_Log_File: zabbix_agent-relay-bin.000002
                Relay_Log_Pos: 592
        Relay_Master_Log_File: master.000010
             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: 1056
              Relay_Log_Space: 806
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 5f753038-103a-11ef-994d-000c29cc6b2f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Seconds_Behind_Master: 复制延迟时长，单位为秒
Read_Master_Log_Pos
Exec_Master_Log_Pos		通过这两个值的差，判断数据是否一致

在主库上查看连接的从库信息

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        11 |      | 3306 |        10 | fc1c8e83-103b-11ef-992c-000c2929e543 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

三、读写分离

1、读写分离介绍

  • 加快数据库工作效率
  • 在主从复制环境 ，主库执行写操作，从库执行读操作

2、实现方案

  • 开发在业务代码中固定写数据库地址
  • 数据库中间件
    mysql-proxy MySQL官方
    aomerba 阿里，开源
    MyCAT 国内，开源
    Atlas 360

四、MyCAT实现读写分离

1、mycat软件安装

1.1 安装JDK 1.8

[root@localhost ~]# tar xf jdk-8u91-linux-x64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  etc  games  include  jdk1.8.0_91  lib  lib64  libexec  sbin  share  src

1.2 为JDK添加环境变量

[root@localhost ~]# vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.8.0_91
export PATH=$PATH:$JAVA_HOME/bin

[root@localhost ~]# source /etc/profile

[root@localhost ~]# java -version 
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

1.3 安装mycat

[root@localhost ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  etc  games  include  jdk1.8.0_91  lib  lib64  libexec  mycat  sbin  share  src

[root@localhost ~]# vim /etc/profile
export MYCAT_HOME=/usr/local/mycat

[root@localhost ~]# source /etc/profile

2、编辑schema.xml文件，定义读写分离

[root@localhost ~]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	// 定义虚拟数据库，关联dataNode
	<schema name="game" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn01">
	</schema>

	// 定义真实数据库，关联dataHost
	<dataNode name="dn01" dataHost="dh01" database="game" />

	// 定义数据主机，主库、从库地址
	<dataHost name="dh01" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.140.10:3306" user="mycatuser"
				   password="WWW.1.com">
		</writeHost>
		<writeHost host="hostS1" url="192.168.140.11:3306" user="mycatuser"
				   password="WWW.1.com" />
	</dataHost>
</mycat:schema>

3、在后端数据库建立允许mycat连接的用户

mysql> CREATE USER 'mycatuser'@"192.168.140.12" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT all ON game.* TO 'mycatuser'@"192.168.140.12";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4、在MyCAT上创建允许业务连接的用户

[root@localhost conf]# vim /usr/local/mycat/conf/server.xml 

        <user name="admin">
                <property name="password">123456</property>
                <property name="schemas">game</property>
        </user>

5、启动MyCAT

[root@localhost ~]# /usr/local/mycat/bin/mycat start 
Starting Mycat-server...

[root@localhost ~]# netstat -tunlp | grep java 
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      1765/java           
tcp6       0      0 :::1984                 :::*                    LISTEN      1765/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      1765/java           
tcp6       0      0 :::9066                 :::*                    LISTEN      1765/java

6、测试读写分离

6.1 连接Mycat进行写操作

连接Mycat执行写操作，到后端从库上验证有同步的数据
在这里插入图片描述

6.2 测试读操作

五、主从复制维护

1、从库宕机恢复后，会自动连接主库复制数据

  • master.info
  • relay-log.info
  • 文件中记录着连接主库的地址信息、二进制日志、中继日志信息

2、模拟主从复制故障

2.1 IO线程故障

网络通信 ping、traceroute
主库服务宕机/防火墙拦截 telnet
用户密码有错误、用户授权replication slave

2.2 SQL线程故障

  • 从库上执行了写操作，引起数据冲突

从库设置为只读

# vim /etc/my.cnf
[mysqld]
read_only=1

3、主从角色切换

3.1 将从服务器提升为新主

关闭复制线程						sql> stop slave;
清除主服务器的连接信息		sql> reset slave all;
删除read_only只读配置

3.2 将业务的数据库连接 切换到新主服务器

[root@localhost ~]# vim	网页目录/config/config_global.php 

// ----------------------------  CONFIG DB  ----------------------------- //
$_config['db'][1]['dbhost'] = '192.168.140.11';
$_config['db'][1]['dbuser'] = 'dzuser';
$_config['db'][1]['dbpw'] = 'WWW.1.com';
$_config['db'][1]['dbcharset'] = 'utf8mb4';
$_config['db'][1]['pconnect'] = 0;
$_config['db'][1]['dbname'] = 'discuz';

mysql> CREATE USER 'dzuser'@"%" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT all ON discuz.* TO 'dzuser'@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.3 将故障的主机修复完毕后，作为从连接到新主上

六、基于GTID的主从复制

1、GTID的介绍

GTID， 全局事务ID， 数据库中的每个事务/操作都有一个惟一ID， 从库连接主库复制数据时，会根据GTID自动识别哪些操作执行过，哪些操作没有执行
MySQL 5.6版本以上支持的

  • GTID == server_UUID + 数字编号

2、基于GTID的主从复制

2.1 清理MySQL现有所有数据

2.2 开启GTID的功能

[root@localhost ~]# vim /etc/my.cnf
gtid_mode=ON									// 启用gtid
enforce_gtid_consistency=true	//强制gtid的一致性

2.3 从库连接主库

mysql> CHANGE MASTER TO
    -> MASTER_HOST="192.168.140.10",
    -> MASTER_USER="repluser",
    -> MASTER_PASSWORD="WWW.1.com",
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.140.10
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 1177
               Relay_Log_File: zabbix_agent-relay-bin.000002
                Relay_Log_Pos: 1384
        Relay_Master_Log_File: master.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: 1177
              Relay_Log_Space: 1598
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 997e5ca7-1294-11ef-9338-000c29cc6b2f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 997e5ca7-1294-11ef-9338-000c29cc6b2f:1-5
            Executed_Gtid_Set: 997e5ca7-1294-11ef-9338-000c29cc6b2f:1-5,
c992938f-1294-11ef-93fe-000c2929e543:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

七、双主复制

  • 在旧主服务器建立允许远程连接的用户

双主复制都允许执行写操作，同时会复制给对方，为了避免由于主键冲突导致故障，可以调整自动增长的步长

# vim /etc/my.cnf
auto_increment_offset=2				// 定义起始数字
auto_increment_increment=2		// 定义步长
Martin_wjc
关注
  • 9
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL主从复制与读写分离原理及用法详解
09-08
MySQL主从复制与读写分离是数据库高可用性和负载均衡的重要技术。主从复制允许数据从一个服务器（主服务器）自动同步到其他服务器（从服务器），而读写分离则是将读操作分散到从服务器，以减轻主服务器的压力，提高...
详解MySQL主从复制读写分离搭建
12-16
MySQL主从复制，读写分离的设置非常简单： 修改配置my.cnf文件 master 和 slave设置的差不多： [mysqld] log-bin=mysql-bin server-id=222 log-bin=mysql-bin的意思是：启用二进制日志。 server-id=222的意思是...
mysql8.0.17主从复制读写分离搭建
yrsg666的博客
10-09 459
https://blog.csdn.net/weixin_33724659/article/details/92929970 按照这篇博客即可，本人已实践成功。 注意事项： 1、中间注意那个3.2步全局锁动作 FLUSH TABLES WITH READ LOCK;这个直接退出mysql视图重新登录即可解锁，或者用命令UNLOCK TABLES解锁。搭建的基础准备（两台mysql8.0.1...
Mysql8.0主从复制搭建，shardingsphere+springboot+mybatis读写分离
lp_cq242的博客
01-29 2809
1、安装mysql8.0 首先需要在192.167.3.171上安装JDK。 下载mysql安装包，https://dev.mysql.com/downloads/，找到以下页面下载。     下载后放到linux系统中 官网说需要先查看本机是否已安装mysql，删除mysql这里不介绍。 1 yum install libaio  yum...
Mysql8.x版本主从加读写分离（一） mysql8.x主从
W1124824402的博客
11-23 735
主从安装步骤一致，安装mysql的yum仓库主库192.168.11.141从库192.168.11.144 环境centos7.9wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpmrpm -ivh mysql80-community-release-el7-3.noarch.rpmyum install -y mysql-community-server下载时报错执行一下这条命令yum -y install
MySQL主从复制与读写分离
qq_64612585的博客
03-28 1289
MySQL 主从复制是一种数据复制技术，其中一个数据库服务器（称为主服务器）的更改会被自动地传播到一个或多个其他数据库服务器（称为从服务器）。MySQL读写分离是一种常见的数据库优化策略，它通过将读和写操作分配给不同的数据库服务器来提高系统的性能和可伸缩性。在这种架构中，主服务器负责处理所有的写操作，而多个从服务器则负责处理读操作。
MySQL主从复制和读写分离
2301_81272194的博客
03-28 1798
关于MySQL主从复制和读写分离相关内容
MySql主从复制读写分离
Genius_zhu的博客
09-22 722
一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以【事件】的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志（relay log）中，然后slave的一个SQL线程会把相关的【事件】执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。基于客户端的实现，也就是在应用程序/代码层面的实现。
Docker 搭建MySQL主从复制-读写分离
m0_56659620的博客
01-29 1229
MySQL主从复制是一种常用的数据库高可用性解决方案，通过在主数据库上记录的数据变更，同步到一个或多个从数据库，实现数据的冗余备份和读写分离。在Docker环境下搭建MySQL主从复制和读写分离，不仅方便管理，还能充分发挥Docker的轻量、可移植性等特性。通过Docker搭建MySQL主从复制和读写分离，不仅简化了部署过程，还提高了系统的可维护性。合理配置主从关系和读写分离，可以优化数据库性能，提高系统的稳定性和可用性。
MySQL 主从复制、读写分离
qq_61657394的博客
04-13 342
MySQL 主从复制、读写分离
MySQL主从复制，读写分离
qi341500的博客
03-10 474
一、MySQL主从复制介绍 MySQL复制过程分成三步 二、主库配置master 1、步骤1 2、第二步:重启Mysql服务 3、第三步：登录Mysql数据库，执行下面SQL 4、第四步：登录Mysql数据库，执行下面SQL，记录下结果中File和Position的值 三、配置-从库Slave 1、步骤1 2、第二步:重启Mysql服务 3、第三步:登录Mysql数据库，执行下面SQL 4、第四步：登录Mysql数据库，执行下面sQL，查看从数据库的状态
MySQL主从复制读写分离.zip
03-14
MySQL主从复制与读写分离是数据库架构中的重要策略，用于提高系统性能和可用性。在高并发的互联网应用中，这种设计模式尤为常见。本文将深入探讨这两个概念及其实施方法。 **一、MySQL主从复制** MySQL主从复制是...
MySQL主从复制 读写分离
06-24
经本人测试已经成功实现
mysql主从复制读写分离的配置方法详解
09-09
MySQL主从复制和读写分离是数据库高可用性和性能优化的重要技术。主从复制确保数据在多个服务器间同步，而读写分离则可以提高数据库处理并发请求的能力，尤其适用于读多写少的应用场景。 主从复制配置过程如下： 1...
centos7中MySQL的卸载
CodeTom的博客
07-11 259
6、删除/var/log/mysqld.log（如果不删除这个文件，会导致新安装的mysql无法生存新密码，会出现无法登陆的情况）卸载完成后不要忘记检查一遍是否卸载干净。4、对安装的所有XXX目录进行删除操作。1、查看mysql安装了哪些东西。5、删除/etc/my.cnf。删除完成后检查是否删除干净。3、找mysql相关目录。
JavaFx+MySql学生管理系统
最新发布
2301_81253185的博客
07-11 1130
上个月学习了javafx和mysql数据库,于是写了一个学生管理系统,因为上个月在复习并且有一些事情,比较忙,所以没有更新博客了,这个项目页面虽然看着有点简陋了,但是大致内容还是比较简单的,于是现在跟大家分享一下我的学生管理系统,希望对这方面有兴趣的同学提供一些帮助。
数据库（mysql）忘记密码解决办法
GGB_GG的博客
07-11 523
【代码】数据库（mysql）忘记密码解决办法。
mysql主从复制 读写分离面试题
04-11
MySQL主从复制是一种常用的数据库架构设计，用于提高数据库的可用性和性能。它通过将数据从一个MySQL主服务器复制到一个或多个从服务器来实现。 主从复制的工作原理如下： 1. 主服务器（Master）接收到写操作（INSERT、UPDATE、DELETE）后，将操作记录写入二进制日志（Binary Log）。 2. 从服务器（Slave）连接到主服务器，并请求复制主服务器的二进制日志。 3. 主服务器将二进制日志传输给从服务器，从服务器将其写入中继日志（Relay Log）。 4. 从服务器读取中继日志，并将其中的操作应用到自己的数据库中，实现数据的同步。 读写分离是在主从复制的基础上进一步优化数据库性能的一种方式。它将读操作和写操作分离，使得读操作可以在从服务器上进行，而写操作仍然在主服务器上进行。这样可以提高数据库的并发处理能力和读取性能。 面试题相关问题： 1. 什么是MySQL主从复制？ 2. 主从复制的工作原理是什么？ 3. 为什么要使用MySQL主从复制？ 4. 读写分离是什么？有什么优势？ 5. 主从复制和读写分离的应用场景有哪些？ 6. 主从复制可能存在的问题和风险有哪些？ 7. 如何配置MySQL主从复制和读写分离？ 8. 如何监控和管理MySQL主从复制和读写分离的状态？ 9. 有没有其他替代MySQL主从复制和读写分离的方案？ 10. 如何保证主从复制的数据一致性和可靠性？

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值