问题：
困扰了很久的问题，，
使用apt-get来安装mysql，安装好之后发现安装的是 MariaDB，如下，无需密码既可以登录了。即使使用mysqladmin设置好密码，用密码登录可以，不用密码登录也可以
来自 https://www.cnblogs.com/Dicky-Zhang/p/8000584.html 的解决办法，好像没有用，改成 ‘mysql_native_password‘ 也能登录
sudo service mysql stop sudo mysqld_safe --skip-grant-tables 进去mysql执行如下命令： MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD(‘mypassword‘), plugin = ‘mysql_native_password‘ WHERE User = ‘root‘ AND Host = ‘localhost‘; MariaDB [(none)]> FLUSH PRIVILEGES; 验证： MariaDB [(none)]> select user, plugin from mysql.user -> ; +------+-----------------------+ | user | plugin | +------+-----------------------+ | root | mysql_native_password | +------+-----------------------+ 1 row in set (0.01 sec) 先杀死mysql kill -9 pid 启动： sudo service mysql start
看了 https://*.com/questions/44298160/mysql-mariadb-10-0-29-set-root-password-but-still-can-login-without-asking-p 这个回答也没有结果
SET PASSWORD FOR ‘root‘@‘localhost‘ = PASSWORD(‘MyNewPass‘); UPDATE mysql.user SET plugin = ‘‘ WHERE user = ‘root‘ AND host = ‘localhost‘; FLUSH PRIVILEGES;
直到看见-------------------------------------------> Authentication in MariaDB 10.4 — Understanding the Changes
MariaDB Server 10.4 came with a whole lot of Security related changes. Some of them are merely optimizations (like MDEV-15649), some improve existing features to be more robust (MDEV-15473, MDEV-7598) or convenient (MDEV-12835, MDEV-16266). Some are MySQL compatibility features, requested by our users (MDEV-7597, MDEV-13095). But the first thing any MariaDB Server user, whether an experienced veteran or a newbie, does — before even issuing the first SQL statement — is logging in. Authenticating to the database server. And 10.4 brings changes to this process too. If you are a new user, you will hopefully find MariaDB Server easier and more intuitive to use with less struggling over passwords. But if you have used MariaDB Server for a while, the new behavior might feel at times baffling. This post explains what has changed and what MariaDB Server is doing now. 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. 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. 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. 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 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). 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! 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. 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: $ 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 $ 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 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: restart MariaDB with --skip-grant-tables login into the unprotected server run FLUSH PRIVILEGES (note, before 10.4 it would’ve been the last step, not anymore) 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 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!
不要惊讶，不要慌张，user表变成了view，最好使用global_priv进行修改
重要的帖子
https://mariadb.org/authentication-in-mariadb-10-4/
No root user password for MariaDB
For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command.
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘new_password‘;
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:
SET PASSWORD FOR ‘root‘@‘localhost‘ = PASSWORD(‘new_password‘);Basically, you can connect by root from localhost from root shell without password.
If you don‘t known root password, there is a way to start mysql without using mysql db, where users and passwords stored. Run each command with sudo, except mysql console commands:
# service mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
use mysql;
update user set password=PASSWORD(‘NEWPASSWORD‘) where User=‘root‘;
flush privileges;
exit;
# service mysql restart
Now you can login with new password: # mysql -u root -p
Update: Its better not change password for root, but create new user with all privileges who can connect only from localhost. When in mysql console do following:
CREATE USER ‘newuser‘@‘localhost‘ IDENTIFIED BY ‘password‘;
GRANT ALL PRIVILEGES ON * . * TO ‘newuser‘@‘localhost‘;
If you need external connections to mysql - limit it by hosts and/or don`t grant all privileges and limit to only required databases(the * in above example).
reflink: https://askubuntu.com/questions/1091548/no-root-user-password-for-mariadb
How to login in MariaDB with OS user without password
What is the solution if I don’t want to give password in command line (i.e mysql -uroot -p ) OR don’t want to store password in files(in .my.cnf) and still can login into MySQL/MariaDB without password ? I was also bit curious to know but finally I found very easy solution called “unix_socket plugin” provided by MariaDB.
This plugin allows to use operating system user credentials while connecting to MariaDB via Unix socket. When we try to connect with OS user, it will retrieve uid of the process which has connected to the socket and allow it to connect to MariaDB with the same user.
You can simply install that plugin with command,
MariaDB [(none)]> INSTALL PLUGIN unix_socket SONAME ‘auth_socket‘;
Query OK, 0 rows affected (0.02 sec)
After, then you need to identify the user which you want to use to login into MariaDB. Like for me,
[nil@centos68 ~]$ whoami
nil
[nil@centos68 ~]$
Now, create user in MariaDB with whatever required privileges.
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘nil‘ IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user, host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| nil | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)
and try to login with only username
[nil@centos68 ~]$ mysql -unil
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.5-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> show grants;
+---------------------------------------------------------------------+
| Grants for nil@% |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘nil‘@‘%‘ IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
Keep in mind that nil user is already authenticated by operating system so when it tries to login into database, it didn’t need password. If you’ll try to login with another OS user then it will not work even if you have added in MariaDB server.
Actually, this plugin can help in many scenarios where you don’t want to store user password in readable format. Like, you want to run some automated script or backup tool or mysql client on local server and your security policy says, you can’t save password, you can use this plugin. This way is really very secure because password will not be store in plain text files which is mostly unprotected way to store password and can be compromised.
Also, some people like me ?? alway poor to remember password, can take the advantage by allowing their own OS user accounts to authenticate with the database server.
reflink: http://www.nilinfobin.com/mysql/how-to-login-in-mariadb-with-os-user-without-password/
Use MySQL Without a Password (And Still Be Secure)
Some say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.
Neither of these plugins is new, but while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them, because, in MariaDB 10.4, a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).
You may also like: You Should Be Aware of These 10 Most Prevalent MySQL Mistakes.
As already mentioned, this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created, so it uses the socket authentication. This is true for both MySQL and MariaDB:
root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Using the Debian packages of MySQL, the root is authenticated as follows:
root@app:~# whoami
root=
root@app:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user = ‘root‘;
+------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket | |
+------+-----------+-------------+-----------------------+
1 row in set (0.01 sec)
Same for the MariaDB .deb package:
10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘%‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
For the Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:
root@app:~# whoami
root
root@app:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-7 Percona Server (GPL), Release ‘7‘, Revision ‘613e312‘
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user =‘root‘;
+------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket | |
+------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)
So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux.
The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.
Here’s an example with the user “vagrant”:
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user ‘vagrant‘@‘localhost‘
Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘vagrant‘@‘localhost‘ IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)
vagrant@mysql1:~$ mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------+
| Grants for vagrant@localhost |
+---------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘vagrant‘@‘localhost‘ IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Success!
Now, what about on a non-debian distro, where this is not the default? Let’s try it on the Percona Server for MySQL 8 installed on a CentOS 7:
mysql> show variables like ‘%version%comment‘;
+-----------------+---------------------------------------------------+
| Variable_name | Value |
+-----------------+---------------------------------------------------+
| version_comment | Percona Server (GPL), Release 7, Revision 613e312 |
+-----------------+---------------------------------------------------+
1 row in set (0.01 sec)
mysql> CREATE USER ‘percona‘@‘localhost‘ IDENTIFIED WITH auth_socket;
ERROR 1524 (HY000): Plugin ‘auth_socket‘ is not loaded
It failed. What is missing? The plugin is not loaded:
mysql> pager grep socket
PAGER set to ‘grep socket‘
mysql> show plugins;
47 rows in set (0.00 sec)
Let’s add the plugin in runtime:
mysql> nopager
PAGER set to stdout
mysql> INSTALL PLUGIN auth_socket SONAME ‘auth_socket.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> pager grep socket; show plugins;
PAGER set to ‘grep socket‘
| auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL |
48 rows in set (0.00 sec)
We have all we need now. Let’s try again:
mysql> CREATE USER ‘percona‘@‘localhost‘ IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘percona‘@‘localhost‘;
Query OK, 0 rows affected (0.01 sec)
And now we can log in as the OS user “percona.”
[percona@ip-192-168-1-111 ~]$ whoami
percona
[percona@ip-192-168-1-111 ~]$ mysql -upercona
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user =‘percona‘;
+---------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+---------+-----------+-------------+-----------------------+
| percona | localhost | auth_socket | |
+---------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)
Success again!
Question: Can I try to log as the user percona from another user?
[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user ‘percona‘@‘localhost‘
No, you can’t.
Conclusion
MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.
reflink: https://dzone.com/articles/use-mysql-without-a-password-and-still-be-secure