Dominique Dutra's picture
Dominique Dutra - Fri, 2013/07/19 - 20:52

Hello,

Seems like triggers are lost when restoring a backup.

Just created a new server (mysql) and tried restoring from a backup. After a workaround (https://github.com/turnkeylinux/tracker/issues/40) time to check database integrity. All triggers are gone.

 

Any ideias?

 

Best regards.

Forum: 
Support
Tags: 
tklbam
mysql
Jeremy Davis's picture

Jeremy Davis - Sun, 2013/07/21 - 05:40

I only run very simple MySQL DBs and don't sue triggers so I am not sure. AFAIK TKLBAM just does a dump of the DB on backup and then restores it on restore. Someone else previously mentioned that Views were also lost on backup/restore so perhaps triggers are another thing that are not included in DB dumps by default? If you still have your original DB running then perhaps you can do some experimentation to see what is required to get the triggers included in a DB dump?

Regardless I have lodged a bug on the TKL Dev Tracker.

Dominique Dutra's picture

Dominique Dutra - Sun, 2013/07/21 - 17:34

Jeremy

 

Thanks alot for your input.

 

This specific db is not very large, but it is very busy. 

Last week I have a rogue query mess up the whole db and I have to restore from s3 backup (tkl). Also this db relies on triggers to keep some data consistent.

Anyways - first I had problems restoring (hence another topic I created) So i decided to restore do ec2 and everything went fine except for triggers and function.

I also updated the local db (running under vmware) to the last version that uses debian. I created the triggers and functions again and everything is fine now.

As for the triggers - if tklbam uses mysqldump thats where the problem is. See, if I start a tkl-mysql machine from scratch and create a couple of triggers, simply run mysql dump and restore, my triggers wont be there. I am no expert but this has something to do with permissions.

If you do a grant all permissions to root@% on % hosts, you will be able to backup and restore triggers normally using mysqldump. I think you'll also need the --routines parameter but now sure.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit

After this, mysqldump will keep triggers. Not sure for tklbam - maybe it uses another user to do the backup process of databases?

Best regards and sorry for my English. Greetings from Brazil :)

Add new comment