Using MySQL Workbench with XAMPP

Please Note, it’s been pointed out that I need to make it clear that this covers version 5.2, the developer release, of MySQL Workbench.  The principles still apply to 5.1, but it’s a different process that I’ve not documented at all.  Apologies for any confusion.

This is a slight departure from our usual posts as it’s not about WordPress. However, if you’re developing with WordPress on a Windows PC there’s a good chance you’re using XAMPP as your platform.

I always liked the MySQL Administrator and MySQL Query Browser tools, but these have recently been deprecated and will not be updated any more. Instead, MySQL have released the MySQL Workbench, which is an all-in-one style DB tool that includes query browsing, database management and, interestingly, an entity relationship modeller. The latter is useful if you’re designing databases. In WordPress development you don’t do too much of that as it’s designed as a database light application. A shame, because a better DB structure would really extend its power and reduce the frankly vast amount of code that’s invoked. But that’s for another conversation!

Anyway, you’ll get the Workbench application and then find that it’s far harder to get going than the old tools. But don’t be put off, once you work out a few simple steps you’ll be off.

It’s worth noting that this is as new to me as it is to you – there may be better ways to do this, and I’ve made certain assumptions about how you use XAMPP. If you have any feedback, please comment below!

Create a New Server Instance

media_1263421672264.png

Start Workbench and you’ll be presented with a dashboard. Select New Server Instance.

Host machine is LocalHost

media_1263421812966.png

OS is Windows

media_1263421896152.png

You’ll need to check which version of MySQL you have installed – 5 or 5.1

Don’t Worry About the Error

media_1263421950071.png

XAMPP uses a .cnf file, not .ini No idea why, just the way they’ve compiled it, I guess. Remember, we’re not running MySQL from a standard build. Just click next.

Connection Values

media_1263422064481.png

Click and Open Connection Manager

Create a Connection Called localhost

media_1263422141981.png

Assuming you have the default XAMPP for development purposes then you can use the settings exactly as above. If you’ve assigned root a password you’ll need to completely the fields appropriately. Click Test Connection to make sure all is well. I like to use the Local Socket/Pipe, but you can use TCP/IP also.

The next step in the wizard will test the connection. All should be well and if not you’ve made a mistake.

Config File

media_1263422491464.png

This is the first point where you deviate from ‘normal’. If you click check path before setting it correctly, nothing will work. So, you need to know where XAMPP is installed. If it’s in the root of your T: drive as it is for our dev machines, then you need to enter the following path: T:\xampp\mysql\bin\my.cnf Change this according to where you placed XAMPP. If you can’t work this path out, maybe you should try a different vocation?

Click Next

Commands to Manage MySQL Server

media_1263422767403.png

This is where it gets a little more fun. The first two are quite easy to work out, but the third is quite different to the default. Maybe there’s a better command, but it was the best I could think of at the time.

As you can see, the commands are:

T:\xampp\mysql\bin\mysqld --defaults-file=T:\xampp\mysql\bin\my.cnf --standalone --console
T:\xampp\mysql\bin\mysqladmin --user=pma --password= shutdown
mysqladmin --user=pma --password= ping | findstr alive

You may also want to run the commands in the context of administrator in many cases, but I don’t run on my machine as administrator (I’m security conscious) and don’t have the need to run these as admin, so it’s not strictly necessary to tick the box. But for a development machine this isn’t a worry either way.

Name the Instance

media_1263422882590.png

Simples.

Finish off the wizard and you’ll have a new instance. As long as you start MySQL in advance with XAMPP, it will work as expected. Read the note below for more information on why.

A Note About Starting and Stopping

I have to admit that with the current settings I always start MySQL from the XAMPP control panel or shortcuts. If I stop MySQL from the Workbench it can stop it, but it will never notice that it’s stopped. This is less of an issue when you run MySQL as a service, and you can use the default commands given by the wizard, but running Apache and MySQL as a service can be a painful approach with XAMPP so few people bother. If I can find time to work out a slicker approach, or you can tell me of one, then I’ll update this guide.

I hope this guide has been useful. Thanks for reading!

Comments
  • victor 6 / Aug / 2013 at 10:39 am

    Not so far I have found new cool tool to work with mySQL on mac os x – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
    You can install Valentina Studio (FREE) directly from Mac App Store: https://itunes.apple.com/us/app/valentina-studio/id604825918?ls=1&mt=12

  • Pavel 26 / Nov / 2012 at 3:00 pm

    Thanks a lot. It worked out for my XAMPP and separately installed workbench on MacOS just as good as on Windows!

  • Muhammad Iqbal 31 / Aug / 2012 at 7:12 am

    I am developing a web site which includes MySQL and PHP while web server is XAMMP, Question is that how we connect with MySQL on XAMMP and proceed for the database development ( Kindly the most easiest way out)

  • peter 7 / Jun / 2012 at 3:53 pm

    no help in the new version of workbench 5.2.40!
    windows management will nocht let me pick a service…
    it say’s: No MySQL service found
    :o(

  • Mani 9 / May / 2012 at 1:18 am

    Aaah…There is some issue with starting of MySQL server through XAMPP, it is telling the same error…Error starting…

  • Mani 9 / May / 2012 at 1:14 am

    Hey Thanks man ! It was helpful!! I was wondering with the question now, and I found your blog, even useful pretty much now for the new release!

  • Darwin 6 / Feb / 2012 at 6:42 am

    mysql workbench need mysql as services or not?

    mysql workbench clash with mysql front?

    • David Coveney
      David Coveney 6 / Feb / 2012 at 5:07 pm

      I can’t remember for sure, but you may indeed have to try it as a service – I have a memory of this, but I no longer use the tool so can’t really remember.

    • shaan 19 / Feb / 2012 at 10:04 am

      in xampp control panel start start mysql as svc then all thinds will fall in place

  • Denis 22 / Jan / 2012 at 8:16 pm

    Thanks, this was useful. Now I will search how to run scripts using workbench and import data.

  • china 9 / Dec / 2011 at 2:30 am

    Paul
    Thanks for this post, it’s really great.
    Just a quick note:
    I’m using XAMPP lite 1.7.3 on Window XP SP3
    I have to click Svc check box infront of MySql in the XAMPP control to be able to create a new server instance in MySQL WB.

  • Paul 25 / Feb / 2011 at 5:35 am

    Thanks for this post, it’s really great.

    Just a quick note:

    I’m using XAMPP lite 1.7.3 on Window XP SP3

    I have to click Svc check box infront of MySql in the XAMPP control to be able to create a new server instance in MySQL WB.

  • Shehi 18 / Dec / 2010 at 7:10 am

    After you register service with name “MySQL”, following commands work just fine – tested with custom installed MySQL 5.5 under XAMPP:

    Start: sc start MySQL
    Stop: sc stop MySQL
    Status: sc query MySQL | wba_filter(RUNNING)

    @see
    http://bugs.mysql.com/bug.php?id=56919&thanks=3&notify=727

  • Jide Otuyelu 2 / Nov / 2010 at 4:54 am

    Following the gist of the tutorial, I am using 5.2.29.CE, while I can connect and run SQL queries on the server, it detects the server as being offline. Very weird

  • Carlton 8 / Jun / 2010 at 3:48 pm

    Thanks for the main tutorial and your tip Pedro, works fine now although stopping via xampp causes a little problem for the workbench

1 2
css.php