8

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

Community
  • 1
  • 1
Monica Heddneck
  • 2,973
  • 10
  • 55
  • 89
  • 1
    Just to make sure and try to help if you somehow missed like I often do - since the referred answer says trigger problem is solved with the mysql conf setting change - which make sense as it looks like a permission issue: did you restart your mysql server after editing the mysql conf? And second question, instead of workbench - which I don't trust - did you try command line to restore your database? No need to create schema but only blank database. I hope you got it solved, I can tell how you feel about it. – smozgur May 14 '16 at 23:41
  • You can add the option `-o` or `--one-database` to ignore everything that is not meant for your database `my_db` (assuming it has the same name), so basically just use `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 -o < "/home/monica/dumps/Dump20160406.sql"` (you can do it directly in the command line) – Solarflare May 15 '16 at 13:45
  • @Solarflare: thanks for this! My question is, where did `file="/tmp/tmpMJpTQj/extraparams.cnf"` come from? – Monica Heddneck May 16 '16 at 06:19
  • 1
    It contains options that differ from global options and is created by the workbench. You could copy it while workbench is trying to run your import, but you can probably just leave it out. (If you don't know what it is, you probably won't need it). It is not needed for -o. The import would work with just `mysql -D my_db -o <"/home/monica/..."`, the rest is just there because the workbench is throwing in everything it knows (it's not a bad idea though, so just remove the one option you don't need). – Solarflare May 16 '16 at 10:48
  • 1
    With -o, the databasename in your backup file has to be the same name as your defaultdatabase you import into (I'm not sure if you really named your database on AWS `for_import`...). The -o parameter will skip everything in the backupfile that doesn't belong to `for_import`. (You can of course replace the name in the file if you absolutely need to) – Solarflare May 16 '16 at 16:54
  • Is for_import the real name of the database? Can you get the first 10-20 lines of your dump file and update the question. Your file extension is .sql so I am guessing you have a plain sql file. Get the file first lines by head -n25 Dump20160406 – e4c5 May 19 '16 at 10:28
  • @Solarflare hit the nail on the head with the -o parameter. If he/she decides to write up an official answer, I think that it would probably get accepted – Monica Heddneck May 19 '16 at 18:32
  • @Monica Heddneck thanks for the reminder, yes, it might be a good idea to have the solution as an answer for future readers with the same problem, so I posted the answer – Solarflare May 21 '16 at 18:16

3 Answers3

8

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.

Solarflare
  • 10,721
  • 2
  • 18
  • 35
3

Another option is to import with mysql -f to ignore and skip over failing definitions.

Alex R
  • 11,364
  • 15
  • 100
  • 180
0

I think that your dump can have triggers on system tables, just as error text says. Open your SQL dump (with any text editor) and remove everything that concerns system databases (mysql, perfomance_schema, information_schema). Or you can remove all triggers.

Jehy
  • 4,729
  • 1
  • 38
  • 55