1

Today, Mariadb went down on one of my servers. The specific issue was something to do with ibdata1 file being locked. I found a website that showed how to fix it, and so all was OK. So I thought.

30 minutes later it went down again, same issue, same fix.

Its now running OK but I am worried it will happen again. I dont know why this would just happen when its been running fine for so long. Its a Linode server that runs Virtualmin for LAMP stack website hosting. Quite straightforward.

So I wondered if there is some sort of tool that can check over the DB and files to check there is nothing corrupt or untoward. Much like you do for operating systems, checking the disk is OK, defragging windows etc. Anything that gives the DB a once over to check all is OK.

2

Verifying that a MariaDB or MySQL instance is "OK" is a complicated task, not least because there are different levels of "OK", and one man's "OK" is another man's "not OK". There are various tools you can use, though. To name a few in no particular order:

1

MySQL has some basic tools described in the chapter 13.7.2 Maintenance statements:

13.7.2.1 ANALYZE TABLE Syntax
13.7.2.2 CHECK TABLE Syntax
13.7.2.3 CHECKSUM TABLE Syntax
13.7.2.4 OPTIMIZE TABLE Syntax
13.7.2.5 REPAIR TABLE Syntax
  • So your answer is a no then, because these are table specifics not DB as a whole. Its not practical to check all tables individually if you dont know which ones may have issues. – Laurence Cope Apr 3 '18 at 9:58
  • I've used HeidiSQL client that allows to check all the tables belonging to some DB recursively. Sure MysqlBench can do the same. I'm not sure about phpMyAdmin but you always can write a script like for dbtab IN $(show tables) ; do check table $dbtab; done; – Kondybas Apr 3 '18 at 14:09
  • Also DB or scheme in terms of MySQL is just a scope for names and have no additional internals to maintain ACIDity or any kind of consistency. Within same db/scheme you can refer other entities (tables/views/procedures/funtions etc) directly without any db.prefix. Also it is used for the storage/filesystem path. But "database" isn't a thing that can be checked/repaired as single entity. – Kondybas Apr 3 '18 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.