CentOS7通过SCL安装MariaDB与密码重置

其实想写这个主要是因为工作时遇到一台部门自用测试机中MariaDB的root密码丢失的情况，同事的备份文件中也没有找到，这很尬尴，看了一下用的还是SCL中的版本，与自带的安装源版本不大一样，就记录一下该种方式的安装和怎么在密码丢失的情况下重置密码。

关于SCL

SCL(Software Collections)可以让你在同一个操作系统上安装和使用多个版本的软件，而不会影响整个系统的安装包。
SCL主页: https://www.softwarecollections.org/en/

安装SCL

1 2	[root@toptop ~]# yum install epel-release -y [root@toptop ~]# yum install centos-release-scl -y

安装MariaDB

SCL中关于MariaDB的介绍如下:

rh-mariadb102 - A recent stable release of MariaDB 10.2. This Software Collection gives users of CentOS and RHEL an alternative to MySQL, which is binary compatible with MariaDB in most practical cases and can be replaced with it.

那么现在进行安装:

1	[root@toptop ~]# yum install rh-mariadb102

在会话中启用MariaDB命令，这样MariaDB中的命令就均在直接在bash中执行，无需进入相关目录:

1	[root@toptop ~]# scl enable rh-mariadb102 bash

开启MariaDB服务

[root@toptop ~]# systemctl enable --now rh-mariadb102-mariadb.service
[root@toptop ~]# echo [mysqld] | tee /etc/opt/rh/rh-mariadb102/my.cnf.d/bind-address.cnf
[root@toptop ~]# echo bind-address=127.0.0.1 | tee -a /etc/opt/rh/rh-mariadb102/my.cnf.d/bind-address.cnf
[root@toptop ~]# systemctl restart rh-mariadb102-mariadb

此时可以查看MariaDB的安装位置和版本:

[root@toptop ~]# which mysql
/opt/rh/rh-mariadb102/root/usr/bin/mysql

[root@toptop ~]# mysql -V
mysql  Ver 15.1 Distrib 10.2.22-MariaDB, for Linux (x86_64) using  EditLine wrapper

MySQL安全配置向导

[root@toptop ~]# mysql_secure_installation  

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):        <–----------注意在初次运行时直接回车即可
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

登录验证

[root@toptop ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.22-MariaDB MariaDB Server

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, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

接下来，其他的配置文件就自行在配置文件调整吧。

修改密码

下面说一下MariaDB的root密码丢失时处理的过程:

1
2
3

[root@toptop ~]# cd /etc/opt/rh/rh-mariadb102/my.cnf.d/
[root@toptop my.cnf.d]# ls
bind-address.cnf  enable_encryption.preset  mariadb-server.cnf  mysql-clients.cnf

在mariadb-server.cnf文件中增加如下一行:

[mysqld]
datadir=/var/opt/rh/rh-mariadb102/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/opt/rh/rh-mariadb102/log/mariadb/mariadb.log
pid-file=/run/rh-mariadb102-mariadb/mariadb.pid
skip-grant-tables      # <–----------增加此行

此时重启MariaDB服务，即可免密进入MariaDB:

[root@toptop my.cnf.d]# systemctl restart rh-mariadb102-mariadb
[root@toptop my.cnf.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.22-MariaDB MariaDB Server

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)]>

在数据库中执行如下语句:

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 Password = password ('qwerty') WHERE User = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>

接下来退出数据库，删除mariadb-server.cnf中增加的skip-grant-tables，重启服务，使用刚刚设置的密码，即可登录MariaDB。

[root@toptop my.cnf.d]# systemctl restart rh-mariadb102-mariadb
[root@toptop my.cnf.d]# mysql -uroot -pqwerty
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.22-MariaDB MariaDB Server

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)]>