Preamble
There seems to be little mention here of PhpMyAdmin , which is a browser based way of accessing MySQL databases. It can be used on the host computer browser by ‘localhost/phpmyadmin’ and from other computers on the same network using the local IP address of the computer with the database followed by’/phpmyadmin’. This method of access allows rapid construction of databases, modification and editing of data.
With reference to the present discussion it can access the MySQL ‘internal’ database and allow a user to be added to facilitate remote access by program or command line. A small modification to /etc/mysql/my.cnf is the only other requirement.
Several other bits are needed to get started with phpmyadmin as follows:-
Installation of apache2 webserver
Using root terminal type in these 5 commands. (or prefix with sudo)
If you do not use PERL, enter only the first line.
apt-get install apache2
apt-get update
apt-get install libapache2-mod-perl2
a2enmod cgi
service apache2 restart
This sets up apache2 with PERL support. If you are a python programmer, I am sure you can find the equivalent bindings via Google.
Test by typing localhost in your pi browser or your pi's ip address in a browser on a computer or tablet on the same network.
Put html files in /var/www/html/ and perl files in /usr/lib/cgi-bin/.
Remember to set execute permissions for perl files to 'anyone'
Installation of MySQL and phpmyadmin
The following loads mysql, but as you are at this post you probably have it already installed.
Using root terminal
(Set a password when requested)
apt-get install mysql-server
Install PHP5 and phpmyadmin
(Set a password when requested)
apt-get install php5 libapache2-mod-php5
apt-get install phpmyadmin
Now add the following line (as root) to file /etc/apache2/apache2.conf
Include /etc/phpmyadmin/apache.conf
Set up new user for mysql
Type
http://localhost/phpmyadmin into your browser on the Pi with the databases or
http://ipaddress/phpmyadmin from a remote pc on the same network. Log in with the password created earlier.
On the left you will see a list of databases. Click on the ‘+’ by mysql, then on the word ‘user’ at the bottom of the list. In the other pane the current list of users is displayed. Note that the password is encrypted. Choose ‘copy’ on one of the rows. The insert page appears and you just need to change some entries as follows:-
In ‘Host’ replace with something like ‘192.168.1.%’ where the 192.168.1 are the numbers used in your network. ‘%’ is a wildcard which allows any computer on your network access to the databases.
In ‘User’ give a username . I use pi.
If you want to change the password, select ‘PASSWORD’ from the function box on the ‘Password’ row and enter your new password in the ‘Value’ box.
At the bottom of the page click the ‘Go’ button.
Then click ‘Browse’ on the top menu and the list of users will include your addition.
Finally
Edit /etc/mysql/my.cnf as root.
Find ‘bind_address=’ and change the value to 0.0.0.0
If you do not have the latest mysql you may need to comment out this line (by adding the # at the front)
#skip-networking
Conclusion
You should now be able to access the databases on the host computer either by program or command line from a remote pc.
The code for PERL for example is
my $dbh = DBI->connect("DBI:mysql:database=databasename;host=ipaddress of host computer","username","password") or die " No connection to database \n";
There is more information on Raspberry Pi set up with Jessie and information on my Pi projects at
http://www.torbeta.com.
