Checking crashed tables in Mysql with PHP

Asked

Viewed 214 times

3

Here in the company there is a structure with at least 20 servers, each server has at least 20 databases, and each database must have between 40 to 50 tables. For such a structure, rotate the command check table 'nome_da_tabela'; in all tables, even if by code is very time consuming.

Currently there is a code in PHP that runs through all the servers and runs the command already mentioned above, but as I said is very time consuming, so I wonder if when a table Crasha in Mysql, whether this generates any log within Mysql itself that can be queried instead of entering table by table and checking?

I ask this, because I have read somewhere that in the base information_schema in some table is stored this information, but I have not found anything that explains me clearly.

php mysql query

  • Are you looking for the checksum which is the table control sum report which is one of the tests done CHECK TABLE to determine whether a table is damaged or not. The checksum works as follows, every time an information is inserted or modified in a row the DB sums all the bytes of that line and stores the result next to the line. The CHECK TABLE or CHECKSUM TABLE simply redoes this sum line by line, in the checked table, and compares with the stored value if the difference accuses the error

  • before posting an answer, you already used the command SHOW TABLE STATUS WHERE COMMENT LIKE '%crash%' to see if this result helps? Hence it is possible to insert into a table and use this data

1 answer

3


There is the program mysqlcheck for this task. For example, to search for errors in tables of all databases (check in, or -c, is the default operation and can be omitted):

$ mysqlcheck -u usuario -p --all-databases

Or, on a specific basis:

$ mysqlcheck -u usuario -p --databases db1 db2 ...

I note that you do not need to expose the administrative user root. Any user with permission to run SELECT and INSERT in the tables is sufficient.

If you still want to do it manually without the mysqlcheck, you can use the option in the scripts FAST or CHANGED of command CHECK TABLE. According to official documentation, FAST and CHANGED were created to check tables periodically. In most cases, FAST is preferable. This should already help you. Now let’s talk about records (logs) for this purpose.

One way to record everything that happens in a session is by using the command builtin tee:

mysql> tee meus_logs.txt
mysql> //COMANDOS...
mysql> notee

Or, by invoking the client from the command line:

$ mysql --tee meus_logs.txt ...

You can also use redirect operators on a system similar to UNIX, but unlike the above option, errors end execution:

$ mysql ... < comandos.sql > meus_logs.txt

Okay, then just process the file meus_logs.txt (look for errors). And saw, if you want to make a daemon in PHP for the task, take a look at cron and permissions.

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.