Networking

How to connect LibreOffice Base to a remote MySQL database

LibreOffice Base can make working with remote MySQL databases a snap. Jack Wallen walks you through the steps to make this happen.

mysqlhero.jpg
Image: Jack Wallen

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 5.3.3.2 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:

bind-address 172.0.0.1

Change the above line to:

bind-address 0.0.0.0

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.

Configuring LibreOffice

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).

Figure A

Figure A

Adding the JDBC Connector to LibreOffice.

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).

Figure B

Figure B

You must connect to your remote MySQL database using JDBC.

Click Next and then fill out the necessary information for your remote MySQL database (Figure C).

Figure C

Figure C

Adding the information for your remote connection.

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.

Also see

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox