Introduction
The most common way, and probably better known, to manage a MySQL database is using the PhpMyAdmin web application. This sounds good in all that cases where the MySQL database is remotely stored on a web server, especially when the core components of the MySQL database are managed by the server provider reserving a specific database partition.
Note: another good way to use the PhpMyAdmin is for the popular Blog and CMS Wordpress, with the database management can be done with a PhpMyAdmin Wordpress plugin.
Things comes slightly different when the server is a Raspberry PI, a Linux machine we can take full control from our LAN. In this case we can adopt a more reliable solution directly provided by Oracle. Better if the bare database management includes queries testing, working faster, work with a confortable visual tool.
These and other things are possible with a free multi-platform tool provided by Oracle that maybe considered the better way to access and control MySQL databases on the Raspberry PI. This Meditech project annex explain how the MySQL Workbench has been used to setup and maintain the database of the Meditech project.
MySQL accessing by remote
It is not sufficient to install and setup the MySQL database on the raspberry PI (following the common MySQL installation procedure) to enable external (remote) users access to the data: the MySQL database should be enabled for remote access. For more details on the enabling procedure see the attached document.
As the database resides on the RPI master device that should be updated by the other slave units granting the remote access should be set anyway. The procedure is almost simple, part of the standard MySQL online documentation.
The MySQL database should have a user and password enabled for remote access; in our case to make the things easier the same user/password pair to access the Raspberry PI has been adopted. Take in account that his is NOT a regular Linux user but it is a database user with nothing to do with the operating system.
Connecting the workbench to the remote database
After the installation, launching the workbench the main screen shows the the possible options and the database connections with the remote devices as in the image below
While the PhpMyAdmin after logged the user has access to the databases he is authorized as this web application is part of the same MySQL database here things are different. It is like having an IDE that can connect to as many database as you want, local or remote just as they were different projects.
The image below shows the LAN connection settings from the development Mac to the RPI master where the Meditech project database is stored. The connection parameters can be tested, then after confirmed these are permanently stored in the workbench. The database connection should be considered the entry point to the database schema we want to work with.
Every time we need to work with the database schemas (i.e. the entire MySQL architecture on the desired server, users, tables, queries etc.) it is sufficient to double-click the corresponding connection on the workbench main screen.
As the database connection is established it is shown the main SQL editor page. The following image shows the RPI master database where the only schema is the PhpMyAdmin, that was already installed on the raspberry PI, for testing purposes.
One of the most important differences between the MySQL Workbench and PhpMyAdmin is that with the workbench we have a top level vision of the installed MySQL engine with a better and wider control over the architecture. Who is used to manage server databases with PhpMyAdmin known that it operates from inside the database and it is not possible to have this kind of scenario.
An helpful tool set for database design
The following images shows a first advantage of having the full control of the MySQL engine: all the users, connection, server status and more can be checked in every moment including a good traffic monitor while the database is running serving other users.
But I think that one of the most interesting features of the workbench cover the database design aspects. After the essential components of the database has been defined - like in this example the PhpMyAdmin database tables, we can use it to generate and then edit graphically the data queries, tables relationship and more.
Starting from the MySqlAdmin tables definition with a simple automated wizard the database tables structure has been extracted and generated graphically like shown in the image below.
The database designs can be organized visually to easily create the documentation like ths simple example in the attached pdf; the design is also interactive and can be used to expand the database features, complete the tables relationships, creating queries, procedures etc. in a comfortable visual environment.
 | MySQL - Enable Remote Access. Grant & Revoke Examples.pdf |
 | MySQL Workbench printout example.pdf |
Top Comments