MySQL Maintenance Tasks
You may notice that your database is not running as it use to, and you may think that it’s due to it size and it’s normal, but few MySQL maintenance tasks can help you get it back to normal.
NOTE: This tasks will lock the tables and they should be done in a maintenance window because they can take hours to complete for very large tables.
The list of tasks should be:
- Backup your database
- Check
- Optimize
- Analyze
You could do all of them but you don’t always need to. Please read below and see if you need to do all of the above MySQL maintenance tasks.
Database Backup
You probably already have a backup plan for your databases, if not you need to make one but also create a backup just before doing this tasks and copy the file to a remote server.
For this you can also check my posts of MySQL backup and restore and using Linux scp to copy files to another server.
Or simply do:
mysqldump -u root -p dbname > dbname.sql scp dbname.sql [email protected]:~/
mysqlcheck command
This command checks tables for integrity errors and i think it should be done on a regular basis.
It can be done on a table and on all tables in a database.
From MySQL prompt:
mysql> CHECK TABLE {table1}; mysql> CHECK TABLE {table2}; mysql> CHECK TABLE {table3};
Or check all tables in database:
mysqlcheck -u root -p --check --databases dbname
MySQL Optimize command
This command it’s like defragment for MyISAM tables, it reclaims the unused space, and for InnoDB it runs ALTER TABLE which rebuilds the tables and indexes.
NOTE: For InnoDB you will probably get the message: “Table does not support optimize, doing recreate + analyze instead” which is NORMAL!
From MySQL prompt:
mysql> OPTIMIZE TABLE {table1}; mysql> OPTIMIZE TABLE {table2}; mysql> OPTIMIZE TABLE {table3};
Or optimize all tables in database:
mysqlcheck -u root -p --optimize --databases dbname
MySQL Analyze command
This command is already done if you have run OPTIMIZE and you don’t need to run it again, but have saved me a few times on a slow running databases, it optimizes and rebuilds the index keys.
From MySQL prompt:
mysql> ANALYZE TABLE {table1}; mysql> ANALYZE TABLE {table2}; mysql> ANALYZE TABLE {table3};
Or analyze all tables in database:
mysqlcheck -u root -p --analyze --databases dbname
NOTE: This will help you a bit but you should also check your server performance, maybe your DB has become too LARGE and you need to add a bit more RAM. Also you can check our MySQL Tuning Scripts for some configuration tuning.
MySQL DOCS: https://dev.mysql.com/doc/