Dumping a mysql5.5 schema with triggers and restoring to a new schema with a different name

Question

The problem I have is that the trigger definitions have the original schema name hardcoded within them e.g:

TRIGGER `sales`.`tender_delete_trigger`
AFTER DELETE ON `sales`.`tender`
FOR EACH ROW
......

So if backup the 'sales' schema, using mysqldump, then try and restore to a newly created schema called 'sales_test' on the same server I get an error stating something along the lines of multiple triggers are not allowed on the same event.

My current solution is to manually replace the old schema name 'sales' with the new schema name 'sales_test' within the dump file. This works, but is there an easier less tedious solution?

Answer 1

Don't do it manually. Since you don't specify an O/S, I'll assume Linux, though there are equivalents for most others:

vim -c'%s/`sales`/`new_schema_name`/g' -c':x' dumpfile.sql

Another possibility is to omit the schema name from the triggers. The current schema is assumed.

---

(edit)

1) Agreed. But renaming a schema is a strange problem to begin with. 2) That is certainly a risk since there are likely to be tables and variables similarly named. A modification to the pattern might help:

  %s/\([^.]\)`sales`\([.]\)/\1`new_schema_name`\2/g

would insist that the schema name is appropriately placed, unless there is intervening whitespace.