Solved

Mysqladmin: connect to server at 'localhost' failed

Posted on 2013-12-09
43
10,415 Views
Last Modified: 2013-12-11
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
0
Comment
Question by:sharingsunshine
  • 21
  • 13
  • 9
43 Comments
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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

0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
Run the command:
man mysqladmin

Open in new window

to view the manual pages for it.
0
 

Author Comment

by:sharingsunshine
Comment Utility
I tried that command

Macintosh:~ abc$ man mysqladmin
No manual entry for mysqladmin
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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.
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
@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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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?
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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).
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 
LVL 19

Accepted Solution

by:
xterm earned 250 total points
Comment Utility
You have one of two issues here, either you didn't kill the old mysqld process before running safe_mysqld or there are files in /usr/local/mysql/data that are not owned by the mysql user.

At any rate, since it's a fresh installation, I suggest doing the following:

1)  Kill any mysqld processes

You will need to use the ps command to find them.  So something like this:

xterm@foo:/ide> ps ax | grep mysql
11489 ?        Sl    86:34 /usr/sbin/mysqld --defaults-file=/home/xterm/.local/share/akonadi/mysql.conf --datadir=/home/xterm/.local/share/akonadi/db_data/ --socket=/home/xterm/.local/share/akonadi/socket-hp/mysql.socket

The number in the first column (11489) is the process id.  As root, type "kill 11489" (substituting whatever number appears on your system)

2)  Clear out the old databases by simply backing up the directory, then recreate the data directory empty:

cd /usr/local/mysql/
mv data data.bak
mkdir data
chown mysql:mysql data

This saves your old data directory which you can delete later if you don't need it, and creates a new empty one.

3)  Now run the script to create the basic databases:

/usr/local/mysql/bin/mysql_install_db

This will install the default databases with no root password, and should also tell you the command you will then use to start the MySQL server again.

4)  Start the MySQL server

5 [optional])  From the output of #3 above, you should see some directions on how to set the root password if you want to.  Something like the following:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/bin/mysqladmin -u root -h localhost password 'new-password'

6)  Now you should be able to run mysqladmin.  If you ran #5 above to set the root password, then do:

mysqladmin -uroot -prootpassword status

If you didn't set the password, you should just be able to do:

mysqladmin -uroot status

You should see something like this in the output:

[xterm@foo ~]$ mysqladmin -uroot -prootpass status
Uptime: 3092473  Threads: 1  Questions: 114677  Slow queries: 0  Opens: 53  Flush tables: 1  Open tables: 46  Queries per second avg: 0.037

If anything fails, please stop right there, and cut & paste the output.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
That means that mysqld is not running right now.  You can proceed forward with the rest of the steps.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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?
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
Try:

find /usr/local/mysql | grep install
0
 

Author Comment

by:sharingsunshine
Comment Utility
it comes up blank
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 19

Expert Comment

by:xterm
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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)'
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 250 total points
Comment Utility
Did you try just hitting enter when it requested a password? If so, then perhaps the installation process you're using is setting an initial value for the root password.
0
 

Author Comment

by:sharingsunshine
Comment Utility
no, I didn't just hit enter is that something I can do now or is it set and something needs to be removed?
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
That looks like an unrelated error, which may mean you run into trouble with other attempts too.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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

0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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.
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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.
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 

Author Comment

by:sharingsunshine
Comment Utility
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
0
 

Author Closing Comment

by:sharingsunshine
Comment Utility
I appreciate both of you helping me.
0
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
Glad you got it solved, and thanks for the points!
0
 

Author Comment

by:sharingsunshine
Comment Utility
Thanks and Your Welcome!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now