安装MariaDB 无法登录解惑

Debian 9 installed mariadb-server , the phpmyadmin can't login and terminal too,for prove this problem ;

$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

The reason is that recent Ubuntu installation (maybe others also), mysql is using by default the UNIX auth_socket plugin.

Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:

$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+ 

As you can see in the query, the root user is using the auth_socket plugin

There are 2 ways to solve this:

  1. You can set the root user to use the mysql_native_password plugin
  2. You can create a new db_user with you system_user (recommended)

Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart 

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart 

Remember that if you use option #2 you'll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)

If you could login with sudo but not without sudo then do:

$ sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q 

Once done $ mysql -u root without sudo will work just fine.

原文发布于微信公众号 - 竹清助手(zhuqing_help)

原文发表时间:2017-11-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏闵开慧

hive启动后相关操作

hadoop@ubuntu118:~$ $HIVE_HOME/bin/hive WARNING: org.apache.hadoop.metri...

36780
来自专栏专注数据中心高性能网络技术研发

Never reveal your current salary to a potential employer

In 2017, both California and New York banned potential employers from asking job...

364100
来自专栏Hadoop实操

CENTOS6.5安装CDH5.12.1(二)

[root@ip-172-31-6-148~]# hadoop fs -mkdir -p /fayson/test

41260
来自专栏李蔚蓬的专栏

java.lang.NullPointerException: Attempt to invoke virtual method 'int android.content.Intent.getI...

这个报错比较骚,完全只是因为Intent没有写好而已,下面是错误的写法(最好不要在方法外赋值):

1K20
来自专栏智能计算时代

Designing your SaaS Database for Scale with Postgres

? If you’re building a SaaS application, you probably already have the notion o...

22740
来自专栏Golang语言社区

Go调试简单的内存泄漏

Memory leaks are a class of bugs where memory is not released even after it is n...

57330
来自专栏技术点滴

Windows注册表操作基础代码

Windows注册表操作基础代码     Windows下对注册表进行操作使用的一段基础代码Reg.h: #pragma once #include<asser...

23290
来自专栏我的博客

Zend Framework自动加载、简单路由配置(Bootstrap.php)

<?php class Bootstrap extends Zend_Application_Bootstrap_Bootstrap { /* * 自动加...

36580
来自专栏算法修养

POJ-2184 Cow Exhibition(01背包变形)

Cow Exhibition Time Limit: 1000MS Memory Limit: 65536K Total Submission...

314100
来自专栏Pulsar-V

OpenCV编译参数一览

全部参数一览 //Path to a program. ANT_EXECUTABLE:FILEPATH=D:/apache-ant-1.10.1/bin/ant...

89860

扫码关注云+社区

领取腾讯云代金券