Linux Centos 安装配置MySql

Linux Centos 安装配置MySql

1、卸载MySql

1.1、查询是否安装过MySql

[root@server79 software]# rpm -qa | grep -i mysql
mysql-community-client-8.0.28-1.el7.x86_64
mysql-community-common-8.0.28-1.el7.x86_64
mysql-community-libs-8.0.28-1.el7.x86_64
mysql-community-icu-data-files-8.0.28-1.el7.x86_64
mysql-community-libs-compat-8.0.28-1.el7.x86_64
mysql80-community-release-el7-5.noarch
mysql-community-client-plugins-8.0.28-1.el7.x86_64
mysql-community-server-8.0.28-1.el7.x86_64

1.2、删除MySql

[root@server79 software]# yum remove -y mysql*
已加载插件：fastestmirror
参数 mysql80-community-release-el7-5.noarch.rpm 没有匹配
不删除任何软件包
[root@server79 software]# yum -y remove mysql80-community-release-el7-5.noarch
---------------------------------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
[root@server79 software]# yum -y remove mysql-*

[root@server79 software]# rpm -qa | grep -i mysql
[root@server79 software]# 
[root@server79 software]# 

1.3、把所有关于MySql的目录都删掉

# 查找并删除对应的文件或文件夹
[root@server79 software]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/lib64/mysql
[root@server79 software]# rm -rf /var/lib/mysql
[root@server79 software]# find / -name mysql

1.4、删除MySql配置文件

rm -rf /etc/my.cnf

1.5、删除日志MySQL日志文件

如果不删除这个文件，会导致新安装的mysql无法生成新密码，导致无法登陆

rm -rf /var/log/mysqld.log

---

2、安装MySql

2.1、从MySql官网获取最新的MySql安装源

[root@server79 /]# mkdir software
[root@server79 /]# ls

[root@server79 software]# wget https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
-bash: wget: command not found
[root@server79 software]# 
[root@server79 software]# 
[root@server79 software]# yum -y install wget  //安装wget

2.2、更新MySql软件安装包

#更新安装包 
[root@server79 software]# rpm -Uvh mysql80-community-release-el7-5.noarch.rpm 

2.3、列出MySql软件安装包里面所有可用的版本

#查看mysql仓库，根据实际需要修改要安装的版本
[root@server79 software]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64  MySQL Cluster 7.5 Community     禁用
mysql-cluster-7.5-community-source  MySQL Cluster 7.5 Community - S 禁用
mysql-cluster-7.6-community/x86_64  MySQL Cluster 7.6 Community     禁用
mysql-cluster-7.6-community-source  MySQL Cluster 7.6 Community - S 禁用
mysql-cluster-8.0-community/x86_64  MySQL Cluster 8.0 Community     禁用
mysql-cluster-8.0-community-source  MySQL Cluster 8.0 Community - S 禁用
!mysql-connectors-community/x86_64  MySQL Connectors Community      启用:    230
mysql-connectors-community-source   MySQL Connectors Community - So 禁用
!mysql-tools-community/x86_64       MySQL Tools Community           启用:    138
mysql-tools-community-source        MySQL Tools Community - Source  禁用
mysql-tools-preview/x86_64          MySQL Tools Preview             禁用
mysql-tools-preview-source          MySQL Tools Preview - Source    禁用
mysql57-community/x86_64            MySQL 5.7 Community Server      禁用
mysql57-community-source            MySQL 5.7 Community Server - So 禁用
!mysql80-community/x86_64           MySQL 8.0 Community Server      启用:    321
mysql80-community-source            MySQL 8.0 Community Server - So 禁用
[root@server79 software]# 
[root@server79 software]# yum repolist | grep mysql
!mysql-connectors-community/x86_64       MySQL Connectors Community          230
!mysql-tools-community/x86_64            MySQL Tools Community               138
!mysql80-community/x86_64                MySQL 8.0 Community Server          321
[root@server79 software]# 

2.4、配置MySql软件安装版本

# 修改要安装的包
# 禁用不要的包
[root@server79 software]# yum-config-manager --disable mysql80-community  
-bash: yum-config-manager: 未找到命令
[root@server79 software]# yum -y install yum-utils
-------->>>>>    #开始安装
# 开启需要的包
[root@server79 software]# yum-config-manager --enable mysql80-community 

2.5、安装MySql及初始化

1. 开始安装

   # 开始安装
   [root@server79 software]# yum -y install mysql-community-server
   
   # 确认安装版本
   [root@server79 software]# mysql --version
   mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
   [root@server79 software]# 
   
   #检查mysql运行状态
   [root@server79 software]# systemctl status mysqld
   [root@server79 software]# systemctl start mysqld
   

