Mysql的安装及客户端使用

最新推荐文章于 2024-06-18 10:32:44 发布

NeaWalke

最新推荐文章于 2024-06-18 10:32:44 发布

阅读量401

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/NeaWalke/article/details/119866795

版权

数据库介绍

数据结构模型

数据结构模型：

层次模型
网状结构
关系模型

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

MySQL介绍

MySQL 是一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。
该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。

MySQL特点

功能强大
支持跨平台
运行速度快
支持面向对象
安全性高
成本低
支持各种开发语言
数据库存储容量大
支持强大的内置函数

MySQL

配置Mysql的yum源

[root@node3 ~]# wget -O /usr/src/mysql57-community-release-el7-10.noarch.rpm http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

[root@node3 ~]# rpm -Uvh /usr/src/mysql57-community-release-el7-10.noarch.rpm
警告：/usr/src/mysql57-community-release-el7-10.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql57-community-release-el7-10 ################################# [100%]

安装mariadb

[root@node3 ~]# dnf -y install mariadb mariadb-server

[root@node3 ~]# systemctl enable --now mariadb

[root@node3 ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port        Peer Address:Port   
LISTEN    0         80                       *:3306                   *:*

修改mysql密码

[root@node3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set password = password('123');
Query OK, 0 rows affected (0.000 sec)

Mysql程序组成

客户端

mysql：CLI交互式客户端程序
mysql_secure_installation：安全初始化
mysqldump：mysql备份工具
mysqladmin

服务器

mysqld

二进制安装mysql

下载二进制安装包
链接: mysql.

# 创建用户和组
[root@node2 ~]# useradd -r -M -s /bin/nologin mysql

# 解压软件包
[root@node2 ~]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@node2 local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@node2 local]# ll
总用量 0
lrwxrwxrwx  1 root root 47 8月  26 09:48 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/

# 修改mysql属主属组
[root@node2 local]# chown -R mysql.mysql mysql*
[root@node2 local]# ll
总用量 0
lrwxrwxrwx  1 mysql mysql 47 8月  26 09:48 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x  2 mysql mysql  6 8月  26 09:48 mysql-5.7.34-linux-glibc2.12-x86_64

# 添加环境变量
[root@node2 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@node2 local]# source /etc/profile.d/mysql.sh 
[root@node2 local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

# 建立数据存放目录
[root@node2 local]# mkdir /opt/data
[root@node2 local]# chown -R mysql.mysql /opt/data/

# 初始化数据库
[root@node2 ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2021-08-26T02:45:27.330010Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-26T02:45:27.472069Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-26T02:45:27.497168Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-26T02:45:27.552303Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: aba7437f-0617-11ec-8ff3-000c29264193.
2021-08-26T02:45:27.553045Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-26T02:45:27.973462Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-26T02:45:28.018388Z 1 [Note] A temporary password is generated for root@localhost: (afy-h%PE4ir


# 生成配置文件
[root@node2 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

# 修改配置文件
[root@node2 support-files]# vim mysql.server
basedir=/usr/local/mysql
datadir=/opt/data

# 启动脚本
[root@node2 ~]# /usr/local/mysql/support-files/mysql.server start

# 安装依赖包
[root@node2 ~]# yum -y install ncurses-compat-libs

[root@node2 ~]# mysql -uroot -p'(afy-h%PE4ir'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

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> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye

# 以service服务启动mysql，
[root@node2 system]# cat mysqld.service 
[Unit]
Description=mysql server daemon
After=network.targe

[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.serve stop
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target


[root@node2 system]# systemctl daemon-reload
[root@node2 system]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

脚本安装mysql


#!/bin/bash

mysql_version=5.7.34
install_dir=/usr/local
storage_dir=/opt/data

if [ $UID -ne 0 ];then
        echo "请以管理员执行"
        exit
fi

read -p "请输入创建的实例个数：" instance_count
if [ -z $instance_count ];then
    instance_count=1
fi

echo $instance_count | grep -E "^[0-9]+$" &>/dev/null
if [ $? -ne 0 ];then
    instance_count=1
fi

read -p "请输入你要设置的数据库密码：" password
if [ -z $password ];then
    password=123
fi


function single(){
    if [ ! -d $storage_dir ];then
	    mkdir -p $storage_dir
    fi
    chown -R mysql.mysql $storage_dir
    content=$(ls $storage_dir | wc -l)
    if [ $content -eq 0 ];then
    $install_dir/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=$storage_dir
    fi
    cat > /etc/my.cnf <<EOF
[mysqld]
basedir = $install_dir/mysql
datadir = $storage_dir
socket = /tmp/mysql.sock
port = 3306
pid-file = $storage_dir/mysql.pid
user = mysql
skip-name-resolve
EOF
    sed  -i "s!^basedir=!basedir=/usr/local/mysql!g;s!^datadir=!datadir=/opt/data!" /usr/local/mysql/support-files/mysql.server
    cat > /usr/lib/systemd/system/mysqld.service <<EOF
[Unit]
Description=mysql server daemon
After=network.targe

[Service]
Type=forking
ExecStart=$install_dir/mysql/support-files/mysql.server start
ExecStop=$install_dir/mysql/support-files/mysql.serve stop
ExecReload=/bin/kill -HUP \$MAINPID

[Install]
WantedBy=multi-user.target
EOF
    
    systemctl daemon-reload
    systemctl enable --now mysqld

    $install_dir/mysql/bin/mysql -uroot -e "set password=password(\"$password\")"
    echo -e "\e[31;40m数据库密码为$password\e[0m"
}

function multi(){
    port=3305
    if [ ! -e /etc/my.cnf ];then
        touch /etc/my.cnf
    else
   	cp /etc/my.cnf /tmp/my.cnf
    fi

    cat > /etc/my.cnf <<EOF
[mysqld_multi]
mysqld = $install_dir/mysql/bin/mysqld_safe
mysqladmin = $install_dir/mysql/bin/mysqladmin

EOF
    for i in $(seq $instance_count);do
        let port++
	if [ ! -d $storage_dir/$port ];then
	    mkdir -p $storage_dir/$port
	    chown -R mysql.mysql $storage_dir
        fi
        content=$(ls $storage_dir/$port | wc -l)
        if [ $content -eq 0 ];then
            $install_dir/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=$storage_dir/$port
        fi
	cat >> /etc/my.cnf <<EOF
[mysqld$port]
datadir = $storage_dir/$port
port = $port
socket = /tmp/mysql$port.sock
pid-file = $storage_dir/$port/mysql.pid
log-error = /var/log/$port.log
	
EOF
    $install_dir/mysql/bin/mysqld_multi start $port

    sleep 5    

    $install_dir/mysql/bin/mysql -uroot -e "set password=password('$password')" -h127.0.0.1 -P$port
    $install_dir/mysql/bin/mysqld_multi stop $port
    done
    cat > /usr/lib/systemd/system/mysqld.service <<EOF
[Unit]
Description=mysql server daemon
After=network.targe

[Service]
Type=forking
ExecStart=$install_dir/mysql/support-files/mysqld_multi.server start
ExecStop=$install_dir/mysql/support-files/mysqld_multi.server stop
ExecReload=/bin/kill -HUP

[Install]
WantedBy=multi-user.target
EOF

    sed -i 's!^bindir=/usr/local/mysql/bin!bindir=/usr/local/mysql/bin\nexport PATH=\$bindir:\$PATH!g' mysqld_multi.server
    systemctl daemon-reload
    systemctl enable --now mysqld
}


id mysql
if [ $? -ne 0 ];then
    useradd -r -M -s /sbin/nologin mysql
fi

#Install dependent software
yum -y install ncurses-compat-libs perl

#unzip package
if [ ! -d $install_dir/mysql-${mysql_version}-linux-glibc2.12-x86_64 ] && [ ! -d $install_dir/mysql ];then
    tar xf soft/mysql-${mysql_version}-linux-glibc2.12-x86_64.tar.gz -C $install_dir
    ln -s $install_dir/mysql-${mysql_version}-linux-glibc2.12-x86_64/ $install_dir/mysql
fi

chown -R mysql.mysql /usr/local/mysql*
echo "export PATH=$install_dir/mysql/bin:\$PATH" > /etc/profile.d/mysql.sh

if [ $instance_count -eq 1 ];then
    single
else
    multi
fi

mysql破解密码

绕过密码验证
将skip-grant-tables加入/etc/my.cnf
重启服务
systemctl restart mysqld
修改密码
use mysql
updata mysql.user set authentication_string = password('123456') where User = 'root' and Host = 'localhost';
恢复密码验证
从/etc/my.cnf 文件中删除skip-grant-tables
重启服务
systemctl restart mysqld