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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.