I wrote a little script that will defragment fragmented tables. You can supply usernames and passwords, and even run it against remote MySQL databases.
Here’s an example of it’s output:
root@lit [~]# mysqlFragFinder.sh
MySQL fragmentation finder (and fixer) v1.0.0
Written by Phil Dufault (phil@dufault.info, http://www.dufault.info)
Found 2 databases
Checking dufault_blog ... found 6 fragmented tables.
Optimizing wp_commentmeta ... done
Optimizing wp_comments ... done
Optimizing wp_options ... done
Optimizing wp_postmeta ... done
Optimizing wp_term_relationships ... done
Optimizing wp_users ... done
6 tables were fragmented, and were optimized.
I find this a great script — maybe a feature to add later is a way to prevent it from optimizing really large tables.
The source code is now hosted on GitHub!
Here’s a link to it:
https://github.com/pdufault/mysqlfragfinder
Any other features you’d like to see in it? Comment! Patches welcome.
{ 31 comments… read them below or add one }
You did a great job, is a very good script
Thanks!
Thanks for the feedback! Let me know if you see any improvements I can make in the script.
Thanks dude, nice job!
i use this on a ton of my servers.
cheers!
The process of optimizing/fixing fragmented tables has always been kinda painful especially with 100′s of relation tables.
Hundreds of thousands of users thank you…
Great script! How about a parameter to only operate on a specific database (and it’s tables). I need to work on just one table as I am not responsible for all of the tables on this server.
Thanks for this script. Just an FYI, with mysql 5, you can do the following query that can replace the fragmented variable or you can use it to replace other variables that list the tables or databases.
SELECT TABLE_NAME,ENGINE,Data_free
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'mysql’)
AND (ENGINE = ‘MyISAM’ OR ENGINE = ‘InnoDB’)
AND Data_free > 0;
Just a heads up, the line:
mysql -u”$mysqlUser” -p”$mysqlPass” -h”$mysqlHost” -D “$i” –skip-column-names –batch -e “optimize table $table” 2>”$log” >/dev/null
would benefit from escaping of the $table variable.
EG:
mysql -u”$mysqlUser” -p”$mysqlPass” -h”$mysqlHost” -D “$i” –skip-column-names –batch -e “optimize table \`$table\`” 2>”$log” >/dev/null
Tables with “-” in their name will mess things up if you don’t escape the variable.
Pretty cool!
Thank you very much!
Very nice script. Thank you for sharing!
Thanks for letting me know, updated the file.
Hi Phil,
I tried running the script with the following syntax:
./mysqlDefragmenter -u”username”-p”mypassword” -h”localhost”
The script runs and stays there without showing any output besides “MySQL fragmentation finder (and fixer) v0.7.5, written by Phil Dufault ( http://www.dufault.info/ )”.
I’m running this on a machine with CentOS 5.5 against mysql-5.0.77-4.el5_6.6
What am I doing wrong? Please advise.
Thanks,
Arun.
I was typing the wrong password. Got it to work after keying in the right one
Thanks for the awesome script Phil.
Best Regards,
Arun.
The script runs with an error
[root@IRS-LNX-07 scripts]# ./mysqlDefragmenter –user “root” –password “**redacted**” –host “localhost”
MySQL fragmentation finder (and fixer) v0.7.5, written by Phil Dufault ( http://www.dufault.info/ )
Found 4 databases
Checking openmeetings … found 1 fragmented table.
Optimizing sessiondata … An error occured, check /root/scripts/mysql_error_log.txt for more information.
and the following text is logged:
ERROR at line 1: Unknown command ‘\”.
Please advise.
Best Regards,
Arun.
Not to worry…I managed to fix the error. Its time to use it on my production box now
Thanks,
Arun.
Phil,
godsyn’s suggestion still threw an error on my file. I had to remove the escape characters and run the variable itself without any quote marks. I would make a note to anyone who experiences an error that this is likely the problem.
Thank you Phil:)
Phil – the updated file is using the ‘ character in the new script (which shares the key with “), whereas it needs to use a ` (which shares the key with ~). It won’t work as posted and errors out.
Thanks, fixed.
Thanks for letting me know about the errors Arun. I’ve fixed them.
However, please be more careful with what text you’re pasting to the internet! You showed me your root password.
The script seems to run fine, after I figured out how to populate the ~/.my.cnf file.
But… each time I run it, the same 44 tables are found to be fragmented and optimized.
How can I make the optimization stick?
Thanks, Tom
Hi Tom,
I’ll need some more data from you — try running the script in debug mode to find out what actions it’s taking. (put -x at the end of the line at top of the script)
Let me know how you make out.
Hi Phil,
I seem to have the same “error”, i tried your script on a more or less fresh installed piwik installation, this is the -x output: https://pastee.org/8z7m2
Best regards,
Paul
Hi Paul,
Are these InnoDB tables perchance?
Hi Phil,
yes, these are InnoDB tables.
Thanks for your scripts
Great job, Phil! Thank you so much.
How are the results of your script different than running this?:
mysqlcheck -u root -p –auto-repair –check –all-databases;mysqlcheck -u root -p –auto-repair –optimize –all-databases;
Nice and useful script.
But i had to change some lines regarding the parsing of the file “.my.cnf” to get it working. In my “.my.cnf” the password value is actually “pasword” and not “pass”.
So i had to change your line “43″ from
if grep “pass=” “$HOME/.my.cnf” >/dev/null 2>&1; then
to
if grep “password=” “$HOME/.my.cnf” >/dev/null 2>&1; then
And i had to change the two lines for the parsing of those values into variables from
mysqlUser=$(grep user= < "$HOME/.my.cnf" | awk -F\" '{print $2}');
mysqlPass=$(grep pass= < "$HOME/.my.cnf" | awk -F\" '{print $2}');
to
mysqlUser=$(grep "user=" "$HOME/.my.cnf" | awk -F= '{print $2}');
mysqlPass=$(grep "password=" "$HOME/.my.cnf" | awk -F= '{print $2}');
Now it works on my system
Thanks, I’ve committed this to the GitHub repo.
Having a little more control over what tables it optimizes, ie: not the big ones
{ 6 trackbacks }