ThetaJoin Blog

On the front cover of MySQL Administrator’s Bible is a sentence that reads:

“The book you need to succeed!”

I must say, I do agree. Authored by two very experienced DBAs, Sheeri Cabral and Keith Murphy, they’ve combined their talents to cover what you really need to know to succeed. This book is very versatile. If you’re new to MySQL, or experienced in another database and have to start administrating MySQL, you need this book. I can honestly say, even if you have years of MySQL experience, you will learn something new. I did. Divided into four parts, MySQL Administrator’s Bible covers your First Steps with MySQL, Developing with MySQL, Core MySQL Administration and Extending Your Skills.

First Steps with MySQL starts with a gentle introduction to MySQL with company information, which seems to be changing annually, and most importantly, the MySQL community itself. What makes MySQL so fantastic is the community. After that, you’ll be lead into installing and configuring MySQL on various platforms including Linux, Windows and Solaris while touching on post installation configuration too. Basic security is covered as well as some tips on troubleshooting and accessing your new MySQL installation using tools included with MySQL or using third party software.

Developing with MySQL covers the MySQL Language Structure and if you’re coming from another RDBMS, it covers how MySQL deviates from the SQL standard by extending that standard to make MySQL the number one open source database used on the Internet. After that, this section covers the same type of topics covering just about any other mainstream databases such as using stored procedures, cursors, events, views and transactions.

The Core MySQL Administration is the heart of this book. It covers MySQL server tuning, covering all major storage engines including MyISAM, InnoDB, Falcon, PBXT, and NDB engines including the first time I’ve seen in print, the Maria storage engine. An entire chapter is devoted to implementing cache tables and using the query cache. Memcached is also mentioned, and mentioned again in the final section. Continuing on with what I consider the most important job of a DBA, backup and recovery. Databases are very central to running a business, any data loss could put a company out of business. Be prepared.

This section gives a solid introduction to the topic of dealing with users, and how they are managed within MySQL. Count on covering GRANT/REVOKE, using SHOW GRANTS and mk-show-grants MaatKit tool. Partitioning, logging and replication and measuring performance rounds out this section.

If you have experience with another RDBMS, plan on spending a significant amount of time in this section. Not that the other sections aren’t important, they are, but this is the bread and butter of what a MySQL DBA does on a daily basis.

Extending You Skills section can be considered getting your Masters in Database Administration. Just about every DBA will have to tackle improving queries and the tuning of indexes. The second most important job of a DBA is monitoring performance of your MySQL server. Don’t let your users be your first line of monitoring! Be proactive, there are plenty of open source monitoring tools available. The most popular are discussed, as well as MySQL Enterprise and third party companies too. MySQL Data Dictionary is covered in in detail over 58 pages. This is the most I’ve read in any book about the data dictionary. 

Last but not least, most high performance MySQL systems involve scaling up or out. It covers the usual suspects of replication, MySQL Cluster, and memcached. MySQL Proxy is initially covered and has an appendix to expand on that information. MySQL Proxy itself is worthy of its own book. (hint, hint :) ) Two more appendices cover MySQL Functions and Operators, and additional resources.

Even though this book targets MySQL 5.1/6.0, there is plenty of information that will apply to 5.0. If you’re still on 5.0, don’t hesitate to pick up a copy. This will be a book that can stay with you as your upgrade to 5.1 and beyond. The companion website – http://www.wiley.com/go/mysqladminbible contains all the code from the book too, rounding out this fine tome.

What didn’t I like about the book? There are only a couple of things, all personal I’m sure. First, I really don’t care too much for tables of options from the various tools. Most open source tools are developed rather quickly and options change. This could render portions of the book out of date quickly.

The other thing I noticed that wasn’t mentioned in the book was the community versions of MySQL supported by Open Query and Percona. The latter has their own storage engine, XtraDB and backup solution, XtraBackup.

All in all, this is a very solid book on administering MySQL. This book digs deeper, the experience of the authors really show. Well done Sheeri and Keith!

