MySQL在Linux系统下配置文件及日志详解

128次阅读

共计 40609 个字符，预计需要花费 102 分钟才能阅读完成。

MySQL 在 Linux 系统下配置文件及日志详解提纲：

1. 概述
2. 详解配置文件
3. 详解日志

MySQL 配置文件在 Windows 下叫 my.ini，在 MySQL 的安装根目录下；在 Linux 下叫 my.cnf，该文件位于/etc/my.cnf。

basedir = path	使用给定目录作为根目录(安装目录)。
character-sets-dir = path	给出存放着字符集的目录。
datadir = path	从给定目录读取数据库文件。
pid-file = filename	为 mysqld 程序指定一个存放进程 ID 的文件(仅适用于 UNIX/Linux 系统); Init- V 脚本需要使用这个文件里的进程 ID 结束 mysqld 进程。
socket = filename	为 MySQL 客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于 UNIX/Linux 系统; 默认设置一般是 /var/lib/mysql/mysql.sock 文件)。在 Windows 环境下，如果 MySQL 客户与服务器是通过命名管道进行通信的，–sock 选项给出的将是该命名管道的名字(默认设置是 MySQL)。
lower_case_table_name = 1/0	新目录和数据表的名字是否只允许使用小写字母; 这个选项在 Windows 环境下的默认设置是 1(只允许使用小写字母)。

mysqld 程序：语言设置

character-sets-server = name	新数据库或数据表的默认字符集。为了与 MySQL 的早期版本保持兼容，这个字符集也可以用–default-character-set 选项给出; 但这个选项已经显得有点过时了。
collation-server = name	新数据库或数据表的默认排序方式。
lanuage = name	用指定的语言显示出错信息。

mysqld 程序：通信、网络、信息安全

enable-named-pipes	允许 Windows 2000/XP 环境下的客户和服务器使用命名管道 (named pipe) 进行通信。这个命名管道的默认名字是 MySQL，但可以用–socket 选项来改变。
local-infile [=0]	允许 / 禁止使用 LOAD DATA LOCAL 语句来处理本地文件。
myisam-recover [=opt1, opt2, …]	在启动时自动修复所有受损的 MyISAM 数据表。这个选项的可取值有 4 种:DEFAULT、BACKUP、QUICK 和 FORCE; 它们与 myisamchk 程序的同名选项作用相同。
old-passwords	使用 MySQL 3.23 和 4.0 版本中的老算法来加密 mysql 数据库里的密码(默认使用 MySQL 4.1 版本开始引入的新加密算法)。
port = n	为 MySQL 程序指定一个 TCP/IP 通信端口(通常是 3306 端口)。
safe-user-create	只有在 mysql.user 数据库表上拥有 INSERT 权限的用户才能使用 GRANT 命令; 这是一种双保险机制(此用户还必须具备 GRANT 权限才能执行 GRANT 命令)。
shared-memory	允许使用内存 (shared memory) 进行通信(仅适用于 Windows)。
shared-memory-base-name = name	给共享内存块起一个名字(默认的名字是 MySQL)。
skip-grant-tables	不使用 mysql 数据库里的信息来进行访问控制(警告: 这将允许用户任何用户去修改任何数据库)。
skip-host-cache	不使用高速缓存区来存放主机名和 IP 地址的对应关系。
skip-name-resovle	不把 IP 地址解析为主机名; 与访问控制 (mysql.user 数据表) 有关的检查全部通过 IP 地址行进。
skip-networking	只允许通过一个套接字文件 (Unix/Linux 系统) 或通过命名管道 (Windows 系统) 进行本地连接，不允许 ICP/IP 连接; 这提高了安全性，但阻断了来自网络的外部连接和所有的 Java 客户程序(Java 客户即使在本地连接里也使用 TCP/IP)。
user = name	mysqld 程序在启动后将在给定 UNIX/Linux 账户下执行; mysqld 必须从 root 账户启动才能在启动后切换到另一个账户下执行; mysqld_safe 脚本将默认使用–user=mysql 选项来启动 mysqld 程序。

mysqld 程序：内存管理、优化、查询缓存区

