一、下载软件

1.下载安装

官网地址：https://dev.mysql.com/downloads/

选择 "MySQL Community Server" -> 选择 "Archives"（历史版本）-> 选择版本（5.7.40）、系统（Linux - Generic）

mkdir /usr/local/download
mkdir /usr/local/software

cd /usr/local/download
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz

tar -zxvf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
cp -r mysql-5.7.40-linux-glibc2.12-x86_64 /usr/local/software/mysql5.7

2.创建用户组

groupadd mysql
useradd -r -g mysql mysql

chown -R mysql /usr/local/software/mysql5.7
chgrp -R mysql /usr/local/software/mysql5.7

touch /www/log/mysql/mysqld.log # 新建空白文件
chown mysql:mysql /www/log/mysql/mysqld.log # 指定文件所有者

touch /www/log/mysql/mysqld_slow.log # 新建空白文件
chown mysql:mysql /www/log/mysql/mysqld_slow.log # 指定文件所有者

3.创建数据目录

mkdir -p /usr/local/software/mysql5.7/data 
chown mysql:mysql -R /usr/local/software/mysql5.7/data 
chmod -R 777 /usr/local/software/mysql5.7/data

4.创建配置文件

vim /etc/my.cnf

[mysqld]
user=mysql

basedir=/usr/local/software/mysql5.7
datadir=/usr/local/software/mysql5.7/data
socket=/tmp/mysql.sock
pid-file=/usr/local/software/mysql5.7/data/mysqld.pid

# 重新指定日志路径
log-error=/www/log/mysql/mysqld.log
port=3306 
bind-address=127.0.0.1 

# character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

# 慢日志记录
slow_query_log=1
long_query_time=3
slow_query_log_file=/www/log/mysql/mysqld_slow.log

# skip-grant-tables

5.初始化数据库

yum install -y libaio.so.1 libaio numactl

cd /usr/local/software/mysql5.7/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/software/mysql5.7/ --datadir=/usr/local/software/mysql5.7/data/ --user=mysql --initialize

# 创建 pid 
touch /usr/local/software/mysql5.7/data/mysqld.pid
chmod 777 /usr/local/software/mysql5.7/data/mysqld.pid

6.设置开机启动

cp /usr/local/software/mysql5.7/support-files/mysql.server /etc/init.d/mysqld57

chkconfig --add mysqld57
chkconfig mysqld57 on

service mysqld57 start
service mysqld57 stop
service mysqld57 restart

systemctl start mysqld57.service
systemctl stop mysqld57.service
systemctl restart mysqld57.service

7.创建软链接

ln -s  /usr/local/software/mysql5.7/bin/mysql /usr/bin/mysql
ln -s  /usr/local/software/mysql5.7/bin/mysqldump /usr/bin/mysqldump # 备份数据库

8.用户登录

# 查找初始密码
cat /www/log/mysql/mysqld.log | grep password
2023-04-23T04:43:28.176816Z 1 [Note] A temporary password is generated for root@localhost: l5%sOhmiVpdV

# 登录
mysql -u root -p 

二、配置文件

1.修改 mysql 密码

vim /etc/my.cnf

# 打开配置文件，在 [mysqld] 后面添加新行
skip-grant-tables

service mysqld restart # 重启 mysqld

# 执行命令：
mysql -uroot -p

select user, host, authentication_string  from mysql.user; # 查看用户
alter user 'root'@'localhost' identified by '123456'; # 修改密码

flush privileges;
quit;

# 退出后删除 my.cnf 文件中 skip-grant-tables 这一行，并重启 mysqld

2.密码简单报错处理

alter user 'root'@'localhost' identified by '123456'; # 再次执行修改密码

set global validate_password_policy=0; # 首先修改 validate_password_policy 参数 
set global validate_password_length=1; # 再修改密码的长度

# 如果 mysql 只需要 ssh 连接，可以不创建 root@% 用户，只使用默认的 root@localhost 即可
select user, host, authentication_string  from mysql.user; # 显示当前所有用户
create user 'root'@'%' identified by '123456'; # 创建用户 root@%
update user set host = '%' where user ='root'; # 允许任何 ip 链接

flush privileges;

三、数据库用户

1.查看用户状态

mysql -h172.16.0.8 -uroot -P3306 -p # 用户登录

show grants for 'root'@'localhost'; # 查看某个用户授权
select user,host from mysql.user; # 显示当前所有用户

2.创建数据库

show databases;
create database blog default charset utf8mb4;
drop database blog;

3.添加 mysql 用户

create user 'test'@'localhost' identified by '123456'; # 创建用户

grant all privileges on *.* to 'test'@'localhost' identified by '123456'; # 赋予数据库权限
grant all privileges on `blog`.* to 'test'@'localhost' identified by '123456'; # 赋予 blog 数据库权限
grant create,drop,index,alter,select,insert,update,delete on `blog`.* to 'test'@'localhost' identified by '123456'; # 赋予 blog 数据库部分权限

revoke all privileges on `blog`.* from 'test'@'localhost'; # 回收所有权限
revoke delete on `blog`.* from 'test'@'localhost'; # 回收 delete 权限
flush privileges; # 刷新权限

4.删除用户

revoke all privileges on `blog`.* from 'test'@'localhost';
delete from mysql.user where user='test' and host='localhost';
flush privileges;
drop user 'test'@'localhost'; 

5.修改密码

select user, host, authentication_string  from mysql.user; # 查看用户
alter user 'root'@'localhost' identified by '123456'; # 修改密码

四、报错处理

1.Couldn't agree a key exchange algorithm (available: curve25519-sha256@libssh.org, ecdh-sha2-nistp521,ecdh-sha2-nistp384,ecdh-sha2-nistp256)

vim /etc/ssh/sshd_config

# 注释最后一行并添加
Ciphers aes128-ctr,aes192-ctr,aes256-ctr
# KexAlgorithms curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256

KexAlgorithms curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group1-sha1,diffie-hellman-group14-sha1,diffie-hellman-group-exchange-sha1

# 重启服务
systemctl restart sshd