Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a script doing mysql maintenance ?

or

how can i make it manually, using cron tab ?

and

what steps should a good mysql maintenance run ?

(MyISAM)

share|improve this question
    
Let's start with you defining what you mean by "maintenance". –  John Gardeniers Nov 8 '10 at 2:34
    
i am newbie on db stuff, so i think i mean table optimizations, checks, repairs... –  RULE101 Nov 8 '10 at 9:00
add comment

3 Answers

up vote 0 down vote accepted

mysqlcheck in crontab works well. Periodically review the logs.

There's an important caveat in the documentation:

Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock onlyREAD lock only

Here's how I've used it:

mysqlcheck -u $DB_USER -p"$DB_PASS" --all-databases > /root/mysql_backups/check_errors-${DATESTAMP}.log
share|improve this answer
add comment

Unless you're using FULLTEXT indexes, you should really convert to InnoDB. MyISAM is old and will corrupt your data given the slightest excuse, not to mention the fact that it's slow. Conversion is as simple as:

  1. Making sure that you're running a 5.1 or newer version of MySQL.
  2. Issuing ALTER TABLE (tablename) ENGINE=InnoDB for each table.
  3. (If running your own MySQLd) Changing your my.cnf to have space allocated to innodb_buffer_pool; you can swap whatever space you've dedicated to key_buffer out, since InnoDB doesn't use that.

If you insist on using MyISAM, a simple "mysqlcheck -A --optimize" or similar should do what you seem to want.

share|improve this answer
    
my version is 5.1.51 somehow "ALTER TABLE (tablename) ENGINE=InnoDB" doesnt work when i execute it from phpmyadmin ? –  RULE101 Nov 8 '10 at 9:10
add comment

By far the best opensource and used code is this for simple backups automysqlbackup

It can make simple full backups and have them rotated automatically for you..

You could also instead consider using LVM on your mysql directory and making backups on the fly without needing to locktables..

MyISAM tables are table locking, not row locking, so during a mysql backup some tables are not writable..

Unless you are having extremely high throughput, just go with the link i gave you :D

share|improve this answer
    
i am already using automysqlbackup, i meant other maintenance process like table optimizations, checks, repairs etc... –  RULE101 Nov 8 '10 at 0:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.