新做的网站如何,低价网站建设优化公司,江汉建站公司,网站开发济南华子目录 MySQL日志管理为什么需要日志日志作用日志文件查看方法错误日志通用查询日志慢查询日志示例 撤销日志重做日志二进制日志---重要中继日志 MySQL备份备份类型逻辑备份优缺点备份内容备份工具导入sql文件 MySQL日志管理
为什么需要日志
用于排错用来做数据分析了解程序… 华子目录 MySQL日志管理为什么需要日志日志作用日志文件查看方法错误日志通用查询日志慢查询日志示例 撤销日志重做日志二进制日志---重要中继日志 MySQL备份备份类型逻辑备份优缺点备份内容备份工具导入sql文件 MySQL日志管理
为什么需要日志
用于排错用来做数据分析了解程序的运行情况了解MySQL的性能
日志作用
在数据库保存数据时有时候不可避免会出现数据丢失或者被破坏这样情况下就必须保证数据的安全性和完整性则需要使用日志来查看或者恢复数据
日志文件查看方法
由于多种安装mysql的方法可能导致文件存储位置和名称不同需要先通过mysql配置文件确定错误日志文件位置及名称
[rootmysql ~]# cat /etc/my.cnf
log-error/var/log/mysqld.log #错误日志查看mysql数据目录中的日志文件
[rootmysql ~]# cat /etc/my.cnf
datadir/var/lib/mysql错误日志
错误日志记录以下信息
服务器启动和关闭过程中的信息服务器运行过程中的错误信息事件调度器运行一个时间是产生的信息在从服务器上启动从服务器进程是产生的信息
#查看错误日志位置及文件名一般以主机名.err方式命名
mysql show variables like log_error;
------------------------------------
| Variable_name | Value |
------------------------------------
| log_error | /var/log/mysqld.log | #错误日志文件在主机中的存储位置
------------------------------------错误日志信息需要注意三类[System]、[Warning]、[Error] [System] 描述[System] 级别的日志条目通常记录的是系统级的信息比如MySQL服务的启动和停止时间、配置文件读取情况、系统变量设置等。用途这些信息对于理解数据库服务器的启动过程、配置文件的加载以及系统变量的最终设置状态非常有用。 [Warning] 描述[Warning] 级别的日志条目记录的是警告信息这些信息表明数据库操作中存在潜在的问题或不符合预期的行为但这些问题通常不会导致数据库立即停止工作或数据丢失。用途警告信息可以帮助数据库管理员DBA识别和解决可能的问题源从而避免未来可能出现的更严重问题。 [Error] 描述[Error] 级别的日志条目记录的是错误信息这些信息表明数据库操作中遇到了严重问题可能会导致数据库功能受限、性能下降或数据损坏。用途错误信息对于快速定位和解决问题至关重要。DBA应该立即关注这些条目并根据错误信息进行故障排除和修复
通用查询日志
由于数据库一般有多条连接不一定是本地连接所以需要记录每个连接客户端的所有操作包括启动和关闭MySQL服务、更新语句和查询语句等从而产生查询日志查看设置状态
mysql show global variables like %general_log%;
--------------------------------------------
| Variable_name | Value |
--------------------------------------------
| general_log | OFF | #默认关闭
| general_log_file | /var/lib/mysql/mysql.log | #通用日志文件存储位置
--------------------------------------------启动通用查询日志
mysql set global general_log1;mysql show global variables like %general_log%;
--------------------------------------------
| Variable_name | Value |
--------------------------------------------
| general_log | ON |
| general_log_file | /var/lib/mysql/mysql.log |
--------------------------------------------# 注意这是临时更改若永久更改则在my.cnf中配置general-log1并重启服务
# 注意可以在my.cnf中设置 general_log_file路径/文件名 的形式设置永久更改存储位置设置日志记录类型表/文本文件/空
mysql show variables like %log_output%;
----------------------
| Variable_name | Value |
----------------------
| log_output | FILE |
----------------------
#注意可以在my.cnf中设置log-output{TABLE|FILE|NONE}类型注意
要启用通用查询日志需要至少配置general-log1log-output{TABLE|FILE}general_log_file如果没有指定默认名是主机名.log默认通用查询日志是不开启的因为会消耗大量的磁盘空间、CPU以及内存所以当需要通过查询日志还原操作场景准确定位问题时可以短时间开启
# 可以通过下面的查询测试查询日志的文件变化
mysql show variables like %general_log%;
--------------------------------------------
| Variable_name | Value |
--------------------------------------------
| general_log | ON |
| general_log_file | /var/lib/mysql/mysql.log |
--------------------------------------------mysql use view;mysql show tables;
----------------
| Tables_in_view |
----------------
| class |
| student |
| viewInfo_stu |
| view_stu |
| view_stu1 |
----------------mysql select * from student;
-----------------------------------------------------
| id | name | age | gender | address | classid |
-----------------------------------------------------
| 1 | 文 | 21 | M | 陕西省西安市 | 1003 |
| 2 | rong | 18 | F | 陕西省榆林市 | 1001 |
| 3 | yuan | 19 | M | 陕西省西安市 | 1002 |
| 5 | hua | 10 | M | 山西省 | 1001 |
| 6 | huazi | 9 | M | 山西省 | 1002 |
-----------------------------------------------------[rootmysql ~]# cat /var/lib/mysql/mysql.log
/usr/sbin/mysqld, Version: 8.4.3 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2024-11-30T07:52:20.060357Z 15 Init DB view
2024-11-30T07:52:20.061025Z 15 Query show databases
2024-11-30T07:52:20.061583Z 15 Query show tables
2024-11-30T07:52:20.062232Z 15 Query SELECT * FROM class LIMIT 0
2024-11-30T07:52:20.063133Z 15 Query SELECT * FROM student LIMIT 0
2024-11-30T07:52:20.063937Z 15 Query SELECT * FROM viewInfo_stu LIMIT 0
2024-11-30T07:52:20.064518Z 15 Query SELECT * FROM view_stu LIMIT 0
2024-11-30T07:52:20.064896Z 15 Query SELECT * FROM view_stu1 LIMIT 0
2024-11-30T07:52:29.367548Z 15 Query show tables
2024-11-30T07:52:43.639933Z 15 Query select * from student关闭通用查询日志
mysql set global general_log0;
Query OK, 0 rows affected (0.00 sec)mysql show variables like %general_log%;
--------------------------------------------
| Variable_name | Value |
--------------------------------------------
| general_log | OFF |
| general_log_file | /var/lib/mysql/mysql.log |
--------------------------------------------慢查询日志
慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询解释若某条查询语句的执行时间过长超过了设定的阈值则就会记录到慢日志中可以对其进行后期select语句的优化查看慢查询日志状态
mysql show variables like %slow_query_log%;
----------------------------------------------------
| Variable_name | Value |
----------------------------------------------------
| slow_query_log | ON | #开启状态
| slow_query_log_file | /var/lib/mysql/mysql-slow.log | #日志文件位置
----------------------------------------------------开启慢查询日志
mysql set global slow_query_log1;
# 注意为了服务器调优建议开启慢日志的时间阈值
mysql show variables like %long_query_time%;
---------------------------
| Variable_name | Value |
---------------------------
| long_query_time | 10.00000 | #默认为10秒精度可以到微秒
---------------------------示例
mysql show variables like %slow_query_log%;
-------------------------------------------------------
| Variable_name | Value |
-------------------------------------------------------
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
-------------------------------------------------------mysql show variables like %long_query_time%;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------mysql set global slow_query_log1;mysql select sleep(11);
#等待11秒[rootmysql-node1 ~]# cd /data/mysql/
[rootmysql-node1 mysql]# cat mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-12-01T14:05:19.572448Z
# UserHost: root[root] localhost [] Id: 2
# Query_time: 11.008686 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use db_hua;
SET timestamp1733061919;
select sleep(11);撤销日志
Undo-log日志即撤销日志或回滚日志Undo即撤销的意思在日常开发过程中如果代码敲错了一般会习惯性的按下CtrlZ撤销而Undo-log的作用也是如此但它是用来给MySQL撤销SQL操作的。Undo-log日志记录内容 若是insert插入操作则生成一个对应的delete操作。若是delete删除操作InnoDB中会修改隐藏字段deleted_bit1则生成改为0的语句。若是update修改操作如将性别从男改成了女则就生成一个从女改回男的操作 可以理解为当一条写入类型的SQL执行时都会在Undo-log日志中生成相应的反SQL放入到Undo-log中
#从mysql8.0.20版本开始存储位置
[rootmysql ~]# cd /var/lib/mysql/
[rootmysql mysql]# ls #以undo开头的文件是撤销日志
undo_001
undo_002重做日志
InnoDB引擎在设计时是基于磁盘存储数据的当MySQL启动后就会在内存中创建一个BufferPool运行过程中会将大量操作汇集在内存中进行比如写入数据时先写到内存中然后由后台线程再刷写到磁盘虽然使用BufferPool提升了MySQL整体的读写性能但它是基于内存的也就意味着随着机器的宕机、重启其中保存的数据会消失当向内存中写入数据后MySQL突然宕机了则这条未刷写到磁盘的数据会丢失也正由于该原因redo-log应运而生redo-log重做日志是一种预写式日志即在向内存写入数据前会先写日志当后续数据未被刷写到磁盘、MySQL崩溃时就可以通过日志来恢复数据确保所有提交的事务都会被持久化mysql8中Redo-log存储在/var/lib/mysql/#innodb_redo目录下由32个文件组成有两种类型的redo log文件一种是当前正在使用的 #ib_redoN另一种是空闲的文件名为 #ib_redoN_tmp多加了个_tmp后缀
mysql select * from performance_schema.innodb_redo_log_files\G;
*************************** 1. row ***************************FILE_ID: 148FILE_NAME: ./#innodb_redo/#ib_redo148START_LSN: 484671488END_LSN: 487946240SIZE_IN_BYTES: 3276800IS_FULL: 0
CONSUMER_LEVEL: 0mysql show status like %innodb%redo%;
------------------------------------------------
| Variable_name | Value |
------------------------------------------------
| Innodb_redo_log_read_only | OFF |
| Innodb_redo_log_uuid | 952782583 |
| Innodb_redo_log_checkpoint_lsn | 486687544 |
| Innodb_redo_log_current_lsn | 486687544 |
| Innodb_redo_log_flushed_to_disk_lsn | 486687544 |
| Innodb_redo_log_logical_size | 512 |
| Innodb_redo_log_physical_size | 3276800 |
| Innodb_redo_log_capacity_resized | 104857600 |
| Innodb_redo_log_resize_status | OK |
| Innodb_redo_log_enabled | ON |
------------------------------------------------二进制日志—重要
二进制日志作用
记录所有更改数据的语句insert、update、delete等不记录查询语句用于主从复制因为从服务器需要到主服务器里拷贝二进制日志然后根据二进制日志的内容去执行SQL语句从而达到主从服务器里的数据一模一样用于恢复数据日志审计的场景用户可以通过二进制日志中的信息来进行审计判断是否有对数据库进行注入攻击mysql注入攻击黑客可以提交一段数据库查询代码根据程序返回的结果获得某些想要得到的数据
#开启二进制日志
[rootmysql ~]# vim /etc/my.cnf
log-binmysql-bin #开启二进制日志#二进制日志的位置
[rootmysql ~]# cd /var/lib/mysql/
[rootmysql mysql]# lsbinlog.000001binlog.000002binlog.000003binlog.000004binlog.000005binlog.000006binlog.000007binlog.000008binlog.000009查看二进制日志状态
mysql show variables like %log_bin%;
--------------------------------------------------------------
| Variable_name | Value |
--------------------------------------------------------------
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
--------------------------------------------------------------日志查看
#查看有哪些二进制日志文件
mysql show binary logs;
-------------------------------------
| Log_name | File_size | Encrypted |
-------------------------------------
| binlog.000001 | 26826 | No |
| binlog.000002 | 181 | No |
| binlog.000003 | 158 | No |
| binlog.000004 | 158 | No |
| binlog.000005 | 83200151 | No |
| binlog.000006 | 12162 | No |
| binlog.000007 | 181 | No |
| binlog.000008 | 2753 | No |
| binlog.000009 | 2651 | No |
-------------------------------------
#显示名称、容量单位字节、加密与否# 查看当前正在使用的是哪一个二进制日志文件
mysql show master status\G;
*************************** 1. row ***************************File: mysql-bin.000001Position: 154Binlog_Do_DB:Binlog_Ignore_DB:
Executed_Gtid_Set:#查看二进制日志内容
mysql show binlog events in binlog.000001;使用命令mysqlbinlog查看二进制日志内容
# 使用命令mysqlbinlog查看二进制日志内容
[rootmysql ~]# cd /var/lib/mysql/
[rootmysql mysql]# mysqlbinlog binlog.000001分割日志新的操作会记录的新的日志文件中 使用mysqladmin刷新日志
#每刷新一次就会产生一个binlog日志
[rootmysql ~]# mysqladmin flush-logs -uroot -p
Enter password:[rootmysql ~]# mysql -uroot -p#新建数据库
mysql create database mydb12_journal;
# 查看当前正在使用的是哪一个二进制日志文件
mysql show master status;mysql show binary logs;
-------------------------------------
| Log_name | File_size | Encrypted |
-------------------------------------
| binlog.000001 | 26826 | No |
| binlog.000002 | 181 | No |
| binlog.000003 | 158 | No |
| binlog.000004 | 158 | No |
| binlog.000005 | 83200151 | No |
| binlog.000006 | 12162 | No |
| binlog.000007 | 181 | No |
| binlog.000008 | 2753 | No |
| binlog.000009 | 2695 | No |
| binlog.000010 | 202 | No |
-------------------------------------[rootmysql mysql]# mysqlbinlog binlog.000010
......
create database mydb12_journal
......日志刷新
作用通过刷新日志进行更新日志对缓存数据进行磁盘I/O并强制mysqld来关闭和重新打开日志文件或者在某些情况下切换到一个新的日志由于日志的记录不是直接写入磁盘上的日志文件中而是使用日志缓存的方式当频繁执行多条修改的sql语句时为了避免对磁盘频繁I/O会将日志记录写入到内存的特殊空间即日志缓存中之后每隔一个固定时间间隔将缓存的日志写入到磁盘文件中
刷新日志命令格式
#每刷新一次就会产生一个binlog文件
mysql flush logs;
# 查看当前正在使用的是哪一个二进制日志文件
mysql show master status;#在shell中通过mysqladmin命令执行日志刷新
[rootmysql ~]# mysqladmin flush-logs -uroot -p
Enter password:
#或但是refresh会重置binlog文件将之前的binlog文件全部删除再新建一个binlog.00001文件
[rootmysql ~]# mysqladmin refresh -uroot -p
Enter password:不小心删库后应该跑路吗
如果在线上真的删库了哪就先别想着跑路你跑不掉bin-log日志中会记录执行SQL的连接会话信息同时一般规模较大的企业都会搭建完善的监控系统会监控服务的网络连接因此当你删库后他们可以顺着bin-log → session → network-connection这条线确定执行删库SQL的IP如果你还未断开连接直接通过MySQL的命令就能定位到删库的IP因此基本上删库了是可以定位到责任人的因此当你删库后可以直接去本地找Bin-log的日志文件然后拷贝出来一份再打开最后一个文件把里面删库的记录手动移除再利用mysqlbinlog工具导出xx.SQL文件最后执行该SQL文件即可恢复删库前的数据
中继日志 Relay-log中继日志在单库中是见不到的该类型的日志仅存在主从架构中的从机上 主从架构中的从机其数据基本上都是复制主机bin-log日志同步过来并放在relay-log日志中中继日志的作用就跟它的名字一样仅仅只是作为主从同步数据的“中转站”
MySQL备份
备份类型
根据服务器状态可以分为热备份、温备份、冷备份 热备份读、写不受影响温备份仅可以执行读操作冷备份离线备份读、写操作均中止 从对象来分可以分为物理备份与逻辑备份 物理备份复制数据文件逻辑备份将数据导出至文本文件中 从数据收集来分可以完全备份、增量备份、差异备份 完全备份备份全部数据增量备份仅备份上次完全备份或增量备份以后变化的数据差异备份仅备份上次完全备份以来变化的数据
逻辑备份优缺点
在备份速度上两种备份要取决于不同的存储引擎物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表逻辑备份保存的结构通常都是纯ASCII的所以我们可以使用文本处理工具来处理逻辑备份有非常强的兼容性而物理备份则对版本要求非常高逻辑备份也对保持数据的安全性有保证逻辑备份的缺点 逻辑备份要对RDBMS产生额外的压力而裸备份无压力逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩逻辑备份可能会丢失浮点数的精度信息
备份内容
数据文件日志文件比如事务日志二进制日志存储过程存储函数触发器配置文件十分重要各个配置文件都要备份用于实现数据库备份的脚本数据库自身清理的Crontab等……
备份工具
MySQL自带的备份工具–mysqldump是mysql数据库管理系统自带的逻辑备份工具支持完全备份增加备份速度相对较慢适合中小型数据库支持所有引擎备份策略第一次完全备份每天一次增量备份每周再做一次完全备份如此一直重复
完全备份语法
[rootmysql ~]# mysqldump -u用户名 --password密码 -A 绝对路径\备份文件.sql备份库语法
[rootmysql ~]# mysqldump -u用户名 --password密码 -B 数据库名1 数据库名2 绝对路径\备份文件.sql备份库中表语法
[rootmysql ~]# mysqldump -u用户名 --password密码 数据库名 表1 表2 绝对路径\备份文件.sql示例1使用mysqldump工具实现全量备份
# 原理必须先做全量备份使得数据库有一个基准还原点在做增量备份还原差异数据
[rootmysql ~]# mysqldump -uroot --password123456 -A back1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#警告表示将密码显示在屏幕上不安全备份的盘符一定要和MySQL安装目录在同一个盘符中要不然会提示拒绝访问# 进入mysql中删库
[rootmysql ~]# mysql -uroot -p
Enter password:
mysql show databases;
--------------------
| Database |
--------------------
| events |
| huazi |
| information_schema |
| mydb12_journal |
| mydb13_indexdb |
| mydb17_transcation |
| mysql |
| performance_schema |
| sys |
| view |
--------------------mysql drop database events;
mysql drop database huazi;
......
mysql drop database view;mysql show databases;
--------------------
| Database | #这3个库root用户删不了
--------------------
| information_schema |
| mysql |
| performance_schema |
--------------------mysql quit
Bye
[rootmysql ~]# mysql -uroot -p
Enter password:#全库恢复
mysql source /root/back1.sql#发现没有对sys库做备份
mysql show databases;
--------------------
| Database |
--------------------
| events |
| huazi |
| information_schema |
| mydb12_journal |
| mydb13_indexdb |
| mydb17_transcation |
| mysql |
| performance_schema |
| view |
--------------------mysql use view;mysql show tables;
----------------
| Tables_in_view |
----------------
| class |
| student |
| viewInfo_stu |
| view_stu |
| view_stu1 |
----------------mysql select * from student;
-----------------------------------------------------
| id | name | age | gender | address | classid |
-----------------------------------------------------
| 1 | 文 | 21 | M | 陕西省西安市 | 1003 |
| 2 | rong | 18 | F | 陕西省榆林市 | 1001 |
| 3 | yuan | 19 | M | 陕西省西安市 | 1002 |
| 5 | hua | 10 | M | 山西省 | 1001 |
| 6 | huazi | 9 | M | 山西省 | 1002 |
-----------------------------------------------------示例2局部备份恢复
#对view库做备份
[rootmysql ~]# mysqldump -uroot --password123456 -B view back2.sql[rootmysql ~]# mysql -uroot -p
Enter password:#删除view库
mysql drop database view;#开始恢复
mysql source /root/back2.sql#发现已经恢复
mysql show databases;
--------------------
| Database |
--------------------
| events |
| huazi |
| information_schema |
| mydb12_journal |
| mydb13_indexdb |
| mydb17_transcation |
| mysql |
| performance_schema |
| view |
--------------------mysql use view;mysql show tables;
----------------
| Tables_in_view |
----------------
| class |
| student |
| viewInfo_stu |
| view_stu |
| view_stu1 |
----------------mysql select * from student;
-----------------------------------------------------
| id | name | age | gender | address | classid |
-----------------------------------------------------
| 1 | 文 | 21 | M | 陕西省西安市 | 1003 |
| 2 | rong | 18 | F | 陕西省榆林市 | 1001 |
| 3 | yuan | 19 | M | 陕西省西安市 | 1002 |
| 5 | hua | 10 | M | 山西省 | 1001 |
| 6 | huazi | 9 | M | 山西省 | 1002 |
-----------------------------------------------------文件系统备份工具 cp命令冷备份支持所有引擎复制命令只能实现冷备物理备份。使用归档工具cp命令对其进行备份时备份速度快还原速度几乎最快但是灵活度很低可以跨系统但是跨平台能力很差lvm几乎是热备份支持所有引擎基于快照(LVMZFS)的物理备份速度非常快几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用所以备份速度比较快恢复速度比较快没有什么弹性空间而且LVM的限制不能对多个逻辑卷同一时间进行备份所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份 其它工具 ibbackup商业工具MyISAM是温备份InnoDB是热备份备份和还原速度都很快这个软件它的服务器授权版本是5000美元xtrabackup开源工具MyISAM是温备份InnoDB是热备份 是ibbackup商业工具的替代工具mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称mysqlbackup
导入sql文件
交互式
mysql source sql文件路径非交互式
[rootmysql ~]# mysql -uroot -p123456 库名 sql文件