bulk_insert_buffer_size = n	为一次插入多条新记录的 INSERT 命令分配的缓存区长度(默认设置是 8M)。
key_buffer_size = n	用来存放索引区块的 RMA 值(默认设置是 8M)。
join_buffer_size = n	在参加 JOIN 操作的数据列没有索引时为 JOIN 操作分配的缓存区长度(默认设置是 128K)。
max_heap_table_size = n	HEAP 数据表的最大长度(默认设置是 16M); 超过这个长度的 HEAP 数据表将被存入一个临时文件而不是驻留在内存里。
max_connections = n	MySQL 服务器同时处理的数据库连接的最大数量(默认设置是 100)。
query_cache_limit = n	允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是 1M)。
query_cache_size = n	查询缓存区的最大长度(默认设置是 0，不开辟查询缓存区)。
query_cache_type = 0/1/2	查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，”按需分配”模式，只响应 SELECT SQL_CACHE 命令。
read_buffer_size = n	为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是 128KB); 这个选项的设置值在必要时可以用 SQL 命令 SET SESSION read_buffer_size = n 命令加以改变。
read_rnd_buffer_size = n	类似于 read_buffer_size 选项，但针对的是按某种特定顺序 (比如使用了 ORDER BY 子句的查询) 输出的查询结果(默认设置是 256K)。
sore_buffer = n	为排序操作分配的缓存区的长度(默认设置是 2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。
table_cache = n	同时打开的数据表的数量(默认设置是 64)。
tmp_table_size = n	临时 HEAP 数据表的最大长度(默认设置是 32M); 超过这个长度的临时数据表将被转换为 MyISAM 数据表并存入一个临时文件。

mysqld 程序：日志

log [= file]	把所有的连接以及所有的 SQL 命令记入日志(通用查询日志); 如果没有给出 file 参数，MySQL 将在数据库目录里创建一个 hostname.log 文件作为这种日志文件(hostname 是服务器的主机名)。
log-slow-queries [= file]	把执行用时超过 long_query_time 变量值的查询命令记入日志(慢查询日志); 如果没有给出 file 参数，MySQL 将在数据库目录里创建一个 hostname-slow.log 文件作为这种日志文件(hostname 是服务器主机名)。
long_query_time = n	慢查询的执行用时上限(默认设置是 10s)。
long_queries_not_using_indexs	把慢查询以及执行时没有使用索引的查询命令全都记入日志(其余同–log-slow-queries 选项)。
log-bin [= filename]	把对数据进行修改的所有 SQL 命令 (也就是 INSERT、UPDATE 和 DELETE 命令) 以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是 filename.n 或默认的 hostname.n，其中 n 是一个 6 位数字的整数(日志文件按顺序编号)。
log-bin-index = filename	二进制日志功能的索引文件名。在默认情况下，这个索引文件与二进制日志文件的名字相同，但后缀名是.index 而不是.nnnnnn。
max_binlog_size = n	二进制日志文件的最大长度(默认设置是 1GB)。在前一个二进制日志文件里的信息量超过这个最大长度之前，MySQL 服务器会自动提供一个新的二进制日志文件接续上。
binlog-do-db = dbname	只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname	不把给定数据库里的变化情况记入二进制日志文件。
sync_binlog = n	每经过 n 次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n= 1 是最安全的做法，但效率最低。默认设置是 n =0，意思是由操作系统来负责二进制日志文件的同步工作。
log-update [= file]	记载出错情况的日志文件名(出错日志)。这种日志功能无法禁用。如果没有给出 file 参数，MySQL 会使用 hostname.err 作为种日志文件的名字。

mysqld 程序：镜像(主控镜像服务器)

server-id = n	给服务器分配一个独一无二的 ID 编号; n 的取值范围是 1~2 的 32 次方启用二进制日志功能。
log-bin = name	启用二进制日志功能。这种日志的文件名是 filename.n 或默认的 hostname.n，其中的 n 是一个 6 位数字的整数(日志文件顺序编号)。
binlog-do/ignore-db = dbname	只把给定数据库里的变化情况记入二进制日志文件 / 不把给定的数据库里的变化记入二进制日志文件。

mysqld 程序：镜像(从属镜像服务器)

server-id = n	给服务器分配一个唯一的 ID 编号
log-slave-updates	启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)。
master-host = hostname	主控服务器的主机名或 IP 地址。如果从属服务器上存在 mater.info 文件(镜像关系定义文件)，它将忽略此选项。
master-user = replicusername	从属服务器用来连接主控服务器的用户名。如果从属服务器上存在 mater.info 文件，它将忽略此选项。
master-password = passwd	从属服务器用来连接主控服务器的密码。如果从属服务器上存在 mater.info 文件，它将忽略此选项。
master-port = n	从属服务器用来连接主控服务器的 TCP/IP 端口(默认设置是 3306 端口)。
master-connect-retry = n	如果与主控服务器的连接没有成功，则等待 n 秒 (s) 后再进行管理方式(默认设置是 60s)。如果从属服务器存在 mater.info 文件，它将忽略此选项。
master-ssl-xxx = xxx	对主、从服务器之间的 SSL 通信进行配置。
read-only = 0/1	0: 允许从属服务器独立地执行 SQL 命令(默认设置); 1: 从属服务器只能执行来自主控服务器的 SQL 命令。
read-log-purge = 0/1	1: 把处理完的 SQL 命令立刻从中继日志文件里删除(默认设置); 0: 不把处理完的 SQL 命令立刻从中继日志文件里删除。
replicate-do-table = dbname.tablename	与–replicate-do-table 选项的含义和用法相同，但数据库和数据库表名字里允许出现通配符”%”(例如: test%.%–对名字以”test”开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = name	只对这个数据库进行镜像处理。
replicate-ignore-table = dbname.tablename	不对这个数据表进行镜像处理。
replicate-wild-ignore-table = dbn.tablen	不对这些数据表进行镜像处理。
replicate-ignore-db = dbname	不对这个数据库进行镜像处理。
replicate-rewrite-db = db1name > db2name	把主控数据库上的 db1name 数据库镜像处理为从属服务器上的 db2name 数据库。
report-host = hostname	从属服务器的主机名; 这项信息只与 SHOW SLAVE HOSTS 命令有关–主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1	主、从服务器使用压缩格式进行通信–如果它们都支持这么做的话。
slave-skip-errors = n1, n2, …或 all	即使发生出错代码为 n1、n2 等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。如果配置得当，从属服务器不应该在执行 SQL 命令时发生错误(在主控服务器上执行出错的 SQL 命令不会被发送到从属服务器上做镜像处理); 如果不使用 slave-skip-errors 选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。

mysqld–InnoDB：基本设置、表空间文件

skip-innodb	不加载 InnoDB 数据表驱动程序–如果用不着 InnoDB 数据表，可以用这个选项节省一些内存。
innodb-file-per-table	为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里(后者是默认设置)。该选项始见于 MySQL 4.1。
innodb-open-file = n	InnoDB 数据表驱动程序最多可以同时打开的文件数(默认设置是 300)。如果使用了 innodb-file-per-table 选项并且需要同时打开很多数据表的话，这个数字很可能需要加大。
innodb_data_home_dir = p	InnoDB 主目录，所有与 InnoDB 数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是 MySQL 的数据目录。
innodb_data_file_path = ts	用来容纳 InnoDB 为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节 (B)、兆字节(MB) 或千兆字节 (GB) 为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个 autoextend 属性和一个最大长度(max:n)。例如，ibdata1:1G; ibdata2:1G:autoextend:max:2G 的意思是: 表空间文件 ibdata1 的最大长度是 1GB，ibdata2 的最大长度也是 1G，但允许它扩充到 2GB。除文件名外，还可以用硬盘分区的设置名来定义表空间，此时必须给表空间的最大初始长度值加上 newraw 关键字做后缀，给表空间的最大扩充长度值加上 raw 关键字做后缀(例如 /dev/hdb1: 20Gnewraw 或 /dev/hdb1:20Graw); MySQL 4.0 及更高版本的默认设置是 ibdata1:10M:autoextend。
innodb_autoextend_increment = n	带有 autoextend 属性的表空间文件每次加大多少兆字节(默认设置是 8MB)。这个属性不涉及具体的数据表文件，那些文件的增大速度相对是比较小的。
innodb_lock_wait_timeout = n	如果某个事务在等待 n 秒 (s) 后还没有获得所需要的资源，就使用 ROLLBACK 命令放弃这个事务。这项设置对于发现和处理未能被 InnoDB 数据表驱动程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是 50s。
innodb_fast_shutdown 0/1	是否以最快的速度关闭 InnoDB，默认设置是 1，意思是不把缓存在 INSERT 缓存区的数据写入数据表，那些数据将在 MySQL 服务器下次启动时再写入 (这么做没有什么风险，因为 INSERT 缓存区是表空间的一个组成部分，数据不会丢失)。把这个选项设置为 0 反面危险，因为在计算机关闭时，InnoDB 驱动程序很可能没有足够的时间完成它的数据同步工作，操作系统也许会在它完成数据同步工作之前强行结束 InnoDB，而这会导致数据不完整。

mysqld 程序：InnoDB–日志

innodb_log_group_home_dir = p	用来存放 InnoDB 日志文件的目录路径(如 ib_logfile0、ib_logfile1 等)。在默认的情况下，InnoDB 驱动程序将使用 MySQL 数据目录作为自己保存日志文件的位置。
innodb_log_files_in_group = n	使用多少个日志文件 (默认设置是 2)。InnoDB 数据表驱动程序将以轮转方式依次填写这些文件; 当所有的日志文件都写满以后，之后的日志信息将写入第一个日志文件的最大长度(默认设置是 5MB)。这个长度必须以 MB(兆字节) 或 GB(千兆字节)为单位进行设置。
innodb_flush_log_at_trx_commit = 0/1/2	这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写 (术语称为”同步”) 到硬盘上。设置值 0 的意思是每隔一秒写一次日志并进行同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值 1(设置设置)的意思是在每执行完一条 COMMIT 命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值 2 是一般折衷的办法，即每执行完一条 COMMIT 命令写一次日志，每隔一秒进行一次同步。
innodb_flush_method = x	InnoDB 日志文件的同步办法 (仅适用于 UNIX/Linux 系统)。这个选项的可取值有两种: fdatasync，用 fsync() 函数进行同步; O_DSYNC，用 O_SYNC()函数进行同步。
innodb_log_archive = 1	启用 InnoDB 驱动程序的 archive(档案)日志功能，把日志信息写入 ib_arch_log_n 文件。启用这种日志功能在 InnoDB 与 MySQL 一起使用时没有多大意义(启用 MySQL 服务器的二进制日志功能就足够用了)。

mysqld 程序–InnoDB：缓存区的设置和优化

innodb_log_buffer_pool_size = n	为 InnoDB 数据表及其索引而保留的 RAM 内存量(默认设置是 8MB)。这个参数对速度有着相当大的影响，如果计算机上只运行有 MySQL/InnoDB 数据库服务器，就应该把全部内存的 80% 用于这个用途。
innodb_log_buffer_size = n	事务日志文件写操作缓存区的最大长度(默认设置是 1MB)。
innodb_additional_men_pool_size = n	为用于内部管理的各种数据结构分配的缓存区最大长度(默认设置是 1MB)。
innodb_file_io_threads = n	I/ O 操作 (硬盘写操作) 的最大线程个数(默认设置是 4)。
innodb_thread_concurrency = n	InnoDB 驱动程序能够同时使用的最大线程个数(默认设置是 8)。

mysqld 程序：其它选项

bind-address = ipaddr	MySQL 服务器的 IP 地址。如果 MySQL 服务器所在的计算机有多个 IP 地址，这个选项将非常重要。
default-storage-engine = type	新数据表的默认数据表类型(默认设置是 MyISAM)。这项设置还可以通过–default-table-type 选项来设置。
default-timezone = name	为 MySQL 服务器设置一个地理时区(如果它与本地计算机的地理时区不一样)。
ft_min_word_len = n	全文索引的最小单词长度工。这个选项的默认设置是 4，意思是在创建全文索引时不考虑那些由 3 个或更少的字符构建单词。
Max-allowed-packet = n	客户与服务器之间交换的数据包的最大长度，这个数字至少应该大于客户程序将要处理的最大 BLOB 块的长度。这个选项的默认设置是 1MB。
Sql-mode = model1, mode2, …	MySQL 将运行在哪一种 SQL 模式下。这个选项的作用是让 MySQL 与其他的数据库系统保持最大程度的兼容。这个选项的可取值包括 ansi、db2、Oracle、no_zero_date、pipes_as_concat。

注意：如果在配置文件里给出的某个选项是 mysqld 无法识别的，MySQL 服务器将不启动。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-01/139346p2.htm

3. 详解日志

MySQL 日志：主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志；日志是 mysql 数据库的重要组成部分。日志文件中记录着 mysql 数据库运行期间发生的变化；也就是说用来记录 mysql 数据库的客户端连接状况、SQL 语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。错误日志
在 mysql 数据库中，错误日志功能是默认开启的。并且，错误日志无法被禁止。默认情况下，错误日志存储在 mysql 数据库的数据文件中。错误日志文件通常的名称为 hostname.err。其中，hostname 表示服务器主机名。错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过 log-error 和 log-warnings 来定义的，其中 log-err 是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings 是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如 mysql 如何启动 InnoDB 的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。下面我们来定义 mysql 错误日志的功能：一般而言，日志级别的定义没有回话变量都只是在全局级别下进行定义。

[root@stu18 data]# vim /etc/my.cnf
[mysqld]
Log_error=DIR/[filename]
解析：其中，DIR 参数指定错误日志的路径 filename 参数是错误日志的名称，没有指定该参数时默认为主机名。重启 mysql 服务器即可生效。查看 mysql 错误日志：

[root@stu18 data]# tail -20 stu18.magedu.com.err
130813 15:30:50 InnoDB: Starting shutdown…
130813 15:30:51 InnoDB: Shutdown completed; log sequence number 1630920
130813 15:30:51 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
130813 15:30:52 mysqld_safe mysqld from pid file /mydata/data/stu18.magedu.com.pid ended
130813 15:30:53 mysqld_safe Starting mysqld daemon with databases from /mydata/data
130813 15:30:54 InnoDB: The InnoDB memory heap is disabled #禁用了 InnoDB memory 的堆功能。
130813 15:30:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins #Mutexes（互斥量）和 rw_locks（行级锁）是 GCC 编译的是 InnoDB 内置的。
130813 15:30:54 InnoDB: Compressed tables use zlib 1.2.3 #默认压缩工具是 zlib
130813 15:30:55 InnoDB: Initializing buffer pool, size = 128.0M #InnoDB 引擎的缓冲池（buffer pool）的值大小
130813 15:30:55 InnoDB: Completed initialization of buffer pool
130813 15:30:55 InnoDB: highest supported file format is Barracuda.
130813 15:30:57 InnoDB: Waiting for the background threads to start
130813 15:30:58 InnoDB: 5.5.33 started; log sequence number 1630920
130813 15:30:58 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
130813 15:30:58 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’; #0.0.0.0 会反解主机名，这里反解失败
130813 15:30:58 [Note] Server socket created on IP: ‘0.0.0.0’.
130813 15:30:58 [Note] Event Scheduler: Loaded 0 events #事件调度器没有任何事件，因为没有装载。
130813 15:30:58 [Note] /usr/local/mysql/bin/mysqld: ready for connections. #mysql 启动完成等待客户端的请求。
Version: ‘5.5.33-log’ socket: ‘/tmp/mysql.sock’ port: 3306 Source distribution #创建一个本地 sock 用于本地连接。

启动和设��慢查询日志：1、通过配置文件 my.cnf 中的 log-slow-queries 选项可以开启慢查询日志；形式如下：

[root@stu18 data]# vim /etc/my.cnf
[mysqld]
slow_query_log=1
log-slow-queries [= DIR/[filename] ]

其中，DIR 参数指定慢查询日志的存储路径；filename 参数指定日志的文件名，生成日志文件的完成名称为 filename-slow.log。如果不指定存储路径，慢查询日志默认存储到 mysql 数据库的数据文件下，如果不指定文件名，默认文件名为 hostname-slow.log。2、通过登录 mysql 服务器直接定义，方式如下：首先要有全局权限；然后执行 mysql>set global slow_query_log=1; 时间默认超过多少的称为慢查询日志？一般都是通过 long_query_time 选项来设置这个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为 10 秒），这个查询语句将被记录到慢查询日志中。查看服务器默认时间值方式如下：

注释：其中这个慢查询时间并不是只表示语句自身执行超过 10 秒还包含由于其他资源被征用造成阻塞的查询执行时间或其他原因等都被记录到慢查询中。所以这个慢查的时长表示从查询开始到查询结束中间包含可能的任何原因所经历的所有时间。测试是否可以记录日志：

查看慢查询日志：

mysql> use mysql
mysql> selectuser,host,password from user where user=”root”;
+——+——————+———-+
| user | host | password |
+——+——————+———-+
| root |localhost | |
| root |stu18.magedu.com | |
| root |127.0.0.1 | |
| root | ::1 | |
+——+——————+———-+
4 rows in set (0.08sec) #查询时间为 0.08
mysql> systemmore /mydata/data/stu18_slow.log #查询慢查询日志记录信息
/usr/local/mysql/bin/mysqld,Version: 5.5.33-log (Source distribution). started
with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
>>>>>>>>>>>>>>>> 部分已省略 >>>>>>>>>>>>>>
# Time: 13100723:46:33
# User@Host:root[root] @ localhost []
# Query_time:0.108459 Lock_time: 0.000216 Rows_sent:4 Rows_examined: 6
SETtimestamp=1381160793;
selectuser,host,password from user where user=”root”;

事务日志：事务日志（InnoDB 特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I /O，而不像随机 I / O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。查看事务日志的定义：

二进制日志：二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的 mysql 语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询 mysql 数据库中进行了哪些变化。一般大小体积上限为 1G。二进制开启状态：

mysql> showglobal variables like “%log_bin%”;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| log_bin | ON | #已开启
|log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+———————————+——-+

二进制日志相关的参数：

mysql> showglobal variables like “%log%”;
sql_log_bin ={ON|OFF} #用于控制会话级别二进制日志功能的开启或关闭。默认为 ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有 SUPER 权限。
binlog_cache_size =32768 #默认值 32768 Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高 binlog 的记录效率而设计的一个用于短时间内临时缓存 binlog 数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB 是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高 binlog_cache_size。同时，我们可以通过 binlog_cache_use 以及 binlog_cache_disk_use 来分析设置的 binlog_cache_size 是否足够，是否有大量的 binlog_cache 由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。
binlog_stmt_cache_size= 32768 #当非事务语句使用二进制日志缓存，但是超出 binlog_stmt_cache_size 时，使用一个临时文件来存放这些语句。
log_bin = mysql-bin# 指定 binlog 的位置，默认在数据目录下。
binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型，默认为 MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则 MySQL 启动时会产生警告日志信息并记录于错误日志中。
sync_binlog = 10# 设定多久同步一次二进制日志至磁盘文件中，0 表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当 autocommit 的值为 1 时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步
max_binlog_cache_size= {4096 .. 18446744073709547520} #二进定日志缓存空间大小，5.5.9 及以后的版本仅应用于事务缓存，其上限由 max_binlog_stmt_cache_size 决定。
max_binlog_stmt_cache_size= {4096 .. 18446744073709547520} #二进定日志缓存空间大小，5.5.9 及以后的版本仅应用于事务缓存
expire_log_days ={0..99} #设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为 0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在 MySQL 启动时或 FLUSH 日志时。

二进制日志定义方式：其一、log_bin 可以直接定义为文件路径，也可以为 ON|OFF。其二、通过编辑 my.cnf 中的 log-bin 选项可以开启二进制日志；形式如下：

[root@stu18 ~]#my.cnf
[mysqld]
log-bin [=DIR \ [filename]]

其中，DIR 参数指定二进制文件的存储路径；filename 参数指定二级制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等。每次重启 mysql 服务或运行 mysql> flush logs; 都会生成一个新的二进制日志文件，这些日志文件的 number 会不断地递增。除了生成上述的文件外还会生成一个名为 filename.index 的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

[root@stu18 ~]# cd /mydata/data/
[root@stu18 data]#ls -lh
-rw-rw—- 1 mysqlmysql 14K Aug 13 15:30 mysql-bin.000001
-rw-rw—- 1 mysqlmysql 150 Aug 13 17:05 mysql-bin.000002
-rw-rw—- 1 mysqlmysql 150 Aug 13 17:06 mysql-bin.000003
-rw-rw—- 1 mysqlmysql 150 Aug 13 17:07 mysql-bin.000004
-rw-rw—- 1 mysqlmysql 150 Aug 13 17:39 mysql-bin.000005
-rw-rw—- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000006
-rw-rw—- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000007
-rw-rw—- 1 mysqlmysql 126 Aug 13 19:05 mysql-bin.000008
-rw-rw—- 1 mysqlmysql 107 Aug 13 19:05 mysql-bin.000009
-rw-rw—- 1 mysqlmysql 353 Oct 7 23:40 mysql-bin.000010
-rw-rw—- 1 mysqlmysql 190 Oct 7 20:43 mysql-bin.index
[root@stu18 data]#cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010

如果说我们向某个表的某个字段插入一个数据而这个数据为当前时间(日期时间型)；过段时间将此二进制文件应用到另一台服务器上数据就会变动从而导致数据的不一致性所以说对于这种非确定性的数据使用默认的语句定义并不是可靠的；二进制日志中常用的定义格式：1、语句(statement)：默认的记录格式；2、行(row)：定义的并非数据本身而是这一行的数据是什么；3、混合模式(mixed)：交替使用行和语句、由 mysql 服务器自行判断。其中基于行的定义格式数据量会大一些但是可以保证数据的精确性。查看二进制日志：二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用查看命令打开并查看二进制日志。

mysql> showbinary logs; #显示当前服务器使用的二进制文件及大小
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001| 13814 |
| mysql-bin.000002| 150 |
| mysql-bin.000003| 150 |
| mysql-bin.000004| 150 |
| mysql-bin.000005| 150 |
| mysql-bin.000006| 126 |
| mysql-bin.000007| 126 |
| mysql-bin.000008| 126 |
| mysql-bin.000009| 107 |
| mysql-bin.000010| 353 |
+——————+———–+
10 rows in set (0.07sec)
mysql> showmaster logs; #显示主服务器使用的二进制文件及大小
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001| 13814 |
| mysql-bin.000002| 150 |
| mysql-bin.000003| 150 |
| mysql-bin.000004| 150 |
| mysql-bin.000005| 150 |
| mysql-bin.000006| 126 |
| mysql-bin.000007| 126 |
| mysql-bin.000008| 126 |
| mysql-bin.000009| 107 |
| mysql-bin.000010| 353 |
+——————+———–+
10 rows in set (0.02sec)
mysql> showmaster status; #当前使用的二进制文件及所处位置
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010| 353 | | |
+——————+———-+————–+——————+
1 row in set (0.00sec)

小扩展：二进制日志的记录位置：通常为上一个事件执行结束时间的位置，每一个日志文件本身也有自己的元数据所以说对于当前版本的 mysql 来说二进制的开始位置通常为 107；

查看当前二进制文件的信息：

mysql> createdatabase yong;
Query OK, 1 rowaffected (0.12 sec)
mysql> createtable yong.tb1 (id int,name char(20));
Query OK, 0 rowsaffected (0.44 sec)
mysql> insertinto yong.tb1 values(1,’tom’);
Query OK, 1 rowaffected (0.14 sec)
mysql> showmaster status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000011| 479 | | |
+——————+———-+————–+——————+
1 row in set (0.00sec)

查看二进制日志信息的命令：

SHOW BINLOG EVENTS[IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
mysql> showbinlog events\G #查看所有的二进制信息
***************************87. row ***************************
Log_name: mysql-bin.000001
Pos: 13580
Event_type: Query
Server_id: 1
End_log_pos: 13688
Info: use `hellodb`; /*!40000 ALTERTABLE `toc` DISABLE KEYS */
***************************88. row ***************************
Log_name: mysql-bin.000001
Pos: 13688
Event_type: Query
Server_id: 1
End_log_pos: 13795
Info: use `hellodb`; /*!40000 ALTERTABLE `toc` ENABLE KEYS */
***************************89. row ***************************
Log_name: mysql-bin.000001
Pos: 13795
Event_type: Stop
Server_id: 1
End_log_pos: 13814
Info:
89 rows in set (0.00sec)
mysql> showbinlog events in ‘mysql-bin.000011’; #查看指定日志的二进制信息
+——————+—–+————-+———–+————-+———————————————-+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+—–+————-+———–+————-+———————————————-+
| mysql-bin.000011| 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlogver: 4 |
| mysql-bin.000011 |107 | Query | 1 | 190 | create database yong |
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,’tom’) |
| mysql-bin.000011 |452 | Xid | 1 | 479 | COMMIT /* xid=103 */ |
+——————+—–+————-+———–+————-+———————————————-+
6 rows in set (0.00sec)
mysql> showbinlog events in ‘mysql-bin.000011’ from 190; #从指定的事件位置开始
+——————+—–+————+———–+————-+———————————————-+
| Log_name | Pos | Event_type | Server_id |End_log_pos | Info |
+——————+—–+————+———–+————-+———————————————-+
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,’tom’) |
| mysql-bin.000011 |452 | Xid | 1 | 479 | COMMIT /* xid=103 */ |
+——————+—–+————+———–+————-+———————————————-+
4 rows in set (0.00sec)
mysql> showbinlog events in ‘mysql-bin.000011’ from 190 limit 3; #指定偏移量(不是语句，是事件)
+——————+—–+————+———–+————-+———————————————-+
| Log_name | Pos | Event_type | Server_id |End_log_pos | Info |
+——————+—–+————+———–+————-+———————————————-+
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,’tom’) |
+——————+—–+————+———–+————-+———————————————-+
3 rows in set (0.00sec)

