MySql数据库(八)

mysql数据库

数据库基础

数据库设计
对象关系分析 -- ER图
数据表设计 --

第一范式(属性值不能再次拆分)、

第二范式(所有的属性必须和主键有关)、

第三范式(和主键有直接关系)
数据库软件：mysql、redis、elasticsearch
mysql软件分支：mysql、mariadb、percona server
软件安装的方式：
二进制【apt|yum、rpm|dpkg】、
源码方式安装【预编译、纯源码】、
实例数量【单实例、多实例】

数据库环境

整体环境：
客户端环境 mysql 、mysql -u(用户名) -p(密码) -P(指定数据库端口号，默认3306) -h(指定数据库服务器IP或主机地址远程连接专用)
服务端环境 mysqld(主进程) + 配置文件 /etc/my.cnf
客户端环境命令：\s(查看数据库当前状态信息，版本 ，端口) \r(重新连接数据库) \h(查看帮助命令) \c(终止，取消当前输入)
服务端环境命令：sql
类型：

DDL(data definition language 数据定义语言：create alter drop truncate(删表)delect(删行))、

DCL(data control language:数据控制语言：grant 授权 revoke 授权)

DQL(data query language 数据查询语言：select from where group by having分组排序，limt分页)

DML(data manipulation language 数据操纵语言：insert update delete )

TCL(transaction control language 事务控制语言：commit rollback savepoint保存点)
数据库级别：show、create、drop、alter
数据表级别：show、create、desc

mysql数据库

sql语句

table操作语句：create alter、drop、
数据操作语句：insert、update、delete、truncate
扩展对象：视图、函数、存储过程、触发器、事件、事务、
DCL:
create user user@'10.0.0.%' identified by '密码';
alter user user@'10.0.0.%' identified by '密码';
grant all on *.* to user@'10.0.0.%' with grant option;

图形化工具：navicat

mysql的结构

引擎

MyISAM：特点：无事务、速度快【MYD(数据)、MYI(索引)、frm(表结构)、sid(系统标识文件,标记数据表唯一身份编号)】、

InnoDB【idb(数据 + 索引)、frm(表结构)】

配置

全局配置【配置文件,永久生效，所有会话通用】

会话配置【set,用 set 命令设置，仅当前连接生效，断开失效】、

select、show variables like '%%';--------模糊查询系统变量

索引

单独存储、附加在数据表的字段上，数据格式B+树、单表理论上可以存储数千万条数据
创建索引：create index 名 on 表(字段)
删除：drop index 名

事务

特点：ACID

1. 原子性：整体执行，全成或全回滚

2. 一致性：事务前后数据状态合法统一

3. 隔离性：并发事务彼此互不干扰

4. 持久性：提交后数据永久生效
问题：脏读、不可重复读、幻读
策略：读未提交、读已提交、可重复读【默认】、序列化
命令：begin、commit、rollback

日志

事务：redo log、undo log
查询：通用、错误、慢查询日志
集群：二进制日志、中继日志
二进制日志命令：
show master logs; show binary logs;
show master status; show binary log status;
reset master; reset binary logs and gtids;
purge master logs to ‘xxx’;
show binary log events;

备份

类型：全量、增量、差异、部分、热、冷、温、物理、逻辑

1. 全量：完整拷贝全部数据 2. 增量：仅备份上次备份后新增改动 3. 差异：备份最近全量后的变动数据 4. 部分：仅备份指定局部数据 5. 热备份：业务运行中备份，无需停服 6. 冷备份：停机离线状态下备份 7. 温备份：低负载时段执行备份 8. 物理备份：直接拷贝磁盘文件块 9. 逻辑备份：解析导出数据表数据

mysqldump：[-A -B -F ] [库] [表]

-A：备份所有数据库 -B：指定备份库，带建库语句 -F：刷新日志 [库]：指定要备份的数据库 [表]：指定要备份的表

主从复制

Ubuntu24.04 MySQL8.0 主从复制部署（192.168.9.106 主，192.168.9.107 从）

两台机器统一基础操作如下

安装 MySQL

apt update apt install mysql-server -y

放行防火墙（二台都执行）

ufw allow 3306/tcp

主库配置 192.168.9.106

修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] # 监听所有地址 bind-address = 0.0.0.0 # 主从唯一ID，主库=1 server-id = 1 # 开启二进制日志 log_bin = /var/log/mysql/mysql-bin.log # 日志格式 binlog_format = ROW # 可选：只同步指定库 # binlog_do_db = testdb # 跳过系统库 binlog_ignore_db = mysql expire_logs_days = 7

重启 mysql

systemctl restart mysql

登录 MySQL 创建复制账号

mysql -uroot -p

