mysql 基础复习-安装部署、增删改查 、视图、触发器、存储过程、索引、备份恢复迁移、分库分表

---

MySQL 安装部署

一、安装 MySQL

（一）使用包管理器安装

1. Ubuntu/Debian 系统

使用 apt 包管理器安装 MySQL。

# 更新包列表
sudo apt update# 安装 MySQL 服务器
sudo apt install mysql-server# 安装 MySQL 客户端（可选）
sudo apt install mysql-client

2. CentOS/RHEL 系统

使用 yum 包管理器安装 MySQL。

# 安装 MySQL 服务器
sudo yum install mysql-server# 启动 MySQL 服务
sudo systemctl start mysqld# 设置开机启动
sudo systemctl enable mysqld

（二）从源码编译安装（高级用户）

如果你需要特定版本的 MySQL 或需要自定义编译选项，可以从源码编译安装。

1. 下载源码

   wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.29.tar.gz
   

2. 解压源码

   tar -zxvf mysql-8.0.29.tar.gz
   cd mysql-8.0.29
   

3. 安装依赖

   sudo apt-get install build-essential checkinstall
   sudo apt-get install libncurses5-dev libncursesw5-dev
   

4. 编译安装

   cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_USER=mysql \-DDEFAULT_CHARSET=utf8mb4 \-DDEFAULT_COLLATION=utf8mb4_general_ci
   make
   sudo make install
   

5. 初始化 MySQL

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

6. 启动 MySQL

   sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &
   

---

二、配置 MySQL

（一）安全配置

安装完成后，运行 mysql_secure_installation 脚本，增强 MySQL 的安全性。

sudo mysql_secure_installation

该脚本会提示你设置 root 密码、移除匿名用户、禁止 root 用户远程登录等操作。

（二）配置文件

MySQL 的配置文件通常位于 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf。你可以根据需要修改配置文件。

1. 常用配置项

• bind-address：设置 MySQL 监听的 IP 地址，默认为 127.0.0.1，表示只允许本地访问。如果需要远程访问，可以修改为服务器的 IP 地址或 0.0.0.0。

  bind-address = 0.0.0.0
  

• port：设置 MySQL 的监听端口，默认为 3306。

  port = 3306
  

• max_connections：设置最大连接数，默认为 151。

  max_connections = 500
  

• character_set_server：设置默认字符集，默认为 latin1，推荐设置为 utf8mb4。

  character_set_server = utf8mb4
  

• collation_server：设置默认校对集，默认为 latin1_swedish_ci，推荐设置为 utf8mb4_general_ci。

  collation_server = utf8mb4_general_ci
  

（三）启动和停止 MySQL 服务

• Ubuntu/Debian 系统

  sudo systemctl start mysql
  sudo systemctl stop mysql
  sudo systemctl restart mysql
  

• CentOS/RHEL 系统

  sudo systemctl start mysqld
  sudo systemctl stop mysqld
  sudo systemctl restart mysqld
  

---

三、创建用户和数据库

（一）登录 MySQL

mysql -u root -p

（二）创建数据库

CREATE DATABASE mydb;

（三）创建用户

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

（四）授权用户访问数据库

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

---

四、备份与恢复

（一）备份数据库

使用 mysqldump 工具备份数据库。

mysqldump -u root -p mydb > mydb_backup.sql

（二）恢复数据库

将备份文件导入到目标数据库。

mysql -u root -p mydb < mydb_backup.sql

---

五、读写分离配置（可选）

（一）主数据库配置

编辑主数据库的配置文件 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，添加以下内容：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=MIXED

重启 MySQL 服务：

sudo systemctl restart mysql

（二）从数据库配置

编辑从数据库的配置文件 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，添加以下内容：

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

重启 MySQL 服务：

sudo systemctl restart mysql

（三）配置主从同步

在主数据库上，获取当前的二进制日志状态：

SHOW MASTER STATUS;

在从数据库上，配置主从同步：

CHANGE MASTER TO
MASTER_HOST='主数据库IP',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;

启动从数据库同步：

START SLAVE;

检查同步状态：

SHOW SLAVE STATUS\G

---

六、优化与监控

（一）性能优化

• 调整缓冲区大小：根据服务器的内存大小，调整 innodb_buffer_pool_size。

  innodb_buffer_pool_size = 1G
  

• 调整线程缓存：设置 thread_cache_size。

  thread_cache_size = 100
  

（二）监控工具

• htop 或 top：监控系统资源使用情况。

• mysqladmin：查看 MySQL 服务器状态。

  mysqladmin -u root -p extended-status
  

