Geek Answers Handbook

Error restoring triggers in system tables when restoring MySQL database

I had a large database on AWS for several months, but she left because it started to get more expensive.

Now I have one 32 gigabyte file on my hard drive, which I exported before disconnecting the MySQL database running on the instance.

I would like to import 4 million rows or so into my local MySQL on my laptop.

Using MySQL Workbench, I tried to do just that. But first, I recreated the same schema locally (3 tables in total). Then, using the "Import data" option, I selected "Import from offline file" and pointed to my file. I allow him to copy - only to receive this disgusting message in response:

01:56:30 PM Restoring /home/monica/dumps/Dump20160406.sql Running: mysql --defaults-file="/tmp/tmpMJpTQj/extraparams.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=my_db < "/home/monica/dumps/Dump20160406.sql" ERROR 1465 (HY000) at line 488: Triggers can not be created on system tables Operation failed with exitcode 1

Looks like there is a problem with triggers? At first I am a bit confused, since my database has never had any triggers.

To troubleshoot, I found t its SO question , and I tried the tip - I edited my.conf, but that didn't make any difference.

Other searches made me think. There really is nothing known about this error that I can find.

If anyone has any advice, that would be great. Thank you

EDIT

I used some tips in the comments from @Solarflare and used this statement:

mysql -u root -p for_import -o < /home/monica/dumps/Dump20160406.sql

In fact, I discovered a cool utility called Pipe Viewer that provided a progress bar - a visual proof (hopefully) that something was actually happening. So I rewrite the line:

pv /home/monica/dumps/Dump20160406.sql | mysql -u root -p -o for_import

Of course, it looks like everything is working, and after 5 or so minutes the import was complete:

34.1GB 0:08:19 [69.9MB / s] [==================================== = =>] 100%

Nice! HOWEVER: when I show databases and then use for_import , then show tables , I get:

Empty set (0.00 sec) My heart is broken .: (

+5
source share
3 answers

You can use the -o or --one-database option to skip anything that is not intended for the default database that you want to import:

 mysql -u root -p -D mydb -o < /home/monica/dumps/Dump20160406.sql

will import the mydb database from your file and skip all the others, especially system databases that cause problems. The name mydb should be the same in your file and in your database.

+5
source

Another option is to import using mysql -f to ignore and skip failed definitions.

+2
source

I think your dump may have triggers on system tables, as the error text says. Open the SQL dump (with any text editor) and delete everything related to system databases (mysql, perfomance_schema, information_schema). Or you can delete all triggers.

0
source

More articles:


All Articles