架构图

图片来源

数据文件(innodb)

每张表都有两种后缀名的文件，分别为frm后缀与ibd后缀。

.frm 后缀的文件中存储了表的表结构信息。

.ibd 后缀的文件中存放了表的数据信息与索引信息，又叫表空间文件。

ibdata 所有表公用的数据文件

如果在配置文件中没有开启innodb_file_per_table参数，在数据库对应的目录中则不会出现 .ibd 为后缀的文件，因为默认情况下会共用ibdata1。

如果在一开始没有开启innodb_file_per_table，并且已经存在某些使用innodb存储类型的表，那么这些表将共用ibdata1。

如果这时，又开启了innodb_file_per_table，那么原来的表的数据仍然存在于ibdata1中，新创建的表才会使用单独的以.ibd为后缀的表空间文件。

bin log

The binary log contains “events” that describe database changes such as table creation operations or changes to table data.

It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows),

unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

The binary log has two important purposes:

For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers.

The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data

changes that were made on the master. See Section 16.2, “Replication Implementation”.

Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log

that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

bin log：记录的是表的修改或表中数据的修改事件，包括一些潜在的修改，例如没有任何匹配的删除操作。有两个重要的目的：

1、replication，记录master数据变更的bin log可以提供给slave服务器。

2、recovery, 某些数据恢复操作需要使用bin log。备份恢复后，重新执行备份后记录的二进制日志中的事件。 这些事件从备份的角度来说是最新的数据库。

最优配置：

For durability and consistency in a replication setup that uses InnoDB with transactions:

If binary logging is enabled, set sync_binlog=1.

Always set innodb_flush_log_at_trx_commit=1.

sync_binlog=1

如果设置为0或N，最好为操作系统准备带有备用电源

参考：

https://hk.saowen.com/a/4626dd4d7e8d5224b6ac211068d3db0b212e1ebeb573fd04fcd47feed7bcb0d6

http://www.cnblogs.com/xinysu/p/6607658.html

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

http://www.cnblogs.com/xinysu/p/6607658.html

http://www.cnblogs.com/xinysu/p/6555082.html

http://www.zhdba.com/mysqlops/2012/04/06/innodb-log1/

http://9i9icenter.com/huanqiuNews/599f30ca2277046518e85878