软件安装 - Ubuntu 18.04 通过 apt 安装 MySQL 5.7 - 《Blog - 博客笔记》

安装 MySQL
配置 MySQL
调整 MySQL root 用户登录验证方式
安装 phpMyAdmin

本文是一篇转载文章，原文地址：https://zhangrenzheng.com/34.html
目前在 Ubuntu 18.04.3 LTS 通过 apt 安装的 MySql 默认版本为 5.7.28。

root@nyc:~# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

本文以下所有步骤均使用 root 用户在系统版本为 Ubuntu 18.04.3 LTS 的 DigitalOcean 云服务器上进行。
系统版本详细信息

root@nyc:~# lsb_release -a
No LSB modules are available.
Distributor ID:    Ubuntu
Description:    Ubuntu 18.04.3 LTS
Release:    18.04
Codename:    bionic

安装 MySQL

首先运行以下命令更新软件包列表：

apt update

然后安装 MySQL：

apt install mysql-server

在执行以上命令的时候，不会像安装其他 MySQL 版本一样提示设置 root 密码以及进行其他任何配置的更改。以上命令执行结束后，MySQL 将自动启动。
查看 MySQL 运行状态：

root@nyc:~# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2019-12-06 15:32:48 CST; 9min ago
 Main PID: 22275 (mysqld)
    Tasks: 27 (limit: 2318)
   CGroup: /system.slice/mysql.service
           └─22275 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

配置 MySQL

MySQL 5.7 这种的全新的安装方式要求运行一个软件包附带的安全脚本。运行这个脚本会更改一些不太安全的默认选项，例如远程 root 登录和测试用户。
执行以下命令运行安全脚本：

mysql_secure_installation

以下是运行脚本是的交互内容：

root@ubuntu-bj:~# mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
# 是否启用验证密码插件以检测密码强度？是
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
# 选择密码强度：2（强)
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.
# 输入两遍 root 密码
New password: 
Re-enter new password: 
Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : 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.
# 是否不允许 root 远程登录？否
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
 ... skipping.
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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.
All done!

请注意：在 MySQL 5.7 以及之后的版本中，尽管在运行安全脚本时已经设置好了 MySQL root 用户的密码，在使用 root 用户登录至 MySQL Shell 时不会使用密码认证，而是使用一个叫 auth_socket的插件认证。
以下演示使用 root 登录 MySQL Shell：

root@nyc:~# mysql -u root -p
Enter password: # 由于不是使用密码验证登录，这里随便输入便可以正确登录
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-0ubuntu0.18.04.4 (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>

调整 MySQL root 用户登录验证方式

如上面提到的，MySQL root 使用auth_socket插件而不是密码进行身份验证。所以当使用到其他数据库管理工具时，容易遇到很多错误。
为了使用密码以 root 用户身份连接到 MySQL ，您需要将其身份验证方法从 auth_socket 切换为mysql_native_password。
进入 MySQL Shell 界面：

mysql

接下来，使用以下命令检查每个MySQL用户帐户使用的身份验证方法：

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *E19B559DDA1964F6C56B92185EC94FDA646E43E9 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

在以上命令的执行结果中，可以看到 root 用户的验证方式为 auth_socket。要修改验证方式为密码验证，需要运行以下命令，其中 password 替换为密码。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'kangkang';
mysql> FLUSH PRIVILEGES;

注意：这一操作会将安全脚本中设置的 MySQL root 用户密码修改。
再次检查 MySQL root 用户的身份验证方式，可以看到已经修改为 mysql_native_password。

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *C80E73759CC86EB71DFFC81448098EE1D33437AA | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *E19B559DDA1964F6C56B92185EC94FDA646E43E9 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

在进行上述更改之后，可以使用 exit 退出 MySQL Shell。但是下次要进入 MySQL Shell 时，需要使用 mysql -u root -p，然后输入刚才设置的密码。

安装 phpMyAdmin

phpMyAdmin 是一个以PHP为基础，以Web-Base方式架构在网站主机上的MySQL的数据库管理工具，让管理者可用Web接口管理MySQL数据库。
这里将 phpMyAdmin 安装到默认站点下（通过 http://ip/phpmyadmim 访问)。
修改默认站点的服务器块 /etc/nginx/sites-available/default ：
1、找到 index 这一行，添加 index.php：

     # Add index.php to the list if you are using PHP
     index index.html index.htm index.nginx-debian.html index.php;

2、找到以下内容，并且去除一些注释，其中 fastcgi_pass 要和 /etc/php/7.2/fpm/pool.d/www.conf 中一致（见安装 php-fpm 说明的第2条）

    # pass PHP scripts to FastCGI server
    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        # With php-fpm (or other unix sockets):
        fastcgi_pass unix:/run/php/php7.2-fpm.sock; 
    #   # With php-cgi (or other tcp sockets):
    #   fastcgi_pass 127.0.0.1:9000;
    }

重载 Nginx:

service nginx reload

切换到 /var/www/html 目录，下载 phpMyadmin：

cd /var/www/html
wget https://files.phpmyadmin.net/phpMyAdmin/4.7.9/phpMyAdmin-4.7.9-all-languages.zip

解压压缩包、重命名 phpMyAdmin 文件夹以及修改以下文件权限：

unzip phpMyAdmin-4.7.9-all-languages.zip # 如果 unzip 没有安装 可以通过 apt install unzip 安装
mv phpMyAdmin-4.7.9-all-languages/ phpmyadmin
rm -f phpMyAdmin-4.7.9-all-languages.zip 
chown -R www-data:www-data .

到此，就可以通过 http://ip/phpmyadmim 访问 phpMyAdmin 了。