1

We use mysqldump for backup (without Trigger/Events/Routines included).

mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --all-databases

The triggers we copy with cp to backup folder. We use Galera 5.5.41-MariaDB-wsrep.

After restoring the mysqldump, howto restore the Triggers? Simple cp or are other tasks necessary?

# find . -name *.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/user_login.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation_participant.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/accounttovacancyrelation.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike.TRG

# find . -name *.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_participant_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_user_login_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_delete.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_insert.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN

example content of files

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN
TYPE=TRIGGERNAME
trigger_table=eventcomment

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
TYPE=TRIGGERNAME
trigger_table=user

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`test`@`%` TRIGGER `DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED`.`eventcomment_BEFORE_DELETE` BEFORE DELETE ON `eventcomment` FOR EACH ROW\nBEGIN\n    DELETE FROM eventcommentlike where eventcommentlike_comment = OLD.eventcomment_id;\nEND'
sql_modes=0
definers='test@%'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='latin1_swedish_ci'
1

You shouldn't have to copy .TRN and .TRG files.

Just make sure when you run mysqldump to sue the --triggers option. It is already set by default. You also need the TRIGGER privilege in order to dump triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.