<服务>MySQL日志和引擎

时间：Jan. 7, 2017 分类：Linux服务

目录：

错误日志

[mysqld_safe]标签下的log-error指定

mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

查询查询

不同查询日志

mysql> show variables like 'general_log%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)

慢查询日志

mysql> show variables like '%query%';
+------------------------------+---------------------------------+
| Variable_name                | Value                           |
+------------------------------+---------------------------------+
| long_query_time              | 10.000000                       |
| slow_query_log               | OFF                             |
| slow_query_log_file          | /var/run/mysqld/mysqld-slow.log |
+------------------------------+---------------------------------+
3 rows in set (0.00 sec)

以及是否使用索引

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

二进制日志

mysql> show variables like 'log_bin';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
+---------------------------------+-------+
1 rows in set (0.00 sec)

二进制文件的三种工作模式

Statement Level模式

每一条修改数据的SQL语句都会记录到master的binlog中，slave在复制的时候QL线程会解析成和原来master端执行过的相同的SQL来再次执行。

  • 优点： Statement Level下的优点是解决了row level下的缺点，不需要记录每一行的变化，减少了binlog日志量，节约IO，提高性能
  • 缺点： 由于记录的执行语句，所以为了让slave端也能正确执行，binlog还必须记录每条语句在执行时候的一些相关上下文信息，以保障所有的语句在slave端执行的时候能得到与在master端执行相同的结果。

Row Level模式

日志中会记录成每一行数据被修改的形式，然后slave端再对相同的数据进行修改。举个栗子，主库一次性插入五行，到从也是五行一行一行的改，而不是一次性。

  • 优点： binlog不会记录执行的SQL语句及其上下文信息，仅仅只需要记录那一条数据被修改了，修改成什么样子，所以会非常清楚的记录下每一行的修改信息，非常容易理解，而且不会出现在某些特定的存储过程中，例如function或trigger的调用和触发无法被正确的复制，sleep()函数在有些版本就不能进行正确的主从复制，存储过程中使用last_insert_id()函数也有可能获得主从不一致的id
  • 缺点： 这样记录在所有执行语句，都将以每行的修改来记录，这样就会产生大量的日志内容，例如update table set own_id = 'w' where own_id = 'c',这样存在多少条where own_id = 'c'，就会记录多少条binlog，或者alter table 相当于重建表，相当于该表所有行的日志

Mixed模式

实际上就是前两种模式的结合，在mixed模式下，MySQL会根据执行的每一条具体的SQL语句来区分和对待记录日志的形式，也就是说在Statement和Row中选一种，在新版本中Statement Level模式还是和以前一样，但是Row Level遇到表结构变化的时候会采用statement level来记录，不过update和delete还是用row level

调整模式的方法


mysql> show variables like '%binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

在my.cnf中mysqld标签下进行配置

binlog_format="STATEMENT"
binlog_format="ROW"
binlog_format="Mixed"

在线生效

SET SESSION binlog_format="STATEMENT";

全局生效

SET GLOBAL binlog_format="STATEMENT";

切分binlog日志

mysqladmin -uroot -p123456 flush-log

解析日志

mysqlbinlog --base64-out=decode-rows -v mysql-bin.000016

MySQL存储引擎

最常用的Myisam和Innodb引擎。其他还有HEAP和NDB等等 数据库表里的数据存储在数据库里以及磁盘上，对于用户或者应用程序来说是一张表，无论用什么引擎来存储，用户看到的数据都是一样的，不用的引擎存取，引擎的功能，占用空间的大小，读取性能都可能会有区别。

MySQL架构

  • 支持的连接：C API,JDBC,ODBC,.NET,PHP,Python,perl,Ruby,Cobol
  • 管理:备份,恢复,安全,同步,集群,管理员行为,配置,迁移,元数据信息
  • 连接池 权限，线程同用，连接限制，检查内存，缓存
  • 接口DML，DDL，存储过程，视图，触发器
  • 查询事务，对象权限
  • 优化访问，路径
  • 状态，缓存
  • 可插线的存储引擎

