Cron to run weekly to optimize mysql

[ManagedCloud-Hosting]

Hi

Can we run weekly to optimize mysql, is this possible ?

How to use the below script is possible this code used to work for cPanel as advised in a thread but we have shifted to DA, please help as we are new to this...Thanks

----------------------------------------

Yes, create a db_clean.php and set it up as a cronjob....Code:



<?
include '../incl/config.php'; //// This should contain your DB connections
// Set font
echo "<font size=\"-1\" face=\"arial\">";
// Statement to select the databases
$db_select = 'SHOW DATABASES';
// Query mySQL for the results
$db_result = mysql_query($db_select);
// Loop through all the databases
while ($db_row = mysql_fetch_array($db_result)) {
// Select currently looped database and continue only if sucessful (have permission to)
If (mysql_select_db($db_row[0])) {
// Echo database name
echo "<br><b>";
echo $db_row[0];
echo "</b><br>";
// Statement to select the tables in the currently looped database
$tbl_status = 'SHOW TABLE STATUS FROM ' . $db_row[0];
// Query mySQL for the results
$tbl_result = mysql_query($tbl_status);
// Check to see if any tables exist within database
If(mysql_num_rows($tbl_result)) {
// Loop through all the tables
while ($tbl_row = mysql_fetch_array($tbl_result)) {
// Statement to optimize table
$opt_table = 'OPTIMIZE TABLE ' . $tbl_row[0];
// Query mySQL to optimize currently looped table
$opt_result = mysql_query($opt_table);
// Echo table name
echo "&nbsp;&nbsp;<i>";
echo $tbl_row[0];
echo "</i><br>";
} // End table while loop
} else {
// Alert that there are no tables within database
echo "&nbsp;&nbsp;<i>No Tables</i><br>";
} // End table existance if statement
} // End database if statement
} // End database while loop
// Alert them that operation was successful
echo "<br><br><b>Above tables successfully optimized!</b>";
// End font
echo "</font>";
?>

----------------------------------------

 

[kristian]

Hi!

Not sure what the intention is with the PHP script in regards to output, but to optimize all tables in all databases, I believe you can run the following command from the command line (assuming you have set up a ~/.my.cnf file with the correct credentials, or pass them as additional options):

mysqlcheck -o --all-databases

However, unless you know for a fact that all of your databases need a weekly optimize, I would generally recommend against it. If you haven't already, read up on optimize pros/cons before you implement your plan.

 

[Wanabo]

I have in \etc\cron.daily\ a script that will do just that.
For weekly optimize mysql put this script in \etc\cron.weekly

Make file sqloptimize.sh in \etc\cron.weekly and put the below content in it.

#!/bin/bash
[email protected]
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --optimize --all-databases

It will automatically find and use your password for the da_admin account and optimize all databases.

Make sure sqloptimize.sh is executable by user root.

Edit: You can test if its working by executing ./sqloptimize.sh in your \etc\cron.weekly directory

 

[kristian]

Instead of that subprocess madness to fetch the MySQL password, do this instead, to use the mysql.conf file directly. Added benefit is that there will be zero chances of your password showing up in the process list:

#!/bin/bash
[email protected]
mysqlcheck --defaults-extra-file="/usr/local/directadmin/conf/mysql.conf" --auto-repair --optimize --all-databases

Edit: In fact, not entirely sure that'll work, since the format of that file does not seem to be suitable for MySQL. There is however, a file called my.cnf in the same directory that would be a better choice if you want to go with the --defaults-extra-file variant.