Reset MySQL database root password in Ubuntu 16.04

Posted by: mah 7 years, 7 months ago

(0 comments)

Standart method of reseting password in Ubuntu 16.04 not working as usualy describe in WEB

- Ubuntu 16.04

- Mysql server 5.7.17 (after update).

dpkg-reconfigure mysql-server 5.7   is not working - do nothing

Need to do something magic.

Steps for reset root password:

It takes few minutes or less

Step 1:

stop Mysql service

$sudo /etc/init.d/mysql stop

Step 2:

Start mysqld process with option  --skip-grant-tables  --skip-networking

$mysqld_safe --skip-grant-tables --skip-networking &
[1] 5728
root@exim:/var/run/network# 2017-04-21T21:11:39.353719Z mysqld_safe Logging to syslog.
2017-04-21T21:11:39.357852Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:11:39.362554Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:11:39.366645Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

[1]+ Exit 1 mysqld_safe --skip-grant-tables --skip-networking
 

What????

try again with no '&'

$sudo mysqld_safe --skip-grant-tables --skip-networking
2017-04-21T21:14:21.092023Z mysqld_safe Logging to syslog.
2017-04-21T21:14:21.095954Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:14:21.100310Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:14:21.104173Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

create dir for that:

sudo mkdit /var/run/mysqld

and give permissions:

$sudo chown mysql.mysql /var/run/mysqld

and try again start process:

You can start with no '&' just for make sure everything is OK and see output in console

$sudo mysqld_safe --skip-grant-tables --skip-networking
2017-04-21T21:19:34.770008Z mysqld_safe Logging to syslog.
2017-04-21T21:19:34.773829Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:19:34.778261Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-04-21T21:19:34.801799Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Good, 

check if process started:

$ ps ax |grep mysql
 7287 pts/1 S+ 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking
 7671 pts/1 Sl+ 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=

Step 3:

open next terminal for reseting password

$mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2016, 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> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');
Query OK, 0 rows affected, 1 warning (0.01 sec)
Ctrl+D
$

Now need restart mysql in normal node

$sudo /etc/init.d/mysql stop    .. not working

Step 4:

try hard kill process:

$ps ax |grep mysql
17842 pts/1 S+ 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking
18226 pts/1 Sl+ 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=
$sudo kill -9 17842
$ps ax |grep mysql
18388 pts/5 S+ 0:00 grep --color=auto mysql
$
$sudo systemctl start mysql
$

Done

• Tags:
• Mysql,
• reset password

• ← Spamassassin
• Configure IPMITOOL remotely over ssh →