• Percona Toolkit：一套用于 MySQL 性能优化和监控的工具。

---

七、常见问题解决

（一）无法远程连接

确保 bind-address 设置为 0.0.0.0，并允许防火墙通过 MySQL 端口（默认为 3306）。

sudo ufw allow 3306/tcp

（二）忘记 root 密码

1. 停止 MySQL 服务：

   sudo systemctl stop mysql
   

2. 启动 MySQL 服务，跳过权限表：

   sudo mysqld_safe --skip-grant-tables &
   

3. 登录 MySQL：

   mysql -u root
   

4. 重置密码：

   FLUSH PRIVILEGES;
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
   

5. 重启 MySQL 服务：

   sudo systemctl restart mysql
   

---

MySQL 增删改查 、视图、触发器、存储过程、索引、备份恢复迁移、分库分表

一、增删改查（CRUD）

（一）增（INSERT）

1. 插入单行数据

• 语法：

  INSERT INTO 表名 (字段1, 字段2, ..., 字段n) VALUES (值1, 值2, ..., 值n);
  

• 示例：

  INSERT INTO students (name, age, gender) VALUES ('张三', 20, '男');
  

2. 插入多行数据

• 语法：

  INSERT INTO 表名 (字段1, 字段2, ..., 字段n) VALUES
  (值1a, 值2a, ..., 值na),
  (值1b, 值2b, ..., 值nb),
  ...;
  

• 示例：

  INSERT INTO students (name, age, gender) VALUES
  ('李四', 22, '男'),
  ('王五', 21, '女');
  

3. 插入查询结果

• 语法：

  INSERT INTO 表名 (字段1, 字段2, ..., 字段n) SELECT 字段1, 字段2, ..., 字段n FROM 另一个表 WHERE 条件;
  

• 示例：

  INSERT INTO students (name, age) SELECT name, age FROM temp_students WHERE gender = '男';
  

（二）删（DELETE）

1. 删除指定条件的记录

• 语法：

  DELETE FROM 表名 WHERE 条件;
  

• 示例：

  DELETE FROM students WHERE age > 25;
  

2. 删除表中所有记录

• 语法：

  DELETE FROM 表名;
  

• 或者使用：

  TRUNCATE TABLE 表名;
  

  注意：TRUNCATE 是直接清空表，不会记录删除的行数，且不能回滚，执行速度比 DELETE 快。

（三）改（UPDATE）

1. 更新指定条件的记录

• 语法：

  UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ..., 字段n = 值n WHERE 条件;
  

• 示例：

  UPDATE students SET age = 23 WHERE name = '张三';
  

2. 更新表中所有记录

• 语法：

  UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ..., 字段n = 值n;
  

• 示例：

  UPDATE students SET gender = '男';
  

（四）查（SELECT）

1. 基本查询

• 语法：

  SELECT 字段1, 字段2, ..., 字段n FROM 表名;
  

• 示例：

  SELECT name, age FROM students;
  

2. 条件查询

• 语法：

  SELECT 字段1, 字段2, ..., 字段n FROM 表名 WHERE 条件;
  

• 示例：

  SELECT * FROM students WHERE age > 20 AND gender = '男';
  

3. 排序查询

• 语法：

  SELECT 字段1, 字段2, ..., 字段n FROM 表名 ORDER BY 字段 [ASC|DESC];
  

• 示例：

  SELECT * FROM students ORDER BY age DESC;
  

4. 分组查询

• 语法：

  SELECT 字段1, COUNT(*), SUM(字段2), ... FROM 表名 GROUP BY 字段1;
  

• 示例：

  SELECT gender, COUNT(*) FROM students GROUP BY gender;
  

5. 分页查询

• 语法：

  SELECT 字段1, 字段2, ..., 字段n FROM 表名 LIMIT 起始行, 行数;
  

• 示例：

  SELECT * FROM students LIMIT 0, 10; -- 查询第一页，每页10条
  

二、视图（View）

（一）创建视图

• 语法：

  CREATE VIEW 视图名 AS SELECT 查询语句;
  

• 示例：

  CREATE VIEW male_students AS SELECT * FROM students WHERE gender = '男';
  

（二）查询视图

• 语法：

  SELECT * FROM 视图名;
  

• 示例：

  SELECT * FROM male_students;
  

（三）修改视图

• 语法：

  ALTER VIEW 视图名 AS SELECT 新的查询语句;
  

• 示例：

  ALTER VIEW male_students AS SELECT name, age FROM students WHERE gender = '男';
  

（四）删除视图

• 语法：

  DROP VIEW 视图名;
  

• 示例：

  DROP VIEW male_students;
  

（五）应用场景及好处

• 应用场景：

  • 简化复杂查询：将复杂的 SQL 查询封装成视图，简化查询操作。
  • 数据抽象：隐藏底层表的复杂性，提供更直观的数据视图。
  • 安全性：限制用户对底层表的直接访问，只允许通过视图访问数据。
  • 逻辑数据独立性：即使底层表结构发生变化，视图可以保持不变，减少对应用程序的影响。

• 好处：

  • 提高查询效率：减少重复编写复杂查询的开销。
  • 增强安全性：通过视图限制用户对敏感数据的访问。
  • 提高可维护性：减少应用程序对底层表结构的依赖，便于维护和升级。

三、存储过程（Stored Procedure）

（一）创建存储过程

• 语法：

  DELIMITER // -- 改变语句结束符
  CREATE PROCEDURE 存储过程名 (IN 参数1 类型, OUT 参数2 类型, ...)
  BEGIN-- SQL语句
  END //
  DELIMITER ; -- 恢复默认语句结束符
  

• 示例：

  DELIMITER //
  CREATE PROCEDURE add_student(IN name VARCHAR(20), IN age INT, OUT id INT)
  BEGININSERT INTO students (name, age) VALUES (name, age);SET id = LAST_INSERT_ID();
  END //
  DELIMITER ;
  

（二）调用存储过程

• 语法：

  CALL 存储过程名(参数1, 参数2, ...);
  

• 示例：

  CALL add_student('赵六', 24, @id);
  SELECT @id;
  

（三）删除存储过程

• 语法：

  DROP PROCEDURE 存储过程名;
  

• 示例：

  DROP PROCEDURE add_student;
  

（四）应用场景及好处

• 应用场景：

  • 批量操作：批量插入、更新或删除数据。
  • 复杂查询：封装复杂的 SQL 逻辑，减少客户端和服务器之间的交互。
  • 数据封装：隐藏数据操作的细节，提供统一的接口。

• 好处：

  • 性能优化：减少网络传输，提高查询效率。
  • 代码复用：封装复杂逻辑，便于复用。
  • 安全性：通过权限控制，限制对底层数据的直接访问。

四、触发器（Trigger）

（一）创建触发器

• 语法：

  CREATE TRIGGER 触发器名 触发时机 触发事件 ON 表名 FOR EACH ROW
  BEGIN-- SQL语句
  END;
  

• 示例：

  CREATE TRIGGER before_insert_student
  BEFORE INSERT ON students FOR EACH ROW
  BEGINSET NEW.age = NEW.age + 1; -- 插入时年龄加1
  END;
  

（二）删除触发器

• 语法：

  DROP TRIGGER 触发器名;
  

• 示例：

  DROP TRIGGER before_insert_student;
  

（三）应用场景及好处

• 应用场景：

  • 自动更新时间戳：在记录更新时自动更新 updated_at 字段。
  • 日志记录：在删除或更新记录时，将操作记录到日志表中。
  • 数据校验：在插入或更新数据时，校验数据的有效性，防止非法数据进入数据库。

• 好处：

  • 自动化：减少手动操作，降低错误率。
  • 数据一致性：确保数据的完整性和一致性。
  • 安全性：防止非法数据进入数据库。

五、索引（Index）

（一）创建索引

• 语法：

  CREATE INDEX 索引名 ON 表名 (字段1, 字段2, ...);
  

• 示例：

  CREATE INDEX idx_age ON students (age);
  CREATE INDEX idx_name_age ON students (name, age);
  

（二）查看索引

• 语法：

  SHOW INDEX FROM 表名;
  

• 示例：

  SHOW INDEX FROM students;
  

（三）删除索引

• 语法：

  DROP INDEX 索引名 ON 表名;
  

• 示例：

  DROP INDEX idx_age ON students;
  

（四）应用场景及好处

• 应用场景：

  • 快速查询：在查询条件中频繁使用的列上创建索引，提高查询效率。
  • 排序和分组：在 ORDER BY 和 GROUP BY 操作中使用的列上创建索引，优化排序和分组操作。
  • 唯一性约束：通过唯一索引确保列中的数据值不重复。

• 好处：

  • 提高查询效率：快速定位数据，减少扫描的数据量。
  • 优化排序和分组：减少排序和分组操作的开销。
  • 数据完整性：通过唯一索引确保数据的唯一性。

六、备份与恢复

（一）备份

