By   September 3, 2018

In the unlikely event that you’ve lost your Mysql ib_logfile[01] files, you will google and try to figure out whether you can get the data back.  All the googling will tell you that all of your data is in those log files but that’s not true as of any recent version of mysql.  I was looking around in /var/lib/mysql/zm/ and noticed the .ibd files were sizable which suggested the data was actually in there instead.  After some googling, I found you can import data back into a freshly created Zoneminder DB.

I decided to uninstall/reinstall zoneminder  from scratch and then recreate the DB:

https://stackoverflow.com/questions/18761594/how-do-i-do-a-clean-re-install-of-zoneminder

After doing that, I did the following:

(there are shell commands interspersed with the mysql commands so you can see the order of operations):

 

lock tables Devices write;
alter table Devices discard tablespace;
# cp -p saved/Devices.idb /var/lib/mysql/zm/
alter table Devices import tablespace;

lock tables Events write;
alter table Events discard tablespace;
# cp -p saved/Events.idb /var/lib/mysql/zm/
alter table Events import tablespace;

lock tables Filters write;
alter table Filters discard tablespace;
# cp -p saved/Filters.idb /var/lib/mysql/zm/
alter table Filters import tablespace;

lock tables Frames write;
alter table Frames discard tablespace;
# cp -p saved/Frames.idb /var/lib/mysql/zm/
alter table Frames import tablespace;

lock tables Groups write;
alter table Groups discard tablespace;
# cp -p saved/Groups.idb /var/lib/mysql/zm/
alter table Groups import tablespace;

lock tables Logs write;
alter table Logs discard tablespace;
# cp -p saved/Logs.idb /var/lib/mysql/zm/
alter table Logs import tablespace;

lock tables MonitorPresets write;
alter table MonitorPresets discard tablespace;
# cp -p saved/MonitorPresets.idb /var/lib/mysql/zm/
alter table MonitorPresets import tablespace;

lock tables Monitors write;
alter table Monitors discard tablespace;
# cp -p saved/Monitors.idb /var/lib/mysql/zm/
alter table Monitors import tablespace;

lock tables Servers write;
alter table Servers discard tablespace;
# cp -p saved/Servers.idb /var/lib/mysql/zm/
alter table Servers import tablespace;

lock tables States write;
alter table States discard tablespace;
# cp -p saved/States.idb /var/lib/mysql/zm/
alter table States import tablespace;

lock tables TriggersX10 write;
alter table TriggersX10 discard tablespace;
# cp -p saved/TriggersX10.idb /var/lib/mysql/zm/
alter table TriggersX10 import tablespace;

lock tables Users write;
alter table Users discard tablespace;
# cp -p saved/Users.idb /var/lib/mysql/zm/
alter table Users import tablespace;

lock tables ZonePresets write;
alter table ZonePresets discard tablespace;
# cp -p saved/ZonePresets.idb /var/lib/mysql/zm/
alter table ZonePresets import tablespace;

lock tables Zones write;
alter table Zones discard tablespace;
# cp -p saved/Zones.idb /var/lib/mysql/zm/
alter table Zones import tablespace;

After that, ‘systemctl restart zoneminder’ and hope for the best.