# 创建从库连接账号 CREATE USER 'repl'@'192.168.9.107' IDENTIFIED BY 'Repl@123456'; # 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.9.107'; FLUSH PRIVILEGES; # 锁表，禁止写入，记录binlog位置 FLUSH TABLES WITH READ LOCK; # 查看主库状态，记住 File 和 Position SHOW MASTER STATUS;

输出示例（保存这两个值，后面从库要用）

File: mysql-bin.000001

Position: 156

新开终端导出数据，不要关闭当前 mysql 会话，关闭会自动解锁

全量备份主库数据

mysqldump -uroot -p --all-databases --master-data=2 --single-transaction > all_db.sql

把 all_db.sql 传到从库

scp all_db.sql root@192.168.9.107:/tmp/

备份完成后回到 mysql 会话解锁

UNLOCK TABLES;

从库配置 192.168.9.107

修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] bind-address = 0.0.0.0 # server-id必须和主库不同，设为2 server-id = 2 # 开启中继日志 relay_log = /var/log/mysql/relay-bin.log log_slave_updates = 0 # 从库只读 read_only = ON super_read_only = ON

重启 MySQL

systemctl restart mysql

导入主库备份（全新空环境可跳过）

mysql -uroot -p < /tmp/all_db.sql

登录 MySQL 配置主从关联

mysql -uroot -p

执行 SQL，替换下面 FILE、POSITION 为主库 show master status 查到的值

STOP SLAVE; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST='192.168.9.106', MASTER_USER='repl', MASTER_PASSWORD='Repl@123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156; START SLAVE;

验证从库状态

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

IO No：网络不通、账号密码错误、主库 IP / 端口不对

SQL No：数据不一致、主键冲突、备份不全

常用排错命令

# 查看mysql日志 tail -f /var/log/mysql/error.log # 重启从同步 STOP SLAVE;START SLAVE; # 重建从同步（数据不一致时慎用） RESET SLAVE ALL;

MySQL8.0 默认密码认证插件，如果遇到 repl 账号连接报错，执行

ALTER USER 'repl'@'192.168.9.107' IDENTIFIED WITH mysql_native_password BY 'Repl@123456';

若两台机器全新无任何数据，可以不用 mysqldump 备份，直接搭建主从。

集群

类型: 复制风格、分布式风格
mysql集群：主从复制原理
三个前提、三个线程、两个日志

1. 复制风格：单机数据同步备份 2. 分布式风格：多节点拆分存储调度 3. 主从复制三前提：主库开启 binlog、主从 server-id 不同、主从网络互通 4. 三个线程：主库 dump 线程、从库 IO 线程、从库 SQL 线程 5. 两个日志：二进制日志 binlog、中继日志 relay-log

msyql的主从复制原理

主库开启 binlog，节点 ID 唯一，网络连通 数据变更写入二进制日志 dump 线程向外推送日志数据 从库 IO 线程接收，存入中继日志 SQL 线程回放日志执行语句 最终主从数据保持同步

mysql集群

一主一从【新环境、旧环境】、一主多从、级联复制、双主复制、复制过滤器、半同步复制、GTID复制
遇到问题的通用处理流程：
- 分析问题、定位错误关键字、解决数据本身的问题、忽略错误
- 分析io问题、分析sql问题、分析程序问题

一主一从（新环境） 主库开启 binlog → 从库全新配置 → 直接建立同步 → 无历史数据干扰 一主一从（旧环境） 先备份主库数据 → 导入从库 → 配置同步点位 → 启动复制追数据 一主多从 一个主库 → 分给多个从库 → 从库各自独立同步 → 实现读写分离、负载分摊 级联复制 主 → 从 1 → 从 2 主库只给从 1 发数据 → 从 1 再同步给从 2 → 减轻主库压力 双主复制 两台互为主从 → 都能写 → 自动互相同步 → 高可用切换 复制过滤器 只同步指定库 / 表 → 过滤不需要的数据 → 节省带宽、存储空间 半同步复制 主库写数据 → 至少一个从库接收成功 → 主才返回成功 → 数据更安全 GTID 复制 全局事务 ID → 自动定位同步位置 → 不用手动找点位 → 搭建、故障恢复更简单

中间件

功能：读写分离、负载均衡、分库分表
软件：mycat、proxySQL

高可用集群

MGR集群【多主模式、单主模式[默认的-只有 1 个节点可写，其他只读，自动选主]】
一主一从【有数据的、GTID|日志】 2个集群(有数据先备份导入再同步，无数据直接配)

rocky下部署mysql过程

1.关闭防火墙、SELinux

systemctl stop firewalld systemctl disable firewalld setenforce 0 vim /etc/selinux/config

2.安装依赖

yum install -y libaio-devel ncurses-devel numactl net-tools wget

