[MySQL] Install difficulties : Can't connect to local MySQL
server through socket '/opt/local/var/run/mysql5/mysqld.sock'
Bill Hernandez
ms at mac-specialist.com
Mon Mar 31 20:17:58 PDT 2008
On Mar 31, 2008, at 5:45 AM, sourceforge.rocks at xemaps.com wrote:
> I've installed and reinstalled MySQL with the following command line:
> $ sudo port install mysql5 +server
>
> Initially, I had trouble creating the initial mysql database because
> the /opt/local/temp directory did not exist (and therefore the /opt/
> local/temp/mysql5 directory did not exist).
>
> Creating these two directories, and making the mysql user the owner
> of /opt/local/temp/mysql5 allowed me to build the initial database,
> but sadly it had no data in any of the non-index tables.
>
> I went ahead and unistalled mysql5 and reinstalled it, and now I get
> the following when I try to change the root password:
> $ mysqladmin -u root password 'newpass'
> mysqladmin: connect to server at 'localhost' failed error:
> 'Can't connect to local MySQL server through socket '/opt/local/var/
> run/mysql5/mysqld.sock' (2)' Check that mysqld is running and
> that the socket: '/opt/local/var/run/mysql5/mysqld.sock' exists!
>
> I verified that mysqld is running through the Activity Monitor.
>
> Sure enough, /opt/local/var/run/mysql5/mysqld.sock does not exist.
> In fact, mysqld.sock does not exist anywhere on my hard drive, a
> fact which I determined by:
> $ sudo find / -name "*.sock"
>
> Elsewhere, I found an instruction to
> $ touch opt/local/var/run/mysql5/mysqld.sock
When MySQL-Server is running the SOCKET (which is basically a file
containing some information to help other processes communicate with
MySQL-Server) is created automatically, and when MySQL-Server shuts
down the SOCKET is deleted. You can verify this to promote
understanding as to what is happening by trying this when you get
running :
$ open /opt/local/var/run/mysql5/
Then start MySQL-Server, and watch the SOCKET as it is created, the
shutdown MYSQL-Server and watch the SOCKET disappear as it is deleted.
> This command creates an empty file of the correct name; however,
> when I tried to change the root password, I got:
> $ mysqladmin -u root password 'newpass'
> mysqladmin: connect to server at 'localhost' failed
> error: 'Can't connect to local MySQL server through socket '/opt/
> local/var/run/mysql5/mysqld.sock' (38)' Check that mysqld is
> running and that the socket: '/opt/local/var/run/mysql5/mysqld.sock'
> exists!
You need to be aware of three locations where you have to use matching
values for the SOCKET :
( 1 ) /etc/my.cnf
( 2 ) /etc/php.ini
( 3 ) if using a GUI like Navicat the preferences have to be correct
for the connection.
YOUR PROBLEM, at the Terminal is obviously nothing to do with ( 2 ) or
( 3 ), it is however is caused by the fact that MySQL-Server is simply
not running, most likely due to incorrect permissions
You are trying to assign a <mysql_root_password> to the
mysql_root_user, but you are getting the error because MySQL-Server is
not running.
Typically this error is caused by the permissions not being set
correctly for the data directory, and/or the directory that is
supposed to contain the SOCKET.
Just to show what happens, if I shutdown MySQL-Server and try :
shell> ~ $ ps aux | grep mysql
bhernandez 1463 0.0 0.0 599740 384 s001 R+ 10:08PM
0:00.00 grep mysql
I can see that MySQL-Server is no longer running, and try :
shell> ~ $ mysqladmin -u root -p password 'new_mysql_root_password'
Enter password: <mysql_root_password>
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/
mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!
shell> ~ $
If I restart MySQL-Server and try the same thing, then it works fine,
and the password is accepted.
First you need to determine if MySQL-Server is running, try :
shell> ~ $ ps aux | grep mysql
<snip>
...
If everything went well, this command will show that MySQL-Server is
running, and
display 4-5 lines showing the associated processes, and some other
useful information
...
</snip>
Assuming that either ~/.bashrc, or ~/.bash_profile contain the correct
paths :
export PATH="/opt/local/bin:$PATH"
export PATH="/opt/local/sbin:$PATH"
If you have BBEdit.app you can try (if you don't have BBEdit.app, you
can substitute it with TextEdit.app) :
shell> $ open -a /Applications/BBEdit.app ~/.bashrc
shell> $ open -a /Applications/BBEdit.app ~/.bash_profile
OR
shell> $ open -a bbedit ~/.bashrc
shell> $ open -a textedit ~/.bash_profile
in order to check if the path exists, if not add them, but you
shouldn't have to, they should already be there.
If MySQL-Server is running, then set the <mysql_root_password> for the
first time, do not use the "-p" switch
shell> $ mysqladmin -u root -h localhost password
'<mysql_root_password>'
IF the SOCKET cannot be created, you will get an error when you try to
start MySQL-Server, which you will find if you look at the log.
IF your SOCKET is supposed to exist at :
'/opt/local/var/run/mysql5/mysqld.sock'
then try to set the permissions for the dir that is supposed to
contain it :
shell> $ sudo chown -R mysql:wheel /opt/local/var/run/mysql5
If you take a MySQL_Server that is running correctly and assign the
<mysql_root_password> for the first time, omit the "-p" switch
shell> ~ $ ps aux | grep mysql
<snip>
...
If everything went well, this command will show that MySQL-Server is
running, and
display 4-5 lines showing the associated processes, and some other
useful information
...
</snip>
If you try something like this :
shell> ~ $ mysqladmin -u root password '<new_mysql_root_password>'
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
The reason you got the '(using password: NO)' error here is because
you did not include the "-p" and there appears to be an existing
password, which is odd if this is the first time you tried this.
if the first line does not work, try the second line, and any password
you might have accidentally already tried, this shouldn't happen, but...
$ mysqladmin -u root password '<new_mysql_root_password>'
$ mysqladmin -u root -p password '<new_mysql_root_password>'
Just for informational purposes, In this next case I will change the
<old_mysql_root_password> with <new_mysql_root_password>
shell> ~ $ mysqladmin -u root -p password '<new_mysql_root_password>'
Enter password: <old_mysql_root_password>
The previous line authenticates that it's ok to change the password
To restore my old password to its original value, I will reverse the
order of the passwords
shell> ~ $ mysqladmin -u root -p password '<old_mysql_root_password>'
Enter password: <new_mysql_root_password>
and now my original <old_mysql_root_password> password has been
restored. This has nothing to do with your problem, but it may help
you or others.
shell> ~ $
Try :
Most documentation on the web, and even some from the examples at
mysql.com show the command the same way you tried it, and they always
generate an error when you try to create the password for the root
user for the second host:
shell> $ mysqladmin -u root -h localhost password
'<mysql_root_password>'
shell> $ mysqladmin -u root -h my_computer_name.local password
'<mysql_root_password>'
by then the root password already exists, and the second line needs
the "-p" switch
shell> $ mysqladmin -u root -h my_computer_name.local -p password
'<mysql_root_password>'
I use the <mysql_root_password> to differentiate from the
<os_root_password> which is requested with the sudo command
Here's a shell script that may help with your permissions issue, save
it as a text file :
You can copy the following 9 commands :
shell> $ mkdir -p ~/Desktop/bin
shell> $ open ~/Desktop/bin
shell> $ touch ~/Desktop/bin/bh_mysql_fix_perms
shell> $ open -a textedit ~/Desktop/bin/bh_mysql_fix_perms
# COPY AND PASTE THE SCRIPT BELOW, SAVE IT, CLOSE IT, RUN IT :
shell> $ binpath=~/Desktop/bin/; echo "export PATH=\"${binpath}:\$PATH
\"" >> ~/.bash_profile
shell> $ user=$(id -u -n)
shell> $ sudo chown -R ${user}:admin ~/Desktop/bin
shell> $ sudo chmod -R 770 ~/Desktop/bin
shell> $ bh_mysql_fix_perms
If you get an error like this, it is caused by carriage returns,
instead of linefeeds. BBEdit.app allows selection of line endings,
TextEdit does the same by saving as "Unicode(UTF-8)"
shell> ~ $ bh_mysql_fix_perms
#: bad interpreter: No such file or directorymysql: /bin/bash
WHEN YOU COPY AND PASTE THE SCRIPT, SAVE IT, CLOSE IT, RUN IT :
IF YOU USE textedit MAKE SURE TO SAVE AS "Unicode(UTF-8)"
Select Format -> Make Plain Text MenuItem
Select File -> Save As
SAVE AS "Unicode(UTF-8)"
HERE ARE THE SAME COMMANDS WITHOUT THE "shell> $"
sudo echo
mkdir -p ~/Desktop/bin
open ~/Desktop/bin
touch ~/Desktop/bin/bh_mysql_fix_perms
open -a textedit ~/Desktop/bin/bh_mysql_fix_perms
# COPY AND PASTE THE SCRIPT INTO THE OPEN TextEdit page
# Select Format -> Make Plain Text MenuItem
# Select File -> Save As
# SAVE AS "Unicode(UTF-8)"
binpath=~/Desktop/bin/; echo "export PATH=\"${binpath}:\$PATH\"" >>
~/.bash_profile
user=$(id -u -n)
sudo chown -R ${user}:admin ~/Desktop/bin
sudo chmod -R 770 ~/Desktop/bin
# QUIT OUT OF THE TERMINAL, AND RELAUNCH IT AGAIN
bh_mysql_fix_perms
HERE IS THE ACTUAL SCRIPT
# +---------+---------+---------+---------+---------+---------
+---------+---------+
#!/bin/bash
# Author - Bill Hernandez (Plano, Texas)
# ScriptName - bh_mysql_fix_perms v_1.0.0
# Modified - Thursday, September 13, 2007 (7:59 PM)
# THIS IS FOR USE WITH THE MACPORTS INSTALL OF MySQL ONLY
# IF YOU HAVE A DIFFERENT INSTALL IT WONT HURT ANYTHING
# BUT YOU WILL GET A BUNCH OF "No such file or directory" ERRORS
# IT IS CALLED ONE OF TWO WAYS, TO CHECK OR SET PERMISSIONS
# called shell> $ bh_mysql "ck"
# called shell> $ bh_mysql "set"
# +---------+---------+---------+---------+---------+---------
+---------+---------+
# bh_mysql
# +---------+---------+---------+---------+---------+---------
+---------+---------+
sudo echo
echo argc is $#. argv is "$*"
echo
if [ $# = 0 ]; then
choice="ck";
elif [ "$*" = "check" ]; then
choice="ck";
else
choice="$*";
fi
divider
=
"---------------------------------------------------------------------------"
if [ $choice = "ck" ]; then
echo $divider
echo "Checking [MySQL] Permissions..."
echo $divider
echo "$ sudo ls -Rls /opt/local/bin/mysql5"
sudo ls -Rls /opt/local/bin/mysql5
echo "$ sudo ls -Rls /opt/local/etc/mysql5"
sudo ls -Rls /opt/local/etc/mysql5
echo "$ sudo ls -Rls /opt/local/include/mysql5"
sudo ls -Rls /opt/local/include/mysql5
echo "$ sudo ls -Rls /opt/local/lib/mysql5"
sudo ls -Rls /opt/local/lib/mysql5
echo "$ sudo ls -Rls /opt/local/share/mysql5"
sudo ls -Rls /opt/local/share/mysql5
echo "$ sudo ls -Rls /opt/local/var/db/mysql5"
sudo ls -Rls /opt/local/var/db/mysql5
echo "$ sudo ls -Rls /opt/local/var/run/mysql5"
sudo ls -Rls /opt/local/var/run/mysql5
else
echo $divider
echo "Setting [MySQL] Permissions..."
echo $divider
echo "$ sudo chown -R root:wheel /opt/local/bin/mysql5"
sudo chown -R root:wheel /opt/local/bin/mysql5
echo "$ sudo chown -R root:wheel /opt/local/etc/mysql5"
sudo chown -R root:wheel /opt/local/etc/mysql5
echo "$ sudo chown -R mysql:mysql /opt/local/include/mysql5"
sudo chown -R mysql:mysql /opt/local/include/mysql5
echo "$ sudo chown -R root:wheel /opt/local/lib/mysql5"
sudo chown -R root:wheel /opt/local/lib/mysql5
echo "$ sudo chown -R mysql:mysql /opt/local/share/mysql5"
sudo chown -R mysql:mysql /opt/local/share/mysql5
echo "$ sudo chown -R mysql:mysql /opt/local/var/db/mysql5"
sudo chown -R mysql:mysql /opt/local/var/db/mysql5
echo "$ sudo chown -R root:wheel /opt/local/var/run/mysql5"
sudo chown -R root:wheel /opt/local/var/run/mysql5
echo $divider
echo "$ sudo chmod -R 755 /opt/local/bin/mysql5"
sudo chmod -R 755 /opt/local/bin/mysql5
echo "$ sudo chmod -R 755 /opt/local/etc/mysql5"
sudo chmod -R 755 /opt/local/etc/mysql5
echo "$ sudo chmod -R 755 /opt/local/include/mysql5"
sudo chmod -R 755 /opt/local/include/mysql5
echo "$ sudo chmod -R 755 /opt/local/lib/mysql5"
sudo chmod -R 755 /opt/local/lib/mysql5
echo "$ sudo chmod -R 755 /opt/local/share/mysql5"
sudo chmod -R 755 /opt/local/share/mysql5
echo "$ sudo chmod -R 755 /opt/local/var/db/mysql5"
sudo chmod -R 755 /opt/local/var/db/mysql5
echo "$ sudo chmod -R 755 /opt/local/var/run/mysql5"
sudo chmod -R 755 /opt/local/var/run/mysql5
echo $divider
fi
# +---------+---------+---------+---------+---------+---------
+---------+---------+
Bill Hernandez
ms at mac-specialist.com
More information about the macports-users
mailing list