phpMyAdmin Works on HESA

I spent Labor Day weekend working on the Home Environmental Sensor Array on my Raspberry Pi.  I want to start reading and writing data on MySQL tables on the Raspberry Pi.  In order to make this easier, I need to use phpMyAdmin on my laptop to manage the MySQL databases on the Raspberry Pi.  There were several things I had to do to get this working.

First, phpMyAdmin tries to only connect to the MySQL server on the localhost.  I found a web page that explained how to get phpMyAdmin to connect to MySQL on another device.  Basically, I had to edit the config.inc.php file in the /etc/phpmyadmin folder.  The lines below were added after the section like it that setup the localhost connection.

$cfg['Servers'][$i]['verbose']         = 'HESA';$cfg['Servers'][$i]['host']            = 'hesa.local';$cfg['Servers'][$i]['port']            = '3306';$cfg['Servers'][$i]['connect_type']    = 'tcp';$cfg['Servers'][$i]['extension']       = 'mysqli';$cfg['Servers'][$i]['compress']        = FALSE;$cfg['Servers'][$i]['auth_type']       = 'cookie';$i++;

One thing to note is that these lines can be added to this file for every server that you want to control with this copy of phpMyAdmin.

Next, I had to go to the Raspberry Pi and tell MySQL to allow connections on the public internet connection and port 3306.  This is done by editing the /etc/mysql/my.cnf file and changing the bind-address variable in the [mysql] section to the local IP.  In my case, it looked like this:

bind-address = 192.168.0.58

Finally, I had to setup security for the users in MySQL.  I started up the mysql command line tool and executed a command like this:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Now that I am documenting all of this, it seems pretty simple. Its hard to believe that I spent so much time trying to get this to work.  Here is the end result.  Now, there is a Server Choice dropdown that shows all the servers, I can connect to from my laptop.

After getting this working, I created tables for the data that I store on the rynok.org website.  I also mostly copied data from the production site to the database on the Raspberry Pi.  The RJ comments table did not fully copy.  There is probably something wrong with the variable size for the id column.  I'll deal with that later.