PlanetMysql.ru — информация о СУБД MySQL

The confusion about whether MySQL Community Edition still included InnoDB seems now to have dissipated. Thank you to Sheeri Cabral, Darren Cassar and all of you who helped correct the misperception created by catchy headlines. In part, the confusion was caused by people assuming that the MySQL Classic Edition (an option for embedding that has been around for a long time) is the same as MySQL Community Edition...not so. We have made appropriate changes to our web page to clarify this.

 

A number of blog postings and articles have referred to Oracle raising and doubling prices for MySQL. That is not accurate. Let me give you the facts.

 

MySQL continues to have a dual license model. The Community Edition of MySQL continues to be freely available and licensed under the GPL (including the InnoDB storage engine).  For the commercial editions, we have made a few changes to simplify licensing and have added more benefits for users.

• Pricing model (for end-users) continues to be "Annual Subscription" (includes Software, Support, Updates, and Maintenance).
• Pricing metric continues to be "Per Server"

Commercial Editions for end-users now include a choice of two subscriptions:

• MySQL Standard Edition subscribers now receive Oracle's Premier support, as well as MySQL Workbench Standard Edition
• MySQL Enterprise Edition subscribers get, in addition to the above, access to MySQL Enterprise Monitor and MySQL Enterprise Backup
• Both subscriptions include 24x7, Global, Premier Support with unlimited support incidents.

 

So, what's different?

 

1. 24x7, unlimited incidents, global support for all customers

In the past, MySQL had tiered support model with 4 support tiers ranging from getting only 2 incidents/yr. to only business hour support to 24x7 support. With Oracle's new model, all MySQL customers now get the highest quality of 24x7, global, Premier Support with unlimited incidents.

2. Simple Licensing, More Features

In the past, MySQL subscription packages included Basic, Silver, Gold & Platinum (all at different levels of tiered support, not all features were included...starting at $599, going up to $5000). New subscription packages include Standard Edition and Enterprise Edition (both with 24x7 support, unlimited calls, and new features included; starting at $2000 going up to $5000).

 

We dropped the $599 per server price point (called Basic Support). It included just access to patches and updates and ability to call only within business hours and get resolution for only two incidents per year - very very limited support. This was not a popular option anyway. Now the starting point is $2000 per server which comes with unlimited, 24x7 support, and also includes the Workbench tool - so, much higher level of support and more functionality.

3. In conclusion, we now offer more functionality and 24x7, unlimited incidents, premier support at lower price. In every category now on the price list, we are offering better support and more features than in the past. (To compare, whereas in the past 24x7 support was available starting at $3000 per server, you can now get it for $2000 per server).

NOTES:

·         MySQL Cluster Carrier Grade Edition is a separate product (a real-time database), and for the first time ever available under a subscription model, including 24x7, Premier Support, on a per server basis

·         OEMs/ISVs can also embed MySQL Database and MySQL Cluster http://mysql.com/oem/

 

Hope this clarifies our licensing policy for MySQL. 

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL 5.5 is set to ship GA soon so I thought I would create a series of blogs that highlight the key new features  I will start with a rundown of where we have made improvements and then expand into details as things progress.  MySQL 5.5 merges the technical architectures of the MySQL database with the InnoDB storage engine so that the two should now be considered one and will move in tandem for the most part from now on (We can do this now that Oracle owns both...)  That said, the key things to know about MySQL 5.5 and the re-architected InnoDB storage engine are:

• Oracle's InnoDB is the default database storage engine
• Performance and Scalability on Windows is on par or better than for other platforms
  
• MySQL now fully utilizes the computing power of modern multi-core architectures across all platforms
• Replication is better and easier to monitor, manage and use
• InnoDB provides improved features around storage, manageability and efficiency
• Developers will like the new SIGNAL/RESIGNAL syntax and the new partitioning options
• The new Performance Schema is good and will only get better

So, to get the ball rolling, let's delve into the first couple of bullet items:

Oracle's InnoDB is the mostly widely used MySQL storage engine and for good reason; it provides ACID compliant transactional capabilities and includes unique architectural elements that assure high performance and scalability.  It also is has built-in crash recovery, referential integrity, high levels of user concurrency and SLA exceeding response times.  Given most applications, even if they are read-intensive by nature, need most of these things, it makes complete sense to make InnoDB the default storage engine so developers can get to work building apps without the need to reconfigure things. MyISAM and the other table types are still readily available.

