前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL(3)——日志

MySQL(3)——日志

作者头像
py3study
发布2020-01-09 19:45:45
5380
发布2020-01-09 19:45:45
举报
文章被收录于专栏:python3

MySQL数据库的并发性与锁有很大的关系:

读锁:

    是共享锁,施加后,其他人可以读,但是不能写。

写锁:

    是独占锁,施加后,其他人不能写、也不能读。

    由于数据库的读量大于写量,所以当读锁源源不断时,写锁就不能施加。所以可能采用读5个,写1个的策略施加锁就可以解决问题(具体的情况视各自的"锁策略"而定)

锁粒度:

    当锁是锁定整个表的时候,那么即使"锁策略"再好,也不会有很好的性能。所以我们可以考虑只锁定我们需要数据所在的那几个数据块。甚至只锁定我们需要那一行数据。

    锁粒度越低,并发性越好,但是需要维护的成本也越大。

    表级锁:锁定整个表

    页  锁:锁定需要的数据块

    行  锁:维护成本高,容易发生死锁:也就是A、B用户同时到来。A是先锁定第1行,再锁定第5行;B是先锁定第5行,再锁定第1行。他们都进行到第二步时,就会各自等待,这就是死锁。一般解锁的策略是让资源占用最少的解锁,具体的实现要视不同的策略、不同的公司而定。

MySQL的工作模型:

    一个线程响应一个用户,而且这个线程会长期在线,直到用户退出。但是如果使用API就可能瞬间建立,瞬间退出。

    由于涉及到AAA的安全问题,所以MySQL不能一个线程响应多个用户。但是线程的销毁、创建、授权也是需要很长的时间的,所以MySQL采用的是线程池复用的机制。也就是当用户退出后,其使用的线程不会被销毁,而只是快速清空上一个用户的数据库,等待下一个用户的连接。

SQL:

    DML:CRUD(insert、update、delete、select),数据库操作语言

    DDL:create、drop、alter,数据库定义语言。用来操作数据库对象的,比如表、索引、游标等

    DCL:grant revoke,权限管理语言。

事务的ACID特性:

    原子性:一般指一组DML(DDL无法实现)语句要么都执行完成,要么都不执行。如果只执行一部分,就回滚。

    一致性:数据库的总量保持不变,比如银行的转账操作,A减去1000,B就得加1000.

    隔离性:多个事务(SQL语句)不能同时操作一个数据。有4个隔离级别:

            读未提交:READ UNCOMMITED

            读 提 交:READ COMMITED 大多数数据库的默认

            可 重 读:REPEATABLE READ MySQL的默认,较严格

            可串行化:SERIABLIZABLE

    持久性:只要事务一提交,那么数据库的状态一定是改变的,不能被断电情况影响。它也有级别之分

            1、只要事务一提交就从内存同步到事务日志,同时也会1秒同步一次,这样大的事务不会丢失

            2、只有在事务提交时才会同步到事务日志,没有其他同步。

MySQL日志:

    mysql> SHOW [GLOBAL|SESSION] VARIABLES LIKE '%log%';

    这样子可以看到所有关于日志的变量:

        错误日志、查询日志、慢查询日志、事务日志

错误日志:

    默认开启,且在datadir的根目录下,文件名是"HOSTNAME.err"

    可以在/etc/my.cnf中定义

        log_error=/path/to/NAME.err,

        log_warnings = {1|0}警告信息是否一并记录到错误日志

    记录内容如下:

    1、MySQL启动、关闭过程中的信息,未必是错误信息。

       会包含sock文件找不到、MySQL未初始化

       还比如会反解0.0.0.0到本地失败的信息

    2、服务器运行过程中的错误信息

    3、时间调度器运行一个时间时产生的信息

    4、在从服务器上启动从服务器进程时产生的信息。

查询日志:

    默认不启用,因为会严重降低性能。

代码语言:javascript
复制
log                OFF
        是否开启日志功能
log_output         FILE/TABLE/NONE
        在默认的mysql数据中有一个general表,用来保存查询日志
        可以同时启用、也可以选择NONE,即使已经打开日志功能,但我可以不记录

general_log        OFF
        表示是否开启查询日志的
general_log_file    /data/mydata/HOSTNAME.log

慢查询日志:

    它记录了一组DML的SQL语句从启动到执行完成的操作时长,包含了由于锁问题被阻塞的时间,用来定位问题。有的资料叫做墙上的挂钟时间。一般开启,对性能影响不大。

