MySQL数据库入门到精通——运维篇（1）

2023-11-01 23:36
文章标签 mysql 入门 精通 数据库 运维篇 database

本文主要是介绍MySQL数据库入门到精通——运维篇（1），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL数据库入门到精通——运维篇（1）

  • 1. 日志
    • 1.1 错误日志
    • 1.2 二进制日志
    • 1.3 查询日志
    • 1.4 慢查询日志
  • 2. 主从复制
    • 2.1 主从复制的概述
    • 2.2 主从复制的原理
    • 2.3 主从复制的搭建
      • 2.3.1 服务器准备
      • 2.3.2 主库配置
      • 2.3.3 从库配置
      • 2.3.4 测试

在这里插入图片描述
在这里插入图片描述

1. 日志

在任何一种数据库中，都会有各种各样的日志，这些日志记录了数据库运行的各个方面。可以帮助数据库管理员追踪数据库曾经发生的一些事情。

对于MySQL数据库，提供了四种不同的日志帮助我们追踪。

  • 错误日志
  • 二进制日志
  • 查询日志
  • 慢查询日志

1.1 错误日志

​错误日志是MySQL中最重要的日志之一，它记录了当mysqld(MySQL服务)启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

​ 该日志是默认开启的，默认存放目录/var/logl，默认的日志文件名为psmysqld.log。查看日志位置；

show variables like '%log_error%'

C:\Users\HP>mysql -uroot -p
Enter password: **************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.33 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | .\DESKTOP-JL38L7P.err                  |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set, 1 warning (0.08 sec)

🐬 打开第二个控制台 输入：tail -f /var/log/mysqld.log

这样就可以在第二个控制台中实时查看错误日志。

在这里插入图片描述

1.2 二进制日志

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

作用

①.灾难时的数据恢复;
②. MySQL的主从复制。

在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下:
show variables like '%log_bin%'

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------------------------+
| Variable_name                   | Value                                                                |
+---------------------------------+----------------------------------------------------------------------+
| log_bin                         | ON                                                                   |
| log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-JL38L7P-bin       |
| log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-JL38L7P-bin.index |
| log_bin_trust_function_creators | OFF                                                                  |
| log_bin_use_v1_row_events       | OFF                                                                  |
| sql_log_bin                     | ON                                                                   |
+---------------------------------+----------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

在这里插入图片描述
涉及两类文件：索引文件、日志文件

日志格式

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

日志格式含义
STATEMENT基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW基于行的日志记录，记录的是每一行的数据变更。(默认)
MIXED混合了STATEMENT和ROW两种格式，默认采用STATEMENT,在某些特殊情况下会自动切换为ROW进行记录。

show variables like 'binlog_format';

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

我们现在想要查看日志，但是二进制，对于我们来讲是无意义的。

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mybatis            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| tlias              |
| world              |
+--------------------+
9 rows in set (0.03 sec)mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account        |
| course         |
| dept           |
| emp            |
| emp2           |
| stu            |
| student        |
| student_course |
| tb_user        |
| tb_user_edu    |
| user           |
+----------------+
11 rows in set (0.01 sec)mysql> select * from tb_user_edu;
+----+--------+----------+----------------+----------------+--------------+--------+
| id | degree | major    | primaryschool  | middleschool   | university   | userid |
+----+--------+----------+----------------+----------------+--------------+--------+
|  1 | 本科   | 舞蹈     | 静安区第一小学 | 静安区第一中学 | 北京舞蹈学院 |      1 |
|  2 | 硕士   | 表演     | 朝阳区第一小学 | 朝阳区第一中学 | 北京电影学院 |      2 |
|  3 | 本科   | 英语     | 杭州市第一小学 | 杭州市第一中学 | 杭州师范大学 |      3 |
|  4 | 本科   | 应用数学 | 阳泉第一小学   | 阳泉区第一中学 | 清华大学     |      4 |
+----+--------+----------+----------------+----------------+--------------+--------+
4 rows in set (0.02 sec)mysql> update tb_user_edu set university = "北京大学";
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql>

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志;

指令含义
reset master删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始
purge master logs to 'binlog. ****删除*****编号之前的所有日志
purge master logs before ‘yyy-mm-dd hh24:mi:ss’删除日志为"yyy-mm-dd hh24:mi:ss"之前产生的所有日志

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

show variables like '%binlog_expire%';

在这里插入图片描述

1.3 查询日志

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

show variables like '%general%';

mysql> show variables like '%general%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | OFF                 |
| general_log_file | DESKTOP-JL38L7P.log |
+------------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

在这里插入图片描述

在这里插入图片描述

1.4 慢查询日志

慢查询日志记录了所有执行时间超过参数long_ query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。

long_query_time 默认为10秒，最小为0，精度可以到微秒。