What do you do when you're in a situation where you REALLY need your favorite Maatkit tools, but are not allowed to load software on the server running MySQL, AND you're not allowed to install software on the provided workstation, especially a workstation that's running software from a large corporation in Redmond? Portable Maatkit to the rescue!

DISCLAIMER: I've only tested a few maatkit tools, namely mk-find and mk-query-digest, but not completely. Some features and some tools may not function, such as mk-audit. Use your best judgment. I'm not responsible if your workstation or server looses all your data, your USB key bursts into flames, or your significant other leaves you. YMMV, use at YOUR OWN RISK!

Strawberry Perl has been out for some time now, and recently, a portable version of Strawberry Perl has been available. Download the the latest 5.10 version of Portable Strawberry Perl and unzip it onto your USB key. Also download and unzip to your USB key the latest copy of Maatkit. After unzipping everything, your USB key should look something like:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\>dir
 Volume in drive E has no label.
 Volume Serial Number is BEEF-FEED

 Directory of E:\

01/29/2009  11:13 PM               116 Strawberry Perl Website.url
01/29/2009  11:13 PM                50 Win32 Perl Wiki.url
01/29/2009  11:14 PM    <DIR>          cpan
01/29/2009  10:29 PM    <DIR>          licenses
01/29/2009  11:14 PM    <DIR>          perl
01/29/2009  11:09 PM    <DIR>          ppm
07/23/2009  01:20 PM    <DIR>          maatkit-4047
01/29/2009  11:13 PM                51 CPAN Search.url
01/29/2009  11:13 PM                52 Perl 5.10.0 Documentation.url
10/16/2008  09:01 PM             1,861 portable.perl
12/24/2007  03:44 PM             1,406 Strawberry Perl Website.ico
01/29/2009  11:14 PM    <DIR>          c
               8 File(s)         55,836 bytes
               6 Dir(s)   1,848,025,088 bytes free

The portable version ships with DBI installed, but not DBD::mysql. To install DBD::mysql, use the Perl Package Manager (PPM) that comes with Strawberry Perl.

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\> cd perl\bin
E:\perl\bin> ppm
PPM interactive shell (0.01_01) - type 'help' for available commands.
PPM> help
Commands:
    exit              - leave the program.
    help [command]    - prints this screen, or help on 'command'.
    install PACKAGES  - installs specified PACKAGES.
    quit              - leave the program.
    query [options]   - query information about installed packages.
    remove PACKAGES   - removes the specified PACKAGES from the system.
    search [options]  - search information about available packages.
    set [options]     - set/display current options.
    verify [options]  - verifies current install is up to date.
    version           - displays PPM version number


PPM> search mysql
Packages available from http://cpan.uwinnipeg.ca/PPMPackages/10xx:
DBD-mysql [4.005] A MySQL driver for the Perl5 Database Interface (DBI)
Packages available from http://ppm.activestate.com/PPMPackages/5.10-windows:
ApacheMysql                  [0.3 ] Initiate a persistent database connection
                                    to Mysql
DBIx-Mysql-InformationSchema [0.04] Perl module to access the mysql
                                    INFORMATION_SCHEMA view, which contains
                                    database metadata.

Install it:

PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 1018351
Files found in blib\arch: installing files in blib\lib into architecture depende
nt library tree
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.bs
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.dll
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.exp
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.lib
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.pdb
Installing E:\perl\site\lib\Bundle\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql\GetInfo.pm
Installing E:\perl\site\lib\DBD\mysql\INSTALL.pod
PPM>

Change to the maatkit directory on your USB key, and prepare it for installation:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\maatkit-4047> e:\perl\bin\perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for maatkit
E:\maatkit-4047>

I know this really didn't do much, but it's a good check to make sure all dependancies have been met. Don't install maatkit at this point! You don't need to.

Now, take your USB key to the workstation and plug it in. Once it has been recognized, open a command prompt by going to the start button, click run, then enter cmd into the dropdown box. Navigate to the your maatkit\bin directory.

I really needed mk-find for my task. Here's how to run mk-find with portable Strawberry Perl:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">
F:\maatkit-4047\bin> f:\perl\bin\perl mk-find --help
mk-find searches for MySQL tables and executes actions, like GNU find.  The
default action is to print the database and table name.  For more details,
please use the --help option, or try 'perldoc mk-find' for complete
documentation.

Usage: mk-find [option]... [database...]

Options:

  --ask-pass          Prompt for a password when connecting to MySQL
  --case-insensitive  Specifies that all regular expression searches are
                      case-insensitive
  --charset       -A  Default character set
  --config            Read this comma-separated list of config files; if
                      specified, this must be the first option on the command
                      line
  --day-start         Measure times (for --mmin, etc) from the beginning of
                      today rather than from the current time
  --defaults-file -F  Only read mysql options from the given file
  --help              Show help and exit
  --host          -h  Connect to host
  --or                Combine tests with OR, not AND
  --password      -p  Password to use when connecting
  --port          -P  Port number to use for connection
  --[no]quote         Quotes MySQL identifier names with MySQL's standard
                      backtick character (default yes)
  --set-vars          Set these MySQL variables (default wait_timeout=10000)
  --socket        -S  Socket file to use for connection
  --user          -u  User for login if not current user
  --version           Show version and exit

F:\maatkit-4047\bin>

Nice, no errors! Since Maatkit isn't installed.

Lets try an example:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin> f:\perl\bin\perl mk-find -hmysqlserver -u user --ask-pass --print
Enter password:
`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`

Another example:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin>f:\perl\bin\perl mk-find -hmysqlserver \ 
-u user --ask-pass --ctime +1 --engine MyISAM
Enter password:

`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`

F:\maatkit-4047\bin>

Yet another example:

#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin> f:\perl\bin\perl mk-find -hmysqlserver \ 
-u user --ask-pass --printf "%T\t%D.%N\n"
Enter password:

1024    `mysql`.`columns_priv`
4972    `mysql`.`db`
1024    `mysql`.`func`
23988   `mysql`.`help_category`
90417   `mysql`.`help_keyword`
19909   `mysql`.`help_relation`
281940  `mysql`.`help_topic`
1024    `mysql`.`host`
4628    `mysql`.`proc`
1024    `mysql`.`procs_priv`
1024    `mysql`.`tables_priv`
1024    `mysql`.`time_zone`
1024    `mysql`.`time_zone_leap_second`
1024    `mysql`.`time_zone_name`
1024    `mysql`.`time_zone_transition`
1024    `mysql`.`time_zone_transition_type`
2472    `mysql`.`user`

I can also report that mk-query-digest also worked without an error. I can't show you the output to demonstrate, I just analyzed using mk-query-digest using default settings to process the slow log of a server. I'm sure most options will work, with the exception of daemon mode since this is a windows workstation.

It's incredible the flexibility Open Source tools gives a person. Portable Strawberry Perl and now Portable Maatkit will be in my toolbox. Much thanks to the Strawberry Perl and Maatkit teams!

I recently purchased a new Dell 1525 laptop running Ubuntu 8.04. I've been in need of a new laptop for about a year, and decided to take the plunge and see how good a Linux based laptop from Dell really is. My needs are fairly modest. I'm not a gamer, and don't watch many videos. Mainly it's used for blogging, internet, OpenOffice, IRC/IM, listening to podcasts and programming. Portablity is only somewhat important, mostly it'll be used 80% on a desk with the remaining time mobile.

Model: Dell Inspiron 1525N
Price as reviewed: $749
CPU: Intel Core 2 Duo T7250 (2 Ghz/800 Mhz FSB/2MB Cache)
OS: Ubuntu 8.04 with DVD playback
Screen: Glossy 15.4" widescreen (1280x800)
Video: Intel Graphics Media Accelerator X3100
Memory: 3GB Shared Dual Channel DDR2 at 667Mhz
Hard Drive: 120GB SATA (5400 RPM)
Combo Drive: CD/DVD Writer (DVD+/-RW)
Wireless: Intel 3945 a/g Mini-card
Camera: N/A
Battery: 6 cell Li/Io
Sound: High Definition Audio 2.0
Network: Integrated 10/100
Weight: ~6 pounds

