MySQL数据库文件

MySQL数据库文件

本文档从MySQL数据库和存储引擎层面介绍各种类型的文件。

• 参数文件（my.cnf）

• 错误日志（error log）

• 二进制日志文件（binary log）

• 慢查询日志（slow log）

• 全量日志（general log）

• 审计日志（audit log）

• 中继日志（relay log）

• Pid文件

• Socket文件

• 表结构文件

• InnoDB存储引擎文件

1. 参数文件

在MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数。在默认情况下，MySQL实例会按照一定的顺序在指定的位置进行读取。

# mysql --help|grep my.cnf
...

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

 

如果想指定默认的参数文件，需要配合--defaults-file选项，如：

mysqld --defaults-file=/etc/my3306.cnf &

 

下面介绍一下常见参数文件/etc/my.cnf中的参数的含义。

• [client] 
  用来配置MySQL客户端的参数，MySQL客户端指所有连接mysql的程序

• port = 3306
  指MySQL使用的默认端口号

• socket = /tmp/mysql3306.sock
  指在/tmp目录创建socket文件mysql3306.sock

• [mysql]
  表示配置MySQL的mysql客户端程序

• prompt="\U [\d] \R:\m:\s> "
  格式化mysql提示符

• no-auto-rehash
  不会读取全部meta data。

• [mysqld]
  表示配置MySQL的mysqld客户端程序

• user = mysql 
  以mysql用户运行mysqld服务器。

• port = 3306
  指MySQL使用的默认端口号

• basedir = /usr/local/mysql 
  MySQL安装目录的路径。

• datadir = /data/mysql/mysql3306/data
  MySQL服务器数据目录的路径

• tmpdir = /data/mysql/mysql3306/tmp
  用于创建临时文件的目录的路径。

• socket = /tmp/mysql3306.sock
  指在/tmp目录创建socket文件mysql3306.sock

• pid-file = mysqldb1.pid
  MySQL进程标识文件名，如果没指定目录，则存放在数据目录下。

• character-set-server = utf8mb4 
  使用utf8mb4作为默认服务器字符集

• skip_name_resolve = 0
  表示所有的ip连接mysql，不会进行dns反解析。可以加速客户端连接的速度。

• open_files_limit = 65535
  mysqld可用的文件描述符数。

• back_log = 1024 
  表示在MySQL暂时停止应答新请求之前的短时间内可以堆叠多少个请求。
  默认值为'-1',根据公式50 + (max_connections / 5)进行计算,上限900。人为干预的最大值为65535

• max_connections = 512
  允许的最大同时客户端连接数

• max_connect_errors = 1000000
  默认值为100。表示如果来自主机的多个连续连接请求在没有成功连接的情况下中断，则服务器会阻止该主机进一步连接。
  您可以通过刷新主机缓存来取消阻止阻止的主机。 为此，请发出FLUSH HOSTS语句或执行mysqladmin flush-hosts命令。
  如果在上一次连接中断后，在少于max_connect_errors次尝试的情况下成功建立连接，则主机的错误计数将清零。
  但是，一旦主机被阻止，刷新主机缓存是解除阻塞的唯一方法。 默认值为100。

• table_open_cache = 1024
  操作系统允许mysqld打开的文件数。 此变量在运行时的值是系统允许的实际值，可能与您在服务器启动时指定的值不同。

• table_definition_cache = 1024
  可以存储在定义高速缓存中的表定义（来自.frm文件）的数量。
  如果使用大量表，则可以创建大型表定义高速缓存以加快表的打开速度。 与普通表缓存不同，表定义缓存占用的空间更少，不使用文件描述符。
  最小值为400。默认值基于以下公式，上限为2000：400 + (table_open_cache / 2)

• table_open_cache_instances = 16
  打开表缓存实例的数量。 为了通过减少会话之间的争用来提高可伸缩性，可以将打开表缓存划分为几个较小的缓存实例，
  其大小为table_open_cache / table_open_cache_instances。 会话需要仅锁定一个实例以访问DML语句。
  这会在实例之间对高速缓存进行分段，从而在有许多会话访问表时允许使用高速缓存的操作具有更高的性能。 （DDL语句仍然需要锁定整个缓存，但这些语句比DML语句要频繁得多。）
  对于常规使用16个或更多内核的系统，建议使用值8或16。

• thread_stack = 512K
  每个线程的堆栈大小。 默认值192KB（64位系统为256KB）足以进行正常操作。
  如果线程堆栈大小太小，则会限制服务器可以处理的SQL语句的复杂性，存储过程的递归深度以及其他消耗内存的操作。

• external-locking = FALSE
  启用外部锁定（系统锁定），默认情况下禁用。外部锁定仅影响MyISAM表访问。

• max_allowed_packet = 32M
  限制server接受的数据包的大小。如果写入大数据时，因为默认的配置太小，
  插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。
  默认值4MB

• sort_buffer_size = 4M
  必须执行排序的每个会话都会分配此大小的缓冲区。
  如果在SHOW GLOBAL STATUS输出中看到每秒许多Sort_merge_passes，您可以考虑增加sort_buffer_size值以加快使用查询优化或改进的索引而无法改进的ORDER BY或GROUP BY操作。

• join_buffer_size = 4M
  应用在经常会出现一些两表（或多表）join的操作需求，MySQL在完成某些join需求的时候（all row join/all index /scan join）
  为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作当join buffer 太小，
  MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，
  然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中，如此往复，这势必会造成被驱动表需要被多次读取，成倍增加IO访问，
  降低效率（执行计划中如果现实using join buffer）两个表关联的时候 减少参与被驱动表的join操作（没办法有效利用索引的时候）
  多表join时，就需要用到join buffer的三种情况:

  • All row join do not user indexes nad thus perform full table scans（没有索引的全表扫描）
  • All index join plain index scans（普通索引扫描）,
  • Range index scan join=rangeindex scans（范围索引扫描）,

  最好是添加适当的索引而不是纯粹加大join_buffer_size
  任何两个表间的全表join就会分配一次join_buffer也就是说，如果3个表join就会分配2次join buffer（而不是一个session只分配一次）

• thread_cache_size = 768 
  表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，
  那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.
  默认值-1，表示根据公式8 + (max_connections / 100)自动调整，上限值为100。最大值16384

• interactive_timeout = 600
  服务器在关闭之前等待交互式连接上的活动的秒数。 默认值8小时，太大需调整，需跟wait_timeout一起调整，且值要一致。

• wait_timeout = 600 
  服务器在关闭之前等待非交互式连接上的活动的秒数。默认值8小时，太大需调整，需跟interactive_timeout一起调整，且值要一致。

• tmp_table_size = 32M 
  内部内存临时表最大大小。实际限制是根据tmp_table_size和max_heap_table_size的较小值确定的。
  如果有许多GROUP BY查询并且消耗大量内存，则可以增加tmp_table_size（如果需要，还有max_heap_table_size）的值。

• max_heap_table_size = 32M
  管理heap、memory存储引擎表,此变量还与tmp_table_size结合使用，以限制内部内存表的大小

• log-error = /data/mysql/mysql3306/error.log 
  错误日志文件名

• slow_query_log = 1
  表示启用慢查询日志。

• slow_query_log_file = /data/mysql/mysql3306/slow.log
  慢查询日志文件的名称。

• long_query_time = 0.1
  如果查询花费的时间超过此秒数，则服务器会增加Slow_queries状态变量。
  如果启用了慢查询日志，则查询将记录到慢查询日志文件中。
  默认值10s

• log_queries_not_using_indexes =1
  如果运行的SQL语句没有使用到索引，则MySQL数据库会将这条SQL语句记录到慢查询日志文件中。

• log_throttle_queries_not_using_indexes = 60
  如果启用了log_queries_not_using_indexes，
  则log_throttle_queries_not_using_indexes变量会限制可写入慢查询日志的每分钟此类查询的数量。 值0（默认值）表示“无限制”。

• min_examined_row_limit = 100
  查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

