本博文介绍在云服务器CentOS7下安装MySQL8,并设置表名不区分大小写,修改密码,以及用navicat远程连接。
查看Linux版本信息
[root@node1 ~]# cat /proc/version
Linux version 3.10.0-957.21.3.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Tue Jun 18 16:35:19 UTC 2019
以上查出的重要信息有:Red Hat 和 el7.x86_64
下载mysql
方式一:
进入地址:https://dev.mysql.com/downloads/mysql/
按如下图分别选择:Red Had和Linux7, 下载文件名:mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
不用登录注册,选择直接开始下载
下载好后,上传到Linux
方式二:
在点击just start my download下载后,弹出下载页面后,复制浏览器地址栏的下载地址
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
直接在Linux 使用wget命令下载
[root@node1 ~]# wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
卸载mariadb
查看mariadb
rpm -qa | grep mariadb
卸载mariadb
rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
卸载之前的mysql(如果之前安装过mysql才需要进行此步骤,否则跳过这个步骤)
如果之前安装过mysql,也需要将之前的mysql卸载干净
查看之前安装过的mysql
[root@node1 ~]# rpm -qa|grep -i mysql
mysql-community-common-8.0.24-1.el7.x86_64
mysql-community-client-8.0.24-1.el7.x86_64
mysql-community-libs-8.0.24-1.el7.x86_64
mysql-community-server-8.0.24-1.el7.x86_64
在卸载mysql之前,先要做好数据备份!数据备份!数据备份!(非常重要)
使用mysqldump命令备份数据
语法:mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
[root@node1 ~]# mysqldump -uroot -p123456 yourdb > yourdb.sql
卸载mysql
yum remove mysql
删除老版本mysql的目录、文件和库(重要!!!)
查看相关文件
查看mysql相关目录、文件和库
[root@node1 ~]# find / -name mysql
/usr/local/mysql
/usr/lib64/mysql
/usr/bin/mysql
/etc/logrotate.d/mysql
/var/lib/mysql
/var/lib/mysql/mysql
删除相关文件
删除mysql相关目录、文件和库
[root@node1 ~]# rm -rf /usr/local/mysql /usr/lib64/mysql /usr/bin/mysql /etc/logrotate.d/mysql /var/lib/mysql /var/lib/mysql/mysql
创建安装目录及解压安装文件
创建安装目录
mkdir /usr/local/mysql
将安装文件复制到安装目录
[root@node1 ~]# cp mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar /usr/local/mysql
进入安装目录
cd /usr/local/mysql/
解压安装文件
tar -xvf mysql-8.0.11-1.el7.x86_64.rpm-bundle.tar
ls命令查看解压后的文件,如下:
[root@node1 mysql]# ls
mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.24-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm
mysql-community-common-8.0.24-1.el7.x86_64.rpm
mysql-community-devel-8.0.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-server-8.0.24-1.el7.x86_64.rpm
mysql-community-test-8.0.24-1.el7.x86_64.rpm
修改目录所有者和所属组
chown mysql:mysql /usr/local/mysql -R;
安装相关包
rpm -ivh --nodeps --force mysql-community-common-8.0.24-1.el7.x86_64.rpm
rpm -ivh --nodeps --force mysql-community-libs-8.0.24-1.el7.x86_64.rpm
rpm -ivh --nodeps --force mysql-community-client-8.0.24-1.el7.x86_64.rpm
rpm -ivh --nodeps --force mysql-community-server-8.0.24-1.el7.x86_64.rpm
设置mysql 表名不区分大小写
修改/etc/my.cnf,在文件末尾添加
lower_case_table_names=1
mysql初始化及相关设置
mysqld --initialize;
chown mysql:mysql /var/lib/mysql -R;
systemctl start mysqld.service;
systemctl enable mysqld;
修改密码
查看临时密码
# cat /var/log/mysqld.log | grep temporary
2021-05-07T18:14:53.070097Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: JizY&q7Yqm+N
登录mysql命令行,执行命令后,输入上方查出的密码
[root@node1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 190
Server version: 8.0.24
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
在mysql命令行下,执行修改密码命令, BY后面的'123456789'为新密码,请修改为自己的密码!
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456789' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456789';
FLUSH PRIVILEGES;
navicat远程连接
开放3306端口:网页登录服务器所在的云后台(例如:阿里云),进入控制台的,选择相应实例,进入安全组,开放3306端口。
此时用navicat连接报1130错误,在mysql命令行下执行如下命令:
USE mysql;
SELECT host FROM user WHERE user='root';
UPDATE user SET host = '%' WHERE user ='root';
SELECT host FROM user WHERE user='root';
flush privileges;
接下来navicat就可以连接成功了!
完成!enjoy it!