4

I've got a MySQL database I'm using for a live project and I want to create a copy of it on the same server that I can use for development purposes. I have used MySQL dump to create a copy of the live database. I did this as follows:

mysqldump -u root -p mydatabase_live > mydatabase_dump.sql

I then logged into MySQL and created an empty database called mydatabase_test. I then try to copy the dump to the newly created database_test by logging back out of MySQL and doing the following:

mysql -u root -p database_test < mydatabase_dump.sql

This gives me the error message ERROR 1359 (HY000) at line 527: Trigger already exists.

When I log back into MySQL and examine the triggers on the database I've just took a dump of, I cannot see any triggers with duplicate names. I've tried repeating the above process in case there was some kind of error in the initial dump, but the problem repeats.

Can anyone explain why I'm getting this error message and how to solve this?

I'm using MySQL Ver 14.14 Distrib 5.7.25, for Linux

* NOTE ADDING DETAIL TO THE ANSWER ACCEPTED BELOW * The MySQL dump file can be opened in a text editor. I used vim. The solution was to find the triggers and change the part of the trigger name which identifies it as belonging to the schema from which you took the dump, changing it to the schema you're aiming the dump at. In my case that meant changing mydatabase_live.my_table to mydatabase_test.my_table. Then logging into mysql, Dropping the test table and then recreating it, before logging out and performing the copy command again.

Jay Black
  • 107
  • 2
  • 12
  • 1
    That seems like a bug in mysqldump. When you specify a database on the command line, without preceding it with the `--databases` option, the objects in the dump file should not be qualified with a database name. – Michael - sqlbot Feb 08 '19 at 01:42

2 Answers2

2

Triggers are stored on Information_Schema.Triggers table. Maybe that's why you cant duplicate them, maybe create a new one with a different name?

look here for more information

https://dev.mysql.com/doc/refman/8.0/en/faqs-triggers.html

Adas
  • 404
  • 2
  • 19
2

Linux

    mysqldump -u root -p mydatabase_live > mydatabase_dump.sql
    mysqladmin create database_test
    cat mydatabase_dump.sql | sed s/`mydatabase_live`/`database_test`/g | mysql -u root -p database_test

Windows

    mysqldump -u root -p mydatabase_live > mydatabase_dump.sql
    mysqladmin create database_test
    type mydatabase_dump.sql | sed s/`mydatabase_live`/`database_test`/g | mysql -u root -p database_test

Notes

  • If you're in Windows, you'll need a windows version of sed (e.g. GnuWin32 sed)
  • I used a variation of several answers from a similar question at DBA Stack Exchange.
  • I'm using MySQL 5.7
  • mysqldump in 5.7 includes the database name when exporting triggers and views.
  • I put my password in an env var and passed it on the command line (i know, i'm bad, i get it). I mention it because I don't know what happens when you pipe data to the mysql executable and then it prompts for a password.
Jonathan
  • 949
  • 1
  • 11
  • 13