运维岗需要掌握的数据库基础和调优技巧

---

数据库运维笔记

目录

1. 数据库基本概念
2. MySQL 基本操作
3. 高可用解决方案
4. 常见 SQL 语句
5. 数据库备份与恢复
6. 数据库性能优化

---

1. 数据库基本概念

ACID 特性

• Atomicity（原子性）: 事务的所有操作要么全部完成，要么全部不完成。
• Consistency（一致性）: 事务执行前后，数据库处于一致状态。
• Isolation（隔离性）: 并发事务互不干扰。
• Durability（持久性）: 事务一旦提交，修改将永久保存。

数据库引擎

• InnoDB: 支持事务，支持外键，提供崩溃恢复功能。
• MyISAM: 不支持事务，速度快，适用于读多写少的应用。

---

2. MySQL 基本操作

启动与停止 MySQL 服务

• 启动服务

  systemctl start mysqld

• 停止服务

  systemctl stop mysqld

登录 MySQL

mysql -u root -p

数据库管理

• 创建数据库

  CREATE DATABASE db_name;

• 删除数据库

  DROP DATABASE db_name;

用户管理

• 创建用户

  CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 删除用户

  DROP USER 'username'@'host';

---

3. 高可用解决方案

主从复制 (Master-Slave Replication)

• 主服务器 (Master): 负责处理写操作，数据变化会传递给从服务器。
• 从服务器 (Slave): 负责处理读操作，并同步主服务器的数据。

双主复制 (Master-Master Replication)

• 两个服务器互为主服务器，支持双向数据同步。

PXC (Percona XtraDB Cluster) 或 Galera Cluster

• 提供多主复制，所有节点都能读写，实现真正的高可用。

Keepalived + MySQL: 通过虚拟 IP 实现 MySQL 的高可用性，保证数据库服务不间断。

---

4. 常见 SQL 语句

数据查询

• 查询所有数据

  SELECT * FROM table_name;

• 查询指定条件的数据

  SELECT * FROM table_name WHERE condition;

数据插入

• 插入数据到表中

  INSERT INTO table_name (column1, column2) VALUES (value1, value2);

数据更新

• 更新表中的数据

  UPDATE table_name SET column1 = value1 WHERE condition;

数据删除

• 删除表中的数据

  DELETE FROM table_name WHERE condition;

表结构管理

• 创建表

  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
  );

• 删除表

  DROP TABLE table_name;

• 修改表结构

  ALTER TABLE table_name ADD column_name datatype;

---

5. 数据库备份与恢复

备份

• 使用 mysqldump 进行数据库备份

  mysqldump -u root -p database_name > backup.sql

恢复

• 从备份文件恢复数据库

  mysql -u root -p database_name < backup.sql

• 恢复所有数据库

  mysql -u root -p < alldatabases_backup.sql

增量备份

• 使用 binlog 进行增量备份

  mysqlbinlog binlog.000001 > incremental_backup.sql

---

6. 数据库性能优化

索引优化

• 使用索引加速查询

  CREATE INDEX idx_name ON table_name (column_name);

• 查看表的索引

  SHOW INDEX FROM table_name;

• 删除索引

  DROP INDEX idx_name ON table_name;

查询优化

• 避免使用 SELECT *，只查询需要的字段。
• 使用 EXPLAIN 语句分析查询语句的执行计划。

  EXPLAIN SELECT * FROM table_name WHERE condition;

表优化

• 对表进行碎片整理，提升性能

  OPTIMIZE TABLE table_name;

缓存优化

• 使用查询缓存减少重复查询，提升性能。
  • 启用查询缓存：
    在 MySQL 配置文件 my.cnf 中添加

    query_cache_type = 1
    query_cache_size = 64M