mysqlcheck mysql检查和修复

Mysqlcheck
Mysqlcheck在mysqld运行时使用,其内嵌check/repair/analyze/optimize table命令;
在执行时相应表被锁定,analyze/check table使用读锁,其它均为写锁;
同myisamchk功能近似,但是后者最好在mysqld关闭时运行;
1. Check a Specific Table in a Database
If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.
The following example checks employee table in thegeekstuff database.
# mysqlcheck -c thegeekstuff employee -u root -p
2. Check All Tables in a Database
To check all the tables in a particular database, don’t specify the table name. Just specify the database name.
The following example checks all the tables in the alfresco database.
shell> mysqlcheck -c alfresco -u root -p
3. Check All Tables and All Databases
To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.
# mysqlcheck -c -u root -p –all-databases
If you want to check all tables of few databases, specify the database names using “–databases”.
The following example checks all the tables in thegeekstuff and alfresco database.
# mysqlcheck -c -u root -p –databases thegeekstuff alfresco
Repair Tables using Mysqlcheck
The following repairs employee table that is located in thegeekstuff database.
# mysqlcheck -r thegeekstuff employee -u root -p
Additional Useful Mysqlcheck Options
The following are some of the key options that you can use along with mysqlcheck.
-A, –all-databases Consider all the databases
-a, –analyze Analyze tables
-1, –all-in-1 Use one query per database with tables listed in a comma separated way
–auto-repair Repair the table automatically it if is corrupted
-c, –check Check table errors
-C, –check-only-changed Check tables that are changed since last check
-g, –check-upgrade Check for version dependent changes in the tables
-B, –databases Check more than one databases
-F, –fast Check tables that are not closed properly
–fix-db-names Fix DB names
–fix-table-names Fix table names
-f, –force Continue even when there is an error
-e, –extended Perform extended check on a table. This will take a long time to execute.
-m, –medium-check Faster than extended check option, but does most checks
-o, –optimize Optimize tables
-q, –quick Faster than medium check option
-r, –repair Fix the table corruption
可以在表和数据库级别执行
Mysqlcheck db_name [tbl_name …]
Mysqlcheck –databases db_name
Mysqlcheck –all-databases
选项
–analyze:执行analyze table
–auto-repair:如果表损坏则自动修复
–check:执行check table
–check-only-changed:只对上次操作以来变化过的表执行check table
–check-upgrade:执行check table for upgrade
–compress:压缩服务器和客户端之间的所有通信信息
–extended:检查并修复表;如果用于检查表,会确保100%一致但耗时较长;如果用于修复表,可能会产生垃圾数据;
–fast:只检查没有正常关闭的表
–medium-check:比extended选项稍快,可检查99.99%的错误
–optimize:执行optimize table
–quick:只修复索引树,是最快的方法
–repair:除了unique key包含重复数据,可以修复其它大部分错误
–use-frm:修复MyISAM表
–write-binlog:将analyze/optimize/repair语句记录进binlog
Analyze table
要求对候选表有select和insert权限;
可用于分区表,alter table … analyze partition
用于分析表中key键的分布情况,即收集统计信息;
如果自上次analyze以来表没有改动,则analyze table并不会被实际执行;
如果不想记录binlog,可使用NO_WRITE_TO_BINLOG或LOCAL
Check table
可用于MyISAM/InnoDB,该命令有一系列选项,包括
For upgrade/quick/fast/medium/extened/changed
对于MyISMA默认为medium,等同于Myisamchk –m tbl_name
表损坏经常来自索引,故所有的检查级别都会完整的检查索引;
可用于分区表,alter table … check partition
恢复例子
第一,常用的 check 命令

1
2
3
4
5
mysqlcheck -c -A -uroot -ppassword
mysqlcheck -c  -uroot -ppassword --all-databases
mysqlcheck -c --database dbname -uroot -p
mysqlcheck -c  -u root -ppassword --all-databases
mysqlcheck -r table # myisam表才可以

第二 恢复
1.首先 导入当天的冷备份 那就回到当然冷备的时候 一般是凌晨3点什么的
2. 查看系统当机时间   –start-datetime 冷备开始时间 ; –stop-datetime 系统当机时间 , mysql-bin.000201 binlog文件 这个也要看对应时间

1
 mysqlbinlog  -d games_s25 --start-datetime="2016-07-25 02:05:00"    --stop-datetime="2016-07-25 04:40:00"  "mysql-bin.000201"   | mysql -uroot -ppassword

from http://www.thegeekstuff.com/2011/12/mysqlcheck/
利用mysqlcheck命令快速修复mysql数据库
http://blog.csdn.net/adparking/article/details/6388917
mysqlcheck和myisamchk
http://blog.itpub.net/15480802/viewspace-757890
mysqlbinlog 恢复数据的四种方法