Optmize Database Automatically Using Cron jobs for Unix Users

Optmize Database Automatically Using Cron jobs for Unix Users

by D3SI Boy -
Number of replies: 3

Hi Everyone

This is my First Post here

Found this on Google thought might be useful for *nix users

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

This will optimize your MYSQL database(s) everyday at 1AM

Enjoy

NOTE: There is NO space between -p & [password]

NOTE2: You need to insert your root password

This can be run from Cpanel Direct Admin and other control Panels it can also be run from SSH using Putty

Average of ratings: -
In reply to D3SI Boy

Re: Optmize Database Automatically Using Cron jobs for Unix Users

by Jörg Knegten -

Hi an welcome! smile

You can get a little bit more security on this.

Password for mysql root in command line or cron can be read in process list or logfiles.

A better solution wold be to create, if still not existing, a section in my.cnf like the following:

...

[mysqlcheck]

user=root

password=secret_root_password (replace this with your password)

...

This way you can abandon "-uroot -ppassword" in your crontab line.

Jörg

Average of ratings: Useful (1)
In reply to Jörg Knegten

Re: Optmize Database Automatically Using Cron jobs for Unix Users

by Maik Riecken -
Hi,

I don't see any benefits by just moving the root password from one textfile to another.

my.cnf on most distributions is worldreadable (644) while syslog (where crond normally logs to) is only rootreadable (600).

Seen on debian, ubuntu-server... With these systems security would be decreased in standard configuration.

It's quite more save to create an extra user who is allowed to optimize the DBs and nothing else and use his' password.

regards,

Maik

Average of ratings: -
In reply to Maik Riecken

Re: Optmize Database Automatically Using Cron jobs for Unix Users

by Martín Langhoff -

The actual trick works like this -

  • keep the standard "my.cnf" without passwords, and world readable, as it will control the behaviour of many utilities.
  • Add a .my.cnf (note the leading dot!) in the homedir for the root user - it will be read automatically by the mysql utilities when executed under the root account. So put there the password, and make the file 600.
  • Alternatively, if you are on a unix that uses sudo instead of really having a root account, have a "secondary" configuration file with the passwords, owned by root and 600. Load it with the --defaults-extra-file parameter. This is what Debian and Ubuntu do, in the /etc/mysql directory, the file is called debian.cnf .
Average of ratings: -