Authentication in MariaDB 10.4 (MariaDB 10.4 root账号密码问题)

昨天更换工作电脑，重装了一下ubuntu下的mariadb，ubuntu默认源下的mariadb 是10.0 版本，默认charset 是utf8mb4, 使用sequelize不好解决Index column size too large. The maximum column size is 767 bytes.限制的问题，遂去官网换了10.4版本。结果安装好了又怎么都登录不上去，用mysqld_safe --skip-grant-tables进去设置root密码，又碰到Column 'Password' is not updatable，无法修改密码，无奈只好去官网看看是不是又出幺蛾子了，一找还真是有一篇文章讲这个问题，现把原文摘录贴过来备忘。

Authentication in MariaDB 10.4 — Understanding the Changes (original link)

In a nutshell (简要)

The password storage has changed. All user accounts, passwords, and global privileges are now stored in a mysql.global_priv table. What happened to the mysql.user table? It still exists and has exactly the same set of columns as before, but it’s now a view over mysql.global_priv. If you happen to have tools that analyze mysql.user table — they should continue working as before.

为什么改不了密码这里说的很清楚了，mysql.user表不见了，现在它只是mysql.global_priv表的一个视图。所以严格来说也不是不能改密码，只是不能修改原来的mysql.user表了。

One can specify more than one authentication method per account. They all will work as alternatives. For example, a DBA might start migrating users to the more secure ed25519 password plugin, but keep the old SHA1 one as an alternative for the transitional period.

mariadb 10.4 可以给用户设置多种认证方式了，这里有需要的话再看。

The default authentication for new installations is now more secure. The open-for-everyone all-powerful root account is gone, at last. And installation scripts will no longer shout at you “PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !”, because the root account is created secure automatically.

很好很直接，root密码没了， 也不需要你去设置初始密码了，而且说是这样更安全，什么黑科技？看下面。

Details

Technically, a new MariaDB installation will have two all-powerful accounts — root and the OS user that owns the data directory, typically mysql. They are created as

1
2

CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'
CREATE USER mysql@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'

Using unix_socket means that if you are the system root user, you can login as root@locahost without a password. This technique was pioneered by Otto Kekäläinen in MariaDB packages in Debian as early as MariaDB 10.0. It is based on a simple fact, that asking the system root for a password adds no extra security — root has full access to all the data files and all process memory anyway. But not asking for a password means, there is no root password to forget (bye-bye numerous tutorials “how to reset MariaDB root password”). And if you want to script some tedious database work, there is no need to store the root password in plain text for the scipt to use (bye-bye debian-sys-maint user).

mariadb 10.4安装时创建了2个默认账号: root，mysql。初始只提供了unix_socket方式认证。这样登录mysql你就不需要mysql -uroot -p了， 直接sudo mysql，只要你有系统root权限就可以进去。

Still, some users complained that they want to log in as MariaDB root without using sudo. This is why in 10.4 the root user has a second authentication method — conventional MariaDB password. By default it is disabled (“invalid” is not a valid password hash), but one can set the password with a usual SET PASSWORD statement. And still retain the password-less access via sudo!

如果你不想用sudo登录mysql, mariadb 10.4依然提供了传统的密码登录的方式，只是初始是禁用的(invalid)。你可以登录进mysql用SET PASSWORD的方式设置密码。

Now, what happens, if you install MariaDB locally (for example, from a tarball)? You definitely would not want to use sudo to be able to login. This is why MariaDB creates a second all-powerful user with the same name as a system user that owns the data directory. In local (not system-wide) installations, this will be the user, who installed MariaDB — she automatically gets convenient password-less root-like access, because, frankly, she can access all the data files anyway.

And even if MariaDB is installed system-wide, you may not want to run your database maintenance scripts as system root — now you can run them as system mysql user. And you will know, that they will never destroy your entire system, even if you make a typo in a shell script.

mariadb 10.4 在linux创建了mysql用户，所有数据文件都在mysql用户下面，所以它在数据库里也一并创建了unix_socket认证的mysql 用户，这样你可以不在用root和sudo的情况下通过mysql用户无密码操作数据库，而且也不用担心rm -rf不小心删除系统的问题，这对于脚本操作非常有用。

Cookbook

（下面就不翻译或解释了)

“This is all great”, you may be thinking, “but I’m a seasoned MariaDB DBA, I can write SQL in my sleep, do I need to do something different from now on”? Unfortunately, yes.

After installing MariaDB system-wide the first thing you’ve got used to doing is logging in into the unprotected root account and protecting it, that is, setting the root password:

1
2
3
4

$ sudo dnf install MariaDB-server
$ mysql -uroot
...
MariaDB> set password = password("XH4VmT3_jt");

This is not only unnecessary now, it will simply not work — there is no unprotected root account. To login as root use

1
2

$ sudo dnf install MariaDB-server
$ sudo mysql

Note that it implies you are connecting via the unix socket, not tcp. If you happen to have protocol=tcp in a system-wide /etc/my.cnf file, use sudo mysql --protocol=socket.

After installing MariaDB locally you’ve also used to connect to the unprotected root account using mysql -uroot. It will not work either, use simply mysql without specifying a username.

You want passwords back, no unix_socket authentication anymore? Run

1

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret")

Forgot your root password? No problem — you can still connect using sudo and change the password. Oh, you have also removed unix_socket authentication? In that case, do as follows:

1. restart MariaDB with --skip-grant-tables
2. login into the unprotected server
3. run FLUSH PRIVILEGES (note, before 10.4 it would’ve been the last step, not anymore)
4. run SET PASSWORD FOR root@localhost to change the root password

You want to peek inside privilege tables? Old mysql.user table still exists, you can select from it as before, although you cannot update it anymore. It doesn’t show alternative authentication plugins? Yes, this was one of the reasons for switching to mysql.global_priv table — complex authentication rules did not fit into rigid structure of a relational table. But you can, of course, select from the new table too. For example, with

1

select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;

This is it. Remember, the best way to keep your password safe is not to have one. And have fun!