Page 1 of 2 12 LastLast
Results 1 to 20 of 31

Thread: MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases

  1. #1
    Join Date
    Feb 2007
    Posts
    486

    MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases

    Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

  2. #2
    Join Date
    Aug 2006
    Location
    LT, EU
    Posts
    7,396
    It's better to use the da_admin user with a provided password So, the command would be:
    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
    Martynas Bendorius
    MB Martynas IT. Professional server management company. Official DirectAdmin, CloudLinux, LiteSpeed and Comodo partners.

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    Hi,

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    When using this command, I get error 1045.

    What should I do.

    Thanks.

  4. #4
    Join Date
    Jan 2008
    Location
    /dev/null
    Posts
    702
    Quote Originally Posted by nulkarp View Post
    What should I do.
    Read the post from smtalk
    Why most people reboot the server when they have a problem

  5. #5
    Join Date
    Jun 2009
    Posts
    557
    Move this to the how-to section.

  6. #6
    Join Date
    Jun 2003
    Location
    California
    Posts
    26,122
    Good idea; moved.

    Jeff
    +1 951 643-5345
    Third-Party DirectAdmin administration and support
    Dedicated Servers, Dedicated Reseller Accounts
    NoBaloney Internet Services div. Qnito Incorporated
    848 North Rainbow Blvd., Suite #3789
    Las Vegas, NV 89107-1103

  7. #7
    Join Date
    Oct 2004
    Location
    London, UK
    Posts
    6,773
    very nice trick, thanks.
    SeLLeRoNe - Andrea Iannucci
    Head of Managed Service - Senior DevOps Engineer
    If you need my support write me an E-Mail to Support@CrazyNetwork.it

  8. #8
    Join Date
    Jul 2010
    Posts
    58

    Thumbs down

    what if we want to check database tables only for specific user only... rather than repairing each and every tables.... plz be advice...

  9. #9
    Join Date
    May 2008
    Posts
    788
    For MySQL 5.5 on FreeBSD you have to skip the --check option (giving an error message for contradicting commands?!?). So it should be:


    Code:
    /usr/local/mysql/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair=TRUE --optimize --all-databases-TRUE

  10. #10
    Join Date
    May 2008
    Posts
    788
    and it is "=TRUE" at the end, not "-TRUE":

    Code:
    /usr/local/mysql/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair=TRUE --optimize --all-databases=TRUE

  11. #11
    Join Date
    Apr 2009
    Posts
    2,230
    Quote Originally Posted by smtalk View Post
    It's better to use the da_admin user with a provided password So, the command would be:
    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
    I just used that command, and it seems to work fine. However I get error regarding performance_schema, is that normal? I am on MySQL 5.1.61 (about to upgrade to 5.5.x soon). Here is all the output regarding performance_schema error:

    Code:
    performance_schema.cond_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_current
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_history
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_history_long
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_by_instance
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_by_thread_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_global_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_summary_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_summary_by_instance
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.mutex_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.performance_timers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.rwlock_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_consumers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_instruments
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_timers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.threads
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    
    Repairing tables
    performance_schema.cond_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_current
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_history
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_history_long
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_by_instance
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_by_thread_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.events_waits_summary_global_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_summary_by_event_name
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.file_summary_by_instance
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.mutex_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.performance_timers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.rwlock_instances
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_consumers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_instruments
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.setup_timers
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt
    performance_schema.threads
    Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
    error    : Corrupt

  12. #12
    Join Date
    Apr 2009
    Posts
    2,230
    Quote Originally Posted by smtalk View Post
    It's better to use the da_admin user with a provided password So, the command would be:
    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
    I have now upgraded from mysql 5.1.x to mysql 5.5.x, and that commands does not work anymore, but give this error:

    Code:
    [root@server ~]# mysqlcheck -uda_admin -p`grep "^passwd=REMOVED" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
    Error:  mysqlcheck doesn't support multiple contradicting commands.

  13. #13
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,857
    Then try to use

    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --optimize --all-databases
    or

    Code:
    mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
    as more secure, as no password will be shown in process lists. Of course you should have /usr/local/directadmin/conf/my.cnf on your box.
    Alex Grebenschikov

    - You can hire me on www.poralix.com to work on your server
    - Follow and like @Poralix on Facebook

  14. #14
    Join Date
    Apr 2009
    Posts
    2,230
    Thank you, Alex! I did your second option, and it works great on MySQL 5.5.x

  15. #15
    Join Date
    Jan 2011
    Posts
    110
    Is it risky to use this command?

  16. #16
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,857
    SSHing into the server is much more risky than running this command.... unless you've got enough knowledges.
    Alex Grebenschikov

    - You can hire me on www.poralix.com to work on your server
    - Follow and like @Poralix on Facebook

  17. #17
    Join Date
    Oct 2007
    Location
    Utrecht, The Netherlands
    Posts
    43
    Is it a good idea to cron this, so all databases on the server kept beeing optimized? Or are there risks involved that might cause a corrupted database?

  18. #18
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,857
    I use it in daily cron on some servers, and I've never found an issue with that. So I'd rather say the idea is good.
    Alex Grebenschikov

    - You can hire me on www.poralix.com to work on your server
    - Follow and like @Poralix on Facebook

  19. #19
    Join Date
    Apr 2014
    Posts
    1

    How to first use MySQLcheck

    I need help

    I cannot get MySQLcheck to work on my Linux server

    I do not use SSH often

    but I logged in ,

    once I had a prompted in the root

    I tried many of the suggested command lines for MySQLcheck, and nothing happened

    Once....I got a command to do something, but I got a 1045 error, I think


    it asked for a password!

    what password?

    I had already used the password to access my server
    and each of my databases has its own password
    so what password were they asking for? (I was using a command to repair and optimize all databases)

    -----------

    so I have no clue how to do this

    I want to first check databases before repairing them

    I only want to repair a single database at a time if needed

    and are these mySQLcheck commands run from the root of my server?

    thanks

    Dennis

  20. #20
    Join Date
    Jun 2003
    Location
    California
    Posts
    26,122
    This is the DirectAdmin forum, for users of the DirectAdmin hosting control panel. Are you using a DirectAdmin based server? If so, you can use the master MySQL password for da_admin. You can find it in /usr/local/directadmin/conf/mysql.conf

    Even if you're not running in DirectAdmin this article in our Knowledgebase may be able to help you: http://help.directadmin.com/item.php?id=45

    But be careful.

    Jeff
    +1 951 643-5345
    Third-Party DirectAdmin administration and support
    Dedicated Servers, Dedicated Reseller Accounts
    NoBaloney Internet Services div. Qnito Incorporated
    848 North Rainbow Blvd., Suite #3789
    Las Vegas, NV 89107-1103

Page 1 of 2 12 LastLast

Similar Threads

  1. Auto repair tables every x minutes
    By viciovirtual in forum CentOS
    Replies: 1
    Last Post: 11-11-2009, 06:00 AM
  2. Replies: 2
    Last Post: 09-08-2008, 10:07 AM
  3. Suggestion: MySQL Database Repair and Optimization
    By EGS in forum Feedback & Feature Requests
    Replies: 9
    Last Post: 05-27-2008, 11:09 AM
  4. Replies: 1
    Last Post: 03-27-2008, 04:22 PM
  5. mysql wont start, after following mysql optimize in this forum
    By lkbryant in forum General Technical Discussion & Troubleshooting
    Replies: 3
    Last Post: 01-29-2006, 05:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •