Mysql automatic update on Linux servers

Question

We are running a couple of shared host linux servers and a private one. We are running cPanel/WHM licenses under them.

The predicament I am facing is :

How can I run the following command automatically ?

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

The problem is that I am prompted for the root password, but I would like to run this command automatically and the password prompt intrerupts this process.

Please help me.

Answer 1

Store your root credentials in a file that only the root user can access. Then simply pass the credentials filename.

mysqlcheck --defaults-file=/etc/mysql/root.cnf  --auto-repair --check --optimize --all-databases

/etc/mysql/root.cnf

[client]
host     = servername.domain.tld
user     = backup
password = strongpassword

This doesn't create a huge security vulnerability so long as /etc/mysql/root.cnf is only readable by root since the root user could easily reset the mysql passwords without needing any mysql credentials at all.

Answer 2

You could put the required password either in the [client] section of your ~/.my.cnf or an additional config file you specify with --defaults-extra-file=path/to/yourconfig.cnf

For more details, please refer to the option-files segment of the MySQL documentation.

Answer 3

You could just supply the password on the commandline. Of course this has some privacy/security issues.

mysqlcheck -uroot -pPASSWORD --auto-repair --check --optimize --all-databases