Managing and Using MySQL: Second Edition

Geekboy(Wizard) writes: "MySQL has been used as a database for many sites and products. The U.S. Census Bureau, Slashdot, Yahoo Finance, and many other high-traffic web sites use MySQL for their database needs. It is a popular choice for databases, as it is GPL'd and thus free from costly licenses. If you need to use it in a proprietary application, you can purchase a non-GPL'd version from MySQL AB." Read on for Geekboy's review of Managing and Using MySQL: Second Edition, which he describes as "the only thing you /need/ to make a usable database." Managing and Using MySQL: Second Edition author George Reese, Randy Jay Yarger and Tim King, with Hugh E. Williams pages 425 publisher O'Reilly & Associates rating 9/10 reviewer Peter Hessler ISBN 0596002114 summary Step by step tutorial to using MySQL, with included tools, and your favorites.

Managing and Using MySQL: 2nd Edition assumes that the reader has a little knowledge of databases, although some of the commands are difficult to get your head around until you have a database to test them on. Sample commands, and output for them are shown in the book, but a sample database isn't shown until chapter 9. MySQL took me through 'MySQL land' with the greatest of ease. Aside from the lack of a test database in the beginning, everything was very well laid out. It started with the basics, and worked up to full implementation and administration.

Part I introduces the reader to MySQL. It begins with a nice history, design elements, features and what you would want to use MySQL with. Nicely detailed instructions for downloading, and installing the current version. Information is provided for Unix, and Windows systems, with examples for FreeBSD 4 and Windows 2000. Basic SQL commands are presented, with sample queries and relevant results.

Part II introduces the reader to tuning, securing and designing the database. Several pitfalls are described, along with instructions for avoiding them. The realm of tuning is divided into application tuning, database tuning, and operating system/hardware tuning. Security is discussed from all aspects, but a database that has security concerns will need extensive testing and evaluation. Database design starts with the design on paper, with both the theoretical, and practical aspects. Once the paper design is drawn out, MySQL assists the reader through the actual making of the database.

Part III describes integrating the database into your favorite programming/scripting language. In the opening paragraph of chapter 12, The C API, the author states: "In this book, we examine several different programming languages: Python, Java, Perl, PHP and C. Among these languages, C is by far the most challenging." The information was presented in a way that readers who are new to C would have small difficulties with, and that intermediate and advanced users would find quite useful. The authors presented enough information, that I was able to write programs that interfaced with MySQL in less than 10 minutes. Perl scripts are presented in the same way. Knowledge of the language you will be integrating MySQL into is necessary, but the book presents and explains the rest of the information.

Part IV is the reference portion of the book. All great books that teach contain a reference section, and this book is no exception. The basic SQL syntax and command set are described, as well as the data types, numeric, string, date, and complex. Operations and functions are explained, as well as their order preference. The PHP API, the C API, and the Python DB-API are also fleshed out in nice detail.

Overall, the authors have an excellent introduction to SQL databases, and MySQL. Full examples are included for each topic, with full explanations. The only things I would change, would be to have a sample database in the beginning of the book, and to have a copy of that database online.

Table of Contents
Part I (Chapters 1 - 4) Introduction
1 MySQL
2 Installation
3 SQL According to MySQL
4 Database Administration
Part II (Chapters 5 - 7) MySQL Administration
5 Performance Turning
6 Security
7 Database Design
Part III (Chapters 8 - 14) MySQL Programming
8 Database Applications
9 Perl
10 Python
11 PHP
12 C API
13 Java
14 Extending MySQL
Part IV (Chapters 15 - 20) MySQL Reference
15 SQL Syntax for MySQL
16 MySQL Data Types
17 Operations and Functions
18 MySQL PHP API Reference
19 C Reference
20 The Python DB-API
Index

You can purchase Managing and Using MySQL: Second Edition from bn.com. Slashdot welcomes readers' book reviews -- to submit yours, read the book review guidelines, then visit the submission page.

Comments

Let's get this out of the way right now

[Anonymous Coward]

Right, MySQL story. To save everyone else the trouble, I'll go first.

MySQL rocks!!
No it doesn't, PostgreSQL rocks!
MySQL is faster.
No it's not.
Yes it is.
Use Oracle if you want a serious database.
I run a top-5 US bank with MySQL on a P166MMX and it runs great why do you need Oracle?
MySQL doesn't have transactions yet!
Yes it does
No it doesn't
PostgreSQL rocks!
No MySQL rocks!

