MySQL on Raspberry Pi

These instructions will show you how to download and install the MySQL/Maria database server onto a Raspberry Pi.
This software allows you to collect data from sensors and programs on a Raspberry Pi.

More information on MySQL is available online.
This software will allow you to collect data from devices connected to your Raspberry Pi-such as peripherals and connected sensors.

After completing this assignment you will be able to:

1. Investigate MySQL
2. Run MySQL
3. Issue SQL commands from the Raspberry Pi CLI-Command Line Interface

You are expected to have a working knowledge of SBC hardware and software.
If you do not understand how to use an item, perform a Google Search, refer to the WSU Technology Knowledge Base,  ask a teaching assistant, or ask the instructor.

1. These first steps regarding installation have already been performed on the Raspberry Pi image and are for reference purposes.
2. Open a Terminal window and enter the following commands to ensure your Raspberry Pi is up to date
   sudo apt update
sudo apt full-upgrade
3. In the Terminal window enter the following commands to install MySQL:
   sudo apt install mariadb-server -y
   It will take a few minutes for the installation to complete.
4. In the Terminal window, to verify MySQL is installed and works, enter:
   sudo /etc/init.d/mysql status

   MySQL Running Successfully
   

5. In the Terminal window enter the following commands to log into the MySQL instance, noting that the default password is blank:
   sudo mysql -u root -p -h localhost
   Press Enter when prompted for the password.
   Type 'status' to see the MySQL settings.
   

   MySQL Status
   

6. Install the world sample database by opening the Chromium browser on your RPi and typing the following into the address bar:
   https://dev.mysql.com/doc/index-other.html
   Download World Database
   
   Then click 'Gzip', which will download the world database to the  '/home/pi/Downloads' folder.
7. Open the folder containing the downloaded file and double click it to extract it as 'world.sql' in the '/home/pi/Downloads' folder.
   Extract World Database
   
8. Startup MariaDB/MySQL if it is not running, and type in:
   Source /home/pi/Downloads/world.sql
   Note: this path command is case-sensitive. Find the exact location using File Manager.
   In a few minutes you have created the three tables in the world database.
9. Run the show tables command, you should see three tables:
   MariaDB [world]> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.001 sec)

   
10. Experiment with SQL commands:
    SELECT COUNT(*) FROM city;

SELECT * FROM country;
    
    Other commands to try:
    
    SHOW COLUMNS FROM city;

INSERT INTO city (ID,Name,CountryCode,District,Population) VALUES('4080','Chicago','USA','IL','8000000');
    
    
11. Congratulatons! Your IoT device is running a database server.
    With a little more work, you can use this database to collect information from sensors attached to your Raspberry Pi.

1. These steps are performed on your Raspberry Pi.
2. If not running, open the MySQL database, use the World database and insert a record for Winona, MN into the city table.
3. Once you have inserted the new record, run the following SQL statement:
   SELECT Name FROM city WHERE Name like 'Wi%';
   Your output will look something like this:
   

   Cities Starting With 'Wi'
   

Make sure MySQL is still running.

1. Take a screenshot of MySQL displaying the SQL statement above, showing Winona in the output.
   Note: this can be done using the Snipping Tool if you are running VNC, or use the scrot command if you are connected to the Raspberry Pi with a keyboard and mouse.
2. On your laptop save the screenshot as "IOT07" in your ' ' folder.
   

Submit to the appropriate D2L Assignment folder a screenshot that documents your work.

Based on your experience in this exercise, consider the value of mySQL databases for organizations, especially those needing to curate information.
Think about what type of problems a Raspberry Pi equipped with a relational database could solve, and whether it could be cost-effective.
Businesses are deploying IoT 'solutions'-how can mySQL equipped Raspberry Pis support or automate these efforts?

References:
MySQL on Pi:  Pi MyLifeUp website
W3 Schools:  MySQL Tutorial