Mysql dump restoring

MySQL

I ran into a big trouble. I made mysql dump using version 5.0.77 without setting the compatibility mode (I wasn't aware of this problem). Actual mysql server is version 4.0.18. Today our database crashed. Lots of tables lost. While trying to restore the dump, it's returning error "ERROR 1193 at line 18: Unknown system variable 'character_set_client'". Is there any way we can restore the db?

Thanks a lot for you help.

Best Answer

It may well be worth installing, on another box if required, mysql 5.0.77, restoring, and then re-dumping with the appropriate options.

This isn't an elegant solution, but if you're in "big trouble" and need something that works and fast, this is the first thing that jumped into my mind.

Related Solutions

Mysql – ERROR when restoring MySQL data dump

Looks like an unescaped apostrophe error to me. Just find it, fix it, then go on to the next one and hope there aren't too many.

Mysql – Restoring a slave MySQL database from raw backups of master gives InnoDB tablespace errors

I believe that you have an incosistent snapshot especially due to the error

InnoDB: Error: tablespace id is 150238 in the data dictionary  
InnoDB: but in file ./1_107789/email.ibd it is 150747!

It may not LVM's fault. Googling here and here, it's my guess that you need to make sure mysql has written everything to the disks (no buffers) and that changes won't happen by lock'ing tables to be on the safe side. It could also be due to the different MySQL versions on the small chance that something has changed in the innodb code. You could rule this out by trying that exact snapshot on a clone/(similar server) of your master. Please see this too

Best Answer

Related Solutions

Mysql – ERROR when restoring MySQL data dump

Mysql – Restoring a slave MySQL database from raw backups of master gives InnoDB tablespace errors

Related Topic