Optmize Database Automatically Using Cron jobs for Unix Users

Optmize Database Automatically Using Cron jobs for Unix Users

von D3SI Boy -
Anzahl Antworten: 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

Mittelwert: -
Als Antwort auf D3SI Boy

Re: Optmize Database Automatically Using Cron jobs for Unix Users

von Jörg Knegten -

Hi an welcome! lächelnd

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

Mittelwert:Useful (1)
Als Antwort auf Jörg Knegten

Re: Optmize Database Automatically Using Cron jobs for Unix Users

von 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

Mittelwert: -
Als Antwort auf Maik Riecken

Re: Optmize Database Automatically Using Cron jobs for Unix Users

von 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 .
Mittelwert: -