Snow Leopard Server MySQL troubles

[dysan819]

I am attempting to use MySQL and cannot seem to authenticate.

I am stuck at

$ mysql -u root -p mysql
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have entered the root user's password.
I have reset the root password in the Server Admin.
I have entered every password I could think of.
Is this not a password problem, but something more sinister?

I am trying to set up ATMAIL webmail client and am getting this error:

Error

Could not connect to Database Server:

SQLSTATE[00000] [1130] Host 'localhost' is not allowed to connect to this MySQL server

Does this relate to the first problem?
I have "Allow network connections" checked.

Thank you in advance!
-Brett

[svenwillmann]

do you have a password set for your mysql?

if I remember correctly it comes without initially

try to connect using
...$ mysql -u root

[Winni]

The MySQL root user is NOT the same as the OS X root user. Try what svenwillmann said.

[dysan819]

Yes, I had previously tried 'mysql -u root', which did not work.

$mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I have followed various instructions for resetting the root password which appear on both the Apple and MySQL sites. The password resetting usually does not end with any errors, but yields the same result.

I was thinking that the problem was related to the fact that MySQL was preinstalled with OS X Server. The documentation from Apple gives some pretty easy to follow instructions that don't seem to work.

Would reinstalling MySQL work, or do OS X Server services rely on it in its current form?

[PilotWoo]

I've not tried MySQL on OSX but a default MySQL install on linux has a blank root password after install. The MySQL root password is not the same thing as the root account in the OS.

Try the syntax below. The -P switch is required so that it will prompt for a password (using password: YES). When prompted for the password, press return.

mysql -U root -P

[svenwillmann]

A reinstall of MySQL should work and no, OS X Server services do not rely on the current.

Below you will find a step by step guide how to
uninstall current MySQL
reinstall new MySQL
setup the new MySQL
integrate in launchd

If you do not have anything in your current MySQL DB (which I think you don’t as you can’t access it anyway) you can follow these steps
tried on a os x server 10.6.3

(it is a compilation of excerpts from different machines, so the name of the prompts etc will change in this guide, like sunset:~ svenwillmann$ or xserv or wscsrv ... but not on your side)


Let me know if this solved your problem



1: ###########################

Uninstall MySQL

Fire up terminal and exec following commands

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

(if entry exists
edit /etc/hostconfig and remove the line MYSQLCOM=-YES-



sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*



2: ###########################
install mysql
download it from http://www.mysql.com/downloads/mysql/
make sure you install the right version eg 64bit or 32bit
"mysql-5.1.46-osx10.6-x86_64.dmg" 64bit
"mysql-5.1.46-osx10.6-x86.dmg" 32bit

otherwise you could end up with an error like
Neither host 'wscsrv.mydomain.local' nor 'localhost' could be looked up with ./bin/resolveip

(if installed from "*.dmg" you will find it in dir /usr/local

3: ###########################
Fire up terminal and make sure the path is there

sunset:~ svenwillmann$ echo $PATH
/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin

4: ###########################
Don't rely on your terminal application to set those paths;
instead go ahead and edit/create the file .bash_profile in your home folder (~).

sunset:~ svenwillmann$ touch ~/.bash_profile

inside this file
------------
PATH="/usr/local/bin:/usr/local/sbin:$PATH" # if not already present
PATH="$PATH:/usr/local/mysql/bin"
export PATH=$PATH
------------

5: ###########################
If you don't want to reopen the terminal or switch to another tab, just execute

sunset:~ svenwillmann$ source ~/.bash_profile

6: ###########################
check the path again

sunset:~ svenwillmann$ echo $PATH
/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/usr/local/mysql/bin

7: ###########################
install default tables etc.

cd to
/usr/local/mysql

and execute
sudo ./scripts/mysql_install_db --user=mysql

eg
------------------------
sunset:mysql svenwillmann$ sudo ./scripts/mysql_install_db --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h sunset.mydomain.local password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

sunset:mysql svenwillmann$
------------------------

8: ###########################

now go and check if your installation is starting by

sunset:mysql svenwillmann$ sudo ./bin/mysqld_safe

in terminal

9: ###########################
open a new terminal window and do a

sunset:mysql svenwillmann$ mysql -u root

you should now be connected to your mysql db

type
exit at the
mysql>
prompt to log out of mysql

10: ###########################
now shutdown the mysql server by using the following command in terminal

mysqladmin -u root -p shutdown

11: ###########################

now in Server Admin under MySQL service -> settings change your Database location to
/usr/local/mysql

12: ###########################
in order to use Server Admin with your new MySQL installation you have to do following

in terminal exec
sudo launchctl unload /System/Library/LaunchDaemons/org.mysql.mysqld.plist

13: ###########################
now edit the file
/System/Library/LaunchDaemons/org.mysql.mysqld.plist

and change the path to the new MySQL installation in the following lines:


<string>--datadir=/usr/local/mysql-5.1.46-osx10.6-x86</string>
<string>--pid-file=/usr/local/mysql-5.1.46-osx10.6-x86/xserv.local.pid</string>

make sure you only change the part of the folder where mysql is installed to the right version you installed
eg. ...../mysql-5.1.46-osx10.6-x86/..... in my case

save the file (same location /System/Library/LaunchDaemons/org.mysql.mysqld.plist )

14: ###########################
in terminal exec
sudo launchctl load /System/Library/LaunchDaemons/org.mysql.mysqld.plist


15: ###########################
now go and check in Server Admin under MySQL service

you should be ready to go

[mflocco]

Registered just to say thanks for the comprehensive collection of information. This worked perfectly for me.

[svenwillmann]

Originally Posted by svenwillmann

Let me know if this solved your problem

dysan819:
I know it is a while...
Would be nice to hear if this solve your problem?

[mustang_dvs]

I realize that this thread is a little old, but I wanted to express my thanks.

It saved me quite a lot of heartache and time otherwise spent performing a reinstallation of the entire server OS.

[BornAgainMac]

What about just using Server Admin and adding the MySQL service? You can set the MySQL Root Password with this service and the database location.

[newonmacrumors]

This apply to Snow Leopard Server.
Go to Server Admin, expand your server in the left panel and click on MySQL. Click on the "Stop MySQL" button at the bottom. It will warn you that MySQL clients may lost their connection. Click "Stop Now". Then click on Setting at the right panel. The "Set MySQL Root Password" button should be enabled. Click on it to set the password.

[prattio]

Thank you for this thread. After upgrading from OSX Server 10.6 to 10.8, the settings for SQL were shot.

I followed this guide, and SQL is running on the server again. Thank you.