MariaDB installed without password prompt
sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q
This needs to be followed by following command
# mysql_secure_installation
it is common for root to have password-less access if accessing from localhost, I recommend this setting to be left alone.
I also recommend that you create a user with less permissions and allow that user to login remotely.
create user my_admin identified by '12345';
create database my_database;
grant all on my_database.* to my_admin;
This way you have a little more security.
If you do need to connect as root from a tool like workbench, you can configure those tools to create an ssh tunnel and connect to the database as localhost.
Starting with MariaDB 10.4 root@localhost
account is created with the ability to use two authentication plugins:
- First, it is configured to try to use the
unix_socket
authentication plugin. This allows theroot@localhost
user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating systemroot
user account.- Second, if authentication fails with the
unix_socket
authentication plugin, then it is configured to try to use themysql_native_password
authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set withSET PASSWORD
.
That is why you don't need a password to login on a fresh install.
But then another quote:
When the plugin column is empty, MariaDB defaults to authenticating accounts with either the
mysql_native_password
or themysql_old_password
plugins. It decides which based on the hash used in the value for the Password column. When there's no password set or when the 4.1 password hash is used, (which is 41 characters long), MariaDB uses themysql_native_password
plugin. Themysql_old_password
plugin is used with pre-4.1 password hashes, (which are 16 characters long).
So setting plugin = ''
will force it to use password based authentication. Make sure you set a password before that.
sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q