LibreOffice Base is a user-friendly GUI tool that makes working with databases fairly simple. The tool has the built-in ability to work on both local databases or even remote databases. However, when you attempt to connect to a remote MySQL database server, you’ll run into a couple of issues. I’m going to show you how to overcome those problems, so you can work with your remote MySQL databases with ease.
I’ll be demonstrating with LibreOffice 126.96.36.199 on the client side and MySQL 5.7.17 running on Ubuntu Server 16.04.
Configuring the server
The first thing you must do is enable remote connections on your server. To do this, log into your MySQL server, change to the /etc/mysql/mysql.conf.d/ directory, and open the file mysql.cnf for editing. Locate the line:
Change the above line to:
Save and close that file. Now, restart MySQL with the command:
systemctl restart mysql.service
Once the service successfully restarts, you’re done on the server.
Out of the box, LibreOffice does not have a functioning JDBC Connector. To add this, you must first download the Platform Independent connector (from the MySQL Downloads page) and then extract that file. The extraction will create a new folder that contains the necessary .jar file. Open up LibreOffice and go to Tools | Options | LibreOffice | Advanced | Class Path (Figure A).
In the resulting window, click the Add Archive button and then navigate to the folder containing the .jar file. Select the mysql-connector-java-XXX-bin.jar file (where XXX is the release number) and click OK. Click OK to exit out of the Options window and you’re ready to connect to your remote database.
Connecting to the remote database
Open up LibreOffice Base and the database wizard will start. From the window, select Connect to an existing database and, from the drop-down, select MySQL. Click Next and then make sure Connect using JDBC is selected (Figure B).
Click Next and then fill out the necessary information for your remote MySQL database (Figure C).
You must know the name of the database you want to work with and the IP address of your MySQL server (enter that address for the Server option). Once you’ve filled out this section, click the Test Class button to make sure the JDBC driver is functioning. This should pass. Click Next and, in the resulting window, enter the MySQL user name that has proper privileges to work with the database in question. Click the checkbox for Password required and click the Test Connection button. When prompted, type the password for the MySQL user and click OK. This test should also pass. Click Next and then click Finish. You will then be prompted to save a local copy of the database. Don’t worry, any work you do will be reflected on both the local and remote copies of the database. When you’re finished with the wizard, LibreOffice base will open, ready to work on the remote database.
Congratulations, you’ve successfully connected LibreOffice Base to a remote MySQL database.
Power made simple
If you’ve ever needed to work with MySQL, but didn’t want to have to tackle the command line, you now have the ability to manage your databases with the help of a powerful, user-friendly GUI tool. That is power made simple.