Solved

add MySQL user, can't connect

Posted on 2014-01-26
27
538 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 83

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 83

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 83

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 83

Expert Comment

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

Author Comment

by:thready
ID: 39810842
Tried that...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Fine Tune your automatic Updates for Ubuntu / Debian
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

911 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

18 Experts available now in Live!

Get 1:1 Help Now