June 16, 2019

RaspberyPi as a test SQL Server

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.


Installation

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

Using the --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.

Extra Packages

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-clienthp5-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:

FLUSH PRIVILEGES;

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.

View users

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:

  1. SELECT tells MySQL that you are asking for data.
  2. 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.
  3. 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 -

  1. Create your own database
  2. 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.


Sample Database

Clone the all-famous sample employee database from here

git clone https://github.com/datacharmer/test_db.git

Unzip the archive

unzip test_db-master.zip

Import the employee database

mysql -u root -pmysql> SOURCE /full_path_to_directory/test_db-master/employees.sql
Pi

Try to use the following statements for some basic hands on -

SHOW DATABASES;
SHOW TABLES;
Pi

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket