Linux下MySQL的安装
1 MySQL的安装
1.1 Linux系统以及工具的准备
这里使用两台CentOS7虚拟机，一台安装8.0版本，另一台克隆的虚拟机安装5.7版本
克隆的虚拟机需要进行配置修改：
mac地址
主机名
cd /etc/hostname hadoop101ip地址
vim /etc/sysconfig/network-scripts/ifcfg-ens33 UUID="7d88feef-895e-488f-85f6-ece3f5193a73" DEVICE="ens33" ONBOOT="yes" IPADDR=192.168.60.101uuid
UUID="7d88feef-895e-488f-85f6-ece3f5193a73" DEVICE="ens33" ONBOOT="yes" IPADDR=192.168.60.101
1.2 安装包抽取
MySQL社区版网址：https://downloads.mysql.com/archives/community/，选择版本和红帽系操作系统Linux7，下载RPM Bundle离线版本，然后抽取其中的四个rpm上传到服务器进行安装：
-rw-r--r--. 1 root root 45109364 1月 5 20:44 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 318768 1月 5 20:44 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 4374364 1月 5 20:44 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 208694824 1月 5 20:44 mysql-community-server-5.7.28-1.el7.x86_64.rpm
1.3 CentOS7下检查MySQL依赖环境
① 检查/tmp临时目录权限（必不可少）
在MySQL安装过程中，会通过MySQL用户在目录/tmp下新建tmp_db文件，所以需要给/tmp足够大的权限：
chmod -R 777 /tmp
②检查libaio依赖
[root@hadoop101 opt]# rpm -qa | grep libaio
libaio-0.3.109-13.el7.x86_64
③检查net-tools依赖
[root@hadoop101 opt]# rpm -qa | grep net-tools
net-tools-2.0-0.25.20131004git.el7.x86_64
1.4 将下载的安装包上传至/opt目录然后顺序执行安装
需要使用rpm -ivh 命令按照如下的顺序进行安装：
- mysql-community-common-5.7.28-1.el7.x86_64.rpm
- mysql-community-client-plugins.rpm（高版本mysql会有这一个）
- mysql-community-libs-5.7.28-1.el7.x86_64.rpm
- mysql-community-client-5.7.28-1.el7.x86_64.rpm
- mysql-community-server-5.7.28-1.el7.x86_64.rpm
执行第三条的时候可能会出现错误：
[root@hadoop101 opt]# rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
错误：依赖检测失败：
mysql-community-common(x86-64) >= 5.7.9 被 mysql-community-libs-5.7.28-1.el7.x86_64 需要
mariadb-libs 被 mysql-community-libs-5.7.28-1.el7.x86_64 取代
这时候需要执行下面语句，执行完再次运行上面即可
yum remove mysql-libs
1.5 查看是否安装成功
① 查看版本号
[root@hadoop101 opt]# mysql --version
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper
② rpm查看安装依赖：
[root@hadoop101 opt]# rpm -qa | grep mysql
mysql-community-server-5.7.28-1.el7.x86_64
mysql-community-common-5.7.28-1.el7.x86_64
mysql-community-libs-5.7.28-1.el7.x86_64
mysql-community-client-5.7.28-1.el7.x86_64
1.6 服务初始化
为了保证mysql目录与文件的所有者为mysql登录用户，如果是以root身份运行mysql服务的，需要执行下面的初始化语句
mysqld --initialize --user=mysql
--initialize表示以安全模式来初始化，则会为root用户生成一个密码，并将该密码标记为过期，登录之后需要用户设置新的密码，生成的临时密码会往日志中记录一份。
查看密码：
[root@hadoop101 opt]# cat /var/log/mysqld.log
2023-01-06T05:07:46.462818Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-01-06T05:07:47.050060Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-01-06T05:07:47.158678Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-01-06T05:07:47.182640Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0f62f16d-8d80-11ed-acba-000c29c698c1.
2023-01-06T05:07:47.183419Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-01-06T05:07:47.793260Z 0 [Warning] CA certificate ca.pem is self signed.
2023-01-06T05:07:47.930913Z 1 [Note] A temporary password is generated for root@localhost: h;Q/>jrTQ3uk
1.7 服务启动并查看状态
[root@hadoop101 mysql]# systemctl start mysqld
[root@hadoop101 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 四 2023-01-05 21:25:02 PST; 49s ago
启动过程中出现了一个错误，经过查看/var/log/mysqld.log日志发现是由于是克隆的虚拟机，所以存在另一个mysql服务占用了3306端口，在docker中停止然后删除该容器再执行即可
Job for mysqld.service failed because the control process exited with error
1.8 设置自启动
首先查看服务是否为自启动
[root@hadoop101 mysql]# systemctl list-unit-files | grep mysqld
mysqld.service enabled
[email protected] disabled
如果mysqld.service是disable，则执行下面语句设置自启动
[root@hadoop101 mysql]# systemctl list-unit-files | grep mysqld
mysqld.service enabled
[email protected] disabled
[root@hadoop101 mysql]# systemctl disable mysqld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
[root@hadoop101 mysql]# systemctl list-unit-files | grep mysqld.service
mysqld.service disabled
[root@hadoop101 mysql]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@hadoop101 mysql]# systemctl list-unit-files | grep mysqld.service
mysqld.service enabled
1.9 使用临时密码登录mysql
[root@hadoop101 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28
进行密码修改，否则无法进行任何操作
mysql> alter user 'root'@'localhost' identified by '321Onlyyou';
1.10 使用远程连接工具连接Mysql
直接连接可用看到报1130不允许连接mysql服务器的错误，首先对问题进行排查：
- ping 192.168.60.100 检查虚拟机网络是否畅通
- telnet 192.168.60.101 3306 检查端口号是否畅通
然后第二步出现：
EHost '192.168.60.1' is not allowed to connect to this MySQL server
遗失对主机的连接。
出现这个的原因是windows和linux防火墙造成的，解决的思路有两个
-
关闭防火墙
[root@hadoop101 mysql]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) [root@hadoop101 mysql]# systemctl stop firewalld -
开放端口号
略
然后发现还是连接不上，这其实还有一个用户连接端口设置的问题，首先查看mysql自带的数据库表：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
然后查看mysql数据库中的user表，其中host就代表可用使用user连接的地址
mysql> use mysql;
mysql> select host,user from user
-> ;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
对连接限制的地址进行修改并更新权限：
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
此时就能连接成功了
1.11 安装问题记录
2059 Navcicat编码问题
mysql -uroot -p
password #登录
use mysql; #选择数据库
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的mysql密码' PASSWORD EXPIRE NEVER; #更改加密方式
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新用户密码
FLUSH PRIVILEGES; #刷新权限