# MySQL Community 5.7在SLES11安装与配置 - 运维手记 2018-11-26 > 作者：zning（https://ning.ae） · 发布：2018/11/26 23:45 下午 (UTC+8) · 原文：https://zning.me/15599739718578.html 今天工作有些东西需要整理，现在整理一下。 > 注意：本文首次撰写于2018-11-26，最近修改时间为2018-11-26，请注意相关程序的可用性与安全性。 ### 下载与安装 下载mysql社区版rpm软件包 (可到MySQL官方找到最新的下载地址)： ```shell localhost:software # wget http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.7/mysql-5.7.24-1.sles11.x86_64.rpm-bundle.tar ``` 解压查看： ```shell localhost:software # tar -xvf mysql-5.7.24-1.sles11.x86_64.rpm-bundle.tar ``` 解压后有下述文件： ```shell mysql-community-client-5.7.24-1.sles11.x86_64.rpm mysql-community-common-5.7.24-1.sles11.x86_64.rpm mysql-community-devel-5.7.24-1.sles11.x86_64.rpm mysql-community-embedded-5.7.24-1.sles11.x86_64.rpm mysql-community-embedded-devel-5.7.24-1.sles11.x86_64.rpm mysql-community-libs-5.7.24-1.sles11.x86_64.rpm mysql-community-server-5.7.24-1.sles11.x86_64.rpm mysql-community-test-5.7.24-1.sles11.x86_64.rpm ``` 用`yast2`先卸载已存在的MySQL的rpm包。之后再安装新版本MySQL。如果需要什么依赖，请先安装依赖。 经过上面的解压操作，我们得到了很多rpm文件。但是我们不需要这么多，我们只需要安装一下四个组件就可以了： ```shell mysql-community-common-5.7.24-1.sles11.x86_64.rpm mysql-community-libs-5.7.24-1.sles11.x86_64.rpm mysql-community-client-5.7.24-1.sles11.x86_64.rpm mysql-community-server-5.7.24-1.sles11.x86_64.rpm ``` 因为具有依赖关系，所以我们需要按顺序执行。 用`rpm -ivh`文件名就能安装相应的组件。 在执行server的时候，需要依赖安装一些工具组件，已经在上文有说明了。安装命令如下： ```shell localhost:software # rpm -ivh mysql-community-common-5.7.24-1.sles11.x86_64.rpm localhost:software # rpm -ivh mysql-community-libs-5.7.24-1.sles11.x86_64.rpm localhost:software # rpm -ivh mysql-community-client-5.7.24-1.sles11.x86_64.rpm localhost:software # rpm -ivh mysql-community-server-5.7.24-1.sles11.x86_64.rpm ``` 执行具体记录： ```shell localhost:software # rpm -ivh mysql-community-common-5.7.24-1.sles11.x86_64.rpm warning: mysql-community-common-5.7.24-1.sles11.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-5.7.24-1.e################################# [100%] localhost:software # rpm -ivh mysql-community-libs-5.7.24-1.sles11.x86_64.rpm warning: mysql-community-libs-5.7.24-1.sles11.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-libs-5.7.24-1.sles11################################# [100%] localhost:software # rpm -ivh mysql-community-client-5.7.24-1.sles11.x86_64.rpm warning: mysql-community-client-5.7.24-1.sles11.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-client-5.7.24-1.e################################# [100%] localhost:software # rpm -ivh mysql-community-server-5.7.24-1.sles11.x86_64.rpm warning: mysql-community-server-5.7.24-1.sles11.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-server-5.7.24-1.e################################# [100%] ``` 之后就可以通过`service`命令进行mysql的管理了： ```shell # 查看mysql是否启动 service mysql status # 启动mysql service mysql start # 停止mysql service mysql stop # 重启mysql service mysql restart ``` 另外，可以新建`mysql`用户以便使用。 ```shell useradd mysql ``` 这里注意，如果不执行启动会报错，具体报错为： ```shell ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) ``` 必须执行`service mysql start`才可以进行下一步。 ### 设置密码 mysql安装并启动完成之后我们是没有设置密码的，但是mysql为我们设置了一个临时的密码，我们可以查看mysql的日志知道这个临时密码。 ```shell grep password /var/log/mysql/mysqld.log ``` 执行过程 ```shell localhost:software # grep password /var/log/mysql/mysqld.log 2017-09-01T16:43:10.889769Z 1 [Note] A temporary password is generated for root@localhost: hcLMTxbOh2?w ``` 这样我们得知临时密码是：`hcLMTxbOh2?w`。 然后我们用这个临时密码登录数据库。数据库的密码需要满足以下条件：大小写字母，数字和特殊符号 执行命令 ```shell # 登录mysql，之后需要输入密码 mysql -p # 设置新密码 set password = password("ZNing_123456"); # 退出当前登录 quit; # 或用如下命令退出 exit ``` 执行过程 ```shell localhost ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.19 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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> set password = password("ZNing_123456"); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit; ``` 重新登录，这样我们就能用新的密码登录了。我们可以愉快的进行数据库操作了。 ### 远程访问开启 连接数据库后访问`mysql`数据库： ```shell mysql> use mysql; (此DB存放MySQL的各种配置信息) Database changed mysql> select host,user from user; (查看用户的权限情况) +-------------+-------+ | host | user | +-------------+-------+ | localhost | | | localhost | root | | localhost | | | localhost | mysql | +-------------+-------+ 6 rows in set (0.02 sec) ``` 由此可以看出，只能以localhost的主机方式访问。紧接着执行下面的命令： ```shell mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option; #(%表示是所有的外部机器，如果指定某一台机，就将%改为相应的机器名；‘root’则是指要使用的用户名，里面的password需要自己修改成root的密码) mysql> flush privileges; #(运行为句才生效，或者重启MySQL) Query OK, 0 rows affected (0.03 sec) mysql> select host,user from user; #(再次查看用户的权限情况) +-------------+-------+ | host | user | +-------------+-------+ | ％ | mysql | | ％ | root | | localhost | | | localhost | root | | localhost | | | localhost | mysql | +-------------+-------+ mysql>exit ``` 现在再试试：从远程cmd那边看能不能登录，能登录的话，一般用代码也是可以连接的到的。另外需要注意的是用户权限表是在mysql数据库的db表，里面详细记录了哪些用户有权限对哪几个数据库有操作的权限，这个对多用户的管理比较重要。 但是在执行过程中，我的mysql报了个神奇的错误： ```shell mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ``` 经查，此报错与MySQL5.6.6版本之后增加了密码强度验证插件`validate_password`有关，相关参数设置的较为严格。因此会报此问题。该问题其实与mysql的`validate_password_policy`的值有关。查看一下MySQL密码相关的几个全局参数： ```shell mysql> select @@validate_password_policy; +----------------------------+ | @@validate_password_policy | +----------------------------+ | MEDIUM | +----------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 6 rows in set (0.08 sec) ``` 参数解释： `validate_password_dictionary_file`是插件用于验证密码强度的字典文件路径。 `validate_password_length`是密码最小长度，参数默认为8，它有最小值的限制，最小值为：`validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)` `validate_password_mixed_case_count`是密码至少要包含的小写字母个数和大写字母个数。 `validate_password_number_count`是密码至少要包含的数字个数。 `validate_password_policy`是密码强度检查等级，`0/LOW`、`1/MEDIUM`、`2/STRONG`。有以下取值： | Policy | Tests Performed | | --- | --- | | 0 or LOW | Length | | 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters | | 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |         默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。 `validate_password_special_char_count`密码至少要包含的特殊字符数。 由此，进行MySQL的权限放低设置： ```shell mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.05 sec) mysql> set global validate_password_mixed_case_count=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_number_count=3; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_special_char_count=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=3; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password_dictionary_file | | | validate_password_length | 3 | | validate_password_mixed_case_count | 0 | | validate_password_number_count | 3 | | validate_password_policy | LOW | | validate_password_special_char_count | 0 | +--------------------------------------+-------+ 6 rows in set (0.00 sec) ``` > **注意：这种操作会对账户密码的安全性管理大大降低，请谨慎操作。** 再次执行命令，即可通过： ```shell mysql> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option; Query OK, 0 rows affected (0.00 sec) ``` 再用MySQL Workbench或者Navicat连接即可。 ## 参考文献 1. https://blog.csdn.net/fanshujuntuan/article/details/78077433 2. http://blog.51cto.com/putian/1287959 3. https://blog.csdn.net/maxsky/article/details/51171474 4. http://wiki.jikexueyuan.com/project/git-tutorial/remote-repository.html 5. http://www.luxinzhi.com/system/140.html 6. https://overtrue.me/articles/2015/05/install-latest-git-on-centos.html 7. https://blog.csdn.net/zsx157326/article/details/80059068 8. https://blog.csdn.net/chenqiai0/article/details/83472410 9. https://www.jianshu.com/p/3478e2a214a1