• log_slow_admin_statements = 1
  在写入慢查询日志的语句中包含慢速管理语句。管理语句包括ALTER TABLE，ANALYZE TABLE，CHECK TABLE，CREATE INDEX，DROP INDEX，OPTIMIZE TABLE和REPAIR TABLE。

• log_slow_slave_statements = 1
  启用慢速查询日志时，此变量将启用对slave服务器上执行的查询超过long_query_time秒的日志记录。 这个变量是在MySQL 5.7.1中添加的。 设置此变量不会立即生效。 变量的状态适用于所有后续START SLAVE语句。
  请注意，即使启用了log_slow_slave_statements，主服务器中以行格式记录的所有语句也不会记录在slave的慢查询日志中。

• server-id = 1003306
  服务器id，如果启用binlog，则必须设置该值。

• log-bin = /data/mysql/mysql3306/logs/my3306_binlog
  表示启用binlog功能，并指定路径名称

• sync_binlog = 1
  sync_binlog选项控制mysql怎么刷新二进制日志到磁盘，在MySQL5.7.7后，

  • 默认为1：表示采用同步写磁盘的方式来写二进制日志。

  • 为0时：表示禁用MySQL服务器将日志同步写磁盘，相反，而是依赖于操作系统不时地将二进制日志刷新到磁盘。

  • 为N时：除了0与1之外的值，表示在提交N个事务后，binlog日志将同步到磁盘。

• binlog_cache_size = 4M
  使用事务表存储引擎（如innodb存储引擎）时，所有未提交的binlog日志会被记录到一个缓存中去，
  等事务提交时再将缓存中的binlog写入到binlog文件中。缓存的大小由binlog_cache_size决定，默认大小为32K。

• max_binlog_cache_size = 2G
  表示的是binlog 能够使用的最大cache 内存大小
  当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时
  就会报错：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”

• max_binlog_size = 1G
  指定单个binlog文件最大值。默认值为1g，最大值1g，如果超过该值，则产生新的binlog文件，后缀名+1，并记录到.index文件。

• binlog_rows_query_log_events = 1
  默认为不启用，启用binlog_rows_query_log_events时，会在binlog日志中记录原始SQL语句。

• binlog_format = row
  记录binlog的格式。[statement,row,mixed],在MySQL5.7.7之后，默认为row。

• binlog_checksum = 1
  表示启用，该参数目的就是写入binlog进行校验，有两个值[crc32|none],默认为crc32

• expire_logs_days = 7
  表示binlog文件自动删除N天前的文件。默认值为0，表示不自动删除，最大值99。

• log_slave_updates= 1
  从主服务器接收的更新是否应记录到从属服务器的二进制日志中。 默认值为0，表示不开启。一般应用在master=>slave=>slave架构

• master_info_repository = TABLE
  确定slave端是否将master状态和连接信息记录到FILE（master.info）或TABLE（mysql.slave_master_info）中，默认是FILE，建议调整为TABLE。

• relay_log_info_repository = TABLE
  表示slave 日志中salve 位置写入TABLE中(mysql.slave_relay_log_info)

• gtid_mode = on 
  启用基于GTID的日志记录以及日志可包含的事务类型

• enforce_gtid_consistency = 1
  服务器通过仅允许执行可使用GTID安全记录的语句来强制执行GTID一致性。 在启用基于GTID的复制之前，必须将此变量设置为ON。

• relay_log_recovery = 1
  当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。
  默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启

• relay-log-purge = 1
  启用自动清除中继日志文件。 默认值为1（ON）。

• key_buffer_size = 32M 
  用于MyISAM存储引擎表，缓存MyISAM存储

• read_buffer_size = 8M
  表顺序扫描的缓存，只能应用于MyISAM表存储引擎。

• read_rnd_buffer_size = 4M
  此变量用于从MyISAM表读取，对于任何其他存储引擎，用于多范围读取优化。

• bulk_insert_buffer_size = 64M
  用于myisam引擎，用一个特别的类似的树形结构体缓存，用于提高 insert select insert…values（…）(….)以及load data写数据到非空表的情景

• myisam_sort_buffer_size = 128M 
  在REPAIR TABLE期间排序MyISAM索引时或使用CREATE INDEX或ALTER TABLE创建索引时分配的缓冲区大小。

• myisam_max_sort_file_size = 10G
  允许MySQL在重新创建MyISAM索引时使用的临时文件的最大大小（在REPAIR TABLE，ALTER TABLE或LOAD DATA INFILE期间）。

• myisam_repair_threads = 1
  如果此值大于1，则在修复排序过程期间并行创建MyISAM表索引（每个索引在其自己的线程中）。 默认值为1。

• lock_wait_timeout = 3600 
  表示尝试获取元数据锁的超时（以秒为单位）,获取metadata lock的超时（单位为秒）
  包括对表，视图，存储过程和存储函数的DML和DDL操作，以及LOCK TABLES，FLUSH TABLES WITH READ LOCK和HANDLER语句。

• explicit_defaults_for_timestamp = 1
  此系统变量确定服务器是否为TIMESTAMP列中的默认值和NULL值处理启用某些非标准行为。 默认情况下，将禁用explicit_defaults_for_timestamp，从而启用非标准行为。

• innodb_thread_concurrency = 0 
  同一时刻能够进入innodb层次并发执行的线程数(注意是并发不是并行)，如果超过CPU核数，某些线程可能处于就绪态而没有获得CPU时间轮片，如果SERVER层的线程大于这个值，对不起多余的
  线程将会被放到一个叫做wait queue的队列中，而不能进入INNODB层次，进不到innodb层当然也就不能干活了，谈不上获得CPU。
  既然是一个队列那么它必然满足先进入先出的原则。这也是前面说的长痛不如短痛，与其让你不断的进行上文切换还不如把你处于睡眠态放弃CPU使用权，默认这个值是0，代表不限制。

• innodb_sync_spin_loops = 100
  在线程挂起之前线程等待InnoDB互斥锁被释放的次数。

• innodb_spin_wait_delay = 30
  为了防止自旋锁循环过快，耗费CPU。作用是控制轮询间隔，也就是说在每次轮询的过程中，会休息一会儿然后再轮询。

• transaction_isolation = REPEATABLE-READ
  事物的隔离级别

• #innodb_additional_mem_pool_size = 16M
  用来保存数据字典信息和其他内部数据结构的内存池大小。在MySQL5.7.4中删除。

• innodb_buffer_pool_size = 2560M
  缓存InnoDB表数据、索引、插入缓冲、数据字典等信息。innodb_buffer_pool_size必须是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，
  如果不是将自动调整为innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数

• innodb_buffer_pool_instances = 4
  表示InnoDB缓冲区可以被划分为4个实例，提高并发性，避免在高并发环境下，出现内存争用问题。
  需innodb_buffer_pool_size大于1G时，才会有效。

• innodb_buffer_pool_load_at_startup = 1 
  指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。
  在MySQL 5.7.7后默认启用。

• innodb_buffer_pool_dump_at_shutdown = 1
  指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。 通常与innodb_buffer_pool_load_at_startup结合使用。
  在MySQL 5.7.7后默认启用。

• innodb_buffer_pool_dump_pct = 25
  选项定义要转储的最近使用的缓冲池页面的百分比。在MySQL 5.7.7后默认为25%

• innodb_data_file_path = ibdata1:1G;ibdata2:10M:autoextend 
  定义InnoDB系统表空间数据文件的名称，大小和属性

• innodb_flush_log_at_trx_commit = 1

  • 默认值为1
    表示每次事务提交时，都会触发redo log thread 将日志缓冲中的数据写入文件，并‘flush’到磁盘。
  • 设置为0时
    表示每秒会将redo log buffer中的数据写入redo log文件，同时把数据刷入到磁盘中。
  • 设置为2时
    表示每次事务提交时，会把redo log buffer中的数据写入redo log文件，每秒把数据刷入到磁盘中。

• innodb_log_buffer_size = 32M
  InnoDB用于写入磁盘上日志文件的缓冲区大小（以字节为单位）。

