mySQL - Reset Root Password:

[last updated: 2021-05-12]
go to: mySQL home page:
-----

As first installed, the password for user root is blank, such that a <CR> or any characters followed by a <CR> will log you in.
However a forum note said not all super-user privileges are available in this mode, and that you must change the password to have full functionality.
This page is a bunch of procedures from the web. Many of them I tried, none of them successful. Still trying things...
-----------------
Contents on this page:
- General Procedure:
- procedure from howtoforge & rackspace:
- procedure from processio.com:
- procedure from linuxhomenetworking.com:
- procedure from stackoverflow.com 46087411:
- procedure from ubuntu.com/community/MysqlPasswordReset
  [ubuntu bookmark]
- Other forum suggestions/tips:
- Links:
-----------------
General Procedure:

Stop mysqld server
start in safe mode or with --skip-grant-tables
log into mysql with root
set new password
flush privileges and exit mysql
stop and restart mysqld server

-----------------

Procedure from howtoforge and rackspace:

These instructions are given in context of someone having lost their root user login password:
Step # 1: Stop the MySQL server process:
$ service mysql stop
enter Linux password. worked
OR: $ sudo /etc/init.d/mysql stop
OR: /usr/bin# ./mysqladmin -u root -p shutdown
enter mysql password ([CR]) - worked
Step # 2: Start MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for a password:
$ sudo /usr/bin/mysqld_safe --skip-grant-tables &
this supposedly worked at least once, but later I could not repeat it.
It gave an error:
"Directory '/var/run/mysqld' for UNIX socket file don't exists."
[maybe should be: $ sudo /usr/bin/mysqld_safe ... which does BTW exist...]
Step # 3: Connect to the MySQL server as the root user:
$ mysql -u root
prompt will now be: mysql>
OR: $ mysql
Step # 4: Set a new root password:
mysql> use mysql;
mysql> update user set password=PASSWORD("newpassword") where User='root';
OR: mysql> update user set authentication_string=PASSWORD("newpassword") where User='root';
mysql> flush privileges;
mysql> quit
Step # 5: Exit and restart the MySQL server:
$ service mysql stop
OR: $ killall mysqld
OR: $ sudo /etc/init.d/mysql stop
now restart:
$ sudo /etc/init.d/mysql start
OR: $ service mysql start
now test:
$ mysql -u root -p
OR: $ mysql -u root -p
[end howtoforge & rackspace procedure]

----------------------------

Procedure from processio.com:

