Solved

Mysqladmin: connect to server at 'localhost' failed

Posted on 2013-12-09
43
12,012 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 13
  • 9
43 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39707346
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
ID: 39707347
Run the command:
man mysqladmin

Open in new window

to view the manual pages for it.
0
 

Author Comment

by:sharingsunshine
ID: 39707480
I tried that command

Macintosh:~ abc$ man mysqladmin
No manual entry for mysqladmin
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 39707496
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
ID: 39707616
@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
ID: 39707686
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
ID: 39707704
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
ID: 39707708
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
ID: 39707710
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
ID: 39707713
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
ID: 39708314
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
ID: 39709020
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
ID: 39709037
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
ID: 39709066
That means that mysqld is not running right now.  You can proceed forward with the rest of the steps.
0
 

Author Comment

by:sharingsunshine
ID: 39709092
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
ID: 39709103
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
ID: 39709139
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
ID: 39709165
Try:

find /usr/local/mysql | grep install
0
 

Author Comment

by:sharingsunshine
ID: 39709260
it comes up blank
0
 
LVL 19

Expert Comment

by:xterm
ID: 39709313
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
ID: 39709375
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
 
LVL 19

Expert Comment

by:xterm
ID: 39709785
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
ID: 39709850
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
ID: 39709889
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
ID: 39709896
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
ID: 39709912
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
ID: 39709942
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
ID: 39709950
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
ID: 39709953
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
ID: 39709962
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
ID: 39709970
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
ID: 39709988
That looks like an unrelated error, which may mean you run into trouble with other attempts too.
0
 

Author Comment

by:sharingsunshine
ID: 39710011
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
ID: 39710032
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
ID: 39710046
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
ID: 39710065
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
ID: 39710099
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
ID: 39710104
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
ID: 39711084
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
ID: 39711157
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
ID: 39711167
I appreciate both of you helping me.
0
 
LVL 35

Expert Comment

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

Author Comment

by:sharingsunshine
ID: 39712486
Thanks and Your Welcome!
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

734 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