mysql默认不启动任何日志
mysql提供不同类型的日志记录不同的日志信息:
错误日志:不仅仅记录错误信息
一般查询日志:执行的SELECT、DML语句都有可能涉及到查询操作,默认不启用
慢查询日志:执行了很长时间(一秒钟就算是很长的查询时间、有可能资源隔离级别会导致查询时间久)的查询操作,为了定位服务器的查询性能问题诊断,一般启用该日志,时间可以指定
二进制日志:mysql独有的二进制记录格式,快速记录产生的日志信息,但是只记录DML、DDML、DCL(任何能够引起数据库改变或者潜在有可能引起改变)语句,用于实现mysql复制以及即时点恢复的重要依据。特别:mysql的主从复制功能,就是从服务器将主服务器上二进制日志所产生的所有操作读取,并保存在本地的某个日志文件(中继日志)中,由从mysql的线程操作一次,从而备份数据
中继日志:和二进制日志格式相同,但是作用和功能不完全相同,仅仅是在从服务器上要求执行一次的日志文件
事务日志:存在于有着事务功能上的引擎(INnoDB)上,保证给事务提供ACID的重要组建,实现将随机IO转换为顺序IO,保证数据提交不会丢失
查看和log相关的全局变量:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%'; +-------------------------------------------+--------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-------------------------------------------+--------------------------------------------------------------------------------------------------------------+ | aria_checkpoint_log_activity | 1048576 | | aria_log_file_size | 1073741824 | | aria_log_purge_type | immediate | | aria_sync_log_dir | NEWFILE | | back_log | 50 | | binlog_annotate_row_events二进制日志 | OFF | | binlog_cache_size | 32768 | | binlog_checksum | NONE | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_optimize_thread_scheduling | ON | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log 一般查询日志 | OFF | | general_log_file | mysql.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size //innodb的事务日志 | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_recovery_update_relay_log | OFF | | innodb_use_global_flush_log_at_trx_commit | ON | | log //是否启用所有语句的日志信息于一般查询日志,已经被废弃 | OFF | | log_bin //二进制相关日志 | OFF | | log_bin_trust_function_creators | OFF | | log_error //log输出格式 | /var/log/mariadb/mariadb.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_queries //慢查询日志 | OFF | | log_slow_rate_limit | 1 | | log_slow_verbosity | | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size //日志最大文件大小 | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log //中继日志 | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | slow_query_log //慢查日志 | OFF | | slow_query_log_file | mysql-slow.log | | sql_log_bin //手动启动二进制日志 | ON | | sql_log_off //手动关闭二进制日志 | OFF | | sync_binlog //同步日志缓存到磁盘 | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | +-------------------------------------------+--------------------------------------------------------------------------------------------------------------+ 54 rows in set (0.00 sec)
错误日志
错误日志主要记录:
服务器启动和关闭过程中的信息:mysqld daemon start|stop
服务器运行过程中的错误信息
事件调度器运行一个事件时产生的信息:EVENT产生的信息
在从服务器上启动从服务器进程时产生的信息(记录在从服务器):针对主从复制模型中
错误日志的两个选项:
| log_error //log输出格式 | /var/log/mariadb/mariadb.log
| log_warnings | 1
log_error:错误选项,对于mariadb,在配置文件中定义打开错误日志,并且日志文件保存在/var/log/mariadb/mariadb.err
log_warnings:警告选项,可以定义是否将警告信息也记录,默认记录
可以修改配置文件,或者直接在mysql运行时修改全局变量,但是在有的时候,有些全局变量值只能在重启后生效,也就只能修改配置文件。
查询日志
一般查询日志
如果开启一般查询日志,每执行一次查询相关操作,都会记录一次日志,这样会产生大量的磁盘IO;
一般查询日志相关的选项:
general_log:是否启用一般查询日志
general_log_file:一般查询日志保存文件路径
log_output:mysql也支持将一般日志信息,保存在表中(默认表不存在得自己创建)
| general_log 一般查询日志 | OFF | general_log_file | mysql.log
| log //是否启用所有语句的日志信息于一般查询日志,已经被废弃 | OFF
| log_output | FILE 默认是table,这是配置文件修改后得结果。还可以改为TABLE|NONE
慢查询日志
查询相关操作执行时长超出了指定得时间,而被记录到慢查询日志中。相关选项指令:
long_query_time:指定时长,超过该指令定义的值定义为慢查询日志,这里指的是实际查询时长,而不是在cpu上执行的时长,负载较重的mysql服务器更容易产生慢查询。最小值为0,默认为10妙
slow_query_log:是否记录慢查询,输出位置取决于log_out选项,如果为TABLE则记录在表中,为FILE则记录在文件中,NONE不记录
slow_query_log_file:记录慢查询的日志文件
| long_query_time | 10.000000
| log_slow_queries //慢查询日志 | OFF 和下面的一样,貌似是mysql历史原因
| slow_query_log //慢查日志 | OFF | slow_query_log_file | mysql-slow.log |
启用慢日志:
MariaDB [(none)]> SET GLOBAL slow_query_log=1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+
二进制日志
如果开启了二进制日志,由于mysql服务一次只能使用一个二进制日志文件,所以这很有可能成为mysql服务的性能瓶颈
二机制日志在任何存储引擎上,记录引起或者潜在引起数据库变化的操作(二进制事件),主要用于记录DDL、DML语句,甚至授权语句(潜在影响)。记录格式是二进制格式,不能使用普通文本编辑器查看,只能使用mysql提供的相关命令(mysqlbinlog),才能看到具体信息。
相关二进制日志的指令选项:
log_bin:是否开启使用二进制日志记录
binglog_format:二进制日志格式,由三种方式:statument|row|mixed
binlog_cache_size:提供二进制日志缓存,保证异步写入磁盘,(异步可能会导致数据丢失,同步带来大量的磁盘IO不可取)
sync_binlog:控制同步行为,指定多久同步一次二进制日志到磁盘中,0表示从不同步,任何整数值表示对二进制每多少次写操作之后同步一次。
expire_logs_days:日志的过期天数,即超过某个天数的日志被清除。一般情况,不能让自动清理。
mysqlbinlog工作特性比较独特,一般情况位于数据目录中,而且以mysql_bin开头,或者当前主机名开头;mysql服务器每重启一次,都会滚动一次 (滚动的方式是按规律创建新的文件)。
二进制日志的主要作用是对数据库的即时点恢复,主要是进行重放,重放操作可以生成一样的数据库(不同位置)。是实现恢复操作的重要凭据。
二进制日志的格式:
基于语句(statement):两次重放的结果一样,因为语句相对行数据记录简单
基于行数据(row):有些数据会在不同时间操作产生不同结果(CRUUENTTIME),只能基于行数据重放
混合方式(mixed):同时基于语句和行数据,折中优化的过程,不过需要自定义
二进制日志事件:
产生的时间:起始时间,甚至结束时间
相对位置:起始位置,甚至结束位置
对于读取特定的事件,可以定义产生的时间或者相对位置的方式
二进制日志启用和查看
启动mysql二进制日志:
vim /etc/my.cnf 在[mysqld]模块下添加开启信息 log_bin=1 重启mysql服务器 [root@mysql mysql]# systemctl restart mariadb
进入mysql,查看正在使用的二进制日志文件,(因为会滚动):
MariaDB [(none)]> SHOW MASTER STATUS; +----------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------+----------+--------------+------------------+ | 1.000001 | 245 | | | +----------+----------+--------------+------------------+ //Position是上一个事件结束的位置 MariaDB [(none)]> SHOW DATABASES; //SHOW不会被二进制日志记录,所以Position不变 +--------------------+ | Database | +--------------------+ | information_schema | | fsx | | fsx_test | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> SHOW MASTER STATUS; +----------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------+----------+--------------+------------------+ | 1.000001 | 245 | | | +----------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> INSERT INTO fsx.fsx (name,age) VALUES("kobe",39); Query OK, 1 row affected (0.08 sec)//当插入数据,会被记录到二进制日志中,Position位置也会变化 MariaDB [(none)]> SHOW MASTER STATUS; +----------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------+----------+--------------+------------------+ | 1.000001 | 471 | | | +----------+----------+--------------+------------------+ 1 row in set (0.00 sec)
默认情况下,二进制日志会记录在mysql的数据目录下:
[root@mysql mysql]# ls 1.* 1.000001 1.index [root@mysql mysql]# cat 1.index ./1.000001 //当前只有一个二进制日志文件,1.index是二进制索引文件(是一个文本文件)。这也可以看出来,二进制日志由两部分组成1,二进制日志数据文件 2,二进制日志索引文件 重启mysql,会进行二进制日志滚动;当然,可以在mysql下,手动滚动二进制日志: MariaDB [(none)]> FLUSH LOGS; Query OK, 0 rows affected (0.12 sec) MariaDB [(none)]> SHOW MASTER STATUS; +----------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------+----------+--------------+------------------+ | 1.000004 | 245 | | | +----------+----------+--------------+------------------+ 1 row in set (0.00 sec)
查看具体在二进制日志文件中存放的信息:
MariaDB [(none)]> SHOW BINLOG EVENTS IN '1.000001'\G *************************** 1. row *************************** Log_name: 1.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 245 Info: Server ver: 5.5.35-MariaDB-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: 1.000001 Pos: 245 Event_type: Query Server_id: 1 End_log_pos: 309 从309开始 Info: BEGIN *************************** 3. row *************************** Log_name: 1.000001 Pos: 309 Event_type: Intvar Server_id: 1 End_log_pos: 337 Info: INSERT_ID=7 *************************** 4. row *************************** Log_name: 1.000001 Pos: 337 Event_type: Query Server_id: 1 End_log_pos: 444 Info: INSERT INTO fsx.fsx (name,age) VALUES("kobe",39) //刚执行的语句 *************************** 5. row *************************** Log_name: 1.000001 Pos: 444 Event_type: Xid Server_id: 1 End_log_pos: 471 //到471结束 Info: COMMIT /* xid=7 */ //命令后面添加FROM Position,可以指定从某个位置开始
如果要根据时间查询二进制日志信息,使用mysqlbinlog命令:
[root@mysql mysql]# mysqlbinlog 1.000001 --start-datetime='2018-05-31 16:37:44' --stop-datetime='2018-05-31 16:40:10' /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180531 16:30:59 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.35-MariaDB-log created 180531 16:30:59 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' w7IPWw8BAAAA8QAAAPUAAAABAAQANS41LjM1LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADDsg9bEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAf6AwEg== '/*!*/; # at 245 #180531 16:37:44 server id 1 end_log_pos 309 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1527755864/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 309 #180531 16:37:44 server id 1 end_log_pos 337 Intvar SET INSERT_ID=7/*!*/; # at 337 #180531 16:37:44 server id 1 end_log_pos 444 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1527755864/*!*/; INSERT INTO fsx.fsx (name,age) VALUES("kobe",39) /*!*/; # at 444 #180531 16:37:44 server id 1 end_log_pos 471 Xid = 7 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
删除二进制日志
当备份完成,就可以删除二进制日志(因为二进制日志占据空间比较大)
一定不能手动去rm二进制日志文件,否则重启mysql可能会出问题
在mysql终端下,使用PURGE命令:
MariaDB [(none)]> HELP PURGE; Name: 'PURGE BINARY LOGS' Description: Syntax: PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } //删除二进制日志文件
删除1.000003以前的所有二进制日志:
MariaDB [(none)]> PURGE BINARY LOGS TO '1.000003'; Query OK, 0 rows affected (0.10 sec) MariaDB [(none)]> SHOW BINARY LOGS; //SHOW BINARY LOGS 查看所有二进制日志文件 +----------+-----------+ | Log_name | File_size | +----------+-----------+ | 1.000003 | 280 | | 1.000004 | 245 | +----------+-----------+ 2 rows in set (0.00 sec)
mysqlbinlog基于即时点还原
基于时间参数 --start-datetime=datetime --stop-datetime=datetime 基于位置参数 --start-position=N --stop-position=N
指定恢复时间
这里引用博友的文章:https://blog.csdn.net/linuxheik/article/details/71602033
举例说明,假设在今天上午10:00(今天是20016年4月22日),执行SQL语句来删除一个大表。要想恢复表和数据,你可以恢复前晚上的备份,并输入:
mysqlbinlog --stop-date="2005-04-22 9:59:59" /var/log/mysql/mysql.123456 mysql -u root -pmypwd该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句,可能你想要恢复后面发生的活动。根据这些,你可以用起使日期和时间再次运行mysqlbinlog:
mysqlbinlog --start-date="2005-04-22 10:01:00" /var/log/mysql/mysql.123456 mysql -u root -pmypwd在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。
指定恢复位置
它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法,特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作方法为:
mysqlbinlog --start-date="2005-04-22 9:55:00" --stop-date="2005-04-22 10:05:00" /var/log/mysql/mysql.123456 > /tmp/mysql_restore.sql该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面内容:mysqlbinlog --stop-position="368312" /var/log/mysql/mysql.123456 mysql -u root -pmypwdmysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 mysql -u root -pmypwd 上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。
中继日志
中继日志是从服务器由主服务器二进制日志复制而来的事件,并保存为日志文件。
中继日志牵扯到mysql的主从复制,后面会详细了解。
事务日志
IOPS:是用来衡量硬盘IO能力的参数,即每秒执行IO操作的次数。
事务日志是为了事务引擎保证ACID的日志文件。存储引擎负责实现数据存储。
由于一个事务的执行可以撤销,特别针对写操作,如果直接将事务写入数据文件中,则写操作执行一半,然后撤销,这种行为会导致数据文件的崩溃。所以引入了事务日志:
所谓事务日志,以行为单位记录信息,每一行记录一个事务的id、对数据库、表、行的影响、原始数据,新数据等,当COMMIT以后,会将数据库的数据进行写入,这时在写入可以撤销,因为事务日志中也有原始数据。
事务日志和二进制日志一样,为了保证数据的可靠性,也得每隔一段事件,写入磁盘数据文件中。具体方法:(还是折中,写入磁盘数据文件频率高,势必带来大量的IO,写入磁盘数据问加年的频率低,数据的可靠性得不到保证)
设定一个时间间隔来写入磁盘数据文件。
每COMMIT一次,将事件日志写入一次磁盘数据文件。
提交事务同步数据的同时,设定一个时间间隔来写入磁盘数据文件。
Innodb的一条事务日志共经历4个阶段:
1) 创建阶段:事务创建一条日志;
2)日志刷盘:日志写入到磁盘上的日志文件; (ib_logfile*里面)
3) 数据刷盘:日志对应的脏页数据写入到磁盘上的数据文件;
4)写CKP:日志被当作Checkpoint写入日志文件;(ib_data*里面)
和事务相关的选项参数:
innodb_flush_log_at_trx_commit:在事务提交时,是否有flush行为(如果有,同步内存中的数据信息到磁盘数据文件),由mysql后台线程执行
innodb_log_buffer_size:内存缓存大小
innodb_log_file_size:日志文件大小
innodb_log_files_in_group:日志文件的日志文件个数定义
innodb_log_group_home_dir,事务日志存放的地方
| innodb_flush_log_at_trx_commit | 1 1表示当有事务提交,都会向磁盘中写入,并执行事务刷新; 2表示每当有事务同步,但不知行磁盘flush,由内核决定何时flush(IO写入),但是2不能保证数据安全; 0表示每一秒钟(跳过内核缓存机制)同步一次数据到磁盘数据文件
| innodb_log_buffer_size | 8388608
| innodb_log_file_size | 5242880
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | ./
为了缓解服务器压力,可以将事务日志存放在和数据文件不同的磁盘中。
mysql中的存储引擎
查看mysql中支持的存储引擎:
MariaDB [(none)]> SHOW ENGINES\G *************************** 1. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MyISAM 在mysql5.5.8之前默认使用的存储引擎,不支持 Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 9. row *************************** Engine: FEDERATED Support: YES Comment: FederatedX pluggable storage engine Transactions: YES XA: NO Savepoints: YES *************************** 10. row *************************** Engine: Aria Support: YES Comment: Crash-safe tables with MyISAM heritage Transactions: NO XA: NO Savepoints: NO 10 rows in set (0.00 sec)
Engine: MyISAM 在mysql5.5.8之前默认使用的存储引擎,其特点:
不支持事务,也不支持事务日志。由于不支持事务,MyISAM很多执行比较粗糙
锁力度太大,并发能力不强。更偏向读操作
不支持外键
支持索引类型多:BTree、FULL TEXT、空间索引、表压缩
Engine: InnoDB特点:
支持事务
锁力度很精细,到达行级锁。所以很好支持并行操作
会有很多事务开销
支持BTree索引、聚簇索引、自适应hash索引等
使用表空间,甚至支持使用row磁盘设备(不用创建文件系统,可以自我管理)
Engine: MRG_MYISAM:工作在MYISAM直上的存储引擎,将两个表联合起来,当成一个表来使用
Engine: CSV:利用文本文件格式,存储成文本文件。
存储成文本文件可以方便移植
Engine: ARCHIVE:实现归档,主要目的,将数据进行获取,不再改变,进行数据挖掘
Engine: MEMORY:内存存储引擎
InnoDB默认创建一个表空间,来存放所有InnoDB相关信息,可以通过修配置文件,启用为每个表创建一个表空间:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec)
这里的选项OFF只有在重启后才生效,所以只能写在配置文件中。