Mysql4all Blog

Ivan Zoratti's blog on MySQL and around

HA Reloaded – Many ways to provide High Availability

with 2 comments

High Availability is one of the hottest topics for MySQL DBAs. As a matter of fact, when we (SkySQL) are called by users and customers, the top two questions for our PS team are:

  1. How can I make my MySQL database scalable?
  2. How can I make my MySQL database highly available

Since MySQL is the most used online database, these two questions are totally appropriate. Although the two concepts walk together, they should not be confused.

We refer to scalability when we define that a system must sustain a specific workload, measured in different ways – response time, latency, transactions or operations per second to name few, or a combination of them. So, we define the level of scalability we want to achieve and we design a system that can scale to that level.

We refer to high availability when we define that a system must be available, within the boundaries of the scalability mentioned above, also in case of malfunctions of one or more of its components. The latter, as many of you know, is called fault tolerance.

Developers and administrators tend to confuse scalability and availability and, even worse, approach the availability and the scalability of their systems all in once. This is wrong for the simple reason that the techniques to adopt to achieve high availability are different to the once to achieve scalability. What is true though, is that these techniques must be ultimately work together and a good system design is a constant reiteration of these techniques. up to the fine tuning of all the aspects that are linked together.

At SkySQL, one of the main goals is to design with our customers scalable and highly available systems. I have seen many presentations on the HA subject and I think most of them are really good – see the one that Henrik has prepared, for example, highly recommended – but I wanted to give you our perspective and show you the various options that we have investigated at SkySQL and that we evaluate for community users and for our customers. This is the reason why I have created this presentation, HA Reloaded.

HA Reloaded is a refresh of all the latest options available for MySQL in terms of HA solutions. I have presented a version of it in Seattle, in San Francisco and in Boston in December, next week I will land in New York City. People who know me, also know that I never do the same presentation twice, so if you are around NYC on Mon 23rd and you want to join us, you will find something completely new, even if you have already attended to my preso.

Here are all the details:
  • The slides of my presentation so far are on slideshare, available here.
  • The Meetup in NYC will start on Monday 23rd at 7pm – the venue is:
    General Assembly NY
    902 Broadway, 4th Floor
    New York, NY 10010
  • The URL to the MySQL Meetup in NYC is here: http://www.databasemonth.com/database/high-availability

 

For those of you who are attending the User Conference – no, I have not submitted this speech. As I said, there are already so many HA presos, I hope Henrik Ingo will do a refresh of his presentation and I am sure that our friends at Percona will talk a lot about their new solutions. But enough of this, please come and poke me at the meetup!

/iz

Written by Ivan Zoratti

18 January 2012 at 5:35 am

Posted in MySQL

Tagged with ,

The SkySQL Reference Architecture at the MySQL UC in Santa Clara

leave a comment »

Only few more hours, it is almost here. The long awaited Reference Architecture from SkySQL is going to make its first appearance at the MySQL User Conference in Santa Clara this week.

SkySQL is going to contribute to the evolution and innovation of MySQL from both coasts of the United States. A total of 10 between presentations, tutorials and events will help the MySQL users in Santa Clara and in Orlando in understanding how they can leverage the use of MySQL, improve performance, scalability and high availability. You can find the complete schedule of the MySQL UC and Collaborate 11 here (UC) and here (C11). Regarding the SkySQL Reference Architecture, information regarding the events and the booth are here.

I hope you will stop by on Wednesday at the panel (11.55am – Ballroom H) or at the launch party (6pm – Magnolia Ballroom), I will be glad to give you more details and all the information you may need.

Written by Ivan Zoratti

11 April 2011 at 3:17 am

Posted in Events, MySQL

MySQL User Conference: InnoDB vs NDB – Let the fight begin!

leave a comment »

While wearing my hat of Sales Engineer, I have been asked several times what is the difference between InnoDB and MySQL Cluster/NDB and when it makes sense to use one storage engine or the other. Some may probably think that this is a trivial question: the two engines are so different that there is really no point to compare them. The reality is not so clear though: there are many situations where I have found InnoDB stretched to the point where MySQL Cluster would have been a perfect fit, and other occasions were users implemented a solution based on MySQL Cluster and InnoDB would have been the perfect choice.

This is the reason behind my talk at the MySQL User Conference in Santa Clara: InnoDB vs NDB. I will co-present the session with my good old friend and ex-colleague Johan Andersson – if you know Johan, you may guess who will take the part for NDB! :)

