asked on 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
[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]#
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
[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]#
MySQL Server
Last Comment
jimyX
ASKER
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 ~]#
[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 ~]#
ASKER
[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'
ERROR 1142 (42000): UPDATE command denied to user 'lac_exchange'@'localhost'
mysql>
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'
ERROR 1142 (42000): UPDATE command denied to user 'lac_exchange'@'localhost'
mysql>
ASKER
another try, I created a mysql-init file with the following two lines:
UPDATE mysql.user SET Password=PASSWORD('MyNewPa
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
[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
UPDATE mysql.user SET Password=PASSWORD('MyNewPa
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
[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
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.
ASKER
got it resolved, thank you very much.
ASKER
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/h
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';
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/h
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';
> line 167 of /var/www/staging/lac2014/h
Did you try to perform any successful operation with the user "lac2014" on "lac2014_staging" after the reset?
Have you done "FLUSH PRIVILEGES;"?
Did you try to perform any successful operation with the user "lac2014" on "lac2014_staging" after the reset?
Have you done "FLUSH PRIVILEGES;"?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.