#慢查询日志
slow_ query_ log=1
#执行时间参数
long_query_time=2

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

#记录执行较慢的管理语句
log_slow_admin_statements = 1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

在这里插入图片描述

总结

  1. 错误日志
    主要记录的是MYSQL服务启动及运行过程中的发生的一些错误、异常。那么通过错误日志我们可以排查，数据库运行在哪里报错了，从而定位错误，解决问题。
  2. 二进制日志
    主要记录的是DDL与DML语句，通过二进制日志，我们可以完成数据的恢复，那么对于Mysql的主从复制，也是基于二进制日志的，其中数据格式有三种（实际就两种），默认ROW。
  3. 查询日志
    二进制日志中没有记录对应的查询语句，查询日志记录的所有的语句。这份日志会很大，所以默认是关闭的。
  4. 慢查询日志
    定位执行效率比较低的语句。

2. 主从复制

2.1 主从复制的概述

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

在这里插入图片描述

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

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

  1. 主库出现问题，可以快速切换到从库提供服务；
  2. 实现读写分离，降低主库的访问压力；（如果增删改对主库 查询对从库）
  3. 可以在从库中执行备份，以避免备份期间影响主库服务。

在这里插入图片描述

2.2 主从复制的原理

原理无非就是 主库如何同步到从库中！！！

在这里插入图片描述
主从复制基于二进制文件

从上图来看，复制分成三步：

  1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
  2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
  3. slave重做中继日志中的事件，将改变反映它自己的数据。

在这里插入图片描述

2.3 主从复制的搭建

主从复制的搭建步骤：

  1. 准备主从复制服务器环境
  2. 完成主库配置
  3. 完成从库配置

2.3.1 服务器准备

在这里插入图片描述
在这里插入图片描述
准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备工作。

2.3.2 主库配置

  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

  1. 重启MySQL服务器
    systemctl restart mysqld

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

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

2.3.3 从库配置

  1. 修改配置文件/etc/my.cnf
#mysql服务ID，保证整个集群环境中唯一， 取值范围: 1 - 2^{32}-1，和主库不一样即可
server-id=2
#是否只读,1代表只读, 0代表读写
read-only=1

  1. 重启MySQL服务器
    systemctl restart mysqld

  2. 登录Mysql，设置主库配置
    在这里插入图片描述

参数名含义8.0.23之前
SOURCE_HOST主库IP地址MASTER_HOST
SOURCE_USER连接主库的用户名MASTER_USER
SOURCE_PASSWORD连接主库的密码MASTER_PASSWORD
SOURCE_LOG FILEbinlog日志文件名MASTER LOG_FILE
SOURCE_LOG POSbinlog日志文件位置MASTER_LOG_POS
  1. 开启同步操作
start replica ;
#8.0.22之后
start slave ;
#8.0.22之前

2.3.4 测试

  1. 在主库上创建数据库、表，并插入数据
create database db01;use db01;create table tb_user(id int(11) not null,name varchar(50) not null,sex varchar(1),primary key (id)
)engine=innodb default charset=utf8;insert into tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');insert into tb_user(id,name,sex) values(4,'Jack Ma','1');
insert into tb_user(id,name,sex) values(5,'Coco','0');
insert into tb_user(id,name,sex) values(6,'Jerry','1');
  1. 在从库中查询数据，验证主从是否同步

这篇关于MySQL数据库入门到精通——运维篇（1）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/326548。

相关文章

MySQL 主从复制部署及验证(示例详解)

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5
阅读更多...
SpringBoot中六种批量更新Mysql的方式效率对比分析

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种
阅读更多...
MySql基本查询之表的增删查改+聚合函数案例详解

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入（单行/多行及冲突处理）,SELECT实现数据检索（列选择、条件过滤、排序分页）,UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更
阅读更多...
MySQL深分页进行性能优化的常见方法

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页（deeppagination）却成为了性能优化的一个挑战,在本文中,我们将... 目录引言：深分页，真的只是“翻页慢”那么简单吗？一、背景介绍二、深分页的性能问题三、业务场景分析四、
阅读更多...
MySQL 迁移至 Doris 最佳实践方案(最新整理)

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案（适合跨库实时查询）1. 方案概述2. 环境要求3.
阅读更多...
SQL server数据库如何下载和安装

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步：打开官网下载对应文件第二步：程序安装配置第三部：安装工具SQL Server Manageme
阅读更多...
C#连接SQL server数据库命令的基本步骤

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用：如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.
阅读更多...
全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念：DATEDIFF 究竟在计算什么？2. 主流数据库中的 DATEDIFF 实现2.1
阅读更多...
MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析
阅读更多...
MySQL中的LENGTH()函数用法详解与实例分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集（如UTF-8）的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1：计算字符串
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2