Rest assured it will be a serious and fair comparison between the two engines, but we will do our best to interact with the audience and to make the session enjoyable.

After the session the slides will be available from the O’Reilly site and I will continue to provide information regarding this topic from the MySQL4All blog and from the SkySQL web site.

If you are so lucky to attend the User Conference in Santa Clara this year, please come and join us. If you have questions that you want to raise prior to the presentation and perhaps you would like to topics cover, feel free to send me a message and I will do my best to add the topic to the presentation.

Enjoy the match!

 

Written by Ivan Zoratti

9 April 2011 at 2:59 am

Posted in Events, MySQL

Tagged with

Yet Again On Subqueries

with 10 comments

…with a bit of strategic thinking

They come back, every now and then. Subqueries are far from being perfect at MySQL and they can give you some serious headaches.

Skilled MySQL developers know it better. They avoid subqueries as much as they can. It is not that subqueries do not work, it is just that the optimizer sometimes is, well, “not that optimised”.

So you may stay away from subqueries with some good SQL review. But what happens when the subquery is automatically generated by a script or a tool? If you can change the statement, I’m afraid you need to find some serious workarounds that vary case by case.

Here is an example that I found few weeks ago when I visited one of our customers.

Our customer used Magento for its site. Magento used a couple of queries that I will report here as sales and sales_items, although they are not their real name:

CREATE TABLE `sales` (
   `sales_id`     int(11) NOT NULL,
   `sales_entity` int(11) NOT NULL,
   `sales_status` int(11) NOT NULL,
   PRIMARY KEY (`sales_id`),
   KEY `ix2` (`sales_entity`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE `sales_items` (
   `sales_item_id` int(11) NOT NULL,
   `product_id`    int(11) NOT NULL,
   `quantity`      int(11) NOT NULL,
   PRIMARY KEY (`sales_item_id`),
   KEY `ix2` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

Both queries had tens of thousands of rows, but in our example I will use only few rows:

mysql> select * from sales;
+----------+--------------+--------------+
| sales_id | sales_entity | sales_status |
+----------+--------------+--------------+
|        1 |            1 |            1 |
|        2 |            1 |            2 |
|        3 |            2 |            1 |
|        4 |            2 |            2 |
|        5 |            3 |            1 |
|        6 |            3 |            2 |
|        7 |            4 |            1 |
|        8 |            4 |            2 |
|        9 |            5 |            1 |
|       10 |            5 |            2 |
+----------+--------------+--------------+
10 rows in set (0.00 sec)
 
mysql> select * from sales_items;
+---------------+------------+----------+
| sales_item_id | product_id | quantity |
+---------------+------------+----------+
|             1 |          1 |        1 |
|             2 |          1 |        1 |
|             3 |          2 |        1 |
|             4 |          2 |        1 |
|             5 |          3 |        1 |
|             6 |          3 |        1 |
+---------------+------------+----------+
6 rows in set (0.00 sec)
 

The application was running very slow. When we analysed the query log using SkySQL Enterprise Monitor, we found an UPDATE like this:

UPDATE sales SET sales_status = ?
 WHERE sales_entity IN (
       SELECT DISTINCT sales_item_id
         FROM sales_items
        WHERE product_id IN (?)
 )

The update was executed several times in a minute and it took several seconds to be executed. We already knew that the issue was in the subquery, but just to explain the situation to the customer we wanted to show an execution plan.

As you may know, you cannot use EXPLAIN with and UPDATE statement, therefore we use a SELECT that acts like the UPDATE. This SELECT did the trick:

SELECT * FROM sales
 WHERE sales_entity IN (
       SELECT DISTINCT sales_item_id
         FROM sales_items
        WHERE product_id IN (3)
);
 
+----------+--------------+--------------+
| sales_id | sales_entity | sales_status |
+----------+--------------+--------------+
|        9 |            5 |            1 |
|       10 |            5 |            2 |
+----------+--------------+--------------+
2 rows in set (0.00 sec)
 

The execution plan of the query is:

EXPLAIN SELECT * FROM sales
         WHERE sales_entity IN (
               SELECT DISTINCT sales_item_id
                 FROM sales_items
                WHERE product_id IN (3)
        );
 
+----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table       | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | sales       | ALL             | NULL          | NULL    | NULL    | NULL |   10 | Using where |
|  2 | DEPENDENT SUBQUERY | sales_items | unique_subquery | PRIMARY,ix2   | PRIMARY | 4       | func |    1 | Using where |
+----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)
 

As you can see, the query uses the index in the subquery to retrieve the rows from sales_items, but then it does not use the index on sales_entity in sales. The result is that the UPDATE takes several seconds to be executed, because mysqld performs a full scan of the sales table.

If you want to check the details of this issue, you can have a look at the infamous bug 18826 -  http://bugs.mysql.com/bug.php?id=18826

So what should you do to fix the problem? If you cannot change the statement, I am afraid there is not much you can do. We were luck and the customer could change the statement. By changing the select with a JOIN that replaces the subquery, something like:

SELECT sales.* FROM sales JOIN sales_items
    ON  (sales.sales_entity = sales_items.sales_item_id )
 WHERE sales_items.product_id in (3);
 
+----------+--------------+--------------+
| sales_id | sales_entity | sales_status |
+----------+--------------+--------------+
|        9 |            5 |            1 |
|       10 |            5 |            2 |
+----------+--------------+--------------+
2 rows in set (0.00 sec) 

 

The SELECT gives the same result of the previous SELECT and the EXPLAIN command shows:

EXPLAIN SELECT sales.* FROM sales JOIN sales_items
            ON  (sales.sales_entity = sales_items.sales_item_id )
         WHERE sales_items.product_id in (3);
 
+----+-------------+-------------+------+---------------+------+---------+--------------------------------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref                            | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | sales_items | ref  | PRIMARY,ix2   | ix2  | 4       | const                          |    2 | Using index |
|  1 | SIMPLE      | sales       | ref  | ix2           | ix2  | 4       | test.sales_items.sales_item_id |    1 |             |
+----+-------------+-------------+------+---------------+------+---------+--------------------------------+------+-------------+
2 rows in set (0.00 sec)
 

So ix2 can be used to filter the rows in the SELECT, and ultimately in the UPDATE.

In fact, the new UPDATE is:

UPDATE sales JOIN sales_items
    ON ( sales.sales_entity = sales_items.sales_item_id )
   SET sales_status = ?
 WHERE sales_items.product_id IN ( ? )
 

The server tales few milliseconds to execute the query, fixing also the headache of our developers better than a pain killer.

Some people may argue that the use of subqueries is not efficient and developers should learn some good SQL. Unfortunately, this is not the point. Products like Magento and others – I have primarily in mind BI and reporting tools – are supposed to be database agnostic and generate queries for many RDBMSs with minor customisation. Bugs like this give the end users the impression that MySQL is terribly slow and is simply not good enough for the job, so they should refer to expensive commercial RDBMSs – the ones that have subqueries working properly.

I really, really hope that the optimiser team at Monty Program one day will fix this bug. We will see even more adoption of MySQL in the Enterprise world, something that has been always underestimated. More projects in the “Enterprise world” mean more resources injected into the MySQL ecosystem, resources (funds, skilled developers and DBAs, expertise) that may be used to improve the product and the solutions around it.

Written by Ivan Zoratti

25 January 2011 at 9:39 pm

Oracle 11g and MySQL Enterprise: get the best of both worlds

with 9 comments

In the last 9 months, every time I visited a customer, or I participated to a meetup group or I simply had a chat with a non-MySQL colleague, I heard the very same question: “when should I use MySQL and when should I use Oracle?”. The most audacious dared to ask how difficult it is to migrate from one database to another. Some more conservative DBAs and developers asked me if and how it is possible to integrate both database servers in the same environment or solution.

As you can imagine, there are not simple answers to these questions. There are certainly some guidelines that may help in making a decision around the use of MySQL, Oracle, or any other RDBMS.

When should I use Oracle 11g and when should I use MySQL Enterprise?

The ultra-simplistic answer can be summarised in:

  • Go for Oracle 11g with ERP and CRM systems, Data Warehouses and sophisticated applications that require lots of features built into the database.
  • Go for MySQL Enterprise when you want to power Online applications (especially LAMP apps), you need to create “quick&dirty”  Data Marts, or you want to easily embedded or bundle the database in your application.

Does it mean that I should not use MySQL to power a Data Warehouse? No, I do not mean that, but if you are an Oracle DBA, you may be disappointed in not finding a Parallel Query Option (PQO) or bitmap indexes in MySQL. Or maybe you want to partition a table with 10 billion rows and move these partitions from one table to another, remove one partition for some time and then reattach it to a table. If you manage a relatively small DW with few users, all these features may be irrelevant, but if you have some Terabytes on a proper big iron, you will definitely take these elements into consideration.

Conversely, if you need to create a data mart to do some analysis and reporting, then MySQL may be just perfect. You can install a small VM or a standalone server, have MySQL up and running in minutes and then you can start bulk loading data on MyISAM tables. By the time you are loading your data, your colleage would be on dialog window #23 of the Oracle 11g Installer (if you can manage to set up a proper X11 environment to make it running). Oh, and of course you can pack the MyISAM tables, or you can use a columnar storage engine.

And regarding Online application, just the fact that 16 of 20 top web sites run primarily with MySQL (http://www.alexa.com/topsites – just remove Windows Live, MSN, Taobao and Amazon.com from the list) should give you confidence that MySQL is a good choice.

Should I migrate from one database server to the other?

It is always possible, but first I strongly recommend to answer to these questions:

  • Does my team have the right skills to use and support the destination database?
    Sometimes, the learning curve of a database server is not taken into a proper consideration. Developers may use the RDBMS in the wrong way and they may end up with issues, with terrible performance and big nightmares. This is particularly true when CIOs and IT managers think that a migration to MySQL would simply cut the licenses costs. Because MySQL is such a “simple” product, there is no need to learn, you just start coding. How wrong is that?
  • Have you considered, in your TCO, the man-time cost of a migration, the review of the infrastructure and the estimated cost of the disruptions caused by a significant change?
    The name of the game here is “underestimation”. IT managers often underestimate these costs, sometimes by an order of magnitude. They should be very generous in considering quite expensive disruptions, but most of all, they should bring in expertise that can confidently mitigate the risk of a migration (and consider the cost of this expertise). Thankfully, although it was a migration to a NoSQL solution, our friends at Twitter realised the potential impact of a change in technology just in time. Others did not realise that on time.
  • A migration may save money but it does not bring any value to the end user. Is it really worth the risk?
    Of course, if it is big money, the answer is yes. A new project is definitely preferable to a migration: new and updated features may win the users’ mind to sponsor the move.

Can I really integrate Oracle 11g and MySQL Enterprise today?

The answer is yes, definitely! But again, you need to understand what you mean for “integration”. I have identified three types of integration:

Application integration

Applications can “easily” interact with both databases. Things may be difficult if you use some frameworks, but in general you can push and pull data on both servers at the same time. Another typical approach is to use message queues or a common cache – Memcache may come handy here.

Systems integration

Systems can co-locate instances without any problem and shared storage among the instances can facilitate the integration. You can consider to exchange data through the file system, perhaps by using the CSV storage engine.

Systems integration is also systems management and monitoring integration. For this, you may be interested in knowing more about the use of Oracle Enterprise Manager (OEM) to monitor MySQL. Oracle is working on that and there will be a release expected in the next calendar year. If you need something quicker, you may have a look at the Pythian plug-in.

Data integration

Data integration is probably the most interesting part. I would consider three aspects here:

  • Data exchange: it is performed using a typical ETL approach. Both Oracle 11g and MySQL Enterprise can extract and load data in compatible formats – CSV or logical dumps. Data exchange should then be pretty straightforward.
  • Data replication: there different solutions to this approach, both unidirectional (only from MySQL to Oracle or from Oracle to MySQL) or bidirectional (between the two databases at the same time, although there is no conflict resolution). Oracle GoldenGate 11g may be a great choice if you need to replicate data from Oracle to MySQL: it is fast, portable and highly scalable. For bidirectional replication, Hit Software provided a software called dbMoto that can do the trick – although you should consider that the replicator runs only on windows at the moment.
  • Real time data access: what about federated data, i.e. accessing data on MySQL from an Oracle database and viceversa? Well, this is possible straight away from Oracle to MySQL by using Oracle Heterogeneous Services (OHS). From MySQL to Oracle you should use User Defined Functions that use an OCI client to access an Oracle database from a SQL statement. I will try to blog more about this approach.

Where can I find more information?

The first step may be to take a look at the presentation that I gave last Monday at the Oracle Open World. The slides are available here.

Sheeri Cabral video recorded the presentation and posted it here.

I will replicate this presentation in Europe on these dates:

I hope to see you there!

Written by Ivan Zoratti

22 September 2010 at 6:32 pm

Heading to Oracle Open World

leave a comment »

Today is all airports and flights. The flight between London and San Francisco is not too bad, but I don’t like long haul flights and for 12 hours I can sympathise with sardines and anchovies…

Tomorrow it will be different. The weather looks good, the sessions very interesting. Just few hours to settle, review the presentation and relax a bit before it begins. Then, Oracle Open World is going to open on Sunday evening at the Moscone Center.

What about MySQL?

Sunday is all MySQL! We will have opening keynotes from Edward Screven and the MySQL team. We will have technical sessions all afternoon and a closing keynote from Marten Mickos in the evening. The program for MySQL Sunday is here.

If you cannot participate in person, I am sure you will find presentations and videos soon after the event.

My presentation

I am going to present in the mainstream set of Oracle Open World, On Monday at 5pm Pacific. The topic is “Getting the best of both worlds with Oracle Database 11g and MySQL Enterprise” – details are here.

In the light of the adoption of MySQL in the Enteprise, it is important to find the best ways to integrate Oracle and MySQL. This integration is possible now, but there are many other options to come in the near future.

Anything else re MySQL at the Oracle Open World?

Of course there are hundreds of other sessions and a MySQL booth. If you can stop there you will find me and other colleagues of the MySQL team.

What’s next?

Well, will see. September and October are hot months for the business. Lots of presentations in Europe and customers meetings will keep me busy. I hope there will be time to meet many people at the MySQL User Groups and in customers events.

Written by Ivan Zoratti

18 September 2010 at 2:49 am

Posted in MySQL, Uncategorized

All-GUI MySQL on Mac

with 7 comments

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 Workbench 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:

MySQL Server Package

MySQL Server Package

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:

Go To Folder

Go To Folder

As you can see, there is a mysql sym link (/usr/local/mysql) and a new mysql-5.6.0-m4-osx10.6-x86_64, which is the base directory that you have just installed.
Local Dir

Local Dir

My recommendation is to remove the mysql link, in order to avoid any confusion with multiple instances. Since the server has been installed with the superuser, you need to retype the superuser password again to remove the link.
Another important point to consider is that the installer has created a base directory owned by the superuser, with Read/Write access. Standard users have ReadOnly access. You should open the Info dialog (right click on the dir and select Get Info), then you should grant Read/Write access to everyone.
MySQL Base Dir Info

MySQL Base Dir Info

Change to RW

Change to RW

Now it’s time to install MySQL Workbench. This is an even easier task, since you just need to open the DMG archive to expose the application:

MySQL Workbench Package

MySQL Workbench Package

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

Easy as 1..2..3, done. Just double click on Workbench and you will see the main window:

MySQL Workbench Home Page
MySQL Workbench Home Page

The next step is to create a new server instance. Click on the New Server Instance item on the right side of the main window and you will see this dialog:

Create New Server Instance
Create New Server Instance

The instance will run on your local Mac, so leave the radio button on localhost selected. By clicking the Continue button, you will see the next page:

Create New Server Instance
Create New Server Instance
As Connection Name, I used the version of the server (mysql-5.6.0.-m4) and I have selected Local Socket as Connection Method. Now you need to specify the socket file and path. I usually leave the file in the /tmp directory, therefore the file and path would be /tmp/mysql-5.6.0-m4.sock.
I do not have any relevant information in my instances, so a root access with simple or no password is enough. I use to store it in the Keychain, in order to avoid the request every time I need to open a new connection.
Store Password for Connection
Store Password for Connection
By clicking Continue on the main dialog window, you will move to a testing page. Here Workbench will try to connect to the server. Since the server is down, you will see a set of errors, they are absolutely fine at this stage.
Testing the DB Connection
Testing the DB Connection

By clicking Continue, you will be able to specify the operating system and the package you are using. These combos are already selected for you as MacOS X and MySQL Package. When you will open the profile to change some parameters, you will notice that the Installation Type will be different: this is absolutely normal, since Workbench identifies the fact that we have overridden some defaults as a custom installation.

Specifying the OS

Specifying the OS

When you click Continue, you will test the host settings and again, you will see some errors, due to the fact that the instance is not running (see image below).

Testing the Host Settings

Testing the Host Settings

Another click on the very same button brings you to the MySQL Config Page. In this page you can specify the version of the server and the path to the configuration file. Personally I am very lazy and with fat fingers, so I prefer to see the software to provide the path for me. I will use the same trick adopted in Finder, i.e. I will type the first or first two directories in the path, then I will click “” to select the rest of the path:

Info About MySQL Conf

Info About MySQL Conf

Specify Path to the Conf File

Specify Path to the Conf File

Here you can select the base directory and from the base directory you can select a file. My recommendation is to select the README file:

Select README

Select README

When you click the Open button, the README file and path will fill the Path text box. Now you can change README with my.cnf:

Change README to my.cnf
Change README to my.cnf

You are ready to click Continue again. The next page shows you the commands used to administer the server instance. You can leave these commands as is at the moment, you will change them in a minute.

Commands to Manage the Server
Commands to Manage the Server

The last click on Continue brings you to the final page, where you can give a name to the profile. Again, I used the version and the location to name the profile.

Create Instance Profile
Create Instance Profile

You can finally create the Profile by clicking the Finish button. The result is a new instance in the Server Administration area.

New Instance Created
New Instance Created
Now you may want to change the way you administer the instance, by selecting more details that were not present in the wizard. You can do so by clicking the Manager Server Instances item.
The output window shows the connection parameters that have been set in the wizard.
Manager Server Instances - Connection

Manager Server Instances - Connection

By clicking System Profile, you will see a new set of parameters. Some of them were not present in the previous wizard, but you can review and alter them now. You may have noticed that the Installation Type is now Custom, because the path of the configuration file has changed. Now what you should do is to update the start and stop commands in this page, since they still refer to the symbolic link that I removed.

Manage Server Instances - System Profile
Manage Server Instances – System Profile
This is probably the trickiest part of the configuration. In general, the start and stop commands require the privilege of superuser (or you must play more with the owneship and the grants of files and directories). I had lots of problems with the sudo commands and in the end I decided to use a trick. Again, this trick is not great, since it exposes my user password on the local machine, but I do not have any problem with that, so I decided to adopt it. Basically, I added the sudo command to the start and stop lines and I unchecked the checkbox underneath. The result is that I do not rely on the use of two commands combined in one action, but I can confidently test and execute a command in one go.
The final start and stop commands are something like this:
Start:
echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqld_safe --defaults-file=my.cnf"
Stop:
echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqladmin -uroot --socket=/tmp/mysql-5.6.0-m4.sock shutdown"
The concept is pretty simple: you type a password and you pass it to a sudo command. The command then executes a shell and passes the command string between quotes to the shell. If this explanation does not make sense to you, don’t worry, just replace mypwd with your machine password. Just remember, if you are using special characters, i.e. non digits or letters, you should use the \ symbol as prefix. Hence, the password “Good Job!” should be “Good\ Job\!”. You may notice that I have also removed the sudo command completely, just to avoid any misunderstanding.
Change Start-Stop

Change Start-Stop

When you click the Close button, you are finally ready to go! Now you can doubleclick the new instance profile and Workbench will try to connect to the instance. Again, MySQL is not running, so you should see a dialog that alerts you and you should click Continue Anyway.

Open a MySQL Instance

Open a MySQL Instance

After few seconds, you will see the main server instance window:

Server Instance Window

Server Instance Window

The MySQL Server is stopped and you need to set few more parameters before you can have it up and running. Click the Configuration Item and start with the General tab. Here you should change the TCP port (as advised, so you will not accidentally connect to any instance by default), the base directory and the data directory. The same trick to select a root directory first is applicable here, since the two directories should be respectively /usr/local/mysql-5.6.0-m4-osx10.6-x86_64 and /usr/local/mysql-5.6.0-m4-osx10.6-x86_64/data.

Instance Configuration - General

Instance Configuration - General

Once you have set the directories, you need to change a network parameter. Click the Networking tab and specify the socket file as we have previously defined (/tmp/mysql-5.6.0-m4.sock).

Instance Configuration - Networking

Instance Configuration - Networking

Now you can click the Apply button and create the my.cnf file for the very first time. The dialog that appears is just a confirmation of the actions to take. You will create the file by clicking Apply again.

Conf File Changes

Conf File Changes

You can finally select the server instance again and click on Start Server. The first time you start the server, allow your Mac to work for 40-50 seconds or even a minute or so. MySQL needs to create the InnoDB files and it will take a while.

Open Instance
Open Instance

Finally, you will be rewarded with this screen:

Instance Running

Instance Running

Congratulations!
Now, you may argue it’s overcomplicated, but again, everything is self contained and you can run as many instances as you like at the same time, without touching the Terminal.

Enjoy!

As last bit, you should check the connection parameters set by the wizard, by selecting the new connection on the left and clicking the Manage Connection option. Here, I would just recommend to give a good name to the connection.
Manage Connection

Manage Connection

Now you can close the window and doubleclick the new connection. The query window will appear:

Open Connection
Open Connection
You can now start using the connection, by executing queries or creating objects with the SQL editor or with the data modeler.
But the beauty of Workbench is material for another post!

Written by Ivan Zoratti

11 September 2010 at 9:48 pm

Follow

Get every new post delivered to your Inbox.