Preventative Maintenance Checklist for MySQL Database

preventative maintenance checklistMySQL administrators know that despite their best efforts, power outages, improper shut-down, duplicate records, incomplete writes of tables, crashes and many other reasons can corrupt or even destroy database tables. To avoid considerable downtime and the gnawing stress that these events can cause they must implement a preventative maintenance(PM) plan that helps to keep the database running smoothly and efficiently. The fact that a database is not static in nature means that information gets switched around and while MySQL has earned the reputation of being the easiest database to use that does not mean it does not have its challenges. Any administrator who completes courses such as Learn Database Design With MySQL knows that a preventative maintenance plan does not act as a golden bullet, but minimizes the chances of data damage and corruption in the database.

Here are six other reasons why a scheduled preventative maintenance is an important part of the database management no matter the industry involved.

  1. The cost for preventative maintenance is usually less than the cost of avoiding a time-based or system run-based plan
  2. Unplanned downtime is greatly reduced when a preventative maintenance plan is executed on a regular basis
  3. Small problems are found and corrected to avoid them becoming major headaches in the future
  4. MySQL administrators will find it is a great training tool to know how to react if bigger problems occur in the future
  5. Preventative maintenance can automatically detect problems
  6. Simply put, all administrators who care about their jobs do it

Consider Regular Updates

MySQL is constantly being updated and administrators must factor this into the equation by ensuring that steps are taken to receive information about all new releases. New releases may have considerable differences from the older version and it is the administrators role to see how the two differ and if the newer version is relevant to specific needs. The developers will include the relevant information about the new release in the MySQL Reference Manual so reading this before implementing a possibly incompatible version is of paramount importance. Of course no administrator wants to be guinea pig but there are few that are brave enough to update their systems only to find that there are bugs in the development series. Stable release series on the other hand have fewer bugs and are used mainly to correct bug issues. As part of a preventative maintenance plan regarding updates, these factors must be considered:

  • Are administrators reporting bugs with the new release and if so were there any fixes launched?
  • Does the current version of MySQL present any problems and would it be worthwhile to run the new release on an independent server to see how it works first?
  • Considering the present database, are there features in the new release that should be implemented for the database to become more efficient?
  • Are you prepared as an MySQL administrator to update the MySQL C client library to ensure that other libraries and applications that rely on it are running as they should? Rebuilds can be time consuming, so knowing how you intend to proceed is important.

Auto-Recovery and Table Repair

The MySQL server which is also referred to mysqld already comes with auto-recovery and table repair capabilities which you can learn more in the course Database Design and MySQL Fundamentals. The commands CHECK TABLE and REPAIR TABLE can be used to manually check and repair tables as part of a defined preventative maintenance routine. Commands that work in line with the MySQL server include mysqlcheck and mysqldump. They function by instructing the server regarding table maintenance and which should be performed. If you decide to use the standalone program myisamchk instead to repair table, the necessary steps must be taken to ensure that the server is not writing to the table at the time of doing a preventative maintenance routine. This command does not go through the server as the other commands stated before and this is why they both work independently.

If there was a crash prior to starting up the server, table checks can be conducted the moment the sever is initiated. This will enable the administrator to see any possible errors or corrupted areas and take the necessary corrective action.

MyISAM and ISAM tables are frequently checked and repaired by the myisamchk and isamchk commands respectively. Luckily, the MySQL administrator does not have to worry about table damage and corruption if the wrong command is used to check a particular table.

Database Backup Schedule

Backing up is important because if there is a system failure data recoveryis easier. Within MySQL, the system maintains binary log that can be used in combination with the last backup for recovery. With the commands mysqldump and mysqlhotcopy can be used to make backups of the server. Periodic backups is important and should not be overlooked if a recovery point is needed in the future without experiencing serious data loss in the process.

If an updated version of MySQL has been launched, a backup of the server and files will also allow the administrator to test what could possibly happen if the update is installed. Occasionally there may also be instances in which a bad command such as DROP DATABASE or DROP TABLE can cause information to be lost. Having that information saved in a backed up version will eliminate the need to rebuild and experience considerable downtime. Important information about MySQL backup and recovery is covered in the course MySQL for Beginners.

As mentioned previously, backup can be performed with both binary files and backup logs. The backup logs become useful if the date the backup was conducted is far behind. This is possible with the statements that are in the logs which can be converted into statements that easily recognizable by the MySQL server. This is done with the mysqlbinlog command which essentially does the conversion. Manually the “not like” statements can be used as to identify areas that do not match as explained in this blog post MySQL Not Like Statement: How to Find What Doesn’t Match.

Error Logs

It is also important to note the MySQL maintains error logs and these are a vital source of information to know what is causing problems in the system. Corrupted data files and index files are known to cause crashes and these should be removed or corrected to prevent the system from having frequent problems. An error log will be created if the MySQL server checks and verifies that tables exist in the system that need to be repaired. Always check these error logs to identify any possible areas that can cause major problems. When checking error logs it is important to identify what is an updated version or bug fix is necessary to be install to permanently correct the problem.

Server Startup

The server can start up for two possible reasons: there was a crash and it restarted or it is being started normally. In situations where there was a crash the MyISAM storage engine can be used to correct any possible errors in MyISAM tables as mentioned previously. It must be noted that this command is not done automatically and must be enabled manually to allow it to run the necessary checks.

Preventative maintenance is not a one-step process, but involves different steps and precautions which are mentioned above and essentially summarized as follows:

  • Use backups and binary files as part of a preventative maintenance routine
  • Take full advantage of the built-in auto-recovery functions that are within the MySQL program
  • Know the difference between MyISAM and ISAM tables and use the relevant commands to check and repair these

The daily routines of MySQL administrator do not have to be chaotic with the right database management skills in place. To stay on top of the requirements and procedures, the course Oracle SQL 11g Advance provides valuable tips all administrators should know.