How can I Optimize MySQL DB via Cron

[Coastie]

I\'d like to set up a script to optimize a MySQL DB once a week from cron.

 

I know how to config the crontab, but I am not sure what the syntax of the script should be. I have one to dump my DB and also to move it to a backup server.

 

To dump, I use something like:

 

mysqldump -u User -pPassword --opt DB_Name| gzip > /path/to/save/backup/BACKUPSQL.$(date +%a).gz

 

That creates the BACKUPSQL file with the current day of the week.

 

Now I can\'t use mysqldump to optimize the DB, so would it be something like this?

 

mysql -u User -pPassword OPTIMIZE  TABLE  `c_ban_users` ,  `c_messages` ,  `c_reg_users` ,  `c_users`

 

I just used phpmychat as an example DB.

 

Where do I tell it which DB to optimize?

 

Also can I use a wildcard such as * to mean all tables, or do I need to list them?

 

Thank You,

[Coastie]

Would this be correct?

 

mysqlcheck -u User -pPassword -o DB_Name

 

It seemed to work fine on my test chat DB

[effigy]

setup an sql file that will have the statement, we\'ll call it optimize.sql:

optimize table tbl1, tbl2, tbl3

 

now in the cron you want something like ( i think i have the right syntax ):

 

/path/to/mysql/bin/mysql -uuser -ppassword databasename < optimize.sql