# mysql maintenance: optimize databases to kill overhead

## geki

hi there!

the tables of various databases are updated daily, multiple times.

Therefore, I want to analyze(update index) and optimize all databases

with their respective tables to kill the table overhead every now and then.

so, I wonder how to do that the best way.

some ideas:

1 run mysqlanalyze as a cronjob.

 *mysql_cleanup wrote:*   

> #!/bin/bash
> 
> mysqlanalyze --user root --password --analyze --all-databases
> 
> mysqlanalyze --user root --password --optimize --all-databases

 though, I do not want to have a user with cleartext password there, which has write access.

2 run a php script to iterate over databases and their tables. same issue as before ...

am I paranoid about the password?!  :Rolling Eyes: 

3 add a mysql user with minimal rights to execute the maintenance task via 1.

which rights would that be?

I read somewhere SELECT and INSERT would be enough ...

then ...

3a just have the cleartext password inline.

3b read a file's content for the encrypted password and pass it decrypted to the commandline.

4 execute the maintenance task at the end of the update process every Xth time for selected database.

5 any other ideas?

I just cannot decide which one is the one proper way for the maintenance task to be executed.  :Evil or Very Mad: 

hmm ... looking at it a bit I think I favor 3a. then, the question is what rights are necessary?

Thank you for your aid!

----------

## Ant P.

If you're worried about someone being able to read root's crontab, which is only readable by root in the first place, I'd say your system's already compromised.

----------

## geki

true, true. so I decided to use mcrypt, so that the pass is not cleartext at least. just for the fun of my paranoia.  :Surprised: 

with that I found out about bash's trap function, with a strange "feature". but I will post about that in another thread.

edit #1 well, or just peek at portage shell scripts howto do this and it works.  :Surprised: 

----------

