前言

记的是一次非常离谱的踩坑,mysql_secure_installation设置密码后竟然还是没有密码,此处系统是ubuntu

网上的mysql搭建教程

网上的mysql搭建教程普遍是两步走

  • apt install mysql-server安装mysql本体
  • mysql_secure_installation设置密码和初始化生产环境的相关配置

离谱的问题

我按照脚本提示跑完了全程,过程类似于这样

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, 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 MySQL
root user without the proper authorisation.
Set root password? [Y/n] y
New password: xxxxx
Re-enter new password: xxxxx
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL 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, MySQL 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 MySQL
installation should now be secure.
Thanks for using MySQL!

当然,只是类似于这样(因为上面这段是随便找的,我已经懒得再去装Mysql来复现了)
上面的和我当时运行时的区别只有第一步是否要安装一个密码强度校验的插件

我跑完了脚本全程,高兴的看着

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

然后随手输入了一个mysql然后我就惊呆了
它TMD直接登录进去了??
于是我又使用mysql -u root,结果还是直接登录进去了,连密码请求都没有??
为了确认登录的不是脚本里所说的匿名用户,我又运行

mysql> select user();
+----------------+
| user()         |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.003 sec)

好家伙,居然是root

于是我直接apt-get purge mysql*重来了一遍,这一次我特地选上了上次运行时没有选择的密码强度要求插件,结果还是一样......

为了排查问题,我切换到了mysql的另一个更有信仰的分支,mariadb
apt install mariadb-server
然后mysql_secure_installation

# 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):

看这个脚本都已经被改过了,就觉得成功的希望很大
结果,还是一样......
跑完整个脚本,来一次mysql,直接root登录了......
我直接心态崩了......

为什么?

我依稀记得新版的mysql已经变更了修改密码的方式,以前的SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');已经被弃用,取而代之的是ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';,这个坑我曾经在windows上测试的时候踩过

我尝试在新安装的mariadb上使用ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';的方式修改密码,结果,一次成功

于是我打开了mysql_secure_installation脚本瞧了瞧

    echo "Sorry, you can't use an empty password here."
    echo
    return 1
    fi

    esc_pass=`basic_single_escape "$password1"`
    do_query "UPDATE mysql.user SET Password=PASSWORD('$esc_pass') WHERE User='root';"
    if [ $? -eq 0 ]; then
    echo "Password updated successfully!"
    echo "Reloading privilege tables.."
    reload_privilege_tables
    if [ $? -eq 1 ]; then

嗯?这难道不是被弃用的密码修改方式吗?
我又瞧了瞧脚本里的其他部分,包括删除test表、删除匿名用户、禁用远程访问之类的,在目前我使用的mariadb上,它们默认都没有,也就是说,这个脚本跑了个寂寞,数据库本身就是跑过脚本的状态......

离谱离谱真是离谱,堂堂mysql & mariadb居然自带了这种早该被弃用的东西

到底该怎么初始化Mysql

  • apt install mysql-server
  • mysql
  • ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
  • flush privileges;
  • exit;
  • 结束