'Triggers can not be created on system tables' error when restoring MySQL database

Question

I had a large database up on AWS for a few months, but took it down because it was starting to get expensive.

Now, I have a single 32GB file on my hard drive, that I exported before I shut down the MySQL database running on the instance.

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

Using MySQL Workbench, I attempted to do just that. But first, I recreated the exact same schema locally (just 3 tables). Then, using the Data Import option, I selected "Import from Self-Contained File", and pointed to my file. I let it rip -- only to receive this disheartening 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

It looks like there is some issue with triggers? First I'm a bit confused since my database never had any triggers to begin with.

To troubleshoot, I found this SO question, and I tried the advice -- I edited the my.conf file but it didn't make any difference.

Other searching caused me to come up blank. There's really nothing out there about this error that I can find.

If anyone has any advice, that would be great. Thanks.

EDIT

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

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

Actually, I discovered a cool utility called Pipe Viewer which provided a progress bar -- visual proof (I hope) that something was actually happening. So I rewrite the line to this:

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

Sure enough, it looks like things were working and after 5 or so minutes, the import was completed:

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. :(

Answer 1

You can use the parameter -o or --one-database to skip everything that isn't meant for the default database you want to import:

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

will import the database mydb from your file and skip all others, especially the system-databases that are causing your troubles. The name mydb has to be the same in your file and in your database.