Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

add MySQL user, can't connect

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
thready
Asked:
thready
  • 16
  • 4
  • 4
  • +2
8 Solutions
 
threadyAuthor Commented:
all the instructions I'm seeing are saying to connect as the root user... but that seems to not even be allowed....
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
threadyAuthor Commented:
I'm logged in over ssh.... is that ok?   This is a NAS drive - there's no other way (other than telnet)....
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Dave BaldwinFixer of ProblemsCommented:
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
 
threadyAuthor Commented:
mysqld is running without about 17 processes showing up.....
0
 
threadyAuthor Commented:
i just can't get in with root to be able to create other users....
0
 
threadyAuthor Commented:
I just tried logging in with telnet to see if it could somehow make a difference...  No luck
0
 
threadyAuthor Commented:
oops - just reread something I wrote above - I meant to write:

"mysqld is running WITH about 17 processes showing up....."
0
 
Dave BaldwinFixer of ProblemsCommented:
How are you trying to login to MySQL?
0
 
threadyAuthor Commented:
on the command line - with mysql -u=root -p
0
 
threadyAuthor Commented:
99% positive I'm entering the password I installed with....  Wish I was 100% positive.
0
 
Dave BaldwinFixer of ProblemsCommented:
Try it without a password.
0
 
threadyAuthor Commented:
Tried that...
0
 
Duncan RoeSoftware DeveloperCommented:
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
 
Duncan RoeSoftware DeveloperCommented:
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
 
threadyAuthor Commented:
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
 
Duncan RoeSoftware DeveloperCommented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
threadyAuthor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
threadyAuthor Commented:
good question- I'll check as soon as I get in tonight!  :)
0
 
threadyAuthor Commented:
'root'@'localhost'
0
 
Duncan RoeSoftware DeveloperCommented:
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
 
threadyAuthor Commented:
Sorry for the late response.  I'll get to it tonight, thanks for this!
0
 
Robert SilverCommented:
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
 
threadyAuthor Commented:
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
 
threadyAuthor Commented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 16
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now