Schedule preventative maintenance of MySQL database server

mysql| Server | data | The database should consider establishing a schedule of preventative maintenance to help detect problems so that you can take steps to fix them:
1 perform regular database backups and allow updates to the log.
2 Arrange regular routine table inspection. By checking the table, you will reduce the chances of using backups. This job uses a cron job (typically called from the crontab file in the account that the server is running) and is easy to implement. For example, if you are running a server as a mysqladm user, you can establish a periodic check from the Mysqladm crontab file. If you do not know how to use C R o N, use the following commands to view the related UNIX human pages:
% man cron
% Man crontab
3 Check the database table during system boot before the server starts. The machine may be restarted due to an earlier crash. If so, the database table may have been corrupted and should be checked.
To run automatic table checking, you can write a script that changes the directory to the server data directory and runs Myisamchk and I s a M C K on all database tables. We'll use both of these programs in the script discussed below. If you only have a MyISAM table or only a ISAM table, just one of the programs, you can remove the unrelated program from the script.
Both Myisamchk and Isamchk produce some output based on table checking to see which tables are being checked, even when there is no problem. For crontab items, unless there is an error in the table, the output will normally be blocked (if the job produces any output, the cron job usually generates a mail message and rarely receives a message for a table checking job without any problems). If you invoke any utility with the--silent option, it produces output only if you find a problem. In addition, MYISAMCHK supports the--fast option, which allows the program to skip any tables that have not been modified since the last check.
A simple script to check all tables in the server data directory is as follows (DataDir should be modified to correspond to your system values):

A potential problem with the utility of this script is that if there are many tables, wildcard mode ' */*. M Y I ' or ' */*. I M ' may cause the shell to explode due to "too many arg um e n T S (too many parameters)" error. Another alternative script is the following (again, change the datadir to the value of your system):

Regardless of which form of script you choose, the author assumes that you are calling C h e C K _ mysql_ tables, make sure that you change the method in a timely manner so that it can be executed, and then call it manually to verify that it is working correctly:
% chmod +x check_mysql_tables
% Chmod_mysql_tables
Ideally, there should be no output. If the system does not support external locking, it is possible that the server will change it when you check the table. At this point, the script might report a problem to a table that actually doesn't have a problem. This is a bit unfortunate, but it's better than the opposite: when some failures occur, the script reports no problems. If the system supports external locking, the problem
Not appear.
The following sections explain how to create a script that passes through Cron and is executed automatically during system startup. In the examples of these subsections, the author assumes that the script is installed in/usr/users/mysaladm/bin. You will need to adjust the values that are appropriate for your system.
If more than one server is running on your system, you will need to modify the procedure to check the tables in each server's data directory. You can use a different check_mysql_tables copy, or modify it to receive a command-line argument that specifies the data directory you want to check.

Use cron Periodic checklist

Suppose you want to call the script C h e c K _ mysql_ tables for the Mysqladm user from the crontab file. Register as the user first, and then edit the crontab file with the following command:
% CRONTAB-E
This command takes you to the editor with the current crontab file copy (this file may be empty if you have not previously edited it). Add one row to the file:
0 3 * * 0/usr/users/mysqladm/bin/check_mysql_tables
It tells Cron to run this option every Sunday 3 o'clock in the morning. You can change the time or schedule as required. For formatting these options, see the Crontab manual page.

Check the table during system startup

If you are using a BSD-style system and have added the server's startup command to/etc/rc.local or similar other files, you can call C h e C K _ mysql_ tables from the corresponding file before starting the server.
If you are using the system V-style startup method to invoke MySQL from one of the/ETC/RC.D directories. Server, the process is slightly more complex. The startup scripts in these directories should understand the meaning of the start and stop arguments to take appropriate action on the system's startup and shutdown. To perform a table check, we can write a script that calls C h e C K _ mysql_ tables when the argument is start, and does nothing when the argument is stop. Let's call a script like this
Mysql. C h e C K, which reads as follows:

You can now use a procedure to install MySQL. C h e C K, which is similar to the process of installing Mysql.server described in Chapter 11th. Mysql.check must be given a lower prefix number in the run level directory to allow it to run before Mysql.server. For example, if you link to MySQL in the run-level directory with S99mysql.server. Server, you should link to mysql.check with S98mysql.check.