Solve authentication issue on mariadb

When installing Mariadb on Ubuntu I always have the problem of root  password not set and authentication plugin set to unix_socket. The  commands below solve the issues.

# change to root
sudo -s

# stop mariadb server
systemctl stop mariadb

# start mariadb in safe mode
mysqld_safe --skip-grant-tables --skip-networking &

# log into mariadb without password
mysql -u root
# change to mysql db
USE mysql;

# Change the root password
UPDATE user SET password=PASSWORD("new_password_here") WHERE User='root';

# Set the authentication plugin to default
UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';
Reset privileges
FLUSH privileges;

# Exit from Mariadb
# Restart Mariadb server
systemctl stop mariadb
systemctl start mariadb

After this you should be able to log into mysql with your root password. Tested on Ubuntu 17.10 & 18.04

Leave a Reply

Your email address will not be published. Required fields are marked *