I conclude my series on using LibreOffice Base as your go-to end-user database tool by illustrating how it can also connect with a pre-existing database; this will allow you to run reports, create forms, run queries, and more with that connected database.
It is critical that you run plenty of tests on this system; you do not want to jump in and connect LibreOffice Base to your production databases. Also, you can modify the databases created by other applications, but the LibreOffice Base/MySQL connection is best suited for creating and managing databases that are not used by other applications. So, why would you use this method as opposed to using the built-in database? If your database is larger or you want to use a centralized database that more than one person could manage, you will want to connect to a MySQL database.
Before we continue, I am assuming several things:
- You have read my previous TechRepublic DIY posts about using LibreOffice Base: DIY: Create easy to use databases with LibreOffice Base, DIY: Create data entry forms for databases in LibreOffice Base, DIY: Create a database form in LibreOffice Base Design View, and DIY: Vertical alignment of text in LibreOffice.
- You have LibreOffice Base installed.
- You have a MySQL database server up and running and have access to a user with permissions.
- You can create a new MySQL database for use.
For the creation of databases, I prefer using a tool like MySQL Workbench. For the purpose of this tutorial, I created a database (using MySQL Workbench) called libreoffice_mysql, which will be the database used for the connection from LibreOffice.
The MySQL database server can be located on the machine running LibreOffice, or it can be hosted on a remote machine. If the database is on a remote server, you need to make sure the LibreOffice desktop machine has full access to the database. Now let’s start the process of connecting LibreOffice Base to MySQL.
Step 1: Open LibreOffice Base
Open the LibreOffice suite and then open Base. You must select MySQL from the Connect To An Existing Database drop-down (Figure A).
You can select from a number of databases here, including Oracle, Evolution Local, GroupWise, dBase, spreadsheet, and more.
Step 2: Set up the connection to MySQL
There are three methods for connecting LibreOffice to MySQL:
- ODBC (Open Database Connectivity)
- JDBC (Java Database Connectivity)
To connect using JDBC, you must first install libmysql-java. I have always had better luck connecting directly. In this next window of the Database Wizard, select Connect Directly and click Next (Figure B).
OBDC and JDBC have been flaky in the past, so I think connecting directly is the safest bet.
Step 3: Set up a database connection
In the next window, you need to enter the information for the database connection (Figure C). You will need the name of the database, the server address, and port number.
If your database runs on a non-standard port, make sure you know that port and that it is open for connection from the desktop machine.
After you enter all of the details, click Next.
Step 4: Set up database authentication
Figure D shows the simple authentication window where you will enter the username of the user who has access to the database (full privileged user). After you enter the username, make sure you check the box for Password Required.
Next, click the Test Connection button. A new window will open where you can enter the password for the MySQL database user (Figure E).
Enter the password and click OK. If the authentication succeeds, you will be prompted and returned to the previous window. With a successful authentication, click Next.
Step 5: Register and open the database
In the final window, you can select to register the database and then what to do once you complete the wizard (Figure F).
If you want to immediately start creating tables for the database, make sure to check the box for that step.
Once you click Finish, you will be required to name and locate your database. Don’t worry — this isn’t going to pull the database from the server; it is saving just the connector information that will communicate with the server. You are ready to start working with your database; you can now create forms, run queries, and much more.
The ability to connect LibreOffice Base to a MySQL database makes this office suite so much more flexible and powerful. I hope you can take advantage of this power in order to help your end users work with databases without requiring them to work with complex applications.