解决Mariadb10.1.37不需要密码就可以登录

在Ubuntu下apt装了Mariadb后,改掉root密码后,发现不需要密码就可以进入控制台。
刚开始以为密码修改失败了。后来发现是Mariadb认证插件的原因。

官方解释:https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

mysql库user表中默认有一条root用户记录,这条记录plugin字段(用户认证)默认值是 unix_socket(在centos7 yum安装的为mariadb-server-5.5.60版本,默认这个字段是空的),如果是这种认证方式,则通过系统凭据认证,就跳过数据库的密码认证了。
所以出现不需要密码就可以登录。

# 改回数据库密码认证

root@simon-PC:/etc/mysql# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

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)]> select user,plugin from mysql.user;
+------+-------------+
| user | plugin      |
+------+-------------+
| root | unix_socket |
+------+-------------+
1 rows in set (0.00 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set plugin = 'mysql_native_password' where user = 'root';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select user,plugin from mysql.user;
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 rows in set (0.00 sec)

MariaDB [mysql]> exit
Bye

# 重启MariaDB数据库

root@simon-PC:/etc/mysql# systemctl restart mysql
root@simon-PC:/etc/mysql# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

上面提示没有带密码,拒绝登录了。

root@simon-PC:/etc/mysql# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
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)]> 

完成。

centos6.5下yum安装mariadb

从https://downloads.mariadb.org/mariadb/repositories/找到你的系统版本
并且可以选择数据库版本。
一般平常用的版本,rhel5/6、centos5/6
我这里选择MariaDB5.5,以下是centos6.5_x64的yum仓库信息:
# MariaDB 5.5 CentOS repository list - created 2014-05-02 10:28 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

保存为MariaDB.repo放到/etc/yum.repos.d/下。
安装:
[root@centos-6.5-x64 ~]#yum install -y MariaDB-server MariaDB-client
[root@centos-6.5-x64 ~]#service mysql start #是mysql不是mysqld
[root@centos-6.5-x64 ~]#lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4347 mysql 14u IPv4 17716 0t0 TCP *:mysql (LISTEN)
其它操作跟mysql一样。

修改MariaDB数据库编码:
MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.12 sec)

先看下配置文件:
[root@centos-6.5-x64 ~]#cat /etc/my.cnf | grep -v "^$" | grep -v "#"
[client-server]
!includedir /etc/my.cnf.d
发现从/etc/my.cnf.d配置目录下加载所有文件。
[root@centos-6.5-x64 ~]#vi /etc/my.cnf.d/server.cnf
在[mysqld]段添加
character_set_server = utf8

以前是default-character-set现在在MariaDB不同于mysql。
重启mysql服务。
MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.28 sec)

mariadb简介及安装

mariadb简介及安装:

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB来代替MySQL的InnoDB。 MariaDB由MySQL的创始人
Michael Widenius主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL AB卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入Oracle的手中。MariaDB名称来自
Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎,替换了MySQL的MyISAM存储引擎,它使用了Percona的 XtraDB,InnoDB的变体,分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了
PrimeBase XT (PBXT) 和 FederatedX存储引擎。

MariaDB直到5.5版本,均依照MySQL的版本。因此,使用MariaDB5.5的人会从MySQL 5.5中了解到MariaDB的所有功能。

MariaDB默认的存储引擎是Maria,不是MyISAM。Maria可以支持事务,但是默认情况下没有打开事务支持,因为事务支持对性能会有影响。

与 MySQL 相比较,MariaDB 更强的地方在于:
Maria 存储引擎
PBXT 存储引擎
XtraDB 存储引擎
FederatedX 存储引擎
更快的复制查询处理
线程池
更少的警告和bug
运行速度更快
更多的 Extensions (More index parts, new startup options etc)
更好的功能测试
数据表消除
慢查询日志的扩展统计
支持对 Unicode 的排序

相对于MySQL最新的版本5.6来说,在性能、功能、管理、NoSQL扩展方面,MariaDB包含了更丰富的特性。比如微秒的支持、线程池、子查询优化、组提交、进度报告等。
官网:www.mariadb.org
目前稳定版:mariadb-5.5.36
下载地址:http://mirrors.neusoft.edu.cn/mariadb/mariadb-5.5.36/kvm-tarbake-jaunty-x86/mariadb-5.5.36.tar.gz

安装bison:
[root@localhost bison-2.5]# ./configure
[root@localhost bison-2.5]# make && make install

安装cmake:
[root@localhost cmake-2.8.10.2]# ./configure
[root@localhost cmake-2.8.10.2]# gmake
[root@localhost cmake-2.8.10.2]# make install

安装mariadb:

[root@localhost mariadb-5.5.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb -DMYSQL_UNIX_ADDR=/tmp/mariadb.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/usr/local/mariadb/data -DMYSQL_USER=mysql
[root@localhost mariadb-5.5.36]# make && make install
[root@localhost mariadb]# cp support-files/my-medium.cnf /etc/my.cnf
[root@localhost mariadb]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mariadb]# chmod 755 /etc/init.d/mysqld
[root@localhost mariadb]# chown -R root.mysql /usr/local/mariadb/
[root@localhost mariadb]# chown -R mysql /usr/local/mariadb/data/
[root@localhost mariadb]# /usr/local/mariadb/scripts/mysql_install_db --basedir=/usr/local/mariadb/ --datadir=/usr/local/mariadb/data/ --user=mysql

[root@localhost mariadb]# service mysqld start
Starting MySQL… [确定]
[root@localhost mariadb]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 10996 mysql 15u IPv6 109597 TCP *:mysql (LISTEN)

[root@localhost ~]# /usr/local/mariadb/bin/mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.36-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> show engines;
+——————–+———+—————————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————————-+————–+——+————+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+——————–+———+—————————————————————————-+————–+——+————+
7 rows in set (0.00 sec)

MariaDB [(none)]>