For 5.5 InnoDB has been re-architected so that it can be optimized to take full advantage of modern hardware and operating system resources and efficiencies.  For brevity, I will pick off the key enhancements and refer to the MySQL docs for a complete rundown of all of the new improvements.  Here goes:

Improved Performance and Scalability on Windows - MySQL has traditionally performed well on UNIX based platforms but not so much on Windows.  With more developers now building and deploying applications on Windows, MySQL's footprint has expanded on Windows from the development desktop to the production datacenter.  In fact, Windows is now the most commonly downloaded MySQL platform.  MySQL 5.5 includes Windows specific improvements that ramp up performance and scalability for systems and applications designed to service high concurrency and user loads.  The key MySQL 5.5. improvements for Windows include:

• MySQL now uses native Windows synchronization primitives to implement mutexes and locking algorithms.
• MySQL now uses native Windows atomic operations vs POSIX threads to implement and free read/write specific locks.
• MySQL now uses native Windows operating system memory allocators by default.
• MySQL on Windows I/O thread handles maximum now equals the maximum on other platforms.
• Legacy optimizations made on other platforms have now been ported to MySQL on Windows.
• Many lingering Windows specific bugs have been cleaned up.
  

This is a biggie as MySQL on Windows is a viable alternative to the much more costly SQL Server for Windows based applications and web sites..

Improved Default Thread Concurrency - InnoDB now defaults to allow an unlimited number of concurrently executing threads, leveraging the processing power of multi-threaded and multi-core systems.  Users can override this default behavior by setting the value of innodb_thread_concurrency to the limit that best works for specific implementations.  This one actually got applause when presented at MySQL Sunday during Oracle Open World.

Control of Using Operating System Memory Allocators - Users can now control whether InnoDB uses it own memory allocator or leverages the more efficient allocators available in the current versions of the most commonly deployed operating systems.  This is easily controlled by setting the value of the new system configuration parameter innodb_use_sys_malloc in the MySQL 5.5 option file (my.cnf or my.ini).   The default setting is 1, which instructs InnoDB to use the operating system resource.

Improved scalability via Faster Locking algorithm - For most platforms (UNIX, Linux, Windows), InnoDB now uses native atomic operations vs POSIX threads to implement mutexes and read/write locks.   This boosts InnoDB performance and scale, specifically on multi-core systems.

Improved Recovery Performance - InnoDB is known for its ability to reliably recover data after a crash.  In previous versions the recovery time needed to scan and apply the redo log prior to the next startup could be exceedingly long depending on the amount of data and time between server restarts.  MySQL 5.5 includes a number of default optimizations designed to speed up the scanning and applying of redo logs so the next restart is faster.  Users who had previously sized redo logs artificially low because of slow recovery times can now increase the log file size without concern.  We actually had a customer report 10x improvement in recovery time, but mileage may vary.

Multiple Buffer Pool Instances - Today's buffer pools are consistently sized in the multi-gigabyte range, data pages are persisted and are constantly being read and updated by different database threads.  MySQL 5.5 removes the bottleneck of waiting threads when one thread is updating the buffer pool.  All the structures normally associated with the buffer pool can now be multiplied, such as its protecting mutex, the last row used (LRU) information, and the flush list.  Users can now control and tune how many buffer pool instances are used; however, for backward compatibility the default is still 1.  This feature works best with combined buffer pool sizes of several gigabytes, where each buffer pool instance can be a gigabyte or more.

 Multiple Rollback Segments - InnoDB's single rollback segment has been divided into multiple parts improving performance and scalability and greatly increasing the number of concurrent transactions that can be serviced.   While previous InnoDB versions had a limit of 1023 concurrent transacations, MySQL 5.5 now allows for up to 128K concurrent transactions that create undo data (from insert, update, and delete operations) without locking/blocking contention.  This feature is backward compatible with previous InnoDB versions and file formats but requires a new system tablespace to be created and that data be imported into it to leverage the new transaction concurrency functionality.

 Native Asynchronous I/O for Linux - MySQL 5.5 enables improved concurrency of I/O requests on Linux systems.  Previous versions of InnoDB have provided "simulated asynchronous I/O" for Linux by internally arranging I/O calls as if they were asynchronous, while behind the scenes the query thread would block competing threads until the I/O request was complete. MySQL 5.5 now provides true native asynchronous I/O support for Linux and Windows based systems.  This feature requires the libaio userspace library to be installed on Linux and comes with a configuration option innodb_use_native_aio that can turned off if the new default setting is not compatible with the host I/O subsystem.

 Improved Log Sys Mutex and Separate Flush List Mutex - In previous InnoDB versions a single mutex protected memory areas related to the undo log and logging operations.  This mutex blocked access to the InnoDB buffer pool, while DDL changes to the data dictionary were being logged.  MySQL 5.5 removes this limitation by splitting the legacy log_sys mutex to create a separate log_flush_order mutex.  The resulting new mutex supports a new default behavior that allows undo log and logging operations to happen without blocking other operations involving the buffer pool.

Similarly, operations involving the buffer pool and the flush list previously were protected by a single mutex, which could cause contention and unnecessary delays. In MySQL 5.5 the flush list has its own mutex, reducing contention with buffer pool operations.  This is the new default behavior and requires no configuration setting to enable.

 Improved Purge Scheduling - The InnoDB purge operation is a type of garbage collection that runs periodically.  In previous versions, the purge was part of the master thread, meaning that it could block other database operations when running.  In MySQL 5.5 this operation can run in its own thread, allowing for more concurrency.  Users can control whether the purge operation is split into its own thread with the innodb_purge_threads configuration option, which can be set to 0 (the default) or 1 (for a single separate purge thread).

 Improved Metadata Locking Within Transactions - In previous MySQL versions when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a data definition language ("DDL") statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.  MySQL 5.5 ensures transaction serialization by not permitting one session to perform a DDL statement on a table that is used in an incomplete transaction in another session.  This is achieved by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.  This metadata locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.  For example, if a table t1 is in use by a transaction, another session that attempts to execute DROP TABLE t1 will block until the transaction ends.

These changes, along with optimizations made to how MySQL internally manages table locking (LOCK_open) improve performance for OLTP applications, specifically those that require frequent DDL activity. 

The MySQL 5.5 and InnoDB docs provide a complete rundown of all the new performance and scalability enhancements and how to enable them.

I will cover the MySQL 5.5 Replication and availability enhancements in my next post.  Thanks for reading! 
PlanetMySQL Voting: Vote UP / Vote DOWN

