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

pppplus

Verified User
Joined
Dec 19, 2008
Messages
518
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
 

mkred

Verified User
Joined
May 19, 2014
Messages
9
Location
Poland
Thanks pppplus. I'll check the last command because I have a problem with one of the tables.
 

redesb

Verified User
Joined
May 10, 2004
Messages
207
Location
Spain
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:

Richard G

Verified User
Joined
Jul 6, 2008
Messages
4,218
Location
Maastricht
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.
 

redesb

Verified User
Joined
May 10, 2004
Messages
207
Location
Spain
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.
 

Mattpl

Verified User
Joined
Jun 28, 2017
Messages
78
Location
ZS
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 ??
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
13,854
Location
GMT +7.00
It's debian.... you should have a symlink from /usr/local/mariadb-10.0.35-linux-x86_64/ to /usr/local/mysql/
 

Mattpl

Verified User
Joined
Jun 28, 2017
Messages
78
Location
ZS
thanks! Work with
/usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
 

shanayaS

New member
Joined
Nov 21, 2019
Messages
1
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.
 
Top