Skip to navigation

Setting up remote access to a MySQL database

In the previous article about setting up MySQL on a Banana Pi I covered the basics of setting up MySQL, and I used a script called db-script.py to access the database. The next step is to set up remote access so that the database can be used from a second Banana Pi. Db-script.py will run on that Pi and access the database on the original Pi via the local network. 

Set up the MySQL client

Set up another Banana Pi with Raspbian, and give it a static IP address.  In my case, the server's IP address is 192.168.0.8, so I set the client Pi's IP address to 192.168.0.9.  I have a monitor, keyboard and mouse connected to the server, so I'm going to use ssh to login to the client.

On the server, modify the script used to access the database.  On line 73 of db-script.py, replace 'localhost' with the IP address of your MySQL server:

    conn = MySQLdb.connect('192.168.0.8', 'db_user', 'mypassword', 'my_db')

Save this modification, and transfer the file to the client Pi using the scp command:

scp ./db-script.py bananapi@192.168.0.9:./

Db-script.py will be copied to /home/bananapi on the second Pi.  Log into the client node via ssh, and make the script executable:

ssh bananapi@192.168.0.9
chmod +x db-script.py

Install the MySQL client and Python libraries on the client:

sudo apt-get install python-mysqldb mysql-client

Change settings on the MySQL server

You need to edit the settings for the MySQL server to allow it to accept requests from other IP addresses besides the loopback address.  On the Pi running the MySQL server, open the MySQL configuration file in a text editor:

sudo leafpad  /etc/mysql/my.cnf 

Look for the bind address and change it to 0.0.0.0: 

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0

Restart the MySQL server:

sudo service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Change the database user settings to allow remote access for user db_user:

mysql -u root -p

mysql> GRANT ALL ON my_db.* TO 'db_user'@'192.168.0.9' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

The FLUSH PRIVILEGES command commits the changes to the datbase.  

Run the script on the client

Log into the client node via ssh:

ssh bananapi@192.168.0.9

Now run the database script on the client:

./db-script.py
Setting up database...
Dropped tables

Created tables

Added User

('Steve', 'password', 'email@addr.com', datetime.datetime(2014, 8, 30, 0, 0))
DB Connection closed

You should see the same output as when you ran the script on the server.  

Share this page:

comments powered by Disqus