Re:Let's get this out of the way right now

[jayhawk88]

You forgot to flame MS-SQL. I believe the correct position would be right after the Oracle zealot comment.

O'Reilly

[Beatbyte]

I've been reading about 100 pages a night of this book and its awesome. Personally I'm using it for web applications and if anyone of you are doing the same, also pick up Web Database Applications with PHP & MySQL (ISBN: 0-596-00041-3). Complimenting each other in the very best way.

Every time I read an O'Reilly book it reminds me of why they're the only thing in my book case (of the computer nerdish-tech know how sort).

Awesome stuff.

Best Win32 Front End I've Seen

[WellHungYungWun]

http://www.anse.de/mysqlfront/ This is hands down the easiest tool for constructing databases. I use mysql for keeping track of millions of records, and this tool has proven stable. It is an "Enterprise Manager" like gui that could make a pointy haired boss a mysql guru in no time. My .02

Web application security

[ppetru]

Does anyone know of good references that cover the security of web applications from the ground up?

Yes, try the Open Web Application Security Project. They released a very informative paper on building secure webapps, and it's free.

(I'm not affiliated with them in any way)

Re:Front End ?

[nomadlogic]

check out the openoffice front end. there was a posting a little while ago about that. basicly it's a DB frontend built into openoffice. i've already started using that to convert ASP/Access junkies ;-)

Great, now we're going to have a war...

[Wee]

I can hear it now: "MySQL sucks", "It doesn't have triggers|rollbacks|stored procedures|nested selects", "It isn't PostgreSQL|Sybase|Oracle", "It's not a real RDBMS", "It uses a bastardized version of SQL", "It stole my woman", "It owes me money", "It called the Pope bad names", blah blah blah. Guess what? It doesn't have those "features" for a reason! MySQL was meant to be fast and small, that's it. It was meant to fill in the gaps left by mSQL and to drive web sites (quickly). It's a feature-rich, glorified flat file, ok? And it fills its intended role quite well.

You can't even mention MySQL without the know-it-alls coming out of the woodwork. You'd think the mere mention of MySQL offends their sense of personal or national pride or something. If MySQL isn't your taste, doesn't meet your needs, isn't robust enough, whatever, then don't use it. Use PostreSQL or another RDBMS. Why people continue to begrudge other's use of MySQL is beyond me...

-B

Re:Great, now we're going to have a war...

[MattRog]

I would agree to that if it wasn't the de-facto open source DBMS, and if the MySQL marketing-machine didn't *scream* otherwise.

[OT] Can anything get a rating besides 9?

[ajakk]

I thought it was just me at first, but do any books that get a rating that is not 9 get posted? I went to the book section and checked out the most recent reviews. Of the last nine books reviewed, eight of them got a nine, and the other got five stars (out of five I assume). The two book reviews before that (both in the Java meets XP subject) got 7's.

I would like to see someone review a book that isn't very good, and tell us why. It almost seems that these great book reviews are being pushed so someone can make some bucks through an affiliate program.

non-GPL version

[Enry]

you can purchase a non-GPL'd version from MySQL AB

Yea, until MySQL AB decides they don't want to do business with you anymore. Then your commercial version becomes GPL'd and they take you to court. NuSphere licensed MySQL from MySQL AB and look where it got them.

Something Slashdot should run....

[Lord_Slepnir]

SELECT * FROM trolls WHERE IQ > 70; Error: 0 rows returned

Exactly. Precisely. But only used to be...

[A nonymous Coward]

When MySQL made no bones about it, before they tacked on a lot of bandaids to make it LOOK as if it supported transactions, before they decided to pretend they weren't just a simple DB with a hacked up SQL front end -- they were a great, simple, lightweight, read-mostly DB.

But then they got defensive, tried to claim that transactions weren't really necessary or even useful, tacked on transactions anyway, added other gorp to make it look like they could compete with PostgreSQL (the ACID free source DB), and that's when I lost all respect for them.

