Installing an Example MySQL Database
Sometimes it is useful to build a demo app using database data that looks realistic but isn't otherwise used for any mission-critical reasons. For this purpose I like to use the "Sakila" database, which you can learn more about here. This database is also known as the "DVD Store" database, because it mimics what might be used to manage a hypothetical DVD store from back in the day.
I've personally used this and the MySQL "employee" database for years as part of technical sales demos at both DreamFactory and now, Adalo. These databases are large enough to be realistic yet easily understandable at first glance, making them ideal for sales-related demonstrations.
You can download this database in zip or tar.gz format here. Once downloaded, decompress it and then import the schema into your MySQL instance like so:
$ mysql -u root -p < sakila-schema.sql
Enter password:
This will create a new database called sakila. If you want to use a different database name, then open sakila-schema.sql and find these three lines:
DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;
Update each sakila reference to the name of the database you'd like to use, such as dvdstore. Keep in mind that if you don't modify these lines and already have a database named sakila, it will be destroyed before being recreated by the second line!
Next, import the data:
$ mysql -u root -p < sakila-data.sql
Finally, you'll want to create a dedicated user for interacting with the database. The following command will create a user who can connect to the MySQL server from anywhere (the latter defined by the %):
mysql>create user 'dvdstore'@'%' identified by 'your-password-goes-here';
The dvdstore user cannot however do anything. You need to grant it permission (known as privileges in the MySQL world). If you want to give the dvdstore user full access to the sakila database, you can assign it all privileges like so:
mysql>grant all privileges on sakila.* to 'dvdstore'@'%';
If instead you want to give the user read-only access, you can execute this instead:
mysql>grant select on sakila.* to 'dvdstore'@'%';
If you wanted to restrict access from a specific IP address, you can execute:
mysql>grant select on sakila.* to 'dvdstore'@'192.168.2.43';
After creating the user, logout of the root account and confirm you can login with the new dvdstore user:
$ mysql -u dvdstore -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sakila |
+--------------------+
2 rows in set (0.00 sec)
mysql>
After confirming you can see the sakila database, connect to it and view the tables:
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)