第十二章《mysql的日志优化》

一、日志 1.redo、undo 2.mysql主要的日志：1、错误日志2、查询日志（普通查询日志和慢查询日志）3、二进制日志

错误日志： 错误日志记录mysql服务器启动和停止以及运行过程中出现的错误或问题； 默认情况下，错误日志是关闭的。 默认路径是在数据目录下；错误日志的主要作用，记录错误信息帮助我们解决问题，刷新日志flush logs的时候，错误日志会重新加载（5.7版本前），将原先的错误日志保存为以old结尾的文件，然后再重新创建一个错误日志（5.7版本后） 开启错误日志 1.修改配置文件： Windows：my.ini linux：my.cnf 在【mysql】下面添加： log-erro=路径＋文件名 添加完重启mysql

查看错误日志路径：

在这里插入图片描述

查看日志是否开启：

在这里插入图片描述

二进制日志： 主要是记录mysql数据库的变化，不记录select、show等语句；二进制日志以事件的格式记录日志主要包括时间、数据发生变化的内容以及位置；二进制日志的作用：用来恢复数据和数据的复制； 二进制日志默认是关闭的，因为开启日志会消耗mysql性能。二进制日志尽量不要和数据目录放到同一个磁盘上。 二进制日志的配置 修改配置文件：在[mysql]下边添加 log-bin=/path＋文件名 //开启二进制并指定路径 expire_logs_days=10 //二进制日志自动删除时间，默认是10天。 max_binlog_size=100M //单个二进制日志文件的大小，默认是1G； logbin_format = mixed //日志记录的方式为混杂模式 重启mysql服务

MySQL支持statement、row、mixed三种形式的记录方式。statement模式是指路发生改变的 数据的内容，如果改变数据库的sql语句中包含了一些函数，它无法记录，所以会造成数据的部分 丢失；row形式是基于行来记录，也就是将相关行的每一列的值都在日志中保存下来，这样的结 果会导致日志文件变得非常大，但是保证了动态值的确定性。还有一种mixed形式，表示如何记 录日志由MySQL自己来决定。

开启二进制日志后，mysql会在相应的目录下创建一个index结尾的目录文件和xxx.bin mysql-bin.000001这样的日志文件，mysql-bin.index这个文件路面记录了所有的二进制日志的文件名，以6位数字结尾的日志文件我们在进行日志的刷新或者mysql服务器重启后他都会创建一个新的日志文件名的数字递增

查看二进制日志文件名和大小

在这里插入图片描述

编写etc/my.cnf

在这里插入图片描述

重启mysql

查看二进制日志的内容; 不能直接查看二进制日志文件，而是通过mysqlbinlog工具来查看 如果执行mysqlbinlog工具未找到命令 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/

mysqlbinlog mysql-bin.000012

在这里插入图片描述

通过二进制日志恢复或还原数据 使用二进制日志还原数据到最有一次备份的内容，也可以指定具体时间或者位置来还原数据； mysqlbinlog [option] 二进制日志文件 mysql -u 用户名 -p 密码；

option 我们可以用–start-date= ‘开始时间’ –stop-date= ‘结束时间’ 指定还原的时间； 还可以用–start-position = ‘开始位置’ 或 –stop-position=‘结束位置’ 指定还原位置；

在这里插入图片描述

删除二进制日志： 1.expire_logs_days = 10 在配置文件里设置自动删除； 2.手动删除： （1）RESET MASTER //删除所有的二进制日志； （2）PURGE MASTER/BINARY LOGS purge master/binary logs to ‘二进制日志’； // 删除指定二进制日志前的所有日志 purge master/binary logs before ‘date’； //删除指定时间之前的日志文件；（date不可以是正在进行的二进制日志里的时间）

暂时停止二进制日志功能 通过命令： SET SQL_LOG_BIN = 0/1 0代表停止，1代表恢复；

慢查询日志：为了优化查询时间太长查询语句；

开启慢查询日志： 在my.cnf里面添加 log-slow-queries = /path/filename log_query_time = second //默认阈值为10s

mysql主从复制； 指将一个mysql数据库里的数据复制到其他数据当中。 主从复制的用途； 1.读写分离：通过mysql主从复制来实现读写分离以解决读写相互阻塞的问题；读写分离也可以减轻单个数据库的压力； 主库复制写，从库负责读。 2.数据实时备份，当系统某个节点发生故障，可以方便的故障切换； 3.HA高可用：系统业务访问量增大，如果时候单机mysql的话，就会导致I/O访问频率过高，并发太大，可能会出现故障，有了 主从复制，通过增加多个数据存储节点，将负载分布到多个从节点上，降低单机I/O的频率，提高I/O性能

主从复制的形式： 一主多从，多主一从，互为主从，级联复制

1.一主多从 ：最常见的一种形式， 作用：读写分离，高可用，实时数据备份； 2.多主一从：mysql-5.7开始支持，主要功能就是备份数据 3.主主复制：两台mysql互为主从，即是master节点也是slave节点，物理任何一方数据发生变更都会通过复制应用到另外一方数据库中； 4.级联复制：如果一个主上边连接的从节点太多，会消耗大量的主节点的性能用于replication，为了解决这个问题，采用级联复制的方式; 主节点上只连接3到5个从节点，其他的从节点连接在这几个从节点上进行复制，这样不仅可以缓解主节点的压力，也对数据的一致性没有负面影响。

MySQL主从复制原理 mysql主从复制涉及到3个线程，一个是运行在主节点上的（log dump thread），其余两个是运行在从节点上的（I/O thread，SQL thread）

主节点binary log dump线程： 当从节点连接主节点时，主节点会创建一个log dump线程，用于发送bin-log的内容，当读取bin-log日志时，此线程会对主节点上的bin-log加锁，当读取完成，甚至是发送给从节点之前，锁会被释放；

