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

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
 
Thanks pppplus. I'll check the last command because I have a problem with one of the tables.
 
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:
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.
 
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.
 
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 ??
 
It's debian.... you should have a symlink from /usr/local/mariadb-10.0.35-linux-x86_64/ to /usr/local/mysql/
 
thanks! Work with
/usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
 
Firstly copy all of the files from all your databases to a directory name based on the current time and to keep the data more protected you can back up the database files to a remote location instead of the server and to do this

Type the following command:

cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

After backing up your databases you can run the following command:

mysqlcheck -u root -p -A --auto-repair
Where mysqlcheck is the command to check the database, root stands for root user, prompt for a password, check all databases, and auto-repair any corrupted tables.
Or Else you can use 3 different commands for these:

mysqlcheck --all-databases -a (Analyse)
mysqlcheck --all-databases -r (Repair)
mysqlcheck --all-databases -o (Optimize)


In some cases after running these commands if you still aren’t able to repair the database then you can make use of a tool named Stellar Repair for MySQL that can help you repair corrupt MySQL database and safely restore all inaccessible database objects including primary keys, views, triggers, tables, etc. in the original format. Using this tool you can easily repair InnoDB and MyISAM tables of MySQL database.

Note: Always try repair your database through manual method and use the tool only incase if you are short of time or want to see a preview of recoverable database objects.
 
Back
Top