7.11 mysqlcheck — テーブル メンテナンスと修復プログラム
http://dev.mysql.com/doc/refman/5.1/ja/mysqlcheck.html
mysqlcheckクライアントはテーブルのチェック、修復、最適化、そして分析を行います。
mysqlcheckのファンクションはmyisamchkと似ていますが、作動方法が異なります。実質的な作動方法の違いは、mysqlcheckはmysqldサーバが作動中の時に使用されなければいけません。myisamchkはこのサーバが作動していない時に使用されなければいけません。mysqlcheckを使用することの利点は、テーブルのチェックや修復時にサーバを停止させなくてすむことです。
mysqlcheckはmysqldが起動している状態でチェックできるので、cronでoptimizeとかができて良さそうです
mysqlcheck with cron to optimize automatically
http://rtcamp.com/wordpress-nginx/tutorials/mysql/check-repair-automatically/
- データベースのチェック
- データベースの修復
- データベースの最適化
データベースの修復が必要そうです
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
; html-script: false ][root@host1 ~]# mysqlcheck -u root -p --check --all-databases Enter password: wordpress.wp_commentmeta error : Table upgrade required. Please do "REPAIR TABLE `wp_commentmeta`" or dump/reload to fix it! wordpress.wp_comments error : Table upgrade required. Please do "REPAIR TABLE `wp_comments`" or dump/reload to fix it! wordpress.wp_links error : Table upgrade required. Please do "REPAIR TABLE `wp_links`" or dump/reload to fix it! wordpress.wp_ngg_album OK wordpress.wp_ngg_gallery OK wordpress.wp_ngg_pictures OK wordpress.wp_options error : Table upgrade required. Please do "REPAIR TABLE `wp_options`" or dump/reload to fix it! wordpress.wp_postmeta error : Table upgrade required. Please do "REPAIR TABLE `wp_postmeta`" or dump/reload to fix it! wordpress.wp_posts error : Table upgrade required. Please do "REPAIR TABLE `wp_posts`" or dump/reload to fix it! wordpress.wp_term_relationships OK wordpress.wp_term_taxonomy error : Table upgrade required. Please do "REPAIR TABLE `wp_term_taxonomy`" or dump/reload to fix it! wordpress.wp_terms error : Table upgrade required. Please do "REPAIR TABLE `wp_terms`" or dump/reload to fix it! wordpress.wp_usermeta error : Table upgrade required. Please do "REPAIR TABLE `wp_usermeta`" or dump/reload to fix it! wordpress.wp_users error : Table upgrade required. Please do "REPAIR TABLE `wp_users`" or dump/reload to fix it! wordpress.wp_wassup error : Table upgrade required. Please do "REPAIR TABLE `wp_wassup`" or dump/reload to fix it! wordpress.wp_wassup_tmp error : Table upgrade required. Please do "REPAIR TABLE `wp_wassup_tmp`" or dump/reload to fix it! wordpress.wp_weatherjournal OK |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
; html-script: false ][root@host1 ~]# mysqlcheck -u root -p --auto-repair --all-databases Enter password: wordpress.wp_commentmeta error : Table upgrade required. Please do "REPAIR TABLE `wp_commentmeta`" or dump/reload to fix it! wordpress.wp_comments error : Table upgrade required. Please do "REPAIR TABLE `wp_comments`" or dump/reload to fix it! wordpress.wp_links error : Table upgrade required. Please do "REPAIR TABLE `wp_links`" or dump/reload to fix it! wordpress.wp_ngg_album OK wordpress.wp_ngg_gallery OK wordpress.wp_ngg_pictures OK wordpress.wp_options error : Table upgrade required. Please do "REPAIR TABLE `wp_options`" or dump/reload to fix it! wordpress.wp_postmeta error : Table upgrade required. Please do "REPAIR TABLE `wp_postmeta`" or dump/reload to fix it! wordpress.wp_posts error : Table upgrade required. Please do "REPAIR TABLE `wp_posts`" or dump/reload to fix it! wordpress.wp_term_relationships OK wordpress.wp_term_taxonomy error : Table upgrade required. Please do "REPAIR TABLE `wp_term_taxonomy`" or dump/reload to fix it! wordpress.wp_terms error : Table upgrade required. Please do "REPAIR TABLE `wp_terms`" or dump/reload to fix it! wordpress.wp_usermeta error : Table upgrade required. Please do "REPAIR TABLE `wp_usermeta`" or dump/reload to fix it! wordpress.wp_users error : Table upgrade required. Please do "REPAIR TABLE `wp_users`" or dump/reload to fix it! wordpress.wp_wassup error : Table upgrade required. Please do "REPAIR TABLE `wp_wassup`" or dump/reload to fix it! wordpress.wp_wassup_tmp error : Table upgrade required. Please do "REPAIR TABLE `wp_wassup_tmp`" or dump/reload to fix it! wordpress.wp_weatherjournal OK Repairing tables wordpress.wp_commentmeta OK wordpress.wp_comments OK wordpress.wp_links OK wordpress.wp_options OK wordpress.wp_postmeta OK wordpress.wp_posts OK wordpress.wp_term_taxonomy OK wordpress.wp_terms OK wordpress.wp_usermeta OK wordpress.wp_users OK wordpress.wp_wassup OK wordpress.wp_wassup_tmp OK |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
; html-script: false ][root@host1 ~]# mysqlcheck -u root -p --optimize --all-databases Enter password: wordpress.wp_commentmeta OK wordpress.wp_comments OK wordpress.wp_links OK wordpress.wp_ngg_album Table is already up to date wordpress.wp_ngg_gallery Table is already up to date wordpress.wp_ngg_pictures Table is already up to date wordpress.wp_options OK wordpress.wp_postmeta OK wordpress.wp_posts OK wordpress.wp_term_relationships Table is already up to date wordpress.wp_term_taxonomy OK wordpress.wp_terms OK wordpress.wp_usermeta OK wordpress.wp_users OK wordpress.wp_wassup OK wordpress.wp_wassup_tmp OK wordpress.wp_weatherjournal Table is already up to date |
データベースエンジンがInnoDBの場合は–optimizeオプションは対応していなくて下記のメッセージが表示されます
dotproject.dotp_billingcode
note : Table does not support optimize, doing recreate + analyze instead
status : OK
これはoptimizeはサポートしていなけど、代わりにrecreate + analyze を行ったよってことで最適化はできているそうです