After installing mysql/mariadb the root password is not working, even after executing the secure_installation
script. I followed these steps on a Ubuntu server 18.08 and seems to work there.
sudo su
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -uroot
This will completely stop mysql, bypass user authentication (no password needed) and connect to mysql with user "root".
Now, in mysql console, go using mysql administrative db:
use mysql;
update user set password=PASSWORD("mynewpassword") where User='root';
update user set plugin="mysql_native_password";
quit;
Depending on the mysql installation the password
column might not be available, you can change the password with:
use mysql;
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
update user set plugin="mysql_native_password";
quit;
This one will overwrite authentication method, remove the unix_socket request (and everything else), restoring a normal and working password method
Stop and start everything related to mysql:
/etc/init.d/mysql stop
kill -9 $(pgrep mysql)
/etc/init.d/mysql start
exit;
You should be able to login normally by:
mysql -u root -p
EOF