Initial Impressions

I ordered this laptop on a Tues and according to Dell, it was going to take 7-10 working days then 3-5 days shipping before I'd see my new laptop here in Southern California. Much to my surprise, it shipped two days later, and arrived at my home two more days after that! I'd say Dell is very conservative in estimating dates. For once, it was great to see a laptop that didn't have all kinds of extra packaging. Dell packaged the laptop with a very minimum amount of packaging material. My box arrived via DHL intact. The contents of the box were very minimal as well, only including the power supply and Ubuntu DVD. A quick start guide and manual was also included, but not really referenced. It's a laptop after all, there's not much to put together! The included manual was for Windows OS only, there were no directions on using Ubuntu.

Upon power up, it was cool to see the Ubuntu splash screen. It went through some hardware configuration, found my wireless network and I was online in under 10 minutes from opening the box! A very flawless start! Ubuntu is installed in mostly a default configuration, and after a few minutes of being online, it had almost 700MB of updates for me to download. I transferred my data from my aging desktop system on a USB thumbdrive to the Dell. I was 100% working off the Dell in about 30 mins from the time I opened the box. The 700MB of updates would wait until I went to bed before downloading and updating.

Performance

Performance is very subjective, but for my needs, the 1525N has been great. Firefox 3.0.1 performance has been very acceptable. OpenOffice also has very acceptable load times too. I downloaded the latest branch of Drizzle and compiled it in about 8 mins. Under normal use, the CPU fan isn't running very fast, but during the compile, it was running at fullspeed. Graphics has been very good for my needs. Watching the occasional video, it was crisp with good colors. The Intel GMA runs at 500Mhz and uses up to 384MB of shared RAM. Using that amount of system RAM, it's best to install as much RAM as you can afford. I rank installing the most RAM you can afford as the #1 upgrade you should do to this laptop. Upgrading to a faster CPU or harddisk may not be a great return on your investment. YYMV of course.

I tested out the DVD writer by creating a restore disk using the Dell/Ubuntu Rescue DVD. I used standard Memorex DVD-R disks, and it burned about 1.8GB of data in around 3 minutes. I made two DVDs, and when trying to burn the second disk, it took a few ejects before the system would recognize a blank DVD was inserted.

Wireless has been flawless. My WAP is a Buffalo WHR-G54S flashed with dd-wrt. The laptop hasn't dropped the link at anytime, unlike my work HP/Compaq 6710b which drops WIFI every few mins.

Sound is a tad weak, it's not very loud. The speakers are upward facing, and Ubuntu responds to the hardware volume controls just fine. Sound quality is what you'd expect from built-in speakers, kinda rough. Plugging in headphones is the high quality option.

The touch pad is just OK. The default settings are ,very sensitive, causing text selection to occur when that's not the intended action. Double clicking by double tapping doesn't work reliably either. This could be due to Ubuntu settings, something I haven't investigated yet.

Keyboard has a nice tactile feel to it. I use the Dvorak layout, so I might see about rearranging the keys from QWERTY layout. By default, the backspace and delete keys are not configured to autorepeat.

Battery life is about what I expected. I'm seeing around 2 hours and 45 mins runtime with the 6 cell battery. I haven't made any configuration changes to Ubuntu for power saving modes, this run time is based on the stock Ubuntu configuration and "general" useage.

Not Tested

• Modem
• 8-in-1 memory card reader
• HDMI port
• S-video output
• Express card slot
  

Conclusion

For less than $750, this is a fantastic laptop. I'm very happy with it. My other choice was an IBM Thinkpad T61, but I'm glad I went with the Dell. I've only had the laptop about 5 days now, but it's performed flawlessly for my needs. I think the Dell/Ubuntu laptops are a great combination. Only a few small negatives, but they are not a major concern for this laptop. I think Dell should include some kind of Ubuntu quick start guide instead of the manual that ships. If you're already experienced with Ubuntu, this won't be such a big issue, it's more of a concern for users that are new to Ubuntu.