从节点的I/O线程： 当从节点执行’start slave‘命令之后，从节点会创建一个I/O线程用来连接主节点，请求主节点更新的bin-log。I/O线程接收到主节点的bin-log保存到本地的relay-log（中继日志）中。

从节点的sql线程; sql线程负责读取relay-log中的内容，解析 成具体的操作sql语句并执行，将数据写入到库中实现主从数据的一致性

对于每一个主从连接都需要这3个线程来完成，当主节点有多个从节点时，主节点会为每一个从节点生成一个dump线程，mysql默认的复制模式是异步的。而从库上的I/O和sql线程他们负责的工作是分开的，这样从节点的I/O线程只要拉取到了主的bin-log并写入到relay-log中，即使在sql线程没有执行写入操作时，从节点故障，也能保证数据的一致性，当从节点恢复运行后，sql线程会继续完成工作，另外sql线程会在相应的表工作不繁忙的时候进行写入操作。

因此要实现主从复制，主节点必须要打开bin-log功能；

GTID复制功能; 主节点更新数据时，会在事务前产生GTID，一起记录到bin-log当中，从节点的I/O线程将变更的bin-log写入到本地的relay-log中，sql线程从relay-log中获取GTID，然后对比本地的bin-log日志，是否有记录（所以从节点也需要开启bin-log），如果有，说明已经执行过了，从节点就会忽略，如果没有记录，那么从节点就会从relay-log中执行GTID的事务。并记录bin-log日志

主从复制的模式; 1.异步复制：主节点不会主动push bin log到从节点，这样有可能导致failover（故障切换）的情况下从节点并没有及时的将最新的bin-log同步到本地。

2.半同步复制：这种模式下主节点只需要受到其中一台从节点的确认信息，就会commit；否则他会等待，直到超时时间然后切换异步模式再commit；这样做的目的是主从数据库的数据延迟缩小，可以提高数据安全性，确保有一台从节点和主节点数据完全一致，性能上会有一定降低，响应时间变长

3.全同步复制：主节点等所有的从节点都发送了确认信息才commit；

mysql的备份 1、备份的必要性 在生产环境中，为列防止硬件故障、软件故障、自然灾害、误操作。等各种原因导致的数据库数据丢失后能恢复到只顾之前的状态，我们需要对数据库进行备份和恢复操作，数据库的备份和恢复是非常重要的巩固走，数据的备份不是最终目的，数据的恢复才是 2、备份需要注意的事项： 1.最多能容忍少数据丢失； 2.恢复数据需要在多长时间内完成 3.需要恢复哪些数据 4.定期测试备份的可用性并提高恢复操作的效率 5.备份时的服务器负载 6.锁定资源的时长

3、备份的类型： a.按照备份的数据集的范围分类； 完全备份：整个数据库； 部分备份：数据集的一部分，比如部分表 b.按照数据的变化分类； 全量备份：这个数据集； 增量备份：仅备份上一次全量备份或增量备份以来发生变化的那部分数据； 差量备份：仅备份上一次全量备份以来发生变化的那部分数据 c.按照操作对象分类 物理备份：直接从磁盘复制数据文件进行备份；（cp、tar） 逻辑备份：从数据库导出数据另存在一个或多个文件中，将数据转化为具体的sql语句 d.按照数据库服务备份时的运行状态分类； 热备：读写操作均可进行的状态下进行备份； 温备：可读但不可写的状态下进行备份；

3.备份的策略； 备份一般都是 全量+差量+binlogs 或者 全量+增量+binlogs这种策略；

MySQL备份工具 1.mysqldump: mysql自带的备份工具。mysqldump是一个逻辑备份工具，它的本质就是将数据库数据转换为可执行的sql脚本。可以用来做完全备份和部分备份，支持innodb存储弓|擎的热备，myisam存储弓|擎的温备功能。 Mysqldump [option] 数据库或数据库.数据表 > /bakpath/文件名.sql

-u：指定用户 -p： 指定用户密码 -h：指定服务器主机 -R：导出存储过程和自定义函数 -d：只备份表结构，不备份表数据 -t：只备份表数据，不备份表结构 -A/–all-databases：备份所有库； -B： 备份多个库； –lock-all-tables：对要备份的数据的所有表加读锁； –lock-tables： 在备份时给单个表加读锁，即使要备份整个数据库，他也是备份哪张表加锁 –single-transaction： innodb引擎的表用这个参数可以实现热备 –master-data=[0/1/2] 0：不记录备份那一刻二进制日志的位置信息 1：记录备份那一刻二进制日志的位置信息，并且不注释 2：记录备份那一刻二进制日志的位置信息，并且注释

mysqldump还原的时候我们需要创建和要还原备份的库; 2.直接复制整个数据库目录: 通过qp命令复制我们库文件到备份目录(mysq|必须停机才有效) ，还原将备份的文件再移动到 mysq|的数据目录下(给数据目录重新给mysq|用户授权)，然后还原成功。 3.mysqlhotcopy备份(只能为myisam引擎备份, innodb不可用) ; 语法: mysqlhotcopy db_ name1 db_ name2… /path/new_ dir 还原的方法同2. 4. xtrabackup 安装依赖关系: yum -y install libev yum -y install rsync perl | perl-Digest-MD5 安装rpm包 rpm -ivh percona-xtrabackup-80-8.0.4-1.el7.x86_ 64.rpm 使用xtrabackup进行备份: 1.创建一个备份的目录， 为mysq|用户给这 个命令授权; 2.备份的语法： xtrabackup/innobackup [option] /path

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/101966.html原文链接：https://javaforall.cn