CentOS7.9环境下安装mysql-8.0.32详解

1. 设置主机名

# 设置主机名：
[root@192 ~]# hostnamectl set-hostname db && bash

2. 卸载残留

# 卸载软件残留
[root@db scripts]# yum remove mariadb* -y

3. 安装包准备

1.上传软件包
# 下载地址：https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
上传安装包至/apps/scripts目录下

2.解压及更名
[root@db scripts]# tar -xf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz -C /apps/
[root@db scripts]# cd ..
[root@db apps]# mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql
[root@db apps]# cd mysql

4. 创建用户、组

# 创建用户
[root@db mysql]# groupadd mysql -g 666
[root@db mysql]# useradd mysql -g 666 -u 666 -r -M -s /sbin/nologin

5. 创建配置文件

# 创建配置文件
[root@db mysql]# vim my.cnf 
[client]
port=3306
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysql]
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysqld]
port=3306
character-set-server=utf8mb4
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
basedir=/apps/mysql
datadir=/apps/mysql/data
pid-file=/apps/mysql/data/mysqld.pid
user=mysql
slow_query_log=OFF
lower_case_table_names=1
socket=/apps/mysql/data/mysql.sock
default-storage-engine=InnoDB
max_connections=600
max_connect_errors=1000
tmp_table_size=1024M
max_heap_table_size=1024M
join_buffer_size=1024M
thread_cache_size=32
key_buffer_size=256M
read_buffer_size=1M
read_rnd_buffer_size=128M
default_authentication_plugin=caching_sha2_password
sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
max_allowed_packet=256M
innodb_log_file_size=256M
innodb_log_buffer_size=512M
innodb_buffer_pool_size=1638M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_file_per_table=1
innodb_page_size=16K
group_concat_max_len=1024000
transaction-isolation=READ-COMMITTED
default-time-zone='+8:00'

# mysql 主从相关设置 - 主库
server-id=150
log_bin=db-mysql-bin
binlog_format=row
binlog_expire_logs_seconds=86400
replicate_wild_do_table=oadb.%
auto-increment-offset=1
auto-increment-increment=2
sync_binlog=1
gtid_mode=ON
enforce_gtid_consistency=ON
relay-log=db-relay-bin


# 安全加固
local-infile=0
log_timestamps=system
max_connect_errors=1000

[mysqld_safe]
log-error=/apps/mysql/logs/error.log

[mysql.server]
basedir=/apps/mysql

6. 创建数据目录

# 创建数据目录
[root@db mysql]# mkdir {data,logs}
[root@db mysql]# pwd
/apps/mysql

7. 安装包准备

# 授权
[root@db mysql]# chown -R mysql:mysql /apps/mysql/

8. 依赖安装

# 安装MySQL依赖软件
[root@db mysql]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ numactl libaio glibc cmake autoconf

9. 初始化数据库

# 初始化数据库
[root@db mysql]# bin/mysqld --initialize --user=mysql --basedir=/apps/mysql --datadir=/apps/mysql/data --lower_case_table_names=1
# --lower_case_table_names=1在初始化时候，添加该参数是因为在my.cnf中存在该参数，不加会报错

# A temporary password is generated for root@localhost: 3X(G>W0kdqBm
# 3X(G>W0kdqBm为自动生成的root密码，需要记录

10. 安装包准备

# 注册MySQL启动服务
[root@db mysql]# vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Start main service
ExecStart=/apps/mysql/support-files/mysql.server start
# Sets open_files_limit
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false

# 需修改/apps/mysql/support-files/mysql.server文件中basedir和datadir，其他内容无需修改
[root@db mysql]# vim /apps/mysql/support-files/mysql.server
basedir=/apps/mysql
datadir=/apps/mysql/data

# 重载服务
[root@db mysql]# systemctl daemon-reload
# 启动mysql服务
[root@db mysql]# systemctl start mysqld

11. 测试连接

# 测试连接
[root@db mysql]# bin/mysql -h127.0.0.1 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32
Copyright (c) 2000, 2023, 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> 


# 默认密码修改
mysql> alter user root@localhost identified by 'Abc123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

12. 修改环境变量

# 添加环境变量
[root@db mysql]# vim /etc/profile
# 在最后添加以下两行
export MYSQL_HOME=/apps/mysql
export PATH=$PATH:$MYSQL_HOME/bin

# 重新加载配置
[root@db mysql]# source /etc/profile

# 切换目录测试环境变量是否添加成功
[root@db mysql]# cd /
[root@db /]# mysql -h127.0.0.1 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

13. 相关操作

# 创建数据库
[root@db /]# mysql -h127.0.0.1 -uroot -p
mysql> CREATE DATABASE sql_test DEFAULT CHARACTER SET UTF8MB4;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sql_test             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 允许其他主机连接该数据库
mysql> use mysql;
mysql> UPDATE user SET Host = '%' WHERE User = 'root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 导入数据
[root@db scripts]# mysql -h127.0.0.1 -uroot -pAbc123 sql_test < ./sql_test.sql

# 防火墙开放3306端口
[root@db scripts]# firewall-cmd --zone=public --list-ports
[root@db scripts]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
[root@db scripts]# firewall-cmd --reload