部署-Day3

1. Mysql有哪些常用版本，有哪些可以替代MySQL的服务

MySQL 常用版本包括：

1. MySQL 5.7：这是长期以来的稳定版本，广泛用于生产环境。它在性能、安全性和功能方面都有较好的表现。

2. MySQL 8.0：最新主流版本，引入了诸多新特性，如窗口函数、更强大的 JSON 支持、改进的安全性（如默认身份验证插件 caching_sha2_password）和优化器增强。

3. MySQL 8.4：作为 MySQL 8.0 的扩展版本，提供了更多的性能优化和新功能，适合需要最新特性的场景。

可替代 MySQL 的服务

1. PostgreSQL

   • 功能丰富的关系型数据库，支持高级特性如 JSONB 数据类型、地理空间数据处理等。

   • 适用于复杂查询、大规模数据分析和高并发场景。

2. MariaDB

   • MySQL 的一个分支，由原 MySQL 开发者维护，兼容 MySQL 协议和语法。

   • 提供了额外的存储引擎、性能优化和新功能。

   • 适合需要与 MySQL 兼容但希望获得更好性能或新特性的用户。

3. Oracle Database

   • 强大的企业级关系型数据库，支持高可用性、分布式架构和复杂的事务处理。

   • 适合大型企业应用，尤其是对数据一致性和安全性要求极高的场景。

4. Microsoft SQL Server

   • Windows 平台下的主流关系型数据库，提供完整的数据管理解决方案。

   • 适合与 Microsoft 生态系统集成的应用场景。

5. SQLite

   • 轻量级嵌入式数据库，无需独立的服务器进程。

   • 适合小型应用、移动应用或开发原型时使用。

6. TiDB

   • 分布式 NewSQL 数据库，兼容 MySQL 协议，支持水平扩展。

   • 适合需要高可用性、强一致性以及海量数据处理的场景。

7. Amazon Aurora

   • AWS 提供的云数据库服务，兼容 MySQL 和 PostgreSQL。

   • 提供高性能、高可用性和自动扩展能力，适合云原生应用。

2. 任意方法安装LTS版本的Mysql

ubuntu 安装 LTS 版本 MySQL

1. 更新系统软件包

sudo apt update

2. 安装 MySQL 服务

Ubuntu 软件仓库中通常包含 LTS 版本的 MySQL，默认安装的是稳定版本：

sudo apt install -y mysql-server

3. 验证 MySQL 是否安装成功

sudo systemctl status mysql

4. 初始化安全设置（可选）

MySQL 提供了一个安全脚本，用于配置基本的安全选项（如 root 密码、匿名用户移除等）：

sudo mysql_secure_installation

5. 登录 MySQL

sudo mysql -u root -p

3. mysql的配置文件在哪里，其默认端口是多少。

MySQL 的配置文件位置和默认端口如下：

默认端口

• MySQL 默认端口是 3306，这是大多数操作系统和发行版的标准设置。

MySQL 配置文件位置

MySQL 的主配置文件通常是 my.cnf 或 my.ini，具体路径取决于你的操作系统：

4. Mysql的数据文件是什么格式保存在哪里？

MySQL 支持多种存储引擎，不同引擎使用不同的数据文件格式：

1. InnoDB（默认存储引擎）

• 表空间文件：每个表一个 .ibd 文件（启用 innodb_file_per_table=ON）

• 共享表空间文件：ibdata1（如果未启用独立表空间）

• 事务日志文件：ib_logfile0 和 ib_logfile1

• 数据字典文件：mysql.ibd, sys.ibd 等

推荐使用 InnoDB，支持事务、崩溃恢复、行级锁等高级特性。

2. MyISAM

• 数据文件：.MYD（MyISAM Data）

• 索引文件：.MYI（MyISAM Index）

MyISAM 不支持事务，不推荐用于新项目。

3. Memory / Heap

• 数据保存在内存中，重启后丢失。

• 文件扩展名一般为 .frm（表结构定义）

4. Archive

• 用于归档日志类数据，压缩率高。

• 文件扩展名为 .arc

5. CSV

• 数据以纯文本 CSV 格式存储。

• 文件扩展名为 .csv

6. BLACKHOLE

• 所有写入操作都会被丢弃，适用于测试或复制场景。

• 无实际数据文件，仅保留表结构（.frm）

表结构定义文件（所有引擎通用）

• .frm 文件：每个表都有一个 .frm 文件，存储表结构定义（列名、类型、约束等）。

• 即使使用 InnoDB 或其他引擎，也会生成 .frm 文件。

查看某个数据库的文件结构

假设你有一个数据库名为 test_db，它包含一张表 users，使用 InnoDB 引擎：

cd /var/lib/mysql/test_db/ ls -l

输出示例：

-rw-r----- 1 mysql mysql 8658 Jul 11 14:00 users.frm
-rw-r----- 1 mysql mysql 98304 Jul 11 14:00 users.ibd

• users.frm：表结构定义

• users.ibd：InnoDB 表空间文件

5. Mysql的日志文件保存在哪里？

MySQL 的日志文件位置取决于你的系统配置和 MySQL 的设置。以下是常见的日志类型及其默认保存路径：

常见日志文件类型及作用

如何查看当前日志文件路径？

你可以通过以下 SQL 命令查看各类日志的实际路径：

SHOW VARIABLES LIKE 'log_error'; -- 错误日志路径 
SHOW VARIABLES LIKE 'general_log_file'; -- 查询日志路径 
SHOW VARIABLES LIKE 'slow_query_log_file';-- 慢查询日志路径 
SHOW VARIABLES LIKE 'log_bin_index'; -- 二进制日志索引文件

6. 在Mysql中创建一个库，再创建一个表，表插入10万条任意数据

1. 创建数据库

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

2. 创建一张示例表

我们创建一个简单的用户表 users：

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 插入 10 万条测试数据

MySQL 本身不支持直接批量插入大量数据的 SQL 命令，因此我们可以使用存储过程来高效生成数据。

DELIMITER $$

