mysql数据库

1.关系行数据库

关系型数据库（RDBMS）是一种用于存储相互关联的数据点并提供数据访问的数据库。它采用关系模型，直接、直观地在表中展示数据。在关系数据库中，表中的每一行都代表一条记录，每条记录都具有一个唯一的ID（又被称为键），而表中的列则用于存储数据的属性。通过这种方式，用户可以轻松地在数据点之间建立关联
关系（Relation）：
关系是关系型数据库中最基本的概念，通常表示为表。一个关系包含了多个元组（行）以及每个元组的属性（列）。在关系型数据库中，通常将表格称为关系
行（Tuple）：
行是关系中的一个实例，也称为记录或数据项。每一行包含了关系的一个实体的数据。例如，员工表中的每一行表示一个员工的信息
列（Attribute）：
列是关系中的一个属性，也称为字段或属性。每一列包含了关系中所有行共同的数据类型。例如，员工表中的列可以包括员工的姓名、年龄、工资等
主键（Primary Key）：
主键是关系中的一列或一组列，用于唯一标识每一行数据。主键的值必须唯一且不为空。在关系数据库中，每个表只能有一个主键
惟一键（Unique Key）：
惟一键是关系中的一列或一组列，用于确保该列或列组中的值是唯一的，但允许为空值。与主键不同的是，一个表可以有多个惟一键
域（Domain）：
域是数据类型的集合，用于定义列中允许存储的值的类型。例如，域可以定义为整数、字符串、日期等数据类型

2.关联类型

关联类型指的是数据库表之间的关系，其中包括 1 对 1、1 对 多和 多 对 多三种常见的关系类型
1 对 1 关系（One-to-One Relationship）：

• 在 1 对 1 关系中，一个实体的记录只能关联到另一个实体的一条记录
• 假设有两个表：Employee 和 EmployeeDetails。每个 Employee 记录代表一个员工，而 EmployeeDetails 记录存储有关员工的详细信息，如联系信息、工作历史等。在这种情况下，每个员工只有一条详细信息记录，而每个详细信息记录也只能关联到一个员工
  1 对 多 关系（One-to-Many Relationship）：
• 在 1 对 多 关系中，一个实体的记录可以关联到另一个实体的多条记录
• 假设有两个表：Department 和 Employee。每个 Department 记录代表一个部门，而 Employee 记录代表一个员工。在这种情况下，每个部门可以有多个员工，但每个员工只能属于一个部门
  多 对 多 关系（Many-to-Many Relationship）：
• 在 多 对 多 关系中，一个实体的记录可以关联到另一个实体的多个记录，并且反过来也成立
• 假设有两个表：Student 和 Course。每个 Student 记录代表一个学生，而 Course 记录代表一个课程。在这种情况下，一个学生可以选择多门课程，而一门课程也可以被多个学生选择

3.Mysql设计范式

关系型数据库设计范式是一组规则，用于规范数据库表的结构，以确保数据库中的数据不会产生冗余或不一致。MySQL 数据库的设计通常遵循三种范式:
第一范式（1NF）：

• 第一范式要求每个数据库表中的每一列都是原子性的，即每个单元格中的值都是不可再分的原子值。此外，每个表中的每一行都必须具有唯一标识，通常使用主键来实现
• 在一个学生表中，学生姓名和学生课程可以分别放在不同的列中，而不应该将一个学生的多门课程放在同一列中
  第二范式（2NF）：
• 第二范式要求表中的每一列都必须完全依赖于表的候选键（或主键），而不是部分依赖。换句话说，每个非主键列都必须直接依赖于整个主键，而不是只依赖于主键的一部分
• 在一个订单表中，订单号和产品号构成了复合主键。产品名和产品价格应该与订单号和产品号一起确定，而不是仅仅依赖于订单号或产品号
  第三范式（3NF）：
• 第三范式要求表中的每一列都必须直接依赖于主键，而不是依赖于其他非主键列。换句话说，不存在传递依赖
• ，在一个员工表中，员工的地址依赖于员工的 ID，而不应该依赖于员工的姓名或其他非主键列