MySQL事务

逻辑上的一组SQL操作，组成这组操作的各个SQL语句，要么全成功，要么全失败 需要第一个SQL完成，再执行第二个SQL，要么都执行，要么都不执行，这就是事务的原子性。 支持事务的引擎有innodb/ndb(集群)

事务的四大特性

  1. 原子性(Atomicity) 事务是一个不可分割的单位,事务中的所有SQL等操作要么都发生，要么都不发生。
  2. 一致性(Consistency) 事务发生的前和后，数据的完整性必须保持一致
  3. 隔离性(Isolation) 当并发访问数据库的时候，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务执行的数据是相互隔离的
  4. 持久性(Durability) 一个事务一旦被提交， 它对数据库中的数据改变就是永久性的，如果错误，事务也不能撤销，只能通过补偿性事务。

事务的开启

数据库默认事务是自动提交的，也就是发一条sql他它就执行一条，如果想多条sql放到一个事务中执行，则需要使用事务进行处理。当我们开启一个事务的时候，并且没有提交，mysql会自动回滚事务，或者我们使用rollback命令手动执行回滚。

  • start stransaction 数据库事务开启
  • rollback 回滚事务
  • commit 提交事务

事务的配置

set GLOBAL autocommit = 0 禁止自动提交,也有的本班需要用ON
set GLOBAL autocommit = 1 开启自动提交,也有的本班需要用OFF

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MyISAM引擎

MyISAM引擎是MySQL关系型数据库管理系统的默认存储引擎(MySQL5.5.5以前)，而在5.5.5版本以后，InnoDB引擎由于事务参照的完整性以及更高的并发性等优点开始逐步取代MyISAM引擎。也并非InnoDB是新兴的，而是InnoDB需要更高的硬件配置才能体现出性能，单现在硬件配置早已不成为问题。

mysql> show create table blog_family\G;
*************************** 1. row ***************************
       Table: blog_family
