1、安装过程
1、下载MySQL
下载地址:https://dev.mysql.com/downloads/mysql/
2、利用Xftp软件将tar包发送到CentOS虚拟机
3、使用Xshell远程连接工具连接到虚拟机
4、使用Linux解压命令解压mysql的tar包
[root@localhost ~]# cd /
[root@localhost /]# ls
bin boot dev etc home lib lib64 media mnt opt proc root run sbin srv sys tmp usr var
[root@localhost /]# cd opt
[root@localhost opt]# ls
rh
[root@localhost opt]# ls
mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar rh
[root@localhost opt]# tar -xvf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.31-1.el7.x86_64.rpm
mysql-community-libs-5.7.31-1.el7.x86_64.rpm
mysql-community-client-5.7.31-1.el7.x86_64.rpm
mysql-community-server-5.7.31-1.el7.x86_64.rpm
mysql-community-embedded-5.7.31-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.31-1.el7.x86_64.rpm
mysql-community-common-5.7.31-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.31-1.el7.x86_64.rpm
mysql-community-devel-5.7.31-1.el7.x86_64.rpm
mysql-community-test-5.7.31-1.el7.x86_64.rpm
5、按照顺序安装
rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
报错:安装的mysqlcommon库和mariadb的库包冲突
[root@localhost opt]# rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
警告:mysql-community-common-5.7.31-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.31-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
file /usr/share/mysql/danish/errmsg.sys from install of mysql-community-common-5.7.31-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
......
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB(英语:XtraDB)来代替MySQL的InnoDB。 (来自百度百科的资料)
CentOS7选择了MariaDB来作为默认数据库,而放弃了MySQL。
那么,我们选择删除有关MariaDB的组件。
(1)检查组件
[root@localhost opt]# cd
[root@localhost ~]# rpm -qa|grep -i mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
(2)删除
[root@localhost ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64
错误:依赖检测失败:
libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要
存在依赖关系
(3)强制删除
[root@localhost ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
–nodeps:不检查依赖关系
6、继续安装完成
[root@localhost opt]# rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
警告:mysql-community-common-5.7.31-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-common-5.7.31-1.e################################# [100%]
[root@localhost opt]# rpm -ivh mysql-community-libs-5.7.31-1.el7.x86_64.rpm
警告:mysql-community-libs-5.7.31-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-libs-5.7.31-1.el7################################# [100%]
[root@localhost opt]# rpm -ivh mysql-community-client-5.7.31-1.el7.x86_64.rpm
警告:mysql-community-client-5.7.31-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-client-5.7.31-1.e################################# [100%]
[root@localhost opt]# rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm
警告:mysql-community-server-5.7.31-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-server-5.7.31-1.e################################# [100%]
2、设置和使用
1、查看安装和存储位置
[root@localhost /]# find / -name mysql
/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql #数据存放路径
/usr/bin/mysql
/usr/lib64/mysql
/usr/share/mysql #默认安装路径
2、启动MySQL服务
service mysqld start
3、查看MySQL服务器状态
service mysqld status
4、启动服务后,设置MySQL服务器的root用户密码
MySQL设置了一个临时密码,我们可以通过查看日志来获取。
[root@localhost /]# grep password /var/log/mysqld.log
2020-08-10T06:00:24.002154Z 1 [Note] A temporary password is generated for root@localhost: lTPK-axlK2)G
grep:查找文件中符合条件的字符串
5、登录MySQL
当我在Xshell远程登录时,MySQL会拒绝访问。
[root@localhost /]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
所以我们在虚拟机上进行登录。
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
登录成功。
6、在虚拟机上配置MySQL的新密码
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1998zxvcLWJ.';
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
7、允许root用户在任何地方进行远程登录
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1998zxvcLWJ.' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 允许root用户在任何地方进行远程登录,并具有所有库所有表任何操作权限。
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
mysql> EXIT
Bye
# 退出
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
# 重启MySQL服务
在XShell远程登录:
8、配置防火墙
目的在于在Windows的数据库软件上可以连接Linux的MySQL服务器。
[root@localhost /]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost /]# firewall-cmd --reload
success
命令含义:
--zone : 作用域
--add-port=3306/tcp : 添加端口,格式为:端口/通讯协议
--permanent : 永久生效,没有此参数重启后失效
--reload : 重启防火墙
在Windows上登录成功。
SQLyog上登录成功。
USE mysql;
SELECT * FROM `user`;
SELECT PASSWORD('1998zxvcLWJ.');
# *0D2D880CDAA66BCEAD621B2A100FC675C6AC37A5
3、MySQL权限问题
分配权限账户
权限: select insert delete update drop create 或 all
@ 后面可以是localhost 也可以是ip 也可以给% 那%代表任意一台计算机都可以连接上来
语法
GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
注意分配多个权限用逗号隔开
GRANT DELETE,SELECT,UPDATE ON mydb.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';