Linux—MySql安装

1、概念简介

目前MySql 的安装包分为 5.x 版本和 8.x版本；具体区别是什么可以查阅官方文档：
https://dev.mysql.com/doc/refman/5.7/en/

2、部署安装

2.1、前置准备

1）用户创建

groupadd gdsgrp 
useradd gpadmin -r -m -g gdsgrp

• 路径规划

/software/MySql8/dataDir #数据文件路径
/software/MySql8/logDir  #日志文件路径
/software/MySql8/tmpDir  #临时文件路径

2）安装包下载
下载地址：https://www.mysql.com/downloads/

可以下载社区版本，也可以直接下载

3）上传解压

上传包 mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz到/software 路径下，如下操作在 /software 目录执行；

tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.37-linux-glibc2.12-x86_64 MySql

mkdir -p /software/MySql/{dataDir,logDir,tmpDir}

2.2、安装配置

0）用户变量

vim /home/os_user/.bash_profile
配置文件末尾添加如下记录：
export PATH=/software/MySql8/bin:$PATH

生效配置文件：soure /home/os_user/.bash_profile

1）库初始化
bin/mysqld --initialize --user=gdsuser --basedir=/software/MySql --datadir=/software/MySql/dataDir

此处执行成功后会生成一个root用户的临时密码，如果前台日志信息没有输出，可以查看 /software/MySql8/logDir/mysql-err.log。

2）参数配置(root用户执行)

在这里有些版本support-files没有mysql-default.cnf文件，需要自己新建，然后编辑该文件；

vim /etc/my.cnf

[client]
port=3306
socket=/software/MySql/tmpDir/mysql.sock

[mysqld]
basedir=/software/MySql
datadir=/software/MySql/dataDir
tmpdir=/software/MySql/tmpDir
socket=/software/MySql/tmpDir/mysql.sock
user=gdsuser
port=3306
log-error=/software/MySql/logDir/mysql_err.log

skip-external-locking
skip-name-resolve=1
max_allowed_packet = 16M
character-set-server = utf8mb4

transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp=1
log_timestamps=system
lower_case_table_names=1
secure_file_priv=''



## binlog set
sync_binlog=1
max_binlog_size = 1024M
binlog_cache_size = 4M
expire-logs-days = 30 
binlog_format =ROW 

## GTID set
enforce_gtid_consistency = on
gtid_mode = on

3）开机自启(root用户执行)

cp ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

4）服务启停

service mysql start

service mysql status

service mysql stop

2.3、连接访问

• 命令连接

mysqld --verbose --help


mysql -P 3306 -u root -p
mysql -P3306 -uroot -p

说明：此处首次登录 mysql 使用 root用户(mysql自带的管理元用户，不是操作系统用户)；

• 基本命令

# 列出所有数据库
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> quit;

说明：mysql在安装完成后会有如上4个初始化数据库；

• 修改密码

临时密码(mimtemppassword)：gNefL8Q:#a9j

mysql> 
mysql> set password=password("new_password");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 

如果以上方法不行，可以如下操作：
alter user 'root'@'localhost' identified by 'new_passwd';

• 建库建用户

mysql> 
mysql> create user sjjc_dsj identified by 'user_pwd';
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> 
mysql> create database sj_dsj;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on sj_dsj to sjjc_dsj;

2.4、异常问题

报错信息1：

[hive@virtualServer-A ~]$ 
[hive@virtualServer-A ~]$ service mysql start
Starting MySQL.. ERROR! The server quit without updating PID file (/software/MySql8/dataDir/virtualServer-A.pid).
[hive@virtualServer-A ~]$ 

说明：此处报错原因有很多，网络上各种文件路径执行权限以及my.cnf配置问题等等，试了都无法解决；

实际查看日志 /software/MySql8/logDir/mysql_err.log文件，里边有如下2行关键错误信息：

[hive@virtualServer-A logDir]$ 
[hive@virtualServer-A logDir]$ cat mysql_err.log 
2023-10-11T06:22:13.557973Z mysqld_safe Logging to '/software/MySql8/logDir/mysql_err.log'.
2023-10-11T06:22:13.586784Z mysqld_safe Starting mysqld daemon with databases from /software/MySql8/dataDir
2023-10-11T14:22:13.767541+08:00 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2023-10-11T14:22:13.767556+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2023-10-11T14:22:13.767606+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2023-10-11T14:22:13.767666+08:00 0 [System] [MY-010116] [Server] /software/MySql8/bin/mysqld (mysqld 8.0.34) starting as process 4755
2023-10-11T14:22:13.778751+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-11T14:22:14.302691+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-11T14:22:14.306685+08:00 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-10-11T14:22:14.306842+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-10-11T14:22:14.306857+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-11T14:22:14.830780+08:00 0 [System] [MY-010910] [Server] /software/MySql8/bin/mysqld: Shutdown complete (mysqld 8.0.34)  MySQL Community Server - GPL.
2023-10-11T06:22:14.846791Z mysqld_safe mysqld from pid file /software/MySql8/dataDir/virtualServer-A.pid ended
[hive@virtualServer-A logDir]$ 

查询资料后发现mysql8.0 后新增了一个data dictionary的概念，数据库初始化和服务启动必须参数一致；默认数据库初始化时 lower-case-table-names=0，与服务启动时读取的 /etc/my.cnf 配置的参数不一致；

• 解决方式
  在初始化参数中定义 lower-case-table-names=1，即不区分大小写；

bin/mysqld --initialize --lower-case-table-names=1 --user=hive --basedir=/software/MySql8 --datadir=/software/MySql8/dataDir 

============================================== over ==============================================