第六周作业11-22题

11. 基于apache, php, mysql搭建wordpress站点。

yum install mysql-community-server

yum install httpd php php-mysqlnd php-json

cat /var/log/mysqld.log | grep password

mysql -uroot -p

Alteruser root@'localhost'identified by 'Dbadmin@123';

mysql> create database wordpress;

Query OK, 1 row affected (0.01 sec)

mysql> create user wordpress@'localhost'identified by 'Dbadmin@123';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all on wordpress.* to wordpress@'localhost';

Query OK, 0 rows affected (0.00 sec)

12. 总结mysql架构原理.

MySQL 是一个典型的客户端/服务端模式的数据库管理系统，它的架构主要分为以下三个部分：

连接层（Connection Layer）：处理客户端连接请求，并进行身份验证、权限验证等操作。一旦连接建立成功，它将负责管理客户端和服务端之间的通信，并通过缓存、预读等技术提高数据查询的效率。

服务层（Server Layer）：该层是 MySQL 的核心组成部分，由多个线程池、缓存和存储引擎组成。所有的 SQL 查询请求都发送到该层处理，其中相关的 SQL 解析和优化工作由 Query Cache 和 Optimizer 处理，然后将查询请求发送到相应的存储引擎中执行。

存储引擎层（Storage Engine Layer）：MySQL 支持多种存储引擎，例如 InnoDB、MyISAM 等。每个存储引擎都可以独立处理数据的读写，因此在存储引擎层中，数据会被不同的存储引擎以不同的方式进行存储和管理。

其中，还有以下几个重要的组件：

连接器（Connector）：负责连接客户端和服务端、进行身份认证和权限验证。

查询缓存（Query Cache）：缓存查询结果，提高重复查询的效率。

SQL 解析器和优化器（SQL Parser & Optimizer）：将 SQL 语句转换为执行计划，进行查询优化。

存储引擎（Storage Engine）：负责数据的存储和管理，提供不同的数据访问方式、锁机制以及事务支持等功能。

总体来说，MySQL 的架构设计非常清晰，各个组件之间分工明确、相互独立，使得 MySQL 具备高效、灵活和可扩展的特性。同时，MySQL 还提供了多种配置参数以及插件接口，能够满足各种不同的需求和场景。

13. 总结myisam和Innodb存储引擎的区别。

Myisam引擎特点：

不支持事务

表级锁定

读写互相阻塞，写入时不能读，读时不能写入。

只缓存索引

不支持外键约束

不支持聚簇索引

读取数据较快，占用资源少。

不支持MVCC(多版本并发控制机制)高并发。

崩溃恢复性较差

MySQL5.5之前默认的数据库引擎。

InnoDB引擎

行级锁

支持事务，适合处理大量短期事务

读写阻塞与事务隔离级别相关

可缓存数据与索引

支持聚簇索引

崩溃恢复性更好

支持MVCC高并发

从mysql5.5后支持全文索引

从mysql5.5开始为默认的数据库引擎

14. 总结mysql索引作用，同时总结哪些查询不会使用到索引。

MySQL索引是一种重要的数据库技术，主要用于提高数据检索效率。索引可以加速查询操作并降低数据库系统的I/O代价，从而提高系统的性能。

具体来说，索引的作用主要有以下几个方面：

1.快速定位：索引可以帮助MySQL快速定位到需要查询的数据，避免了全表扫描，从而加快查询速度。

2.降低I/O代价：索引可以减少磁盘I/O次数，节约系统资源；当MySQL需要读取数据时，通过索引可以直接读取数据所在的磁盘块，减少了磁盘寻址时间，从而提高了查询效率。

3.改善排序性能：如果查询语句中包含了对某个列进行排序，且该列上存在索引，则MySQL可以使用索引加速排序操作，从而提高查询效率。

索引的作用在于提高MySQL的查询效率和优化数据库性能，但是过多或不必要的索引也会影响数据库性能，因此需要根据具体情况进行维护和优化。

