Snow Leopard Server MySQL troubles

Discussion in 'Mac OS X Server, Xserve, and Networking' started by dysan819, Jul 2, 2010.

  1. macrumors newbie

    Joined:
    Jun 25, 2010
    #1
    I am attempting to use MySQL and cannot seem to authenticate.

    I am stuck at

    Code:
    $ 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:

    Code:
    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
     
  2. macrumors member

    Joined:
    May 6, 2008
    #2
    do you have a password set for your mysql?

    if I remember correctly it comes without initially

    try to connect using
    ...$ mysql -u root
     
  3. macrumors 68030

    Winni

    Joined:
    Oct 15, 2008
    Location:
    Germany.
    #3
    The MySQL root user is NOT the same as the OS X root user. Try what svenwillmann said.
     
  4. thread starter macrumors newbie

    Joined:
    Jun 25, 2010
    #4
    Yes, I had previously tried 'mysql -u root', which did not work.

    Code:
    $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?
     
  5. macrumors regular

    Joined:
    Jul 14, 2006
    #5
    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
     
  6. macrumors member

    Joined:
    May 6, 2008
    #6
    reinstall or change MySQL on OS X Server 10.6

    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
     
  7. macrumors newbie

    Joined:
    Jul 14, 2010
    #7
    Registered just to say thanks for the comprehensive collection of information. This worked perfectly for me.
     
  8. macrumors member

    Joined:
    May 6, 2008
    #8
    dysan819:
    I know it is a while...
    Would be nice to hear if this solve your problem?
     
  9. macrumors 6502a

    mustang_dvs

    Joined:
    Feb 9, 2003
    Location:
    Durham, NC
    #9
    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.
     
  10. macrumors 601

    BornAgainMac

    Joined:
    Feb 4, 2004
    Location:
    Florida Resident
    #10
    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.
     
  11. macrumors newbie

    Joined:
    Apr 6, 2011
    #11
    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.
     
  12. macrumors newbie

    Joined:
    Aug 6, 2012
    #12
    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.
     

Share This Page