MySQL基本安装过程

2021-12-16 | 阅读：次

1. 基本信息：

网络接口：

[root@mysql-1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:43:16:ef brd ff:ff:ff:ff:ff:ff
    inet 192.168.174.129/24 brd 192.168.174.255 scope global noprefixroute dynamic ens33
       valid_lft 1475sec preferred_lft 1475sec
    inet6 fe80::6000:2d5d:bc9:b4d3/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: ens37: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:43:16:f9 brd ff:ff:ff:ff:ff:ff
    inet 192.168.203.181/24 brd 192.168.203.255 scope global noprefixroute ens37
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe43:16f9/64 scope link
       valid_lft forever preferred_lft forever

内核：

[root@mysql-1 ~]# uname -a
Linux mysql-1 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

操作系统：

cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

2. 准备MySQL RPM包

下载地址： https://dev.mysql.com/downloads/mysql/

找到对应版本下载即可。本例直接wget下载：

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar

解压缩：

mkdir mysql
tar xvf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar  -C ./mysql
cd mysql
ll

得到RPM文件：

[root@mysql-1 mysql]# ll
total 817720
-rw-r--r--. 1 7155 31415  55178328 Sep 29 15:33 mysql-community-client-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   5932800 Sep 29 15:34 mysql-community-client-plugins-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    641616 Sep 29 15:34 mysql-community-common-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   7760100 Sep 29 15:34 mysql-community-devel-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  23637616 Sep 29 15:34 mysql-community-embedded-compat-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   4935900 Sep 29 15:34 mysql-community-libs-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   1264256 Sep 29 15:34 mysql-community-libs-compat-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 470252428 Sep 29 15:36 mysql-community-server-8.0.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 267724484 Sep 29 15:38 mysql-community-test-8.0.27-1.el7.x86_64.rpm

作为数据库服务器，需要安装server包：

sudo yum install mysql-community-{server,client,common,libs}-*

作为应用客户端安装：

sudo yum install mysql-community-{client,common,libs}-*

安装过程

[root@mysql-1 mysql]# sudo yum install mysql-community-{server,client,common,libs}-*
Loaded plugins: fastestmirror
Examining mysql-community-server-8.0.27-1.el7.x86_64.rpm: mysql-community-server-8.0.27-1.el7.x86_64
Marking mysql-community-server-8.0.27-1.el7.x86_64.rpm to be installed
Examining mysql-community-client-8.0.27-1.el7.x86_64.rpm: mysql-community-client-8.0.27-1.el7.x86_64
Marking mysql-community-client-8.0.27-1.el7.x86_64.rpm to be installed
Examining mysql-community-client-plugins-8.0.27-1.el7.x86_64.rpm: mysql-community-client-plugins-8.0.27-1.el7.x86_64
Marking mysql-community-client-plugins-8.0.27-1.el7.x86_64.rpm to be installed
Examining mysql-community-common-8.0.27-1.el7.x86_64.rpm: mysql-community-common-8.0.27-1.el7.x86_64
Marking mysql-community-common-8.0.27-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-8.0.27-1.el7.x86_64.rpm: mysql-community-libs-8.0.27-1.el7.x86_64
Marking mysql-community-libs-8.0.27-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-compat-8.0.27-1.el7.x86_64.rpm: mysql-community-libs-compat-8.0.27-1.el7.x86_64
Marking mysql-community-libs-compat-8.0.27-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted
---> Package mysql-community-client.x86_64 0:8.0.27-1.el7 will be installed
---> Package mysql-community-client-plugins.x86_64 0:8.0.27-1.el7 will be installed
---> Package mysql-community-common.x86_64 0:8.0.27-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.27-1.el7 will be obsoleting
---> Package mysql-community-libs-compat.x86_64 0:8.0.27-1.el7 will be obsoleting
---> Package mysql-community-server.x86_64 0:8.0.27-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================
 Package                             Arch        Version            Repository                                                Size
===================================================================================================================================
Installing:
 mysql-community-client              x86_64      8.0.27-1.el7       /mysql-community-client-8.0.27-1.el7.x86_64              262 M
 mysql-community-client-plugins      x86_64      8.0.27-1.el7       /mysql-community-client-plugins-8.0.27-1.el7.x86_64       28 M
 mysql-community-common              x86_64      8.0.27-1.el7       /mysql-community-common-8.0.27-1.el7.x86_64              9.5 M
 mysql-community-libs                x86_64      8.0.27-1.el7       /mysql-community-libs-8.0.27-1.el7.x86_64                 24 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-libs-compat         x86_64      8.0.27-1.el7       /mysql-community-libs-compat-8.0.27-1.el7.x86_64         6.0 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-server              x86_64      8.0.27-1.el7       /mysql-community-server-8.0.27-1.el7.x86_64              2.1 G

Transaction Summary
===================================================================================================================================
Install  6 Packages

Total size: 2.4 G
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-client-plugins-8.0.27-1.el7.x86_64                                                              1/7
  Installing : mysql-community-common-8.0.27-1.el7.x86_64                                                                      2/7
  Installing : mysql-community-libs-8.0.27-1.el7.x86_64                                                                        3/7
  Installing : mysql-community-client-8.0.27-1.el7.x86_64                                                                      4/7
  Installing : mysql-community-server-8.0.27-1.el7.x86_64                                                                      5/7
  Installing : mysql-community-libs-compat-8.0.27-1.el7.x86_64                                                                 6/7
  Erasing    : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                              7/7
  Verifying  : mysql-community-libs-8.0.27-1.el7.x86_64                                                                        1/7
  Verifying  : mysql-community-common-8.0.27-1.el7.x86_64                                                                      2/7
  Verifying  : mysql-community-client-plugins-8.0.27-1.el7.x86_64                                                              3/7
  Verifying  : mysql-community-server-8.0.27-1.el7.x86_64                                                                      4/7
  Verifying  : mysql-community-client-8.0.27-1.el7.x86_64                                                                      5/7
  Verifying  : mysql-community-libs-compat-8.0.27-1.el7.x86_64                                                                 6/7
  Verifying  : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                              7/7

Installed:
  mysql-community-client.x86_64 0:8.0.27-1.el7                   mysql-community-client-plugins.x86_64 0:8.0.27-1.el7
  mysql-community-common.x86_64 0:8.0.27-1.el7                   mysql-community-libs.x86_64 0:8.0.27-1.el7
  mysql-community-libs-compat.x86_64 0:8.0.27-1.el7              mysql-community-server.x86_64 0:8.0.27-1.el7

Replaced:
  mariadb-libs.x86_64 1:5.5.68-1.el7

Complete!

安装完成，Mysql服务不会自动启动：

[root@mysql-1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

使用RPM安装后，一些资源的默认位置：

文件或资源	位置
客户端程序和脚本	/usr/bin
mysqld 服务	/usr/sbin
配置文件	/etc/my.cnf
数据目录	/var/lib/mysql
错误日志文件	RHEL： /var/log/mysqld.log
secure_file_priv 值	/var/lib/mysql-files
系统服务初始化脚本	RHEL: /etc/init.d/mysqld
系统服务	mysqld
Pid文件	/var/run/mysql/mysqld.pid
Socket	/var/lib/mysql/mysql.sock
Keyring 钥匙环目录	/var/lib/mysql-keyring
用户手册	/usr/share/man
头文件	/usr/include/mysql
库文件	/usr/lib/mysql
其他支持文件(错误消息、角色设置文件等)	/usr/share/mysql

同时，采用RPM安装会自动创建mysql用户和mysql用户组。

3. 启动mysql及初始化

启动服务

systemctl start mysqld

第一次启动服务，会在日志中打印出临时的root密码：

cat /var/log/mysqld.log
2021-12-16T09:10:50.210028Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 75876
2021-12-16T09:10:50.247429Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-16T09:10:50.966990Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-12-16T09:10:52.089170Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-12-16T09:10:52.089191Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-12-16T09:10:52.335377Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: OIwqgfXqw9<x
2021-12-16T09:10:55.430854Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 75923
2021-12-16T09:10:55.450371Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-16T09:10:55.874854Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-12-16T09:10:56.219276Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-12-16T09:10:56.219301Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-12-16T09:10:56.220460Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-12-16T09:10:56.220501Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-12-16T09:10:56.242979Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2021-12-16T09:10:56.243098Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

如上：

A temporary password is generated for root@localhost: OIwqgfXqw9<x

进入mysql数据库，输入以下内容，回车后输入以上默认密码，看到 mysql> 提示符及进入mysql数据库

[root@mysql-1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

此时需要先修改默认root@localhost用户的临时密码，否则无法进行其他操作，会提示：

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

修改默认的密码时，密码默认有复杂度要求，简单密码默认无法设置。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dbwork';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'DBw0Rk!2021';
Query OK, 0 rows affected (0.00 sec)

mysql>

密码验证功能说明：https://dev.mysql.com/doc/refman/8.0/en/validate-password.html 密码至少包含一个大写字母，一个小写字母，一个数字和一个特殊字符，并且密码长度最少为8个字符。

修改完后可以进行其他操作：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

4. 创建库和用户

一般应用系统，应用和数据库分开部署，应用一般采用分布式部署。

需求：

创建数据库 app1
创建可远程访问数据库的用户 user_app1
user_app1拥有数据库app1的全部权限

创建数据库：

CREATE DATABASE app1;

创建用户：

CREATE USER 'user_app1'@'%' IDENTIFIED BY 'App1#Test';

用户授权：

GRANT ALL PRIVILEGES ON app1.* TO 'user_app1'@'%';

% 为host的通配符，* 为数据库和表的通配符。

刷新权限表：

flush privileges;

查看某用户的权限：

show grants for 'user_app1'@'%';

取消授权使用 revoke ... on ... from ... 语句。

YaoYan