代码语言:javascript
复制
slow_query_log        ON
        定义是否开启慢查询日志
slow_query_log_file   /data/mydata/mysql-slow.log
        定义慢查询日志位置
long_query_time = 10.000000
        这里单位为秒,当一个SQL语句从启动到执行完成的时间超过这个时间,就会被记录
        由于有6个0,所以可以精确到微妙。
演示:       
mysql> SET GLOBAL slow_query_log=1;
mysql> SET GLOBAL long_query_time=2;
mysql> USE mysql
mysql> LOCK TABLES user write; 
        使用用户A给user表施加写锁。
mysql> SELECT * FROM user;
        使用用户B再进行查询时就会卡住。
mysql> UNLOCK TABLES;
        使用用户A释放锁。
mysql> SHOW WARNINGS;
        查看警告信息。
# tail /data/mydata/mysql-slow.log
        这里就会看到超过2秒的日志。

事务日志:

    记录DML语句本身到一个具有连续磁盘块且固定大小的文件上。

    它不是历史日志,具有幂等性,也就是多次操作后的结果都是一样的。

    由于事务日志没有写入磁盘,当下一个操作需要用到上一个操作的结果时,事务日志就必须能够生成一个视图给用户查询。

    事务日志能够容纳一段时间内的事务即可,文件越大,并发性越好,但服务器宕机恢复时间越长。

    事务日志有2个或多个,写完一个再写另一个。

    事务日志在非阵列存储的情况下,必须不能和数据库文件放到同一个磁盘上,因为会影响性能。

代码语言:javascript
复制
innodb_log_group_home_dir    ./  
        定义事务日志组(也就是ib_logfile10 和ib_logfile1 )的位置。
        ./表示在datadir所在目录
innodb_log_file_size          5242880 
        默认每个事务日志文件的大小是5M
innodb_log_buffer_size        8388608  
        由于事务需要先在内存中完成,然后再同步到事务日志中去,
        所以这里定义事务在内存中占用的空间,默认为8M。
        为了防止断电等意外,不能定义太大。而太小了,又会影响性能
innodb_flush_log_at_trx_commit    1
        为了防止mysql进程崩溃造成内存中的事务丢失,所以有了此选项
        1,默认的,表示只要事务一提交,就同步到事务日志文件,
            而且不管事务提交与否,都会隔1秒再同步一次。
            定义为1,安全性有保证,但是性能会下降
        2,表示只有事务提交时才会同步,没有后面的每秒再次同步了。
innodb_mirrored_log_groups        1
        在服务器级别对事务日志做镜像,防止单个磁盘坏掉影响业务。
        如果硬件是RAID10,就没有必要开启此项了。

二进制日志:

    用途:用来记录修改表数据,或有可能引起表数据改变的SQL语句、发生时间、执行时长等,当不会保留只用来查询的SELECT语句。而且对于slave,就是复制二进制日志的。

代码语言:javascript
复制
log_bin            ON
        最关键的开关,这样就会保存到/etc/my.cnf中默认的位置上。
        也可以直接跟上PATH
sql_log_bin        ON
        只用来控制会话级别的二进制日志功能的开关,对于全局是不生效的。查看也是一样
        一般在利用二进制日志进行还原时,必须使用此项,关闭会话级别的二进制日志。
        否则只会突然增加二进制日志的量,不便于管理。
        关闭方法:set session sql_log_bin=0
binlog_format      MIXED
        当一个SQL语句调用的是date()函数插入到字段中时,
        如果二进制日志只是记录原格式的SQL语句,那么在使用二进制日志恢复时,
        必然会导致二者的时间数据不一致。
        所以二进制日志由很多格式:
        statement:纯粹的记录SQL语句本身,数据量较小。
        row      : 记录行的内容,数据量大,但是精准。 
        mixed    : 混合的,由MySQL自行判断。
binlog_stmt_cache_size    32768 
        与上面的binlog_format相关,也就是当使用satement格式时,分配的内存。
binlog_cache_size         32768
        刚开始使用二进制日志时分配的内存大小,32KB
max_binlog_cache_size     18446744073709547520
        如果二进制日志需要更大内存,这就是它的上限,接近无限大了。
max_binlog_size           1073741824 
        一个二进制文件的最大值,默认是1G,超过后会自动滚动。
sync_binlog                0
        事务提交时,是否将二进制日志立刻写入磁盘。
        0,默认值,表示否
        1,是,性能会下降,但是为了安全性值得更改。
expire_logs_days            0
        用来设定二进制日志的过期天数,超过此值的二进制日志会被删除
        默认为0,表示不启用

