Preparing the Environment
1. From your computer, clone the repository of https://github.com/shaneyake/shaneyake/tree/master and save the downloaded files to your computer for later use.
Unless otherwise noted, the following actions are to be performed via the terminal connected to the Pi.
2. pi@raspberrypi:~ $ sudo apt-get install python-dev libmysqlclient-dev
4. pi@raspberrypi:~ $ sudo pip install MySQL-python
5. pi@raspberrypi:~ $ git clone https://github.com/eclipse/paho.mqtt.python.git
6. ls
The "paho.mqtt.python" is the folder created at step-5.
7. pi@raspberrypi:~ $ cd paho.mqtt.python
Before proceed further, do the followings:
Reference:
https://askubuntu.com/questions/683601/how-to-upgrade-python-setuptools-12-2-on-ubuntu-15-04
Check Python setuptools version
$ python
>>> import pkg_resources
>>> r = pkg_resources.require(["setuptools"])[0]
>>> print("setuptools version: %s" % r.version)
Note, when done, use ctrl+z to exit the Python interpreter.
Upgrade setuptools
First, remove the installed repository version
pi@raspberrypi:~/paho.mqtt.python $ sudo apt-get remove python-setuptools
If necessary, install pip again.
pi@raspberrypi:~/paho.mqtt.python $ wget https://bootstrap.pypa.io/get-pip.py
pi@raspberrypi:~/paho.mqtt.python $ sudo -H python get-pip.py
Note, it will take a while for the below message to appear.
When done, install setuptools via pip
pi@raspberrypi:~/paho.mqtt.python $ sudo -H pip install -U pip setuptools
Check again the Python setuptools version
$ python
>>> import pkg_resources
>>> r = pkg_resources.require(["setuptools"])[0]
>>> print("setuptools version: %s" % r.version)
The setuptools version is now upgraded from 5.5.1 to 36.2.7.
Use ctrl-z to exit the Python interpreter.
8. From within the "paho.mqtt.python" folder, run sudo python setup.py install.
pi@raspberrypi:~/paho.mqtt.python $ sudo python setup.py install
pi@raspberrypi:~/paho.mqtt.python $ sudo python setup.py install zip_safe flag not set; analyzing archive contents... Installed /home/pi/paho.mqtt.python/.eggs/pytest_runner-2.11.1-py2.7.egg running install running bdist_egg running egg_info creating src/paho_mqtt.egg-info writing src/paho_mqtt.egg-info/PKG-INFO writing top-level names to src/paho_mqtt.egg-info/top_level.txt writing dependency_links to src/paho_mqtt.egg-info/dependency_links.txt writing manifest file 'src/paho_mqtt.egg-info/SOURCES.txt' reading manifest file 'src/paho_mqtt.egg-info/SOURCES.txt' reading manifest template 'MANIFEST.in' writing manifest file 'src/paho_mqtt.egg-info/SOURCES.txt' installing library code to build/bdist.linux-armv6l/egg running install_lib running build_py creating build creating build/lib.linux-armv6l-2.7 creating build/lib.linux-armv6l-2.7/paho copying src/paho/__init__.py -> build/lib.linux-armv6l-2.7/paho creating build/lib.linux-armv6l-2.7/paho/mqtt copying src/paho/mqtt/publish.py -> build/lib.linux-armv6l-2.7/paho/mqtt copying src/paho/mqtt/__init__.py -> build/lib.linux-armv6l-2.7/paho/mqtt copying src/paho/mqtt/client.py -> build/lib.linux-armv6l-2.7/paho/mqtt copying src/paho/mqtt/matcher.py -> build/lib.linux-armv6l-2.7/paho/mqtt copying src/paho/mqtt/subscribe.py -> build/lib.linux-armv6l-2.7/paho/mqtt creating build/bdist.linux-armv6l creating build/bdist.linux-armv6l/egg creating build/bdist.linux-armv6l/egg/paho creating build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/mqtt/publish.py -> build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/mqtt/__init__.py -> build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/mqtt/client.py -> build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/mqtt/matcher.py -> build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/mqtt/subscribe.py -> build/bdist.linux-armv6l/egg/paho/mqtt copying build/lib.linux-armv6l-2.7/paho/__init__.py -> build/bdist.linux-armv6l/egg/paho byte-compiling build/bdist.linux-armv6l/egg/paho/mqtt/publish.py to publish.pyc byte-compiling build/bdist.linux-armv6l/egg/paho/mqtt/__init__.py to __init__.pyc byte-compiling build/bdist.linux-armv6l/egg/paho/mqtt/client.py to client.pyc byte-compiling build/bdist.linux-armv6l/egg/paho/mqtt/matcher.py to matcher.pyc byte-compiling build/bdist.linux-armv6l/egg/paho/mqtt/subscribe.py to subscribe.pyc byte-compiling build/bdist.linux-armv6l/egg/paho/__init__.py to __init__.pyc creating build/bdist.linux-armv6l/egg/EGG-INFO copying src/paho_mqtt.egg-info/PKG-INFO -> build/bdist.linux-armv6l/egg/EGG-INFO copying src/paho_mqtt.egg-info/SOURCES.txt -> build/bdist.linux-armv6l/egg/EGG-INFO copying src/paho_mqtt.egg-info/dependency_links.txt -> build/bdist.linux-armv6l/egg/EGG-INFO copying src/paho_mqtt.egg-info/not-zip-safe -> build/bdist.linux-armv6l/egg/EGG-INFO copying src/paho_mqtt.egg-info/top_level.txt -> build/bdist.linux-armv6l/egg/EGG-INFO creating dist creating 'dist/paho_mqtt-1.3.0-py2.7.egg' and adding 'build/bdist.linux-armv6l/egg' to it removing 'build/bdist.linux-armv6l/egg' (and everything under it) Processing paho_mqtt-1.3.0-py2.7.egg creating /usr/local/lib/python2.7/dist-packages/paho_mqtt-1.3.0-py2.7.egg Extracting paho_mqtt-1.3.0-py2.7.egg to /usr/local/lib/python2.7/dist-packages Adding paho-mqtt 1.3.0 to easy-install.pth file Installed /usr/local/lib/python2.7/dist-packages/paho_mqtt-1.3.0-py2.7.egg Processing dependencies for paho-mqtt==1.3.0 Finished processing dependencies for paho-mqtt==1.3.0
Note,
If python3 is used, change the command from "sudo python setup.py install" to "sudo python3 setup.py install".
9. Install WinSCP (https://winscp.net/eng/index.php) or FileZilla (https://filezilla-project.org/) to your computer (note, if either one of them has been installed, you could skip this step).
10. Unzip the cloned file from step 1 and locate the "mqtt_websockets.py" and "SimpleWebSocketServer.py".
11. Use WinSCP to copy "mqtt_websockets.py" and "SimpleWebSocketServer.py" to your home directory on Raspberry Pi (in my case, my home directory is at "/home/pi").
Before.
After (on WinSCP).
After (on linux).
A quick note on navigating the Linux directory using WinSCP
In Linux, the "~" symbol denotes the home directory of the current login user.
In WinSCP, the "~" is located under /home/pi (when pi is the login username).
12. Modify "mqtt_websockets.py".
Note, this step could be done on your local computer then copy the modified file to the Raspberry Pi or on the Raspberry Pi directly. Below is the original, unmodified version of the code.
#Imports from SimpleWebSocketServer import WebSocket, SimpleWebSocketServer, SimpleSSLWebSocketServer import signal, sys, logging from optparse import OptionParser import MySQLdb as mdb import string import datetime import time import thread import json from collections import defaultdict import paho.mqtt.client as mqtt #websockets_port=#WEBSOCKETS_PORT# websockets_port=#WEBSOCKETS_PORT# #mqtt_server="#MQTT_SERVER#" #mqtt_port=#MQTT_PORT# #mqtt_username="#MQTT_USERNAME#" #mqtt_password="#MQTT_PASSWORD#" mqtt_server="#MQTT_SERVER#" mqtt_port=#MQTT_PORT# mqtt_username="#MQTT_USERNAME#" mqtt_password="#MQTT_PASSWORD#" #mysql_db='#MYSQL_DB#' #mysql_username='#MYSQL_USERNAME#' #mysql_password='#MYSQL_PASSWORD#' #mysql_server='#MYSQL_SERVER#' mysql_db='#MYSQL_DB#' mysql_username='#MYSQL_USERNAME#' mysql_password='#MYSQL_PASSWORD#' mysql_server='#MYSQL_SERVER#' #Variables var_connections_id = {} delchars = ''.join(c for c in map(chr, range(256)) if not c.isalnum()) #Mysql class DB: conn = None def connect(self): self.conn = mdb.connect(mysql_server, mysql_username, mysql_password, mysql_db) self.conn.autocommit(True) print("MySQL Connected") def query(self, sql): try: cursor = self.conn.cursor() cursor.execute(sql) except (AttributeError, mdb.OperationalError): self.connect() cursor = self.conn.cursor() cursor.execute(sql) return cursor db = DB() db.connect() db2 = DB() db2.connect() db3 = DB() db3.connect() db4 = DB() db4.connect() #MQTT on connect callback def on_connect(mqttc, userdata, flags, rc): print("MQTT Connected...") #MQTT on message callback def on_message(mqttc, userdata, msg): #print(msg.topic+" "+str(msg.qos)+" "+str(msg.payload)) global var_connections_id msg_topic_sql={} msg_topic_sql[0]="" msg_topic_sql[1]="" msg_topic_sql[2]="" msg_topic_sql[3]="" msg_topic_sql[4]="" msg_topic_sql[5]="" msg_topic_sql[6]="" msg_topic_sql[7]="" msg_topic_sql[8]="" msg_topic_sql[9]="" temp_msg_topic = string.split(str(msg.topic),'/') for i in range(len(temp_msg_topic)): msg_topic_sql[i]=temp_msg_topic[i] sql4 ="SELECT `connections_id` FROM `websockets_topics` WHERE (`topic1`='"+str(msg_topic_sql[0])+"') AND `topic2`='"+str(msg_topic_sql[1])+"' AND `topic3`='"+str(msg_topic_sql[2])+"' AND `topic4`='"+str(msg_topic_sql[3])+"' AND `topic5`='"+str(msg_topic_sql[4])+"' AND `topic6`='"+str(msg_topic_sql[5])+"' AND `topic7`='"+str(msg_topic_sql[6])+"' AND `topic8`='"+str(msg_topic_sql[7])+"' AND `topic9`='"+str(msg_topic_sql[8])+"' AND `topic10`='"+str(msg_topic_sql[9])+"';" cur4 = db4.query(sql4) numrows4 = int(cur4.rowcount) if(numrows4>=1): results = cur4.fetchall() for row in results: try: temp_id =row[0] client=var_connections_id[temp_id][0] temp_send_ws={} temp_send_ws['topic']=str(msg.topic) #temp_send_ws['message']=str(msg.payload) temp_send_ws['message']=msg.payload.decode("UTF-8") temp_send_ws['mode']="mqtt" print(msg.payload.decode("UTF-8")) print(msg.payload.decode("UTF-8")) print(str(json.dumps(temp_send_ws))) client.sendMessage(str(json.dumps(temp_send_ws))) except Exception as n: print(n) #MQTT Client Function def start_mqtt(): global mqttc mqttc = mqtt.Client() mqttc.on_connect = on_connect mqttc.on_message = on_message mqttc.username_pw_set(mqtt_username, mqtt_password) mqttc.connect(mqtt_server, mqtt_port, 60) mqttc.loop_forever() thread.start_new_thread( start_mqtt, (), ) class SimpleChat(WebSocket): def handleMessage(self): if self.data is None: self.data = '' #print(self.data) global var_connections_id global delchars print('New Message') print(self.data.decode("UTF-8")) jason_message =json.loads(self.data.decode("UTF-8")) #jason_message =json.loads(str(self.data)) print(jason_message) #print('Hello2') try: sql2 ="SELECT `logged_in`,`username_md5` FROM `websockets_conlist` WHERE `connections_id`='"+str(id(self))+"';" cur2 = db2.query(sql2) row2 = cur2.fetchone() var_loggedin=row2[0] var_username=row2[1] except: print("error 1") if (var_loggedin==1):#if Allowed if jason_message['mode']=="login": try: temp_send_ws4={} temp_send_ws4['mode']="system" temp_send_ws4['ws_token']=jason_message['ws_token'] self.sendMessage(str(json.dumps(temp_send_ws4))) except Exception as n: print(n) elif jason_message['mode']=="subscribe": var_data_on=0 var_temp_data = str(jason_message['topic']) if var_temp_data!='': sql ="SELECT * FROM websockets_acls WHERE username = '"+str(var_username)+"'&&rw >= 1" cur = db.query(sql) numrows = int(cur.rowcount) if(numrows>=1): results = cur.fetchall() for row in results: data_topic_db = row[2] data_topic_db = string.split(data_topic_db,'/') data_topic_message = string.split(var_temp_data, '/') if row[2]=="#": var_data_on=1 break elif row[2]==var_temp_data: var_data_on=1 break elif "guest"==data_topic_message[0]: var_data_on=1 break else: var_data_on=0 else: var_data_on=0 if var_data_on==1: msg_topic_sql={} msg_topic_sql[0]="" msg_topic_sql[1]="" msg_topic_sql[2]="" msg_topic_sql[3]="" msg_topic_sql[4]="" msg_topic_sql[5]="" msg_topic_sql[6]="" msg_topic_sql[7]="" msg_topic_sql[8]="" msg_topic_sql[9]="" temp_msg_topic = string.split(str(var_temp_data),'/') for i in range(len(temp_msg_topic)): msg_topic_sql[i]=temp_msg_topic[i] sql3="INSERT INTO `websockets_topics`(`id`, `connections_id`, `topic1`, `topic2`, `topic3`, `topic4`, `topic5`, `topic6`, `topic7`, `topic8`, `topic9`, `topic10`) VALUES (NULL,'"+str(id(self))+"','"+str(msg_topic_sql[0])+"','"+str(msg_topic_sql[1])+"','"+str(msg_topic_sql[2])+"','"+str(msg_topic_sql[3])+"','"+str(msg_topic_sql[4])+"','"+str(msg_topic_sql[5])+"','"+str(msg_topic_sql[6])+"','"+str(msg_topic_sql[7])+"','"+str(msg_topic_sql[8])+"','"+str(msg_topic_sql[9])+"');" cur3 = db3.query(sql3) mqttc.subscribe(var_temp_data) print("Subcribe Topic: "+var_temp_data) try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="SUBSCRIBED_GRANTED" self.sendMessage(str(json.dumps(temp_send_ws3))) except: print("Error 2") else: try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="SUBSCRIBED_DENIED" self.sendMessage(str(json.dumps(temp_send_ws3))) except: print("Error 3") elif jason_message['mode']=="publish": try: var_temp_data = str(jason_message['topic']) var_temp_data2 = str(jason_message['message']) #print(jason_message['message']) #print(str(json.dumps(jason_message['message']))) var_data_on=0 sql ="SELECT * FROM websockets_acls WHERE username = '"+str(var_username)+"'&&rw >= 2" cur = db.query(sql) numrows = int(cur.rowcount) if(numrows>=1): if var_temp_data!='': results = cur.fetchall() for row in results: data_topic_db = row[2] data_topic_db = string.split(data_topic_db,'/') data_topic_message = string.split(var_temp_data, '/') if row[2]=="#": var_data_on=1 break elif row[2]==var_temp_data: var_data_on=1 break elif "guest"==data_topic_message[0]: var_data_on=1 break if(var_data_on==1): print("Published: "+var_temp_data) try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="PUBLISHED_GRANTED" self.sendMessage(str(json.dumps(temp_send_ws3))) print(jason_message['message']) mqttc.publish(var_temp_data, jason_message['message']) except: print("Error 4") else: try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="PUBLISHED_DENIED" self.sendMessage(str(json.dumps(temp_send_ws3))) except: print("Error 5") except Exception as n: #elif "#topic#:" print n print ("Error elif #topic#:") else: print("Error"+str(self.data)) if (var_loggedin==0): #if busy sent=0 if jason_message["mode"]=="login": var_temp_data = str(jason_message['ws_token']) var_sql_q = var_temp_data.translate(None, delchars) sql ="SELECT * FROM logins WHERE WS_token = '"+var_sql_q+"'&&active = 1" cur = db.query(sql) numrows = int(cur.rowcount) if(numrows==1): row = cur.fetchone() if(row[3]>=time.strftime('%Y-%m-%d %H:%M:%S')): print("Allowed " + str(id(self))) print("IP: " + str(self.address[0])) sql ="UPDATE `websockets_conlist` SET `username_md5`='"+str(row[1])+"',`logged_in`=1 WHERE `connections_id`='"+str(id(self))+"';" cur2 = db2.query(sql) try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="ACCESS_GRANTED" self.sendMessage(str(json.dumps(temp_send_ws3))) sent=1 except Exception as n: print(n) else: #print "NOOPE" try: temp_send_ws3={} temp_send_ws3['mode']="system" temp_send_ws3['status']="ACCESS_DENIED" self.sendMessage(str(json.dumps(temp_send_ws3))) sent=1 except Exception as n: print(n) def handleConnected(self): print("Connected " + str(self)) global var_connections_id #Make Sure no old rows sql ="DELETE FROM `websockets_conlist` WHERE `websockets_conlist`.`connections_id`='"+str(id(self))+"';" cur2 = db2.query(sql) print (id(self)) print (var_connections_id) sql ="INSERT INTO `websockets_conlist` (`id`, `connections_id`, `username_md5`, `connected`,`IP`) VALUES (NULL, '"+str(id(self))+"', '', '1','"+str(self.address[0])+"');" cur2 = db2.query(sql) if id(self) in var_connections_id: del var_connections_id[id(self)] print("Deleted: var_connections_id") var_connections_id[id(self)]=[] var_connections_id[id(self)].append(self) print("Connected FOR GOOD") try: temp_send_ws2={} temp_send_ws2['mode']="login" self.sendMessage(str(json.dumps(temp_send_ws2))) except Exception as n: print(n) def handleClose(self): print("Closed " + str(self)) global var_connections_id sql ="DELETE FROM `websockets_conlist` WHERE `websockets_conlist`.`connections_id`='"+str(id(self))+"';" cur2 = db2.query(sql) sql ="DELETE FROM `websockets_topics` WHERE `websockets_topics`.`connections_id`='"+str(id(self))+"';" cur2 = db2.query(sql) if id(self) in var_connections_id: del var_connections_id[id(self)] print(var_connections_id) print("Closed FOR GOOD") if __name__ == "__main__": cls = SimpleChat server = SimpleWebSocketServer('', websockets_port, cls) def close_sig_handler(signal, frame): server.close() sys.exit() signal.signal(signal.SIGINT, close_sig_handler) server.serveforever()
Note, Make a copy of the original codes, comment out the original ones and make changes to the new ones so that it's easier to find the differences.
For the entry for "mysql_password", follow the instructions below.
- Login to phpMyAdmin, then click on the "Users" tab.
- Click on "Add user" then do the followings:
- Enter "websockets" into the blank field next to User name.
- Select "localhost" from the Host drop down menu.
- Click on the "Generate" button next to "Generate password:" to generate the password.
- Copy the password generated above in phpMyAdmin to the field next to "mysql_password=" in mqtt_websockets.py".
- Use ctrl-x to save the file.
- In phpMyAdmin, scroll down and click on SELECT, INSERT, UPDATE, DELETE, and FILE under Data to enable these privileges for the account (note, websockets) to be created.
- Click on the "Go" button on the bottom right corner of phpMyAdmin to create the account.
- A new account for user - "websockets" is now created (see below pic).
Create MQTT user account in phpMyAdmin
- Open a new terminal.
- Change the working directory to where the "mosquitto-auth-plug" is located.
pi@raspberrypi:~ $ cd mosquitto-1.4.14/mosquitto-auth-plug
pi@raspberrypi:~/mosquitto-1.4.14/mosquitto-auth-plug $ ./np
Note, If you encounter the "./np: error while loading shared libraries: libcrypto.so.1.1: cannot open shared object file: No such file or directory" error, follow the instruction below to solve it.
--------------------------------------------------------------------------------------------------------------------------
Solution to the problem of "libcrypto.so.1.1: cannot open shared object file: No such file or directory"
pi@raspberrypi:~/mosquitto-1.4.14/mosquitto-auth-plug $ sudo ln -s /usr/local/lib/libcrypto.so.1.1 /usr/lib/libcrypto.so.1.1
error while loading shared libraries: libcrypto.so.1.1
https://serverfault.com/questions/818445/error-while-loading-shared-libraries-libcrypto-so-1-1
How to completly remove openssl installed via sources in Ubuntu? [closed]
https://stackoverflow.com/questions/29727433/how-to-completly-remove-openssl-installed-via-sources-in-ubuntu?rq=1
--------------------------------------------------------------------------------------------------------------------------
After the problem is solved, issue again the ./np command.
pi@raspberrypi:~/mosquitto-1.4.14/mosquitto-auth-plug $ ./np
pi@raspberrypi:~/mosquitto-1.4.14/mosquitto-auth-plug $ ./np Enter password: Re-enter same password: PBKDF2$sha256$901$cujr2cTzJ7w/fk72$9hNbQ9ehxXGQUOzJxi3hBFwa73B8uhlL
Copy and paste the password for mqtt_password (in this case, "12345678") to the Enter password: and Re-enter same password: fields. This will generate an encrypted version of the password as the last line shown above.
Setting up the MQTT user account in phpMyAdmin
Create a MQTT user
1. Login to phpMyAdmin.
2. Click on HAC, then click on mqtt_users.
3. Click on Insert. Enter the username and the encrypted password. Enter 0 for super.
Note, Setting super to 1 will bypass the ACLS (mqtt_acls) and the user account will have access to ALL the MQTT topics on the server. Setting super to 0 will subject the user account to ACLS. For production, always set super to 0.
4. Click Go to save it.
Create the ACLS for the newly created MQTT user
1. Click on "mqtt_acls" on the left column.
2. Click on Insert.
3. Enter the data as shown below.
Note that for the rw field, 1 means read, 2 means write.
4. Click Go to save.
Click on HAC on the left to show the status of the 2 tables. Now that there is 1 entry for "mqtt_acls" and 1 entry for "mqtt_users".
Primary Reference:
HAC 6. Mosquitto MQTT ACL
https://youtu.be/YGOBKE2xbEE?list=PL-x6E_rBMvai4l2akwY-VaQOto5rm7p7Q
Other References:
HAC 7. MQTT to Webpages
https://youtu.be/n3F7auYZqPY
HAC 7. MQTT to Webpages Part 2
https://youtu.be/zjIcYwt_kXw
Store Messages From Mosquitto MQTT Broker Into SQL Database
http://www.instructables.com/id/Store-Messages-From-Mosquitto-MQTT-Broker-Into-SQL/
How-To Get Started with Mosquitto MQTT Broker on a Raspberry Pi
https://youtu.be/AsDHEDbyLfg
mqtt message store into database
https://youtu.be/P6pUGky5R40
Store messages from Mosquitto MQTT broker into SQL Database
http://ediy.com.my/blog/item/143-store-messages-from-mosquitto-mqtt-broker-into-sql-database
How to save IoT Sensor Data from MQTT into a SQL Database
https://youtu.be/6TwJK-cfQb4
Store MQTT Data from Sensors into SQL Database
https://iotbytes.wordpress.com/store-mqtt-data-from-sensors-into-sql-database/
[教學] 中華電信 ZyXEL P874 搭配 Synology DS216j 架設家用 VPN Server
https://dotblogs.com.tw/swordnzen/2016/10/16/225702
No comments:
Post a Comment