cat /etc/redhat-release
cat /proc/version
#1,关闭selinux功能
sed -i -e '/^SELINUX=enable/c SELINUX=disabled' /etc/selinux/config
#2,查看系统是否安装mariadb并卸载(防止mysql与mariadb的文件发生冲突)
查询 rpm -qa | grep mariadb
删除 rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
#3,将下载好的压缩包上传至服务器
cd /usr/local/src
tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
cd /usr/local/
mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql
#4,进入mysql文件夹,创建date,log目录(data和log目录根据自己的磁盘情况而定)
cd mysql
mkdir data log
touch ./log/mysql.log
#5,创建MySQL用户和组并修改权限
groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql
#6,创建my.cnf文件
vim /etc/my.cnf
[client]
port = 3306
#根据实际情况调整mysql.sock配置
socket = /tmp/mysql.sock
[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
#服务端口号 默认3306
port = 3306
#mysql安装根目录
basedir = /usr/local/mysql
#mysql数据文件所在位置
datadir = /usr/local/mysql/data
#pid
pid-file = /usr/local/mysql/mysql.pid
#设置socke文件所在目录
socket = /tmp/mysql.sock
#设置临时目录
tmpdir = /tmp
# 用户
user = mysql
# 允许访问的IP网段
bind-address = 0.0.0.0
#错误日志
log_error=/usr/local/mysql/log/mysql.log
#设置认证插件
default_authentication_plugin=mysql_native_password
#设置sqlmode(根据需求自定义)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# 跳过密码登录
#skip-grant-tables
#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
skip-external-locking
#只能用IP地址检查客户端的登录,不用主机名
skip_name_resolve = 1
#数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
character-set-server = utf8mb4
#数据库字符集对应一些排序等规则,注意要和character-set-server对应
collation-server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
#是否对sql语句大小写敏感,1表示不敏感
lower_case_table_names = 1
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
#TIMESTAMP如果没有显示声明NOT NULL,允许NULL值
explicit_defaults_for_timestamp = true
#SQL数据包发送的大小,如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
#MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
#内部内存临时表的最大值 ,设置成128M。
#比如大数据量的group by ,order by时可能用到临时表,
#超过了这个值将写入磁盘,系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728
#mysql binlog日志文件保存的过期时间,过期后自动删除
expire_logs_days = 5
#7,初始化mysql
进入mysql文件夹的bin目录下,初始化mysql --如有路径变动,注意修改对应路径
cd /usr/local/mysql/bin
./mysqld --user=mysql --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --initialize
#8,创建软连接
ln -s /usr/local/mysql/bin/mysql /usr/
#9,创建service文件
vim /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL server
After=syslog.target network.target
[Service]
User=mysql
Group=mysql
Type=forking
TimeoutSec=0
#PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=3
RestartPreventExitStatus=1
PrivateTmp=false
[Install]
WantedBy=multi-user.target
#10.加载并启动
#重新加载systemctl配置
systemctl daemon-reload
#启动mysql服务
systemctl start mysql
#启动mysql服务
systemctl start mysql
#停止mysql服务
systemctl stop mysql
#查看mysql服务状态
systemctl status mysql
#设置mysql服务开机自启
systemctl enable mysql
#关闭mysql服务开机自启
systemctl disable mysql
#11,查看临时密码
cat /usr/local/mysql/log/mysql.log | grep "temporary password"
mysql -uroot -p
#12,重置root密码并设置远程访问
# 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
# 远程登录设置
use mysql;
select Host,User from user;
# 授权root用户任何IP访问
update user set host = '%' where user ='root';
select Host,User from user;
#刷新权限
flush privileges;
#13,添加防火墙端口
## 关闭防火墙
# 查看 firewalld 服务状态
systemctl status firewalld
## 开放端口
# 查看防火墙所有开放的端口
firewall-cmd --zone=public --list-ports
# 开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
# 关闭3306端口
firewall-cmd --zone=public --remove-port=3306/tcp --permanent
# 配置立即生效
firewall-cmd --reload
下载rpm包安装
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum clean all;yum makecache
yum install mysql-community-server --nogpgcheck //--nogpgcheck这样子安装是禁用GPG签名验证
systemctl enable --now mysqld
grep 'temporary password' /var/log/mysqld.log
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxxx';
远程登录问题处理:
1,检查MySQL服务的运行状态
systemctl status mysqld
2,确认MySQL监听的IP地址和端口
[root@env-test ~]#netstat -tnulp
tcp 0 0 :::3306 :::* LISTEN 27200/mysqld
3. 确认防火墙设置
firewall-cmd --zone=public --add-port=3306/tcp --permanent;firewall-cmd --reload
firewall-cmd --list-all
4. 检查MySQL用户权限
确保root用户有权限从非localhost登录。你可以登录MySQL然后使用这个SQL命令查看用户权限:
先登录mysql -u root -p、然后切换到mysql数据库use mysql;、查看SELECT User, Host FROM mysql.user;。
如果root用户的host字段是localhost,那么你需要更改权限让root用户可以从任何主机登录。
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
MySQL8.0+ 版本
或者创建一个允许从任何主机登录的root用户:
mysql>CREATE USER 'root'@'%' IDENTIFIED BY '123';
授予新用户所有权限:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
刷新权限
mysql> FLUSH PRIVILEGES;
查看查看用户权限:
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
MySQL8.0+ 版本忘记密码
1,先在配置文件里加上 skip-grant-tables 重启mysql后免密登陆进入mysql
2,查看当前认证方式以及用户,默认是 caching_sha2_password 认证
mysql> SELECT host, user, authentication_string, plugin from user;
3,如果2是默认认证方式,则修改为密码认证为 mysql_native_password先, 在配置文件里加上,后再次重启mysqld,免密登陆
mysql> default_authentication_plugin=mysql_native_password
4,不能直接修改密码,会报错。要先清空root密码:
mysql> update user set authentication_string='' where user='root';
mysql> flush privileges;
5,退出mysql,删除/etc/my.cnf文件里的 skip-grant-tables ,再一次重启 mysql 服务,再次登陆的时候是空密码登陆:
mysql> mysql -u root -p
6.登陆后即可修改密码了:
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456';
mysql> flush privileges;
sudo dnf update
sudo dnf install mysql-server
sudo systemctl start mysqld
sudo systemctl enable mysqld
sudo systemctl status mysqld
安装MySQL后,建议进行一些基本的安全设置。可以使用以下命令配置MySQL安全性:
sudo mysql_secure_installation
登录MySQL,默认没有密码,直接回车跳过
mysql -u root -p
登录之后修改密码:
mysql> alter user "root"@"localhost" identified by "密码";
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
mysql> FLUSH PRIVILEGES;
开启远程访问:
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '密码';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
增加最大连接数:
show variables like 'max_connections';(查可以看当前的最大连接数)
set global max_connections=5000;(设置最大连接数为1000,可以再次查看是否设置成功)
[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
此警告信息处理:
show variables like 'log_error_suppression_list';
set global log_error_suppression_list='MY-013360';
show variables like 'log_error_suppression_list';
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。