• 使用 mysqldump 命令备份

  • 语法：

    mysqldump -u 用户名 -p 数据库名 > 备份文件.sql
    

  • 示例：

    mysqldump -u root -p mydb > mydb_backup.sql
    

• 备份单个表

  • 语法：

    mysqldump -u 用户名 -p 数据库名 表名 > 备份文件.sql
    

  • 示例：

    mysqldump -u root -p mydb students > students_backup.sql
    

（二）恢复

• 使用 source 命令恢复

  • 语法：

    source 备份文件.sql;
    

  • 示例：

    source mydb_backup.sql;
    

• 使用命令行恢复

  • 语法：

    mysql -u 用户名 -p 数据库名 < 备份文件.sql
    

  • 示例：

    mysql -u root -p mydb < mydb_backup.sql
    

（三）应用场景及好处

• 应用场景：

  • 数据安全：定期备份数据，防止数据丢失。
  • 灾难恢复：在硬件故障、人为错误或自然灾害等情况下，快速恢复数据。

• 好处：

  • 数据安全：确保数据的完整性和可用性。
  • 快速恢复：减少停机时间，提高系统的可用性。

七、分库分表

（一）分库

• 按业务划分

  • 将不同业务模块的数据存储在不同的数据库中，例如：
    • 用户信息数据库：user_db
    • 订单信息数据库：order_db

• 按读写分离

  • 将数据库分为读库和写库，写库负责数据的插入、更新、删除操作，读库负责数据的查询操作。

（二）分表

• 水平分表

  • 按数据行进行分表，例如：
    • 将用户表按用户 ID 的范围分为多个表：
      • users_0_1000：存储用户 ID 在 0 到 1000 之间的用户数据
      • users_1001_2000：存储用户 ID 在 1001 到 2000 之间的用户数据

• 垂直分表

  • 按数据列进行分表，将表中字段较多的表拆分为多个表，例如：
    • 将用户表拆分为基本信息表和扩展信息表：
      • user_basic：存储用户的基本信息，如用户 ID、用户名、密码等
      • user_extend：存储用户的扩展信息，如用户头像、用户简介等

（三）应用场景及好处

• 应用场景：

  • 大数据量：当单表数据量过大时，通过分库分表提高查询效率。
  • 业务隔离：将不同业务模块的数据存储在不同的数据库或表中，提高系统的可维护性。

• 好处：

  • 性能优化：减少单表数据量，提高查询和更新效率。
  • 可维护性：业务模块分离，便于管理和维护。

八、数据迁移

（一）使用 mysqldump 和 mysql 命令迁移

• 导出数据

  • 语法：

    mysqldump -u 用户名 -p 数据库名 > 导出文件.sql
    

  • 示例：

    mysqldump -u root -p mydb > mydb_dump.sql
    

• 导入数据

  • 语法：

    mysql -u 用户名 -p 新数据库名 < 导出文件.sql
    

  • 示例：

    mysql -u root -p new_mydb < mydb_dump.sql
    

（二）使用 phpMyAdmin 迁移

• 在 phpMyAdmin 中，选择要迁移的数据库或表，点击“导出”按钮，选择导出格式（如 SQL），然后将导出的文件导入到目标数据库中。

（三）使用数据迁移工具

• 例如：MySQL Workbench、Navicat 等，这些工具提供了图形化界面，方便进行数据迁移操作。

（四）应用场景及好处

• 应用场景：

  • 系统升级：从旧系统迁移到新系统。
  • 数据整合：将分散的数据整合到一个统一的数据库中。
  • 环境迁移：从开发环境迁移到生产环境。

• 好处：

  • 数据一致性：确保数据在迁移过程中保持一致。
  • 减少停机时间：通过高效的迁移工具和策略，减少系统停机时间。

---

补充：读写分离

一、读写分离的原理

（一）基本概念

• 读操作（SELECT）：查询数据，不修改数据。
• 写操作（INSERT、UPDATE、DELETE）：修改数据，包括插入、更新和删除操作。

（二）读写分离的架构

• 主数据库（Master）：负责处理写操作（INSERT、UPDATE、DELETE）。
• 从数据库（Slave）：负责处理读操作（SELECT）。
• 数据同步机制：主数据库将数据变更同步到从数据库，确保从数据库的数据与主数据库保持一致。

（三）工作流程

1. 写操作：

   • 客户端将写操作（INSERT、UPDATE、DELETE）发送到主数据库。
   • 主数据库处理写操作，并将变更记录到日志（如 MySQL 的二进制日志，binlog）。
   • 主数据库将变更日志推送给从数据库。

