MySQL运维之日志与主从复制

若瑜9个月前行业资讯403

一、日志

1.错误日志

错误日志是MySQL中最重要的日志之一,它记录了当mysql启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,建议首先查看此日志。


该日志是默认开启的,默认放在目录 /var/log/,默认的日志文件名为 mysqld.log。查看日志位置:

show variables like '%log_error%';

2.二进制日志

介绍

二进制日志(BINLOG)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。

作用:①.灾难时的数据恢复;

②.MySQL的主从复制。在MySQL8版本中,默认二进制日志时开启的,涉及到的参数如下:

show variables like '%log_bin%';

image.png

MySQL服务器中提供了多种格式来记录二进制日志,具体格式及特点如下:

image.png

show variables like '%binlog_format%';

日志查看

由于日志是以二进制方式存储的,不能直接读取,需要通过二进制日志查询工具mysqlbinlog来查看,具体语法:

mysqlbinlog [参数选择] logfilename 


参数选项:
	-d 		指定数据库名称,只列出指定的数据库相关操作。
	-o		忽略掉日志中的前n行命令。
	-v		将行事件(数据变更)重构为SQL语句。
	-vv		将行事件(数据变更)重构为SQL语句,并输出注释信息


mysqlbinlog [参数选择] logfilename 



参数选项:

-d 指定数据库名称,只列出指定的数据库相关操作。

-o 忽略掉日志中的前n行命令。

-v 将行事件(数据变更)重构为SQL语句。

-vv 将行事件(数据变更)重构为SQL语句,并输出注释信息

image.png

也可以在mysql的配置文件中配置二进制日志的过期时间,设置了之后,二进制日志过期会自动删除。

show variables like '%binlog_expire_logs_seconds%';


3.查询日志

查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL 语句。默认情况下,查询日志事未开启的。如果要开启查询日志,可以设置以下配置:

show variables like '%general%';

image.png

修改MySQL的配置文件 /etc/my.cnf文件,添加如下内容:

该选项用来开启查询日志,可选值:0或者1;0代表关闭,1代表开启

general_log=1

设置日志的文件名,如果没有指定,默认的文件名为host_name.log

general_log_file=mysql_query.log

4.慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志,默认未开启。long_query_time默认为10秒,最小为0,精度可以到微妙。


慢查询日志

slow_query_log=1

执行时间参数

long_query_time=2

默认情况下,不会记录管理语句,也不会记录不使用索引进行查找的查询。可以使用log_show_admin_statements和更改行为log_queries_not_using_indexes,如下所述

记录执行较慢的管理语句

log_slow_admin_statements=1

记录执行较慢的未使用索引的语句

log_queries_not_using_indexes=1

二、主从复制

1.概述

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制。

image.png

MySQL复制的有点主要包含以下三个方面:

1.主库出现问题,可以快速切换到从库提供服务。

2.实现读写分离,降低主库的访问压力。

3.可以在从库中执行备份,以避免备份期间影响主库服务。


2.原理

MySQL的主从复制原理如下。

image.png

从上图来看,复制分为三步:

1.Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中。

2.从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log。

3.slave重做中继日志中的事件,将改变反映它自己的数据。


3.搭建

服务器准备

image.png

准备好服务器后,在两台服务器中分别安装好MySQL,完成基础的初始化准备工作。


主库配置


  1. 修改配置文件 /etc/my.cnf

#mysql服务ID,保证整个集群环境中唯一,取值范围:1-2^32 -1,默认为1
server-id=1
#是否只读,1代表只读,0代表读写
read-only=0
#忽略的数据,指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

2.重启MySQL服务器

systemctl restart mysqld

3.登录mysql,创建远程连接的账号,并授予主从复制权限

#创建itcast用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为'itcast'@'%'用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';


4.通过指令,查看二进制日志坐标

show master status;

字段含义说明:

file:从哪个日志文件开始推送日志文件

position:从哪个位置开始推送日志

