Normalization design of MySQL database

Establish a database table maintenance specification

It is a good idea to perform a database table check on a regular basis rather than waiting for the problem to occur. Consideration should be given to establishing a schedule of preventative maintenance to assist with automatic issues that you can take steps to fix:

Perform a regular database backup and allow the update of the log.

Schedule Regular table checks. By checking the table, you will reduce the chances of using backups. This job, using a scheduled task under Windows, UNIX uses a cron job (typically called from the crontab file of the account that is used to run the server) and is easy to implement.

For example, if you run a server as a MySQL user, you can set up periodic checks from the MySQL crontab file. If you do not know how to use cron, you should use the following command to view the related UNIX man pages:

$man Cron

$man crontab

Checks the database table as a system boot before the server starts. and may be restarted as a result of earlier crashes. If such a flower, the database table may have been destroyed, it should be thoroughly checked.

Create a script that applies to regular maintenance

To run automatic table checking, you can write a script that changes the directory to a server data directory and MYISAMCHK and ISAMCHK all database tables. If you only have a MyISAM table or only a ISAM table, you need only one of the programs to comment out the irrelevant program from the script.

The textbooks can be invoked by Cron or invoked during system startup.

For maintenance purposes, you can use Myisamchk-s to check the table. -S,--silent option makes Myisamchk and Isamchk run in silent mode, only printing messages when errors occur. In addition Myisamchk supports the--FAST option, which allows the program to skip people and tables that have not been modified since the last check.

1. A simple script

For example, an easier to understand simple script that checks all tables in the server directory (DataDir should be modified to correspond to the appropriate values for your system):

#!/bin/sh

CD DataDir

Myisamchk--silent--fast */*. Myii

Samchk--silent */*. Ism

2. A more complex script

Useful for a potential problem with this script: if there are many tables, wildcard mode ' */*. Myi ' and ' */*. ISM may not be available due to "too many arguments (too many parameters)" or the command line exceeds the length allowed by the shell. The script can be further modified to (again, DataDir modified to fit the value on your system):

#!/bin/sh

Datadir=datadir

Find $dtatdir –name "*. Myi "-print | Xargs Myisamchk--silent--fast

Find $dtatdir –name "*. ISM "-print | Xargs Isamchk--silent

Of course you can also specify multiple database directories in the script.

3. How to execute a script

Assuming that you save the script as Check_tables, you should make sure that it is executable, and of course it is recommended that you first switch to dedicated user mysql:

$su MySQL

$vi check_tables (Edit script, you can also use your favorite editor)

$chmod +x Check_tables

Manual execution to detect if your script has errors:

$check _tables

Ideally, there should be no output. If the system does not support external locking, the visitor anthracene that server will change it when you check the table. At this point, the script might report problematic tables that actually don't have a problem. If the system can support external locking, the problem does not occur.

Using cron periodic checklists in Unix

The following describes how to create a script that is run through cron and during system startup. In the example of this section, the author assumes that the script is installed in/usr/local/mysql/bin, and you need to modify the process to check the tables in each server's data directory. You can use a different check_tables copy, or modify it to accept a command-line argument that specifies the data directory you want to check.

Assume that the script check_tables is invoked on the MySQL user from the crontab file.

1. Register first with the identity of the user

$su MySQL

2. Generate a temporary file to capture the tasks that have been scheduled

$crontab –l>/tmp/entries

3. Add content to the last line of the generated file

Add this line 0 0 * * 0/usr/local/mysql/bin/check_tables to the last line of the temporary file:

$echo "0 0 * * 0/usr/local/mysql/bin/check_tables" >>/tmp/entries

It tells Cron to run this option every Sunday 0 o'clock in the morning. You can change the time or schedule as required. For a format for these options, see the Crontab man page.

4. Reschedule the Schedule

$crontab/tmp/entries

If there is any information after the check, the cron job usually generates a mail message to the user. Because of the--silent option, there will be output only if there are errors in the table, and there will be message information, so there will not be a lot of unwanted message information. (You should now understand that the reason the script takes the--silent option for this regular maintenance, your server is best to support external locking, so that when you check the table, no access violation occurs.) If you can't do this, you'd better maintain it when no user is using the server, such as the wee hours.

Check the table during system startup

If you are using a BSD-style system, such as OPENBSD,FREEBSD, and have added the server's startup command to/etc/rc.local, check the table during startup to call Check_tables from the appropriate file before starting the server.

For systems that use the Sytem V-style boot method, for example, most commercial UNIX systems, the startup method is from the/etc/ One of the RC.D directories calls the Mysql.server script, the process of checking the table before the database is started is complicated because the scripts in these directories must understand the start and stop arguments.

For example, scripting like this, named Mysql.check, calls Check_tables when the argument is start, and does nothing when the argument is stop:

#!/bin/sh

#See Hou we sere called

Case "$" in

Start

Echo–n "Checking MySQL tables:"

if [-x/usr/local/mysql/bin/check_tables]; Then

/usr/local/mysql/bin/check_tables

fi;;

Stop

#don ' t do anything

;;

*)

echo "Usage: $o {start|stop}"

Exit 1

Esac

Exit 0

Now you can install the Mysql.check, the process class seems to be introduced in chapter II to let the server automatically start the installation of Mysql.server process. Mysql.check must be given a lower prefix number in the run level directory to enable it to run before Mysql.server. For example, if you are connecting to Mysql.server in the run-level directory with S99mysql.server, you should link to mysql.check with S98mysql.check.

Because Linux centralizes the advantages of the BSD and Sytem v systems, the above two approaches are entirely applicable to Linux. For simplicity, the first method is generally used.