mysql学习笔记（一）centos如何离线安装mysql?

梦里梦外; 2023-07-13 10:44 2阅读 0赞

## 引言 ##

*  mysql 是当下最为流行的关系型数据库之一，本章内容小猿将对自己电脑虚拟机centos6.9 安装mysql5.7.62版本做一次全面的记录。

## mysql下载 ##

*  对于不了解mysql的童鞋可以上官网了解一二，但是小猿不推荐直接上官网下载mysql，应为这样下载速度十分缓慢，故小猿推荐大家上一些镜像网站去下载mysql，如aliyun和huaweiyun镜像网站，小猿推荐一个下载页面，[请点击这里][Link 1]。

## mysql的安装 ##

### 卸载自带的mysql版本 ###

*  首先要卸载centos自带的mysql版本

rpm -qa | grep mysql

当出现结果时说明自带的mysql还没有完全卸载完成，开始执行卸载。

rpm -e --nodeps 上面查出来的文件名

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]  
再次执行查询时就发现关于mysql的信息已经没了，说明旧版本卸载成功。

### 离线mysql压缩包的上传和解压 ###

*  上传mysql文件，上传文件主要取决于客户端的选取，如采用securityCRT，则直接按下alt+p进入上传窗口，然后采用put命令即可；若采用的是xshell远程客户端，则需要采用lrzsz工具，若centos没有安装lrzsz，则需要安装上传和下载工具，执行以下命令：

yum install lrzsz

![在这里插入图片描述][20200311170018127.png]

rpm  -qa | grep lrzsz

![在这里插入图片描述][2020031117023670.png]  
说明lrzsz已经成功安装，直接上传文件即可，  
现将下载的5.7版本的mysql解压到/usr/local目录下。

tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/

可以将解压后的文件重命名一下：

mv -f mysql-5.7.26-linux-glibc2.12-x86_64  mysql

![在这里插入图片描述][20200311224041419.png]  
修改完毕后在mysql文件目录后，查看mysql文件目录下是否有data目录，若没有则需要创建。

mkdir   /usr/local/mysql/data

### 用户组创建及修改 ###

需要创建mysql用户组以及mysql用户

groupadd mysql
    useradd mysql -g mysql

当执行完成后可以采用以下命令来检测是否创建成功

cat  /etc/group
    cat /etc/passwd

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 2]  
为了确保操作正常执行，我们需要修改mysql目录的属性以及权限：

cd /usr/local/mysql
    #chown -R mysql .
    #chgrp -R mysql .
    #或者
    chown -R mysql ./
    chgrp -R mysql ./

或者是

chown -R  mysql:mysql ./mysql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 3]

### 初始化mysql ###

cd /usr/local/mysql/
    #./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --#datadir=/usr/local/mysql/data
    ./bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

若已经定义my.cnf 则下面命令直接输入

./bin/mysqld --defaults-file=/usr/local/mysql/my.cnf 
    --basedir=/usr/local/mysql
    --datadir=/usr/local/mysql/data 
    --user=mysql --initialize-insecure

执行后会提示以下信息:

[root@localhost local]# cd mysql/
    [root@localhost mysql]# ./bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
    2020-03-11 07:46:47 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
    2020-03-11 07:46:50 [WARNING] The bootstrap log isn't empty:
    2020-03-11 07:46:50 [WARNING] 2020-03-11T14:46:47.559141Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
    2020-03-11T14:46:47.561118Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
    2020-03-11T14:46:47.561138Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

![在这里插入图片描述][20200311224917948.png]

### 修改my.cnf文件 ###

# 进入/usr/local/mysql文件夹下
     cd /usr/local/mysql
    # 创建my.cnf文件
     touch my.cnf #或者cd ''>my.conf
    vim /usr/local/mysql/my.cnf

对其进行修改

# Use settings from rpl_1slave_base.cnf
    # add setting to connect the slave to the master by default
    [mysql]
    socket=/var/lib/mysql/mysql.sock
    # set mysql client default chararter
    default-character-set=utf8
    #
    [mysqld]
    socket=/var/lib/mysql/mysql.sock
    # set mysql server port 
    port = 7006 ##默认是3306，这里发现3306已经被占用，因此防止这种情况发生，可以避免使用3306mysql默认端口
    # set mysql install base dir
    basedir=/usr/local/mysql
    # # set the data store dir
    datadir=/usr/local/mysql/data
    # set the number of allow max connnection
    max_connections=200
    # set server charactre default encoding
    character-set-server=utf8
    # the storage engine
    default-storage-engine=INNODB
    lower_case_table_names=1
    max_allowed_packet=16M
    explicit_defaults_for_timestamp=true
    #skip-grant-tables
    #
    [mysql.server]
    user=mysql
    basedir=/usr/local/mysql

**建议准备好my.cnf 后在进行脚本执行。**

### 修改权限 ###

chmod -R 755 my.cnf

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 4]

### 启动mysql ###

./support-files/mysql.server start
    #或者
    ./etc/init.d/mysqld  start

出现启动错误

[root@localhost mysql]# ./support-files/mysql.server  start
    my_print_defaults: [Warning] World-writable config file '/etc/my.cnf' is ignored.
    Starting MySQL.my_print_defaults: [Warning] World-writable config file '/etc/my.cnf' is ignored.
    my_print_defaults: [Warning] World-writable config file '/etc/my.cnf' is ignored.
    ... ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).

![在这里插入图片描述][2020031200322754.png]  
在此感谢大佬的一篇排错文章，[请看这里][Link 2]。小猿依据上面的思路排查，将防火前也关闭

vim /etc/selinux/config
    # 把SELINUX=enforcing改为SELINUX=disabled后存盘退出重启机器

任然启动不了mysql  
然后直接查看错误日志  
![在这里插入图片描述][2020031200364163.png]  
发现是端口问题，重新配置my.cnf，修改端口。  
将端口改为7006，继续启动  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 5]  
启动成功。

### 注册开机启动 ###

# 依据情况修改support-files/mysql.server
    cp support-files/mysql.server /etc/init.d/mysql

![在这里插入图片描述][20200311230837112.png]

# 添加开机启动
    chkconfig mysql on

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 6]  
修改环境变量  
同jdk配置类似，在/etc/profile 中配置。

vim /etc/profile
    #将光标移动到最底，然后加入下面的内容
    export MYSQL_HOME=/usr/local/mysql
    export PATH=$PATH:$MYSQL_HOME/bin

![在这里插入图片描述][20200312010701303.png]

source  /etc/profile

![在这里插入图片描述][20200312010820889.png]

### 获取mysql 初始密码及修改密码 ###

#### 获取密码及登录 ####

cat /root/.mysql_secret 
    #得到
    NlT6a,hnWvkN

![在这里插入图片描述][20200312011135886.png]  
首次登录

cd /usr/local/mysql/bin
    ./mysql -uroot -p:NlT6a,hnWvkN

![在这里插入图片描述][20200312011949604.png]  
不让我访问，root中的密码可能已经失效，所以我们先配置my.cnf文件，让其跳过密码授权，在mysqld中添加一行

skip-grant-tables

重新启动mysql

service mysql restart
    #或者
    ./support-files/mysql.server restart

再次登录

cd /usr/local/mysql/bin
    ./mysql -uroot -p
    #直接按enter跳过即可登录

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 7]

#### 修改密码 ####

flush privileges;
    use mysql
    update mysql.user set authentication_string=password('root') where user='root';
    flush privileges;

去掉my.cnf中的skip-grant-tables并保存  
![在这里插入图片描述][20200312021257657.png]  
重启mysql 后登录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 8]  
所出现一下形势的报错

ERROR 1820 (HY000): You must reset your password using ALTER USER statement

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 9]  
直接修改密码，刷新权限即可

set password=password("123456");
    flush privileges;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 10]

### 远程访问 ###

设定远程登录mysql。在Linux下为了安全，默认是不允许mysql本机以外的机器访问mysql数据库服务，因此需要重新授权root。

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
    flush privileges;

或者

use mysql;
    update user set host='%' where user='root';
    flush privileges;

都可以，但是如果root已经在授权表中，则会报错。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 11]

select host,user from user;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 12]  
说明授权成功，可以远程登录了。  
查看防火墙状态，若开启则直接关闭防火墙。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 13]  
开始远程测试。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 15]  
至此mysql5.7的安装就告一段落了。

[Link 1]: https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-5.7/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200311164108259.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[20200311170018127.png]: https://img-blog.csdnimg.cn/20200311170018127.png
[2020031117023670.png]: https://img-blog.csdnimg.cn/2020031117023670.png
[20200311224041419.png]: https://img-blog.csdnimg.cn/20200311224041419.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200311210255456.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200311210345462.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200311224303489.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[20200311224917948.png]: https://img-blog.csdnimg.cn/20200311224917948.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200311232114352.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[2020031200322754.png]: https://img-blog.csdnimg.cn/2020031200322754.png
[Link 2]: https://blog.csdn.net/Qevery678/article/details/96422599
[2020031200364163.png]: https://img-blog.csdnimg.cn/2020031200364163.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200312005956604.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[20200311230837112.png]: https://img-blog.csdnimg.cn/20200311230837112.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200311231951927.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[20200312010701303.png]: https://img-blog.csdnimg.cn/20200312010701303.png
[20200312010820889.png]: https://img-blog.csdnimg.cn/20200312010820889.png
[20200312011135886.png]: https://img-blog.csdnimg.cn/20200312011135886.png
[20200312011949604.png]: https://img-blog.csdnimg.cn/20200312011949604.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200312013257777.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[20200312021257657.png]: https://img-blog.csdnimg.cn/20200312021257657.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200312021347634.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/2020031202263897.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200312022529790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200312024121198.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200312024517728.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200312024825577.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20200312030004930.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20200312030200871.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu,size_16,color_FFFFFF,t_70