AUTHOR'S MAIN SITE  »  OVERVIEW: client/ server work.  »  Intro: MySQL.
Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon

Setting up a MySQL database server

This page is "browser friendly". 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, Sheepdog Software, 3/13-3/7.



How to do it: How to set up a MySQL server

Very shortly, I will delve into the details of how to set up a MySQL server. I work in Windows... thank you for your sympathy... but I suspect that many points here will also apply to Linux and Mac users.

Before you will be fully ready to benefit from what follows, you need a reasonable grasp of "the big picture". You should also have a pretty good idea of using a database. I used Open Office's Base, but there are many ways to connect to a MySQL database server. I offer an extensive set of tutorials about using the free Open Office Base database.... in general. Only a few of them relate to MySQL relevant details.

I've tried to discuss "the big picture" other web pages...

Sometimes it is important to find out what something is supposed to be for. Just because you think, say, that a hammer is for putting screws into wood doesn't mean that it is.

If you have a reasonable grasp of "simple" database work, the following statement of what you can do with a MySQL server may be useful:

With a MySQL server, your database tables reside "inside" the server. The database's query, form and report definitions reside with the client. The client presents a query to the server (the query passes from client to server, and is processed by server). The resulting temporary "table" of data is then passed back to the client, for whatever use the client's operator had for that data.

Forms will reside with the client. When the user wants to see data from a table or tables, the user connects to the server. The user's screen fills with what the form requires, the structure being determined by the form, from the client, and by the data in the tables to which the form connects.

Enough "introduction"! (I hope! If you find yourself struggling with what follows, consider having a more careful look at one or more of the pages commended to you above.)




The road to success...

Which road should you take, setting up a MySQL server?

If you have little experience of MySQL, or servers in general, I would suggest you start with a MySQL server on the same PC as you intend to run the client on. (The client which will use the server.)

If you are more experienced, and you are going to need multi-machine access to the server, remote access (i.e. across the internet), etc, etc, you MAY want to start out as you mean to finish... with the server on the PC which will be the final home for your MySQL server needs.(If you don't know that "this is you", then start with the beginner's path.)

If you are not sure which path to follow, it wouldn't be a great hassle, and it might enable you to get to the final destination faster, if you install a "test bed" MySQL server on a PC you can work on, "face to face", with your client running on the same machine, at least until you get past the early hurdles. "Switching" the server to a different machine would entail just a few relatively painless operations. Going with the "all on one PC" approach as you start up the learning curve would spare you having to deal with "everything" at once. And, to a depressingly large degree, "everything" has to be working before you can see anything working. Let's say that "everything" is just having to have a user ID, a password, and a URL. A typo in any one of those... during creation, or during the attempt to use it... will mean that "it" "won't work". Do you really, at first, want to add more things- which- can- go- wrong than you need?

If you do take my advice and start on one machine, then once you have some little "test" databases behaving themselves, "all" you'll then need to do is...

1) Repeat the "install server" on the PC you want as your "production" server,

2) Transfer across any databases (not a big deal... a good chance to practice export/import. Exporting something which can be imported is my preferred backup routine, anyway.)

3) Make changes to the links your client uses to "connect" to a database which is on the server.

So... I hope you'll start with the server and client in one machine. The instructions below are for that "answer", but need little adaptation to work in other scenarios.

In a related vein, you will also need to ask yourself if you are only interested in having a MySQL server.

Many people who are ready to think about having a MySQL server are also ready to think about having their own web server. If in doubt, after you've done the "simple" case of MySQL (alone) and database client on the same PC, go the whole hog, and use a WAMP (or LAMP, in the case of Linux) package to set up an integrated Apache (web page server), MySQL server, PHP hypertext pre-processor on your server machine. It is very easy to "turn off" the web server and PHP, if you aren't quite ready to use them... but I'd lay money on the idea that people interesting enough to take on setting up a MySQL server will one day also want to do the things Apache and PHP allow!

But... at first: Just a MySQL server on a PC where you will also have the client that uses it. Later: WAMP on the "final home" for your MySQL needs. If you are really only interested in having access to a MySQL machine, on one computer, a computer on the same LAN as the client will be run on, then you are probably going to be entirely happy with installing just the MySQL server and tools.




Installing MySQL and tools...