It was bad enough they made all their own little extensions to SQL (timestamps in a row automatically update even if not part of the update statement, etc). At least they had the lightweight speed king crown. Now I won't touch it, because they are going to slow it down with all this tacked on overhead. I simply don't believe that stuff can be added on afterwards while retaining the speed it was famous for. Those fancy features have to be designed in from the start, not riveted on afterwards. They are going to lose the niche they have all to themselves, by trying to compete in the ACID world, with established polished competitors. They will lose what they have by trying to grab what they can't win.

PHP + MySQL = use ADOdb

[Kozz]

For those who use MySQL or other database systems with PHP, I highly recommend using the ADOdb Database Library for PHP. It's feature-rich, robust, and has a smaller and smaller footprint with nearly every successive version. I use it all over the place, and it's very very handy. Sure, there are other abstraction libraries out there, but this one takes the cake, IMO. Check out all the supported database drivers!

Re:MySQL

[highcaffeine]

Your statement shows you don't understand the real benefits of stored procedures. Say you have a very common task that, unfortunately, needs to work on a large set of data, but consistently results in only a few rows of data when it has finished going through all the data. Now, which of the following two options is better:

1. The original large set of data (say, 10MB) is transferred over the network to the front end machine which then runs through it's motions and trims that dataset down to the final 8KB of data actually needed. Aside from the IO, both machines need to reserve 10MB of memory to store the data set, plus additional memory for intermediary data structures while the code works.

2. The original 10MB of data stays on the database server, never being sent over the network to the front end machine. The stored procedure works on the original data culled from the database, does it's magic, and then transfers the final 8KB worth of data over the network to the front end machine. While the database server still needs to allocate 10MB for the dataset plus memory for intermediary data structures, the front end machines only has to allocate 8KB of memory for the final results -- plus, 10MB of data never needed to be transferred over the network.

If you answered #2, you've just given one example of why stored procedures can be a far better way to handle certain problems. They're no panacea, but "emulating" stored procedures in your front end application is a horrible way to justify not providing stored procedures in a database server.

If that example wasn't enough for you, consider the following.

You have an enterprise department consisting of a few hundred employees all running a GUI application which connects to a database to allow access to financial/customer/product/whatever data to all the employees. Various inquires in to the data need to compute values, collate data, or perform other complex operations on tens of thousands of ledger entries/customer accounts/products/whatevers.

Now, choose one of these two options:

1. You embed all, and I mean all (since you don't have stored procedures), logic relating to these inquiries in to the GUI application. You then update each workstation with the new version of the client to support the changes. This involves rolling out the new version to hundreds of machines, causing worker downtime for each machine (or a few very late nights and expensive overtime for your IT staff). Then you find out a few days later that your QA staff didn't catch a very problematic bug that affects half the staff. It turns out the bug was very easy to fix, but you now have to redeploy the updated application to the hundreds of machines -- again.

2. Your GUI application does not contain any of the logic relating to munging/collating/etc. the data in question. These are contained as stored procedures inside the database server. Your client application, installed on hundreds of machines, simply call this procedure on the database. You update the stored procedure and instantly all clients are now using the new version. A couple days later, you find out QA didn't find a problematic bug. Turns out, it was easy to fix, and in minutes all of the hundreds of machines now use the fixed version of the procedure.

As my last example, how exactly do you propose to be able to create effective triggers without some form of a stored procedure? Triggers are a wonderful feature (also lacking in MySQL) that go hand in hand with stored procedures. But, I've already been long winded enough in this post, so I'll wrap up.

Are you starting to get the picture? If stored procedures weren't so useful, nobody would want them. The real problem is that people who don't understand databases or database application design *think* that stored procedures can always be effectively "emulated" in the client layer, and so they *think* that stored procedures aren't useful.

I'm not sure which niche MySQL is supposed to fill

[cartman]

I regularly hear the comment the MySQL lacks features on purpose, because it occupies a niche that does not require those features. It is a "lightweight" database and it supposedly fills that role well.

I have never been able to tell which niche MySQL is supposed to occupy. Is there really a niche where it doesn't matter if the database is corrupted? Is there really any niche where it doesn't matter if transactional integrity isn't maintained? MySQL does not provide any recovery functionality!

I can understand the point in lightweight software. But ACID features and transactions are at the very core of what constitutes a database; they are not "bloatware features" like the microsoft paperclip. Having a database without data integrity is like having a word processor which can't save files. It doesn't matter how lightweight it is.