mysql sql 检测磁盘_【MySQL】一条SQL使磁盘暴涨并导致MySQL Crash

[ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file $datadir/ibtmp1, desired size 67108864 bytes. Operating system error number 28.Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

[ERROR] Disk is full writing '$datadir/mysql-bin.000004' (Errcode: 15870576 - No space left on device). Waiting for someone to free space...

[ERROR] Retry in 60 secs. Message reprinted in 600 secs

[Warning] InnoDB: 1048576 bytes should have been written. Only 647168 bytes written. Retrying for the remaining bytes.

[Warning] InnoDB: Retry attempts for writing partial data failed.

[ERROR] InnoDB: Write to file$datadir/ibtmp1 failed at offset 201911697408, 1048576 bytes should have been written, only 647168 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.

[ERROR] InnoDB: Error number 28 means 'No space left on device'

[Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html

[Warning] InnoDB:Error while writing 67108864 zeroes to $datadir/ibtmp1 starting at offset 201876045824

[ERROR] $basedir/bin/mysqld: The table'$tmpdir/#sql_37c5_0' is full

[ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./thread_quartz/QRTZ_FIRED_TRIGGERS.ibd, desired size 32768 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

[Warning] InnoDB: Retry attempts for writing partial data failed.

[Warning] InnoDB: Error while writing 32768 zeroes to ./thread_quartz/QRTZ_FIRED_TRIGGERS.ibd starting at offset 442362017-07-06T11:49:21.893377Z mysqld_safe Number of processes running now: 0

mysqld_safe mysqld restarted

……………………………………………………………………………………………………………………

[Note] InnoDB: Last MySQL binlog file position 0 690908428, file name mysql-bin.000004

[Note] InnoDB: Starting in background the rollback of uncommitted transactions

[Note] InnoDB: Rollback of non-prepared transactions completed

[Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

[Note] InnoDB: Creating shared tablespace for temporary tables

[Note] InnoDB: Setting file '$datadir/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

[Note] InnoDB: File '$datadir/ibtmp1' size is now 12 MB.

[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

[Note] InnoDB: 32 non-redo rollback segment(s) are active.

[Note] InnoDB: Waiting for purge to start

[Note] InnoDB: 5.7.12 started; log sequence number 4828513952

[Note] InnoDB: page_cleaner: 1000ms intended loop took 7748ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

[Note] InnoDB: Loading buffer pool(s) from $datadir/ib_buffer_pool

[Note] Plugin 'FEDERATED' is disabled.

[Note] InnoDB: Buffer pool(s) load completed at 170706 19:49:30

[Note] Recovering after a crash using $basedir/mysql-bin

[ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 579, event_type: 2

[Note] Starting crash recovery...

[Note] InnoDB: Starting recovery for XA transactions...

[Note] InnoDB: Transaction 6729603 in prepared state after recovery

[Note] InnoDB: Transaction contains changes to 1 rows

[Note] InnoDB: 1 transactions in prepared state after recovery

[Note] Found 1 prepared transaction(s) in InnoDB

[Note] Crash recovery finished.

[Note] Crashed binlog file $basedir/mysql-bin.000004 size is 690909184, but recovered up to 690908428. Binlog trimmed to 690908428 bytes.

[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

……………………………………………………………………………………………………………………

[ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 835, event_type: 2

[Warning] Error reading GTIDs from relaylog: -1

[Note] Slave I/O thread: Start asynchronous replication to master '*****' in log 'mysql-bin.000014' at position 286486095

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

[Note] Slave I/O thread for channel '': connected to master '*****',replication started in log 'mysql-bin.000014' at position 286486095

[Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

[Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000014' at position 286485153, relay log '$datadir/mysql-relay.000013' position: 286485326

[ERROR]Error in Log_event::read_log_event(): 'read error', data_len: 835, event_type: 2

[ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error

[ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594

[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000014' position 286485153

[Note] Event Scheduler: Loaded 0 events

[Note] $basedir/mysqld: ready for connections.

Version: '5.7.12' socket: '$datadir/mysqld.sock' port: 3306 Source distribution