How can I verify my MySQL/MariaDB database files for errors?
Linode
Linode Staff
I am importing my database to a new Linode manually by copying files out of /var/lib/mysql. The transfer was successful, but now I want to check the imported database files for errors. How can I make this happen?
1 Reply
andrewm
Linode Staff
The best option for checking your database for errors is to use the mysqlcheck utility:
However, this utility only works on an actively running database, and you may need to check these files without the database running. For that, I first need to provide some background knowledge about how MySQL/MariaDB store your data.
MySQL/MariaDB have a configuration entry called datadir which defines the directory where your database files are stored. By default, this directory is /var/lib/mysql. Your configuration file will most likely be /etc/my.cnf, but this may vary depending on your specific setup.
Within this directory is another directory, also called mysql, which contains your actual database files. Transferring your database between Linodes should simply be a matter of ensuring that these database files exist within this mysql directory:
While the best way to export your database is by using mysqldump, this requires an actively running database, so you may need to manually back up your database as you have done.
These raw database files will most likely be in a format called MyISAM, and you can validate these file format for errors using the myisamchk utility:
- https://mariadb.com/kb/en/myisamchk/
- https://dev.mysql.com/doc/refman/8.0/en/myisam-crash-recovery.html
They may alternatively be in Aria format, which will use the separate aria_chk utility:
MySQL and MariaDB are largely interoperable, although they may have some distinct differences in their detailed operation. In particular, it doesn't appear that MySQL's documentation references aria_chk, so this may be exclusively be a MariaDB utility.
I hope this helps! Let us know if you have any other questions.