Page 2 of 2 FirstFirst 12
Results 21 to 31 of 31

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

  1. #21
    Join Date
    Dec 2008
    Posts
    512
    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
    doesn't work for me
    error = mysqlcheck doesn't support multiple contradicting command

    Code:
    mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair  --optimize --all-databases
    this one works (without --check)



    I want to --auto-repair only some tables (they crash regularly, probably because a lot of datas are send every morning with LOAD DATA)

    So for only one table :
    Code:
    mysqlcheck -uXXX -pYYY --auto-repair  --optimize -B MY_DATABASE --tables MY_TABLE

  2. #22
    Join Date
    May 2014
    Location
    Poland
    Posts
    9
    Thanks pppplus. I'll check the last command because I have a problem with one of the tables.

  3. #23
    Join Date
    May 2004
    Location
    Spain
    Posts
    207
    If we create '.my.cnf' file in the user's home directory with the following data:

    Code:
    [client]
    user=da_admin
    password=secretpass
    No need to specify the username and password on the command line of most mysql applications (mysqladmin, mysqlcheck, ...).

    As a minimum security measure, you may wish to change the file permissions to '600' (chmod 600 .my.cnf) and make sure the user and group are correct.

    Besides the above, as you know, you can also use abbreviated commands to execute 'mysqlcheck' as:
    Code:
    mysqlcheck --auto-repair -o -A
    # more abbreviated
    mysqlcheck -Ao --auto-repair
    # -o = --optimize
    # -A = --all-databases
    # --auto-repair don't have abbreviated command
    Another useful command is '--silent' or '-s', Silent mode. Print only error messages.

    I wish I could be of use to someone
    Last edited by redesb; 11-21-2014 at 10:32 AM. Reason: More info

  4. #24
    Join Date
    Jul 2008
    Location
    Maastricht
    Posts
    3,464
    If we create '.my.cnf' file in the user's home directory with the following data:
    I would never put da_admin's mysql pass in a user's home directory in whatever file.

    You can do this, but you have to put this my.cnf in /user/local/directadmin/conf and not in the user's home directory.
    Greetings, Richard.

  5. #25
    Join Date
    May 2004
    Location
    Spain
    Posts
    207
    Sorry Richard, it's my fault for not having explained it better. In fact when he spoke of creating a file in the 'home' directory of the user, I meant my '/root' directory.

  6. #26
    Join Date
    Jun 2017
    Location
    ZS
    Posts
    63
    Hi I nedd create cron job to opt mysql database, when I type mysqlcheck command I have error that
    -bash: mysqlcheck: command not found
    Working with Deb 8/DA/CB2/MariaDB10.0/PHP7 | https://szukampracy.pl & https://ksservice.pl & https://pramed.pl

  7. #27
    Join Date
    Oct 2004
    Location
    London, UK
    Posts
    6,749
    You can use:
    whereis mysqlcheck
    To find the the executable is and than use the full path inside the script.
    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. #28
    Join Date
    Jun 2017
    Location
    ZS
    Posts
    63
    whereis
    /# whereis mysqlcheck
    mysqlcheck:
    and locate
    /usr/local/mariadb-10.0.35-linux-x86_64/bin/mysqlcheck
    /usr/local/mariadb-10.0.35-linux-x86_64/man/man1/mysqlcheck.1
    /usr/local/mariadb-10.0.35-linux-x86_64/mysql-test/r/mysqlcheck.result
    /usr/local/mariadb-10.0.35-linux-x86_64/mysql-test/t/mysqlcheck.test
    /usr/local/mariadb-5.5.46-linux-x86_64/bin/mysqlcheck
    /usr/local/mariadb-5.5.46-linux-x86_64/man/man1/mysqlcheck.1
    /usr/local/mariadb-5.5.46-linux-x86_64/mysql-test/r/mysqlcheck.result
    /usr/local/mariadb-5.5.46-linux-x86_64/mysql-test/t/mysqlcheck.test
    should use this /usr/local/mariadb-10.0.35-linux-x86_64/bin/mysqlcheck ??
    Working with Deb 8/DA/CB2/MariaDB10.0/PHP7 | https://szukampracy.pl & https://ksservice.pl & https://pramed.pl

  9. #29
    Join Date
    Oct 2004
    Location
    London, UK
    Posts
    6,749
    mmmh that's strange, I would give it a try to actually reinstall MariaDB
    SeLLeRoNe - Andrea Iannucci
    Head of Managed Service - Senior DevOps Engineer
    If you need my support write me an E-Mail to Support@CrazyNetwork.it

  10. #30
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,208
    It's debian.... you should have a symlink from /usr/local/mariadb-10.0.35-linux-x86_64/ to /usr/local/mysql/

  11. #31
    Join Date
    Jun 2017
    Location
    ZS
    Posts
    63
    thanks! Work with
    /usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
    Working with Deb 8/DA/CB2/MariaDB10.0/PHP7 | https://szukampracy.pl & https://ksservice.pl & https://pramed.pl

Page 2 of 2 FirstFirst 12

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
  •