4.事务的ACID特性

在数据库系统中，ACID 是一组用于确保事务的可靠性和一致性的特性。MySQL 是一种关系型数据库管理系统，支持 ACID 特性
原子性（Atomicity）：

• 原子性确保事务中的所有操作要么全部完成，要么全部失败。如果事务中的任何一部分操作失败，整个事务都会被回滚到事务开始之前的状态

• 如果一个银行转账操作包括从一个账户扣除金额和将金额添加到另一个账户，那么如果其中一个操作失败，整个转账操作都会被回滚，以确保账户余额的一致性
  一致性（Consistency）：

• 一致性确保事务在执行之前和之后，数据库都保持一致的状态。换句话说，事务必须遵循数据库的约束和规则，不会破坏数据库的完整性

• 如果一个数据库表的某个列被定义为 NOT NULL，那么任何尝试插入 NULL 值的事务都会被拒绝，以保持数据的一致性
  隔离性（Isolation）：

• 隔离性确保并发执行的事务彼此独立，互相不受影响。换句话说，每个事务应该感觉就像是在独占访问数据库一样，即使有其他事务在同时运行

• 如果一个事务正在读取数据库表中的数据，而另一个事务正在修改相同的数据，隔离性确保第一个事务不会看到第二个事务的修改，直到第二个事务提交
  持久性（Durability）：

• 持久性确保一旦事务提交，其所做的更改将永久保存在数据库中，即使系统发生故障或重启，数据也不会丢失

• 一旦一个订单被提交并写入数据库，即使系统发生故障，该订单的信息也应该保留在数据库中，以确保客户的订单数据不会丢失
  总结：
  这些 ACID 特性确保了数据库的可靠性和一致性，使得数据库系统能够有效地处理并发事务，同时保护数据的完整性

5.安装mysql

MySQL多种安装方式：
1.使用包管理器：

[14:29:39root@ubuntu ~]# apt -y install mysql-server
[14:30:02root@localhost ~]# yum -y install mysql-server
#缺点是不能定制化安装

2.使用官方安装包(二进制安装)：

