2. 科技
    3. Linux运维笔记（四）

    Linux运维笔记（四）

    科技2022-07-12  131

    目录

    一、构建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/

    [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

    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)

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

    mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | 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 | +--------------------------------------+--------+ 7 rows in set (0.02 sec)

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

    PolicyTests Performe（要求0 or LOWLength1 or MEDIUMnumeric, lowercase/uppercase, and special characters2 or STRONGLength; numeric, lowercase/uppercase, and special characters

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

    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操作

    mysql> create database test_db; Query OK, 1 row affected (0.05 sec) mysql> use test_db; Database changed mysql> show tables; Empty set (0.01 sec) mysql> create table test01(id varchar(20),name varchar(20)); Query OK, 0 rows affected (0.12 sec) mysql> insert into test01 values("test01", "yangxiaoyang"); Query OK, 1 row affected (0.10 sec) mysql> select * from test01; +--------+--------------+ | id | name | +--------+--------------+ | test01 | yangxiaoyang | +--------+--------------+ 1 row in set (0.00 sec) mysql> desc test01; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)

    新用户授予权限，注意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

    2、https://blog.csdn.net/ssiyla/article/details/82931439

    Processed: 0.010, SQL: 8