MySQL Community 5.7在SLES11安装与配置 - 运维手记 2018-11-26

2018/11/26 23:45 下午 posted in  技术 随记 comments

今天工作有些东西需要整理,现在整理一下。

注意:本文首次撰写于2018-11-26,最近修改时间为2018-11-26,请注意相关程序的可用性与安全性。

下载与安装

下载mysql社区版rpm软件包 (可到MySQL官方找到最新的下载地址):

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

解压查看:

localhost:software # tar -xvf mysql-5.7.24-1.sles11.x86_64.rpm-bundle.tar 

解压后有下述文件:

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文件。但是我们不需要这么多,我们只需要安装一下四个组件就可以了:

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的时候,需要依赖安装一些工具组件,已经在上文有说明了。安装命令如下:

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

执行具体记录:

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的管理了:

# 查看mysql是否启动
service mysql status

# 启动mysql
service mysql start

# 停止mysql
service mysql stop

# 重启mysql
service mysql restart

另外,可以新建mysql用户以便使用。

useradd mysql

这里注意,如果不执行启动会报错,具体报错为:

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) 

必须执行service mysql start才可以进行下一步。

设置密码

mysql安装并启动完成之后我们是没有设置密码的,但是mysql为我们设置了一个临时的密码,我们可以查看mysql的日志知道这个临时密码。

grep password /var/log/mysql/mysqld.log

执行过程

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。 然后我们用这个临时密码登录数据库。数据库的密码需要满足以下条件:大小写字母,数字和特殊符号

执行命令

# 登录mysql,之后需要输入密码
mysql -p

# 设置新密码
set password = password("ZNing_123456");

# 退出当前登录
quit;

# 或用如下命令退出
exit

执行过程

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数据库:

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的主机方式访问。紧接着执行下面的命令:

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报了个神奇的错误:

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密码相关的几个全局参数:

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/LOW1/MEDIUM2/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的权限放低设置:

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)

注意:这种操作会对账户密码的安全性管理大大降低,请谨慎操作。

再次执行命令,即可通过:

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