#优点可以定制安装
#缺点安装比较繁琐
OS_ID=`sed -rn '/^NAME=/s@.*="([[:alpha:]]+).*"$@\1@p' /etc/os-release` 
OS_RELEASE_VERSION=`sed -rn '/^VERSION_ID=/s@.*="?([0-9]+)\.?.*"?@\1@p' /etc/os-release`
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz'
MYSQL='mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
URL=https://downloads.mysql.com/archives/get/p/23/file/
MYSQL_ROOT_PASSWORD=luomu147
color () {
    RES_COL=60
    MOVE_TO_COL="echo -en \\033[${RES_COL}G"
    SETCOLOR_SUCCESS="echo -en \\033[1;32m"
    SETCOLOR_FAILURE="echo -en \\033[1;31m"
    SETCOLOR_WARNING="echo -en \\033[1;33m"
    SETCOLOR_BLUE="echo -en \\033[1;34m"
    SETCOLOR_NORMAL="echo -en \E[0m"
    ${SETCOLOR_BLUE} "$1" && $MOVE_TO_COL
    ${SETCOLOR_NORMAL}
    echo -n "["
    if [ $2 = "success" -o $2 = "0" ] ;then
        ${SETCOLOR_SUCCESS}
        echo -n $"  OK  "    
    elif [ $2 = "false" -o $2 = "1"  ] ;then 
        ${SETCOLOR_FAILURE}
        echo -n $"FAILED"
    else
        ${SETCOLOR_WARNING}
        echo -n $"WARNING"
    fi
    ${SETCOLOR_NORMAL}
    echo -n "]"
    echo 
}
offline_check () {
    if [ $UID -eq 0 ]; then
        if [ -e /usr/local/mysql ];then
            color "数据库已存在，安装失败" false
            exit
        elif [   -e $MYSQL ];then
            $COLOR"${MYSQL}文件已存在选择离线安装"$END
            exit
        elif [[ $OS_ID =~ Rocky|Centos  ]];then
            rpm -ql wget &>/dev/null || yum -y install wget
            $COLOR"开始下载MySQL数据库..."$END
            wget $URL$MYSQL
            [ $? -eq 0 ] || { color 下载失败 1;exit; }
        elif [[ $OS_ID =~ Ubuntu  ]];then
            dpkg -l wget &>/dev/null || { apt update  && apt -y install wget;}
            $COLOR"开始下载MySQL数据库..."$END
            wget $URL$MYSQL
            [ $? -eq 0 ] || { color 下载失败 1;exit; }
        fi
    else
        color "当前用户不是root,安装失败" false
        exit 1
    fi
}
Online_check (){
    if [ $UID -eq 0 ]; then
        if [ !  -e $MYSQL ];then
            $COLOR"缺少${MYSQL}文件"$END
            $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
            exit
        elif [ -e /usr/local/mysql ];then
            color "数据库已存在，安装失败" false
            exit
        fi
    else
        color "当前用户不是root,安装失败" false
        exit 1
    fi
} 
install_mysql () {
    if [[ $OS_ID =~ Rocky|Centos  ]];then
        yum  -y -q install libaio numactl-libs ncurses-compat-libs 
        [ $OS_RELEASE_VERSION -eq 9 ] && ln -s /etc/rc.d/init.d /etc/init.d
        $COLOR"开始安装MySQL数据库..."$END
    elif [[ $OS_ID =~ Ubuntu  ]];then
        apt update &> /dev/null && apt -y  install numactl libaio-dev libtinfo5
        $COLOR"开始安装MySQL数据库..."$END
    fi
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; color "创建mysql用户" 0; }
    chown -R  mysql.mysql /usr/local/mysql/
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF

    [ -d /data ] || mkdir /data 
    mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    if [[ $OS_ID =~ Rocky|Centos  ]];then
        chkconfig --add mysqld
        chkconfig mysqld on
    elif [[ $OS_ID =~ Ubuntu  ]];then
        update-rc.d -f mysqld defaults
    fi
    chown -R mysql.mysql /data/mysql
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    sleep 3
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    color "数据库安装完成"  0
}
offline () {
    offline_check
    install_mysql
}
Online () {
    Online_check
    install_mysql
}
menu () {
    while true;do
        echo -e "\E[$[RANDOM%7+31];1m"
        cat <<-EOF
(1)在线安装MySQL
(2)离线安装MYSQL
(3)退出
EOF
       echo -e '\E[0m'
       read -p "请选择相应的编号(1-3): " choice
        case ${choice} in
        1)
            offline
            break
            ;;
        2)
            Online
            break
            ;;
        3)
            break
            ;;
        *)
            ${COLOR}"输入错误,请输入正确的数字(1-3)!"${END}
            ;;
        esac
    done
}
main () {
    menu
}
main

5.1安全加固

• 更改默认密码：安装完成后立即更改 MySQL 的默认密码，使用复杂、随机的密码
• 移除不必要的账户：禁用或删除不必要的 MySQL账户，特别是具有特权的账户
• 限制远程访问：限制远程访问 MySQL 数据库，只允许特定 IP 地址或 IP 地址范围访问
• 启用防火墙：使用防火墙阻止未经授权的访问到达 MySQL 端口
• 使用 SSL/TLS：启用 MySQL 的 SSL/TLS加密以保护数据在传输过程中的安全性

5.2mysql配置文件

[14:44:48root@localhost ~]# tree /etc/my.cnf.d 
/etc/my.cnf.d
├── client.cnf
├── mysql-default-authentication-plugin.cnf
└── mysql-server.cnf
#常见配置
datadir： 数据文件的存储目录。
port： MySQL 服务器监听的端口。
bind-address： MySQL 服务器绑定的 IP 地址。
max_connections： 允许的最大连接数。
key_buffer_size： 索引缓冲区的大小。
innodb_buffer_pool_size： InnoDB 缓冲池的大小。
log_error： 错误日志文件的路径。
slow_query_log： 慢查询日志是否启用

