Solved

add MySQL user, can't connect

Posted on 2014-01-26
27
536 Views
Last Modified: 2014-04-16
Hi Experts,

I finally have mysqld running on funplug- I created a unix account and setup mysqld so that datadir was owned by a user called, 'mysql'.  Problem is I can't login with this user because it was never created in the tables.  I also can't login as root because that's just disabled I guess..  (getting access denied for user=root).  I have no idea how to initially setup mysqld so that I can get in the first time.....

Thanks for any help,
Mike
0
Comment
Question by:thready
  • 16
  • 4
  • 4
  • +2
27 Comments
 
LVL 1

Author Comment

by:thready
ID: 39810777
all the instructions I'm seeing are saying to connect as the root user... but that seems to not even be allowed....
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 126 total points
ID: 39810793
The 'root' user for MySQL is Not the 'root' user for the operating system.  To login as 'root' in MySQL, you normally have to be running locally, not remotely.  Which of course means you have to have logged into the local system first.
0
 
LVL 1

Author Comment

by:thready
ID: 39810796
I'm logged in over ssh.... is that ok?   This is a NAS drive - there's no other way (other than telnet)....
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 126 total points
ID: 39810802
That should be ok if everything else is running.  This page shows how to check the running services:  http://www.cyberciti.biz/faq/show-all-running-processes-in-linux/  If that works in your system, then 'ps -u mysql' should show whether 'mysqld' is running.  Which it needs to be to do anything with MySQL.  Even the command line 'mysql' is a front end for 'mysqld', the service daemon.
0
 
LVL 1

Author Comment

by:thready
ID: 39810804
mysqld is running without about 17 processes showing up.....
0
 
LVL 1

Author Comment

by:thready
ID: 39810806
i just can't get in with root to be able to create other users....
0
 
LVL 1

Author Comment

by:thready
ID: 39810811
I just tried logging in with telnet to see if it could somehow make a difference...  No luck
0
 
LVL 1

Author Comment

by:thready
ID: 39810814
oops - just reread something I wrote above - I meant to write:

"mysqld is running WITH about 17 processes showing up....."
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39810816
How are you trying to login to MySQL?
0
 
LVL 1

Author Comment

by:thready
ID: 39810819
on the command line - with mysql -u=root -p
0
 
LVL 1

Author Comment

by:thready
ID: 39810824
99% positive I'm entering the password I installed with....  Wish I was 100% positive.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39810840
Try it without a password.
0
 
LVL 1

Author Comment

by:thready
ID: 39810842
Tried that...
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 188 total points
ID: 39810850
There is root the Unix user and root the mysql user. They are different. You cannot log in as Unix user mysql - that user is only for the daemon. You can log in as any user and give mysql the mysql root password. If you have lost it I don't know how you recover.
0
 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 188 total points
ID: 39810859
The command line format that works for me is mysql -uroot -proot (Yes, mysql password for root is root. It's only a trial install).
0
 
LVL 1

Author Comment

by:thready
ID: 39810986
I tried "mysql -uroot -proot" - access denied again.

What did you mean by, "You can log in as any user and give mysql the mysql root password"?  I haven't been able to connect at all - please correct me if I'm wrong but I'm thinking if I don't get in as root, I won't be able to create any other users...

Thanks a lot,
Mike
0
 
LVL 34

Expert Comment

by:Duncan Roe
ID: 39811034
I meant your Unix user name can be anything. It's just the password on the mysql line that has to be right. You should substitute your MySQL root password for root in -proot
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 124 total points
ID: 39811067
Reset the root password for your Mysql installation.

1. kill mysqld (you find it's PID with ps)
2. start mysqld with /path/to/mysqld --skip-grant-tables
3. type mysql
4. type UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
5. type FLUSH PRIVILEGES;
6. type exit;
7. stop mysqld again
8. start mysqld normally
9. login into mysql with your new root password.

HTH,
Dan
0
 
LVL 1

Author Comment

by:thready
ID: 39813891
I did this line for line.  Everything worked.  Then when I tried to login with my password that surely is correct this time, I get the same result as before - access denied...  :o(
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 124 total points
ID: 39814142
OK, so when you type

mysql -uroot -p
and then type your password

you get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES),

or you get ERROR 1045 (28000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)?
0
 
LVL 1

Author Comment

by:thready
ID: 39814983
good question- I'll check as soon as I get in tonight!  :)
0
 
LVL 1

Author Comment

by:thready
ID: 39816449
'root'@'localhost'
0
 
LVL 34

Assisted Solution

by:Duncan Roe
Duncan Roe earned 188 total points
ID: 39819205
I tried the above instructions too, and they worked for me. But ... I checked exactly what /etc/rc.d/rc.mysqld actually did and followed that. First, as root
07:47:52# /etc/rc.d/rc.mysqld stop
07:48:29# /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid --skip-networking --skip-grant-tables &
[1] 1948

Open in new window

I don't like running stuff as root unnecessarily, so did the next bit as an ordinary user
07:52:29$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.7-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD('toor') WHERE User='root';
Query OK, 3 rows affected (0.75 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit;
Bye

Open in new window

As root, shut down this special invocation of the server and start it normally
07:54:20# /etc/rc.d/rc.mysqld stop
[1]+  Done                    /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid --skip-networking --skip-grant-tables
07:54:39# 
07:54:46# 
07:54:46# /etc/rc.d/rc.mysqld start

Open in new window


Check that the new password works
08:01:59$ mysql -uroot -ptoor  
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.7-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye

Open in new window

Yes I'm running MariaDB in place of MySQL, but it is meant to be a drop-in replacement and really is one as far as I can tell.
rc.mysqld.txt
0
 
LVL 1

Author Comment

by:thready
ID: 39820817
Sorry for the late response.  I'll get to it tonight, thanks for this!
0
 
LVL 2

Assisted Solution

by:Robert Silver
Robert Silver earned 62 total points
ID: 39988735
This question is looking abandoned.

Here is what I would do if I were you thready.
answer the following questions please:
How are you starting the mysqld_safe  or mysqld server?
What users on the Linux/ unix system are you able to login as?
Who runs your Linux/ Unix operating system?
Why can't you just request the system administrator of your Unix/Linux server just   reset the mysql user password  unless your system administrator left without giving out the root password  
you or someone in your company , a vendor should have this access. It may be  time to rebuild the unix/linux server  if you can not find an OS method for accessing root user
have no ability to contact the root user
if you do not have root access someone should  ,  a vendor support person or your system administrator with root access.
There a full way to reset the users table completely which I would gladly share with you
First I have to determine I am not wasting my time here as you have clearly not responded in over 2 months.  Assuming you are still out there and interested in your question being answered reply back and I will share my complete answer once you answer the questions I posed for you

I have already dealt with this problem of gaining access to mysql instances where mysql root user passwords are lost or the user table corrupted but it must have a OS mysql or root user who has full access to the mysql server.

Let me know assuming this question is moot now anyway which I suspect. Still you should assign points here or delete the question.
0
 
LVL 1

Author Comment

by:thready
ID: 39998143
I'm sorry this is taking so long.  I'm completely swamped on other projects and want to come back to this ASAP.  I'll write soon.
0
 
LVL 1

Author Closing Comment

by:thready
ID: 40005067
Sorry for the late reply everyone, I'm sure the solution is in here somewhere, but I'll need to start over.  The machine is in a really messy state.  Thanks a lot for your help - I will refer back to it when the fires subside...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
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.

760 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

20 Experts available now in Live!

Get 1:1 Help Now