Go along to http://dev.mysql.com/downloads.

The page you are reading was first written in May 2013. I then went through it again, 24 June 2015, doing what the page said to do, on a fresh machine. (Somewhere around 5/13 I did another install, but didn't keep notes.)

Paragraphs starting or ending "(5/13)" are comments specific to my May 2013 experiences. Paragraphs ending "(6/15)" relate to... you know.

There are various "MySQL"s you can install. The free one is referred to as the "MySQL Community Server".

Once you get to the sub-pages for that, there are still choices. Back in May 13, they confused me a bit, but I've edited most of that out, as they're academic now, I hope!



Some annoying details

For a while, I am going to give tiny details. You can read with a little less attention for a bit, but maybe things in this will be details you need.

(5/13) When I tried to do the download, first I had to answer a bunch of questions about what I wanted it for, market research stuff, I suppose. Well... the product is free. Annoying or not, maybe we shouldn't begrudge the questions? I'm afraid that I wouldn't be shocked if you decided to lie when asked for information which might compromise your identity, or were about things like your age, income, etc. I haven't found that I have subsequently needed the answers to any of the questions I was asked. Some seemed reasonable enough.

(Both 5/13 and 6/15) For the sort of audience my pages usually get, you probably want the full "Developer Default". Given the cost of storage today, having the tools, even if you don't use them, is probably best. In the past, I've had trouble adding bits "piecemeal", upon discovering I did need something I hadn't realized I needed. If you, a good open Source user, don't have Excel, you may have to go to the "Custom" install option. Be generous in what you choose to install... You should probably opt for the Server and all sub-components, In "Applications", de-select the MySQL for Excel. (Keep the Workbench. I'm not sure what the "Notifier" is for, but I kept it in the list of things to install), I installed all the connectors, in hopes that they were small. I installed all of the documentation, which includes sample files.

(6/15) I went for the somewhat dauntingly named "MySQL Installer 5.6... all MySQL Products... in one package." Using the .msi route. This will probably put things on my system beyond just the server... but I have had bad experiences in the past with trying to install the various elements of MySQL in a selective, piecemeal fashion.

Be sure you have a book to read in the long waits ahead...

(6/15) A little way into the process, I was given the chance to choose a setup type. Again, I went for "full".

I don't have Microsoft Office on any of my machines, so early in the process, I was told there were problems with installing some elements of MySQL. I just ignored the fact that the "MySQL for Excel" elements were problematic. I was less relaxed about doing without MySQL Workbench. For this, apparently, I needed the MS Visual C++ 2013 Runtime... but the MySQL installer said it would take care of that deficiency for me. ("Execute" seemed to be the right button to click, and quite a long wait ensued... but eventually, installation of the C++ Runtime package started. Finally, a green tick mark appeared agains the first of my 6 deficiency warning. Then there was another very long wait, with no sign that anything was happening. Five minutes? (Only the "cancel" button was available on the dialog at this point. Sigh.)(6/15)

I really didn't want the Visual Studio Tools, as they appeared only to relate to using MySQL with Excel, but again, I decided that the risks of NOT installing outweighed the alternative. (6/15)

Remember: If you are using the "web" .msi installer, then once you get to the "Installation" stage, you will be facing a long session of downloading and installing. The "Show Details" option will help you remain confident that something is proceeding....

(6/15) Well, along the way, there was a brief message saying the process seemed to have died, but I could click "continue", and hope for the best. This was while I was in the "Installation" phace of the process, and was downloading vast swathes of code. About a dozen different set-up packages appeared to be flowing onto my hard drive, simultaneously. Each had a "% complete" indicator, and as the package finished downloading, the status changed to "downloaded", and a green tick appeared.

As I say, during that (6/15), the "died" message arose, but after I clicked "continue", things seemed to be progressing... but when all but one of the packages had downloaded, and we got to 100% on the remaining one, things Just Stalled.

(6/15) Now, things had seemed stalled a number of times already... but this time I think it was really stalled.

(6/15) I finished two more games of Freecell on the adjacent computer, and finally accepted that things had gone sour with the install. I cancelled out (the only option on offer.) And re-ran the .msi... hoping that the half done things from the first session wouldn't give rise to too many problems. ARGH. I DO like OO and MySQL. If they were to blame, "cost of doing business".... and of course there are other cooks in the kitchen who might be the source of the problems.)

(6/15) The second attempt started well enough. Things I'd previously supposedly solved with the C++ Runtime, and the other package, seemed still solved. The list of things to download (11 items) all showed the download done, apart from the one that had stalled at 100% previously. It started its download again automatically, and promptly.... and died again. Same message as before, but at least it appeared sooner.

(6/15) Started AGAIN, but this time elected the custom install, and attempted only to install the MySQL server and the MySQL Workbench.

(6/15) Ha! Better! Only two games of Freecell, and a worrying hiatus between "99% done" and "complete" for the second element, and then a "Next" button allowed me to progress to "Product Confirmation".

(6/15)

(5/13) I thought it would be easy to enable TCP/IP networking later on, if I did NOT enable it during the set up process. This was probably a BIG MISTAKE. Leave it enabled, even if you only intend to use the server from the same PC as the server is installed on. (Those who know more may be able to tell me this is unnecessary. All I know is that I had several hours of "fun" during something which should have been "simple" (see sidebar)... and un-ticking that box is my prime suspect for the cause of my "fun".)

(6/15) In the Type and Networking dialog, I had to make some choiced I really don't have the experience to make. My guesses?...

Config type: Development Machnine
Connectivity: TCP/IP, port 3306 (the defaults)
Open firewall port for net access. (You should be able to re-close it in your router, if you want to be safe.... which is probably a good idea, until you NEED the port open.
Nothing else ticked... not "named pipe", not "shared memory", not "advanced options".

(5/13 and 6/15)You don't need to set up extra user accounts during setup.... but...

You should, I believe, establish the root user's account with a strong password. You shouldn't need to use it often, so go for something better than "fido". And write it down where you will be able to find it later.

But, as I said, don't worry about other users yet.

(6/15) "Windows Service" dialog: I left "Configure... as a Windows service" ticked.
I un-ticked "Start at system start up"
I said to run service as a standard system account. (The default.)

(6/15) The next window put my heart in my throat... there were 9 steps... some of them major... that would be attempted when I clicked "Execute"... but there was nothing for it but to do so. Sigh. stopped for a bit at "Starting Server". During such things it is always worth checking underneath, or on the taskbar, for "Do you want to allow...?" dialogs "hiding" under main windows. But there were none, and after a short while, the process continued.... successfully to the end, if the screen prompt was to be believed!!! Clicked "Finish", in hopes I was DONE, but no, now there was "Product Configuration" to do.... Or so it said. Happily, clicking next just took me to a "Done" message. Whew.

(6/15) At this point, I did a full restart of the PC concerned, just to clear cobwebs.

(6/15) Whew. Went fine.

(6/15) A very simple, direct test to make now: Find the "Services.msc" mini-app in your system. The "msc" may be hidden, and don't fall for "Services.exe"services. It's icon is two blue gears, at least at this time. Sigh. If in doubt: Just put ALL of that, "services.msc" into the "search programs" edit box. You should then be offered only one "services".

(6/15... and would have applied 5/13, too) Services.msc brings up a simple window with a list of the available services. On a simple machine, this will just be local services. Select the local set, if you have a fancy machine. There may be two tabs... at the ,i>bottom of the window, just to be easy to overlook. You want the "Extended" services tab.

(6/15) Among them, you should see "MySQL56". As they are listed alphabetically, this will not be hard to find.

And, 6/15, in my case it was shown as "started". Not right. Shouldn't have been. I clicked "Stop". Right-clickin the line. Select "Properties". Change startup type to "manual". NOW your MySQL server won't start until you tell it to. (Make it automatic, if you wish... but I don't often use my MySQL server, so prefer not to have the service running all the time. Of course, this means I have to remember to start the service, before I try to use it!! (^_^)

Start of long sidebar... all from 5/13...

If your install goes well, and your server is working for you, you can skip this sidebar! How do you know if it is working? (The "how do you know" here was covered for 6/15 above... but sidebar has more.) Well, use Windows' Run "services.msc" and see that your MySQL server is running. Start it if it isn't. Wait 20 seconds. Right-click. Refresh. Still running? Then start up MySQL Workbench. Try the "Server Admin" module of Workbench. Then double-clicking the "Local MySQL56" item (one of one) on the "list" in the pane above "New Server Instance". If you DON'T get "no connection", you CAN skip the sidebar!

Hmmm. May 2013. I "did" an install, from scratch. And at the penultimate step of the wizard, I got "failed, could not connect...", or some such message. Was this because I had unticked the "start service when Windows starts" box? (I prefer to run Services.msc", and start the service by hand, when I want it.)

"Retry" was an option... but after about 30 seconds, I got the dread "Must shut down" message, and I was "dumped" out of the process, not quite sure where to go!

I started the service, as above.

I started the Workbench... started up okay... whew... I've had mixed experiences, even "just" on XP systems... or was it XP and Win7?

Invoked the "Server Admin" module of Workbench. There was no connection to the server. I did this by double-clicking on the "Local MySQL56" item (one of one) on the "list" in the pane above "New Server Instance".

In near panic at this point, I was scratching my head. As I say, I'd started the service, but something was wrong. In my Windows menu, under "MySQL" there was a "MySQL Installer". Crossed fingers, and invoked this... and "Add/ Modify" was present. Tried that. This went well. Took me through the process again, without the big downloads. I left the Excel "thing" unselected. At "Installation", the wizard wanted to update "Connector/Net 6.6.5"... promising... and when I said "execute", it said "Update success". Seemed to go well. I let it launch MySQL Workbench for me.

Invoked Server Admin, within Workbench, again, as before. Still "No connection". Fiddled about with services.msc, with a few little "excitements" there. Closed both the workbench and services.msc. Tried running services.msc again. MySQL56 service wasn't running. Started it. When I tried, with services.msc, to stop it... I got a message about an internet explorer script error. Sigh. I also found it was "starting"... but not staying started. If I right-clicked on "MySQL56" (in the services.msc list of services), and hit "refresh", I'd find that my "started" service wasn't still running. (Only took about 10 seconds to die.. but remember: You have to do "refresh" to SEE it is dead.)

Went into the properties of the MySQL56 service. On the LogOn tab, changed the "log on as" to "Local System Account", with "allow service to interact with desktop" also ticked. Still refused to stay starte.

Ran the installer again. Removed the server and all (four) of the connectors. (Umm... there was a false start, during which I started to uninstall the complement of that set. Sigh.) Then ran installer again, re-installed what was missing... only "Workbench", it seemed....

That wasn't right! Tried system again, but service still wouldn't stay running. Tried AGAIN with MySQL Installer to uninstall/ re-install the basic Server and the Connectors. This time, progress was more convincing. (I think my earlier false start with the wrong boxes ticked thwarted that attempt.) Also said "Remove server data files... in spite of DOUBLE check from system! ("Are uyou SURE??...")

Re-install went as I thought it might. As before, in the Configuration step, I asked for the "Advanced" options, but UNLIKE before, I left the "Enable TCP/IP networking" ticked. I also created a user account this time, called tmp, p/w xxxxxx (bad passord. You need at least 6 characters.) in addition to the root user.

This time, I checked MySQL56 in services before running Workbench. There was the "old" MySQL56, and a new MySQL-1. The latter was already running, somehow. I accidentally did a "restart" along the way. Successfully stopped and (re-)started it.

By the way... elsewhere you'll read that starting/ stopping MySQL servers from the "Processes" tab of the Task Manager is a Bad Idea. (Just thought I'd re-iterate that here.)

NOW MySQL Workbench worked just fine. The first thing I did was to get rid of the, as I thought it would be, unnecessary user "Tmp". I also got rid of the extra "Server Instance"... the system-provided "Local MySQL56" was fine. Invoking it requested me to enter the password (Server's root user's password), as expected. (You should set up other users as an early priority, with their privileges restricted to what they need.)

(...end of long sidebar)

Somewhere in the install, there is probably something about "Start service when Windows starts?".

I'm delighted to tell you that your answer to that isn't critical. Go with the flow; use the default. I will talk about starting and stopping the service in due course.

Are you tempted to change the name of the service... perhaps so you can tell that "yes, this one is the one I installed"?... I'd suggest you resist the temptation. If you really, really want to do that, I'd suggest that you at least leave the name starting "MySQL". (You may need to find the service in long lists from time to time, and by then you will have forgotten the name you used, see no "MySQL..." and think it isn't in the list.

You may see some "error" messages along the way. Don't be too downhearted yet.

Maybe only when I was trying to do something other than the default, but maybe even then: if you create a new folder for the data as part of the install process, you get a warning that it "already exists". I think this is just to protect you from inadvertently overwriting old stuff. I "ignored" the warning... but I made sure that I wasn't proposing to use an existing folder, or if I was, that it was empty.

5/13, I allowed it to install all the "connectors" it offered. 6/15, I don't think I installed any "connectors". I doubt they take up much disc space, and I didn't want to struggle with how to install a missing one to an already existing server.

When the choice was presented, I un-ticked "MySQL for Excel", as I don't use Excel. (Open Office's "Calc" does all I need, and keeps me out of Microsoft's clutches.)

My notes say: "In a subsequent window...

a) Selected "Configure for development machine".
b) UN-ticked Enable TCP/IP... I didn't need it at this time, decided that improving security, especially for a MySQL novice, trumped convenience later. I hope it will be relatively easy to turn that on in due course.

But, as I told you, it took me many tries to get everything right. Before I was through, I was leaving the Enable TCP/IP ticked. Older and wiser, I suspect that may be necessary even to use the server from a client on the same PC. And I believe you should be able to "lock out" people from the outside world by other means. In fact, letting them in if you want to may be hard. (I will attempt to say something about that... but that is something for much later in this saga.)

(5/13... see next paragraph for 6/15 thoughts) If offered, you certainly want MySQL Administrator, by the way, and, I think, MySQL Notifier. The former wouldn't work for me on a Windows XP PC, but it did no harm "sitting there". It is exceedingly useful... essential?... on a Win 7 PC. We'll come to details o fusing it in due course, and also details of what will work on an XP machine. Also avail yourself of MySQL Workbench if it is on offer.

6/15, I only installed the MySQL server, and the Workbench. As far as I am aware, I did not install the Administrator (I don't think it is still available), nor the Notifier. That (Notifier) was in the long list of things that appeared in my early efforts. In the end, I don't think it was installed (6/15).

(5/13 and 6/15) Windows 7, in particular, and maybe even XP, and probably your anti-malware software will (should!) Not Be Happy that you are trying to install a service on your machine. Grit your teeth, hope for the best, and tell everyone that it is "okay". Beware: In one case on my machine one of the warnings was in a window underneath other things, and I wasted quite a bit of time waiting for the computer while it was waiting for me. Keep an eye on the "running programs" "buttons" on your task bar during the install process. One of the warning questions I chose to say "okay" to asked ""Revocation information... certificate... for this site... not avail. Proceed?"

That should do it! You should have a MySQL server installed! But how can you tell? How do you use it? Patience, Grasshopper! (We've already covered this ground, once, but let's just re-cap, if nothing else...)

This would be a good time to do a full restart of your machine, if you didn't do one with me earlier. I'm perhaps a little manic about doing restarts more often than I need to, but you really have just worked some major mojo... give it every chance to succeed... and/ or throw up "complaints"?

After the restart... do we have a MySQL server? Managing it

Did your efforts to install the server work?

There are a number of ways to find out, but for a variety of reasons, I'm going to recommend... Windows 7 or Win XP... that you run "services.msc"

Windows XP:

A window with "Services" in the title bar at the top should open, with left and right hand panes.

Windows 7:

A window with "Services" in the title bar at the top should open.... as described once already... but here it is again, just to keep the flow going.

Both versions of Windows:

The right hand panes should have two tabs at its bottom: "Extended" and "Standard". In the list for "Extended", you should now have "MySQL..." (in my case, Feb 13, it was "MySQL55" for "version 5.5", 6/15 it was MySQL56.)

Image of running services.msc

Select that service. As soon as you select it, at the upper left of the right hand pane, you should see either "Start the service" or "Stop / Pause/ Restart" the service.

Try starting and stopping it. You may well get a progress bar for a few moments (no more than 15 seconds) each time you invoke a start or stop.

Image of running services.msc

================================
Below here: Still needs edit, to bring in what I learned in 6/15 session.
All of what follows, at the moment, is from 5/13...
================================

That much working? Very good news! (Not working?... Ummm... I don't know what to tell you. Try to GET that much working! (Sorry))

Moving on...

(5/13, and, if you didn't do it already, from description earlier, if needed...) When you have got that much working, have a look on the "MySQL..." service's line under "Startup Type". You will probably see either "Automatic" or "Manual". Right click on the line, select "properties" to change the startup type. I tend to have mine set to Manual, as I don't use my MySQL very often, and why have the overheads? When I want to use it, starting the service is no big deal. With a working MySQL client, set up to a previously established and initialize database, if I try to connect, having forgotten to start the server, I just get a message saying "Can't. Server problems.", and I give myself a DiNozzo, start the server, close the earlier attempt to access my database, and try again. (I suspect that if you set the "Startup Type" to "Automatic", the service will start with Windows. I played a bit with "Automatic (delayed)", wondering if it would leave the service un-started at boot time, but let the process of trying to access the database start the service, but even if that is what they meant... it didn't seem to work for MySQL. Just go for "full" automatic, or go for simple manual.)

You've caught your rabbit! Let's make stew...



Administer the MySQL Server

Remember: With this "client/ server" stuff, there are three things going on:

These concepts talk a bit of "living with" to fully assimilate. I've tried to say the above another way at my basic statement about users, servers and administrators of client/ server systems.

By the way... I sometimes speak of "users" in this as if they are somehow separate from "the administrator". They may be separate people... or they may just be one person, sometimes "wearing one hat", sometimes "wearing" another.

Before anyone can use your MySQL server, you have to tell it about what users are to be allowed access to one or more of the databases on the server.

For each user, the admin has to specify a user name and a password for the account. For now, you can forget all about the "groups" stuff of which you will see hints. The admin has to say what level of access each user has to any database the user has access to.

We'll come to what software you would employ to be a user of your MySQL server in a moment, but first we need to do some work as administrator ("admin"). Which brings me to a little annoyance.

If you are using Windows 7 (or higher), or a different OS, I suspect that the admin package of choice for you is going to be MySQLWorkbench. It was installed on your machine along with the MySQL server, if you followed my advice above. A shortcut to it probably exists in the MySQL folder of your "All Programs" menu, off of the "Start" button. (If you are using Windows XP, give it a try... it may work, but support is no longer promised, and on at least one XP system it didn't work. (More on admin- from- XP in a moment. From Win 98... forget it. Buying an eBay XP machine will be less aggravating in the long run. (98 didn't "do" services nicely.))

I'm sure that when all is fully tweaked, you can run MySQLWorkbench remotely. However, I would strongly recommend that a beginner start with the MySQL server, and client on one PC, and while a novice, the user/ admin would also (of course?) be using MySQLWorkbench (or the XP alternative) on the same PC as everything else. Be a little careful about granting remote access to MySQLWorkbench, because if you succeed in making it possible, and along the way make it too easy, then Bad People will get into your system, and will be able to make changes which will not be good for you.

Bear with me until I say "go back to 'Bear with me...'"?

When you are no longer a MySQL newbie- admin, you may decide that you want to create users who can create new databases. For the moment, because it keeps things simple, I'm going to suggest that you not allow users to do that. Since, for now, you are the only person with access to it all, you can "swap hats" as necessary. Put your "admin" hat on when you want to start a new database. Do that "dangerous" stuff fully aware that you are playing with fire, and get out of "admin" status as soon as possible. Once the database exists, and you have granted a user access to it, you can then log off as admin, log back in, but as the mere user, and, with the user's more limited powers, mistakes are less likely to be catastrophic.

Keep in mind that, in MySQL-speak (and in all "grown up" discussions of the same) "database" is not just a single table. A "database is a collection of tables, etc, all pertaining to a single data management task. Some parts of the collection reside on the server, notably the tables. Others, e.g. the forms for displaying the tables, reside in whatever front end software is used by the client. A database may of course have only one table... but even then, the table isn't "the database". "The database" is a container. It holds one or more tables, and often other things. (And then there's "database" in the sense that "MySQL" and Open Office's "Base", and, heaven help us, "Access" are "databases". Better to refer to MySQL as an RDBMS (Relational DataBase Management System) to avoid overwhelming "database" on its own with too many meanings.

Right! now go back to 'Bear with me...' and re-read the previous two paragraphs, see if I've conveyed my suggestion.

Create a database... i.e. a "container".

We're not going to use either of the "fancy" admin tools to do this at this stage. (They can create databases, and we'll come to that, but, for now, I want to visit a very basic "answer".

Start by choosing a name for you database. I am lucky, and my initials, "tkb", don't overlap with any common word or acronym. So, in your shoes, if it were March 2013, I'd probably call my first, experimental, database "tkb13marfirst".

By the way... MySQL and Windows "fight" when it comes to case sensitivity. And MySQL doesn't like anything except letter, digits and the underscore character in file names. (And I don't like the underscore!) Hence the "poor" name.

Two further thoughts on filenames... and all of this applied equally to table names, field names, etc....

1) I don't like calling things "my..." this or that because MS is so "cute" with those names. Is "My Pictures" MY folder for pictures? Or one created by the operating system or some app?

2) Although noting but my personal discipline enforces the following, I have found it useful for many, many years: If I need to save something, but it is unimportant, and something I won't need later, I give it a name starting "tmp" (for "temporary"). Because I never use "tmp", except thus, any time I come across something called "tmp..." that I don't recognize, I just delete it, un-inspected. If I broke the "Don't call it tmp unless..." rule, I deserve to lose something I didn't want to. (This saves me a lot of opening orphan files to see if they hold something important.)

But! Back to MySQL!...

I was saying that we need to create a database. That is a first step along a road that will consist of....

(I am self-taught in all of this. I am beginning to see why it took me years to get it all straight!)

So here's a simple way to...

Create the database

When you installed MySQL, you also installed something called the "MySQL x.x Command Line Client", where the "x.x" will be the version number of your MySQL. In my case, "5.5", March 2013, "5.6", May 13.

You should find a shortcut to that in the MySQL folder in your "Start/ All programs" list. (If not, please send me an email... I encountered a little mystery while writing this up for you.)

The command line client is similar to, but not the same as the OS, or DOS, command line window... and just as annoying. (Once upon a time, we did everything in such windows. Shudder.)

Start the command line client. You should get a window with a black background, white text, and a request for your password... whether or not the MySQL server is running.

If the MySQL server is not running, the client just disappears after you enter your password and press enter. I told you the command line client was annoying. (Use services.msc to see if server running, and start it, if it isn't.)

The response isn't instant, but after only seconds, not tens of seconds, you should see... "Welcome to the MySQL monitor...."

(And May 13, I didn't get that even when the server (MySQL56_1) was running. Probably a side effect of my bungled install. Instead of fixing that, I used the MySQL Workbench, SQL Development module to execute the SQL commands listed later, (starting with "create database tkb13mardemo;", and (gasp) I did it as the root user. (Bad.))

In my example, I'm going to create a new database called tkb13mardemo.

Enter the following into the MySQL Command Line Client/ Monitor, adapting tkb13mardemo if you wish to... but if you change that, you will have to make corresponding changes in lots of other places. "Copy/ paste" works... but not always as you would hope. For "Paste", if a right click on your mouse doesn't bring up a menu, try seeing if ctrl-v will work. (The latter didn't work... for me.. today...)

create database tkb13mardemo;

If you forget the semi-colon at the end, you will just get another "->" prompt... at which point you can supply the missing semi-colon, at which point you should get "Query ok, 1 row affected".

That's it! You created the database!... which, you remember, don't you, is just a container. And no one has use of it yet, except maybe the MySQL server admin, and you would never, I hope, do "everyday things" with a server admin's account??? (Seriously. I know many of us "cheat" with our Windows admin accounts, but Don't Do It with server accounts!)

You can, actually, do a great deal from the MySQL Command Line Client, but now that we have used it to see that certain things are in place, we're not going to use it for much more. If you didn't get the "Query ok" message, don't go forward... something fundamental isn't right. Get things working as far as this before you try to go on.

Shut down the by entering "quit;". (Don't forget the semicolon at the end.)

Create your first user

Now that you have a server, and you have a database, it is time to create your first user. I hope you will be able to use MySQLWorkbench for this. Alternatively, maybe because you are on an XP system, try MySQLAdminsitrator.

I'm going to write subordinate pages to go into the "create user" details, but whatever tool you use, you will be telling the server about a user it should recognize. The user's name. The password for that user. What databases that user can even see. And what the user can do to or with those tables. That's all.

A draft of the guide to using MySQLWorkbench to set up a user has been put online. Incorporated in that are some "getting started with MySQLWorkbench" notes, too. Start with MySQLWorkbench with the "set up a user" guide.

Use the user

Just as we created our first database using a crude, but few-things-to-go-wrong tool, and then moved on to using something more sophisticated, we will create our first table with things which are already working, even if they aren't the best tools for the job. This will establish that we can create a table. If when we try the better tools they don't work, we will know that the problem somehow lies with the way we are trying to use the "better tool", or maybe with the way it was installed.

This time... to see that we can use the database (though we won't test the user's set up just yet)... we will use something even more basic that MySQLWorkbench. We will use the MySQL Command Line Client to create a table, and put a few records in it.

Start making routine use of the user

Now by means of the user account, and the front end software of your choice, connect to your database. You should be able to see the table. You should be able to see the records in it, and modify them. If you can't, you at least know a lot of things which are not where the problem lies. The problem probably is in the way your front end software is trying to connect to the database, which we have already established as working.

Again... this will be explained in a subordinate page. I'm afraid I can only help you with connecting to your MySQL server with Open Office's "Base" database... but there are many ways to connect. In various corners of my web-space alone, you will find hints and scraps of things about using data in a MySQL database via PHP (to put text into web pages, for instance, and connecting with programs written in Delphi. I've seen posts saying that Lazarus, the "new (free!) Delphi" is also very happy to interact with MySQL databases.

Hey! We're done!

Well... not "done" maybe, but well on the way! We have a server set up. We have a client which can use the server. We have a database, and a first table. Now it is just a matter of building up the database: data needs to be entered, maybe more tables need building. After that, the rest is just "normal" use of the client program of your choice... I like Open Office's "Base"... and before long, you should hardly be aware that you are using a client server setup. It should just be "database work as usual".

The next stage is probably to learn to use OpenOffice's "Base" database as a front end to your MySQL databases. And I have done a guide to that for you: Connecting to a MySQL database from OpenOffice Base.

I lied

Being human, we are, of course, never happy. So far, I've showed you how to get a MySQL server and MySQL client running in a single PC.

At the very least, you need to learn how to back up your database(s)... it is not merely a question of making copies of a few files or folders (directories).

You may well want to have the database server on one PC, but access it from another, across a LAN. This is quite painless, as long as it is just one person using the database at a time. (In theory, multi-user access across a LAN is not a big deal, either... but I'm not experienced enough to say much about how well that works in practice.)

It isn't a terribly huge "leap" to accessing the server across the WAN, i.e. across the internet. You'd have to "do things" with your router, fire walls, MySQL user's settings... but, I don't think, not a lot of rocket science... just lots of fiddly stuff, all of which has to be right before what you want will be available. And once you start opening up access to the server, you run the risk of opening access further than you wanted to. If 0.00001% of the people on the internet are bad people, that still leaves an awful lot out there with direct access to your server.

I've concentrated in these essays on setting up a MySQL server so that you can manage databases on it via a client running, say, Open Office's Base RDBMS ("database"). However, you might want a MySQL database in order to "feed" material to a dynamic web page, using PHP, for instance. All of the stuff in the above will help prepare you for such endeavors.

Good luck! Enjoy!

If you haven't worked it out yet, I'm sorry, but let me tell you: If you just want a database to keep your Christmas Card mailing list in... you don't need all the overheads of MySQL.

But... if you have a want, or if you enjoy a challenge, then there's a lot of fun to be had "making it work". And there are free, multi-platform, well supported, mature software packages out there for you to work (or "play"!) with.





Have you heard of Flattr? Great new idea to make it easy for you to send small thank you$ to people who provide Good Stuff on the web. If you want to send $$erious thank yous, there are better ways, but for a small "tip" here and there, Flattr ticks a lot of boxes which no one else has found a way to do yet. Please at least check out my introduction to Flattr, if you haven't heard of it? "No obligation", as they say!



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

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

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)




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 (tm) freeware, shareware pages <<< PLEASE


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

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page WILL BE tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.


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

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