binlog_ignore_db:指定不需要同步的数据库

从库配置

1.修改配置文件 /etc/my.cnf

#mysql服务ID,保证整个集群环境中唯一,取值范围:1-2^32-1,和主库不一样即可
server-id=2
#是否只读,1代表只读,0代表读写
read-only=1

#super-read-only=1


2.重启MySQL服务器

systemctl restart mysqld

3.登录mysql,设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx.xxx',SOURCE_USER='XXX',SOURCE_LOG_FILE='xxx',SOURCE_LOG_POS=xxx;

上述时8.0.23中的语法,如是时mysql8.0.23之前的版本,执行如下SQL:

CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;

d991d73f8abe941513a70c0d05f505b9.png

4.开启同步操作

8.0.22之后

start replica;


8.0.22之前

start slave;

5.查看主从同步状态

8.0.22之后

show replica status;

8.0.22之前

show slave status;

测试

1.在主库上创建数据库、表,并插入数据

2.在从库中查询数据,验证主从是否同步


4.总结

1.概述


将主库的数据变更同步到从库,从而保证主库和从库数据一致。

数据备份、失败迁移、读写分裂,降低单库读写压力。


2.原理


①.主库会把数据变更记录在二进制日志文件Binlog中。

②.从库连接主库,读取binlog日志,并写入自身中继日志relaylog。

③.slave重做中继日志,将改变反映它自己的数据。


3.搭建


①.准备服务器

②.配置主库

③.配置从库

④.测试主从复制

————————————————

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

原文链接:https://blog.csdn.net/qq_40492693/article/details/125073548


本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!  

云掣基于多年在运维领域的丰富时间经验,编写了《云运维服务白皮书》,欢迎大家互相交流学习:

《云运维服务白皮书》下载地址:https://fs80.cn/v2kbbq

想了解更多大数据运维托管服务、数据库运维托管服务、应用系统运维托管服务的的客户,欢迎点击云掣官网沟通咨询:https://yunche.pro/?t=shequ


相关文章

MyBatisPlus从零到一:快速入门与核心功能详解(6)

MyBatisPlus从零到一:快速入门与核心功能详解(6)

四、分页插件MybatisPlus 提供了很多的插件功能,进一步拓展其功能。目前已有的插件有:PaginationInnerInterceptor:自动分页TenantLineInnerInterce...

大数据平台之数据存储

大数据平台之数据存储

1.大数据生态技术数据存储处理: 清洗, 关联, 规范化, 组织建模, 通过数据质量的检测, 数据分析然后提供相应的数据服务离线数仓:实时数仓:以Kafka, cancal/Maxwell/Flink...

一文帮你理解整个SRE运维体系

一文帮你理解整个SRE运维体系

SRE运维体系的构建和工作职责划分。SRE工程师近年来的岗位需求逐年增加,被称为IT行业十大最受欢迎的行业之一。可观测性系统在任何有一定规模的企业内部,一旦推行起来整个SRE的运维模式,那么对于可观测...

Docker: ubuntu系统下Docker的安装

Docker: ubuntu系统下Docker的安装

安装依赖操作系统版本Ubuntu Kinetic 22.10Ubuntu Jammy 24.04 (LTS)Ubuntu Jammy 22.04 (LTS)Ubuntu Focal 20.04 (LT...

Linux 中通过 cpulimit 限制服务的CPU占用率

Linux 中通过 cpulimit 限制服务的CPU占用率

通常做资源限制有多种方法,比如用 CGroup 方法、system-run等,cpulimit 只是其中的一种方式。安装sudo apt update sud...

【Docker】深入理解 Dockerfile:构建高效 Docker 镜像的指南

【Docker】深入理解 Dockerfile:构建高效 Docker 镜像的指南

Dockerfile 是 Docker 的核心组成部分之一,它定义了如何构建 Docker 镜像。Dockerfile 是一份文本文件,其中包含了构建 Docker 镜像所需的所有命令和参数。通过 D...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。