6.调整mysql字符集

服务器端配置更改：
使用包管理器：

[14:46:17root@localhost ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character-set-server=utf8mb4

客户端更改：

[14:46:17root@localhost ~]# vim /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8mb4

7.MYSQL数据库简要使用

7.1获取SQL命令帮助

mysql> \h #可以查询所有在使用help过滤
mysql> HELP CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

7.2创建testdb的数据库

#使用 SQL 命令来添加名为 testdb 的数据库，
#其字符集为 utf8，排序规则为 utf8_bin，
#并创建一个名为 host 的表，该表包含 id、host、ip 和 cname 等字段
mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)
#创建 testdb 数据库，并设置字符集和排序规则
mysql> USE testdb;
Database changed
#进入 testdb 数据库
mysql> create table host (
    -> id int unsigned auto_increment primary key,
    -> host varchar(24),
    -> ip varchar(16),
    -> cname varchar(24)
    -> );
Query OK, 0 rows affected (0.01 sec)
#创建 host 表
mysql> DESCRIBE host; #显示表结构
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| host  | varchar(24)  | YES  |     | NULL    |                |
| ip    | varchar(16)  | YES  |     | NULL    |                |
| cname | varchar(24)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

8.DDL和DML

DDL（数据定义语言）用于定义、修改和删除数据库对象，例如数据库、表、索引等。DML（数据操作语言）用于插入、查询、更新和删除数据库中的数据

8.1DDL

创建表（CREATE TABLE）：

mysql> CREATE TABLE users (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     username VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

修改表结构（ALTER TABLE）：

mysql> ALTER TABLE users ADD COLUMN age INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表（DROP TABLE）：

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.01 sec)

8.2 DML

插入数据（INSERT INTO）：

mysql> INSERT INTO users (username, email, age) VALUES ('wang', 'luomu', 30);
Query OK, 1 row affected (0.01 sec)

查询数据（SELECT）：

mysql> SELECT * FROM users WHERE age > 25;
+----+----------+-------+------+
| id | username | email | age  |
+----+----------+-------+------+
|  1 | wang     | luomu |   30 |
+----+----------+-------+------+
1 row in set (0.00 sec)

更新数据（UPDATE）：

mysql> UPDATE users SET age = 35 WHERE username = 'wang';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除数据（DELETE FROM）：

mysql> DELETE FROM users WHERE age > 40;
Query OK, 0 rows affected (0.00 sec)

9 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

[15:21:59root@localhost ~]# vim backup_mysql.sh
#!/bin/bash
# 
#********************************************************************************************
**
#Author:        luomu
DB_HOST="备份的服务器"
MySQL_USER="mysql账户名"
MySQL_PASS="密码"
DATA_NAME="需要备份的数据库"
# 备份目录
BACKUP_DIR="/data/mysql_BACKUP"
# 当前日期
DATE=$(date +%Y%m%d)
# 完整备份文件名
FULL_BACKUP_FILE="$BACKUP_DIR/full_backup_$DATE.xbstream"
# 增量备份文件名
INCREMENTAL_BACKUP_FILE="$BACKUP_DIR/incremental_backup_$DATE.xbstream"
# 执行完整备份
xtrabackup --backup --target-dir=$FULL_BACKUP_FILE --host=$DB_HOST --user=MySQL_USER --password=MySQL_PASS --databases=DATA_NAME
# 执行增量备份（周二到周四）
if [ $(date +%u) -ge 2 ] && [ $(date +%u) -le 4 ]; then
    xtrabackup --backup --target-dir=$INCREMENTAL_BACKUP_FILE --host=$DB_HOST --user=MySQL_USER --password=MySQL_PASS --databases=DATA_NAME --incremental-basedir=$FULL_BACKUP_FILE
fi
[15:22:30root@localhost ~]# crontab -e
# 每周一和周五凌晨四点执行完整备份
0 4 * * 1,5 /root/backup_mysql.sh