- A Mind for Numbers
- Accelerate
- Agile Project Management for Dummies
- Algorithms to Live By
- Atomic Habits
- AWS Certified Cloud Practitioner Study Guide
- Banking on It
- Brexitland
- Build Your Dream Network
- Bulletproof SSL and TLS
- Business Analysis
- Collapse
- CompTIA Security+ Practice Tests
- CompTIA Security+ Study Guide
- Corporate Rebels
- Countdown to Zero Day
- Creative Acts for Curious People
- Creative DIY Microcontroller Projects with TinyGo...
- Cryptanalysis
- Crypto Trader
- Cryptography
- Culture Code
- Daniel Goleman Omnibus
- Deep and deliberate delegation
- Dhl
- Drive
- Effective Python
- Every Tool's a Hammer
- Exam Ref AZ-900 Microsoft Azure Fundamentals
- Expert Scripting and Automation for SQL Server...
- Fifty Quick Ideas to Improve Your User Stories
- Fixing Your Scrum
- Fundamentals of ServiceNow Administration and...
- Future Leader
- Future of Violence - Robots and Germs, Hackers and...
- GCHQ Puzzle Book
- Getting Things Done
- Harvard Business Review manager's handbook
- Hooked
- How Google Works
- How to Take Smart Notes
- How to Win Friends and Influence People
- HTML and CSS
- I Think, Therefore I Am
- Itsm Value Streams : Transform Opportun
- JavaScript and jQuery
- Kill It with Fire
- Leaders Eat Last
- Leading change
- Leading Without Authority
- Lean Thinking
- LONDON'S UNDERGROUND.
- Managing Successful Projects Prince2
- Managing Successful Projects with PRINCE2
- Measure What Matters : OKRs
- Meteorology today
- Mindf*ck
- Modern Cryptanalysis
- Modernist cuisine at home
- Money Revolution
- Never Split the Difference
- New One Minute Manager
- Open Circuits
- Oversubscribed
- Permanent Record
- PHP 5 advanced
- Practical Docker with Python: Build, Release and...
- Practical electronics for inventors
- PRINCE2 for dummies
- Pro Python 3: Features and Tools for Professional...
- Pro SQL Server Always On Availability Groups
- Pro SQL Server on Linux: Including Container-Based...
- Professional Scrum Master Guide
- Project to Product
- Radical Simplicity
- Rules of People
- SAFe 5.0 Distilled
- Sapiens
- SEARCH INSIDE YOURSELF- TPB
- Secret Barrister
- Securing SQL Server: DBAs Defending the Database
- Site Reliability Engineering
- SQL Server 2017 Administration Inside Out
- Start with Why
- System Center Configuration Manager Current Branch...
- T-SQL Fundamentals
- Teach Yourself Electricity and Electronics,...
- Teach Yourself Setting Up a Small Business (Teach...
- Team Topologies
- The Art of Deception
- The art of invisibility
- The Chimp Paradox How Our Impulses And Emotions...
- The coaching habit
- The code book
- The Courage To Be Disliked
- The DevOps handbook
- The Epic Guide to Agile
- The Five Dysfunctions of a Team: A Leadership...
- The Go Programming Language
- The Golden Ratio
- The Introvert's Guide to the Workplace
- The Manager's Path
- The New Silk Roads: The Present and Future of the...
- The outward mindset
- The Phoenix Project
- The Professional Product Owner
- The Unicorn Project
- Turn The Ship Around!
- Visual Thinking
- Weapons of Math Destruction
- Who Moved My Cheese?
- Work Rules!
- Working Out Loud
- Writing An Interpreter In Go
- Wrong Fit, Right Fit
Using Gammu-smsd and MySQL to Send and Receive SMS Text Messages
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. The apt-get package names have changed over time, but I have left the previous names here in case it helps on your version of Linux.
sudo apt-get update
sudo apt-get install mariadb-server-10.0 mariadb-client-10.0
With the database installed, we now need to secure it. By default there is no password securing the database.
sudo mysql_secure_installation
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.
FLUSH PRIVILEGES;
Now that this has been completed, we can exit the MySQL client.
quit
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.
sudo 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.
ifconfig
In a web browser on your computer, enter http:///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 gammu
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.
cd /usr/share/doc/gammu/examples/sql
sudo gunzip mysql.sql.gz
sudo mysql -uroot -p smsd < mysql.sql
cd ~
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.
The gammu-smsd service will need restarting in order to start using the new configuration.
sudo systemctl restart gammu-smsd
If you would like to check the service output, the journalctl command will provide the most recent output.
sudo journalctl -u gammu-smsd
If there are still problems, check the configuration file again for errors. 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.
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.
For further information, please have a look at the Gammu-smsd configuration manual and the gammu-smsd-inject manual.