what to do if you crashed your database OR it says there are no tables (but there are!)

Sometimes moving a database file (/var/lib/mysql/*) breaks the records inside mysql's brain about what databases it had and what their tables were. If you did not use mysqldump to take backups, you are in trouble. Unfortunately if you do this, you have a long night ahead of you. However, help is at hand.

A symptom of breaking Moodle in this way is you get this error:

    Config table does not contain version, can not continue, sorry.

    which means it cannot read the table mdl_config.

The basic steps are:

1. Move your crashed/confused databases out the way so you don't break them further. 

2. Recreate your mysql instance.

3. Recreate the databases and tables WITHOUT their contents (just the database names and table structures).

4. Tell mysql to forget their contents without using drop command (you use discard instead)

5. move your original db files back into place

6. Tell mysql to re-scan (import) the db files.

7. Create a timed script to run backups

Here are the detailed steps:

Step 1-2

1. and 2. Moving crashed databases and recreating the instance.

mv /var/lib/mysql /var/lib/mysql_original

mkdir /var/lib/mysql

chown mysql:mysql /var/lib/mysql

chmod 750 /var/lib/mysql

# and now the coup de grace

mysqld --initialize

You can now re-launch mysqld and it will let you reset your password.

Step 3

3.a. Let's suppose your database is called "whatever" and your database owner is "admin":

mysql> create database whatever;

mysql> grant all on whatever.* to 'admin'@'localhost';

mysql> flush privileges

3.b. Now, to recreate the tables, you need to get the table structure from the .FRM files. In your backup (step 1-2 above, the folder mysql_original) you will see a bunch of .FRM and .IBD files. The .FRM files contain the database form/tables. The .IBD contains the InnoDB database contents (the actual contents).

Run dbsake.sh. You can get it from 

http://www.ostrowick.co.za/share/reinstate_moodle_ibd_files/

or 

http://get.dbsake.net

use save as binary and mark it executable

chmod 755 dbsake.sh

PS it is a shell wrapper around a python around a binary, but don't be scared, no malware.

What dbsake.sh does is outputs the SQL that makes the tables that have been destroyed. So if for example you have a table called

mdl_users

the FRM will be called

mdl_users.frm

you then do this:

./dbsake.sh frmdump mdl_users.frm > mdl_users.sql

and that generates a file called mdl_users.sql. 

you then import that SQL back into mysql: assuming your database is called "whatever"

mysql whatever < mdl_users.sql

that will recreate that table.

Now if you have to reinstate a moodle instance which has like 1000 tables, you obviously don't want to do this horrible exercise one at a time by hand, so:

for i in `/bin/ls /var/lib/mysql_old/whatever/*.frm` ; do ( echo "processing $i"; ./dbsake.sh frmdump $i > whatever/$i.sql ) ; done 

Note that the quotemarks here are very specific: the single quotes are backticks (grave accents), and the double quotes are not smartquotes. That will list all the files in a folder called "whatever" (presumably /var/lib/mysql_old/whatever) and make sql versions of them.

Then to import them all in a batch:

cd /var/lib/mysql_old/whatever

for i in `/bin/ls *.sql` ; do ( echo "processing $i"; mysql whatever < $i ) ; done 

This process will create fresh .frm (table) files in /var/lib/mysql/whatever/

Step 4.

Now we have to tell MySQL to forget the contents of the tables so we can recreate them.

You can see that the tables are still broken because if you try type in mysql:

use whatever;

show tables;

it shows the tables

but if you try do this, it doesn’t work:

select * from whatever.tablename

It will give an error.

If it DOES NOT give an error then stop, you have succeeded already.

4.a. Copy the .IDB files back: we need this so MySQL has something to empty.

cp -f /var/lib/mysql_old/whatever/*.idb /var/lib/mysql/whatever/

chown mysql:mysql /var/lib/mysql/whatever/*.idb

chmod 640 /var/lib/mysql/whatever/*.idb

note this will erase any existing new idb files. Technically your database doesn't really exist so this shouldn't be a problem, just make sure you never mess with / delete / move the mysql_old contents again.

4.b. Now login to mysql and run the "discard" function for each database table (not DROP, which deletes the table structure as well). I suggest you prepare these commands in a text editor using copy/paste as it is tedious to type them out over and over, especially with 1000 tables to process.

To get a list of the file names / table names, do:

 ls whatever/*.frm | mail youremail@gmail.com

which will email you a list of the files that you can copy/paste

mysql> use whatever;  # choose your database

mysql>

alter table mdl_analytics_indicator_calc  discard tablespace;

alter table mdl_analytics_models discard tablespace;

alter table mdl_analytics_models_log discard tablespace;

alter table mdl_analytics_predict_samples discard tablespace;

alter table mdl_analytics_prediction_actions discard tablespace;

alter table mdl_analytics_predictions discard tablespace;

alter table mdl_analytics_train_samples discard tablespace;

alter table mdl_analytics_used_analysables discard tablespace;

alter table mdl_analytics_used_files discard tablespace;

etc for all the tables. This will effectively emtpy the IDB files you copied but keep the db structure. This step tells mysql to forget what it “thinks” are in the IDB files.

Step 5

5. now copy the IDB files back AGAIN.

Now we’ve told mysql to forget the table contents, we are going to tell it to reimport them. Copy the .IDB files back again: we need this so MySQL has something to reimport.

cp -f /var/lib/mysql_old/whatever/*.idb /var/lib/mysql/whatever/

chown mysql:mysql /var/lib/mysql/whatever/*.idb

chmod 640 /var/lib/mysql/whatever/*.idb

Step 6

6.a. Now login to mysql and run the "import" function for each database table. I suggest you prepare these commands in a text editor using copy/paste as it is tedious to type them out over and over, especially with 1000 tables to process.

mysql> use whatever;  # choose your database

mysql>

alter table mdl_analytics_indicator_calc  import tablespace;

alter table mdl_analytics_models import tablespace;

alter table mdl_analytics_models_log import tablespace;

alter table mdl_analytics_predict_samples import tablespace;

alter table mdl_analytics_prediction_actions import tablespace;

alter table mdl_analytics_predictions import tablespace;

alter table mdl_analytics_train_samples import tablespace;

alter table mdl_analytics_used_analysables import tablespace;

alter table mdl_analytics_used_files import tablespace;

6.b. Confirm that it did the job properly.

use whatever;

show tables;

it shows the tables

and if you try do this, it SHOULD NOW work:

select * from whatever.tablename

that should show the OLD contents of your database that were lost and are now found.

Step 7.

Back it up now! And make a cron job to keep the backup running!

7.a. Exit mysql with exit. On the command line:

mkdir -p /var/backups/mysql

mysqldump whatever > /var/backups/mysql/whatever.sql

gzip /var/backups/mysql/whatever.sql

7.b. Put the above into a shell script:

mkdir /scripts

vi /scripts/backup.sh

[press i key]

#!/bin/sh

mkdir -p /var/backups/mysql

ourdate=`date | sed -e s/\ /_/g | sed -e s/\:/-/g`

dbname="whatever" #put your database name here

filename=$dbname"-"$ourdate

echo $filename

mysqldump -u root -p"password" $dbname > /var/backups/mysql/$filename.sql

gzip /var/backups/mysql/$filename.sql

[ESC key]

[Shift-Z keys Shift-Z keys]

chmod 700 /scripts/backup.sh # we make it 700 so only root can read the password

crontab -e

[i]

[cursor down to below all the explanations]

0 0 * * * root /scripts/backup.sh  # run once a day

[ESC]

[ZZ]

You are done. You will now get a daily backup in /var/backups/mysql