mysql analyze and optimize from bash
You want to optimize your mysql databases on a regular bases [say every night].
this is the script that does it. just put it in the crontab and you will be fine:
You could say you could do it in php, but there are machines dedicated for mysql, so why put php on them when you have bash?
I assure you, in php there are more lines than in this small script.
#!/bin/sh
for table in $(mysql -u MysqlUser -pMysqlPassword -D DBName -e 'show tables' | awk '!/Tables/ {print $1}'); do
mysql -s -s -u MysqlUser -pMysqlPassword -D DBName -e "analyze table $table"
mysql -s -s -u MysqlUser -pMysqlPassword -D DBName -e "optimize table $table"
echo " $table -> optimized"
done
explanations:awk '!/Tables/ {print $1}' i want the header produced by mysql to not show, so i take Tables_in_DBName out of the output
mysql -s -s -> suppressing some formatting from mysql output
Now, your mysql will run faster and using the indexes you put in place.
a better one , actually a more complete one is:
#!/bin/sh
for db in $(mysql -u root -pPASS -e ’show databases’ | awk ‘!/Database/ {print $1}’); do
echo "Mysql DB: $db is optimizing …"
for table in $(mysql -u root -pPASS -D $db -e ’show tables’ | awk ‘!/Tables/ {print $1}’); do
mysql -s -s -u root -pPASS -D $db -e "analyze table $table"
mysql -s -s -u root -pPASS -D $db -e "optimize table $table"
echo " $table -> optimized"
done
echo "Mysql DB $db optimization over!"
echo "——————————-"
echo "——————————-"
done