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 [email protected]: 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