how to reset root password for mysql database

I have an existing web server with mysql database installed. There are four databases on it, I have all four database's owner's username and passwords. However, I don't have the root password so that I couldn't create new users for each database and assign privileges. I tried this article to reset the root password but failed.

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Could anyone tell me how to reset it?

thanks.






[root@dorrington init.d]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i686) using readline 5.1
[root@dorrington init.d]#  /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@dorrington init.d]#  /etc/init.d/mysqld status
mysqld is stopped
[root@dorrington init.d]#  /etc/init.d/mysqld --skip-grant-tables
Usage: /etc/init.d/mysqld {start|stop|status|restart|condrestart|try-restart|reload|force-reload}
[root@dorrington init.d]# vi mysqld
[root@dorrington init.d]# cp mysqld /tmp
[root@dorrington init.d]# chmod 775 /tmp/mysqld
[root@dorrington init.d]#
Jason YuAsked:
Who is Participating?
 
jimyXCommented:
> Is the account defined in this table a generally global account for all the databases on this mysql server?

You make the account global by giving the proper privileges with "Grant".

From your other thread I just realized you did not specify any tables of the DB "lac2014_staging" to be accessible by the user "lac2014".

Do as follows:
mysql>  GRANT ALL PRIVILEGES ON lac2014_staging.* TO 'lac2014'@'localhost';
0
 
jimyXCommented:
Can you tell which method you tried, and what result did you get?

Also make sure to read the comments at the bottom, some practical and helpful info. by others who faced similar issues in the area of the article would be mentioned.
0
 
Jason YuAuthor Commented:
I ran the following two commands, and don't know how to continue.

[root@dorrington ~]#  /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@dorrington ~]#  /etc/init.d/mysqld start --skip-grant-tables
Starting mysqld:                                           [  OK  ]
[root@dorrington ~]#
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jason YuAuthor Commented:
[root@dorrington ~]# mysql -u lac_exchange  -h localhost  -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> UPDATE mysql.user SET Password=PASSWORD('lacare') WHERE User='root';
ERROR 1142 (42000): UPDATE command denied to user 'lac_exchange'@'localhost' for table 'user'
mysql>
0
 
Jason YuAuthor Commented:
another try, I created a mysql-init file with the following two lines:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;


Then tried to start up mysql using this initiate file. But it failed to start the server.


[root@dorrington mysqld]# mysqld_safe --init-file=/home/me/mysql-init &
[1] 20234
[root@dorrington mysqld]# 150322 20:39:21 mysqld_safe Logging to '/var/log/mysqld.log'.
150322 20:39:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150322 20:39:26 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
0
 
jimyXCommented:
I ran the following two commands, and don't know how to continue.

[root@dorrington ~]#  /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@dorrington ~]#  /etc/init.d/mysqld start --skip-grant-tables
Starting mysqld:                                           [  OK  ]
[root@dorrington ~]#

After this you just update the password, then stop mySQL and restart normally by entering the new password.
Read this link and follow the steps as described.
0
 
Jason YuAuthor Commented:
got it resolved, thank you very much.
0
 
Jason YuAuthor Commented:
I then got this error, what this error mean:

PDOException: SQLSTATE[42000] [1044] Access denied for user 'lac2014'@'localhost' to database 'lac2014_staging' in lock_may_be_available() (line 167 of /var/www/staging/lac2014/html/includes/lock.inc)...



I successfully ran this command to give user "lac2014" full privilege to "lac2014_staging" database already.


mysql>  GRANT ALL PRIVILEGES ON lac2014_staging TO 'lac2014'@'localhost';
0
 
jimyXCommented:
> line 167 of /var/www/staging/lac2014/html/includes/lock.inc

Did you try to perform any successful operation with the user "lac2014" on "lac2014_staging" after the reset?

Have you done "FLUSH PRIVILEGES;"?
0
 
Jason YuAuthor Commented:
yes, I did "flush privileges"

This "lac2014" was defined in user table in mysql database. Is the account defined in this table a generally global account for all the databases on this mysql server?

thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.