[SOLVED] Skip triggers while restoring MySQL dump using Linux command

Issue

I have a large dump(~50GB) of MySQL database(Multiple databases backup). I am restoring it to a new Server which is taking a long time. I think it is taking this much time because it is huge data. the command ( gunzip < 1922-1648-329-75_all-databases.sql.20220305-0000.gz | MySQL -u test -p) also working fine and it started importing. But after some time, I’m getting an error called "Unknown column ‘id’ in ‘OLD’". I have troubleshot and found that this error is coming from one of the triggers in the backup file.
I don’t really need triggers on the new server. Is there a command-line option to use in MySQL which will allow me to skip the triggers while restoring the dump?

Below is the Restore Commane which I am using.

gunzip < 1922-1648-329-75_all-databases.sql.20220305-0000.gz | MySQL -u test -p

Solution

Recreate the dump if you can, using the –skip-triggers option. You will have to recreat them afterwards.

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_triggers

There is no option available to disable the triggers on import.

Answered By – Valeriu Ciuca

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.