MySQL Administrator on Mac OSX Leopard: A Few Tips and Tricks

I mentioned in my previous article I’d show a couple of MySQL Administrator tricks so here goes. Many programmers like to use Terminal to do all MySQL administration tasks. It’s a great skill to have and one that can be picked up pretty easily and quickly when you get comfortable working with MySQL. I prefer to do everything visually so I can actually see what I’m doing. There’s no right or wrong way but a lot of advanced tasks require work in the Terminal. However GUI tools are perfect for everyday administration tasks.

Getting started in MySQL Administrator

I presume if you followed my previous article you downloaded the MySQL GUI Tools, if not go ahead and download and install them. When you first open MySQL Administrator you’ll get a login screen:

MySQL Admin Login

Where the button says “Skip” above it’ll say “Connect” for you… for some reason while taking screen shots it changed to “Skip.” Weird…

Working on the assumption that this is a fresh install of MySQL, you have not created a password for the “root” user. This is not a problem as you can easily do this after logging in to MySQL Administrator. Let’s not forget to tell MySQL Administrator where mysql.sock is located… expand “More Options” and put /var/mysql/mysql.sock in the box next to “Connect Using Socket.” So now the login screen should look like this:

MySQL Admin Screen

Go ahead and hit “Connect” and you’re in:

MySQL Admin Info

If you take a look at the icon’s up top you can begin to get an idea of what you can do with MySQL Administrator. You can run backups, monitor MySQL connections, view logs, create new databases and users, and a host of other things. Lets start off by assigning a password to “root” user. A little disclaimer… keep in mind that this is MySQL’s superuser of sorts and some people do not assign “root” a password. So with that out of the way, click on “Accounts” and highlight “root.” As you’ll see all you have to do is type a password in the box, then confirm it in the next box, then click “Save Changes:”

Adding a new MySQL user

Since we’re already in the “Accounts” tab, lets go ahead and create a new MySQL user. All you need to do is press the “Add New User” button which is the first button in the bottom left corner:

Add New User

So just type in the Login Information and press “Save Changes:”

Login Info

Creating a new database

So let’s move on and create a new database. Click on “Catalogs” and then the “Add” (aka +) symbol in the bottom left corner:

Add New Database

Name your new Schema (aka database) and press “Ok.” This creates an empty database ready for your next web application! I have a local installation of WordPress on my MacBook Pro so here is what that “Schema” looks like:

So cool! Now we need to go back and assign privileges to the user we created in the previous step so the database can be accessed in an app.

Assigning privileges to a user

So go back to the “Accounts” panel, expand the user you previously created, and click on “Schema Privileges.” Directly underneath the User are a list of “hosts.” Since we’re just working locally, we’ll assign privileges to “localhost.” But feel free to add privileges to the other “hosts” for this user. This is probably the most confusing task you’ll do with MySQL Administrator so pay close attention. Highlight “localhost” and the “schema” you want to add user privileges to:

Schema Privileges

Now you need to select all (shift + click) the privileges you want to grant under “Available Privileges.” I just went ahead and gave myself all available privileges:

Available Privileges

Next you’ll need to click the “Grant” button which is the one that has the “less than” character <. Now the privileges you assigned will be in the “Assigned Privileges” column:

Assigned Privileges

This was a head-scratcher for me when I first started using MySQL Administrator. If you ever run into any difficulties just hover over the area you have questions about and usually a tool tip will appear. Now your database is ready to tag team with PHP in your web app of choice!

Well this should get you off and running with MySQL Administrator. I really only scratched the surface of things that can be done. Just click around and check stuff out… that’s pretty much how I learned. Have fun…

17 Responses to “MySQL Administrator on Mac OSX Leopard: A Few Tips and Tricks”

  1. Oszkar

    Hey, I’ve been addicted to MySQL Administrator in Windows for years, and it was very strange on Mac that it doesn’t work exactly the same way. The ‘host’ option gave me some thinking, thanks for being available with the solution in Google.

    I’ve added ‘localhost’ to the users’ hosts and it works great. Still, one might want to keep an eye on ‘%’ because that means ‘any host from the Internet’, so the default might mean ‘nooooo localhost, but anyone else – sure’ 🙂

  2. rickie

    I’m new to MySQL and I’m having problems connecting to my localhost.

    I typed in /var/mysql/mysql.sock in the Connect Using Socket: text field and I’m receiving and error.

    Error: Can’t connect to local MySQL server through socket ‘//var/mysql/mysql.sock’ (2) code 2002

    It gives me an option to ping host which doesn’t work.

    Any help getting this connected would be GREATLY appreciated.

  3. Stevie

    @Kento: Nice… thanks for the tip. I will definitely see if that works for me.

  4. Kento

    The reason it says “Skip” in your screenshots is because it changes to “Skip” whenever you are holding the command key. Change your screenshot hotkey, and your screenshots won’t have this problem! Not that it’s a big deal…

  5. Stevie

    I haven’t used cocoaMySQL in it’s new incarnation but I will check it out at some point. I’m a big Navicat fan at this point so it’s going to take a lot to pull me away…

  6. Jitsu

    Well.. after experiencing NUMEROUS bugs and crashes from the mySQL QB – I was nudged in the direction of cocaoMySql which has since become Sequel Pro ( http://code.google.com/p/sequel-pro/ ) – and boy… what a piece of work… ill reccomend it to anybody… 🙂

    JW.

  7. Paul

    @Scott

    I also had the “no host selected” problem. Your tip works great.

    Thanks!

  8. Stevie

    @DD: That is a crazy problem. I’ve never had that one myself. Certainly a head scratcher.

    @Tom: I’ve never had problems with MySQL Administrator being unstable at all. The obvious thing to do would be to reinstall it but other than that I’m unsure of how to attack that issue.

  9. Tom

    Hi nice article, my sql administrator is totally unstable! is this normal, or are your installations stable? the datebase is stable, but not the sql admin tool under mac osx leopard

  10. DD

    Thanks for the great info!
    Question though..
    I can’t seem to find anyone that can tell me why that-
    after I create new user, asign host(localhost), grant all privs, save changes, close MySql Admin.
    Then I find that in terminal, I get an error from mysql that the user is denied access to database due to privileges.
    Then I reopen MySql Admin and see all but two privs are assigned to that user except
    EVENT
    TRIGGER
    back to terminal – same error msg – access denied..
    any ideas?

  11. richbos

    Hi there

    MySQL Admin opens and connects but for all accounts (inc new ones I create) I just get ‘loading’….that’s it, unable to do anything else. Any ideas anyone? It’s driving (driven) me nuts..!

    Regards

    Rich B

  12. Scott

    @Lauras

    I had the same problem as you when I expand my user name it only shows %

    After a stiff drink I notice that there are two other buttons near the +/- for adding user. These are to add/remove hosts, if you highlight the % then click on the “Add Host” you are then presented with a table of three options, add them one at a time as follows and things should run fine (so far they have for me)

    1: localhost
    2: specify hostname (find this in preferences Sharing “Computer Name.local”
    3: specify hostname “127.0.0.1”

    Remember to save the changes.

    I hope this helps.

  13. Lauras

    On your very last screenshot (privileges), where it shows “stevie@localhost” for me it says “Not Applicable (no host selected)”. Trying to add localhost results in the “localhost” is already added message. I looked in mysql schema users table, I see that in the host column under my newly created userid contains %, when root account has localhost, 127.0.0.1 and one other. Its a fresh mysql 5.1 install (reinstalled many times to hopefully fix the issue). Any ideas?