MySQL(3)——日志

发布时间：2019-08-25 09:37:59编辑：auto阅读（1133）

    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、在从服务器上启动从服务器进程时产生的信息。


    查询日志：

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

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

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


    慢查询日志：

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

    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个或多个，写完一个再写另一个。

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

    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，就是复制二进制日志的。

    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，表示不启用


    二进制日志的查看：

    # 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
	这里说明了处于启用状态的二进制日志文件有哪些个。
	所以不要手工暴力删除二进制文件，否则会引起各种错误	
    查看正在使用二进制文件有哪些个：
mysql> SHOW {BINARY | MASTER} LOGS；

    wKiom1SmmyqAlGOfAAKzbUUq8mM459.jpg

        

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

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

     wKioL1SmnPOykx_cAAHsdFEvoJE524.jpg


    查看二进制日志的内容：

    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.jpgwKiom1SmoLXhswzhAAIQu5gHGpo672.jpg

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

    # 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=#     结束的位置  截止到不想要的位置

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

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


    正确删除二进制日志：

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

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

    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服务器等，但也可以手动滚动。

    mysql> HELP FLUSH;
    FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH
READ LOCK 
    这些命令都可以滚动二进制日志。一般常用FLUSH LOGS；





关键字：

上一篇： FLEX 3 COOKBOOK

下一篇： running 3 virtual ma