How to deal with Mysql crashes

[musicmaster]

Recently I had a Mysql crash. I had closed the program a bit rude and it didn't allow me back in. It took me quite some time to find out what were my options. This is meant as a kind of  tutorial to get you on track. Your directory structure may be different and there may be small differences because I used MariaDb instead of Mysql, I worked under Windows instead of Linux and I used InnoDB tables stored as separate files. But it will give you a good orientation.

Step 1: orientation and backup

Of course you have  a recent backup.... But you may want to make another copy of at least your data directory and preferably also the rest of your Mysql installation. While trying to repair you may change your data and that can go wrong. So it is good to be able make a copy that  you can quickly put back.

Locate your Mysql configuration file: my.ini or my.cfg. if you run "mysqld --verbose –help" you will see many pages of information. On one of the first lines you will see the option where the program will look for the config file. In my case this file was in a directory that was not in this list: it didn't work.

Locate also the error log file. The config file will tell you whether it is enabled and where you can find it. 

Step 2: export your data

When I in the previous step talked about a backup I meant copying directories. This step is about exporting a sql file.

Mysql misses repair tools for InnoDB tables (It can repair ISAM tables with mysqlcheck or myisamchk). Instead the most common solution when you have a problem is exporting and importing a sql file. In this step we will export the file. With importing we will wait until we have done some further checks.

If you have a problem logging in into your mysql installation there is the "innodb_force_recovery" option in my.ini under the [mysqld] header. It can have a value of 1 to 6. It is usual to start with the lightest option (1) and when that doesn't work to try heavier options. You either put the value in the config file (like: innodb_force_recovery=3) or use it as an option when starting mysqld. A value of zero means that it is switched off. Note that when you enter Mysql when this option is active you cannot insert, update or delete rows. You can only export and delete tables and databases. Note that after each change to the configuration file you need to stop and restart Mysql to activate the change.

To export all tables you can use something like:

mysqldump -u root -p --opt --all-databases --result-file=alldb.sql

Instead of the --result-file option you can also use a ">" under Linux. But that will not work under Windows: it will export in some 16-bit character set that contains the same text but is twice as long and won't import.

Of course you can also choose to export only certain databases.

Step 3: exploration

Now it is time to look at the error messages and to try solutions. Search the internet for your situation and try to understand what might work for you. Sometimes the problem is as simple as a rights problem or a full disk.

Some common solutions:

 - add "innodb_flush_method=normal" to the [mysqld] section of your configuration file.

 - add "skip-grant-tables" to the [mysqld] section of your configuration file. This gives every user the rights of the root. Useful when you have a problem logging in as root.

 - delete the logfiles ib_logfile0 and ib_logfile1 while Mysql is inactive.

- run the mysql installer. It will offer you a Repair option. If present you can execute "mysql-upgrade --force" (force means here that it "upgrades" even when you already have the latest version). Mysql-upgrade is not present in Xampp. 

Step 4: import the sql file

If the exploration didn't provide a solution you can import the file we previously exported.

To do this we first make sure that the "innodb_force_recovery" line in our config file is set back to zero or deleted. If you don't do that your will get an error message that your tables are read-only when you try to import.

Next we delete (or rename) the data directory and replace it with a new empty directory. 

Then we activate mysqld. Note that depending on the software it will have no password or a random expired password at this point. By default a random password is put in $HOME/.mysql_secret.

Next we go into the Mysql console:

mysql -uroot -p –default-character-set=utf8
mysql> SET names 'utf8';
mysql> SET autocommit=0;
mysql> SOURCE alldb.sql; commit;

If you use Thirty Bees you can replace utf8 in the first line with utf8mb4. 

Note that while the export is a matter of minutes the import is a matter of hours. Some utilities might do it faster but I didn't try.

Switching autocommit temporarily off should make the import faster but is not necessary. I have put the final commit on the same line so that you won't forget after the long import.

You may have noticed that by exporting and importing all databases you export and import also system databases like information_schema. For me that worked good enough. However, you if it gives problems for you you might consider exporting the non-system databases one by one. Then after you empty the data directory you initialize it with the command  "mysql_install_db" or its more modern variety "mysqld --initialize" and then you import the databases one by one. But I haven't explored that enough to provide detailed instructions.

Appendix: how to deal with a single database?

When you want to export and import only a single database some things change (I have called the database mydb):

 - The export command becomes: mysqldump -u root -p mydb --opt --result-file=alldb.sql

 - you don't delete the whole data directory but only its mydb subdirectory

 - when importing you run the command "use mydb" before you give the source command.

Edited April 20, 2019 by musicmaster (see edit history)