CODEWITHSUNDEEP
phpdebiancron
John Magnolia
John Magnolia

Reputation: 16803

Cronjob that optimizes and cleans all mysql databases

I am trying to set up a cron job that everyday optmizes the mysql database using debian on a virtual machine.

First I have tried using the mysqlcheck like this:

0 * * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null

I got this error: mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

after googling this error I found Dave Stoddard Comment who was experiencing the same problem:

0 * * * * /usr/local/bin/mysqlcheck --defaults-file=/root/.my.cnf --all-databases --auto-repair 2>&1 | grep -v "OK" | mail -s "Database Problem" root

or 0 * * * * /usr/local/bin/mysqlcheck --defaults-file=/root/.my.cnf --all-databases --auto-repair 2>&1 | grep -v "OK" | mail -s "Database Problem" root

Error from above:

/bin/sh: root: command not found
/bin/sh: /usr/local/bin/mysqlcheck: No such file or directory

Upvotes: 1

Views: 2620

Answers (2)

ajreal
ajreal

Reputation: 47321

seriously, you might over-doing,
your approach will causing some unnecessary table lock,
which mean it easily freezing your application that required database access

the optimization is required only when the table is too fragmented meaning contains free-space,
an optimization able to impact the index key and data file

what is free-space?

this query will return list of table need to be optimized

select TABLE_SCHEMA, TABLE_NAME
from information_schema.tables 
where data_free>0;

build using a simple bash script

chk="select concat(TABLE_SCHEMA,'.', TABLE_NAME) from information_schema.tables where data_free>0;"

for tbl in $(mysql -u root -pxxx -N <<< $chk)
do
  mysql -u xxx -pxxx -N <<< "optimize table $tbl"
done

Upvotes: 5

Marc B
Marc B

Reputation: 360782

The two errors are pretty clear... The first one says that root@localhost is not permitted access to the database - either you're using the wrong password, or the root account in MySQL (which is usually present by default) has been removed.

The mail error is typical of using mail on the different systems - standard GNU mail (from GNU mailutils 1.2) has a -E option to execute a command, which your mail command is lacking. Check man mail to see if the option has a different name on your system.

Upvotes: 0

Related Questions