15. 总结事务ACID事务特性

ACID是数据库管理中重要的概念，是指数据库事务应该具备的四个特性，分别为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）

1.原子性（Atomicity）：指事务是一个不可分割的操作单元，要么全部执行成功，要么全部执行失败。如果事务执行失败，在撤销所有操作时必须将数据库恢复到事务开始前的状态，以保证数据的正确性。

2.一致性（Consistency）：指在事务执行结束后，数据库的状态应该满足预定义的约束条件，如外键约束、非空约束等，确保数据的正确性。

3.隔离性（Isolation）：指多个事务之间相互隔离，彼此不会产生影响，每个事务应该感觉不到其他事务的存在。这样可以避免由于并发访问导致的数据异常或不一致。

4.持久性（Durability）：指事务完成后对数据的修改是永久性的，即使出现系统故障，也可以通过数据恢复技术将数据恢复到上次提交事务的状态。

所以ACID特性确保了数据库事务的可靠性和数据一致性，是数据库管理的核心理念，也是设计高度可用、高性能数据库系统必须要考虑的因素。

16. 总结事务日志工作原理。

事务日志（Transaction Log）是数据库管理系统中的一种重要机制，用于记录每个事务所做出的修改操作。其工作原理可以简单概括如下：

1.事务启动：当用户发起一个事务时，数据库开始记录该事务的相关信息，并在事务日志中创建一个新的日志文件，以记录此次事务的所有操作。

2.日志记录：在事务执行过程中，数据库系统将所有对数据进行的修改操作，都以日志的形式记录下来，包括对数据的插入、更新、删除等操作。每个日志条目都包含了该操作的详细信息，如何修改数据，修改之前和之后的值等。

3.日志缓存：由于频繁写磁盘会影响性能，为了提高性能，数据库通常会使用缓存技术，将日志信息先暂存在内存中，等到缓存满或到达一定时间后再一次性写入硬盘。

将事务提交：当事务执行完成后，如果用户提交了该事务，那么就会触发事务的提交操作，同时也会将该事务所产生的所有日志记录写入磁盘上的日志文件中。

4.恢复操作：当数据库系统重新启动时，会检查每个已提交的事务是否正确执行，并根据事务日志对未提交的事务进行恢复。如果有未完成的事务，则进行回滚操作，将数据库恢复到事务开始前的状态。

所以事务日志是确保数据库数据正确性和可靠性的重要手段，在数据库管理中扮演着至关重要角色。

17.总结mysql日志类型，并说明如何启动日志

MySQL的日志类型可以分为以下几类：

1.错误日志（Error Log）: 记录了MySQL运行过程中出现的所有错误信息。

2.慢查询日志（Slow Query Log）: 记录了执行时间超过指定阈值的SQL语句，方便性能优化。

3.查询日志（General Query Log）: 记录了MySQL服务器收到的所有 SQL 查询语句，包括查询、更新、删除等操作。

4.二进制日志（Binary Log）：记录了对数据库进行任何更改操作的详细情况，以实现基于日志的数据库备份和灾难恢复。

5.事务日志（Transaction Log）：也称为重做日志（Redo Log），记录了已提交的事务对数据库所作的修改操作，以确保数据能够在系统故障后恢复到已提交的状态。

18.总结二进制日志的不同格式的使用场景

MySQL的二进制日志（Binary Log）可以分为两种不同格式：语句格式（statement-based）和行格式（row-based）。

1.语句格式记录的是事务中执行的SQL语句，可以用于复制、恢复、回滚等操作。但是也存在一些缺点，如对于某些复杂的语句或函数可能无法正确记录，因此可能导致数据不一致的问题。

2.行格式记录的是每行数据的具体修改操作，更加精确地记录了事务执行过程中的数据变化。由于记录的信息比较详细，因此行格式在进行数据复制和故障恢复时更加可靠，但是也会给硬盘写入带来更大的压力，并且日志文件会相对比较大。

使用场景：

语句格式适合轻量级的系统，查询和写入都不多，而且性能要求没有那么高的场景。

