In the previous post, Send and Receive SMS Text Messages with Raspberry Pi and SIM800 GSM Board, we connected up an Itead Raspberry Pi GSM Board (SIM800) from ModMyPi and configured Gammu to send and receive SMS text messages from the command line.
In this post, we will install and configure Gammu-smsd to provide a MySQL database interface. This allows for more control and easier access to send and receive SMS text messages on your Raspberry Pi.
The Gammu-smsd software runs as a service so will almost immediately update the MySQL table with incoming messages to the inbox and send new messages in the outbox. This post assumes that you have a GSM modem already working with Gammu.
Let’s start by installing and configuring MySQL on Raspbian. It will actually install MariaDB, which is a fork of MySQL that will always remain as free Open Source software. We’ll also secure the MySQL configuration and install phpMyAdmin to provide a simple method of accessing the MySQL tables.
sudo apt-get update
sudo apt-get install mysql-server mysql-client
With the database installed, we now need to secure it. By default there is no password securing the database.
The current password for root is blank, so just press enter. All questions are ‘y’ for yes. Enter a new secure password for root, and then again to confirm.
Now that MySQL is installed and secured, we need a new database for Gammu-smsd. Let’s log into our MySQL server using the MySQL client and create a database for our SMS text messages. The following command will load the MySQL client and ask for root password that you just set.
sudo mysql -u root -p
The Gammu-smsd MySQL page suggests the following configuration of the database. Please change the password in the first line to something secure. The console output is shown below.
GRANT USAGE ON *.* TO 'smsd'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON `smsd`.* TO 'smsd'@'localhost';
CREATE DATABASE smsd;
The privileges need to be re-read from the database before the new user will work. The following command entered into the MySQL client will achieve this.
Now that this has been completed, we can exit the MySQL client.
The next step will be to install phpMyAdmin. If you are happy to use the MySQL client for writing SQL select statements to check for SMS text messages, this step may not be necessary for you. The following command will install phpMyAdmin. Select apache2 using space bar and press tab to move to OK, press enter.
apt-get install phpmyadmin
phpMyAdmin will require a database itself, which can be automatically created by the tool. Select Yes when the following box appears.
Enter a password for the phpmyadmin database user. As you already have the root password and the smsd password, this could be left blank for a random password.
Let’s check that phpMyAdmin is working correctly. You’ll need the IP Address of the Raspberry Pi. Enter the following command to find out what it is.
In a web browser on your computer, enter http://<IP Address>/phpmyadmin
For example, if the IP Address is 192.168.0.30, the URL would be http://192.168.0.30/phpmyadmin
Log in to phpMyAdmin with the username that we created for Gammu-smsd called smsd and the password that you entered in the SQL command. Once logged in, the smsd database will appear on the left, but it will not have any tables yet.
We can create the required tables once the Gammu-smsd software is installed. Let’s install Gammu-smsd now, although I have noticed that the install currently ends with an error. The reason is that the install tries to start the software before it is configured. We can clear the error after configuration.
sudo apt-get install gammu-smsd
Don’t worry, we will edit the configuration file next. Nano is a friendly text editor in the console.
sudo nano /etc/gammu-smsdrc
The configuration I am using with the SIM800 board is as follows. Please remember to change the password for the smsd database user to the secure password you set earlier.
After the file has been changed, use the keyboard combination Ctrl-o to write out the file, and press enter to accept the filename.
Next we need to create the default database tables for Gammu-smsd to use. The error will not be resolved until the tables are created. The great news is that it’s easy to create them.
sudo gunzip mysql.sql.gz
sudo mysql -uroot -p smsd < mysql.sql
The first line will change to the directory holding the default Gammu-smsd MySQL database file. The second line will uncompress the mysql.sql.gz file. The third line will import the SQL statements in the mysql.sql file. Enter the root database password that you set previously. The fourth command will change back to the home directory /home/pi.
In order to resolve the install error, running the following command should complete the install and start Gammu-smsd. If it fails, it’s either because of a configuration file problem or connecting to the database.
sudo apt-get install
If there are still problems, check the configuration file again for errors. The above command can be run again to check everything is working correctly and complete the install process. If you’re stuck, running
sudo gammu-smsd --config /etc/gammu-smsdrc will show the full error message. If it just displays the log file location, it’s now working, press Ctrl-c to quit and run
sudo apt-get install.
In phpMyAdmin, we can see there are now database tables. If you send an SMS text message from your mobile phone to your GSM modems SIM card mobile number, it should appear in the inbox table. You may need to click the refresh link to update the rows displayed. Scrolling the web page across should show you all the details about the SMS text message, including the decoded text.
Now let’s send an SMS text message from the Raspberry Pi to your mobile phone. The following command will write a row into the MySQL database outbox table, but please change the xxxxxxxxx to your mobile phone number. Once the SMS text message is sent, it will disappear from the outbox table and appear in the sentitems table.
echo "Sending an SMS from my Raspberry Pi" | sudo gammu-smsd-inject TEXT 00447xxxxxxxxx
Hopefully you have been able to send and receive an SMS text message using your Raspberry Pi, Gammu-smsd, and MySQL.