阿里云centos7.8通过yum安装 Mysql 8.0.22
删除原来的数据库:
centos7中默认安装了数据库MariaDB,如果直接安装MySQL的话,会直接覆盖掉这个数据库,当然也可以手动删除一下:
[root@iZwz9j1nogjvowrlo3htsyZ ~]# rpm -qa|grep mariadb // 查询出来已安装的mariadb
mariadb-libs-5.5.65-1.el7.x86_64
[root@iZwz9j1nogjvowrlo3htsyZ ~]# rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64
// 卸载mariadb 命令:rpm -e --nodeps+文件名
清理原有的mysql
rpm -qa | grep mysql
#可能的显示情况如下
#mysql-community-libs-8.0.12-1.el7.x86_64
#mysql80-community-release-el7-1.noarch
#mysql-community-client-8.0.12-1.el7.x86_64
#mysql-community-common-8.0.12-1.el7.x86_64
#mysql-community-server-8.0.12-1.el7.x86_64
#通过下面的命令进行删除
yum remove mysql-xxx-xxx-
#查找mysql的相关文件
find / -name mysql
#根据自身的需求删除mysql的配置文件信息
rm -rf /var/lib/mysql
下载与安装MySQL:
这里采用Yum管理好了各种rpm包的依赖,能够从指定的服务器自动下载RPM包并且安装,所以在安装完成后必须要卸掉,否则会自动更新。
1、获取MySQL yum仓库地址:
访问地址:https://dev.mysql.com/downloads/mysql/
跳转:https://dev.mysql.com/downloads/repo/yum/
得到yum源下载地址:https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
2、安装MySQL官方的yum repository
[root@iZwz9j1nogjvowrlo3htsyZ ~]# wget -i -c https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
-bash: wegt: command not found
[root@iZwz9j1nogjvowrlo3htsyZ ~]#
提示 -bash:wget command not found,很明显没有安装wget软件包,先安装wegt
[root@iZwz9j1nogjvowrlo3htsyZ ~]# yum -y install wget
wget安装完成后再重新执行上述指令
[root@iZwz9j1nogjvowrlo3htsyZ ~]# wget -i -c https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
--2020-11-15 11:06:23-- https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm [following]
--2020-11-15 11:06:25-- https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.211.97.88
Connecting to repo.mysql.com (repo.mysql.com)|23.211.97.88|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 26024 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el7-3.noarch.rpm’
100%[=================================================================>] 26,024 --.-K/s in 0.1s
2020-11-15 11:06:26 (186 KB/s) - ‘mysql80-community-release-el7-3.noarch.rpm’ saved [26024/26024]
-c: No such file or directory
No URLs found in -c.
FINISHED --2020-11-15 11:06:26--
Total wall clock time: 2.8s
Downloaded: 1 files, 25K in 0.1s (186 KB/s)
3、下载rpm包
[root@iZwz9j1nogjvowrlo3htsyZ ~]# yum -y install mysql80-community-release-el7-3.noarch.rpm
出现Complete!即完成下载
4、安装MySQL服务
[root@iZwz9j1nogjvowrlo3htsyZ ~]# yum -y install mysql-community-server
出现Complete!即完成安装
5、启动MySQL服务
[root@iZwz9j1nogjvowrlo3htsyZ ~]# systemctl start mysqld.service
或者
systemctl start mysqld
其他常用命令:
重启:systemctl restart mysqld.service
停止:systemctl stop mysqld.service
查看状态:systemctl status mysqld.service
开机启动:systemctl enable mysqld
刚刚配置的服务需要让systemctl能识别,就必须刷新配置:systemctl daemon-reload
6、登录MySQL
第一次启动MySQL后,就会有临时密码,这个默认的初始密码在/var/log/mysqld.log文件中,我们可以用这个命令来查看:
[root@iZwz9j1nogjvowrlo3htsyZ ~]# grep "password" /var/log/mysqld.log
2020-11-15T14:49:25.766663Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: +wvaQOuc4Ue>
或者:
#启动成功后可以查看随机生成的初始化密码
cat /var/log/mysqld.log | grep password
登陆命令:
[root@iZwz9j1nogjvowrlo3htsyZ ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22
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.
mysql>
a、直接使用初始密码登录后先不改密码
简单测试
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
报错,意思是要修改密码
mysql> SET PASSWORD = PASSWORD('helloworld');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('helloworld')' at line 1
百度了一下,好多教程是“SET PASSWORD = PASSWORD(‘123’);”,然而mysql8.0修改密码应该这样写
mysql> SET PASSWORD = '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>
又报了另外一个错误,万能的百度,原因是密码等级太简单,如果你坚持要这样的密码,要改变密码等级:
注意到8.0 比5.7多了带“.”的变量导致只设置一半是不够的,百度的教程好多是”set global validate_password_policy=0;“是无法在mysql8.0版本设置成功的,正确如下:
mysql> set global validate_password.policy=0; //改变密码等级
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.length=4; //改变密码最小长度
Query OK, 0 rows affected (0.00 sec)
mysql>
然后再输入刚刚的命令:
mysql> set password = 'helloworld';
Query OK, 0 rows affected (0.02 sec)
mysql>
这样密码就设置好了。但是这样还不够,其实为了配置远程登录,MySQL8.0可以用下面这种写法,修改root用户密码,以及开启远程登录权限
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'helloworld';
Query OK, 0 rows affected (0.00 sec)
mysql>
然后后要输入flush privileges,刷新数据库。否则会保留在缓存中。结果如下:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改root用户的访问权限为‘%’
mysql> use mysql; // 改变当前使用的数据库为mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
刷新数据库
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看用户信息
mysql> select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
4 rows in set (0.00 sec)
b、我们还可以先跳过密码验证登录进MySQL:
以下方法百度所得,还未验证是否正确-----2020.11.16
停止服务:
systemctl stop mysqld.service
修改mMySQL的配置文件:
vi /etc/my.cnf
在最后加上配置:
skip-grant-tables
然后再启动服务:
systemctl start mysqld.service
然后这时就可以跳过密码来登录mysql:
mysql -u root
然后是修改下密码:(就看别人的例子是这样的)
mysql> use mysql;
Database changed
mysql> update mysql.user set authentication_string=password('4008') where user='root' ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
然后exit退出mysql,重新在刚刚那个配置文件中去掉skip-grant-tables,然后重启MySQL。
然后就可以用新密码登录了:
sql报错
但这个时候,我试了一下一个简单的sql语句:
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
这个情况要跟第一种情况一样加个这样的改密码的语句:
SET PASSWORD = '密码';
7、注意:
需要注意mysql的配置文件中的bindaddress 的参数和skip-networking 配置
bindaddress : 设定哪些ip地址被配置,使得mysql服务器只回应哪些ip地址的请求),最好注释掉该参数或设置成为127.0.0.1以外的值
skip-networking : 如果设置了该参数项,将导致所有TCP/IP端口没有被监听,也就是说出了本机,其他客户端都无法用网络连接到本mysql服务器,所以应该注释掉该参数
添加3306端口:
命令:
firewall-cmd --zone=public --add-port=3306/tcp --permanent;
如果说没有运行防火墙,那就先开防火墙:
systemctl status firewalld 查看防火墙状态
systemctl start firewalld 打开防火墙
然后再输入那个开放3306端口的命令就行了
[root@iZwz9j1nogjvowrlo3htsyZ ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent;
success
[root@iZwz9j1nogjvowrlo3htsyZ ~]# firewall-cmd --reload // 重启防火墙
success
最后的收尾:
1.我们刚开始说要写在yum的repository,用这个命令就行:
[root@iZwz9j1nogjvowrlo3htsyZ ~]# yum -y remove mysql80-community-release-el7-3.noarch
结尾显示Complete!代表成功
2.MySQL设一下utf8:
打开/etc/my.cnf也就是数据库的配置文件
[root@iZwz9j1nogjvowrlo3htsyZ ~]# vim /etc/my.cnf
然后在底部复制粘贴:
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
采用navicat新建数据库时,需要将编码方式设置为,字符集:utf8 – UTF-8 Unicode ,排序规则:utf8_general_ci
3.阿里云的服务器中的安全组加入mysql连接的规则。这个很重要不然远程无法连接上。
4.配置文件的说明:
/etc/my.cnf 这是mysql的主配置文件
/var/lib/mysql mysql数据库的数据库文件存放位置
/var/log mysql数据库的日志输出存放位置
5.退出mysql:
使用: mysql -u root -p 进入 mysql 命令号以后, 如果想退出, 可以使用: quit 命令
mysql> quit;
Bye