二进制日志的查看:

代码语言:javascript
复制
# file mysql-bin.000001 
    mysql-bin.000001: MySQL replication log
        这里就告诉我们这是一个复制的日志,由于不是ASCII,所以不能用cat查看
# file mysql-bin.index 
    mysql-bin.index: ASCII text
# cat mysql-bin.index 
    /var/lib/mysql/mysql-bin.000001
	这里说明了处于启用状态的二进制日志文件有哪些个。
	所以不要手工暴力删除二进制文件,否则会引起各种错误	
代码语言:javascript
复制
查看正在使用二进制文件有哪些个:
mysql> SHOW {BINARY | MASTER} LOGS;
wKiom1SmmyqAlGOfAAKzbUUq8mM459.jpg
wKiom1SmmyqAlGOfAAKzbUUq8mM459.jpg

 查看当前正在写入的二进制日志及位置   

代码语言:javascript
复制
mysql> show master status;
        不能使用BINARY了,
        Postion:在记录每条二进制日志(也就是每个SQL语句)时,后面都会附加这条日志的元数据信息,比如执行时间等。由于此日志文件是二进制的,所以每个字符都会有自己的位置。
        所以,每个事件的位置就是此事件相对于此文件的位置。而下一个事件开始的位置就是上一个事件结束的位置。
        正如下面看到的,每个二进制文件并不是从1开始的,因为他要积累二进制日志自身的一些数据,所以基本都会从107开始。
wKioL1SmnPOykx_cAAHsdFEvoJE524.jpg
wKioL1SmnPOykx_cAAHsdFEvoJE524.jpg

查看二进制日志的内容:

代码语言:javascript
复制
mysql> SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
        默认值显示mysql-bin.index中的第一个二进制日志内容
        如果查看二进制文件会看到比这里更详细的内容
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000006' FROM 173 LIMIT 2;
        查看第六个二进制日志然后从173这个位置开始,向后查看2个事件。
wKioL1SmoAniYhhzAASa5IWslg4937.jpg
wKioL1SmoAniYhhzAASa5IWslg4937.jpg
wKiom1SmoLXhswzhAAIQu5gHGpo672.jpg
wKiom1SmoLXhswzhAAIQu5gHGpo672.jpg

在shell命令行中查看二进制日志的方法:

代码语言:javascript
复制
# mysqlbinlog mysql-bin.00001
        命令行工具,最好不要打开正在写入的文件,否则可能造成日志滚动
	end_log_pos 173     结束位置,是下一个的开始位置
	# at 173            开始位置
	#140720  2:41:23    从那个时间开始
	server id 1         用在复制场景中,每个服务器的标识
	Query	            表示这是一个语句
	thread_id=2	    哪一个会话线程创建的此语句。
	exec_time=0         执行时长,单位是秒,意味着在一秒钟完成了,不精确
	error_code=0        错误代码,0表示没有错误
	SET 语句             都是做环境预设
	
    --start-datetime=#
    --stop-datetime=#

    --start-position=#    查看时选择开始位置
    --stop-position=#     结束的位置  截止到不想要的位置

使用二进制日志回复数据库:

代码语言:javascript
复制
比如刚不小心drop了一个数据库
#mysqlbinlog mysql-bin.00001 > /tmp/a.sql
	这样将文件内容导入文件,然后将文件在从服务器上执行一遍
	如果删除一个数据库,那么二进制日志的编号需要指定从创建那一刻开始的,否则如果只指定最后一个文件,会报错。
mysql> SET SESSION sql_log_bin=0;
        临时关闭二进制日志	
mysql> source  /tmp/a.sql
	此路径mysql用户必须有权限访问

正确删除二进制日志:

    由于二进制日志太重要,一般都不会删除,即使删除,也需要现将其备份后再删除。

    但是在确定做过备份,且二进制日志没有用的情况下,可以使用purge命令安全删除。

代码语言:javascript
复制
mysql> PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

mysql> purge binary logs to 'mysql-bin.000026';
        删除不包括26这日志文件的以前的所有日志。
msyql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';  
        删除这个时间点以前的日志,这个时间久从mysqlbinlog命令查看得来的

滚动二进制日志:

    造成二进制文件的滚动的原因有很多,比如重启mysql服务器等,但也可以手动滚动。

代码语言:javascript
复制
mysql> HELP FLUSH;
    FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH
READ LOCK 
    这些命令都可以滚动二进制日志。一般常用FLUSH LOGS;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019/08/25 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档