Link to home
Start Free TrialLog in
Avatar of sharingsunshine
sharingsunshineFlag for United States of America

asked on

Mysqladmin: connect to server at 'localhost' failed

Hi,

I am taking a course in PHP on Lynda.com and I need to know why the following command line code won't work.

mysqladmin -u root password

then I get this error message
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

This is a brand new mysql setup on my desktop Mac I am running OS X 10.9.  I know all about Mamp but to do this course I have to do it this way.

So please help me get this figured out.

Thanks,

Randal
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

The command you seem to be trying to run is to set the password for the root user to a new value such as newpass. The command should look like this:
mysqladmin -u root password newpass

Open in new window


However, if there's an existing password for root, access is denied. You can enter your password as part of the command:
mysqladmin -u root -pexistingpassword password newpass

Open in new window

or just add the -p parameter, and you'll be prompted for it:
mysqladmin -u root -p password newpass

Open in new window

Run the command:
man mysqladmin

Open in new window

to view the manual pages for it.
Avatar of sharingsunshine

ASKER

I tried that command

Macintosh:~ abc$ man mysqladmin
No manual entry for mysqladmin
Sorry, I guess the man command only works in *nix. You can get documentation here: http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html

My first post still applies though. It should work for you.
Avatar of xterm
xterm

@TerryAtOpus
The man command actually worked fine, but it said there was no manual entry for that command.  I assume the poster is using either a source or tarball based distribution of MySQL rather than a packaged version.

@sharingsunshine
The syntax is:  mysqladmin -uroot -prootpass <command>

Or as TerryAtOpus said, you can use -p flag not followed by anything, and then mysqladmin will prompt you for the password for root.
When I put in the command
mysqladmin -uroot -p it immediately displays the man pages.  Could it be it has a root password already.

Now I just installed this from a tar ball so maybe one of my password attempts took and I didn't know it.  Is there a way to find out the password that is set for root?  Otherwise, how do I reset it?
It displays the manual because you haven't given it a command. Try:
mysqladmin -uroot -p password newpass

Open in new window

where "password" is literally exactly that (don't change it! It's the command to change the password), and "newpass" is the new password (change it to the value you want as the new password).

Because the -p parameter is not followed by the existing password, it will prompt you for it (if there is one).
rjw mysqladmin -uroot -p password abcde
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
rjw mysqladmin -uroot -p password
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

I substituted abcde for a real password and both times got errors.
Sounds like the existing password you're entering (when it says "Enter password") is wrong. Do you actually know the password for the root user? If not, I believe it might be stored in a file somewhere that (in Linux) is accessible by the root (Linux) user, or some equivalent on a mac.
There are some documented instructions for resetting the root password here if you need them: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-generic
no, I don't know the password if what I am using is incorrect.  Tell me where to find the root password location.

I tried following these directions since a local Mac MySql is a bit different
http://innovativethought.net/2007/05/17/resetting-your-forgotten-mysql-password/

trying to run this command
/usr/local/mysql/bin/safe_mysqld --skip-grant-tables

I get this error

rjw mysqld_safe --skip-grant-tables
131210 07:23:50 mysqld_safe Logging to '/usr/local/mysql/data/Macintosh.local.err'.
131210 07:23:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe: line 129: /usr/local/mysql/data/Macintosh.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 166: /usr/local/mysql/data/Macintosh.local.err: Permission denied
131210 07:23:50 mysqld_safe mysqld from pid file /usr/local/mysql/data/Macintosh.local.pid ended
/usr/local/mysql/bin/mysqld_safe: line 129: /usr/local/mysql/data/Macintosh.local.err: Permission denied
ASKER CERTIFIED SOLUTION
Avatar of xterm
xterm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
when I start the first command this is what I get

rjw ps ax | grep mysql
 2455 s000  S+     0:00.00 grep mysql

wasn't sure what to do since it didn't look like your output
That means that mysqld is not running right now.  You can proceed forward with the rest of the steps.
after I changed directories to run this command I get permission denied

rjw mv data data.bak
mv: rename data to data.bak: Permission denied
You will need to become root to execute all of these steps up until the server is started in step #4, then you can return to user rjw again to execute the mysqladmin commands.
rjw sudo /usr/local/mysql/bin/mysql_install_db
sudo: /usr/local/mysql/bin/mysql_install_db: command not found

I checked that directory and there isn't anything close to it could it be a different command?
Try:

find /usr/local/mysql | grep install
it comes up blank
Can you give me the link to where you downloaded your version of MySQL so I can look at it?

That is normally included in the distribution, but I gather you have some kind of binary installation.
This is the download page http://dev.mysql.com/downloads/file.php?id=450342

I am glad to get another one if that will expedite getting this resolved.  Just let me know.
Yes, please go ahead and reinstall it - I get the feeling that somewhere along the way it sets a default password and either you have to enter it, or it will tell it to you somewhere in the directions.

You should simply be able to rename or remove /usr/local/mysql and then re-execute the original dmg package installation.

Please paste anything relevant from the installation.
ok, I used the following:

rjw sudo mv mysql mysql.bak
rjw rm -rf mysql.bak
rm: mysql.bak: Permission denied
rjw sudo rm -rf mysql.bak
rjw sudo rm -rf mysql-5.6.15-osx10.7-x86_64
rjw which mysql
/usr/local/mysql/bin/mysql

This is the error I am getting
rjw mysqladmin -u root password  
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Okay, did you reinstall the original .dmg package that you sent me the link to?

All you've done so far is remove the mysql data directory.
yes, I am sorry I didn't make that clear I reinstalled everything and there were no hitches until I tried to set the password as I indicated earlier.
Perhaps try:
mysqladmin -u root -p password newpassword

Open in new window

where newpassword is the new password you want to set for root.

If that doesn't work, you could try a different technique as documented here under the "Secure the Database" section of the page.

There's also some information here that might help you figure out exactly what the problem is: http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html
tried the first command and got this error

rjw mysqladmin -u root -p password 123456  
*  Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Then I tried the link and the first command they list is this one

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

I then tried the sudo command
rjw sudo mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

*  when it asked me to put password in the second time I repeated what I put in initially.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
no, I didn't just hit enter is that something I can do now or is it set and something needs to be removed?
We still don't know whether you have a password set for root after the initial install. If not, then I'm not sure whether just hitting enter when asked for the password would work, or whether you just need to exclude the -p parameter, in which case the command would be:
mysqladmin -u root password 123456

Open in new window


I see also that there are some instructions for resetting the root password here, in section C.5.4.1.3:
http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html

If you *do* have a root password set, and you don't know the value, then you may need to follow the instructions for resetting it.
on the first suggestion I get this

rjw mysqladmin -u root password 123456
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!
rjw

will try resetting
That looks like an unrelated error, which may mean you run into trouble with other attempts too.
I think the problem is after killing some of the processes mysql quit running indicated by the preference pains and doesn't seem to want to restart

so on the command line I used the following commands and got a large readout, hopefully this will tell something of value.

rjw service mysqld start
-bash: service: command not found
rjw mysqld start
2013-12-10 17:10:41 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-12-10 17:10:41 6193 [Warning] Can't create test file /usr/local/mysql-5.6.15-osx10.7-x86_64/data/Macintosh.lower-test
2013-12-10 17:10:41 6193 [Warning] Can't create test file /usr/local/mysql-5.6.15-osx10.7-x86_64/data/Macintosh.lower-test
2013-12-10 17:10:41 6193 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2013-12-10 17:10:41 6193 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-12-10 17:10:41 6193 [Note] InnoDB: The InnoDB memory heap is disabled
2013-12-10 17:10:41 6193 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-12-10 17:10:41 6193 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-12-10 17:10:41 6193 [Note] InnoDB: Using CPU crc32 instructions
2013-12-10 17:10:41 6193 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-12-10 17:10:41 6193 [Note] InnoDB: Completed initialization of buffer pool
2013-12-10 17:10:41 6193 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2013-12-10 17:10:41 6193 [ERROR] InnoDB: The system tablespace must be writable!
2013-12-10 17:10:41 6193 [ERROR] Plugin 'InnoDB' init function returned error.
2013-12-10 17:10:41 6193 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mysqld: Too many arguments (first extra is 'start').
Use --verbose --help to get a list of available options
2013-12-10 17:10:41 6193 [ERROR] Aborting

2013-12-10 17:10:41 6193 [Note] Binlog end
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'partition'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_METRICS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_CMP'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'INNODB_TRX'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'BLACKHOLE'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'ARCHIVE'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'MRG_MYISAM'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'MyISAM'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'MEMORY'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'CSV'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'sha256_password'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'mysql_old_password'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'mysql_native_password'
2013-12-10 17:10:41 6193 [Note] Shutting down plugin 'binlog'
2013-12-10 17:10:41 6193 [Note] mysqld: Shutdown complete

Open in new window

This is really beyond my knowledge, I'm afraid; once you manage to get it back up and running, hopefully the suggestions I've provided with sort out the issue with the root password.

@xterm may still be able to help, otherwise maybe it would be sensible to try a clean reinstall or ask a new question regarding these new errors.
I did a clean reinstall already at xterm's suggestion.  If I ask another question won't that prevent you and xterm from getting points?  Since there isn't a solution.
I'm not worried about the points; I'd just like to see you get enough of a response to get your problems solved! Once you get mysql running again, you may still need to make use of the answers given here to set the root password, in which case you can pick a solution at that point.

My understanding is that you won't get abandoned question alerts if the last post in the thread is yours.
Please run mysql_upgrade, and then try to restart the server - something is happening in the installation where the default root user isn't being created correctly.

And yes, don't worry about points - let's just try to get this working - you don't need to ask another question yet, because we're really still just on the original one.
Thanks to you both being more concerned for my solution.  I have removed all of the old mysql and starting the new one.  I have 10.9 and this installation is specifically for it.  So I will let you know when it is complete.
I reinstalled mysql and got this at the end about some missing files.  Should I try to find those files or is something else needed?

Installer: The upgrade was successful.
...
Click Install to install the MySQL preferance pane
...
sudo: /usr/local/mysql/support-files/mysql.server: command not found
sudo: /usr/local/mysql/support-files/mysql.server: command not found
/dev/fd/63: line 119: SORRY, MySQL IS NOT RUNNING ... THERE MUST BE A PROBLEM: command not found
I redid the mysql distribution I originally used so I used this command and no errors seen so I am going to say this is resolved.

I am going to divide the points because you both have contributed greatly.

rjw mysqladmin -u root -p password 123456
Enter password:
rjw
I appreciate both of you helping me.
Glad you got it solved, and thanks for the points!
Thanks and Your Welcome!