• innodb_log_file_size = 2G 
  日志组中每个日志文件的大小（以字节为单位）

• innodb_log_files_in_group = 2
  表示日志组中有2个日志文件。

• innodb_max_undo_log_size = 4G
  表示undo表空间最大大小，如果超过此值，则可以启用innodb_undo_log_truncate来自动truncate undo表空间。

• innodb_undo_directory = undolog
  指定undo表空间位置。如果为指定，默认是在MySQL数据目录中创建。

• innodb_undo_tablespaces = 95
  定义undo表空间的数量。

• innodb_undo_logs = 128 
  定义InnoDB使用的回滚段数。

• innodb_io_capacity = 4000
  设置InnoDB后台任务每秒执行的I/ O操作数的上限，例如从缓冲池刷新页面和合并来自change buffer的数据。
  innodb_io_capacity限制是所有缓冲池实例的总限制。刷新脏页时，限制在缓冲池实例之间平均分配。

• innodb_io_capacity_max = 8000 
  定义了InnoDB后台任务在这种情况下每秒执行的I/O操作数的上限。

• innodb_flush_neighbors = 0 
  指定从InnoDB缓冲池刷新页面是否也刷新同一extent内的其他脏页。

  • 默认值1，从缓冲池中刷新同一extent内的连续脏页。
  • 设置为0，会关闭innodb_flush_neighbors，并且不会从缓冲池中刷新其他脏页。
  • 设置为2，会从缓冲池中刷新同一extent内的脏页。

  在SSD存储上应设置为0(禁用) ,因为使用顺序IO没有任何性能收益. 在使用RAID的某些硬件上也应该禁用此设置,因为逻辑上连续的块在物理磁盘上并不能保证也是连续的.

• innodb_write_io_threads = 8
  是数据库的写请求线程。默认值为4个.

• innodb_read_io_threads = 8
  是数据库的读请求线程。默认值为4个

• innodb_purge_threads = 4
  负责删除无用的undo页。由于进行DML语句的操作都会生成undo，系统需要定期对undo进行清理，
  这时就需要purge操作。在MySQL5.7.8后，默认线程个数为4，最大为32

• innodb_page_cleaners = 4
  负责脏页刷新的线程。在MySQL5.7.8后，默认线程个数为4，最大为32

• innodb_open_files = 65535
  指定MySQL可以一次保持打开的最大.ibd文件数。

• innodb_max_dirty_pages_pct = 50
  指buffer pool中脏页所占的百分比，达到设置的值，就会触发脏页的刷新。默认为75

• innodb_flush_method = O_DIRECT
  用于将数据刷新到InnoDB数据文件和日志文件的方法，这可能会影响I/O吞吐量。

• innodb_lru_scan_depth = 4000
  在更改缓冲池实例的数量时，请考虑调整innodb_lru_scan_depth，因为innodb_lru_scan_depth * innodb_buffer_pool_instances定义页清除程序线程每秒执行的工作量。

• innodb_checksum_algorithm = crc32 
  指定如何生成和验证存储在InnoDB表空间的磁盘块中的校验和。 crc32是MySQL 5.7.7的默认值。

• innodb_lock_wait_timeout = 10
  InnoDB事务等待行锁定的时间长度（以秒为单位）

• innodb_rollback_on_timeout = 1
  默认情况下，InnoDB仅回滚事务超时的最后一个语句。 如果指定了--innodb_rollback_on_timeout，则事务超时会导致InnoDB中止并回滚整个事务。

• innodb_print_all_deadlocks = 1
  启用此选项后，有关InnoDB用户事务中所有死锁的信息将记录在mysqld错误日志中

• innodb_file_per_table = 1
  当启用innodb_file_per_table（默认值）时，InnoDB将每个新创建的表的数据和索引存储在单独的.ibd文件中，而不是系统表空间中。 删除或截断表时，将回收这些表的存储。

• innodb_online_alter_log_max_size = 4G 
  指定InnoDB表的在线DDL操作期间使用的临时日志文件大小的上限（以字节为单位）。
  每个正在创建的索引或要更改的表都有一个这样的日志文件。 此日志文件存储在DDL操作期间在表中插入，更新或删除的数据。
  临时日志文件在需要时由innodb_sort_buffer_size的值扩展，最大为innodb_online_alter_log_max_size指定的最大值。
  如果临时日志文件超出大小上限，则ALTER TABLE操作将失败，并且将回滚所有未提交的并发DML操作。

• internal_tmp_disk_storage_engine = InnoDB
  磁盘内部临时表的存储引擎

• innodb_stats_on_metadata = 0 
  禁用时，InnoDB不会在（如SHOW TABLE STATUS）或访问INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS）操作期间更新统计信息。
  保留禁用的设置可以提高具有大量表或索引的模式的访问速度。它还可以提高涉及InnoDB表的查询的执行计划的稳定性。

• innodb_checksums = 1
  InnoDB可以对从磁盘读取的所有表空间页面使用校验和验证，以确保对硬件故障或损坏的数据文件具有额外的容错能力。 默认情况下启用此验证。

• query_cache_size = 0 
  查询缓存相关参数，建议关闭。默认是关闭

• query_cache_type = 0
  查询缓存相关参数，建议关闭。默认是关闭

• innodb_status_file = 1
  启用InnoDB的status file，便于管理员查看以及监控等

• innodb_status_output = 0

• innodb_status_output_locks = 0
  注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快

• performance_schema = 1
  表示启用performance schema

• performance_schema_instrument = '%=on'
  打开所有指定。

• innodb_monitor_enable="module_innodb"
• innodb_monitor_enable="module_server"
• innodb_monitor_enable="module_dml"
• innodb_monitor_enable="module_ddl"
• innodb_monitor_enable="module_trx"
• innodb_monitor_enable="module_os"
• innodb_monitor_enable="module_purge"
• innodb_monitor_enable="module_log"
• innodb_monitor_enable="module_lock"
• innodb_monitor_enable="module_buffer"
• innodb_monitor_enable="module_index"
• innodb_monitor_enable="module_ibuf_system"
• innodb_monitor_enable="module_buffer_page"
• innodb_monitor_enable="module_adaptive_hash"
  innodb 监控设置

• [mysqldump]
  针对mysqldump 数据库备份程序的参数

• quick
  该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

• max_allowed_packet = 32M
  限制server接受的数据包的大小。如果写入大数据时，因为默认的配置太小，
  插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。
  默认值4MB

2. 错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。

下面示例是一个根据错误日志来处理步骤。

示例准备

1). 在MySQL数据目录手动创建mydir目录，并更改为属主为mysql

# cd /data/mysql/mysql3306/data
# mkdir mydir
# chown mysql:mysql

 

2). 登陆mysql，使用命令show databases可以看到mydir。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
  ...
| mydir              |
+--------------------+
13 rows in set (0.00 sec)

 

3). 在mydir数据库中，创建表t1

mysql> use mydir
mysql> create table t1(c1 int);

 

4). 删除datadir中的mydir目录

# cd /data/mysql/mysql3306/data
# rm -rf mydir

 

5). 重启MySQL 

根据错误日志，来处理数据库错误

1). 查看error log日志

mysql> select @@log_error;
+---------------------------------+
| @@log_error                     |
+---------------------------------+
| /data/mysql/mysql3306/error.log |
+---------------------------------+

# tail -100f  /data/mysql/mysql3306/error.log

