AUTHOR'S MAIN SITE  »  MAIN INDEX: LibreOffice / OpenOffice Base  »  OVERVIEW: client/ server work.  »  Intro: MySQL  »  Setting up MySQL

LibreOffice database manager as front end for MySQL database on server

Filename: s0MySqlFrmBase.htm

This page is "browser friendly". On a "proper" computer, make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, 3/13-5/2020, Sheepdog Software.



Using LibreOffice database manager as front end for MySQL database on server

Getting "Base" (the database manager supplied as part of the free, multi-platform LibreOffice suite) to work as a front end for a MySQL database server is not hard. And you don't have to give up continuing the use Base with other databases the "simple" (embedded engine) way. (You don't need to have a MySQL server on your system to use LibreOffice Base.... but do, please (for your sake) consider setting it up to use the Firebird engine when using an embedded engine. Especially if your are just starting with LibreOffice Base.)

But first catch your rabbit.

Before you can use Base as your front end for interacting with data held on a MySQL server, you need.... data held on a MySQL server!

Getting that set up may give you a few headaches... But it is a sophisticated system, with lots to recommend it. (Also free and multi-platform, I'm glad to say.)

I've written elsewhere about MySQL, and how to set up a server. You can do it! But get it set up and working before you try to access it with a "Base" front end.

When you have all of the elements in place, each time you want to add a database to the MySQL server, and connect to it from within LibreOffice Base, you need to do the following...

Once per new database on the server...

You do the following once, each time you want to start a new database on the server. Remember: Databases do not, as such, have passwords. But the database server keeps a list of "users", and each user can access one or more databases, with privileges which can be different for different databases. One person can be authorized to use more than one "user" account, to accommodate a variety of wants.

Doing the following sets the database up on the server. Well.. the bare essence of a database, anyway! The new database won't even have any tables yet. But this step is still necessary. Once, for each time you start a new database on the MySQL server. I think of this step as providing a "container" for the database. By the way: you won't be called upon... won't have a chance!... to create the folder where your database will live. MySQL takes care of that, in a "behind the scenes area you aren't really meant to visit. (Backups are taken and restored via a part of the Workbench App. Something to master... soon!)

Go into the MySQL Workbench app.

Connect the Workbench app to your server. Log in as "root", or some other powerful user.

You need to get to the screen that's a bit like the following... but the underlined- in- blue line...

create database tmp22mar20tkb

... won't be there yet. Here's what you need...

Graphic of MySQL Workbench session

Under where it says "Query 1", type in the "create database... " command just mentioned. (It won't be underlined, on your screen.) Choose a name for the database. Something fairly short... I keep mine to about 5 characters usually, but that may be shorter than necessary. Start the name with a letter. After that, just letters and digits would be best, but you can probably get away with hyphens, underlines... like this-here_could_be_a_long_name. I'm not sure if spaces are allowed... I doubt it.

The "tmp22mar20tkb" is just what I chose to call the database that I am using to check I explain what exactly you have to do to set up a database on your MySQL server, for access via LibreOffice Base.

A perhaps useful little trick: For at least 30 years I have stuck to a convention that says if "tmp" or "temporary" starts the name of anything I've created, then I can always just delete it if I stumble across it later. (I apply this rule across the board. It isn't just for LibreOffice / OpenOffice work.)

Then click on the lighting bolt. (I've circled it in the graphic above.) The right icon's tool-tip hint is "Execute the selected portion of the script...".

Just in passing... don't try to figure the terminology out! You entered what any normal person would call a command, in a box labeled query, but then to say "do it", you had a script do its thing, "execute". There is sense to these things, but you can be forgiven if you don't see it yet!

Don't be alarmed if it doesn't seem as if much happens!

Look closely: you should see something in the "Output" panel. Something like...

                                                               Duration
# Time      Action                         Message               /Fetch
- ----      ------                         -------             --------
1 23:42:35  create database tmp22mar20tkb  1 row(s) affected   0.140 sec

That is a very good sign... as long as there's nothing in the "Message" column saying "error..."!

Moving on...And when I've talked about "schema", I will show you another way to check we've accomplished something...

Schema(s)

Before we start- the plural of "schema" is... "schema"! But in the heading, I wanted to stress that I was going to talk about them in general for a moment.

The "schema" of a database, as I, perhaps poorly, understand it, is a complete description of "what's in" the database.

When I said the schema is "everything that's in the database", you might, quite reasonably, thought I meant all the entries, all of the data in the database.

No. What a schema tells us is: What tables are being used? What fields are in the records in those tables? What are the properties of each of those fields?

The schema will tell us what the primary key is for for each table.

If the database uses relationships, the schema will tell us about them.

The schema is everything BUT the actual data in the database!

Back to using LibreOffice Base as a front end for a database on a MySQL server

Go back to the MySQLWorkbench. Look at the navigator window again. Note the two tabs at the bottom of that, "Administration" and "Schemas"

Graphic- MySQLWorkbench- Navigator

Prepare for two moments of unnecessary alarm, and select the "Schemas" tab...

Graphic- MySQLWorkbench- Navigator- Schema tab

... and you will probably almost see what appears to the right of this.

Now.. were you hoping to see your database's schema in the list? Seems reasonable!

And you should... after you refresh the contents of the sub-panel.

Graphic- MySQLWorkbench- Navigator- Schema tab

You can do that by right-clicking, and requesting "Refresh All", or you can use the "circling arrows button, at the top of the panel.

Refresh the list, and Ta da! Since I called the database I added "tmp22martkb", and my "create database tmp22mar20tkb" was successful, I see that MySQL has a schema for it.

But wait a minute... what are all those other schema??

"Sakila", "world", and "sys" are other schemas which you will probably see on any "basic" MySQL server. Don't be alarmed by them, and don't mess with them. I'm pretty sure that "sakila" and "world" are demos that whoever setup up the server fetched from The Nice MySQL People, and that "sys" is High Mojo where the server stores things related to its configuration.


I have a rule: In general: Do what you know about, and don't do what you don't know about.

I don't know about "sakila", "world", and "sys" and so I try very hard not to "do" any with of them.

Of course, sometimes, you have to extend what you know, and when you're trying to do that, I suggest that you... as you are now!... seek guidance before you go blundering around "inside" a computer that is, for the moment, relatively "as it should be". Clicking something "to see what happens" is a recipe for disaster. The thought that some "tidy minded" people out there in the world happily delete things, because "I don't know that this is, let's get rid of it" send shudders down my spine. Would you get rig of a thingie under the hood of your car, because you didn't know what it was for?

Yes... I will, probably, start using the demos, if that's what they are. After I have learned something about them. I genuinely wasn't sure, when I typed the above. 15 seconds with Google ("mysql sakila") turned up an explanation of the Sakila database, from a reputable source. (Yes, it is a demo. But I will still be careful with it, try not to "break" it.) I'd try to find a way to make a copy of the demo as the first thing I do, and then I would "play" with the copy. Early in that "play", I would seek to learn how to (properly) remove a database from the server.

Good news/ Bad news...

The good news is that you only have to do the "create database..." once in the life of any of your databases.

The bad news is, that except for "super users", no user can "see" the nice shiny database... well, empty shell of a database.... that you just created.

Manage Users with MySQLWorkbench...

We've already used MyWorkbench once. And it made getting a database started on our server delightfully easy.

Now we will return to MySQLWorkbench to manage the authorized "users" of the database.

Remember, a particular human may be several "users". Many people have more that one email address. Being authorized as several "users" of a particular database server is not so very different.

Are you tired? I'm tired. And I have a headache. But once you've done these things a few times, they don't seem so bad.


Graphic- MySQLWorkbench- Manage Users

While connected to your server, choose "Server/ Users & Privileges". (Okay, so not my most successful screenshot! I just wanted to show you making that choice...)




Graphic- MySQLWorkbench- Manage Users

That should give rise to what you see on the left.

(My arrow "1"): Look at the tab legends. You might think we're looking at the "Query 1" tab, but no, we're in the "Admin- Users and Privileges" tab.

(My arrow "2"): I've elected to add permission to use the new database to a user that already exists on the system.

(My arrow "3"): I've selected the Schema Privileges tab.

(My arrow "4"): We are going to click this now, to add an entry in the list of schema that this user is allowed to access.



Graphic- MySQLWorkbench- Manage Users

-----------------------------------
(Click on "Add Entry".. which should lead to what is on the right.)

We want, for now, baby beginner steps, "Selected Schema". Note how the user-friendly system gives you a dropdown which will show you the available schema.

(If you use "all schema", or "schemas matching pattern", you will probably set this users privileges for more than one schema. Are you really ready to say, for now, and for the future, what privileges you want the user to have, even on schema that aren't going to be on the server until some time in the future?)




Graphic- MySQLWorkbench- Manage Users

If that doesn't scare you, your attitude towards your system's security may be too casual.

I don't claim to know nearly enough about these choices... which is why don't yet use my MySQL server for anything sensitive.

For now, assuming you haven't set your server up in a way that allows access even by other PCs on your LAN, let alone the rest of the world via your internet connection... and assuming that you've done what you can to KNOW how it is set up... I suggest you "play" with databases with only non-sensitive materials. Given all those premises, I would click Select All. Remember to click "Apply" when you've got the settings how you want them.

A little "gotcha": When you ask for "Select all", it doesn't actually select all of the tick-boxes! If you want the user to have the "Grant Option", you have an extra click to do. (I don't know why. I barely understand the Grant Option!)

If you hover your mouse pointer on any of the checkboxes, the tooltip will tell you a bit about what that option controls.

When you've played a bit, the second exercise I would recommend is trying to set up a user who can look at data in one of your MySQL server-held databases, but not alter it. This is a use that may well arise. I sometimes create "read only" copies of documents that are important to me for daily use. As I have write-protected those copies, I don't have to worry about accidental corruption, when I am using them from day to day.

Good news!

That's all we need to do in MySQLWorkbench for now! We've got to the end of a stage!

You are nearly there! A long road, but not endless. Soon, if you've got all of the previous right, you'll be "in". You will be using a database held on a server.

And the best thing I can tell you just now? It is hard to tell that you aren't just using the ordinary LibreOffice Base, aren't just using one of the embedded servers!

So, next:  Launch LibreOffice Base...

More good news.. there are things we'll do in a moment which are again, "do it once" things.

Launch LibreOffice Base. As usual, you get the "Welcome to the LibreOffice Database Wizard". (I would quibble, and say that "Manager", or RDMS should be in that text, just after "Database". But you know what the dialogue is about, I hope?)

Be very, very careful with what you type during this part of the exercise! A confusion between server/ database/ user/ whatever, or a tiny typo, can lead to perplexing fails, sometimes with not a lot of help from the error codes. (The weak prompts are not really the system's fault... you are so far from being "in" that it would have to read your mind to give you good help. Also, unless you have a well established ("known-good") server, with established users, similar errors in establishing the settings in your MySQL server will be just as success-thwarting. Be strong. You are a computery person. (Sounds so much better than "geek"?) Would you have got this far if you weren't? You can do this.



In building this guide for you, I'd had a long day to get to my "first bash" at the description of this stage. It was not a good time to stumble, but I did, and then wasted more time than I care to remember going 'round in circles making as new mistakes as fast as I fixed earlier ones. I then repeated that process for about 30 minutes the next morning. (If I hadn't been "lucky", if I hadn't done the tasks without errors a few days ago when I ran through the process in a trial run, without writing this as I went along, I might have given up on the whole business. But I had. I knew what I wanted to do could be done, and was very conscious of how many things have to be exactly right.

The system isn't flawed. It is a good design, if you want your database secure from hackers. But it doesn't suffer the careless legitimate user gladly. (Doesn't suffer them at all, actually... it just locks them out.)

I got there in the end.



Graphic- Connect LibreOffice Base to MySQL server- step 1

Fill the dialog for the first step, "Select database", as shown. Click Next.



----------------
In step 2, "Set up MySQL connection...", tell it you want "Connect directly".


----------------
In step 3.....


Graphic-  Connect LibreOffice Base to MySQL server- step 2

In step 3, "Set up MySQL server data", don't fall to the possible confusion I fell into briefly. The page is clear, if you are paying attention, but I managed to misread it.

It does not offer three options, of which you select one.

The page starts with the "Database name?" question. You must supply the name of the database you want to use regardless of what settings you choose with the rest of this page. In our example of all this, above, we created a database called tmp22mar20tkb. That's why I gave that for the "database name" here.

----
When you've specified the database, you tell LibreOffice how you want it to "connect" to the server. There are two options, hence the two radio buttons.

To use the "simple answer" I am taking you through, choose to specify the server and port. The right answers are "Server/ port... Server: localhost... Port 3306." (Unless, during the installation of your server, you decided to put it on a different port.) I have edited the screenshot, to keep the port I'm using secret.


Step 4, "Set up user authentication". Enter the your username, the one that let's you connect to the server. And "tick" the "Password required" box, unless the server allows that user to access the database concerned without giving a password.

Take a deep breath, and click "Test Connection".

What you hope to see is a little pop-up message saying "The connection was established successfully".

If you have just one typo or error in (any of lots of places) the error messages you may receive include things like "sha25_password (or plugin caching_sha2 password) could not be loaded". Of course, Murphy's law, as I try to get it to throw those messages again, 10 minutes after they baffled me when I still had not realized errors in what I was doing, I can't trigger them if I try. The good news is that the system does sometimes give sensible error messages!



Step 5, "Save and Proceed"...

If you've used the database package in LibreOffice (or OpenOffice) a bit with the simpler- to- use embedded database engine, then Step 5 will be an old friend.

Yes, you probably DO want to ask the wizard to "register" the database in LibreOffice. Don't worry, this is just something "internal". It won't tell anyone out there in internet-land about your database. The "registration" thing can be un-done later, if you decide you want that.

The choice you make in response to the second question isn't important. For now, tell the machine to open the database for editing.

Click Finish, and you enter an ordinary "Save As" dialog. Just as in LibreOffice Base when you aren't using a separate database server, you will be creating an .odb file. Whenever you want to return to the database you've been working so hard to create a connection to, everything will happen "automatically". (You will have to tell the system your user password, the one for "becoming" the user of the server that you specified you'd be using.)

If you are using one of the embedded database engines, your .odb file holds everything, including the data in the database.

When you use an external database server, which is what this page was all about, the data resides on the server.

SOME other things, in addition to "how do I connect" are held in the .odb file... but that is a story for another time!

Graphic- LibreOffice Base Launch screen

After you specify what name use... and, importantly, where you want the .odb saved... and click finish, LibreOffice Base will launch. (You can put the .odb where ever you want it. When you don't have the database it concerns open, you can move it to other folders, rename it, do what you like. It's "just another file".)

Don't be alarmed by all the "stuff"! You may see stuff about information_schema, as here. You may, other times, see other stuff.

And leave things you don't know about alone (When I am more clever, I'll figure out, and try to remember to tell you, how to have just the right stuff showing.

See the little "-" sign the arrow I added points to? Click on it.

That should "collapse" the details of what is in "information_shema", and let you see "tmp22mar20tkb", "your" database. If that database had any tables, there would be a "+" sign in front of it. Click that, and the tables of tmp22mar20tkb would show. (It's all a bit like folders and files in your everyday computer life.)

Make a table now!

First... close the MySQLWorkbench, if it happens to be open. You shouldn't need it again for a long time. It isn't relevant to USING our database, now that we've finally got everything in place.

This isn't really a "setup" "thing". Nor is it different in many ways from "ordinary" table making. But, while we're here, as they say...

If you are coming from OpenOffice Base, run through this, anyway.

Making a first table for your database is short, and all that is left on this page. (Whew! Just remember, you don't have to go through all of this EVER again, nor any of it often. You've DONE IT!!! HURRAH!!!)

You can make a table either of the usual ways. I'm going to use the wizard.

Step 1- Select Fields...

What we do, for this little demo, barely matters. But be sure to build in an integer field that can be used for an auto-incrementing primary key, so I can show you a quirk of that useful solution to the need for a primary key... not that I much like that sort of primary key. But knowing how to create one when you want it is nice.

I was happy to work with the first sample that came up.. the "Tasks" table from the "Business" samples.

I selected the "Task ID" (first) and "Notes" fields to be the constituents of my table. And Clicked "Next".

Step 2- I made sure that the "Task ID" field type was integer. I did not... and this is important... say that I wanted it to be an "AutoValue" field, nor even that I wanted "Entry required". This seems odd to me, but trust me.

I didn't tweak anything about either field during step 2. Clicked Next.

Graphic- LibreOffice Base Launch screen

Step 3- I did a lot in Step 3: I said "Use an existing field as a primary key. I said to use "TaskID. I said (HERE!! not in step 2!) "make it an "Auto value" field. Clicked Next.

Step 4- again, I used the defaults. Which means the table got called "Tasks", and when I clicked "Finish", I went straight into the table, and was able to enter data immediately.

Yes, I know, a "good" database person would never work directly with a table. They'd make a form, interact with the table through that. Maybe I'll become a Good Database Person someday.

I entered some data, and closed the table, closed the database, saying "yes, save" whenever the question arose.

Held my breath. Tried restarting it, by double-clicking on the .odb file I'd created. (The .odb file is "my part" of my computer, and takes me into my LibreOffice Base. The .odb is not inside the MySQL server. Although the MySQL server that I use happens also to be "on" my computer, for many intents and purposes, it "isn't". It's files are not for me to mess with, except through the server.)

Graphic- LibreOffice Base, first trial of database created

Your database should "appear" to open. That is, you should be rewarded with the database's main project manager window... with "Forms" the selected task ("Database" panel, at left of manager window.)

No forms are listed, right hand, lower panel... because we haven't made any yet!

Click, at the left, on the big "Tables" icon. That, normally, is all we need to do to see our tables... and we SHOULD have a table. We made one, remember?

Ah! we are challenged by the MySQL server. This is the first time, this session, we've asked for something from it. Supply the password for the user (of the MySQL server) you are using in the .odb you just set up.

Boom! You're in! With all the "information_schema" stuff underfoot again. Click the minus sign on that. NOW you can see "tmp22mar20tkb, or example database, can't you? Click the + sign in front of that, and, lo, THERE's the "Tasks" table we made!! And you can do to, and with, it what you would ordinarily do to or with a table in LibreOffice or OpenOffice Base. (If you are Good, and regularly use forms instead of interacting directly with tables, you won't have Information_Schema underfoot.)

I can't tell you...

I can't tell you how thrilled I was to write that last paragraph, 24 May 2020, 14:41, UK time.

Writing the guide has been a VERY long slog. I hope that using it, while not quite like a game of Freecell, hasn't been too terrible. I assure you, the database is well worth mastering. Depending on your prior experience, you may have found getting to here a bit of a challenge. But I promise... the tasks involved really aren't as bad as they may seem at the moment.

Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using Sheepdog Software/ Sheepdog Guides material for inmate education. Situations do exist where good internet connections are not possible!)




Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

  ! Please >>> Click here to visit editor's Sheepdog Software freeware, shareware pages <<< please !  



If you liked this tutorial, see my main webpage for more things from the same author.

How to email this page's editor, Tom Boyd: Editor's email address. Suggestions and questions welcome, but please cite "s0MySqlFrmBase.htm".







Search across all of my sites with a Google search button. There's one on another of my pages. (It's at the top of the page the link will take you to.)

Or...

Use this to search THIS site.... (Go to my other sites, below, and use their search buttons if you want to search them.)

index sitemap advanced
search engine by freefind

Site Map    What's New    Search

The search engine merely looks for the words you type, so....
*    Spell them properly.
*    Don't bother with "How do I get rich?" That will merely return pages with "how", "do", "I"....

Please also note that I have two other sites, and that this search will not include them. They have their own search buttons.

My SheepdogSoftware.co.uk site.

My site at Arunet.

Valid HTML 4.01 Transitional Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.

AND passes... Valid CSS!


One last bit of advice: Be sure you know all you need to about spyware.

....... P a g e . . . E n d s .......