自贡建设网站,仿v2ex wordpress,seo工作室,aso优化师一、MySQL日志管理
1.为什么需要日志
用于排错用来做数据分析了解程序的运行情况#xff0c;了解MySQL的性能
2.日志作用
在数据库保存数据时#xff0c;有时候不可避免会出现数据丢失或者被破坏#xff0c;这样情况下#xff0c;就必须保证数据的安全性和完整性#…一、MySQL日志管理
1.为什么需要日志
用于排错用来做数据分析了解程序的运行情况了解MySQL的性能
2.日志作用
在数据库保存数据时有时候不可避免会出现数据丢失或者被破坏这样情况下就必须保证数据的安全性和完整性则需要使用日志来查看或者恢复数据。
3.数据丢失或被破坏的原因
误删数据库数据库工作时意外断电或程序意外终止由于病毒造成的数据库损坏或丢失文件系统损坏后系统进行自检操作升级数据库时命令语句不严格设备故障等等
4.日志文件查看方法
由于多种安装mysql的方法可能导致文件存储位置和名称不同需要先通过mysql配置文件确定错误日志文件位置及名称
通过mysql配置文件my.ini中查看C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
datadirC:/ProgramData/MySQL/MySQL Server 8.0\Data log-errorLEGION.err
位置C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.err
5.常见日志类型之错误日志
错误日志记录以下信息
服务器启动和关闭过程中的信息服务器运行过程中的错误信息事物调度器运行一个时间时产生的信息在服务器上启动从服务器进程时产生的信息
查看本机错误日志查看错误日志位置及文件名一般以主机名.err方式命名
mysql show variables like log_error;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_error | .\DESKTOP-2V2B7B2.err |
--------------------------------------
1 row in set, 1 warning (0.02 sec)
错误日志信息需要注意三类[System]、[Warning]、[Error]
注意my.ini中可以用log-errorfile_name 选项来指定mysqld保存错误日志文件的位置。
6.常见日志类型之通用查询日志
由于数据库一般有多条连接不一定是本地连接所以需要记录每个连接客户端的所有操作包括启动和关闭 MySQL 服务、更新语句和查询语句等从而产生查询日志。
查看设置状态
mysql show global variables like %general_log%;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| general_log | OFF | # 默认关闭
| general_log_file | DESKTOP-2V2B7B2.log | # 存储位置
---------------------------------------
2 rows in set, 1 warning (0.01 sec)
启动记录查询日志
mysql set global general_logon;
Query OK, 0 rows affected (0.02 sec)mysql show global variables like %general_log%;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| general_log | ON |
| general_log_file | DESKTOP-2V2B7B2.log |
---------------------------------------
2 rows in set, 1 warning (0.00 sec)# 注意这是临时更改若永久更改则在my.ini中配置general-log1并重启服务
# 注意可以在my.ini中设置general_log_file路径/文件名 的形式设置永久更改存储位置
设置日志记录类型表/文本文件/空
mysql show variables like %log_output%;
----------------------
| Variable_name | Value |
----------------------
| log_output | FILE |
----------------------
1 row in set, 1 warning (0.00 sec)# 注意可以在my.ini中设置log-output{TABLE|FILE|NONE}类型
关闭查询日志
mysql set global general_log0;
Query OK, 0 rows affected (0.01 sec)mysql show global variables like %general_log%;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| general_log | OFF |
| general_log_file | DESKTOP-2V2B7B2.log |
---------------------------------------
2 rows in set, 1 warning (0.00 sec)
注意
要启用通用查询日志需要至少配置general-log1log-output{TABLE|FILE}general_log_file如果没有指定默认名是主机名.log默认通用查询日志是不开启的因为会消耗大量的磁盘空间、CPU以及内存所以当需要通过查询日 志还原操作场景准确定位问题时可以短时间开启
7.常见日志类型之慢查询日志
慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
解释若某条查询语句的执行时间过长超过了设定的阈值则就会记录到慢日志中可以对其进行后期 select 语句的优化
查看慢查询日志状态
mysql show global variables like %slow_query_log%;
-----------------------------------------------
| Variable_name | Value |
-----------------------------------------------
| slow_query_log | ON |
| slow_query_log_file | DESKTOP-2V2B7B2-slow.log |
-----------------------------------------------
2 rows in set, 1 warning (0.00 sec)
开启慢查询日志
mysql set global slow_query_log1; # 0为关闭C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION-slow.log# 注意为了服务器调优建议开启
慢日志的时间阈值
mysql show global variables like long_query_time;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 | # 默认10秒最小为0,精度可以到微秒
----------------------------
1 row in set, 1 warning (0.00 sec)
8.*常见日志类型之二进制日志
1二进制日志作用
记录所有更改数据的语句insert、update、delete等不记录查询语句用于主从复制因为从服务器需要到主服务器里拷贝二进制日志然后根据二进制日志的内容去执行 SQL语句从而达到主从服务器里的数据一模一样用于恢复数据日志审计的场景用户可以通过二进制日志中的信息来进行审计判断是否有对数据库进行注入攻击 mysql注入攻击黑客可以提交一段数据库查询代码根据程序返回的结果获得某些想要得到的数据 2查看二进制日志状态
mysql show variables like %log_bin%;
3日志查看
# 查看有哪些二进制日志文件
mysql show binary logs; # 显示名称、容量单位字节、加密与否# 查看当前正在使用的是哪一个二进制日志文件
mysql show master status;# 查看二进制日志内容
mysql show binlog events in DESKTOP-2V2B7B2-bin.000001;# 使用命令mysqlbinlog查看二进制日志内容
# 打开命令提示符
C:\Users\Administratorcd C:\ProgramData\MySQL\MySQL Server 8.0\DataC:\ProgramData\MySQL\MySQL Server 8.0\Datamysqlbinlog DESKTOP-2V2B7B2-bin.000001
test.sql4日志刷新
作用通过刷新日志进行更新日志对缓存数据进行磁盘I/O并强制 mysqld来关闭和重新打开日志文件或者在某些情况下切换到一个新的日志
由于日志的记录不是直接写入日志文件中而是使用日志缓存的方式当频繁执行多条修改的sql语句时为了避免对磁盘频繁I/O会将日志记录写入到内存的特殊空间即日志缓存中之后每隔一个固定时间间隔将缓存的日志写入到磁盘文件中
刷新日志命令格式
mysql flush logs # 会产生新日志文件
mysql show master status;
# 在shell中通过mysqladmin命令执行日志刷新
mysqladmin flush-logs -u 账户名 -p
mysqladmin refresh -u 账户名 -p
5二进制日志是记录执行的语句还是执行后的结果数据呢分为三种情况
假如一个表有10万行数据而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加 1000 UPDATE sales.january SET amountamount1000;
此时如果要记录执行后的结果数据的话日志会非常大。因此在这种情况下应记录执行语句。这种方式就是基于语句的二进制日志。
如果向某个字段插入的是当前的时间呢如下
INSERT INTO tb SET BirthdateCURRENT_TIME();
此时就不能记录语句了因为不同时间执行的结果是不一样的。此时应该记录这一行的值这种就是基于行row的二进制日志。
在有些情况可能会结合两种方式来记录这种叫做混合方式的二进制日志。
6删除二进制日志文件
二进制日志文件不能直接删除的如果使用 rm 等命令直接删除日志文件可能导致 数据库的崩溃。必须使用命令 PURGE 删除日志语法如下
PURGE { BINARY | MASTER } LOGS { TO log_name | BEFORE datetime_expr }
9.常见日志类型之事物日志
作用记录InnoDB等支持事务的存储引擎执行事务时产生的日志。
事务是一种机制一个操作序列它包含了一组数据库操作命令并且把所有的命令作为一个整体一起 向系统提交或撤销操作请求。要么都执行要么都不执行。
解释比如在区块链技术中支出时本地账本会记录同时同步全网收入时也会记录并同步若执行支出 时系统崩溃则会出现问题采用事务日志记录见执行的事务以便与数据回滚等操作.
二、MySQL备份
1.备份类型
根据服务器状态可以分为热备份、温备份、冷备份
热备份读、写不受影响温备份仅可以执行读操作冷备份离线备份读、写操作均中止
从对象来分可以分为物理备份与逻辑备份
物理备份复制数据文件逻辑备份将数据导出至文本文件中
从数据收集来分可以完全备份、增量备份、差异备份
完全备份备份全部数据增量备份仅备份上次完全备份或增量备份以后变化的数据差异备份仅备份上次完全备份以来变化的数据
2.逻辑备份优缺点
在备份速度上两种备份要取决于不同的存储引擎
1物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表。
2逻辑备份保存的结构通常都是纯ASCII的所以我们可以使用文本处理工具来处理
3逻辑备份有非常强的兼容性而物理备份则对版本要求非常高
4逻辑备份也对保持数据的安全性有保证
逻辑备份的缺点
逻辑备份要对RDBMS产生额外的压力而裸备份无压力逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩逻辑备份可能会丢失浮点数的精度信息
3.备份内容
数据文件日志文件比如事务日志二进制日志存储过程存储函数触发器配置文件十分重要各个配置文件都要备份用于实现数据库备份的脚本数据库自身清理的Crontab等……
4.备份工具
MySQL自带的备份工具--mysqldump是mysql数据库管理系统自带的逻辑备份工具支持完全备份增加备份速度相对较慢适合中小型数据库支持所有引擎备份策略第一次完全备份每天一次增量备份每周再做一次完全备份如此一直重复
全备语法
mysqldump -u用户名 --password密码 -A 绝对路径\备份文件.sql
备份库和表语法
mysqldump -u用户名 --password密码 数据库名 表1 表2 绝对路径\备份文件.sql
示例1使用mysqldump工具实现全量备份
# 原理必须先做全量备份使得数据库有一个基准还原点在做增量备份还原差异数据# 注意在命令提示符中处理不要在powershell中# 切换到命令提示符终端完成全备
C:\Users\Administrator mysqldump -u root --password123456 -A C:\back1.sql
mysqldump: [Warning] Using a password on the command line interface can be
insecure. # 警告表示将密码显示在屏幕上不安全备份的盘符一定要和MySQL安装目录在同一个盘符中
要不然会提示拒绝访问# 切换终端到mysql
C:\Users\Administrator mysql -uroot -p
Enter password: ******mysql drop database mydb1_test; # 删库跑路mysql drop database mydb2_stuinfo;
……mysql show databases; # 查看数据库mysql source C:\back1.sql # 全库恢复示例2局部备份恢复
# 切换到命令提示符终端完成全备
C:\Users\Administrator mysqldump -u root --password123456 -B mydb1_test
C:\back2.sql# 切换终端到mysql
C:\Users\Administrator mysql -uroot -p
Enter password: ******mysql drop database mydb1_test; # 删库跑路mysql drop database mydb1_test;# 开始恢复
mysql create database mydb1_test; # 需要先创建待恢复的数据库mysql use mydb1_test;mysql source C:\back2.sqlmysql show tables; # 查看已还原mysql select * from t1 ; # 查看表
文件系统备份工具
cp命令 冷备份支持所有引擎复制命令只能实现冷备物理备份。使用归档工具cp命令 对其进行备份的备份速度快还原速度几乎最快但是灵活度很低可以跨系统但是跨平台能力很差。lvm 几乎是热备份支持所有引擎基于快照(LVMZFS)的物理备份速度非常快几乎是热备。 只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用所以备份速度比较 快恢复速度比较快没有什么弹性空间而且LVM的限制不能对多个逻辑卷同一时间进行备份 所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
其它工具
ibbackup 商业工具 MyISAM是温备份InnoDB是热备份 备份和还原速度都很快这个软件它的每服务器授权版本是5000美元xtrabackup 开源工具 MyISAM是温备份InnoDB是热备份 是ibbackup商业工具的替代工具mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称 mysqlbackup