2018-08-26T13:13:15.132051Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-08-26T13:13:15.132068Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-08-26T13:13:15.132074Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-08-26T13:13:15.132079Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mydir/t1.ibd' OS error: 71
2018-08-26T13:13:15.132084Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-08-26T13:13:15.132087Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-08-26T13:13:15.132091Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-08-26T13:13:15.132097Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mydir/t1`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

 

2). 错误日志中提示找不到/mydir/t1.ibd 表，mydir目录不存在了。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%t1%';
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
|      196 | mydir/t1        |   33 |      4 |   326 | Barracuda   | Dynamic    |             0 | Single     |
|      155 | sbtest/sbtest1  |   33 |      7 |   280 | Barracuda   | Dynamic    |             0 | Single     |
|      160 | sbtest/sbtest10 |   33 |      7 |   285 | Barracuda   | Dynamic    |             0 | Single     |
|      120 | test/t11        |   33 |      6 |   229 | Barracuda   | Dynamic    |             0 | Single     |
|      121 | test/t12        |   33 |      5 |   230 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
5 rows in set (0.00 sec)

 

3). 查看mydir的表结构

mysql> SELECT a.NAME,
    ->        b.name AS col_name,
    ->        CASE
    ->           WHEN b.MTYPE = 1 THEN 'VARCHAR'
    ->           WHEN b.MTYPE = 2 THEN 'CHAR'
    ->           WHEN b.MTYPE = 3 THEN 'FIXBINARY'
    ->           WHEN b.MTYPE = 4 THEN 'BINARY'
    ->           WHEN b.MTYPE = 5 THEN 'BLOB'
    ->           WHEN b.MTYPE = 6 THEN 'int'
    ->           WHEN b.MTYPE = 7 THEN 'SYS_CHILD'
    ->           WHEN b.MTYPE = 8 THEN 'SYS'
    ->           WHEN b.MTYPE = 9 THEN 'FLOAT'
    ->           WHEN b.MTYPE = 10 THEN 'DOUBLE'
    ->           WHEN b.MTYPE = 11 THEN 'DECIMAL'
    ->           WHEN b.MTYPE = 12 THEN 'VARMYSQL'
    ->           WHEN b.MTYPE = 13 THEN 'MYSQL'
    ->           WHEN b.MTYPE = 14 THEN 'GEOMETRY'
    ->           ELSE MTYPE
    ->        END
    ->           AS Type
    -> FROM INNODB_SYS_TABLES    a
    ->      JOIN INNODB_SYS_COLUMNS b ON a.TABLE_ID = b.TABLE_ID
    -> WHERE a.NAME = 'mydir/t1';
+----------+----------+------+
| NAME     | col_name | Type |
+----------+----------+------+
| mydir/t1 | c1       | int  |
+----------+----------+------+
1 row in set (0.01 sec)

 

4). 在test库中，创建同样表结构的t1表。

mysql> create table t1(c1 int);

 

5). 在datadir目录创建mydir目录，并且把test目录中的t1.frm复制到mydir目录中

# mkdir /data/mysql/mysql3306/data/mydir
# cp /data/mysql/mysql3306/data/test/t1.frm /data/mysql/mysql3306/data/mydir
# chown -R mysql:mysql /data/mysql/mysql3306/data/mydir

 

6). 删除mydir数据库

mysql> drop database mydir;

 

7). 重新查询

mysql> SELECT a.NAME,
    ->        b.name AS col_name,
    ->        CASE
    ->           WHEN b.MTYPE = 1 THEN 'VARCHAR'
    ->           WHEN b.MTYPE = 2 THEN 'CHAR'
    ->           WHEN b.MTYPE = 3 THEN 'FIXBINARY'
    ->           WHEN b.MTYPE = 4 THEN 'BINARY'
    ->           WHEN b.MTYPE = 5 THEN 'BLOB'
    ->           WHEN b.MTYPE = 6 THEN 'int'
    ->           WHEN b.MTYPE = 7 THEN 'SYS_CHILD'
    ->           WHEN b.MTYPE = 8 THEN 'SYS'
    ->           WHEN b.MTYPE = 9 THEN 'FLOAT'
    ->           WHEN b.MTYPE = 10 THEN 'DOUBLE'
    ->           WHEN b.MTYPE = 11 THEN 'DECIMAL'
    ->           WHEN b.MTYPE = 12 THEN 'VARMYSQL'
    ->           WHEN b.MTYPE = 13 THEN 'MYSQL'
    ->           WHEN b.MTYPE = 14 THEN 'GEOMETRY'
    ->           ELSE MTYPE
    ->        END
    ->           AS Type
    -> FROM INNODB_SYS_TABLES    a
    ->      JOIN INNODB_SYS_COLUMNS b ON a.TABLE_ID = b.TABLE_ID
    -> WHERE a.NAME = 'mydir/t1';
Empty set (0.01 sec)

 

8). 重启数据库，观察错误日志，已没有此前的错误。

# mysqladmin -S /tmp/mysql3306.sock shutdown
# tail -100f error.log

2018-08-26T14:09:16.614703Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-08-26T14:09:16.614878Z 0 [Note] mysqld (mysqld 5.7.23-log) starting as process 591 ...
2018-08-26T14:09:16.625924Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-08-26T14:09:16.626048Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-08-26T14:09:16.626058Z 0 [Note] InnoDB: Uses event mutexes
2018-08-26T14:09:16.626344Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2018-08-26T14:09:16.626365Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-08-26T14:09:16.626369Z 0 [Note] InnoDB: Using Linux native AIO
2018-08-26T14:09:16.628539Z 0 [Note] InnoDB: Number of pools: 1
2018-08-26T14:09:16.628698Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-08-26T14:09:16.638746Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2.5G, instances = 4, chunk size = 128M
2018-08-26T14:09:16.868792Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-08-26T14:09:16.890056Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-08-26T14:09:16.944638Z 0 [Note] InnoDB: Opened 95 undo tablespaces
2018-08-26T14:09:16.944697Z 0 [Note] InnoDB: 95 undo tablespaces made active
2018-08-26T14:09:16.945949Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-08-26T14:09:17.375987Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-08-26T14:09:17.376087Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-08-26T14:09:17.546965Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-08-26T14:09:17.548799Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-08-26T14:09:17.548850Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-08-26T14:09:17.549070Z 0 [Note] InnoDB: Waiting for purge to start
2018-08-26T14:09:17.607025Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 1651418232
2018-08-26T14:09:17.608571Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2018-08-26T14:09:17.611403Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-08-26T14:09:17.749763Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-08-26T14:09:17.749789Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-08-26T14:09:17.749851Z 0 [Note] IPv6 is available.
2018-08-26T14:09:17.749860Z 0 [Note]   - '::' resolves to '::';
2018-08-26T14:09:17.749874Z 0 [Note] Server socket created on IP: '::'.
2018-08-26T14:09:17.886092Z 0 [Note] Event Scheduler: Loaded 0 events
2018-08-26T14:09:17.886277Z 0 [Note] mysqld: ready for connections.
Version: '5.7.23-log'  socket: '/tmp/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)
2018-08-26T14:09:18.038313Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180826 22:09:18

 

3. 二进制日志文件

binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志，详细信息请参考博文MySQL redo log 与 binlog 的区别

二进制日志的主要作用：

• 1）可以完成主从复制。在主服务器上把所有修改数据的操作记录到binlog中，通过网络发送给从服务器，从而达到主从同步。

• 2）进行恢复操作。数据可以通过binlog日志，使用mysqlbinlog命令，实现基于时间点和位置的恢复操作。

3.1 数据库gtid_mode=ON

利用二进制日志可以实现基于时间与位置的恢复，例如由于误操作删除了一张表，这时候完全恢复是没用的，因为日志里面还是存在错误语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作数据，再恢复后面操作语句。

3.1.1 创建表pitr，并插入两条数据。

mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON         |
+-------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table pitr(name varchar(30), birthday timestamp);
Query OK, 0 rows affected (0.07 sec)

mysql>  insert into pitr values('张三',sysdate());
Query OK, 1 row affected (0.02 sec)

mysql>  insert into pitr values('李四',sysdate());
Query OK, 1 row affected (0.02 sec)

mysql> select * from pitr;
+--------+---------------------+
| name   | birthday            |
+--------+---------------------+
| 张三   | 2018-08-27 13:00:05 |
| 李四   | 2018-08-27 13:00:14 |
+--------+---------------------+
2 rows in set (0.00 sec)

 

3.1.2 误删除数据

误删除数据'李四',并插入数据'王五'，删除表pitr

mysql> delete from pitr where name='李四';
Query OK, 1 row affected (0.01 sec)

mysql> insert into pitr values('王五',sysdate());
Query OK, 1 row affected (0.01 sec)

mysql> select * from pitr;
+--------+---------------------+
| name   | birthday            |
+--------+---------------------+
| 张三   | 2018-08-27 13:00:05 |
| 王五   | 2018-08-27 13:00:55 |
+--------+---------------------+
2 rows in set (0.00 sec)

 

3.1.3 利用mysqlbinlog查看binlog

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+----------------------+----------+--------------+------------------+-------------------------------------------+
| my3306_binlog.000002 |     1921 |              |                  | e4382832-949d-11e8-97ba-080027793430:1-10 |
+----------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'my3306_binlog.000002';
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name             | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                |
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| my3306_binlog.000002 |    4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
| my3306_binlog.000002 |  123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-4                            |
| my3306_binlog.000002 |  194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:5'   |
| my3306_binlog.000002 |  259 | Query          |   1003306 |         390 | use `test`; create table pitr(name varchar(30), birthday timestamp) |
| my3306_binlog.000002 |  390 | Gtid           |   1003306 |         455 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:6'   |
| my3306_binlog.000002 |  455 | Query          |   1003306 |         535 | BEGIN                                                               |
| my3306_binlog.000002 |  535 | Rows_query     |   1003306 |         602 | # insert into pitr values('张三',sysdate())                         |
| my3306_binlog.000002 |  602 | Table_map      |   1003306 |         653 | table_id: 122 (test.pitr)                                           |
| my3306_binlog.000002 |  653 | Write_rows     |   1003306 |         700 | table_id: 122 flags: STMT_END_F                                     |
| my3306_binlog.000002 |  700 | Xid            |   1003306 |         731 | COMMIT /* xid=612 */                                                |
| my3306_binlog.000002 |  731 | Gtid           |   1003306 |         796 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:7'   |
| my3306_binlog.000002 |  796 | Query          |   1003306 |         876 | BEGIN                                                               |
| my3306_binlog.000002 |  876 | Rows_query     |   1003306 |         943 | # insert into pitr values('李四',sysdate())                         |
| my3306_binlog.000002 |  943 | Table_map      |   1003306 |         994 | table_id: 122 (test.pitr)                                           |
| my3306_binlog.000002 |  994 | Write_rows     |   1003306 |        1041 | table_id: 122 flags: STMT_END_F                                     |
| my3306_binlog.000002 | 1041 | Xid            |   1003306 |        1072 | COMMIT /* xid=613 */                                                |
| my3306_binlog.000002 | 1072 | Gtid           |   1003306 |        1137 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:8'   |
| my3306_binlog.000002 | 1137 | Query          |   1003306 |        1209 | BEGIN                                                               |
| my3306_binlog.000002 | 1209 | Rows_query     |   1003306 |        1269 | # delete from pitr where name='李四'                                |
| my3306_binlog.000002 | 1269 | Table_map      |   1003306 |        1320 | table_id: 122 (test.pitr)                                           |
| my3306_binlog.000002 | 1320 | Delete_rows    |   1003306 |        1367 | table_id: 122 flags: STMT_END_F                                     |
| my3306_binlog.000002 | 1367 | Xid            |   1003306 |        1398 | COMMIT /* xid=615 */                                                |
| my3306_binlog.000002 | 1398 | Gtid           |   1003306 |        1463 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:9'   |
| my3306_binlog.000002 | 1463 | Query          |   1003306 |        1543 | BEGIN                                                               |
| my3306_binlog.000002 | 1543 | Rows_query     |   1003306 |        1610 | # insert into pitr values('王五',sysdate())                         |
| my3306_binlog.000002 | 1610 | Table_map      |   1003306 |        1661 | table_id: 122 (test.pitr)                                           |
| my3306_binlog.000002 | 1661 | Write_rows     |   1003306 |        1708 | table_id: 122 flags: STMT_END_F                                     |
| my3306_binlog.000002 | 1708 | Xid            |   1003306 |        1739 | COMMIT /* xid=616 */                                                |
| my3306_binlog.000002 | 1739 | Gtid           |   1003306 |        1804 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:10'  |
| my3306_binlog.000002 | 1804 | Query          |   1003306 |        1921 | use `test`; DROP TABLE `pitr` /* generated by server */             |
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
30 rows in set (0.00 sec)


# mysqlbinlog --no-defaults -vv --base64-output=decode-rows my3306_binlog.000001


/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180827 12:54:21 server id 1003306  end_log_pos 123 CRC32 0xd97051b3     Start: binlog v 4, server v 5.7.23-log created 180827 12:54:21 at startup
ROLLBACK/*!*/;
# at 123
#180827 12:54:21 server id 1003306  end_log_pos 154 CRC32 0x440442c1     Previous-GTIDs
# [empty]
# at 154
#180827 12:13:03 server id 1003306  end_log_pos 219 CRC32 0xd7a0969e     GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:1'/*!*/;
# at 219
#180827 12:13:03 server id 1003306  end_log_pos 349 CRC32 0x21f3f0ef     Query    thread_id=21    exec_time=2491    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1535343183/*!*/;
SET @@session.pseudo_thread_id=21/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
create table pitr(name varchar(20),birthday timestamp)
/*!*/;
# at 349
#180827 12:13:40 server id 1003306  end_log_pos 414 CRC32 0x7b818eb7     GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:2'/*!*/;
# at 414
#180827 12:13:40 server id 1003306  end_log_pos 486 CRC32 0x79a2e77e     Query    thread_id=21    exec_time=2454    error_code=0
SET TIMESTAMP=1535343220/*!*/;
SET @@session.sql_mode=524288/*!*/;
BEGIN
/*!*/;
# at 486
#180827 12:13:40 server id 1003306  end_log_pos 654 CRC32 0xbcf69674     Rows_query
# BINLOG '
# dHqDWxMqTw8AMwAAAGUCAAAAAHgAAAAAAAEABHRlc3QABHBpdHIAAg8RA1AAAAMDbVoa
# dHqDWx4qTw8ALwAAAJQCAAAAAHgAAAAAAAEAAgAC//wG5byg5LiJW4N6dC294eg=
# '
# at 654
#180827 12:13:40 server id 1003306  end_log_pos 705 CRC32 0x344b2cd9     Table_map: `test`.`pitr` mapped to number 121
# at 705
#180827 12:13:40 server id 1003306  end_log_pos 752 CRC32 0x615eda67     Write_rows: table id 121 flags: STMT_END_F
### INSERT INTO `test`.`pitr`
### SET
###   @1='张三' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @2=1535343220 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 752
#180827 12:13:40 server id 1003306  end_log_pos 783 CRC32 0xdb27b4f0     Xid = 587
COMMIT/*!*/;
# at 783
#180827 12:13:46 server id 1003306  end_log_pos 848 CRC32 0x68107e14     GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:3'/*!*/;
# at 848
#180827 12:13:46 server id 1003306  end_log_pos 920 CRC32 0x23c1438e     Query    thread_id=21    exec_time=2448    error_code=0
SET TIMESTAMP=1535343226/*!*/;
BEGIN
/*!*/;
# at 920
#180827 12:13:46 server id 1003306  end_log_pos 1088 CRC32 0x8d9184ed     Rows_query
# BINLOG '
# enqDWxMqTw8AMwAAALsDAAAAAHgAAAAAAAEABHRlc3QABHBpdHIAAg8RA1AAAAN2SiQc
# enqDWx4qTw8ALwAAAOoDAAAAAHgAAAAAAAEAAgAC//wG5p2O5ZubW4N6ennHpOc=
# '
# at 1088
#180827 12:13:46 server id 1003306  end_log_pos 1139 CRC32 0x02366796     Table_map: `test`.`pitr` mapped to number 121
# at 1139
#180827 12:13:46 server id 1003306  end_log_pos 1186 CRC32 0xb45043ab     Write_rows: table id 121 flags: STMT_END_F
### INSERT INTO `test`.`pitr`
### SET
###   @1='李四' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @2=1535343226 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 1186
#180827 12:13:46 server id 1003306  end_log_pos 1217 CRC32 0xb22a5e76     Xid = 594
COMMIT/*!*/;
# at 1217
#180827 12:59:26 server id 1003306  end_log_pos 1282 CRC32 0x205f1806     GTID    last_committed=3    sequence_number=4    rbr_only=no
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:4'/*!*/;
# at 1282
#180827 12:59:26 server id 1003306  end_log_pos 1399 CRC32 0x852c531f     Query    thread_id=27    exec_time=0    error_code=0
SET TIMESTAMP=1535345966/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
DROP TABLE `pitr` /* generated by server */
/*!*/;
# at 1399
#180827 12:59:31 server id 1003306  end_log_pos 1450 CRC32 0x8d409e4b     Rotate to my3306_binlog.000002  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

3.1.4 基于位置恢复

恢复pitr表中三条数据。

# mysqlbinlog --no-defaults  --stop-position='1072' /data/mysql/mysql3306/logs/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

# mysqlbinlog --no-defaults  --start-position='1398' --stop-position='1739' /data/mysql/mysql3306/logs/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock


mysql> use test;
Database changed

mysql> select * from pitr;
ERROR 1146 (42S02): Table 'test.pitr' doesn't exist

 

并没有恢复。。。

是不是缺了什么步骤，理下思路，是不是binlog里的gtid小于当前的gtid，然后恢复的时候就跳过了。

为了确认自己想法的正确性，动手操作吧。

1). 备份当前binlog

# cp my3306_binlog.000002 ../

2). 重置gtid

mysql> reset master;
Query OK, 0 rows affected (0.05 sec)

3). 重新恢复
# mysqlbinlog --no-defaults  --stop-position='1072' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

# mysqlbinlog --no-defaults  --start-position='1398' --stop-position='1739' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

4). 重新查询

mysql> select * from pitr;
+--------+---------------------+
| name   | birthday            |
+--------+---------------------+
| 张三   | 2018-08-27 13:00:05 |
| 李四   | 2018-08-27 13:00:14 |
| 王五   | 2018-08-27 13:00:55 |
+--------+---------------------+
3 rows in set (0.00 sec)

恢复成功

 

下面尝试变更gtid模式能不能恢复

0） ON 模式(新的和复制的事务都必须是GTID事务。)

1） ON_PERMISSIVE 模式（新事务是GTID事务，复制事务可以是GTID也可以不是GTID事务）

root@localhost [test] 13:18:14> set global gtid_mode ='ON_PERMISSIVE';
Query OK, 0 rows affected (0.04 sec)

# mysqlbinlog --no-defaults  --stop-position='1072' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

# mysqlbinlog --no-defaults  --start-position='1398' --stop-position='1739' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

root@localhost [test] 13:19:09> select * from pitr;
ERROR 1146 (42S02): Table 'test.pitr' doesn't exist

2） OFF_PERMISSIVE 模式（新事务不是GTID事务，复制事务可以是GTID也可以不是GTID事务）
root@localhost [test] 13:19:46> set global gtid_mode = 'OFF_PERMISSIVE';

# mysqlbinlog --no-defaults  --stop-position='1072' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

# mysqlbinlog --no-defaults  --start-position='1398' --stop-position='1739' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock

root@localhost [test] 13:21:06> select * from pitr;
ERROR 1146 (42S02): Table 'test.pitr' doesn't exist

3） OFF模式（不产生GTID，只接受不带GTID的事务）  
root@localhost [test] 13:21:16> set global gtid_mode ='OFF';
# mysqlbinlog --no-defaults  --stop-position='1072' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock
ERROR 1781 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

[root@mysqldb1 13:22:56 /data/mysql/mysql3306/logs]
# mysqlbinlog --no-defaults  --start-position='1398' --stop-position='1739' /data/mysql/mysql3306/my3306_binlog.000002 | mysql -S /tmp/mysql3306.sock
ERROR 1781 (HY000) at line 15: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

说明不能恢复。。。

 

3.1.5 加上选项--skip-gtids【重要】

不要在输出中显示任何GTID。从一个或多个包含GTID的二进制日志写入转储文件时需要这样做，如下例所示：

# mysqlbinlog --no-defaults  --skip-gtids --stop-position='1106' /data/mysql/mysql3306/logs/my3306_binlog.000019 | mysql -S /tmp/mysql3306.sock

也能恢复数据。

 

3.2 数据库gtid_mode=OFF

使用mysqlbinlog命令，实现基于位置的恢复操作

mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+
1 row in set (0.00 sec)

root@localhost [(none)] 14:06:07> use test;
Database changed
root@localhost [test] 14:06:17> create table pitr(c1 int);
Query OK, 0 rows affected (0.08 sec)

root@localhost [test] 14:06:41> insert into pitr select 1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [test] 14:06:52> insert into pitr select 2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [test] 14:06:55> insert into pitr select 3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [test] 14:06:58> drop table pitr;
Query OK, 0 rows affected (0.04 sec)

root@localhost [test] 14:07:05> show master status;
+----------------------+----------+--------------+------------------+----------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+----------------------+----------+--------------+------------------+----------------------------------------+
| my3306_binlog.000005 |     1452 |              |                  | e4382832-949d-11e8-97ba-080027793430:1 |
+----------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

root@localhost [test] 14:07:12> show binlog events in 'my3306_binlog.000005';
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
| Log_name             | Pos  | Event_type     | Server_id | End_log_pos | Info                                                    |
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
| my3306_binlog.000005 |    4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                   |
| my3306_binlog.000005 |  123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1                  |
| my3306_binlog.000005 |  194 | Anonymous_Gtid |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                    |
| my3306_binlog.000005 |  259 | Query          |   1003306 |         358 | use `test`; create table pitr(c1 int)                   |
| my3306_binlog.000005 |  358 | Anonymous_Gtid |   1003306 |         423 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                    |
| my3306_binlog.000005 |  423 | Query          |   1003306 |         495 | BEGIN                                                   |
| my3306_binlog.000005 |  495 | Rows_query     |   1003306 |         544 | # insert into pitr select 1                             |
| my3306_binlog.000005 |  544 | Table_map      |   1003306 |         591 | table_id: 129 (test.pitr)                               |
| my3306_binlog.000005 |  591 | Write_rows     |   1003306 |         631 | table_id: 129 flags: STMT_END_F                         |
| my3306_binlog.000005 |  631 | Xid            |   1003306 |         662 | COMMIT /* xid=1066 */                                   |
| my3306_binlog.000005 |  662 | Anonymous_Gtid |   1003306 |         727 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                    |
| my3306_binlog.000005 |  727 | Query          |   1003306 |         799 | BEGIN                                                   |
| my3306_binlog.000005 |  799 | Rows_query     |   1003306 |         848 | # insert into pitr select 2                             |
| my3306_binlog.000005 |  848 | Table_map      |   1003306 |         895 | table_id: 129 (test.pitr)                               |
| my3306_binlog.000005 |  895 | Write_rows     |   1003306 |         935 | table_id: 129 flags: STMT_END_F                         |
| my3306_binlog.000005 |  935 | Xid            |   1003306 |         966 | COMMIT /* xid=1067 */                                   |
| my3306_binlog.000005 |  966 | Anonymous_Gtid |   1003306 |        1031 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                    |
| my3306_binlog.000005 | 1031 | Query          |   1003306 |        1103 | BEGIN                                                   |
| my3306_binlog.000005 | 1103 | Rows_query     |   1003306 |        1152 | # insert into pitr select 3                             |
| my3306_binlog.000005 | 1152 | Table_map      |   1003306 |        1199 | table_id: 129 (test.pitr)                               |
| my3306_binlog.000005 | 1199 | Write_rows     |   1003306 |        1239 | table_id: 129 flags: STMT_END_F                         |
| my3306_binlog.000005 | 1239 | Xid            |   1003306 |        1270 | COMMIT /* xid=1068 */                                   |
| my3306_binlog.000005 | 1270 | Anonymous_Gtid |   1003306 |        1335 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                    |
| my3306_binlog.000005 | 1335 | Query          |   1003306 |        1452 | use `test`; DROP TABLE `pitr` /* generated by server */ |
+----------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
24 rows in set (0.00 sec)

# mysqlbinlog --no-defaults  --stop-position='1270' /data/mysql/mysql3306/logs/my3306_binlog.000005 | mysql -S /tmp/mysql3306.sock

root@localhost [(none)] 14:08:21> use test;
Database changed
root@localhost [test] 14:08:24> select * from pitr;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

 

4. 慢查询日志

慢查询日志可以把超过参数long_query_time时间的所有SQL语句记录进来，帮助DBA人员优化所有问题的SQL语句。

通过mysqldumpslow工具可以查看慢查询日志，如：

# mysqldumpslow /data/mysql/mysql3306/slow.log 

Reading mysql slow query log from /data/mysql/mysql3306/slow.log
Count: 1  Time=2.85s (2s)  Lock=0.00s (0s)  Rows=331603.0 (331603), wanbin[wanbin]@[192.168.56.1]
  SELECT *
  FROM employees.dept_emp
  LEFT JOIN employees.departments
  ON employees.departments.dept_no = employees.dept_emp.dept_no
  LEFT JOIN employees.employees
  ON employees.employees.emp_no = employees.dept_emp.emp_no;
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  # Time: N-N-24T08:N:N.171584Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  set @idbdataindx = (select sum(data_length+index_length) from information_schema.tables where engine = 'S')

Count: 1  Time=1.55s (1s)  Lock=0.00s (0s)  Rows=331603.0 (331603), wanbin[wanbin]@[192.168.56.1]
  SELECT *
  FROM employees.dept_emp
  LEFT JOIN employees.departments
  ON employees.departments.dept_no = employees.dept_emp.dept_no

...

 

如果用户想得到执行时间最长的10条SQL

# mysqldumpslow -s al -n 10 /data/mysql/mysql3306/slow.log 

Reading mysql slow query log from /data/mysql/mysql3306/slow.log
Count: 1  Time=0.18s (0s)  Lock=0.41s (0s)  Rows=1.0 (1), root[root]@localhost
  select * from schema_auto_increment_columns

Count: 1  Time=0.00s (0s)  Lock=0.09s (0s)  Rows=8.0 (8), root[root]@localhost
  SELECT SUBSTRING_INDEX(event_name,'S',N) AS
  code_area, sys.format_bytes(SUM(current_alloc))
  AS current_alloc
  FROM sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(event_name,'S',N)
  ORDER BY SUM(current_alloc) DESC
...

 

下面介绍利用percona-toolkit工具，查看慢查询日志。

1）解压percona-toolkit工具包

# tar -zxvf percona-toolkit-3.0.11_x86_64.tar.gz

 

2）添加至环境变量中

# echo "export PATH=$PATH:/opt/percona-toolkit-3.0.11/bin" >> /etc/profile

# source /etc/profile

 

3）分析slowlog

# pt-query-digest /data/mysql/mysql3306/slow.log 
Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /opt/percona-toolkit-3.0.11/bin/pt-query-digest line 75.
BEGIN failed--compilation aborted at /opt/percona-toolkit-3.0.11/bin/pt-query-digest line 75.

 

报错提示缺少了Data/Dumper.pm 包

wget http://www.cpan.org/modules/by-module/Data/Data-Dumper-2.161.tar.gz

# tar -xvzf Data-Dumper-2.161.tar.gz 

# cd Data-Dumper-2.161/

# perl Makefile.PL 
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 2.
BEGIN failed--compilation aborted at Makefile.PL line 2.

# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan

# make

# make install

# pt-query-digest /data/mysql/mysql3306/slow.log 
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /opt/percona-toolkit-3.0.11/bin/pt-query-digest line 2470.
BEGIN failed--compilation aborted at /opt/percona-toolkit-3.0.11/bin/pt-query-digest line 2470.

 

又提示缺包。。

# yum -y install perl-Digest-MD5

 

再分析

# pt-query-digest /data/mysql/mysql3306/slow.log 

# 250ms user time, 20ms system time, 20.30M rss, 174.68M vsz
# Current date: Mon Aug 27 14:53:12 2018
# Hostname: mysqldb1
# Files: /data/mysql/mysql3306/slow.log
# Overall: 268 total, 90 unique, 0.00 QPS, 0.00x concurrency _____________
# Time range: 2018-08-01T02:24:23 to 2018-08-26T14:29:23
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            42s   316us      3s   157ms   393ms   252ms   116ms
# Lock time          957ms       0   406ms     4ms     6ms    26ms    28us
# Rows sent          2.58M       0 323.83k   9.85k  511.45  51.51k   97.36
# Rows examine       4.12M       0 971.49k  15.76k   9.80k  83.39k  192.76
# Query size        28.02k      19   1.11k  107.06  511.45  163.57   56.92

# Profile
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0xA729E7889F57828D3821AE1F716...  9.3447 22.2%    47 0.1988  0.04 SELECT sbtest?
#    2 0xFF7C69F51BBD3A736EEB1BFDCCF...  7.3304 17.5%    42 0.1745  0.04 SELECT sbtest?
#    3 0xF0C5AE75A52E847D737F39F04B1...  4.9573 11.8%    29 0.1709  0.03 SELECT sbtest?
#    4 0x9934EF6887CC7A6384D1DEE77FA...  4.9344 11.7%    35 0.1410  0.01 SELECT sbtest?
#    5 0xF7424F4815EECCF244D3DC308F4...  3.6196  8.6%     3 1.2065  0.00 SELECT dept_emp
#    6 0x589AC02E8EA38FF73755BE6B68C...  2.8542  6.8%     1 2.8542  0.00 SELECT employees.dept_emp employees.departments employees.employees
 information_schema.tables
#    7 0xDB9BE8D3B05BEF64B13C834B887...  1.6024  3.8%     2 0.8012  0.00 SELECT information_schema.innodb_buffer_page
#    8 0x1038022FC2BA7F900E5A45FBEF0...  1.5519  3.7%     1 1.5519  0.00 SELECT employees.dept_emp employees.departments
#    9 0xA5FAF17DCA069D1E9FC702E9D19...  1.2378  2.9%     1 1.2378  0.00 SELECT innodb_buffer_stats_by_schema
#   10 0xB50AD757EF59AF1382ECBFB7827...  0.5968  1.4%     3 0.1989  0.09 SELECT INFORMATION_SCHEMA.FILES INFORMATION_SCHEMA.PARTITIONS
#   11 0x77465EBF6BA1D66F44A19FF46A7...  0.5883  1.4%     1 0.5883  0.00 SELECT schema_auto_increment_columns
#   12 0x70A8C2B22252441D191B17EB508...  0.4334  1.0%     1 0.4334  0.00 SELECT innodb_buffer_stats_by_table
#   13 0xDB534C97AF86024F633F5856DF7...  0.4042  1.0%     1 0.4042  0.00 SELECT employees
#   14 0xE3C753C2F267B2D767A347A2812...  0.3302  0.8%     1 0.3302  0.00 SELECT help_topic
#   15 0xA3A7D9D5A6985CE9A553C9BA246...  0.3289  0.8%     1 0.3289  0.00 SELECT dept_emp
# MISC 0xMISC                            1.8876  4.5%    99 0.0191   0.0 <75 ITEMS>

# Query 1: 2.04 QPS, 0.41x concurrency, ID 0xA729E7889F57828D3821AE1F716D5205 at byte 68014
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2018-08-25T00:50:18 to 2018-08-25T00:50:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         17      47
# Exec time     22      9s   101ms   502ms   199ms   356ms    88ms   167ms
# Lock time      0     1ms    14us   263us    25us    30us    35us    18us
# Rows sent      0   4.59k     100     100     100     100       0     100
# Rows examine   0   4.59k     100     100     100     100       0     100
# Query size     8   2.48k      54      55   54.04   51.63    0.50   51.63
# String:
# Databases    move_db
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `move_db` LIKE 'sbtest1'\G
#    SHOW CREATE TABLE `move_db`.`sbtest1`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c FROM sbtest1 WHERE id BETWEEN 22744 AND 22843\G

 

5. 全量日志

general log会记录MySQL数据库所有操作的SQL语句，包含select和show。
默认情况下，禁用常规查询日志。

• general log相关参数

  general_log=0 
  #表示禁用general log
  
  general_log_file = file_name
  #表示general log的文件名，默认以hostname.log命名
  
  log_output
  表示输出格式，有三种方式：TABLE,FILE,NONE.默认是输出至FILE格式

   

• 查看general log

  mysql> set global general_log=1;
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> show databases;
  
  mysql> select count(*) from employees.employees;
  
  mysql> exit
  
  # tail -100f /data/mysql/mysql3306/data/mysqldb1.log
  mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
  Tcp port: 3306  Unix socket: /tmp/mysql3306.sock
  Time                 Id Command    Argument
  2018-08-27T12:31:28.156713Z        5 Query    show databases
  2018-08-27T12:31:44.258116Z        5 Query    select count(*) from employees.employees
  2018-08-27T12:31:47.236802Z        5 Quit

   

6. 审计日志

数据库审计能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行告警，对攻击行为进行阻断。

它通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户事后生成合规报告，事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。

MySQL数据库官方的收费组件需要购买企业版才可以使用审计功能。下面利用第三方开源审计插件完成审计工作。

1). 下载插件包

# wget https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip

 

2). 解压

# unzip download_file\?file_path\=audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip 

 

3). 复制插件至MySQL lib库目录下

# cd audit-plugin-mysql-5.7-1.1.6-784/

# ls
COPYING  lib  plugin-name.txt  README.txt  THIRDPARTY.txt  utils

# cd lib/
# ls
libaudit_plugin.so

# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/

 

4). 安装插件

mysql> install plugin audit soname 'libaudit_plugin.so';
ERROR 29 (HY000): File 'mysqld' not found (Errcode: 2 - No such file or directory)

 

5). 查看错误日志

#tail -100f error.log

2018-08-27T12:53:17.364957Z 8 [Note] McAfee Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
2018-08-27T12:53:17.364977Z 8 [ERROR] McAfee Audit Plugin: Failed file open: [mysqld], errno: 2. Retrying with /proc/2193/exe.
2018-08-27T12:53:20.765251Z 8 [Note] McAfee Audit Plugin: mysqld: mysqld (630d78960dfa79b5da11bfbec180899a) 
2018-08-27T12:53:20.765280Z 8 [Note] McAfee Audit Plugin: Couldn't find proper THD offsets for: 5.7.23-log
2018-08-27T12:53:20.765284Z 8 [ERROR] Plugin 'AUDIT' init function returned error.
2018-08-27T12:53:20.765287Z 8 [ERROR] Plugin 'AUDIT' registration as a AUDIT failed.

 

6). 设置偏移量

# cd audit-plugin-mysql-5.7-1.1.6-784/utils
# sh offset-extract.sh /usr/local/mysql/bin/mysqld
//offsets for: /usr/local/mysql/bin/mysqld (5.7.23)
{"5.7.23","630d78960dfa79b5da11bfbec180899a", 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13464, 148, 672},

编辑my3306.cnf文件，添加以下内容。重启mysql

# vi /etc/my3306.cnf

plugin-load             = AUDIT=libaudit_plugin.so
audit_json_file         = ON
audit_offsets           = 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13464, 148, 672
audit_json_log_file     = /data/mysql/mysql3306/mysql3306_audit.log

 

7). 插件已成功安装

root@localhost [(none)] 21:32:22> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
...
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

 

8). 查看审计日志内容

# cat mysql3306_audit.log 

{"msg-type":"header","date":"1535376674438","audit-version":"1.1.6-784","audit-protocol-version":"1.0","hostname":"mysqldb1","mysql-version":"5.7.23-log","mysql-program":"mysqld","mysql-socket":"/tmp/mysql3306.sock","mysql-port":"3306","server_pid":"9433"}
{"msg-type":"activity","date":"1535376716799","thread-id":"2","query-id":"0","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1535376716801","thread-id":"2","query-id":"2","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1535376716804","thread-id":"2","query-id":"3","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","rows":"1","status":"0","cmd":"select","query":"select USER()"}
{"msg-type":"activity","date":"1535376742218","thread-id":"2","query-id":"4","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","rows":"30","status":"0","cmd":"show_variables","objects":[{"db":"","obj_type":"TABLE"},{"db":"performance_schema","name":"session_variables","obj_type":"TABLE"}],"query":"show variables like '%audit%'"}
{"msg-type":"activity","date":"1535376757527","thread-id":"2","query-id":"5","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","rows":"45","status":"0","cmd":"show_plugins","objects":[{"db":"information_schema","name":"/data/mysql/mysql3306/tmp/#sql_24d9_0","obj_type":"TABLE"}],"query":"show plugins"}
{"msg-type":"activity","date":"1535376790256","thread-id":"2","query-id":"6","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"9532","_client_version":"5.7.23","_platform":"x86_64","program_name":"mysql"},"pid":"9532","os_user":"root","appname":"mysql","rows":"45","cmd":"Quit","query":"Quit"}

 

需要关心的参数 

• audit_json_file
  是否开启audit功能。

• audit_json_log_file
  记录文件的路径和名称信息（默认放在mysql数据目录下）。

• audit_record_cmds
  audit记录的命令，默认为记录所有命令。可以设置为任意dml、dcl、ddl的组合。如：audit_record_cmds=select,insert,delete,update。还可以在线设置set global audit_record_cmds=NULL。(表示记录所有命令)

• audit_record_objs
  audit记录操作的对象，默认为记录所有对象，可以用SET GLOBAL audit_record_objs=NULL设置为默认。也可以指定为下面的格式：audit_record_objs=,test.,mysql.,information_schema.*。

• audit_whitelist_users
  用户白名单。

• audit_json_file=on
  保证mysql重启后自动启动插件

• plugin-load=AUDIT=libaudit_plugin.so 
  防止删除了插件，重启后又会加载

7. 中继日志

主从复制中，slave服务器上一个很重要的文件。

复制的工作原理分为以下3个步骤：

• 1） 主服务器（master）把数据更改记录到二进制日志（binlog）中。

• 2） 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。

• 3） 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

8. Pid文件

当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。
该文件由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid

mysql> show variables like '%pid_file%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| pid_file      | mysqldb1.pid |
+---------------+--------------+
1 row in set (0.00 sec)

 

9. 套接字文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字（socket）文件。

由参数socket控制：

mysql> show variables like 'socket';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| socket        | /tmp/mysql3306.sock |
+---------------+---------------------+
1 row in set (0.00 sec)

 

10. 表结构文件

MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

frm可以存放视图的定义，存放视图定义的frm文件是文本文件，可以直接用cat查看，例如：

# cat GLOBAL_STATUS.frm   
TYPE=VIEW
query=select `information_schema`.`GLOBAL_STATUS`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`information_schema`.`GLOBAL_STATUS`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `INFORMATION_SCHEMA`.`GLOBAL_STATUS`
md5=dac896d268861732d0c40425f1e66cc6
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=0
with_check_option=0
timestamp=2018-08-20 06:29:43
create-version=1
source=SELECT * FROM `INFORMATION_SCHEMA`.`GLOBAL_STATUS`
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `information_schema`.`GLOBAL_STATUS`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`information_schema`.`GLOBAL_STATUS`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `INFORMATION_SCHEMA`.`GLOBAL_STATUS`

 

11. InnoDB存储引擎文件

分为redo log文件，undo log文件，表空间文件（ibd文件）

详见博文InnoDB表空间，MySQL redo log 与 binlog 的区别

转载于:https://www.cnblogs.com/wanbin/p/9546638.html