Question General Overview of MySQL 5.1

[marianne]

Good day Banana,

I have been working on studying and exploring around mySQL, but I cannot fully appreciate it yet because it seems I have limited view of mySQL. Unlike Access, I am mostly aware of it.

Can I ask a you to give me more of like General Overview of MySQL on how it works with Access aside from what we have already discussed from the other posts.

Moreover, do I really need to work in the command window? Cant it be worked out inside workbench?

 

[Atomic Shrimp]

The command-line thing is just standard fare for server-based software - it's written to run on servers that usually do not have any GUI environment (on hardware that may not even have a graphics card).
This isn't a shortcoming - it's a design feature - in order to optimise server performance, it's generally desirable not to run only those processes that are absolutely necessary.

However, for almost any bit of server software, there are graphical configuration tools - they just tend to be separate (and often third-party) offerings, as opposed to what happens in Windows, where program and GUI are integrated and bundled, usually to the extent that they can't be separated.

If the official MySQL workbench doesn't do everything you want, you can probably find a third-party substitute that will - such as SQLYog - from here: http://www.webyog.com/en/index.php

 

[marianne]

thanks for the reply AtomicShrimp. Actually I am starting to learn mySQL, but still needs assistance from you, Banana and others.

So you mean I can do that even without the command windows thing procedures there?

 

[Atomic Shrimp]

I'll help as much as I can, but I'm not an expert in MySQL - I've configured it a few times (usually just forllowing instructions) and I think I grasp the basics of how client/server database setups work, but I'm going to be lost on some of the specifics.

I am intending to set up a test rig so I can play with it sometime, time permitting.

 

[Rabbie]

Remember too that MySQL uses a slightly differenr dialect of SQL than Access

 

[Banana]

Atomic Shrimp basically nails it.

In my old project, we had a server with 10-years old hardware and only 1 GB of RAM. Installing a full OS with GUI interface would have had easily consumed more than 1/2 of its resources. The ability to install MySQL atop a very small and light OS (which had no GUI and basically just a bash shell and SSH daemon... Not much more than that). The OS only used something like 16 MB, which was very conservative and enabled MySQL to do much more.

Likewise, even though SQL Server is much more GUI-oriented, they do provide a shell-only package, and I do find myself typing SQL more often even in their GUI than using the designer.

I'm sure I mentioned before that to grok how things work, you should buckle down and learn the command line. Only once you understand how to manipulate using command line, will the rest of GUI tools make more sense and thus be actually helpful. Without that knowledge, GUI tools are more likely to confuse and obfuscate what is actually happening and lead to frustration & excessive debugging efforts due to not completely understanding the basics.

There are two general classes of SQL you should come to become familiar with when using command lines. (There's probably more but for now, just two) First is 'Data Manipulation Language' or DML. You probably already know this as SELECT, UPDATE, INSERT, DELETE. The second is 'Data Definition Language' or DDL. That's where we use to create, analyse and modify the objects. This can include things such as CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, CREATE USER. And for every CREATE <>, there exists an ALTER <>.

When I started out with MySQL, I easily spent 50%+ of my time simply just reading the manual online to learn various commands expected. I think I mentioned that you really want to be intimately familiar with the manual and how to use. For starters, start with charter 3 which contains tutorials, charter 4.5.1 which discuess mysql command line client itself and charter 12, which discusses DML & DDL).

WRT GUI tools, Workbench are great for developing a schema map & scripting out the schema, but that's pretty much its extent. There's Administrator which is great for handling users & security as well as monitoring the MySQL's working. Query Builder is suppose to be the MySQL's Query Designer, but I've actually never really used it because I find it easier just to type out the SQL in the command line.

Finally, for some more in-depth tutorials, I bet you would have better luck if you googled for something like 'mysql client tutorials'. Even though some tutorials may be for Linux or for PHP, the sections about mysql client still apply and you can just skip over the Linux/PHP/non-related stuff.

Hope that helps.

 

[marianne]

Thanks for the replies Atomic Shrimp, Rabbie and Banana.

I see, so one of the purpose of command-line is for memory optimization. Yes, you mentioned to me that I need to be familiar with the manual.