I think most people will agree that one of the biggest advantages of MySQL Community Server is that it’s free. Being free doesn’t get you a multi-million user community though; MySQL offers a great array of transactional engines, advanced high-availability features, robust I/O performance, and it powers many of the top-500 internet sites. When it [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Lately I’ve had to do some environment load testing so I wrote this quick script. It can be modified as needed but the basic idea is that it spawns $x threads (–threads) and then sends two connections (or however many you want with –per-connection=) per thread to the URL (–url=). You can have it wait a configurable time between connections as well (–wait=).

The url is appended with a 32 character randomized string so that any database/caching on the backend of the site isn’t serving data from a warm cache. You can hunt down the string length for 32 and change it to whatever you want. Feel free to change and use as needed, just keep my info at top.

#!/usr/bin/python
################################################################################
## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE: http://kontrollsoft.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
################################################################################

from __future__ import division
import threading
import sys
import urllib2
import select
import random
import string
import getopt
import time

class threader(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
    def run(self):
        global url
        global per
        global u
        for i in range(per):
            if wait > 0:
                time.sleep(wait)
            str = randstr(32)
            # IMPORTANT: this is where we append the search string to the main URL
            # you might need to change this for your site.
            url = "%s/search/%s"%(u,str)
            print "polling url: %s"%(url)
            urllib2.urlopen(url)

def randstr(length):
    global url
    twoletters = [c+d for c in string.letters for d in string.letters]
    r = random.random
    n = len(twoletters)
    l2 = length//2
    lst = [None] * l2
    for i in xrange(l2):
        lst[i] = twoletters[int(r() * n)]
        if length & 1:
            lst.append(random.choice(string.letters))

    return "".join(lst)

def init_thread():
    backgrounds = []
    for thread in range(threads):
        print "Spawning thread: %s"%(thread)
        background = threader()
        background.start()
        backgrounds.append(background)
    for background in backgrounds:
        background.join()

def print_help():
    print '''loader.py - URL load test script
==================================================
Date: 2010-08-26
Website: http://themattreid.com
Author: Matt Reid
Email: themattreid@gmail.com
License: new BSD license
==================================================
Use the following flags to change default behavior

   Option                 Description
   --url=                 URL to test
   --per-connection=      Number of sequential reqests per connection (default 2)
   --threads=             Number of threads for url connections (default 50)
   --wait=                Time to wait in-between requests
   --help                 Print this message

   -u                     Same as --url
   -p                     Same as --per-connection
   -t                     Same as --threads
   -w                     Same as --wait
   -h                     Same as --help
   '''

def main():
    init_thread()
    sys.exit(0)

if __name__ == "__main__":
    global threads #num threads/connections to open
    global u #url to hit
    global per #per connection url hits
    try:
        options, remainder = getopt.getopt(
            sys.argv[1:], 'ptuw', ['per-connection=',
                                   'threads=',
                                   'url=',
                                   'wait=',
                                   'help'])
    except getopt.GetoptError, err:
        print str(err)
        sys.exit(2)

    for opt, arg in options:
        if opt in ('--per-connection'):
            per = int(arg)
        elif opt in ('--threads'):
            threads = int(arg)
        elif opt in ('--url'):
            u = arg
        elif opt in ('--wait'):
            wait = int(arg)
        elif opt in ('--help'):
            print_help()
            sys.exit(2)

    try:
        threads
    except NameError:
        print "No thread quantity specified."
        print_help()
        sys.exit(2)
    try:
        per
    except NameError:
        per = 2
    try:
        u
    except NameError:
        print "No URL Specified"
        print_help()
        sys.exit(2)
    try:
        wait
    except NameError:
        wait=0

    main()

PlanetMySQL Voting: Vote UP / Vote DOWN

Lately I’ve had to do some environment load testing so I wrote this quick script. It can be modified as needed but the basic idea is that it spawns $x threads (–threads) and then sends two connections (or however many you want with –per-connection=) per thread to the URL (–url=). You can have it wait a configurable time between connections as well (–wait=).

The url is appended with a 32 character randomized string so that any database/caching on the backend of the site isn’t serving data from a warm cache. You can hunt down the string length for 32 and change it to whatever you want. Feel free to change and use as needed, just keep my info at top.

#!/usr/bin/python
################################################################################
## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE: http://kontrollsoft.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
################################################################################

from __future__ import division
import threading
import sys
import urllib2
import select
import random
import string
import getopt
import time

class threader(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
    def run(self):
        global url
        global per
        global u
        for i in range(per):
            if wait > 0:
                time.sleep(wait)
            str = randstr(32)
            # IMPORTANT: this is where we append the search string to the main URL
            # you might need to change this for your site.
            url = "%s/search/%s"%(u,str)
            print "polling url: %s"%(url)
            urllib2.urlopen(url)

def randstr(length):
    global url
    twoletters = [c+d for c in string.letters for d in string.letters]
    r = random.random
    n = len(twoletters)
    l2 = length//2
    lst = [None] * l2
    for i in xrange(l2):
        lst[i] = twoletters[int(r() * n)]
        if length & 1:
            lst.append(random.choice(string.letters))

    return "".join(lst)

def init_thread():
    backgrounds = []
    for thread in range(threads):
        print "Spawning thread: %s"%(thread)
        background = threader()
        background.start()
        backgrounds.append(background)
    for background in backgrounds:
        background.join()

def print_help():
    print '''loader.py - URL load test script
==================================================
Date: 2010-08-26
Website: http://themattreid.com
Author: Matt Reid
Email: themattreid@gmail.com
License: new BSD license
==================================================
Use the following flags to change default behavior

   Option                 Description
   --url=                 URL to test
   --per-connection=      Number of sequential reqests per connection (default 2)
   --threads=             Number of threads for url connections (default 50)
   --wait=                Time to wait in-between requests
   --help                 Print this message

   -u                     Same as --url
   -p                     Same as --per-connection
   -t                     Same as --threads
   -w                     Same as --wait
   -h                     Same as --help
   '''

def main():
    init_thread()
    sys.exit(0)

if __name__ == "__main__":
    global threads #num threads/connections to open
    global u #url to hit
    global per #per connection url hits
    try:
        options, remainder = getopt.getopt(
            sys.argv[1:], 'ptuw', ['per-connection=',
                                   'threads=',
                                   'url=',
                                   'wait=',
                                   'help'])
    except getopt.GetoptError, err:
        print str(err)
        sys.exit(2)

    for opt, arg in options:
        if opt in ('--per-connection'):
            per = int(arg)
        elif opt in ('--threads'):
            threads = int(arg)
        elif opt in ('--url'):
            u = arg
        elif opt in ('--wait'):
            wait = int(arg)
        elif opt in ('--help'):
            print_help()
            sys.exit(2)

    try:
        threads
    except NameError:
        print "No thread quantity specified."
        print_help()
        sys.exit(2)
    try:
        per
    except NameError:
        per = 2
    try:
        u
    except NameError:
        print "No URL Specified"
        print_help()
        sys.exit(2)
    try:
        wait
    except NameError:
        wait=0

    main()

PlanetMySQL Voting: Vote UP / Vote DOWN

The recent announcement of the MySQL 5.5 Release Candidate included some pretty staggering increases in performance and scalability. 

Replication is also an area where many enhancements have been made including semi-synchronous replication, replication heartbeating, fsync tuning, relay log recovery, per-server replication filtering, etc.

On Tuesday 12th October, Dr. Lars Thalmann who leads the engineering team responsible for the development and implementation of these enhancements will be discussing what's new in MySQL 5.5 Replication.  He'll also give a sneak-peak into what is cooking in the engineering labs....

You can register for the event here

Even if you can't make the live event, it is still worth registering as you'll be automatically notified when the on-demand replay of the webinar is posted, allowing you ti tune in at your leisure.

For those who are new to replication, there is an incredibly useful post by Andrew Morgan, product manager of Replication and MySQL Cluster, which takes you through a simple step-by-step guide to implementing replication in less than 5 minutes.

You can take the release candidate for MySQL 5.5 for test spin now by downloading the packages here

And hopefully you can tune into the live webinar next week, or the replay when it is available.

PlanetMySQL Voting: Vote UP / Vote DOWN

There’s a new version of the dbbenchmark tool available. Now we’re creating the MySQL connection pool thread count based on automatic reporting of core quantity. There is still the same method to set the thread count manually if you are interested in finding your system’s thread limits. Download the MySQL benchmarking script now and add your server performance to the community database of results!

PlanetMySQL Voting: Vote UP / Vote DOWN

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select
(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb,
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb,
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as innodb_perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='myisam') as myisam_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='myisam') as myisam_perc_data,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='innodb') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_innodb,
(select (select sum(DATA_LENGTH) from tables where ENGINE='innodb') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_innodb,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='myisam') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_myisam,
(select (select sum(DATA_LENGTH) from tables where ENGINE='myisam') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_myisam

from tables limit 1\G

This will output something along the lines of:

          total_size_gb: 2.7505537783727
         total_index_gb: 0.018660545349121
          total_data_gb: 2.7318932330236
             perc_index: 0.6784
              perc_data: 99.3216
      innodb_perc_index: 0.6502
       innodb_perc_data: 99.3498
      myisam_perc_index: 17.5120
       myisam_perc_data: 82.4880
perc_total_index_innodb: 95.6713
 perc_total_data_innodb: 99.8607
perc_total_index_myisam: 4.3287
 perc_total_data_myisam: 0.1393

PlanetMySQL Voting: Vote UP / Vote DOWN

With the MySQL Sunday event at Oracle Open World rapidly approaching, and registrations to the event 10x higher than originally forecast, I thought it would be a good time to highlight sessions that are specifically addressing MySQL high availability, including MySQL Cluster .

You can see details and logistics of all of the sessions here

MySQL Cluster
Andrew Morgan and I will be presenting the latest enhancements in the world of MySQL Cluster and cluster management at our session at 15.30 in Golden Gate C3 of the Marriott Hotel.

If you are staying around for Oracle Open World itself, then drop by our stand at Moscone West W-071 where we'll give you a live demonstration of MySQL Cluster maintaining continuous operation as we upgrade, reconfigure and scale the cluster, all on-line.

MySQL Replication
Before the MySQL Cluster session - at 13.05 (precisely...) in Golden Gate C3, Dr Lars Thalmann, Development Manager, MySQL Replication & Backup - will be presenting the latest replication enhancements  delivered with the MySQL 5.5 release, specifically covering:
- replication heartbeating
- semi-synchronous replication
- fsync tuning
- relay log corruption recovery

There is also a very interesting session planned for 14.45 where Harrison Fisk from Facebook will be presenting Advanced MySQL Replication Techniques

Great, But I'm not Going....
Of course, if you are not able to attend the MySQL Sunday event, we will be presenting many of these subjects in forthcoming live webinars, starting with Lars' session on MySQL replication enhancements, scheduled for October 12th at 0900 pacific time / 17.00 UK .  Look out for more details on how to register for that session in the next week.

If you want to learn more about MySQL Cluster, you can check-out our complimentary whitepapers:
- MySQL Cluster for web applications
- MySQL Cluster for telecoms applications

If you are attending MySQL Sunday, I hope to meet you there in person.  If not, at one of our forthcoming webinars

PlanetMySQL Voting: Vote UP / Vote DOWN

aka “How to use multiple MySQL Servers and Workbench in Snow Leopard without using Terminal… and live happily ever after”

The MySQL Community is a world of command-line aficionados. Many people, including myself, show their love to the simple-but-powerful interface of the mysql command-line client, but not everybody is keen to use a bash shell and give up its GUI, no matter how powerful the software is.

Until recently, GUI tools for MySQL were half baked solutions: in the end, there was always something that you had to do via the command line. Today, you can install, set up and use MySQL on your Mac with Snow Leopard without using Terminal, at all.

My Special Needs

Before digging into the details of the installation, let me describe what I need on my Mac. I use various versions of MySQL and I often need to run 2 or more instances at the same time. I constantly build, install and uninstall versions of MySQL and I need to find a way to quickly remove (or move) one version of MySQL with data, configuration files and all that is related to a specific instance.

Not everybody has complicated environments and multiple instances; for many users, a single installation would be enough. If you are one of these lucky users, you may skip some steps. In any case, everything presented in this article is applicable to 1 or more instances running on a single Mac.

The Ingredients

All the software is available on the MySQL Dev site. You will need 2 kits: MySQL Server and MySQL Workbench. I used the latest .dmg archive from http://dev.mysql.com/downloads and the latest version of Workbench, from here http://dev.mysql.com/downloads/workbench/5.2.html. At the time I am preparing this post, the latest versions are MySQL Server 5.6.0-m4 and MySQL Workbencg 5.2.27.

Preparation

I have my own way of keeping multiple instances on a single mac, and I am sure you can find many others, perhaps they would make even more sense for you. There are some good tools and scripts that may help you (just google a bit or visit confidently Giuseppe Maxia’s blog: http://datacharmer.blogspot.com).

In a nushell, my way of keeping everything separated can be described as:

• Select a parent directory: in my case, I choose /usr/local. For example, the new version of the server will be available on /usr/local/mysql-5.5.6-m4-osx10.6-x86_64. Everything related to this version is in there.
• Select a data directory: I simply use the data sub-directory in the base directory. For an instance on a laptop, used for basic tests, that would be just fine.
• Keep the config files in the base directory: For example, the configuration file, by default, would be /etc/my.cnf. I will use /usr/local/mysql-5.5.6-m4-osx10.6-x86_64/my.cnf instead.
• Select a socket file: in this case, I keep the socket file in the /tmp directory. My suggestion is to use a naming convention, such as mysql-<version>.sock. For example, the socket file for this version would be mysql-5.6.0-m4.sock.
• Identify a specific TCP port for your version: avoid the default port (3306) and start from 3307 ore any other port. In this way, your client tools will not accidentally connect to a server because you forget to specify the TCP port.

Cooking Instructions

Once you have downloaded the software, you are ready to install it. You should start with MySQL Server. When you open the DMG archive, you should see something this:

Just doubleclick on the main package and follow the default instructions, and you are done in literally 1 minute.

By default, the package creates a symbolic link to the base directory. You should see a base dir created under /usr/local. Finder does not allow you to browse /usr/local, but if you go do Go To Folder in the Go menu, you can insert /usr/local in the folder text box and see this window:

The next step is to drag and drop the Workbench icon into your favourite application folder or subfolder.

PlanetMySQL Voting: Vote UP / Vote DOWN