Mysql_Note7

1.日志(上)

MySQL 的日志种类非常多,包括通用查询日志,慢查询日志,错误日志,二进制日志,中继日志,重做日志和回滚日志

1.1通用查询日志

通用查询日志记录了所有用户的连接开始时间和截止时间,以及发给 MySQL 数据库服务器的所有 SQL 指令

查看通用查询日志是否开启

show variables likes '%general%'
1

开启通用查询日志

set global general_log ='on'
set] @@global.general_log_file='H:\mytest.log'
1
2

通用日志格式

2021-04-05T06:39:53.621980Z 28 Connect zhangsan@localhost on using SSL/TLS   -- 账号zhangsan从本地登录
2021-04-05T06:39:53.622085Z 28 Connect Access denied for user 'zhangsan'@'localhost' (using password: NO)  -- 没有使用密码，连接被拒绝了
2021-04-05T06:40:02.522303Z 29 Connect zhangsan@localhost on using SSL/TLS
2021-04-05T06:40:02.522913Z 29 Query select @@version_comment limit 1
2021-04-05T06:40:14.211511Z 29 Query SELECT *
FROM demo.invcount                         -- 查询数据表demo.invcount内容
2021-04-05T06:40:37.647625Z 29 Query UPDATE demo.invcount
SET plquant = - 5                          -- 更新数据表demo.invcount
WHERE itemnumber = 1
2021-04-05T06:41:15.047067Z 29 Query SELECT *
FROM demo.goodsmaster                      -- 查询数据表demo.goodsmaster
1
2
3
4
5
6
7
8
9
10
11

1.2 慢查询日志

慢查询日志用来记录执行时间超过指定时长的查询

系统变量

--min_examined_row_limit:查询扫描过的最少记录数
show variables like 'min%' 
1
2

my.ini

注意

1.如果查询扫描过的记录数大于等于min_examined_row_limit的值,并且查询执行时间超过 long_query_time 的值,那么,这个查询就被记录到慢查询日志中

2.修改了my.ini就需要重启服务

慢日志格式

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.23 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 2021-03-25T07:20:33.412260Z     -- 执行开始时间
# User@Host: root[root] @ localhost [::1] Id: 13   -- 用户
# Query_time: 10.166435 Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0                        -- 执行时长、锁表时长、检查的记录数
use demo;
SET timestamp=1616656823;
1
2
3
4
5
6
7
8

1.3 错误日志

错误日志记录了 MySQL 服务器启动,停止运行的时间,以及系统启动,运行和停止过程中的诊断信息,包括错误,警告和提示

my.ini

# Error Logging.
log-error="GJTECH-PC.err"
1
2

注意

1.错误日志默认是开启的

2.默认就是数据目录：“C:\ProgramData\MySQL\MySQL Server 8.0\Data”

错误日志格式

2021-02-28T08:07:07.228880Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.23) starting as process 7652
2021-02-28T08:07:07.270982Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-02-28T08:07:08.116433Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
1
2
3

1.4思考题

1.请你思考一下:怎么设置开启慢查询日志,并且把慢查询日志的指定时长设置为 5 秒,最少扫描记录数为 1？

2.日志(下)

2.1二进制日志

二进制日志主要记录数据库的更新事件,比如创建数据表,更新表中的数据,数据更新所花费的时长等信息

基本操作

查看二进制日志

-- 查看当前正在写入的二进制日志的名称和当前写入的位置
show master status
-- 查看二进制日志中所有数据更新事件
show binlog events in 二进制文件
1
2
3
4

刷新二进制日志

--关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件名的后缀在现有的基础上加 1
flush binary logs
1
2

用二进制日主恢复数据

--stop-position=yyy 二进制文件名 | mysql -u 用户 -p
mysqlbinlog –start-positon=xxx 
1
2

删除二进制日志

RESET MASTER
1

案例

步骤一:数据库备份

-- mysqldump -u 用户 -p 密码 数据库 > 备份文件
mysqldump -u root -p demo > mybackup.sql
1
2

步骤二:刷新日志

flush binary logs
1

步骤三:插入一条记录

