学习笔记-MySQL部署及初始化设置 - CentOS

下载链接

https://dev.mysql.com/downloads/mysql/           

MySQL安装前置准备

#    检查是否存在MariaDB
rpm -qa | grep mariadb
#    删除现存MariaDB
yum -y remove mariadb*
#    检查是否存在MySQL
rpm -qa | grep mysql
#    删除现存MySQL
yum -y remove mysql*
#    删除旧配置文件
rm -rf /etc/my.cnf
#    删除MySQL相关文件
rm -rf /var/lib/mysql            

安装MySQL

#    配置安装源
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -Uvh http://172.16.28.11/mysql80-community/mysql80-community-release-el7-3.noarch.rpm
#    更新MySQL公钥
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
#    安装MySQL，以防万一增加--nogpgcheck参数，跳过公钥安装
sudo yum --enablerepo=mysql80-community install -y mysql-community-server --nogpgcheck           

启动MySQL

#    启动MySQL
systemctl start mysqld.service
#    检查运行状态
systemctl status mysqld.service
#    设置MySQL为开机自启动
systemctl enable mysqld.service
#    查看端口运行情况
netstat -nlpt
#    停止MySQL
systemctl stop mysqld
#    重启MySQL
systemctl restart mysqld           

MySQL 初始化设置

#    查看默认密码
cat /var/log/mysqld.log | grep localhost
cat /var/log/mysql/mysqld.log | grep localhost
#    登录MySQL Shell
mysql -uroot -p
#    修改MySQL密码 （密码为P@ssw0rd）
ALTER USER USER() IDENTIFIED BY 'P@ssw0rd';
#    检查系统用户访问权限
use mysql;
select user,host,plugin,authentication_string from mysql.user;
select user,host,plugin from mysql.user;
#    开启 root 远程登录权限，并刷新
update mysql.user set host='%' where user='root';
flush privileges;
#    退出MySQL Shell
exit           

使用初始化向导配置MySQL

#    查看默认密码
cat /var/log/mysqld.log
#    数据库初始化向导
sudo mysql_secure_installation
#    确保MySQL服务器部署的安全。输入用户root的密码：
Securing the MySQL server deployment.
Enter password for user root: 
#    用户帐户根的现有密码已过期。请设置新密码。
The existing password for the user account root has expired. Please set a new password.
#    新密码：
New password: 
#    重新输入新密码
Re-enter new password: 
#    是否更换root密码 N
Change the password for root ? ((Press y|Y for Yes, any other key for No) 
#    是否删除匿名用户 Y
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
#     是否禁止root用户远程登录 N
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
#     是否删除test数据库 Y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
#    是否重新加载权限表 Y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :           

测试MySQL

#    进入MySQL Shell
sudo mysql -uroot -pP@ssw0rd
#    测试MySQL，刷新权限
flush privileges;
#    测试MySQL，列出数据库
show databases;
#    测试MySQL，查看用户
select user,host from mysql.user;
#    退出MySQL Shell
exit           

修改MySQL密码加密方式

#    修改root用户密码加密方式为mysql_native_password 并修改密码
ALTER USER `root`@`%` IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
ALTER USER `keystone`@`%` IDENTIFIED WITH mysql_native_password;           

取消非root用户登录MySQL的root用户限制

# 进入MySQL Shell
sudo mysql -uroot -pP@ssw0rd
# 取消非root用户登录MySQL的root用户限制
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
update user set host='%' where user='root';
Grant all privileges on test.* to 'root'@'%';
alter user root identified with mysql_native_password by 'P@ssw0rd';
Grant all privileges on root.* to 'root'@'%';