### Connect MySQL RemoteDB 2

This is an example of selecting records by a remote client from a table from a MySQL Database hosted on a Raspberry Pi (RPi) server.
Setup Testenvironment
MySQL running on Raspberry Pi with IP Address 192.168.0.16
MySQL Client with IP Address 192.168.0.8
Define the MySQL Database, Users and Table
Login MySQL as root and create & define:
MySQL Database: notes
MySQL User/PW: root/*****, notesuser/nu
MySQL Table: notes with field note (varchar(255)
The MySQL commands:
$mysql -uroot -hlocalhost -p
msql>ENTER PASSWORD
msql>CREATE DATABASE notes;
msql>CREATE USER 'notesuser'@'localhost' IDENTIFIED BY 'YOURPASSWORD';
msql>GRANT ALL PRIVILEGES ON notes.* TO 'notesuser'@'localhost';
msql>FLUSH PRIVILEGES;
The MySQL database 'notes' with user 'notesuser' has been created.
To note that so far only local database access is granted for users roor and notesuser.
Setup MySQL Remote Access on the RPi
This is done by setting the bind-address in the mysql configuration.
Define in /etc/mysql/my.cnf the "bind-address" and set to 192.168.0.16 or to 0.0.0.0.
Login to MySQL as root and invoke
$service mysql reload
$service mysql restart
Grant MySQL Remote Access on the RPi
Login to MySQL as root and grant access to the users root and notesuser for the databases and tables.
This is done by telling MySQL the username and the remote IP Address.
So this allows only access from a fixed IP Address.
mysql> GRANT ALL ON *.* To root@'192.168.0.8' IDENTIFIED BY 'YOUR ROOT PASSWORD';
mysql> flush privileges;
mysql> GRANT ALL ON *.* To notesuser@'192.168.0.8' IDENTIFIED BY 'YOUR USER PASSWORD';
mysql> flush privileges;
To allow access from any IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOURUSERNAME'@'%' IDENTIFIED BY 'YOUR USERPASSWORD';
mysql> flush privileges;
B4J Testapp to select records
#Region Project Attributes
#MainFormWidth: 600
#MainFormHeight: 400
'Ensure to download and copy the mysql jdbc connector to the additional libraries folder
#AdditionalJar: mysql-connector-java-5.1.28-bin
#End Region
`Sub Process_Globals
Private Const CAPPINFO As String = "B4JHowTo - MySQLEx2 v20150302"
End Sub`
`Sub AppStart (Form1 As Form, Args() As String)
Log(CAPPINFO)
SelectRecords
ExitApplication
End Sub`
'Select records from the remote mysql db
`Sub SelectRecords
Private gSQL As SQL
Try
gSQL.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.0.16/notes?user=notesuser&password=YOURPASSWORD")
Catch
Log(LastException.Message)
Return
End Try
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
cursor = gSQL.ExecQuery ("SELECT * FROM notes")
Log("Records found:")
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Log(sb.ToString)
Catch
Log("ERROR: " & LastException.Message)
End Try
End Sub`

Changed: , Category: MySQL, File: T6C223M1647.htm, ID: 1647