$ sudo mkdir /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
$ sudo service mysql stop
$ mysqld_safe --skip-grant-tables &
mysql -uroot mysql
mysql> UPDATE mysql-dot-user SET authentication_string=PASSWORD('[newPassword]'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
mysql> exit;
$ sudo mysqladmin -S /var/run/mysqld/mysqld-dot-sock shutdown
$ sudo service mysql start
This completes the procedure, but still didn't work, that is, I still could not log into phpmyadmin as root with the new pw (jjjjtttt)
-----
so from same link, an alt procedure:
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Under [mysqld] add:
skip-grant-tables
[previously I had similarly edited /etc/mysql/conf.d/mysql.cnf]
$ sudo service mysql stop
$ sudo service mysql start
$ sudo service mysql restart ... ???
$ sudo mysql -uroot mysql
mysql> update ... (use query above...)
mysql> exit
Edit mysqld.conf again, remove the line, then restart MySQL:
$ sudo service mysql restart
However: still can't log into phpmyadmin with root...
[end processio.com procedure]

----------------------------

Procedure from linuxhomenetworking.com:

$ sudo service mysql stop
$ sudo su
# mysqld safe --skip-grant-tables &
response: "[1] 18431"
# Starting mysqld daemon with databases from /var/lib/mysql
response:
[maybe this is a typo in tutorial and wasn't actually supposed to be executed as a command?]
$ mysqld_safe --skip-grant-tables
errors...
[end linuxhomenetworking.com procedure]

----------------------------

Procedure from stackoverflow 46087411:

don't have: /etc/my.cnf file
I do have: /etc/mysql/conf.d/mysql.cnf ... that has one line: "[mysql]"
so I edited /etc/mysql/conf.d/mysql.cnf and added the line: skip-grant-tables

... continuing from forum ...
"Restart mysql (/etc/init.d/mysqld restart)"

... continuing from forum ...
"Start mysql (type mysql directly)"

... continuing from forum ...
Change the password of user(root):
ALTER USER 'root'@'localhost'IDENTIFIED BY 'newpassword'
Exit mysql and delete the skip-grant-tables in /etc/my.cnf
Then you can use mysql -u root -p to login the mysql now."

[end stackoverflow procedure]

-----------------------------

Procedure from ubuntu.com/community/MysqlPasswordReset:
- it says do:
  $ sudo /etc/init.d/mysql stop ... worked ...
  $ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
  and in fact I do have a /usr/sbin/mysqld ... so:
  $ sudo /usr/sbin/mysqld --skip-grant-tables & ... (don't need the networking option)
  ...
  but I didn't finish this trial ...
  -------------------
- OTOH. The last part of this link has a last-resort purge and reinstall method:
  This procedure is edited from orig at ubuntu for things that worked for me:
  - $ sudo service mysql stop
    $ dpkg --get-selections | grep mysql ... gives a list of all the modules installed that contain with 'mysql'
    The ones of concern are mysql-server, mysql-client, and mysql-common
    On my tower system there were 3@ of -server and -client programs, and one -common
    I removed them one at a time with: $ sudo apt-get --purge remove [programName]
    in order: -client, -client-5.7, -client-core-5.7, -server, -server-5.7, server-core-5.7, and -common
  - When -server-5.7 was removed, I elected to remove all mysql databases
  - OTOH, the Linux Software Manager shows -client-5.7 and -client-core-5.7 still installed (all -server modules are in fact gone from Software Mgr)
    manually removed them both in Software Mgr
  - $ sudo apt-get install mysql-server
    dpkg confirmed -server, -server-5.7, and -server-core-5.7 all installed
    mysql-server installed version: 5.7.28-0ubuntu0.18.04.4
  - $ sudo apt-get install mysql-client
    dpkg confirmed -server, -server-5.7, -server-core-5.7, and -common all installed
    (though I didn't notice if -common was installed with -client or with -server...)
    mysql-client installed version: 5.7.28-0ubuntu0.18.04.4
  - $ ps -ef | grep mysqld ... shows mysqld is running
  - $ sudo mysqladmin -u root password jrttt
    executed with security warning
  - $ sudo /etc/init.d/mysql restart ... executed successfully
  - $ sudo mysql -u root -p ... logs me in with simple [CR] instead of the 'jrttt' password I set up...
  - Cannot log into phpmyadmin with user: phpmyadmin and pw: tripT (still listed in /etc/dbcong-common/phpmyadmin.conf)
  - Uninstalled phpmyadmin in Software Mgr
    reinstalled and launched ... same result
  - Duh. There's no phpmyadmin user defined in mysql anymore. Probably didn't need to reinstall it...
  - $ sudo mysql -u root
    Create a phpmyadmin database:
    mysql> create database phpmyadmin;
    Create user: phpmyadmin with full privileges to the phpmyadmin database:
    mysql> grant all privileges on phpmyadmin.* to phpmyadmin@"localhost" identified by 'tripT';
    mysql> flush privileges;
    Success: Logged into localhost/phpmyadmin with user: phpmyadmin and pw: tripT
    However, user: root and pw: jrttt still fails
  - Now back to where I was during original local server install, when I could get into phpmyadmin, but selecting databases, the warning said 'no privileges' for creating new databases.
  - stop & restart mysql ... no change
  - $ sudo dpkg-reconfigure phpmyadmin ... no change
  - sudo mysql -u root
    mysql> grant all privileges on *.* to root2@"localhost" identified by 'root2' with grant option;
  - Success: at least a workaround, since I still can't get in with 'root', but now I can get in with root2 and indeed it will allow me to create db's
  - But now I will try to re-create user 'root' with a password:
    $ sudo mysql -u root2 -p ... enter password 'root2'
    mysql> grant all privileges on *.* to root@"localhost" identified by 'root' with grant option;
    Executed without error, but no change:
    $ sudo mysql -u root -p ... still accepts a [CR] to log in
    and localhost/phpmyadmin still gives error 1698 with user: root, pw: root
  - Tried similarly with user: phpmyadmin:
    mysql> grant all privileges on *.* to phpmyadmin@"localhost" identified by 'tripT' with grant option;
    Didn't work
    $ sudo dpkg-reconfigure phpmyadmin ... no change
- [end ubuntu.com procedure]
------------------------------
Other forum suggestions/tips:

"To change the root's password, you can run mysql_secure_installation (link to:) mariaDB.com
"The Easiest Method:
$ sudo dpkg-reconfigure mysql-server-N.N
(where N.N is the MySql Server version - 5.7 for me currently)

-----------------------------

Links:
----------------------------

1068 another procedure done per google search...
Solved: phpmyadmin #1698 - Access denied for user 'root'@'localhost'
Mysql tries to authenticate root using plugin, not password. You need to disable plugin usage for root.

shell$ sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q

----------
then got:
1045 - Access denied for user 'root'@'localhost' (using password: YES)
from: (link to:) OStraining - phpMyAdmin error #1045
however line in config.inc.php that it wanted me to edit doesn't exist in my installation...

.

.

.

eof

Main Menu

Main menu

Site Menu

You are here

mySQL - Reset Root Password: