0

i was install mariaDB 10.1 in Ubuntu16.04LTS

i use this code.

sudo apt-get install mariadb-server

install is success, but when i connect to mariadb, it is don't need password!

i was try this. but these all doesn't work.

  1. i use mysql_secure_installation command and finish configuration successfully. but it is still don't need password.
  2. SET PASSWORD FOR 'root'@'%' = PASSWORD('newpass'); and flush privilege
  3. update user set password=password('newpass') where user='root';

i want use password. what should i do?

YeongCheon Kim
  • 31
  • 5
  • `SELECT user, host, password FROM mysql.user;` -- may give clue of what is wrong. – Rick James Aug 28 '16 at 16:52
  • Looks like this is the same issue as this one: http://stackoverflow.com/a/30417831/6451083 MariaDB 10.0.17 cannot access root with non-root user. – ludofet Sep 08 '16 at 18:05

1 Answers1

2

My answer to "i want use password. what should i do?" is:

"no, you probably don't!" (depending on your app's deployment - see comments below)

With Ubuntu 15.10 and later MariaDB installs with the unix_socket user authentication plugin enabled by default: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/

With a fresh install login with sudo mysql -uroot and execute:

SELECT User, Password, Host, plugin from mysql.user WHERE user = 'root';

You should see a single user. This also means you cannot remotely login as root with the default install (one of the tasks the mysql_secure_installation performs).

+------+----------+-----------+-------------+
| User | Password | Host      | plugin      |
+------+----------+-----------+-------------+
| root |          | localhost | unix_socket |
+------+----------+-----------+-------------+

I would recommend leaving this as-is and creating a non-root user with the appropriate permissions for your application to use.

However, if you absolutely need a password-authenticated root user you need to change the plugin to mysql_native_password.

UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root';
FLUSH PRIVILEGES;

Now quit then log-in with mysql -uroot -p.

Note if you need to restart the service you will need to do three commands instead of just sudo systemctl restart mysql. I am pretty sure there is some addition to a .cnf file you can make so that restart will work as expected but I couldn't easily figure it out. Basically the lesson is use unix_socket.

sudo systemctl stop mysql
sudo kill -9 $(pgrep mysql)
sudo systemctl start mysql
Josh Morel
  • 1,201
  • 12
  • 17
  • 1
    Note that attempting to use the unix socket authentication breaks a number of applications with SSH tunneling. It may actually, still, be a better idea overall to use mysql native password authentication just so you can bury the entire server behind a firewall and tunnel into it. – damianb Feb 27 '18 at 23:10
  • Good point @damianb. I guess it depends on what application deployment (above is perfect for co-located Wordpress, Nextcloud etc). – Josh Morel Feb 28 '18 at 13:46