mysql主从复制中的从库突然出现了警报，sql_thread停止了，show slave status\G;查看
mysql> show slave status\G ; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: IPAddr Master_User: wxapi_slave3309 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000220 Read_Master_Log_Pos: 545197618 Relay_Log_File: mysql3309-relay-bin.000266 Relay_Log_Pos: 539938558 Relay_Master_Log_File: mysql-bin.000220 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 126 Last_Error: Error 'Incorrect key file for table './ota2scan/wx_order_flow.MYI'; try to repair it' on query. Default database: 'ota2scan'. Query: 'insert into wx_order_flow (id, chain_id, room_type_id, room_count, source_type_id, arr_date, dept_date, latest_time, result_code, result_msg, order_code, meb_id, meb_type, openId, meb_mobile, contact_name, contact_phone, revPoint, assure_type, activity_code, remark, seller_id, treasure_id, payType, day_length, room_source_type, guests, total_amount, actual_amount, create_time, sn, ip, couponId, usePoint, useCash, scanType, giftInfo ) values ('0000181785432-1511410392121', '2501', '220', 1, 123, '2017-11-23', '2017-11-24', '2017-11-23', null, null, null, xxxxxxxxxxx, 5, 'oDMTPjnkDTILoxKYOuXRf9nCl-NQ', 'xxxxxxxxxxx', 'xxx', 'xxxxxxxxxxx', null, 0, '', '联系人：xxx,联系方式：xxxxxxxxxxx', 272252, null, 0, 1, '7days', nul Skip_Counter: 0 Exec_Master_Log_Pos: 539938399 Relay_Log_Space: 545197993 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 126 Last_SQL_Error: Error 'Incorrect key file for table './ota2scan/wx_order_flow.MYI'; try to repair it' on query. Default database: 'ota2scan'. Query: 'insert into wx_order_flow (id, chain_id, room_type_id, room_count, source_type_id, arr_date, dept_date, latest_time, result_code, result_msg, order_code, meb_id, meb_type, openId, meb_mobile, contact_name, contact_phone, revPoint, assure_type, activity_code, remark, seller_id, treasure_id, payType, day_length, room_source_type, guests, total_amount, actual_amount, create_time, sn, ip, couponId, usePoint, useCash, scanType, giftInfo ) values ('0000181785432-1511410392121', '2501', '220', 1, 123, '2017-11-23', '2017-11-24', '2017-11-23', null, null, null, 181785432, 5, 'oDMTPjnkDTILoxKYOuXRf9nCl-NQ', 'xxxxxxxxxxx', 'xxx', 'xxxxxxxxxxx', null, 0, '', '联系人：xxx,联系方式：xxxxxxxxxxx', 272252, null, 0, 1, '7days', nul Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: Master_Info_File: /data/mysql/3309/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 171123 12:26:15 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified
发现原来线上还有在使用myisam引擎的业务表，从报错信息来看，是表损坏了，myisam的表损坏，可以先使用myiasmchk进行检测，然后再使用repair table进行修复
repair的相关参数如下：
[root@ bin]# myisamchk --help myisamchk Ver 2.7 for Linux at x86_64 By Monty, for your professional use This software comes with NO WARRANTY: see the PUBLIC for details. Description, check and repair of MyISAM tables. Used without options all tables on the command will be checked for errors Usage: myisamchk [OPTIONS] tables[.MYI] Global options: -H, --HELP Display this help and exit. -?, --help Display this help and exit. -t, --tmpdir=path Path for temporary files. Multiple paths can be specified, separated by colon (:), they will be used in a round-robin fashion. -s, --silent Only print errors. One can use two -s to make myisamchk very silent. -v, --verbose Print more information. This can be used with --description and --check. Use many -v for more verbosity. -V, --version Print version and exit. -w, --wait Wait if table is locked. Check options (check is the default action for myisamchk): -c, --check Check table for errors. -e, --extend-check Check the table VERY throughly. Only use this in extreme cases as myisamchk should normally be able to find out if the table is ok even without this switch. -F, --fast Check only tables that haven't been closed properly. -C, --check-only-changed Check only tables that have changed since last check. -f, --force Restart with '-r' if there are any errors in the table. States will be updated as with '--update-state'. -i, --information Print statistics information about table that is checked. -m, --medium-check Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases. -U --update-state Mark tables as crashed if you find any errors. -T, --read-only Don't mark table as checked. Repair options (When using '-r' or '-o'): -B, --backup Make a backup of the .MYD file as 'filename-time.BAK'. --correct-checksum Correct checksum information for table. -D, --data-file-length=# Max length of data file (when recreating data file when it's full). -e, --extend-check Try to recover every possible row from the data file Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate. -f, --force Overwrite old temporary files. -k, --keys-used=# Tell MyISAM to update only some specific keys. # is a bit mask of which keys to use. This can be used to get faster inserts. --max-record-length=# Skip rows bigger than this if myisamchk can't allocate memory to hold it. -r, --recover Can fix almost anything except unique keys that aren't unique. -n, --sort-recover Forces recovering with sorting even if the temporary file would be very big. -p, --parallel-recover Uses the same technique as '-r' and '-n', but creates all the keys in parallel, in different threads. -o, --safe-recover Uses old recovery method; Slower than '-r' but can handle a couple of cases where '-r' reports that it can't fix the data file. --character-sets-dir=... Directory where character sets are. --set-collation=name Change the collation used by the index. -q, --quick Faster repair by not modifying the data file. One can give a second '-q' to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is currupted can't be fixed with this option. -u, --unpack Unpack file packed with myisampack. Other actions: -a, --analyze Analyze distribution of keys. Will make some joins in MySQL faster. You can check the calculated distribution by using '--description --verbose table_name'. --stats_method=name Specifies how index statistics collection code should treat NULLs. Possible values of name are "nulls_unequal" (default for 4.1/5.0), "nulls_equal" (emulate 4.0), and "nulls_ignored". -d, --description Prints some information about table. -A, --set-auto-increment[=value] Force auto_increment to start at this or higher value If no value is given, then sets the next auto_increment value to the highest used value for the auto key + 1. -S, --sort-index Sort index blocks. This speeds up 'read-next' in applications. -R, --sort-records=# Sort records according to an index. This makes your data much more localized and may speed up things (It may be VERY slow to do a sort the first time!). -b, --block-search=# Find a record, a block at given offset belongs to. Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf The following groups are read: myisamchk The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file, except for login file. --defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read. --defaults-group-suffix=# Also read groups with concat(group, suffix) --login-path=# Read this path from the login file. Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- character-sets-dir (No default value) data-file-length 0 keys-used 18446744073709551615 max-record-length 9223372036854775807 set-auto-increment 0 set-collation (No default value) sort-records 0 tmpdir (No default value) key-buffer-size 520192 key-cache-block-size 1024 myisam-block-size 1024 read-buffer-size 262136 write-buffer-size 262136 sort-buffer-size 2097144 myisam-sort-buffer-size 2097144 sort-key-blocks 16 decode-bits 9 ft-min-word-len 4 ft-max-word-len 84 ft-stopword-file (No default value) stats-method nulls_unequal
修复的步骤如下：
1、使用myisam带上参数-of，进行修复
[root@ bin]# myisamchk -of /data/mysql/3309/ota2scan/wx_order_flow.MYI - recovering (with keycache) MyISAM-table '/data/mysql/3309/ota2scan/wx_order_flow.MYI' Data records: 18878816
2、登录mysql，对表进行repair table
mysql> repair table wx_order_flow; +------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------+--------+----------+----------+ | ota2scan.wx_order_flow | repair | status | OK | +------------------------+--------+----------+----------+ 1 row in set (9 min 56.46 sec)
至此，表已经修复完成。
若是在执行完第一步之后，还是提示“Error 'Incorrect key file for table”的错误，可以在第一步之后再执行以下步骤
[root@ bin]# myisamchk -r /data/mysql/3309/ota2scan/wx_order_flow.MYI - recovering (with sort) MyISAM-table '/data/mysql/3309/ota2scan/wx_order_flow.MYI' Data records: 18878816 - Fixing index 1 [root@ bin]# myisamchk /data/mysql/3309/ota2scan/wx_order_flow.MYI
再执行一次repair table，此时，应该就能修复myisam引擎表，不会再出现“Error 'Incorrect key file for table”的错误
重新启动sql_thread进程
mysql> start slave sql_thread ; Query OK, 0 rows affected (0.01 sec)
检查复制
mysql> show slave status\G ; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: IPAddr Master_User: wxapi_slave3309 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000220 Read_Master_Log_Pos: 567443411 Relay_Log_File: mysql3309-relay-bin.000266 Relay_Log_Pos: 567443570 Relay_Master_Log_File: mysql-bin.000220 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 567443411 Relay_Log_Space: 567443786 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: Master_Info_File: /data/mysql/3309/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
已经恢复正常。
以上，如有错谬，请不吝指正。
5189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
