linux-安装mysql服务机，并在客户机上验证
2022-11-30 本文已影响0人
Tess鱼丸
[postgres@dns ~]$ su - root
密码：
[root@dns ~]#
[root@dns ~]#
[root@dns ~]# dnf install mysql-devel -y
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：1:14:44 前，执行于 2022年12月01日 星期四 08时10分25秒。
依赖关系解决。
============================================================================================
软件包 架构 版本 仓库 大小
============================================================================================
安装:
mysql-devel x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 147 k
安装依赖关系:
keyutils-libs-devel x86_64 1.5.10-6.el8 BaseOS 48 k
krb5-devel x86_64 1.17-18.el8 BaseOS 549 k
libcom_err-devel x86_64 1.45.4-3.el8 BaseOS 38 k
libkadm5 x86_64 1.17-18.el8 BaseOS 185 k
libselinux-devel x86_64 2.9-3.el8 BaseOS 199 k
libsepol-devel x86_64 2.9-1.el8 BaseOS 86 k
libverto-devel x86_64 0.3.0-5.el8 BaseOS 18 k
mariadb-connector-c-config noarch 3.0.7-1.el8 AppStream 13 k
mysql-common x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 143 k
mysql-libs x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 1.2 M
openssl-devel x86_64 1:1.1.1c-15.el8 BaseOS 2.3 M
pcre2-devel x86_64 10.32-1.el8 BaseOS 605 k
pcre2-utf16 x86_64 10.32-1.el8 BaseOS 229 k
pcre2-utf32 x86_64 10.32-1.el8 BaseOS 220 k
zlib-devel x86_64 1.2.11-13.el8 BaseOS 57 k
启用模块流:
mysql 8.0
事务概要
============================================================================================
安装 16 软件包
总计：6.0 M
安装大小：16 M
下载软件包：
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : zlib-devel-1.2.11-13.el8.x86_64 1/16
安装 : mariadb-connector-c-config-3.0.7-1.el8.noarch 2/16
安装 : mysql-common-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 3/16
安装 : mysql-libs-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 4/16
安装 : pcre2-utf32-10.32-1.el8.x86_64 5/16
安装 : pcre2-utf16-10.32-1.el8.x86_64 6/16
安装 : pcre2-devel-10.32-1.el8.x86_64 7/16
安装 : libverto-devel-0.3.0-5.el8.x86_64 8/16
安装 : libsepol-devel-2.9-1.el8.x86_64 9/16
安装 : libselinux-devel-2.9-3.el8.x86_64 10/16
安装 : libkadm5-1.17-18.el8.x86_64 11/16
安装 : libcom_err-devel-1.45.4-3.el8.x86_64 12/16
安装 : keyutils-libs-devel-1.5.10-6.el8.x86_64 13/16
安装 : krb5-devel-1.17-18.el8.x86_64 14/16
安装 : openssl-devel-1:1.1.1c-15.el8.x86_64 15/16
安装 : mysql-devel-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 16/16
运行脚本: mysql-devel-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 16/16
验证 : keyutils-libs-devel-1.5.10-6.el8.x86_64 1/16
验证 : krb5-devel-1.17-18.el8.x86_64 2/16
验证 : libcom_err-devel-1.45.4-3.el8.x86_64 3/16
验证 : libkadm5-1.17-18.el8.x86_64 4/16
验证 : libselinux-devel-2.9-3.el8.x86_64 5/16
验证 : libsepol-devel-2.9-1.el8.x86_64 6/16
验证 : libverto-devel-0.3.0-5.el8.x86_64 7/16
验证 : openssl-devel-1:1.1.1c-15.el8.x86_64 8/16
验证 : pcre2-devel-10.32-1.el8.x86_64 9/16
验证 : pcre2-utf16-10.32-1.el8.x86_64 10/16
验证 : pcre2-utf32-10.32-1.el8.x86_64 11/16
验证 : zlib-devel-1.2.11-13.el8.x86_64 12/16
验证 : mariadb-connector-c-config-3.0.7-1.el8.noarch 13/16
验证 : mysql-common-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 14/16
验证 : mysql-devel-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 15/16
验证 : mysql-libs-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 16/16
Installed products updated.
已安装:
keyutils-libs-devel-1.5.10-6.el8.x86_64
krb5-devel-1.17-18.el8.x86_64
libcom_err-devel-1.45.4-3.el8.x86_64
libkadm5-1.17-18.el8.x86_64
libselinux-devel-2.9-3.el8.x86_64
libsepol-devel-2.9-1.el8.x86_64
libverto-devel-0.3.0-5.el8.x86_64
mariadb-connector-c-config-3.0.7-1.el8.noarch
mysql-common-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-devel-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-libs-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
openssl-devel-1:1.1.1c-15.el8.x86_64
pcre2-devel-10.32-1.el8.x86_64
pcre2-utf16-10.32-1.el8.x86_64
pcre2-utf32-10.32-1.el8.x86_64
zlib-devel-1.2.11-13.el8.x86_64
完毕！
[root@dns ~]#
[root@dns ~]# dnf install mysql-server -y
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：1:15:57 前，执行于 2022年12月01日 星期四 08时10分25秒。
依赖关系解决。
============================================================================================
软件包 架构 版本 仓库 大小
============================================================================================
安装:
mysql-server x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 22 M
安装依赖关系:
mecab x86_64 0.996-1.module+el8.0.0+3898+e09bb8de.9 AppStream 397 k
mysql x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 11 M
mysql-errmsg x86_64 8.0.17-3.module+el8.0.0+3898+e09bb8de AppStream 557 k
protobuf-lite x86_64 3.5.0-7.el8 AppStream 149 k
事务概要
============================================================================================
安装 5 软件包
总计：33 M
安装大小：216 M
下载软件包：
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : protobuf-lite-3.5.0-7.el8.x86_64 1/5
安装 : mysql-errmsg-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 2/5
安装 : mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 3/5
安装 : mecab-0.996-1.module+el8.0.0+3898+e09bb8de.9.x86_64 4/5
运行脚本: mecab-0.996-1.module+el8.0.0+3898+e09bb8de.9.x86_64 4/5
运行脚本: mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 5/5
安装 : mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 5/5
运行脚本: mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 5/5
ValueError: /var/log/mysql(/.*)? 的文件上下文已定义
验证 : mecab-0.996-1.module+el8.0.0+3898+e09bb8de.9.x86_64 1/5
验证 : mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 2/5
验证 : mysql-errmsg-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 3/5
验证 : mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64 4/5
验证 : protobuf-lite-3.5.0-7.el8.x86_64 5/5
Installed products updated.
已安装:
mecab-0.996-1.module+el8.0.0+3898+e09bb8de.9.x86_64
mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-errmsg-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
protobuf-lite-3.5.0-7.el8.x86_64
完毕！
[root@dns ~]#
检查已安装的mysql
[root@dns ~]# rpm -qa |grep mysql
mysql-libs-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-errmsg-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-common-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-devel-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
mysql-server-8.0.17-3.module+el8.0.0+3898+e09bb8de.x86_64
[root@dns ~]#
编辑配置文件/etc/my.cnf.d/mysql-server.cnf
设置服务器端字符格式及引擎：
在[mysqld]下添加
character_set_server=utf8
default-storage-engine=INNODB
修改好之后存盘退出
[root@dns ~]# vim /etc/my.cnf.d/mysql-server.cnf
查看mysqld服务的状态，发现没有启动
启动mysqld服务，再次查看状态，发现已启动
[root@dns my.cnf.d]# systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabl>
Active: inactive (dead)
[root@dns my.cnf.d]#
[root@dns my.cnf.d]# systemctl start mysqld
[root@dns my.cnf.d]#
[root@dns my.cnf.d]#
[root@dns my.cnf.d]# systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabl>
Active: active (running) since Thu 2022-12-01 09:50:40 EST; 4s ago
Process: 12633 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUC>
Process: 12506 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exite>
Process: 12481 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCE>
Main PID: 12590 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 11160)
Memory: 437.8M
CGroup: /system.slice/mysqld.service
└─12590 /usr/libexec/mysqld --basedir=/usr
12月 01 09:50:36 dns.long90.cn.10.168.192.in-addr.arpa systemd[1]: Starting MySQL 8.0 data>
12月 01 09:50:36 dns.long90.cn.10.168.192.in-addr.arpa mysql-prepare-db-dir[12506]: Initia>
12月 01 09:50:40 dns.long90.cn.10.168.192.in-addr.arpa systemd[1]: Started MySQL 8.0 datab>
[root@dns my.cnf.d]#
设置mysqld服务开机自动启动
[root@dns my.cnf.d]# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@dns my.cnf.d]#
登录MySQL进行数据库操作
使用命令：mysql -u root -p
使用root用户登录mysql，这个root不是RHEL8用户，是
MySQL数据库的超级用户，第一次进去没有密码，密码输入处：直接敲回
车。出现“mysql>”提示符，表示成功登录数据库，可以去设置密码。
[root@dns my.cnf.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, 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>
设置root 密码
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> exit
[root@dns my.cnf.d]#
退出数据库再用root 123456密码登录
进入数据库后，查看MySQL的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)
mysql>
查询有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
打开mysql数据库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
查看mysql数据库中所有表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
33 rows in set (0.01 sec)
mysql>
创建数据库mytest
mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)
mysql>
再次查询有哪些数据库，发现多了数据库mytest
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
在mytest数据库中，创建表employee
mysql> use mytest
Database changed
mysql>
mysql> create table employee(
-> no char(20),
-> name char(30),
-> gender char(2)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
查看表employee的表结构
mysql> desc employee;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| no | char(20) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
查看所有表，目前mytest数据库中只有表employee
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| employee |
+------------------+
1 row in set (0.00 sec)
mysql>
打开mysql 数据库，查看user表情况，
发现root用户只支持本地访问，不允许远程访问
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
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)
mysql>
修改user表使其root用户可以通过远程连接
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
查看root用户
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql>
mysql> exit
查看防火墙上允许的端口，3306端口不在允许列表中
[root@dns my.cnf.d]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens160
sources:
services: cockpit dhcpv6-client dns ftp http postgresql ssh
ports:
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
[root@dns my.cnf.d]#
在防火墙上允许的端口3306
[root@dns my.cnf.d]# firewall-cmd --zone=public --permanent --add-port=3306/tcp
success
[root@dns my.cnf.d]#
[root@dns my.cnf.d]# firewall-cmd --reload
success
[root@dns my.cnf.d]# ^C
[root@dns my.cnf.d]#
[root@dns my.cnf.d]#
[root@dns my.cnf.d]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens160
sources:
services: cockpit dhcpv6-client dns ftp http postgresql ssh
ports: 3306/tcp
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
[root@dns my.cnf.d]#
[root@dns my.cnf.d]# firewall-cmd --list-port
3306/tcp
[root@dns my.cnf.d]#
服务端已经配置完了，接下来就是到客户端验证了
在客户端安装mysql 并登录验证
[root@localhost ~]# mysql -h 192.168.10.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, 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>