3.创建 MySQL 用户和数据目录

useradd -r -s /sbin/nologin mysql mkdir -p /data/mysql chown -R mysql:mysql /data/mysql chmod -R 755 /data/mysql

4.下载 / 上传 MySQL 安装包并解压

cd /usr/local wget https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz tar -xf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz mv mysql-8.0.36-linux-glibc2.28-x86_64 mysql chown -R mysql:mysql /usr/local/mysql

5.编写 MySQL 配置文件 /etc/my.cnf

vim /etc/my.cnf

[mysqld] basedir = /usr/local/mysql datadir = /data/mysql socket = /tmp/mysql.sock port = 3306 user = mysql server-id = 1 log-bin = mysql-bin binlog_format = row character-set-server = utf8mb4 default_storage_engine = InnoDB max_connections = 2000

6.初始化 MySQL

/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql

7.配置系统服务，开机自启

vim /etc/systemd/system/mysqld.service

[Unit] Description=MySQL Server Documentation=man:mysqld(8) After=network.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf ExecStop=/usr/local/mysql/bin/mysqladmin shutdown Restart=on-failure [Install] WantedBy=multi-user.target

8.配置环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile source /etc/profile

9.登录 MySQL 并修改密码

mysql -uroot

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; flush privileges; exit;

测试登录

mysql -uroot -p123456

ubuntu下部署mariadb过程

1.替换系统软件源

sed -i "s/cn.archive.ubuntu.com/mirrors.aliyun.com/g" /etc/apt/sources.list.d/ubuntu.sources sed -i "s/security.ubuntu.com/mirrors.aliyun.com/g" /etc/apt/sources.list.d/ubuntu.sources apt update

2,.安装工具并导入密钥

apt install -y apt-transport-https curl mkdir -p /etc/apt/keyrings curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'

3.编辑 MariaDB 源文件

vim /etc/apt/sources.list.d/mariadb.sources

Types: deb URIs: https://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/11.8/ubuntu Suites: noble Components: main main/debug Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp

4.更新源并安装程序

apt update apt install mariadb-server -y

5.查验运行状态

systemctl status mariadb getent passwd mysql ls -lh /var/lib/mysql/

6.本地登录数据库

mariadb

mariadb

使用crontab和mysqldump完成每一个小时完成一次数据库备份，并且完成一次数据恢复

准备两台主机：主 rocky10-12、从 rocky10-15

1.统一关闭防火墙、关闭 selinux

systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i 's/^SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

2.安装启动 MySQL

dnf install mysql-server -y systemctl start mysqld systemctl enable mysqld

3.设置 root 密码为 123456

mysqladmin -uroot password '123456'

以上两台主机统一操作

主节点 rocky10-12 配置

vi /etc/my.cnf

[mysqld] server-id=12 log-bin=mysql-bin binlog-format=ROW

4.重启服务

systemctl restart mysqld

5.创建同步账号并授权

mysql -uroot -p123456

6.执行 SQL语句

create user 'repl'@'%' identified by 'repl123'; grant replication slave on *.* to 'repl'@'%'; flush privileges; show master status;

从节点 rocky10-15 配置

1.编辑配置文件

vi /etc/my.cnf

[mysqld] server-id=15 relay-log=mysql-relay-bin read_only=1

2.重启服务

systemctl restart mysqld

3.关联主库开启同步

mysql -uroot -p123456

替换主库 IP、日志文件、位点后执行

stop slave; change master to master_host='rocky10-12', master_user='repl', master_password='repl123', master_log_file='mysql-bin.000001', master_log_pos=157; start slave;

4.查看检查主从状态

show slave status\G

Slave_IO_Running=yes Slave_SQL_Running=yes

从节点配置每小时定时备份

1.创建备份目录

mkdir -p /home/backup

2.编写备份脚本

vi /home/backup/backup_mysql.sh

#!/bin/bash TIME=$(date +%Y%m%d_%H%M%S) mysqldump -uroot -p123456 --all-databases --single-transaction --dump-slave=2 > /home/backup/mysql_${TIME}.sql

3.赋予执行权限

chmod +x /home/backup/backup_mysql.sh

4.测试脚本

/home/backup/backup_mysql.sh

5.crontab 设置每小时整点备份

crontab -e

添加一行

0 * * * * /home/backup/backup_mysql.sh

6.查看任务

crontab -l

数据恢复

1.停止从库同步

mysql -uroot -p123456 -e "stop slave;"

2.选取最新备份文件恢复

ls /home/backup mysql -uroot -p123456 < /home/backup/对应备份文件名.sql

3.重启同步并查看

mysql -uroot -p123456 -e "start slave;" mysql -uroot -p123456 -e "show slave status\G"