二进制部署MySQL8.0

一、服务部署

1、下载MySQL官方包

## 下载MySQL
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

2、解压并移动安装包

# 解压 tar -zxvf 待解压目录
[root@localhost ~]# tar xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

# 移动 mv 移动目录 移动后的目录
[root@localhost ~]# mv mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

3、创建数据目录

# 进入MySQL目录
[root@localhost ~]# cd /usr/local/mysql

# 创建data文件夹(也可以根据那个磁盘大而定--df -Th查看)
[root@localhost mysql]# mkdir data

4、创建mysql用户权限

# 创建msyql组
[root@localhost mysql]# groupadd mysql

# 创建mysql用户并添加到mysql组中,-r表示mysql是系统级用户，-g是所属组为mysql
[root@localhost mysql]# useradd -r -g mysql mysql

# 修改mysql目录的组以及权限
[root@localhost mysql]# chown mysql:mysql -R ./

5、修改MySQL配置文件

[root@localhost mysql]# vi /etc/my.cnf

[mysqld]
## 用于指定 MySQL 服务器绑定的网络接口地址，127.0.0.1代表只能本地访问
bind-address = 127.0.0.1
port = 3306
user = mysql

# mysql解压目录
basedir = /usr/local/mysql

# 数据存放目录
datadir = /usr/local/mysql/data

# 跳过密码验证
# skip-grant-tables

# socker文件存放目录
socket = /tmp/mysql.sock

# 错误日志
log-error = /usr/local/mysql/data/mysql.error

# mysql进程号
pid-file = /usr/local/mysql/data/mysql.pid

# 日志记录慢查询SQL
slow_query_log = true
slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.log
long_query_time = 1

# 记录没有使用index的查询记录
log-queries-not-using-indexes

# 配置mysqlbinlog
server-id = 1
log-bin = mysql-bin
binlog_format = MIXED
gtid-mode = on
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
# 并行复制工作线程数
slave-parallel-workers = 4

# 设置binlog过期时间，过期自动删除 binlog_expire_logs_seconds单位是秒
binlog_expire_logs_seconds = 259200
max_binlog_size = 50M
log_bin_trust_function_creators = 1

# 设置sql_mode行为规则
sql_mode = 'NO_ENGINE_SUBSTITUTION'

6、初始化MySQL

# 初始化  --defaults-file为配置文件所在 ,--basedir为解压路径,--datadir为数据保存路径 --user表示MySQL所属用户
[root@localhost mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql --initialize

## 如果执行完初始化没有报错，复制MySQL启动脚本到/etc/init.d/目录便于使用service mysql start启动
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysql

7、查看密码

## 初始化MySQL是会生产一个临时密码，可以通过查看在my.cnf中配置的log-error的配置文件知晓。
[root@localhost mysql]# cat data/mysql.error
root@localhost: oK)CZs*bg9zw   ##通常最后一行

8、设置systemd启动文件

[root@localhost mysql]# vim /usr/lib/systemd/system/mysql.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld()
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
 
[Install]
WantedBy=multi-user.target
 
[Service]
User=mysql
Group=mysql
PIDFile=/usr/local/mysql/data/mysqld.pid
 
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=
 
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
 
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid
#注意这里要加上 --daemonize
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql
 
# Sets open_files_limit
LimitNOFILE = 
Restart=on-failure
RestartPreventExitStatus=
PrivateTmp=false

9、启动服务

## 开机自启动
[root@localhost mysql]# systemctl enable --now mysql.service

10、设置环境变量

[root@localhost mysql]# vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin		## 最后一行添加
[root@localhost mysql]# source /etc/profile

11、修改密码

## 登入MySQL
[root@localhost mysql]# mysql -uroot -p
Enter password: 	## mysql.error获取的临时密码: 2yhm?>?s7=iJ

## 修改密码
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> set password for root@localhost = '123456';

## 设置MySQL远程登入
mysql> use mysql;
msyql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
mysql> exit

## 把mysql用户登录密码加密规则还原成mysql_native_password
[root@localhost mysql]# mysql -uroot -p123456
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> FLUSH PRIVILEGES;

12、开放相关端口

[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@localhost mysql]# firewall-cmd --reload