对于高性能和高可靠性要求的场景，推荐使用行格式。比如需要进行主从复制、故障恢复、数据同步等操作时，行格式可以更好地保证数据一致性，减少故障发生的概率。当然，如果存储空间受限，或者IO写入压力过大，可以选择关闭二进制日志或者调整成语句格式进行记录。

19.总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证

MySQL备份类型可以分为以下几种：

1.物理备份：物理备份是指备份数据库的二进制文件。这包括了数据库的数据文件、日志文件等。物理备份可以快速地恢复整个数据库，但是备份文件很大，不适合用于部分恢复。

2.逻辑备份：逻辑备份是指备份数据库中的数据和表结构等逻辑信息。这种备份方式通常以SQL语句的形式存储备份数据。这种方式备份文件较小，但是还原时需要先创建表结构，再导入数据，时间和空间成本较高。

3.增量备份：增量备份是在全量备份的基础上，只备份数据库的增量部分。当数据变化不大时，使用增量备份可以减少备份文件的大小和备份的时间。

4.差异备份：差异备份是在全量备份的基础上，只备份两次备份之间的差异部分。与增量备份相比，差异备份虽然备份的文件较大，但只需要还原最近一次备份和差异备份即可，节省了还原数据的时间和空间成本。

备份类型主要分为物理备份和逻辑备份，而在备份策略方面，我们可以选择全量备份、增量备份和差异备份等方式。不同的备份类型和策略可以根据实际需求进行选择和配置

备份数据库：

使用mysqldump命令连接到需要备份的数据库服务器并指定用户名和密码，例如：mysqldump -u username -p password database_name > backup_name.sql

mysqldump命令会将整个数据库备份成一个.sql文件保存在本地，备份文件名称为backup_name.sql。

恢复数据库：

使用mysql命令连接到需要恢复的数据库服务器并指定用户名和密码，例如：mysql -u username -p password

在mysql命令行下执行“source”命令，将备份数据导入到新的数据库中进行恢复，例如：source backup_name.sql

验证数据库恢复：

使用SELECT命令查询新建的数据库中数据是否正确，例如：SELECT * FROM table_name;

检查查询结果是否与备份文件中的数据一致，如果一致，则说明数据库备份和恢复验证工作完成。

总之，使用mysqldump可以方便的备份MySQL数据库，而通过将备份数据导入到新的数据库进行恢复验证，可以确保备份的完整性和正确性，从而确保了数据的安全性和有效性。

XtraBackup是一个免费的开源物理备份恢复工具，它可以备份InnoDB和XtraDB存储引擎的数据。使用XtraBackup可以完成对MySQL数据库的全量备份、增量备份以及数据库恢复验证等任务。

全量备份

使用XtraBackup命令进行全量备份，备份文件会被存储在指定的目录中。

# 使用XtraBackup进行全量备份

xtrabackup --backup --user=username --password=password --target-dir=/path/to/backup/dir

增量备份

使用XtraBackup命令进行增量备份，备份文件会被存储在指定的目录中。

# 使用XtraBackup进行增量备份

xtrabackup --backup --user=username --password=password --target-dir=/path/to/incremental/backup/dir --incremental-basedir=/path/to/full/backup/dir

数据库恢复

恢复全量备份

使用XtraBackup进行全量备份恢复。

# 使用XtraBackup恢复全量备份

xtrabackup --prepare --target-dir=/path/to/backup/dir

xtrabackup --copy-back --target-dir=/path/to/backup/dir

恢复增量备份

使用XtraBackup进行增量备份恢复，需要先使用全量备份进行基础恢复。

# 使用增量备份进行恢复

xtrabackup --prepare --apply-log-only --target-dir=/path/to/full/backup/dir

xtrabackup --prepare --apply-log-only --target-dir=/path/to/full/backup/dir --incremental-dir=/path/to/incremental/backup/dir

# 最后一次增量包不用加 --apply-log-only