2. 读操作：

   • 客户端将读操作（SELECT）发送到从数据库。
   • 从数据库处理读操作，返回查询结果。

3. 数据同步：

   • 从数据库通过读取主数据库的变更日志（binlog），应用这些变更，保持数据一致性。

---

二、实现方式

（一）基于 MySQL 的主从复制

MySQL 提供了内置的主从复制功能，可以实现读写分离。

1. 配置主数据库（Master）

• 配置文件（my.cnf 或 my.ini）：

  [mysqld]
  server-id=1
  log-bin=mysql-bin
  binlog-format=MIXED
  

• 创建复制用户：

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

• 获取主数据库状态：

  SHOW MASTER STATUS;
  

2. 配置从数据库（Slave）

• 配置文件（my.cnf 或 my.ini）：

  [mysqld]
  server-id=2
  relay-log=mysql-relay-bin
  log-slave-updates=1
  read-only=1
  

• 配置从数据库同步：

  CHANGE MASTER TO
  MASTER_HOST='主数据库IP',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;
  

• 启动从数据库同步：

  START SLAVE;
  

• 检查同步状态：

  SHOW SLAVE STATUS\G
  

• 主数据库配置：

  • server-id=1：设置主数据库的唯一标识符。
  • log-bin=mysql-bin：启用二进制日志，记录数据变更。
  • binlog-format=MIXED：设置二进制日志格式为混合模式。

• 从数据库配置：

  • server-id=2：设置从数据库的唯一标识符。
  • relay-log=mysql-relay-bin：指定中继日志文件前缀。
  • log-slave-updates=1：启用从数据库记录二进制日志（可选）。
  • read-only=1：将从数据库设置为只读模式。

（二）基于中间件的读写分离

中间件可以自动将读写请求分发到不同的数据库实例。

1. 常见中间件

• Cassandra：支持分布式存储，内置读写分离。
• ShardingSphere：支持分片和读写分离，提供灵活的配置。
• Cassandra：支持分布式存储，内置读写分离。

2. 配置示例（以 ShardingSphere 为例）

• 配置文件：

  dataSources:ds_0:url: jdbc:mysql://master:3306/dbusername: rootpassword: passwordds_1:url: jdbc:mysql://slave1:3306/dbusername: rootpassword: passwordds_2:url: jdbc:mysql://slave2:3306/dbusername: rootpassword: passwordrules:- !READWRITE_SPLITTINGdataSources:ds:writeDataSourceName: ds_0readDataSourceNames:- ds_1- ds_2
  

---

三、应用场景

（一）高并发读操作

• 场景：在高并发的读操作场景中，如电商网站、新闻网站等，读操作远多于写操作。
• 解决方案：通过读写分离，将读操作分发到多个从数据库，分散读压力，提高系统性能。

（二）数据备份与容错

• 场景：主数据库发生故障时，需要快速切换到从数据库。
• 解决方案：从数据库可以作为备份，确保数据的可用性和一致性。

（三）读写分离与分片结合

• 场景：大规模数据存储，需要同时解决读写分离和数据分片问题。
• 解决方案：结合中间件（如 ShardingSphere），实现读写分离和数据分片，提高系统的可扩展性。

---

四、好处

（一）提高性能

• 分散读压力：将读操作分发到多个从数据库，减少主数据库的负载。
• 优化写性能：主数据库专注于写操作，减少读操作的干扰，提高写性能。

（二）增强可用性

• 故障切换：主数据库故障时，从数据库可以快速接管，确保服务的可用性。
• 数据备份：从数据库可以作为数据备份，提高数据的安全性。

（三）提高可扩展性

• 水平扩展：通过增加从数据库实例，可以灵活扩展系统的读能力。
• 灵活配置：结合中间件，可以实现更复杂的读写分离和分片策略。

---

五、注意事项

（一）延迟问题

• 问题：从数据库可能存在延迟，导致读操作读取到旧数据。
• 解决方案：
  • 增加从数据库实例：减少每个从数据库的负载，降低延迟。
  • 调整同步策略：优化主从同步机制，减少延迟。

（二）一致性问题

• 问题：主从同步可能存在延迟，导致数据不一致。
• 解决方案：
  • 强一致性读：在需要强一致性的场景中，直接读取主数据库。
  • 最终一致性：在允许延迟的场景中，使用从数据库，提高性能。

（三）复杂查询优化

• 问题：复杂的查询可能需要在主从数据库之间切换。
• 解决方案：
  • 查询优化：优化查询语句，减少复杂查询的开销。
  • 缓存机制：使用缓存（如 Redis）减少对数据库的直接访问。

---