'Skip triggers while restoring MySQL dump using Linux command
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 1:[1]
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.
Solution 2:[2]
rewrite your faulty trigger code.
you have an INSERT trigger with OLD in it, which is not supported.
Maybe it is acopy paste error, but such things must be made before making a backup runs.
the link below shows you how to select all triggers, this should help to find the problematic trigger
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | nbk |