2. 修改初始化密码

   #查询mysql初始密码
   [root@server79 software]# grep 'temporary password' /var/log/mysqld.log
   2022-02-25T02:14:45.364939Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q9rXkqmoN+FL
   [root@server79 software]# 
   
   [root@server79 software]# mysql -uroot -p cqbe6je8>KeH
   mysql -uroot -p eoAqn8oKj%H/
   mysql> alter user 'root'@'localhost' identified by 'Hello88&*';
   mysql> quit;
   
   

3. 密码规则修改

   #显示密码设置状态信息
   mysql> show variables like 'validate_password%';
   /*
   validate_password.dictionary_file    #指定密码验证的文件路径；
   validate_password.length    #固定密码的总长度，默认为8；
   validate_password.mixed_case_count    #整个密码中至少要包含大/小写字母的总个数；
   validate_password.number_count    #整个密码中至少要包含阿拉伯数字的个数；
   validate_password.policy    #指定密码的强度验证等级，默认为 MEDIUM；
   validate_password.special_char_count    #整个密码中至少要包含特殊字符的个数；
   ※ 指定密码的强度验证等级validate_password.policy 取值：
   
   0/LOW         #只验证长度；
   1/MEDIUM    #验证长度、数字、大小写、特殊字符；
   2/STRONG   #验证长度、数字、大小写、特殊字符、字典文件；
   */
   
   mysql> show variables like 'validate_password%';
   +--------------------------------------+-------+
   | Variable_name                        | Value |
   +--------------------------------------+-------+
   | validate_password.check_user_name    | ON    |
   | validate_password.dictionary_file    |       |
   | validate_password.length             | 4     |
   | validate_password.mixed_case_count   | 1     |
   | validate_password.number_count       | 1     |
   | validate_password.policy             | LOW   |
   | validate_password.special_char_count | 1     |
   +--------------------------------------+-------+
   
   mysql> set global validate_password.policy=LOW;
   mysql> set global validate_password.length=4;
   
   mysql> alter user 'root'@'localhost' identified by 'test';   /*执行成功*/
   

4. 用户授权

   #MySQL8.0以前版本授权问题命令
   grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;
   flush privileges;
   
   
   #MySQL8.0以后用户授权问题，新建用户，在授权，分布执行；
   mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'Kcjy88&*';
   Query OK, 0 rows affected (0.01 sec)
   
   mysql>  GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)
   

3、启动MySql并修改root连接权限

设置开机自启动：

1 vim /etc/rc.local
2 添加service mysqld start

3.1、MySql常用指令

# mysql状态
systemctl status mysqld;
sudo sevice mysqld status;
# 启动mysql
systemctl start mysqld
sudo systemctl mysqld start
# 关闭mysql
systemctl stop mysqld;
sudo service mysqld stop

3.2、修改root用户连接权限

# 查看mysql是否区分大小写
show variables like '%case_tables%';

# 获取MySQL默认root用户密码
grep 'temporary password' /var/log/mysqld.log

# 登录MySQL
mysql -uroot -p

# 重设MySQL默认root用户密码
alter user 'root'@'localhost' identified by '新密码'
# 进入mysql数据库

show databases;

use mysql；

# 查看root用户权限
select host,user,authentication_string,plugin from user;

# 授权root用户远程登录
# 更改root用户的host为通配符%，就可以把该用户的访问权限设置成远程连接；
update user set host='%' where user='root';

# 刷新mysql的系统权限相关表
FLUSH PRIVILEGES;

# 退出MySQL
quit；

3.3、配置MySql数据库是否区分大小写

linux下mysql默认数据库表名和数据库名区分大小写

lower_case_table_name=1 (mysql不区分大小写)

lower_case_table_name=0 (mysql区分大小写)

cd /etc
ls
vi my.cnf
# 打开my.cnf文件，在文件中的[mysqld]节点下增加
#lower_case_table_name=1  (mysql不区分大小写)

#lower_case_table_name=0  (mysql区分大小写)
#退出保存按下【esc】-->按下【:】--->输入【wq】回车即可
#重启mysql服务
service mysqld restart;

4、常见问题

1、Windows安装mysql重置root用户密码

#开启两个窗口，第一个窗口执行以下命令
net stop mysql
mysqld --console --skip-grant-tables --shared-memory
#第二条命令会有输出，看到输出后，别关闭这个窗口开启第二个窗口


#第二个窗口
mysql -u root
#[直接回车，不用输密码]
use mysql

#先设置空，在设置密码
update user set authentication_string='' where user='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;