melfneerg.com

- 'cos life is like that

About the Author
Tudor Davies

author Tudor is a techie turned manager who fights like mad to keep his tech skills honed and relevant. Everything from web hosting, networking, *nix and the like. Constantly developing and co-ordinating with others to make the web a better (and easier to use) place.

mySQL maintenance

Monday, 14th Feb 2011 Posted @ 11:43

Sometimes it becomes necessary to ensure that your databases (and indeed db server) are operating "efficiently".

To this, I regularly use the following to make sure everything is in tip top order:

mysqltuner.pl - check the config and performance of your mysql server. It offers suggestions on performance tweaks to make to your /etc/my.conf and how well it is performing.

mytop - top for mySQL databases, you can see the activity on a particular database.

Optimize only fragmented tables in MySQL - make this into a script and run it when mysqltuner warns you that a database or more need optimising

Its also a good idea to back up your databases. I found a script years ago that does just that and its produced below:

#!/bin/sh
#

for I in `echo "show databases;" | mysql -u [id] -p[password] | grep -v Database`;
do mysqldump -u [id] -p[password] $I | gzip > "$I.sql.gz"; done

Basically, it lists all the databases you have access to and then dumps the database for a zipped file.

On my system, I then include these file in the backup schedule (run on all machines) which backup to one of the FreeNAS shares (mounted over NFS).

[ no comments : Add ]

start

layout and initial css based on the Qtractor page