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
mysql -u root -p for_import -o < /home/monica/dumps/Dump20160406.sql
pv /home/monica/dumps/Dump20160406.sql | mysql -u root -p -o for_import
Empty set (0.00 sec)
You can use the parameter
--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.