MySQL之日志管理（二）

hgjgh

　　承接上文 http://11107124.blog.51cto.com/11097124/1897457
　　Sync（同步）：在内存中完成操作的同时，立马将数据写入磁盘中；但是磁盘的I/O性能和内存的I/O性能相差好几个数量级，所以sync同步写入会降低MySQL的服务性能。
　　ASync（异步写入）：写将数据写入内存中，再过一段时间（3，5秒）一并同步到磁盘上
　　所以引入异步的方式将内存中的二进制日志写入磁盘比较好；可以为异步写入提供一个内存缓存空间，先将日志缓存再缓存空间中，当缓存满后再一并写入磁盘
　　mysql> show global variables like '%log%';
　　binlog_cache_size                        32768
　　二级制日志缓存区大小
　　binlog_stmt_cache_size                    32768
　　binlog_format                          STATEMENT
　　二进制日志格式statement（基于语句），还可以定义为row（基于行）和mixed（混合方式）
　　log_bin                              ON
　　是否启用二级制日志存储文件，默认为数据目录下的mysql-bin.00001...可以自己指定存储位置
　　sql_log_bin                            ON
　　控制二进制日志信息是否记录进入职文件
　　slow_query_log                          OFF
　　是否启用记录慢查询日志                         |
　　slow_query_log_file                       /mydata/data/node1-slow.log
　　慢查询日志保存位置                          |
　　sql_log_off                             OFF
　　查询语言（sql）日志是否开启
　　sync_binlog=#
　　设定多久一次同步日志到磁盘，0表示不同步；任何正值都表示对二进制日志每隔多久往磁盘上同步一次；当autocommit值为1时，每条语句执行都会同步到磁盘上，否则每个事物提交的时候进行同步
　　中继日志：从主服务器上复制的二进制日志，并保存在从服务器上可以在从服务器上再执行一遍进行数据备份
　　并发访问时，多个MySQL线程在执行时可能引起数据库变化的操作时，都要同时写入同一个磁盘的二进制文件中，这样就导致写入日志的速度太慢，大大降低服务器并发的性能；为解决这个问题，可以用多块SSD做成一个raid0，大大加速mysql数据库的日志写入速度，从而提高数据库服务器性能
　　现在的许多数据库服务器都使用FIO（FUSION-IO）机制，来讲SSD盘通过PCI-IN接口直接连接到北桥上，时磁盘与内存的交互能力只差一个数量级
　　IOPS：服务器每秒钟可以执行的IO操作数（IO-Peer-Seconds）
　　一般台式机的IOPS为100左右，SICI硬盘位200左右，而SSD盘可以达到500、1000、2000等
　　所以可以将二进制日志文件保存在SSD上
　　事务日志：用来保证数据的可靠性（ACID）
　　二进制日志：当数据崩溃后，用来进行数据的即时点还原
　　注：事务日志不能进行还原，只能撤销；数据的还原必须依靠二进制日志
　　事务只要一提交就不能撤销，而在事务提交之后数据才写入磁盘，在事务提交之前都可以撤销或者重做；如果事务提交后数据还未写入磁盘数据就崩溃了，则会在下次启动时，根据二进制日志记录进行数据修复，叫已经提交的事务数据写入到磁盘上，而将未提交的事务进行撤销（这就是事务重做和事务撤销）
　　所以为了保证数据的安全，可以设定提交事务后每隔一定时间，将数据一并同步到磁盘（ASync），这就避免了一个事务因为持续时间太长而未来得及将数据写入磁盘，而server就崩溃
　　Innodb事务有关的参数：
　　innodb_flush_log_at_trx_commit          | 1
　　0：表示每一秒钟同步一次，并执行磁盘的flush操作
　　1：表示有事务提交或者每隔一秒就同步一次，并且执行磁盘flush操作
　　2：表示事物提交才同步，但不执行磁盘的flush操作，而是由操作系统决定何时对磁盘进行flush操作
　　[root@node1 ~]# cat /mydata/data/master-bin.index
　　二进制日志的索引文件，记录了有哪些二进制日志
　　./master-bin.000001
　　./master-bin.000002
　　./master-bin.000003
　　./master-bin.000004
　　......
　　log_buffer_size 内存的缓存大小默认为8M
　　log_file_size  日志文件大小，默认为5M
　　注：MySQL为用户空间的进程，每次进行IO操作都要先交给内核，再由内核发起系统调用，让内核与磁盘进行交互
　　存储引擎：
　　[root@node1 ~]# mysql -e 'show engines'
　　+--------------------+---------+----------------------------------------------------------------+
　　| Engine             | Support | Comment        | Transactions | XA   | Savepoints |
　　+--------------------+---------+----------------------------------------------------------------+

　　| MRG_MYISAM         | YES     | Collection of>　　| CSV                | YES     | CSV storage engine
　　| MyISAM             | YES     | MyISAM storage engine
　　| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) |
　　| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables
　　| PERFORMANCE_SCHEMA | YES     | Performance Schema
　　| ARCHIVE            | YES     | Archive storage engine
　　| FEDERATED          | NO      | Federated MySQL storage engine
　　| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys
　　+--------------------+---------+-----------------------------------------------------------
　　MyISam:不支持事务，只支持表锁，但由于所粒度太大，导致并发能力不强；但MyISam对读多写少的场景比较适应，支持B数索引FullText引擎，空间索引，表压缩（将表压缩后存储，节省空间、但加大开销）
　　Innodb:支持事务，支持行级锁（并行写入能力强），支持B数索引、聚簇索引、自适应HASH索引
　　MRG_MyISam:工作在MyISam上的存储引擎，能够将两张表结构一样的表合并为一张表进行查看
　　Memory:内存存储引擎，将表数据存在内存中，性能好，但数据不安全，容易丢失
　　CSV:利用文本文件存储表（类似/etc/passwd）,可以方便的将数据从一个数据库导入到另一个数据库
　　注：MySQL的存储引擎为插件式的，所以你完全可以自己编译一个第三方的存储引擎进行使用；但不建议混合使用存储引擎（如既用MyISam又用Innodb）,这样会导致许多操作不能执行（如两个存储引擎不一样的表不能添加外键）
　　MyISam：.frm（表结构文件）.MYD（数据存储文件）.MYI（索引文件）
　　Innodb：.frm（表结构文件）.ibd（数据存储文件和索引文件）
　　innodb_file_per_table            | ON
　　打开Innodb的每表一个空间，可进行许多高级操作；负责所有表都使用同一个表空间