Skip to content

Instantly share code, notes, and snippets.

@emmasaunders
Last active October 10, 2023 16:40
Show Gist options
  • Save emmasaunders/794abed1280c21442b198ca3ed957c48 to your computer and use it in GitHub Desktop.
Save emmasaunders/794abed1280c21442b198ca3ed957c48 to your computer and use it in GitHub Desktop.
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.
@paperjoe
Copy link

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
Copy link

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

@jap2es
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
Copy link

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
Copy link

Thank you!!

@chowkingman
Copy link

That works! Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment