sharingsunshine
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
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
Run the command:
man mysqladmin
to view the manual pages for it.
ASKER
I tried that command
Macintosh:~ abc$ man mysqladmin
No manual entry for mysqladmin
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.
My first post still applies though. It should work for you.
@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.
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.
ASKER
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?
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:
Because the -p parameter is not followed by the existing password, it will prompt you for it (if there is one).
mysqladmin -uroot -p password newpass
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).
ASKER
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.
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
ASKER
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/Mac intosh.loc al.err'.
131210 07:23:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysql d_safe: line 129: /usr/local/mysql/data/Maci ntosh.loca l.err: Permission denied
/usr/local/mysql/bin/mysql d_safe: line 166: /usr/local/mysql/data/Maci ntosh.loca l.err: Permission denied
131210 07:23:50 mysqld_safe mysqld from pid file /usr/local/mysql/data/Maci ntosh.loca l.pid ended
/usr/local/mysql/bin/mysql d_safe: line 129: /usr/local/mysql/data/Maci ntosh.loca l.err: Permission denied
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_
I get this error
rjw mysqld_safe --skip-grant-tables
131210 07:23:50 mysqld_safe Logging to '/usr/local/mysql/data/Mac
131210 07:23:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysql
131210 07:23:50 mysqld_safe mysqld from pid file /usr/local/mysql/data/Maci
/usr/local/mysql/bin/mysql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
ASKER
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
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.
ASKER
rjw sudo /usr/local/mysql/bin/mysql _install_d b
sudo: /usr/local/mysql/bin/mysql _install_d b: command not found
I checked that directory and there isn't anything close to it could it be a different command?
sudo: /usr/local/mysql/bin/mysql
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
find /usr/local/mysql | grep install
ASKER
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.
That is normally included in the distribution, but I gather you have some kind of binary installation.
ASKER
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.
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.
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.
ASKER
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_6 4
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)'
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_6
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.
All you've done so far is remove the mysql data directory.
ASKER
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:
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
mysqladmin -u root -p password newpassword
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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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.
mysqladmin -u root password 123456
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.
ASKER
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
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.
ASKER
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.
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
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.
@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.
ASKER
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.
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.
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.
ASKER
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.
ASKER
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-f iles/mysql .server: command not found
sudo: /usr/local/mysql/support-f iles/mysql .server: command not found
/dev/fd/63: line 119: SORRY, MySQL IS NOT RUNNING ... THERE MUST BE A PROBLEM: command not found
Installer: The upgrade was successful.
...
Click Install to install the MySQL preferance pane
...
sudo: /usr/local/mysql/support-f
sudo: /usr/local/mysql/support-f
/dev/fd/63: line 119: SORRY, MySQL IS NOT RUNNING ... THERE MUST BE A PROBLEM: command not found
ASKER
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 am going to divide the points because you both have contributed greatly.
rjw mysqladmin -u root -p password 123456
Enter password:
rjw
ASKER
I appreciate both of you helping me.
Glad you got it solved, and thanks for the points!
ASKER
Thanks and Your Welcome!
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:
Open in new window
or just add the -p parameter, and you'll be prompted for it:Open in new window