One of the clearest reasons for errors in the database can be an improper shutdown of the MySQL server. As it usually happens, there is a power failure of the server, or other reasons that led to a banal shutdown of the machine, or a reboot. Sometimes, and often, this kind of failure can lead to problems that can be solved only by restoring data from a backup, and this is the question of why you need to make backups . The presence of errors in the database may not appear immediately, but if they are, then sooner or later you will definitely notice them. Problems, as a rule, manifest themselves in the form of errors after queries to the database, or the database begins to go into thought for an inappropriate time.
Let's see what you can do first to try to fix the situation. The mysqlcheck utility is usually installed by default with the MySQL server and can be used to check databases for errors. Let's consider an example of its use.
Using the mysqlcheck utility
# mysqlcheck -u root -p --optimize --auto-repair --all-databases
Purpose of parameters:
1 2 3 | --optimize – optimize all databases.--auto-repair – repair all bases.--all-databases – checking errors for all databases. |
If the database is large, and you urgently need to correct a specific table, then you can run a check of a specific table in the database.
Checking a single table in the database:
# mysqlcheck -r DB_name Table_name -u root -p
Correction of MyISAM tables
There is also the myisamchk utility , which differs from the previous utility in that before using it, you must stop the database server, while mysqlcheck can be used when the server is running. Let's look at an example of using the myisamchk utility.
Stopping MySQL Server
# service mysqld stop
Analyzing the database for errors
# myisamchk -s /var/db/mysql/our_database/*.MYI
Please note that the path to the databases may differ for you, you should also clarify this point. If there are errors, then a message similar to the following will appear:
myisamchk: warning: 2 client is using or hasn't closed the table properly
MyISAM-table '/var/db/mysql/our_database/ary_mem.MYI' is usable but should be fixed
Correcting errors with myisamchk
# myisamchk -r /var/db/mysql/our_database/ary_mem.MYI
We fix errors for all tables in the database (recursively)
# myisamchk -r /var/db/mysql/our_database/*.MYI
The error correction process will be accompanied by a similar message output in the console window:
1 2 3 | - Fixing index 1- Fixing index 2- Fixing index 3 |
After the bugs are fixed, you can start the MySQL server to make sure everything is in order.
Starting the MySQL Server
# service mysqld start
I would like to note one point that the commands for stopping and starting the MySQL server, as well as the paths for the location of the databases on your server, may differ, so you should also take these points into account.