INSERT INTO demo.goodsmaster
(
itemnumber,
barcode,
goodsname,
salesprice
)
VALUES
(
2,
'0002',
'笔',
3
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查看表数据

SELECT * FROM demo.goodsmaster;
1

第五步:从备份恢复数据

mysql -u 用户 -p 密码 数据库名称 < 备份文件
1

第六步

从保存增量信息的二进制日志文件“GJTECH-PC-bin.000012”中恢复增量数据

mysqlbinlog --start-position=306 '/路径/GJTECH-PC-bin.000012' | mysql -u root -p
1

注意

1.用二进制日志文件找回数据时经常会遇到问题 不容易找准起始位置或者截止位置, 找早了,会导致数据冲突重复, 找晚了又会丢失数据, 建议你在数据备份结束之后,把当前的二进制日志位置记录下来,存放在一个安全的地方,这关系到数据恢复的完整性,一定不要怕麻烦

2.2中继日志

中继日志只在主从服务器架构的从服务器上存在, 从服务器为了与主服务器保持一致,要从主服务器读取二进制日志的内容,并且把读取到的信息写入本地的日志文件中,这个从服务器本地的日志文件就叫中继日志

SET TIMESTAMP=1618558728/*!*/;
BEGIN
/*!*/;
# at 950
#210416 15:38:48 server id 1  end_log_pos 832 CRC32 0xcc16d651   Table_map: `demo`.`test` mapped to number 91
# at 1000
#210416 15:38:48 server id 1  end_log_pos 872 CRC32 0x07e4047c   Delete_rows: table id 91 flags: STMT_END_F   -- server id 1 是主服务器，意思是主服务器删了一行数据

BINLOG '
CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw=
CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw==
'/*!*/;
# at 1040
1
2
3
4
5
6
7
8
9
10
11
12
13

中继日志包含从服务器名字,如果从服务器宕机导致重装系统,在恢复数据时需要将名称改回之前的名称

2.3回滚日志

回滚日志的作用是进行事务回滚 , 回滚日志中记录了事务中每次数据更新前的状态, 当事务需要回滚的时候,可以通过读取回滚日志,恢复到指定的位置, 另一方面,回滚日志也可以让其他的事务读取到这个事务对数据更改之前的值，从而确保了其他事务可以不受这个事务修改数据的影响

SHOW VARIABLES LIKE '%innodb_max_undo_log_size%';
1

单个回滚日志最大可占用 1G 字节存储空间

SHOW VARIABLES LIKE '%innodb_undo%';
1

2.4重做日志

作用

1.在系统遇到故障的恢复过程中,可以修复被未完成的事务修改的数据

2.MySQL 为了提高数据存取的效率,减少磁盘操作的频率,对数据的更新操作不会立即写到磁盘上,而是把数据更新先保存在内存中，积累到一定程度，再集中进行磁盘读写操作。这样就存在一个问题：一旦出现宕机或者停电等异常情况，内存中保存的数据更新操作可能会丢失。这个时候就可以通过读取重做日志中记录的数据更新操作，把没来得及写到磁盘上的数据更新写到磁盘上，确保数据的完整性

show variables like '%innodb_log_files_in_group%';
1

2.5思考题

SHOW BINLOG EVENTS IN 'GJTECH-PC-bin.000013';
1

观察一下,其中包括了哪几个 SQL 数据操作呢？为了从二进制日志中恢复这些操作,我们应该如何设置起始位置和截止位置呢？

这段二进制日志包括了2个SQL操作，第一个是从数据表demo.goodsmaster中删除一条记录，第二个是向数据表demo.goodsmaster中插入一条记录。起始位置：627，截止位置：1125 
1

3.数据备份

3.1 如何进行数据备份

1.数据备份

-- 格式
mysqldump -h 服务器 -u 用户 -p 密码 数据库名称 [表名称 … ] > 备份文件名称
-- 示例
mysqldump -u root -p demo goodsmaster membermaster > test.sql
-- 备份整个数据库服务器
mysqldump -h 服务器 -u 用户 -p 密码 --all-databases > 备份文件名

1
2
3
4
5
6
7

2.如何进行数据恢复

方式一

mysql -u root -p demo < test.sql
1

方式二

use 数据库名
source 备份文件名
1
2

3.2 表数据的导出导入

1.导出表数据

select 字段列表 into outfile 文件名
fields terminated by 字符
lines terminated by 字符
from 表名;
1
2
3
4

注意

1.服务器的“secure-file-priv”参数选项,不允许把文件写入到 H:\goodsmaster.txt

检查配置文件my.ini, 看一下“secure-file-priv”参数的设定,并且按照这个参数设定的要求准备导入文件 
1

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
1
2

2.表数据的导入

load data infile 文件名
into table 表名
fields terminated by 字符
lines terminated by 字符;
1
2
3
4

注意

一个 400 万条数据的文件,用“LOAD DATA”语句,只需要几分钟就可以完成,而其他的方法,比如使用 Workbench 来导入数据,就需要花费好几个小时