Correcting errors in MySQL (MariaDB) database

 MySQL (MariaDB) errors correcting

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:

--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:

- 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.

Вас заинтересует / Intresting for you:

Choosing a database for your a...
Choosing a database for your a... 1938 views Валерий Павлюков Mon, 27 Aug 2018, 14:12:09
Learning Programming Terminolo...
Learning Programming Terminolo... 2401 views Antoni Sun, 13 May 2018, 18:12:05
What is NoSQL?
What is NoSQL? 1804 views Aaltonen Fri, 15 Jun 2018, 12:41:03
BigTable database overview
BigTable database overview 1374 views Боба Mon, 16 Sep 2019, 14:09:02
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
Suggested Locations