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

[pppplus]

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

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 :

mysqlcheck -uXXX -pYYY --auto-repair  --optimize -B MY_DATABASE --tables MY_TABLE

 

[mkred]

Thanks pppplus. I'll check the last command because I have a problem with one of the tables.

 

[redesb]

If we create '.my.cnf' file in the user's home directory with the following data:

[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:

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

 

[Richard G]

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]

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]

Hi I nedd create cron job to opt mysql database, when I type mysqlcheck command I have error that

-bash: mysqlcheck: command not found

 

[SeLLeRoNe]

You can use:
whereis mysqlcheck
To find the the executable is and than use the full path inside the script.

 

[Mattpl]

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 ??

 

[SeLLeRoNe]

mmmh that's strange, I would give it a try to actually reinstall MariaDB

 

[zEitEr]

It's debian.... you should have a symlink from /usr/local/mariadb-10.0.35-linux-x86_64/ to /usr/local/mysql/

 

[Mattpl]

thanks! Work with

/usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases

 

[shanayaS]

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.