Connecting to the outside world
Opening the database to the outside world
The default setup of MySQL/MariaDB will only accept connections from the machine hosting the database. This is okay if your requirements only require one machine, but in most cases the database server will be accessed by other machines. One of the joys of the Raspberry Pi is that you can use them for one task, such as a central database server and then use other machines (such as other Raspberry Pis) to feed it.
You can change this default behaviour by setting a variable in one of the database's configuration files.
On a Linux machine (like a Raspberry Pi) it is located here:
/etc/mysql/my.cnf
The required variable is called the
bind-address. It is only relevant to the database server and not client applications running on the server. If it is visible to a client application you will get an error:
mysql: unknown variable 'bind-address=[ip]'
To add this bind address type:
sudo nano /etc/mysql/my.cnf
Look through the file, if there is an existing entry of:
bind-address = 127.0.0.1
Comment this out by putting a # symbol in front of it.
#bind-address = 127.0.0.1
Check if there is a section marked
[server].
If there is no[server] entry, at the bottom of the file add the section:
[server]
Then add
bind-address = 0.0.0.0
This part of the file will look like:
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
#bind-address = 127.0.0.1
[server]
bind-address = 0.0.0.0
This will allow access from other machines.
You will need to restart the server:
sudo service mysql restart
Note: If the host machine has any firewall settings active, you may need to open the MySQL port number 3306.
Adding external users
First check what users you have set up.
Log in to the database server locally:
sudo mysql -uroot -p
And enter your database password.
To check the current users, type:
SELECT User, Host FROM mysql.user;
+-------------+-------------+
| User | Host |
+-------------+-------------+
| datareader | % |
| localreader | localhost |
| root | localhost |
+-------------+-------------+
If you have followed the instructions so far, you should have something similar.
If not, set the default database to be datastorage and a data reader user:
create user 'datareader'@'%' identified by '<data reader password>';
grant select on datastorage.* to'datareader';
Connect to the database using Python
The first task is to install the connector on the machine.
Pip (and Pip3) are installers similar to the APT repository installers on Debian derived Linux but specifically for Python 2 and Python 3. Some installations use pip for Python 3, but the majority use pip for Python 2 and pip3 for Python 3. If you have used pip and your Python program cannot find something, try pip3.
From the comand line on the machine to be used:
pip3 install mysql-connector-python
This will install the required connectors to access MySQL
Test Program
import mysql.connector
print("Test database reader")
cnx = mysql.connector.connect(host="<ip address>"
user="datareader",
passwd="<password>",
port = 3306,
db="datastorage")
print("Connected")
mycursor=cnx.cursor()
sql="SELECT * FROM datavalues"
mycursor.execute(sql)
for row in mycursor.fetchall():
result=""
for field in row:
result+=", " +str(field)
print(result)
cnx.close()
The program first imports the connector.
It then creates the connection with the supplied parameters (database server address, user name, password, port number, database).
cnx = mysql.connector.connect(host="<ip address>"
user="datareader",
passwd="<password>",
port = 3306,
db="datastorage")
It then creates a cursor on that connection.
mycursor=cnx.cursor()
Executes a SQL statement using that cursor
sql="SELECT * FROM datavalues"
mycursor.execute(sql)
Prints the returned data.
for row in mycursor.fetchall():
result=""
for field in row:
result+=", " +str(field)
print(result)
And finally closes the connection.
cnx.close()
This is an example of the output:
Test database reader
Connected
, 1, root@localhost, test, 1.0, 2019-02-27 11:06:09
, 2, root@localhost, test, 2.0, 2019-02-27 11:06:16
, 3, root@localhost, test, 3.0, 2019-02-27 11:06:23
References
