RaspberyPi as a test SQL Server
Getting a good use out of a RaspberyPi Model 3 as a practice SQL server. Get you SQL game up with some sample databases.
A Pi is capable of doing almost anything a regular server can; on a smaller scale. With a footprint similar to that of a credit card, it is easily the top choice for any enthusiast.
If a Pi is setup with any linux OS, installing an SQL server on top of that is minutes work. So if you have Pi ready with linux installed, let’s go.
Now we’re ready to start the installation. First we need to ensure everything’s up-to-date:
sudo apt-get update && sudo apt-get upgrade
Install any out of date packages when prompted. Next we’re going to install the actual MySQL server by typing:
sudo apt-get install mysql-server --fix-missing
–fix-missing flag when installing mysql-server takes care of any missing dependencies.
After installation is complete, the
mysql_secure_installation utility runs. This utility prompts you to define the mysql root password and other security related options, including removing remote access to the root user and setting the root password.
You should be prompted for a root password:
Once you’ve confirmed your password, MySQL server should finish installing and the service should start.
Now we need to install a couple of extra packages, php5-mysql and mysql-client. The php5-mysql package allows connections to be made to MySQL Server through PHP, and mysql-client allows us to connect to our local MySQL server through the CLI:
sudo apt-get install mysql-client php5-mysql
Accessing MySQL server
After installation is done, ssh into the Pi and enter the SQL server -
mysql -u root -p
Set the root password
If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.
For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';
To make the change take effect, reload the stored user information with the following command:
Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.
MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:
SELECT User, Host, authentication_string FROM mysql.user;
The following list describes the parts of that command:
SELECT tells MySQL that you are asking for data.
User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case you are looking for the username, the host associated with the username, and the encrypted password entry.
FROM mysql.user “ tells MySQL to get the data from the mysql database and the user table. A semicolon (;) ends the command.
Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.
Now that SQL server is installed, we have two options here -
- Create your own database
- Use example database from the internet
Example database is the best choice for anyone starting out with MySQL. An example database installation is as follows -
Clone the all-famous sample employee database from here
git clone https://github.com/datacharmer/test_db.git
Unzip the archive
Import the employee database
mysql -u root -p
mysql> SOURCE /full_path_to_directory/test_db-master/employees.sql
Try to use the following statements for some basic hands on -
SHOW DATABASES; SHOW TABLES;
Screenshots are from a Mac, but the concept remains the same. :)