Recently I was testing MariaDB database server on Ubuntu 17.10 / 18.04 and discovered that MariaDB database server now installs on Ubuntu without prompting the root user for password to access the server.
Is this new?
It’s always been the case where MySQL and MariaDB, a fork of MySQL prompt for passwords everytime before access is granted to the server. Apparently, not anymore for MariaDB. Now simply installing the database gives the root access without password.
Even after running the command sudo mysql_secure_installation. the root account password is never required. However, other applications and services that depend on MariaDB will fail if the root password is needed for authentication.
phpMyAdmin and MySQL Workbench database may fail if MariaDB is setup this way.
This brief tutorial is going to show students and new users how to set a root password for MariaDB and allow password authentication.
After digging a bit, I discovered that MariaDB uses unix_socket plugin to authenticate. and not passwords. Even if you set a password, it is ignored. To re-enable password authentication, follow the steps below:
Logon to MariaDB server by running the commands below
sudo mysql -u root
Notice no password?
That should get you into the database server. After that, run the commands below to disable plugin authentication for the root user
use mysql; update user set plugin='' where User="root"; flush privileges; exit
Restart and run the commands below to set a new password.
sudo systemctl restart mariadb.service
After that, run the commands below to secure MariaDB server and create a new root password.
sudo mysql_secure_installation
When prompted, answer the questions below by following the guide.
- Enter current password for root (enter for none): Just press Enter
- Set root password? [Y/n]: Y
- New password: Enter password
- Re-enter new password: Repeat password
- Remove anonymous users? [Y/n]: Y
- Disallow root login remotely? [Y/n]: Y
- Remove test database and access to it? [Y/n]: Y
- Reload privilege tables now? [Y/n]: Y
You should now be able to logon with password authentication. and other applications should now work with the root password authentication.
The next time type the commands below to logon
sudo mysql -u root -p
Then type the password to sign on
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.1.25-MariaDB-1 Ubuntu 17.10 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Enjoy!
You may also like the post below:
