Can’t connect to MySQL server on ‘127.0.0.1’ (61)

I downloaded the MySQL server and installed it on my Mac, but every time I tried to connect to it, I got the following error:

Can’t connect to MySQL server on ‘127.0.0.1’ (61)

There can be many reasons for this error, most of which are generously covered online, but in my case it was because MySQL was running on port 3307. I think the port was updated in a recent MySQL server update, because I’ve never had this issue with a previous MySQL server version. Here’s how to check if you have the same problem, and how to change it to port 3306 (unless you want to keep it running on port 3307).

1. Make sure MySQL server is running

Screenshot 2015-08-21 22.12.09

2. Confirm MySQL server is listening on port 3307

Run this command in Terminal:

$ sudo lsof -i -P | grep -i mysqld
mysqld  51211  _mysql  ...  0t0  TCP *:3307 (LISTEN)
mysqld  51211  _mysql  ...  0t0  TCP localhost:3307->localhost:54348 (ESTABLISHED)
mysqld  51211  _mysql  ...  0t0  TCP localhost:3307->localhost:54350 (ESTABLISHED)

Here’s what it means:

  • sudo — Run as root, so we can see everything that’s running, not just under the current username.
  • lsof — Display information about files open to Unix process.
    • -i — List all internet and x.25 (HP-UX) network files.
    • -P — Inhibit conversion of port numbers to port names.
  • grep mysqld — Display only lines matching mysqld.

As you can see, the MySQL server is running on port 3307. If that’s your problem, too, read on, but if not, I don’t think this post will address it.

3. Confirm port setting in property list (plist) is 3307

It turns out that this configuration file is the culprit:

$ defaults read /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

You can see the port setting on line 18:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
    Disabled = 0;
    ExitTimeOut = 600;
    GroupName = "_mysql";
    KeepAlive = 0;
    Label = "com.oracle.oss.mysql.mysqld";
    LaunchOnlyOnce = 0;
    ProcessType = Interactive;
    Program = "/usr/local/mysql/bin/mysqld";
    ProgramArguments =     (
        "/usr/local/mysql/bin/mysqld",
        "--user=_mysql",
        "--basedir=/usr/local/mysql",
        "--datadir=/usr/local/mysql/data",
        "--plugin-dir=/usr/local/mysql/lib/plugin",
        "--log-error=/usr/local/mysql/data/mysqld.local.err",
        "--pid-file=/usr/local/mysql/data/mysqld.local.pid",
        "--port=3307"
    );
    RunAtLoad = 0;
    SessionCreate = 1;
    UserName = "_mysql";
    WorkingDirectory = "/usr/local/mysql";
}
{
    Disabled = 0;
    ExitTimeOut = 600;
    GroupName = "_mysql";
    KeepAlive = 0;
    Label = "com.oracle.oss.mysql.mysqld";
    LaunchOnlyOnce = 0;
    ProcessType = Interactive;
    Program = "/usr/local/mysql/bin/mysqld";
    ProgramArguments =     (
        "/usr/local/mysql/bin/mysqld",
        "--user=_mysql",
        "--basedir=/usr/local/mysql",
        "--datadir=/usr/local/mysql/data",
        "--plugin-dir=/usr/local/mysql/lib/plugin",
        "--log-error=/usr/local/mysql/data/mysqld.local.err",
        "--pid-file=/usr/local/mysql/data/mysqld.local.pid",
        "--port=3307"
    );
    RunAtLoad = 0;
    SessionCreate = 1;
    UserName = "_mysql";
    WorkingDirectory = "/usr/local/mysql";
}

4. Update port number from 3307 to 3306

Stop the MySQL server and make a backup of the current plist:

$ sudo cp /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist.bak

Convert the plist to XML so you can easily edit it:

$ sudo plutil -convert xml1 /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Edit the file and replace the port with 3306:

$ sudo vi /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Disabled</key>
        <false/>
        <key>ExitTimeOut</key>
        <integer>600</integer>
        <key>GroupName</key>
        <string>_mysql</string>
        <key>KeepAlive</key>
        <false/>
        <key>Label</key>
        <string>com.oracle.oss.mysql.mysqld</string>
        <key>LaunchOnlyOnce</key>
        <false/>
        <key>ProcessType</key>
        <string>Interactive</string>
        <key>Program</key>
        <string>/usr/local/mysql/bin/mysqld</string>
        <key>ProgramArguments</key>
        <array>
                <string>/usr/local/mysql/bin/mysqld</string>
                <string>--user=_mysql</string>
                <string>--basedir=/usr/local/mysql</string>
                <string>--datadir=/usr/local/mysql/data</string>
                <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
                <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
                <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
                <string>--port=3306</string>
        </array>
        <key>RunAtLoad</key>
        <false/>
        <key>SessionCreate</key>
        <true/>
        <key>UserName</key>
        <string>_mysql</string>
        <key>WorkingDirectory</key>
        <string>/usr/local/mysql</string>
</dict>
</plist>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Disabled</key>
        <false/>
        <key>ExitTimeOut</key>
        <integer>600</integer>
        <key>GroupName</key>
        <string>_mysql</string>
        <key>KeepAlive</key>
        <false/>
        <key>Label</key>
        <string>com.oracle.oss.mysql.mysqld</string>
        <key>LaunchOnlyOnce</key>
        <false/>
        <key>ProcessType</key>
        <string>Interactive</string>
        <key>Program</key>
        <string>/usr/local/mysql/bin/mysqld</string>
        <key>ProgramArguments</key>
        <array>
                <string>/usr/local/mysql/bin/mysqld</string>
                <string>--user=_mysql</string>
                <string>--basedir=/usr/local/mysql</string>
                <string>--datadir=/usr/local/mysql/data</string>
                <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
                <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
                <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
                <string>--port=3306</string>
        </array>
        <key>RunAtLoad</key>
        <false/>
        <key>SessionCreate</key>
        <true/>
        <key>UserName</key>
        <string>_mysql</string>
        <key>WorkingDirectory</key>
        <string>/usr/local/mysql</string>
</dict>
</plist>

(You can quickly jump to the port by typing: /3307 and hitting ENTER. Switch to edit mode with: i. Save file with: ESC and :wq!)

Convert the plist back to binary:

$ sudo plutil -convert binary1 /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

5. Start MySQL server and confirm its running on port 3306

This should be all there is to it. Confirm that the MySQL server is running on port 3306 with the same command we used earlier:

$ sudo lsof -i -P | grep -i mysqld

It took me a little while to figure this out, but hopefully this post will save you some troubleshooting time.

5 thoughts on “Can’t connect to MySQL server on ‘127.0.0.1’ (61)

  1. andy

    mysql can’t start also if ram and drive space is full too used… so i need to make swap for ram, and deleting the unnecessary files or folder to make it free space, the mysql will be starting again

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *