2023.6.12 02.数据库安装管理

1.YUM安装数据库

2.通⽤安装数据库

3.源码安装数据库

4.变更数据库密码

 

1.YUM安装数据库

MySQL Yum 仓库提供了⽤于在 Linux 平台上安装 MySQL 服务器，客户端和其他组件的 RPM 包。mysql-yum安装下

载地址

mysql官⽅rpm源

https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm

 

 

注意

使⽤MySQL Yum仓库时，默认选择安装最新的 MySQL 版本。如果需要使⽤低版本请按如下操作。

 

1.安装 MySQL 仓库源

[root@sql ~]# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-

1.noarch.rpm

 

2.选择并启⽤适合当前平台的发⾏包

//列出所有MySQL发⾏版仓库

[root@sql ~]# yum repolist all|grep mysql

//禁⽤8.0发⾏版仓库, 启⽤5.7发⾏版仓库

[root@sql ~]# yum install yum-utils

[root@sql ~]# yum-config-manager --disable mysql80-community

[root@sql ~]# yum-config-manager --enable mysql57-community

注意

可以⼿动编辑/etc/yum.repos.d/mysql-community.repo ⽂件配置仓库

[mysql57-community]

name=MySQL 5.7 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

 

3.通过以下命令安装 MySQL , 并启动 MySQL

[root@sql ~]# yum install -y mysql-community-server --nogpgcheck

[root@sql ~]# systemctl start mysqld

[root@sql ~]# systemctl enable mysqld

MySQL服务器初始化（仅适⽤于MySQL 5.7）在服务器初始启动时，如果服务器的数据⽬录为空，则会发⽣以下情

况：

服务器已初始化。

在数据⽬录中⽣成SSL证书和密钥⽂件。

validate_password插件安装并启⽤。

超级⽤户帐户'root'@'localhost'已创建。

超级⽤户的密码被设置并存储在错误⽇志⽂件中。要显示它，请使⽤以下命令:

[root@sql ~]# grep "password" /var/log/mysqld.log

2023-05-20T09:52:10.486810Z 1 [Note] A temporary password is generated for

root@localhost: 0?=FYrw#kzFs

通过使⽤⽣成的临时密码登录并尽快更改 root 密码并为超级⽤户帐户设置⾃定义密码

[root@sql ~]# mysql -uroot -p'0?=FYrw#kzFs'

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Wing@123';

注意

MySQL 的 validate_password 插件默认安装。将要求密码⾄少包含⼤写、⼩写、数字、特殊字符、并且总

密码⻓度⾄少为8个字符。

mysql -uroot -p'Wing@123'

 

2.通⽤安装数据库

采⽤⼆进制免编译⽅式安装 MySQL , 不需要复杂的编译设置和编译时间等待,解压下载的软件包,初始化即可完

成 MySQL 的安装和启动。MySQL⼆进制包下载地址

MySQL⼆进制包官⽅安装⼿册

 

1.基础环境准备

[root@sql ~]# systemctl stop firewalld

[root@sql ~]# systemctl disable firewalld

[root@sql ~]# setenforce 0

//建⽴⽤户与相应⽬录

[root@sql ~]# groupadd mysql

[root@sql ~]# useradd -r -g mysql -s /sbin/nologin mysql

[root@sql ~]# mkdir /soft/src -p && cd /soft/src

 

2.下载 MySQL 并安装

链接: https://pan.baidu.com/s/1-AOmK3UM4OTrl56K5JrqEw?pwd=c7ig 提取码: c7ig

[root@sql src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux

glibc2.12-x86_64.tar.gz

[root@sql src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /soft

[root@sql src]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql

 

3.进⾏ MySQL 初始化

//创建初始化⽬录以及数据库数据⽬录

[root@sql ~]# mkdir /soft/mysql/{mysql-file,data}

[root@sql ~]# chown mysql.mysql /soft/mysql/

//初始化数据库

[root@sql ~]# /soft/mysql/bin/mysqld --initialize \

--user=mysql --basedir=/soft/mysql \

--datadir=/soft/mysql/data

-------

//初始化数据库会告诉默认登陆账户与密码

[root@sql ~]# egrep 'temporary password is generated' /var/log/mysqld.log

2023-05-21T08:17:39.165590Z 1 [Note] A temporary password is generated for

root@localhost: Ug,R)avY8wje

//使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略]

[root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \

--datadir=/soft/mysql/data/

[root@sql ~]# chown -R mysql.mysql /soft/mysql/

 

4.建⽴ MySQL 配置⽂件

//mysql安装⽬录及mysql数据库⽬录

[root@sql ~]# cp /etc/my.cnf /etc/my.cnf_bak

[root@sql ~]# vim /etc/my.cnf

[mysqld]

basedir=/soft/mysql

datadir=/soft/mysql/data

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

socket=/tmp/mysql.sock

 

5.启动 MySQL 数据库

//⽅式1,使⽤ mysqld_safe

[root@sql ~]# /soft/mysql/bin/mysqld_safe --user=mysql &

//⽅式2, 使⽤(systemV)⽅式管理, [强烈推荐]

[root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@sql ~]# chkconfig --add mysqld

[root@sql ~]# chkconfig mysqld on

//修改安装⽬录与数据存放⽬录

[root@sql ~]# sed -i '/^basedir=/cbasedir=\/soft\/mysql' /etc/init.d/mysqld

[root@sql ~]# sed -i '/^datadir=/cdatadir=\/soft\/mysql\/data' /etc/init.d/mysqld

//启动数据库

[root@sql ~]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

//检查进程

[root@sql ~]# ps aux|grep mysql

//检查端⼝ netstat ss

[root@sql ~]# lsof -i :3306

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 2659 mysql 16u IPv6 28431 0t0 TCP *:mysql (LISTEN)

[root@sql ~]# ss -lntup|grep 3306

tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=2659,fd=16))

 

6.连接数据库测试

//默认情况没有mysql命令, 如果有可能使⽤过yum安装, 这样容易连错数据库(PATH路径存在命令执⾏优先级问题)

[root@sql ~]# mysql

-bash: mysql: command not found

//可以选择添加路径⾄PATH中, 或者直接使⽤绝对路径执⾏

[root@sql ~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile

[root@sql ~]# source /etc/profile

//登陆数据库

[root@sql ~]# mysql -uroot -p'Ug,R)avY8wje' -h 127.0.0.1

//默认系统配置数据库密码必须修改, 否则⽆法使⽤数据库

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 'Wing@123';

Query OK, 0 rows affected (0.01 sec)

mysql> exit;

//退出后使⽤新密码重新登录数据库

[root@sql ~]# mysql -uroot -p"Wing@123"

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.02 sec)

mysql> exit;

 

注意: 如果需要重新初始化[可选]

//如果重新初始化会导致数据全部丢失

[root@sql ~]# yum install -y psmisc

[root@sql ~]# killall mysqld

[root@sql ~]# rm -rf /soft/mysql/data/*

[root@sql ~]# /soft/mysql/bin/mysqld --initialize --user=mysql \

--basedir=/soft/mysql --datadir=/soft/mysql/data

#可不执⾏

[root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup --datadir=/soft/mysql/data

 

3.源码安装数据库

1.源码安装mysql需要依赖 cmake、boost

[root@sql ~]# yum install libaio-devel gcc gcc-c++ ncurses ncurses-devel cmake -y

[root@sql ~]# useradd -M -s /sbin/nologin mysql

[root@sql ~]# mkdir /soft/src/ -p

 

2.下载源码包并编译 MySQL

[root@sql ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-

5.7.22.tar.gz

[root@sql ~]# tar xf mysql-boost-5.7.22.tar.gz

[root@sql ~]# cd mysql-5.7.22/

[root@sql ~]# cmake -DCMAKE_INSTALL_PREFIX=/soft/mysql-5.7.22 \

-DMYSQL_UNIX_ADDR=/soft/mysql-5.7.22/data/mysql.sock \

-DMYSQL_DATADIR=/soft/mysql-5.7.22/data \

-DSYSCONFDIR=/soft/mysql-5.7.22/conf \

-DWITH_MYISAM_STORAGE_ENGINE=0 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_MEMORY_STORAGE_ENGINE=0 \

-DWITH_READLINE=1 \

-DMYSQL_TCP_PORT=3306 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DEXTRA_CHARSETS=all \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_BOOST=/soft/package/src/mysql-5.7.22/boost/boost_1_59_0

[root@sql ~]# make

[root@sql ~]# make install

 

3.完成后基本优化

[root@sql ~]# ln -s /soft/mysql-5.7.22 /soft/mysql

[root@sql ~]# mkdir /soft/mysql/data

[root@sql ~]# chown -R mysql.mysql /soft/mysql

 

4.准备 MySQL 基础配置⽂件

[root@sql ~]# vim /etc/my.cnf

[mysqld]

basedir=/soft/mysql

datadir=/soft/mysql/data

5.拷⻉ MySQL 程序启动⽂件

//拷⻉官⽅准备的启动脚本

[root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld

//添加为系统服务, 并设定开机⾃启动

[root@sql ~]# chkconfig --add mysqld && chkconfig mysqld on

 

6.初始化 MySQL

[root@sql ~]# /soft/mysql/bin/mysqld --initialize-insecure --user=mysql --

basedir=/soft/mysql --datadir=/soft/mysql/data

//启动MySQL

[root@sql ~]# /etc/init.d/mysqld start

//为mysql命令添加环境变量,以便后续简化执⾏命令

[root@sql ~]# echo >> "export PATH=/data/soft/mysql/bin:$PATH" /etc/profile

[root@sql ~]# source /etc/profile

//源码编译MySQL默认root没有密码

[root@sql ~]# mysql

 

4.变更数据库密码

1.更改 root 密码

//第⼀种⽅式, 需要知道密码

[root@sql ~]# mysqladmin -uroot -p'Wing@123' password 'Wing@123'

Warning: Since password will be sent to server in plain text, use ssl connection to

ensure password safety.

[root@sql ~]# mysql -uroot -p'Wing@123'

mysql>

//第⼆种⽅式, 登录MySQL, 修改相应表

mysql> update mysql.user set authentication_string=password('Wing@123') where

user='root';

mysql> flush privileges;

 

2.忘记 mysql root 密码

[root@sql ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables # 新增跳过授权表

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

#登陆就不需要填写任何账号密码

[root@sql ~]# mysql

//查看表字段

mysql> select user,host,authentication_string from mysql.user;

+---------------+-----------+-------------------------------------------+

 

| user | host | authentication_string |

+---------------+-----------+-------------------------------------------+

| root | localhost | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| root | % | *D9ED32292D8F86849C725F9D9FD2B7B2FC29F2BA |

+---------------+-----------+-------------------------------------------+

4 rows in set (0.00 sec)

3 rows in set (0.00 sec)

//如何根据authentication_string上⾯的加密密码,破解推理出来明⽂密码

https://www.cmd5.com/hash.aspx?s=123456

 

//5.7.6版本后更新密码⽅式

mysql> update mysql.user set authentication_string=password('Wing@123') where

user='root';

mysql> exit

//5.7.6版本前更新密码⽅式

mysql> update mysql.user set password=password('Wing@123') where user="root" and

host="localhost";

[root@sql ~]# vim /etc/my.cnf

[mysqld]

#skip-grant-tables #注释

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

//使⽤新密码登录数据库

[root@sql ~]# mysql -uroot -p'Wing@123'

mysql>

 

5. mysql 错误案例演示

 

重点 看⽇志 看⽇志 看⽇志

1. 查看⽇志 默认⽇志路径-> 定义错误⽇志

cat /etc/my.cnf

log-error=/var/log/mysqld.log

2. 权限错误等

检查权限是不是mysql

 

3. 分析mysql启动命令本质

[root@sql ~]# systemctl status mysqld

● mysqld.service - LSB: start and stop MySQL

Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)

Active: active (running) since ⼀ 2023-05-22 11:14:45 CST; 49s ago

Docs: man:systemd-sysv-generator(8)

Process: 130508 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)

Process: 130894 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited,

status=0/SUCCESS)

Main PID: 113248 (code=exited, status=0/SUCCESS)

Tasks: 28

Memory: 169.7M

CGroup: /system.slice/mysqld.service

├─130912 /bin/sh /soft/mysql/bin/mysqld_safe --datadir=/soft/mysql/data --

pid-file=/var/run/mysqld/mysqld.pid

└─131064 /soft/mysql/bin/mysqld --basedir=/soft/mysql --

datadir=/soft/mysql/data --plugin-dir=/soft/mysql/lib/plugin --user=mysql --log-er...

5⽉ 22 11:14:44 lb-node1 systemd[1]: Starting LSB: start and stop MySQL...

5⽉ 22 11:14:45 lb-node1 mysqld[130894]: Starting MySQL. SUCCESS!

5⽉ 22 11:14:45 lb-node1 systemd[1]: Started LSB: start and stop MySQL.

[root@sql ~]# md5sum /etc/rc.d/init.d/mysqld

aa1ac5e30b5066df79d5a1fce9861787 /etc/rc.d/init.d/mysqld

[root@sql ~]# md5sum /etc/init.d/mysqld

aa1ac5e30b5066df79d5a1fce9861787 /etc/init.d/mysqld

#停⽌分析

[root@sql ~]# bash -x /etc/init.d/mysqld stop

#启动分析

[root@sql ~]# bash -x /etc/init.d/mysqld start