I guess you have a very good point telling me that I should be more familiar with the command-line methods that just on the gui so that I can appreciate the gui more.

Its just maybe that when I was working with Access, most of the time I am at ease using the gui of access.

 

[Banana]

Yes, but it should be realized that Access and other RDBMS had very different design goals. When they made Access, they were focusing on making it easy for non-programmers/non-DBA people (the marketing people like to call them as "Knowledge Worker" or "Information Worker", but I've never been a big fan of such parlance), and thus Access is strongly integrated with GUI presentations, among other things.

In fact, while it is possible to execute DDL against Jet, it's not always practical and is probably more problematic compared to working via DAO or UI. A good example is Allow Zero Length property; we can set that property in UI or DAO but DDL has no such syntax.

With MySQL (as well as several other RDBMS including SQL Server, Oracle, DB/2, etc.), the design this time assumes an audience of DBA & professional developers who has already spent lot of time developing SQL solutions so they usually sacrifice for performance over user-friendliness. Not that it's not a selling point. In fact, one thing that worked out for MySQL was the fact that it was simple engine to use which was perfect for people who didn't want to download >2GB package and perform complex installation just to set up a server and perform some more configurations before they could even think of designing first table. But nonetheless, they are designed to do their job very well, and having a GUI would more likely burden and crimp on the daemon's performance which is why they're usually a separate applications. It also goes back to the basic architecture; we're now working with daemons while Access, there wasn't any daemon. We basically worked with it like we would have had with Word and Excel. That's probably one of Access's selling point; the fact that you can treat .mdb as a document to be passed around and shared, certainly made it convenient for those people who weren't IT by trade.

But once it becomes a front-end client, I do think rules changes considerably and several things we could do in Access now may be injurious to the whole performance because we're no longer working with a document but rather a daemon.

Maybe those two articles will help to explain the workings? (Though you may already know the general answers that those two articles discuss, the concepts Albert Kallal discuss are what I want you to learn about.)

Splitting database
Running Access on WAN

 

[marianne]

Hi Banana,

I was reading the documentations and references in mysql.com

I was wondering, do you know where I can download the full manual of mysql because not all of the topics in http://dev.mysql.com/doc/refman/5.1/en/ and http://dev.mysql.com/doc/ can be download in pdf file.

I am not online most of the time so I may need to download the full manual of mysql to my residence. Can you tell me where to download it. Or perhaps you have your own that you can share ! Thanks.

 

[Banana]

Actually, there is a PDF you can download. Look at the table listing various documentation; the manual is along them, and look in PDF column.

Here

 

[marianne]

are you referring to http://dev.mysql.com/doc/ on the MySQL Reference Manual heading and column PDF and or CHM?

Were those the compiled reference from
Browse MySQL Documentation by Topic Heading?

 

[Banana]

Actually, the section right under the Browse.... Reference Manual

 

[Atomic Shrimp]

GUI tools for server administration often have an option to show the command-line conversation they're having with the server (FTP clients are a good example of this, but I've seen it elsewhere).

Not sure if that is a feature of any of the MySQL GUI tools available, but if it was, it would be a really useful one - use the command line wherever you can and let the GUI teach you when you get stuck.

 

[Banana]

Are we talking about "how to build query" cases or more generally?

While there's not really anything like that for MySQL using their tools, there does exists several third party that provides such service. IINM, dbForge is one such example, and possibly Toad, too. I had used Navicat which I really liked how it administered the server for security though it was missing the query builder. All three tools also had some extra support in helping writing stored routines as well.

But using mysql client is pretty simple. Unlike SQL Server, there's not really much administrative commands to issue and most of those would be done via SET [GLOBAL] <variablename> or via my.cnf file.

 

[Atomic Shrimp]

Are we talking about "how to build query" cases or more generally?

More generally, I guess - as I say, I've seen it a lot in GUI front end tools for linux command line software. It can be quite a good way to get a handle on advanced use of the CLI.

Even with GUI tools that don't support it natively, there's probably a way to point them at a proxy that would log the conversation.