Page WILL BE tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.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, 3/13-3/21, Sheepdog Software.
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.)
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.
Before you struggle with this, consider an alternative. If you install "WAMP" or "LAMP", you get MySQl along the way. If you are ready for a MySQL server, you are probably also ready for a Windows server. And even if you aren't, one dose of pain might be better than the two or three you will endure if, later, you discover that I was right. Get WAMP (free) (or LAMP... the Linux version) now, have your pain Done With!
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.)
AND, I used the page again in May 2020, to use the MySQL with (initially) Open Office (vers 4.1.6) Base under Windows 10. That time, I installed MySQL 8.0. It appeared that at that time, for Windows, only a 32-bit version of MySQL was available, so I went with that, despite being on a 64-bit machine, with a 64-bit Office. (It turns out that the "32-bit" only refers to the Installer package. With it, you can install the 64-bit version of the MySQL server.)
Paragraphs starting or ending "(5/13)" are comments specific to my May 2013 experiences. Paragraphs ending "(6/15)" relate to... you know.
And you can guess what "(5/20)" means, if you see it, or you shouldn't be trying to install a service.
There were, and probably are, various "MySQL"s you can install. The free one is referred to as the "Community", or "General Availability" ("GA") releases. (When you have ot all set up, you will see "CE" at the end of various things. I'm guessing that this is to indicate "Community Edition".
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! In 5/2020, things were again quite simple... if you get to the page with LOTS on offer, what you want is "MySQL Installer for Windows". In 2020, There was a choice between two .msi files: a small (24Meg) one, and a monster .zip (420Meg). I went with the small .msi file. I simply downloaded that and then ran it. (I then had to download all sorts of stuff along the way, but NOT bits of the package I wasn't going to use.)
Very early on, you will be invited to wait while the installer does it's thing. Be sure to respond to the usual "User Account Control enquiry about whether you want to do this, or you will wait in vain!. And, as usual, it was hiding under other things, but had a gently flashing icon in the taskbar.
(5/2020) Also, early on, you have to tell the installer the "setup type" you require.
You may at some point be invited to choose where programs, and where data should be located. Hurrah! But!.... resist the temptation to set the program or data folders to the locations you would like. It is one of those things which "should" work... but I failed to get it to work, back in 2016, and had lots of hassle untangling the mess. Do NOTICE where these things are, though. Make a note of the information.
You may hate me after the next five minutes, but I'm going to suggest that you go for the "custom" option.
Click next, and you go to "Select Product and Features".
(5/2020)I took something from every high level category... but note, you have to "drill down", by clicking on the "+" signs. Only when you are well down the tree, and have something selected, can you use the arrow to copy it from the "Available" list to the "To Be Installed" list.
Why all the "old stuff"? I would guess so that someone who, say, installed just the server 6 months ago, and now wants to add the client, has access to the now-old version.. the one that will match the server they have. As I said.. that's my GUESS.
I was well out of my depth by now, but I think (hope!) that in general, you just need a set of all the latest versions, 64-bit, if that's an option, if you are on a 64-bit machine. That's what I did, anyway.
You did me a favor! When I came to review what I'd selected, along the course of writing it up for you, I found a few things unticked that I felt I want TO click. So thank you.
(5/2020)I ended up with the latest version, 64-bit if avail, of...
Choices made, I took deep breath, clicked "Next".
(5/2020) Oh great. My machine, it seems hasn't yet been sullied with the C++ redistributables. The installer said it would try to deal with this for me. I clicked "Execute" to launch that. I had to click various "Okay"s and "I agree"s, but it seemed to go without a hitch. Whew.
(5/2020) That brought me to "Installation... the following will be installed...". I clicked "Execute", and went off to make dinner... which is a bit of a pity, because the installer puts on quite a show, keeping you appraised of the various downloads, and multiple installs.
And then... 15 minutes?... it was time to click "Next" again...
(5/2020) Now "Product configuration" began, for the Server, the Router and the Samples and Examples.
(5/2020) The first choice for the server, for a novice like me with un-fancy needs was easy: "Standalone".
(5/2020) Configuration type: Development computer.
(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 possibly a BIG MISTAKE. I suggest you leave it enabled, at least for now, 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"... and un-ticking that box is my prime suspect for the cause of my "fun".)
(5/2020) Connectivity: I went with the default "TCP/IP/ Port 3306/ X Protocol Port 33060, and, with some trepidation, left "Open Windows Firewall ports for network access" unticked. I suspect for using the server just for client son the server, that could be UN-ticked. i also suspect that you can change it (either way) later.
(5/2020) I didn't elect to avail myself of either a named pipe, or shared memory.
(5/2020) I funked going into the "Advanced configuration and logging" options.
(5/2020) Clicked Next. Bringing on...
(5/2020) "Authentication method": I had an old Open (not Libre) Office database, in an old MySQL, that I wanted to access. I didn't dare go for the probably better Strong Password Encryption, as much as I would have liked to, on several grounds. Clicked Next...
... and came to a part of the MySQL story that I have never fully understood: Accounts, and that lack has been a real nuisance. I think what this part of the story is about is (accounts for) Users.... and in the course of updating these notes, I think my grasp of users has come on leaps and bounds... thank you! If this is "only" about the accounts for users, then if you've read my introduction to database servers material, you should be okay with the next bits.
(5/2020) There is a "MySQL Root Password." The "root" user's account, the account you are setting the password for here, is, I think, a bit like an "Admin" account. You should assign a strong password here. The good news is that you won't need to operate as this user very much, and so won't have to enter that long, complex password often. It will be the password that user "root" uses to get into the system.
I'm confident that you can make, or edit, "User Accounts" later, using the Workbench.. but I set a first one up now, anyway. It can always be taken off later, if you tire of it... Username (Choose something that works for you. For our example, I'll go with "first_usr_for_example")
You must specify "Host". When I was actually doing a MySQL server install, as I wrote this, I used <All hosts (%)>. Since then, I've had second thoughts. I believe "localhost" would be better if the dialog will accept that. (You can change it later... either way... in the MySQLWorkbence, which you will meet later.) "Localhost" will impose some limitations... and provide more robust security. I'd go with that, if you can.
Returning to what we were doing:
I made the user being created a "DB Manager", Authentication: MySQL, password (see records). Said okay to pop-up dialog. (This seemed to be the weakest user I could create- here. I suspect that when you have the server in place, and start creating other users, you'll find that in the tools you'll use then... probably "Workbench", you can create less powerful users.
pcva2root20mayThe above left me with two user accounts: "root" (super powerful, but you NEED at least one super-powerful user), and "first_usr_for_example"... enough for now... so I didn't add any more, just clicked "Next"...
(5/2020) Windows Service: Yes! I want it operating as a "Windows Service". I UN-ticked "Start at System Startup. (The issues of whether and how to have it start when your Windows re-boots are covered in ), and told it to run the service as a "Standard System Account". Clicked "Next...
(5/2020) As for two steps earlier, it showed me what would happen next. I did NOT need to "tick" any of the little circles. They were for showing completion. I only needed to click "Execute."
(5/2020) You might want to "copy/paste" the log, and save a copy using a basic text editor, but I think it will be on the system, anyway, if you ever need it.
(5/2020) HURRAH!!! I got "The configuration for MySQL Server 8.0.20 was successful. Click Finish to continue." So I did.
Oh... Turing. Dinner was beginning to burn, and clicking "Finish" a moment ago finished the configuration of the server. I hadn't realized that I would then have the configuration of the router and samples and examples ahead of me :-(
You, lucky person have just learned that this is No Big Deal. Whew.
(5/2020) Configuring the router. This should be interesting, because I have little idea of what the router is. (It isn't the sort of router that connects things in your LAN to the internet. At least I don't think so.) (What I did doesn't seem to have left me with problems!)
(5/2020) First tickbox: "Bootstrap MySQL Router for use with InnoDB cluster". This was unticked by default. Earlier, there was mention of InnoDB, and I avoided it then. I'm avoiding it now.... which leaves everything else grayed out. Hurrah! And so I can click "Finish", and that finishes the router configuration! :-)
(5/2020) Joking aside... I have a hunch that this is something you could re-configure later, if the want arose.
(5/2020) And so, finally, configuration of the Samples and Examples. Here we "choose" which server to connect the samples and examples to. I put "choose" in quotes because as we have only the one, this is simple. Then we're asked who will use the samples and examples. Don't worry! It doesn't have to be only one of the server's users. In the simple place where we are, we'll start by letting the user "root" be a user of the samples and examples. That user will, for now, be the only user of those databases.
To configure it thus, we give user(name): "root" (the root user's name(!)), and then we supply the password we chose for the root user, and then we click "Check", to see if we've remembered/ entered the name and password correctly.
(5/2020) Read the helpful remarks in the dialog: The user of the samples and examples must have "root privileges", i.e., I think, the user must be a powerful user. If you've created one or additional users with root privileges, I suppose they would have access to the samples and examples.
(5/2020) Seems odd to me... in other words, I'm probably confused about something... The idea that "ordinary" users can't(?) access the samples and examples seems odd. But I think... certainly HOPE!... that the server is now nearly set up, and running. I believe that to use the examples fully, the user needs root privileges. I doubt that other users, or perhaps users with SOME root privileges are unable to make any use of the samples and examples.
(5/2020) We've only to do one more step: Apply configuration, and we don't have to make any choices first. We just click the "Execute" button. The "checking" part went very quickly, and the process moved to "Running scripts..." and just sat there for what seemed a long time. No indication that anything was happening. But it probably wasn't as much as 40 seconds. But it felt like a long time! Before long in real terms, "Running Scripts" finished, and got a green tick. (^_^) (^_^) !
(5/2020) Again, you could "copy/paste" a copy of the log to your text editor, but I don't think you need to.
(5/2020) Brace yourself: Click the "Finish" button... but, being braced, I hope you won't be alarmed to find that...
It looks like you're back at the start of the "Product Configuration" step!!
Don't worry!
Click "Next", and you move on to the "Installation Complete" page. Hurrah! Done! You may or may not want to start either or maybe both of "MySQL Workbench" or/and "MySQL Shell". Make your choices (I wouldn't click either at this stage- you need to take a break!)
Click the "Finish" button.
Sorry to stop rather suddenly there... but I have to turn to other things just now. We need to talk about starting/ stopping the server, and about connecting to it. Stay tuned! If you see this after 24 May 20, please write and complain!
I have been setting up MySQL servers for Open Office, the elderly cousin of LibreOffice, for quite a while. I'm not sure that they are good for much, but I have preserved scraps from old versions of this page, if you want to go look, see for yourself.
Apart from the footer, the rest of this is all OLD STUFF... On it's way to the "scraps" page. but not everything old is useless. You may find helpful notes or comments.
If I were you, I'd skip over the scraps, visit the footer... if I even did that! Unless you are a glutton, in which case, follow some of the links in the above which you left for "later".
========================== v v v IN HIST V V V V BEING CULLED FROM THIS PAGE v v vIf 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 started.
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 you 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 password. 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.)
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"?
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.)
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.
================================
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...
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.
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...
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.)
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.
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.
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.
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.
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.
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.
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.)
! 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.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page WILL BE tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.
. . . . . P a g e . . . E n d s . . . . .