CREATE PROCEDURE insert_users(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_records DO
        INSERT INTO users (name, email) VALUES
            (CONCAT('User-', i), CONCAT('user', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

调用存储过程插入 10 万条数据：

CALL insert_users(100000);

4. 验证插入结果

你可以执行以下命令验证是否成功插入了 10 万条数据：

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

7. 如何备份数据库，什么是逻辑备份什么是物理备份，分别使用什么工具

在 MySQL 中，数据库备份是保障数据安全的重要措施。常见的备份方式分为 逻辑备份 和 物理备份，它们适用于不同的场景和需求。

一、什么是逻辑备份？

逻辑备份 是指通过 SQL 语句（如 CREATE TABLE、INSERT）将数据库的结构和内容导出为文本文件。这种方式备份的是数据库的“逻辑内容”。

常用工具：mysqldump

示例命令：

# 备份整个数据库
mysqldump -u root -p test_db > test_db_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_dbs_backup.sql

# 只备份表结构（不包含数据）
mysqldump -u root -p --no-data test_db > test_db_structure.sql

# 只备份数据（不包含结构）
mysqldump -u root -p --no-create-info test_db > test_db_data.sql

优点：

• 可读性强，易于编辑；

• 跨平台兼容性好；

• 支持恢复到不同版本的 MySQL；

• 可以选择性地恢复某个库或表。

缺点：

• 备份和恢复速度慢；

• 占用较多磁盘空间；

• 不适合超大数据量（如 TB 级别）。

二、什么是物理备份？

物理备份 是直接复制 MySQL 的底层数据文件（如 .ibd、.frm 文件），通常用于快速恢复整个实例或大量数据。

常用工具：

. Percona XtraBackup

• 支持热备份（即在不停止数据库服务的情况下进行备份）。

• 支持 InnoDB 引擎的增量备份。

• 官方推荐用于生产环境的大规模备份。

# 全量备份
xtrabackup --backup --target-dir=/backup/full/

# 准备备份（应用日志）
xtrabackup --prepare --target-dir=/backup/full/

# 恢复备份（需关闭 MySQL）
sudo systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

2. 文件系统级拷贝（仅限停止状态）

如果你确定数据库处于停止状态，可以直接使用 cp 或 rsync 复制数据目录：

sudo cp -r /var/lib/mysql /backup/mysql_data_$(date +%F)

优点：

• 备份和恢复速度快；

• 占用空间相对较小；

• 适合大规模数据库（GB~TB 级别）。

缺点：

• 不可跨平台恢复；

• 需要精确匹配 MySQL 版本；

• 不能只恢复部分表或记录；

8. 什么是热备份，什么是冷备份。什么是全量备份，什么是增量备份

在数据库管理中，备份策略是保障数据安全和系统可用性的关键。以下是四种常见备份类型的概念及其使用场景：

一、热备份（Hot Backup）

定义：

热备份 是指在 数据库持续运行、对外提供服务的状态下 进行的备份。它不会中断数据库的读写操作。

特点：

• 数据库处于“在线”状态；

• 支持并发访问；

• 备份过程不影响业务；

• 要求备份工具支持一致性快照（如 XtraBackup）；

常用工具：

• Percona XtraBackup

• 文件系统快照（LVM、ZFS）

• 商业数据库（如 Oracle RMAN）

优点：

• 不影响业务连续性；

• 支持大规模生产环境；

• 可用于高可用架构下的容灾恢复。

缺点：

• 技术复杂度较高；

• 对硬件或文件系统有依赖；

• 需要一致性保证机制。

二、冷备份（Cold Backup）

定义：

冷备份 是指在 数据库完全停止运行的状态下 进行的备份。此时数据库不接受任何读写请求。

特点：

• 数据库处于“离线”状态；

• 所有事务已完成并落盘；

• 简单直接，适用于小型系统；

• 恢复速度快；

常用方法：

• 直接复制数据目录（如 /var/lib/mysql/）

• 使用 tar 或 rsync 工具备份整个数据文件

优点：

• 简单可靠；

• 恢复速度快；

• 不需要复杂的日志应用步骤。

缺点：

• 必须停机，影响业务连续性；

• 不适合大型生产环境；

• 数据可能不是最新的（最后一次关闭时的状态）。

三、全量备份（Full Backup）

定义：

全量备份 是指对数据库中的 所有数据 进行一次完整的拷贝。无论之前是否做过备份，每次都完整保存全部数据。

示例：

• 整个数据库的 SQL 导出（mysqldump --all-databases）

• 全部 InnoDB 表空间文件的拷贝（XtraBackup 全备）

优点：

• 恢复简单，只需一份备份即可还原；

• 安全性高，数据完整性好；

• 适合首次备份或周期较长的备份策略。

缺点：

• 占用存储空间大；

• 备份时间长；

• 不适合频繁执行。

四、增量备份（Incremental Backup）

定义：

增量备份 是指只备份 自上次备份以来发生变化的数据。它可以基于全量备份进行多次增量备份。

示例：

• MySQL 中通过 XtraBackup 的 --incremental 参数实现；

• 基于 Binlog 的变更记录；

• 文件系统的差异拷贝（如 rsync + 时间戳过滤）

优点：

• 存储空间占用小；

• 备份速度快；

• 支持高频次备份（如每小时一次）；

• 减少数据丢失风险。

缺点：

• 恢复流程复杂，需先恢复全量再依次应用增量；

• 如果某一个增量损坏，后续备份将无法使用；

• 对备份工具和一致性要求高。

五、四种备份方式对比表

9. 备份上面创建的库

备份整个数据库

mysqldump -u root -p test_db > test_db_backup_$(date +%F).sql

10. 如何还原数据库

mysql -u root -p test_db < test_db_backup_2025-07-11.sql

11. 在另一台机器上创建同版本的Mysql，之后搭建主从复制

搭建 MySQL 主从复制的步骤如下，包括在另一台机器上创建同版本的 MySQL：

1. 确保主库和从库的 MySQL 版本一致

在主库上查看 MySQL 版本：

mysql --version

2. 配置主库

修改主库的 MySQL 配置文件（通常是 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）：

[mysqld]
server-id=1
log-bin=mysql-bin
bind-address = 0.0.0.0  # 允许远程连接

重启 MySQL 服务以应用更改：

sudo systemctl restart mysql

创建用于复制的用户并授予权限：

CREATE USER 'replica'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

获取主库的二进制日志位置：

SHOW MASTER STATUS;

记下 File 和 Position 的值，后续配置从库时会用到。

3. 配置从库

修改从库的 MySQL 配置文件（通常是 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）：

[mysqld]
server-id=2
read_only=1

重启 MySQL 服务以应用更改：

sudo systemctl restart mysql

配置从库连接主库：

CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='记录的File值',
MASTER_LOG_POS=记录的Position值;

启动从库的复制线程：

START SLAVE;

检查从库状态：

SHOW SLAVE STATUS\G

确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes。

4. 验证主从复制

在主库上执行一些操作：

USE test;
CREATE TABLE example (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO example VALUES (1, 'Test');

在从库上查询数据：

USE test;
SELECT * FROM example;

12. 主从复制的实现原理是什么

MySQL 主从复制是一种常见的数据库高可用和读写分离方案，其实现原理主要基于 二进制日志（Binary Log） 和 I/O 线程、SQL 线程 的协同工作。以下是主从复制的详细实现原理：

1. 主库的 Binary Log

• MySQL 主库将所有对数据库的更改操作（如 INSERT、UPDATE、DELETE）记录到 Binary Log 中。

• Binary Log 包含了事务的唯一标识（GTID 或 Position）、执行时间戳、操作类型以及具体的 SQL 语句或行数据变更。

Binary Log 的三种格式：

2. 从库的 I/O 线程与 SQL 线程

MySQL 从库通过两个线程来实现主从同步：

• I/O Thread（输入线程）：负责连接主库并拉取 Binary Log 内容，将其写入本地的 Relay Log（中继日志） 文件中。

• SQL Thread（SQL 线程）：读取 Relay Log 并重放其中的 SQL 语句，从而在从库上执行相同的数据变更操作。

主从复制流程如下：

1. 主库写入 Binary Log：

   • 所有写操作都会被记录到主库的 Binary Log 中。

2. 从库 I/O 线程连接主库：

   • 使用 CHANGE MASTER TO 配置的用户连接主库。

   • 请求从指定位置开始读取 Binary Log。

3. 主库推送 Binary Log 到从库：

   • 主库的 Dump 线程将 Binary Log 发送给从库的 I/O 线程。

4. 从库写入 Relay Log：

   • 从库的 I/O 线程将接收到的 Binary Log 写入本地的 Relay Log 文件。

5. 从库 SQL 线程重放 Relay Log：

   • SQL 线程读取 Relay Log，并按顺序执行其中的 SQL 语句，完成数据同步。

3. GTID（Global Transaction Identifier）机制

GTID 是一种全局事务标识符，用于更精确地追踪和管理主从复制中的事务一致性。

GTID 的格式：

GTID = source_id:transaction_id

• source_id：主库的唯一标识（通常是 server_uuid）

• transaction_id：事务的自增编号

GTID 的优势：

• 简化故障恢复：无需手动查找 Binlog 文件和位置。

• 支持自动跳过已执行的事务：避免重复执行。

• 提高一致性：保证主从之间事务的一致性。

4. 延迟从库（Delayed Slave）

延迟从库是指从库故意滞后于主库一段时间（例如 30 分钟）。它主要用于以下场景：

• 防止误操作导致的数据丢失

• 实现 Point-in-Time Recovery (PITR)

延迟从库配置示例：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replica',
  MASTER_PASSWORD='replica_password',
  MASTER_AUTO_POSITION=1,
  MASTER_DELAY=1800;  -- 延迟 30 分钟

13. 什么是数据库的读写分离，为什么要进行读写分离

一、什么是数据库的读写分离？

数据库读写分离（Read/Write Splitting） 是一种常见的数据库架构优化策略，其核心思想是将 数据库的读操作（SELECT） 和 写操作（INSERT、UPDATE、DELETE） 分发到不同的数据库实例上执行。

通常：

• 写操作 发送到 主库（Master）

• 读操作 被分发到一个或多个 从库（Slave）

这种架构基于 MySQL 的主从复制机制 实现，主库负责处理所有写请求并将数据变更同步到从库，从库则专注于处理读请求。

二、为什么要进行读写分离？（优势）

1. 提升系统性能与并发能力

• 写操作集中在主库，避免主库因混合读写压力过大而成为瓶颈。

• 读操作可以分布到多个从库，通过负载均衡实现高并发访问。

举例：电商大促期间，商品浏览量远大于下单量，读写分离可显著提升整体吞吐能力。

2. 提高系统可用性

• 即使某个从库出现故障，其他从库仍可继续提供读服务。

• 主库宕机时可通过 MHA 等工具自动切换，保障写服务不中断。

3. 数据安全性增强

• 从库作为主库的副本，可作为灾备节点使用。

• 可配置延迟从库（Delayed Slave），用于防止误删表或逻辑错误导致的数据丢失。

4. 支持灵活的业务场景

5. 支持横向扩展

• 可通过增加从库数量来应对不断增长的读请求。

• 结合中间件（如 ProxySQL、MaxScale）实现自动读写分离和负载均衡。

三、读写分离的常见实现方式

14. 除了主从复制外，还有哪些方式，各有什么优缺点

15. 什么是数据一致性，在关系型数据库中，数据一致性是否是最重要的

一、什么是数据一致性？

数据一致性（Data Consistency） 是指数据库中存储的数据在任何时刻都处于 逻辑上合理、无矛盾的状态。它是 ACID 特性中的 C（Consistency），确保事务执行前后，数据库从一个一致状态转变为另一个一致状态。

数据一致性的核心含义：

• 业务规则正确：数据满足预定义的约束、触发器、级联等机制。

• 事务完整性：事务开始和结束时，数据库状态应保持合法。

• 多节点一致性（在分布式系统中）：所有副本之间数据同步且相同时间点状态一致。

一般来说，数据一致性是最重要的，但在某些场景下，其他特性可能更重要。