Skip to content

Instantly share code, notes, and snippets.

@emmasaunders emmasaunders/readme.md
Last active Sep 17, 2019

Embed
What would you like to do?
How to import the sakila database into MySQL

##The old method, via phpmyadmin The first time I downloaded the sakila database, I did it via phpmyadmin using Import at the Server level. I did not use a command line. I did not create a database called Sakila and then populate it: the .sql files delete and re-create the database anyway. I imported the schema file and then the data file, ignoring the .mwb file, which is only useful if you have MySQL Workbench installed, at which point it supplies you a database diagram. A nice-to-have, but not critical in creating a database.

When I imported the files, I had to untick the box that said "Allow the interruption of an import in case the script detects...". I also had to temporarily change MySQL's maximum import size (using a line of SQL I found online): https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Today I find this method does not work. I get an error about geometry (which is due to a commented out line in the SQL). Even when I remove the commented out lines (whose purpose is to create spatial / geometric fields for MySQL versions 5.7.5+), I still get an error, which tells me the address table cannot be created. Some suggestions are online that you can't import via phpmyadmin if the file size exceeds 2MB. So I gave up and followed the instructions via the command line, which strikes me as needlessly difficult for beginners. https://dev.mysql.com/doc/sakila/en/sakila-installation.html

##Another method, via terminal app, on a mac http://dev.mysql.com/doc/refman/5.7/en/mysql.html

  1. I started both MySQL and Apache servers running in MAMP.
  2. I installed MySQL Workbench application. I don't think you need to do this step but I include it for completeness.
  3. I opened the terminal app. I was at the root level of my files.
  4. Based on SreerajS's answer on a forum, I ran this: /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot (http://stackoverflow.com/questions/10577374/mysql-command-not-found-in-os-x-10-7). If you aren't using MAMP, your path would be different - it involves finding the MySQL folder on your C drive (PC) or within Applications (mac). Depending on your operating system and your server, your username / password might be presented differently too. Some instructions for Linux and Windows are here: http://dev.mysql.com/doc/mysql-getting-started/en/
  5. Before I got the right syntax, Terminal app kept telling me "mysql: command not found"
  6. It was clear once I'd logged in: I got a Welcome message. Then I typed SOURCE (with a space at the end) and dragged the schema.sql file into Terminal, deleted a space at the end of the file path, put a semi colon there, and hit enter. I got various "Query OK" messages.
  7. Then I repeated with SOURCE and dragged the data.sql file into Terminal. Deleted the end space, added a semi-colon, hit enter.
  8. Then I refreshed my phpmyadmin screen and lo and behold, there was sakila.
@lherskowitz

This comment has been minimized.

Copy link

lherskowitz commented May 5, 2017

Thank you for this. Worked like a charm. New to all this and the directions on the Sakila website were totally inaccessible to me!

@erinhsun

This comment has been minimized.

Copy link

erinhsun commented Sep 23, 2017

Thanks!!! Totally works now!

@marialaustsen

This comment has been minimized.

Copy link

marialaustsen commented Dec 28, 2017

Thanks a lot for this!!

@shrg182

This comment has been minimized.

Copy link

shrg182 commented Jan 21, 2018

Great! Thank you!

@orasas

This comment has been minimized.

Copy link

orasas commented Jun 11, 2018

Thanks!

@talkinriddle

This comment has been minimized.

Copy link

talkinriddle commented Aug 6, 2018

Thanks! I've been struggling for hours and this worked like a charm!

@yajingwang1022

This comment has been minimized.

Copy link

yajingwang1022 commented Aug 23, 2018

This is really helpful! Thank you!!!

@paperjoe

This comment has been minimized.

Copy link

paperjoe commented Dec 29, 2018

Dude, thank you so much. This worked perfectly. I had to do this as part of a lynda.com course and it did not give any instructions!

@KatrinHa

This comment has been minimized.

Copy link

KatrinHa commented Mar 27, 2019

Thank you, this was so helpful and easy to follow. And it worked!

@jap2es

This comment has been minimized.

Copy link

jap2es commented Jun 24, 2019

You are the goat. This was also part of a Lynda course which provided 0 instruction. I don't know how you figured this out but thank you!

Answer to: How do I import sakila database into SQL for Lynda course OR how do I import sakila into phpmyadmin for Lynda course.

Hopefully that routes someone here?..¯_(ツ)_/¯

@sahanxiii

This comment has been minimized.

Copy link

sahanxiii commented Jul 12, 2019

Do you have any idea on how to do it for windows? I'm a beginner for the same Lynda course and I'm struggling!

@koby-a-close

This comment has been minimized.

Copy link

koby-a-close commented Sep 17, 2019

Thank you!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.