Create Table: CREATE TABLE `blog_family` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `family_name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

MyISAM引擎的表在存储的时候都会有三个文件，这三个文件有着相同的文件名，不同的是后缀名MYD数据文件，MYI索引文件和frm保存表的定义

[root@why ~]#  cd /var/lib/mysql/why
[root@why why]# ll
-rw-rw---- 1 mysql mysql   8664 Dec 29 21:01 django_session.frm
-rw-rw---- 1 mysql mysql   2396 Jan  6 11:14 django_session.MYD
-rw-rw---- 1 mysql mysql   3072 Jan  6 11:14 django_session.MYI
[root@why why]# file django_session.MYD 
django_session.MYD: DBase 3 data file (1815420928 records)
[root@why why]# file django_session.MYI
django_session.MYI: MySQL MISAM compressed data file Version 1
[root@why why]# file django_session.frm 
django_session.frm: MySQL table definition file Version 10

MyISAM引擎的特点

  1. 不支持事务
  2. 表级锁定：数据更新时会锁定整个表，锁定的实现成本很小但是也大大降低了并发
  3. 读写互相阻塞:不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，单读本身并不会阻塞另外的读
  4. 只能缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少IO，但是这个缓存区只会缓存索引，而不会缓存数据
  5. 读取速度较快，占用资源较小
  6. 不支持外键约束，单支持全文索引

MyISAM引擎应用场景

  1. 不需要事务的场景
  2. 读取数据比较多的场景，写入多也可以，读写都频繁的不适合
  3. 读取并发相对较低的业务，高并发的写入的也可以
  4. 数据修改相对较少业务
  5. 以读为主的业务
  6. 对数据一致性要求不高的场景

MyISAM引擎调优

  1. 设置合适的索引，例如重复值较少的列，常用的列
  2. 调整读写的优先级，根据实际需求确保重要操作更优先执行，一般情况下写重要
  3. 启用延迟插入改善大批量写入性能(降低写入频率，尽可能一次插入多条)
  4. 尽量顺序插入insert数据都写入尾部，减少阻塞
  5. 分解大的时间长的操作，降低单个操作的阻塞
  6. 降低并发数，某些高并发场景通过应用进行排队队列机制
  7. 对于相对静态(更改不频繁)的数据，利用Query Cache或Memcached,redis缓存服务提高访问速度
  8. Count全表扫描的时候特别高效,带有条件的count效率较慢
  9. 如果做主从同步的话，从库比较适合用MyISAM引擎

query缓存配置

query_cache_size = 256M
query_cache_limit = 1M
query_cache_min_res_unit = 2K
key_buffer_size = 2048M     #缓存索引的大小

InnoDB引擎

InnoDB优点是支持兼容ACID的事务(类似于PostgreSQL)，以及参数完整性(即对外键的支持)，共享表空间ibdata1,在5.5版本之后支持独立表空间

InnoDB引擎的特点

  1. 支持事务以及4个事务隔离级别，支持多版本读
  2. 行级锁定，更新时只锁定当前行(需要通过索引实现，如果是全表扫描仍然会是表锁)
  3. 读写阻塞与事务隔离级别相关
  4. 具有非常高效的缓存特性:能缓存索引，也能缓存数据
  5. 整个表和主键以Cluster方式存储，组成一颗平衡树
  6. 所有Secondary Index都会保存主键
  7. 支持分区，表空间
  8. 在5.5版本后支持外键约束
  9. 对已经资源要求比较高

InnoDB引擎的使用的场景

  1. 需要事务支持
  2. 行级锁定对高并发有很好的适应能力，但需要通过索引确保查询
  3. 数据更新较为频繁的场景
  4. 数据一致性要求较高的业务
  5. 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提供内存的利用率，尽可能减少磁盘IO

相关配置参数

innodb_additional_mem_pool_size = 16M               #元数据信息资源池，一般不需要修改，可以通过show innodb status来查看
innodb_buffer_pool_size = 2048M                     #缓存索引或者数据，减少磁盘IO，官方说一般配置为总内存的50%~80%，不过配置太大会宕机，推荐30%
innodb_data_file_path = ibdata1:1024M:autoextend    #数据文件格式
innodb_write_io_threads = 8                         #写IO线程(异步IO)
innodb_read_io_threads = 8                          #读IO线程
innodb_file_io_threads = 4
innodb_thread_concurrency = 16                      #并发线程数
innodb_flush_log_at_trx_commit = 2                  #0代表每秒把日志写入日志文件并刷新到磁盘，1代表每次commit把日志写入日志文件并刷新到磁盘，2代表每次commit把日志写入日志文件，每秒进行刷新到磁盘。0性能是最好的了，但是安全性是最差的，宕机会损失1s的数据
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120                      #锁表时间
innodb_file_per_table = 0                           #每个表几个文件
innodb_data_home_dir =                              #存放目录
innodb_log_group_home_dir =

InnoDB调优

  1. 主键尽可能少，避免给Secondary index带来过大的空间负担
  2. 避免全表扫描
  3. 尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO
  4. 大批量小插入的时候，控制事务而不使用autocommit自己提交
  5. 设置合理的innodb_flush_log_at_commit参数(0代表每秒刷新)
  6. 避免主键更新

各种引擎功能对比

主要比较的是：事务Transactions，锁表细粒度locking granularity，全文索引Full-text search index，数据缓存data cache ，索引缓存index cache和外键支持foreign key support

查看当前版本数据库支持的引擎

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> show ENGINES\G;
*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.02 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

mysql> show engines\G;
*************************** 1. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
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: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 8. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.00 sec)

ERROR: 
No query specified

修改引擎的方法

  • 最不推荐的是导出数据，然后在修改建表语句，重新导入
  • 直接修改表信息

ALTER TABLE tablename ENGINE = INNODB; SHOW CREATE TABLE $tablename;

  • mysql_convert_table_format功能
mysql_convert_table_format --host=hostname --user=root --password=123456 --socket=   --engine=MyISAM databasename tablename