Linux运维笔记（四）

╰+攻爆jí腚メ 2022-12-11 15:24 137阅读 0赞

**目录**

一、构建Linux下MySQL数据库

1、MySQL数据库

2、MySQL安装

3、启动mysql服务

4、登陆MySQL以及修改root密码

5、简单SQL操作

--------------------

# 一、构建Linux下MySQL数据库 #

## 1、MySQL数据库 ##

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL两个引擎：MyISAM和innoDB。区别是MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但不提供事务支持，如果执行大量的SELECT操作，MyISAM是更好的选择，支持表锁。InnoDB提供事务支持事务外部键等高级数据库功能，执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表，支持行锁。

## 2、MySQL安装 ##

（1）命令行安装(无)

（2）源码下载安装

查了下现在安装方式都是rpm安装，下载路径如下：[https://dev.mysql.com/downloads/mysql/][https_dev.mysql.com_downloads_mysql]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hseXJo_size_16_color_FFFFFF_t_70][]

![20201004233454299.png][]

[root@localhost opt]# tar -xvf mysql-8.0.21-1.el7.x86_64.rpm-bundle\ \(1\).tar
    mysql-community-common-8.0.21-1.el7.x86_64.rpm
    mysql-community-embedded-compat-8.0.21-1.el7.x86_64.rpm
    mysql-community-libs-8.0.21-1.el7.x86_64.rpm
    mysql-community-devel-8.0.21-1.el7.x86_64.rpm
    mysql-community-server-8.0.21-1.el7.x86_64.rpm
    mysql-community-client-8.0.21-1.el7.x86_64.rpm
    mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm
    mysql-community-test-8.0.21-1.el7.x86_64.rpm

（3）rpm安装

这里主要使用的mysql组件有4个：server、client、common、libs，安装顺序依次为：common→libs→client→server

[root@localhost opt]# rpm -ivh mysql-community-common-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-common-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    Preparing...                          ################################# [100%]
    Updating / installing...
       1:mysql-community-common-8.0.21-1.e################################# [100%]
    
    [root@localhost opt]# rpm -ivh mysql-community-libs-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-libs-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    Preparing...                          ################################# [100%]
    Updating / installing...
       1:mysql-community-libs-8.0.21-1.el7################################# [100%]
    [root@localhost opt]# rpm -ivh mysql-community-client-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-client-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    Preparing...                          ################################# [100%]
    Updating / installing...
       1:mysql-community-client-8.0.21-1.e################################# [100%]
    [root@localhost opt]# rpm -ivh mysql-community-server-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-server-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    Preparing...                          ################################# [100%]
    Updating / installing...
       1:mysql-community-server-8.0.21-1.e################################# [100%]

注：

（1）安装lib时候报错，解决方法：yum remove mysql-libs，清除之前的依赖再继续安装

[root@localhost opt]# rpm -ivh mysql-community-libs-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-libs-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    error: Failed dependencies:
            mariadb-libs is obsoleted by mysql-community-libs-8.0.21-1.el7.x86_64

（2）安装server时候报错，需要先安装perl

[root@localhost opt]# rpm -ivh mysql-community-server-8.0.21-1.el7.x86_64.rpm
    warning: mysql-community-server-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
    error: Failed dependencies:
            /usr/bin/perl is needed by mysql-community-server-8.0.21-1.el7.x86_64
            net-tools is needed by mysql-community-server-8.0.21-1.el7.x86_64
            perl(Getopt::Long) is needed by mysql-community-server-8.0.21-1.el7.x86_64

wget [https://cpan.metacpan.org/authors/id/S/SH/SHAY/perl-5.30.2.tar.gz][https_cpan.metacpan.org_authors_id_S_SH_SHAY_perl-5.30.2.tar.gz]

tar -zxvf perl-5.30.2.tar.gz -C /opt

./Configure -des -Dprefix=/opt/perl-5.30.2

make && make test

make install

perl -v

yum install *net-tools*

## 3、启动mysql服务 ##

systemctl start mysqld.service    //启动mysql  
systemctl status mysqld.service  //查看mysql状态  
systemctl stop mysqld.service     //关闭mysql

[root@localhost etc]# systemctl start mysqld.service
    [root@localhost etc]# systemctl status mysqld.service
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: active (running) since Sun 2020-10-04 16:39:16 EDT; 28s ago
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
      Process: 18727 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
     Main PID: 18800 (mysqld)
       Status: "Server is operational"
       CGroup: /system.slice/mysqld.service
               └─18800 /usr/sbin/mysqld
    
    Oct 04 16:38:55 localhost.localdomain systemd[1]: Starting MySQL Server...
    Oct 04 16:39:16 localhost.localdomain systemd[1]: Started MySQL Server.
    [root@localhost etc]# systemctl stop mysqld.service

启动后可以通过命令查看服务状态

[root@localhost ~]# ps -ef|grep mysql
    mysql    18800     1  1 16:39 ?        00:00:51 /usr/sbin/mysqld
    root     19229 18899  0 17:43 pts/1    00:00:00 grep --color=auto mysql
    [root@localhost ~]# ps -ef|grep mysqld
    mysql    18800     1  1 16:39 ?        00:00:51 /usr/sbin/mysqld
    root     19231 18899  0 17:43 pts/1    00:00:00 grep --color=auto mysqld
    [root@localhost ~]# netstat -anop|grep 3306
    tcp6       0      0 :::33060                :::*                    LISTEN      18800/mysqld         off (0.00/0/0)
    tcp6       0      0 :::3306                 :::*                    LISTEN      18800/mysqld         off (0.00/0/0)

## 4、登陆MySQL以及修改root密码 ##

在新版本的mysql中在安装过程中自动设定一个默认密码，这个密码可以通过日志查看，命令如下

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
    2020-10-04T20:39:03.831989Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: pp4,WVoe?kOj

这里pp4,WVoe?kOj即为登陆密码，使用这个密码可以登陆，登陆命令如下：

[root@localhost ~]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 8.0.21
    
    Copyright (c) 2000, 2020, 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> ALTER USER root@localhost IDENTIFIED  BY '123456';
    ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
    mysql> ALTER USER root@localhost IDENTIFIED  BY '123qweRTY!@#';
    Query OK, 0 rows affected (0.05 sec)

修改密码后可以查看密码设置相关参数，命令如下

其中密码复杂度和validate\_password\_policy这个参数有关，参数说明如下表，默认复杂度是1即MEDIUM，所以首次设置密码必须含有**数字，小写或大写字母，特殊字符。**

<table> 
 <thead> 
  <tr> 
   <th>Policy</th> 
   <th>Tests Performe（要求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0 or LOW</td> 
   <td>Length</td> 
  </tr> 
  <tr> 
   <td>1 or MEDIUM</td> 
   <td>numeric, lowercase/uppercase, and special characters</td> 
  </tr> 
  <tr> 
   <td>2 or STRONG</td> 
   <td>Length; numeric, lowercase/uppercase, and special characters</td> 
  </tr> 
 </tbody> 
</table>

首次修改密码后可以修改密码默认配置，便于设置一个简单的密码

mysql> set global validate_password.policy=0;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> set global validate_password.length=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'validate_password%';
    +--------------------------------------+-------+
    | Variable_name                        | Value |
    +--------------------------------------+-------+
    | validate_password.check_user_name    | ON    |
    | validate_password.dictionary_file    |       |
    | validate_password.length             | 4     |
    | validate_password.mixed_case_count   | 1     |
    | validate_password.number_count       | 1     |
    | validate_password.policy             | LOW   |
    | validate_password.special_char_count | 1     |
    +--------------------------------------+-------+
    7 rows in set (0.00 sec)
    
    mysql>  ALTER USER root@localhost IDENTIFIED  BY '1234';
    Query OK, 0 rows affected (0.06 sec)

## 5、简单SQL操作 ##

新用户授予权限，注意mysql8.0以后版本授权方式发生变化，该版本将创建账户和赋予权限分开

mysql> create user 'yang'@'192.168.56.102' identified by  '123456';
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> grant all privileges on *.* to 'yang'@'192.168.56.102' with grant option;
    
    Query OK, 0 rows affected (0.13 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.07 sec)

MySQL备份与恢复

[root@localhost ~]# mysqldump -uroot -p1234 test_db > test_db.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@localhost ~]# ls
    anaconda-ks.cfg  data  test_db.sql  tmp
    [root@localhost ~]# mysql -uroot -p1234 test_db < test_db.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.

参考文档：

1、[https://blog.csdn.net/wudinaniya/article/details/81094578][https_blog.csdn.net_wudinaniya_article_details_81094578]

2、[https://blog.csdn.net/ssiyla/article/details/82931439][https_blog.csdn.net_ssiyla_article_details_82931439]

[https_dev.mysql.com_downloads_mysql]: https://dev.mysql.com/downloads/mysql/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hseXJo_size_16_color_FFFFFF_t_70]: /images/20221123/2a96b1a0fee64b368ea3ee72dff7669d.png
[20201004233454299.png]: /images/20221123/7b49598f18d241cb8c18977d27484f1a.png
[https_cpan.metacpan.org_authors_id_S_SH_SHAY_perl-5.30.2.tar.gz]: https://cpan.metacpan.org/authors/id/S/SH/SHAY/perl-5.30.2.tar.gz
[https_blog.csdn.net_wudinaniya_article_details_81094578]: https://blog.csdn.net/wudinaniya/article/details/81094578
[https_blog.csdn.net_ssiyla_article_details_82931439]: https://blog.csdn.net/ssiyla/article/details/82931439