命令行下查看二进制日志：由于无法使用 cat 等方式直接打开并查看二进制日志；所以必须使用 mysqlbinlog 命令。但是当正在执行 mysql 读写操作时建议不要使用此打开正在使用的二进制日志文件；若非要打开可 flush logs。mysqlbinlog 命令的使用方式：

[root@stu18 data]#mysqlbinlog mysql-bin.000017 #必须在数据目录下
/*!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 #事件开始处
#131009 0:25:59 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.33-log created 131009 0:25:59
# Warning: thisbinlog is either in use or was not closed properly.
BINLOG ‘
FzJUUg8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
‘/*!*/;
# at 107
#131009 0:26:36 server id 1 end_log_pos 192 Query thread_id=12 exec_time=0 error_code=0 #131009 0:26:36 年月日的简写方式；end_log_pos 事件结束处；thread_id=12 哪个会话线程创建的此语句；exec_time=0 执行时长单位为秒；error_code=0 错误代码 0 表示没有
SET TIMESTAMP=1381249596/*!*/; #预设信息(环境设定)

导出此数据库的信息：
[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

导入此数据库的信息：
[root@stu18 data]#mysql < a.sql

删除二进制日志信息：二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删(可以做即时点还原)。也不可在关闭 mysql 服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下：1、删除所有的二进制日志（不可效仿）：使用 RESET MASTER 语句可以删除所有的二进制日志。该语句的形式如下：

解析：首先不建议在生产环境下使用此操作；删除所有的二进制日志后，Mysql 将会重新创建新的二进制日志。新二进制日志的编号从 000001 开始。2、根据文件或时间点来删除二进制日志：语法形式：

mysql> PURGE {BINARY | MASTER} LOGS {TO ‘log_name’ | BEFORE datetime_expr}

其中 TO’log_name’ 表示把这个文件之前的其他文件都删除掉，也可使用 BEFORE datetime_expr 指定把哪个时间之前的二进制文件删除了。

mysql> PURGEBINARY LOGS TO ‘mysql-bin.000007’;
Query OK, 0 rowsaffected (0.11 sec)
mysql> showbinary logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000007| 150 |
| mysql-bin.000008| 150 |
| mysql-bin.000009| 150 |
| mysql-bin.000010| 150 |
| mysql-bin.000011| 150 |
| mysql-bin.000012| 150 |
| mysql-bin.000013| 150 |
| mysql-bin.000014| 150 |
| mysql-bin.000015| 150 |
| mysql-bin.000016| 150 |
| mysql-bin.000017| 483 |
+——————+———–+
11 rows in set (0.04sec)
[root@stu18 data]#cat mysql-bin.index
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017

由此可以看出这种清理二进制日志文件的方式是非常合理的，不会导致数据库的错误发生。
mysql> PURGEBINARY LOGS BEFORE ’13-10-19 10:26:36′; #使用时间来删除二进制日志
Query OK, 0 rowsaffected (0.05 sec)

到此关于二进制的知识就解析完了，其中若有错误不足之处请指出！谢谢！注意二进制日志和错误日志很重要重点学之！！

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139346.htm