Jump to content


How can I Optimize MySQL DB via Cron

  • Please log in to reply
2 replies to this topic

#1 Coastie

  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 19 April 2003 - 09:37 PM

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,

#2 Coastie

  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 19 April 2003 - 10:40 PM

Would this be correct?

mysqlcheck -u User -pPassword -o DB_Name

It seemed to work fine on my test chat DB

#3 effigy

  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 20 April 2003 - 12:15 AM

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

Regexp | Unicode Article | Letter Database

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users