MySQL is the database component of the LAMP stack, being a common method for storing web application data, but its use isn’t limited to this. I’ve been working on a case management system that enables a security department to share malware information on an internal network, with a MySQL server providing a central data source. Here’s how I got the server running, created a database and prototyped a basic front-end UI in LibreOffice.
The default MySQL installation includes a set of command line tools for administrating the server and databases. To start and stop the MySQL server (mysqld):
$sudo /etc/init.d/mysql stop
$sudo /etc/init.d/mysql start
There are also a number of GUI applications for working with MySQL servers. One of these is the default mysqlnavigator utility, but the common application is the freely-available MySQL Workbench. Later it becomes useful for sorting out replication, backups, performance and access management.
In order to work with a server in MySQL Workbench, a ‘server instance’ must be created:
After that, we can start administrating the applications/user accounts registered with it, later sorting out the permissions and importing or exporting databases:
Setting Up the Database in the Command Line
It’s best to acquire some experience of working in the command line. Create the database by logging into the MySQL server:
$mysql -u root -p
Here ‘root’ is the database server (not the system) admin account. The best practice is to assign applications their own accounts to restrict access to specific databases, and to define the actions they could perform on the server. The ‘-p‘ option tells the server to prompt for the account password, if it’s been set.
In case you’ve experimented with MySQL before and lost the password as I did, Rackspace has outlined a process for resetting native user database.
Creating the database is simple as the following command:
mysql> CREATE DATABASE nexus;
Notice that a semi-colon is appended to each statement, which marks the end of an instruction. Next, switch to the database and list the tables it contains (initially there are none):
mysql> USE nexus
mysql> SHOW TABLES;
Records are stored in tables, each table having columns and rows. A database can have multiple tables, and the next stage is to create one. For application development I strongly recommend designing it on paper first, making a note of the table and field names before actually implementing the design.
The following command writes a table (entities), the fields within the tables, their data types and the field lengths:
mysql> CREATE TABLE entities (Entity VARCHAR(60), Description VARCHAR(140), Category VARCHAR(20), EntryDate VARCHAR(20), Source VARCHAR(80),
Target VARCHAR(80), RelatedTo VARCHAR(140), Outcome VARCHAR(160), Notes VARCHAR(160), Reference VARCHAR(160));
To confirm the table’s existence after this, use the ‘SHOW TABLES‘ command, and to view the table layout:
mysql> DESCRIBE entities;
Alternatively, the Workbench application can later be used to modify the database under the SQL Development panel:
We simply need to select ‘Open Connection to Start Querying‘ to start editing.
For the moment I’ll use the command line for this. Knowing the table columns is important here, as the INSERT statement will add the data from a one-dimensional array. e.g.
mysql> INSERT INTO entities (Entity, Description, Category, EntryDate, Source)
-> VALUES ('Test Entity','Test Entity','Object','October 2014','localhost');
To view the records in the table:
mysql> SELECT * FROM entities;
MySQL and OpenOffice
Now we come to developing a front-end. This is an independent application that connects to the server (localhost, through the network interface), and presents a UI for users to manipulate records. The front-end and the MySQL server can also be on two different machines.
Later I’ll make custom software for this, but for now I’m using LibreOffice Base. Since the whole front-end here was based on Java, I made sure the connection driver and OpenOffice MySQL Connector extension were installed:
#apt-get install libmysql-java
Then enter the LibreOffice/OpenOffice Java settings, and change the following dialogue options:
The Oracle JRE makes sense, since MySQL seems heavily supported by Oracle. The Class Path here is /usr/share/java/mysql-connector-java.jar. Restart LibreOffice and you’re good to go.
When LibreOffice/OpenOffice Base is next started, MySQL will be listed in the Database Wizard dialogue, in the drop-down menu for ‘Connect to an existing database’.
Enter the connection details (typically localhost on port 3306), and register the database in LibreOffice. If successful, a screen like the following will appear after saving the file, with our database listed:
Now it’s possible to create the UI for querying and updating records. In the Form Wizard dialogue, the available databases, tables and fields are listed.
Resolving Form Connector Problems
Now you might find that the new LibreOffice form won’t allow the modification of records. One of the fields must be defined as the primary key with the following:
mysql>ALTER TABLE entities ADD PRIMARY KEY (Entity);
This should solve the problem. Now we have a working front-end.