xtrabackup --prepare --target-dir=/path/to/full/backup/dir --incremental-dir=/path/to/incremental/backup/dir

# 复制回数据库目录，注意数据库目录必须为空,MySQL不能启动

xtrabackup --copy-back --target-dir=/path/to/full/backup/dir

chown -R mysql:mysql /var/lib/mysql

systemctl start mysqld

20.编写crontab，每天按表备份所有mysql数据，将备份数据放在以天为时间的目录下

0 2 * * * mkdir -p /backup/mysql/$(date +\%Y-\%m-\%d)/ && mysqldump -u root -pPASSWORD --all-databases | gzip > /backup/mysql/$(date +\%Y-\%m-\%d)/all_databases_$(date +\%Y-\%m-\%d).sql.gz

该命令会在每天凌晨2点执行备份操作，首先创建一个以当天日期命名的目录（如 /backup/mysql/2023-06-10/），然后使用 mysqldump 命令备份 MySQL 的所有数据库并使用 gzip 进行压缩，最后将备份文件以当前日期命名并保存在创建的目录下。

需要注意的是：

该命令中需要将 PASSWORD 替换为 MySQL 的密码。

如果 MySQL 安装在非默认路径下，则需要修改 mysqldump 命令的路径。

21.编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

0 2 * * 1,5 xtrabackup --user=root --password=PASSWORD /backup/full_backup_$(date +\%Y-\%m-\%d)

0 2 * * 2-4 xtrabackup --user=root --password=PASSWORD --incremental /backup/inc_backup_$(date +\%Y-\%m-\%d) --incremental-basedir=/backup/full_backup_$(date -d 'last Monday' +\%Y-\%m-\%d)

该命令会在每周的周一和周五凌晨2点执行完全备份操作，备份数据存储在以日期命名的目录 /backup/full_backup_YYYY-MM-DD 中；在周二到周四凌晨2点执行增量备份操作，备份数据存储在以日期命名的目录 /backup/inc_backup_YYYY-MM-DD 中。其中 PASSWORD 需要替换为 MySQL 的密码。

这里使用了 --incremental 参数来指定增量备份模式，并通过 --incremental-baseidr 参数指定增量备份所基于的全量备份目录。每次增量备份都需要指定前一次的全量备份目录，这里使用 date -d 'last Monday' +\%Y-\%m-\%d 命令获取上一次周一的日期，以取得前一个完全备份的目录。

22.总结mysql主从复制原理

MySQL 主从复制是一种高可用、数据分发和负载均衡的解决方案。其基本原理是将主数据库的变更记录以二进制日志的形式写入到主数据库上，然后通过网络传输将这些变更记录同步到一个或多个从数据库上，从数据库重放这些日志来实现与主数据库一致的复制数据。

主从复制的流程如下：

主数据库将修改操作记录在二进制日志（Binary Log）中；

从数据库连接到主数据库并请求复制数据，主数据库将二进制日志中的内容发送给从数据库；

从数据库将接收到的日志存储在中继日志（Relay Log）中，并重放这些日志，将数据修改操作在从数据库上执行一遍，从而使从数据库与主数据库数据保持一致；

当主数据库发生故障无法提供服务时，可以将从数据库切换为新的主数据库，从而实现高可用。

在 MySQL 主从复制中，需要考虑以下要点：

主数据库通过二进制日志将变更记录传送给从数据库，并确保每个操作被记录只有一次；

从数据库通过中继日志接收和保存主数据库发来的二进制日志，并在重放这些日志时保证主从数据一致性；

主数据库和从数据库之间通过网络通信进行数据同步，因此需要保证网络稳定和速度足够快；

主从架构中的主数据库故障处理和从库切换需要进行合理的规划和配置，建立健壮的高可用解决方案，以确保系统的持续稳定运行。

总之，MySQL 主从复制是一种非常重要的架构模式，可以实现高可用、负载均衡和数据分发等目标，对于大型应用程序和高可用性场景下的企业应用系统来说都是非常必要的。