MySQL 高性能数据库中间件服务

• Author: liuchao

• email: mirschao@gmail.com

• github: https://github.com/mirschao

• gitee: https://gitee.com/mirschao

从研发工程师的角度:

作为研发工程师, MySQL是我们常用的关系型数据库管理系统之一。它具有以下特点和功能.

• 可靠性.MySQL以其稳定性和可靠性而闻名。它经过广泛的测试和使用, 在各种环境和规模的应用中被广泛验证

• 扩展性.MySQL支持高度可扩展的架构。通过主从复制、分片、集群等技术, 可以实现水平和垂直扩展, 满足不同规模和负载的需求

• 强大的查询语言.MySQL支持标准的SQL查询语言, 提供丰富的查询和数据操作功能, 包括聚合函数、子查询、连接操作等。它还支持存储过程、触发器和自定义函数等高级特性, 使得开发人员能够灵活地处理复杂的数据操作和业务逻辑

• 性能优化.MySQL提供了多种性能优化手段。通过索引优化、查询优化器、查询缓存、分区表等技术, 可以提升数据库的查询性能和响应速度。同时, MySQL还提供了性能监控和调优工具, 帮助开发人员定位和解决性能瓶颈问题

从运维工程师的角度:

作为运维工程师, MySQL是我们日常工作中必须管理和维护的数据库系统。以下是MySQL的一些关键方面.

1. 安装和配置.运维工程师需要熟悉MySQL的安装和配置过程, 包括选择合适的版本、设置数据库参数、配置存储引擎等。合理的配置可以提供更好的性能和可靠性。

2. 数据备份和恢复.运维工程师负责制定和执行数据备份和恢复策略, 确保数据库的数据可靠性和持久性。这包括选择合适的备份方法、定期备份数据、恢复测试等。

3. 高可用性和故障恢复.运维工程师需要确保MySQL数据库的高可用性。通过设置主从复制、故障切换、数据库集群等技术, 实现故障自动切换和容灾能力, 减少业务中断时间。

4. 性能监控和调优.运维工程师需要监控数据库的性能指标, 如CPU、内存、磁盘和网络等资源的利用率, 以及查询响应时间、连接数等。他们需要使用性能监控工具和日志分析技术, 识别并解决性能瓶颈和故障问题。

总而言之, 作为研发工程师和运维工程师, 我们对MySQL有广泛的应用和深入的理解。我们可以利用MySQL的强大功能和灵活性, 开发和维护高性能、可靠、安全的数据库系统, 以满足业务需求和用户期望。

⚠️WARNNING: MySQL只是用于将数据 合理且有逻辑 (关系型) 的将数据存储到磁盘上的工具(中间件)

快速跳转：

第一章 MySQL服务部署及配置

MySQL 5.7和MySQL 8.0是MySQL数据库的两个重要版本，在功能、性能和安全性等方面存在区别的。以下是它们之间的一些主要区别：

1. JSON支持：

   • MySQL 5.7引入了对JSON数据类型的支持，允许存储和查询JSON格式的数据，并提供了相应的JSON函数和操作符。

   • MySQL 8.0在JSON支持方面进一步增强，包括新增的JSON函数、操作符和JSON路径表达式，以及更高效的JSON存储格式。

2. Common Table Expressions（公共表达式）：

   • MySQL 8.0支持公共表达式，允许在查询中创建临时的命名子查询，简化了复杂查询的编写和理解。MySQL 5.7不支持此功能。

3. 更强的密码策略：

   • MySQL 8.0改进了密码策略，引入了更强的密码验证插件和默认密码策略。这有助于提高账户的安全性。

4. InnoDB引擎改进：

   • MySQL 8.0对InnoDB存储引擎进行了一系列的改进，包括更好的并发控制、表空间管理和数据压缩等方面的优化。这些改进有助于提升性能和存储效率。

5. Group Replication（组复制）：

   • MySQL 5.7引入了Group Replication，这是一种基于原生MySQL复制的多主复制解决方案，提供了高可用性和故障切换的能力。

   • MySQL 8.0对Group Replication进行了改进，增加了更多功能和灵活性，如自动成员加入、自动扩展、多主模式等。

6. 安全性改进：

   • MySQL 8.0引入了一些新的安全功能，如密码策略的改进、角色管理、数据加密、基于角色的访问控制（RBAC）等。这有助于提高数据库的安全性。

这些是MySQL 5.7和MySQL 8.0之间的一些主要区别。根据你的具体需求和项目情况，选择适合的版本是很重要的。对于新的项目或需要较新功能的项目，通常推荐选择MySQL 8.0版本。对于现有项目，需要评估升级的风险和成本，并确保应用程序的兼容性和稳定性。

⚠️WARNNING: 选择哪个版本不是运维说了算的, 而是研发说了算的

1.1 Yum方式安装mysql服务

yum方式安装mysql数据库仅仅适用于测试环境或者快速验证时才会采用, 生产环境中yum方式安装的mysql数据库是不具备源码编译的稳定性的, 虽然提供的功能基本都是一样的, 但是缺少了自定义性。

首先，执行一下命令将安装mysql服务的repo仓库安装在本地机器中

$ rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-9.noarch.rpm

其次，根据需要决定repo中的 5.7版本 or 8.0版本是否开启, 然后，使用以下命令将mysql服务安装在本地机器上

xxxxxxxxxx
$ yum --disablerepo=mysql80-community --enablerepo=mysql57-community -y install mysql-community-server

之后，在 /var/log/mysqld.log 中获取 mysql的初始密码

​x
$ systemctl enable --now mysqld

$ grep password /var/log/mysqld.log

最后，使用初始密码登陆到mysql中，修改初始密码(大写、小写、数字、特殊字符[.!?,;'])

xxxxxxxxxx
$ mysql -uroot -p'这里应该填写上截取出来的初始化密码'

mysql> alter user root@localhost identified by 'XiDianKeJi120!@';
mysql> exit

1.2 源码编译安装mysql服务

在实际的生产项目中通常都是指定版本进行安装mysql的, 往往此时通过源码安装mysql无疑是最好的选择. 而且通过源码安装，也可以更方便我们自定义安装环境.

首先，将系统支撑mysql运行的软件包安装好

xxxxxxxxxx
$ yum -y groupinstall "Development Tools"
$ yum -y install cmake bison-devel ncurses-devel libaio-devel openssl-devel

其次，在系统中创建好mysql的相关目录

xxxxxxxxxx
$ mkdir -p /data/mysql/{data,tmp,binlog,logs}

然后，创建运行mysql进程的用户mysql用户

xxxxxxxxxx
$ groupadd mysql
$ useradd -M -g mysql -s /sbin/nologin mysql

最后，将mysql源代码进行解压，并开始安装

xxxxxxxxxx
# 清空相关数据库包避免影响后续新版本安装
$ rpm -qa | grep -i mariadb
mariadb-libs-5.5.68-1.el7.x86_64
$ rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

$ tar xf mysql-boost-5.7.41.tar.gz -C /opt
$ cd /opt/mysql-5.7.41
$ cmake \
-DCMAKE_INSTALL_PREFIX=/data/mysql \
-DMYSQL_DATADIR=/data/mysql/data \
-DMYSQL_UNIX_ADDR=/data/mysql/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_EMBEDDED_SERVER=1 \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306 \
-DWITH_SSL=yes \
-DWITH_DEBUG=0 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=boost

$ screen -S mysqlinstaller
$ screen -ls # 列出活动终端
$ screen -r id.终端名 # 继续执行该终端的显示

$ make # 如果编译过程报错, 先删除CMakeCache.txt, 再处理报错
$ make install

接下来，进入配置mysql服务阶段: 增加mysql命令目录环境变量

xxxxxxxxxx
$ vim /etc/profile.d/mysql_environment.sh
export PATH=$PATH:/data/mysql/bin

$ source /etc/profile

修改mysql服务相关目录的所属权限

xxxxxxxxxx
$ chown -R mysql:mysql /data/mysql

编辑配置文件，将日志、数据目录位置、启动用户等设置好

xxxxxxxxxx
$ vim /etc/my.cnf
[client]
port = 3306
socket = /data/mysql/tmp/mysql.sock

[mysqld]
port = 3306
user = mysql
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysqld.pid
socket = /data/mysql/tmp/mysql.sock
tmpdir = /data/mysql/tmp
server-id = 21
max_connections = 100
max_connect_errors = 10
log-bin = /data/mysql/binlog/mysql-bin
log-error = /data/mysql/logs/mysqld_err.log

$ chown -R mysql:mysql /etc/my.cnf

初始化数据库

xxxxxxxxxx
$ mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

拷贝mysql的配置文件到service中进行管理

xxxxxxxxxx
$ cp /data/mysql/support-files/mysql.server /etc/init.d/mysqld

启动mysqld服务

xxxxxxxxxx
$ service mysqld start && chkconfig mysqld on
$ service mysqld status

数据库基本设置:

xxxxxxxxxx
mysql> select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> set password for 'mysql.session'@'localhost' = password('Qfcloud120!!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set password for 'mysql.sys'@'localhost' = password('Qfcloud120!!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set password for 'root'@'localhost' = password('Qfcloud120!!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE USER 'root'@'192.168.19.%' IDENTIFIED BY 'Qfcloud120!!';  【非必要,不用创建】

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

第二章 SQL语句

数据库SQL语句（Structured Query Language）是用于管理和操作关系型数据库的标准化语言。SQL语句由不同的命令组成，用于执行各种操作，例如查询数据、插入新数据、更新现有数据、删除数据以及定义和修改数据库结构。SQL语句是通用的，几乎所有关系型数据库管理系统（如MySQL、Oracle、SQL Server等）都支持SQL语法，尽管不同的数据库系统可能会有一些特定的语法和功能差异。学习和理解SQL语句对于数据库管理和开发非常重要，它们提供了强大的功能来管理和操作数据库中的数据。

2.1 DDL 语句

DDL（数据定义语言）是数据库中用于定义和管理数据库对象（如库、表、索引等）的语句。DDL语句通常用于创建、修改和删除数据库对象的结构，而不是操作实际的数据。

1. CREATE：用于创建数据库对象

   • CREATE DATABASE：创建新的数据库

   • CREATE TABLE：创建新的数据表

   xxxxxxxxxx
   mysql> CREATE DATABASE qfcloud【 CHARACTER SET utf8 COLLATE utf8_bin 】;
   Query OK, 1 row affected (0.01 sec)
   
   mysql> USE qfcloud;
   
   mysql> CREATE TABLE students (
     id INT PRIMARY KEY AUTO_INCREMENT,
     student_name VARCHAR(255),
     student_sex VARCHAR(10),
     classroom_number VARCHAR(10)
   );
   
   mysql> CREATE INDEX idx_student_name ON students (student_name);
   mysql> CREATE INDEX idx_classroom_number ON students (classroom_number);
   # 索引用于提高查询数据效率，通常需要对经常用作限定条件或查询条件的字段进行索引。这也是性能优化常用的手段
   

    

2. ALTER：用于修改数据库对象的结构

   • ALTER TABLE：修改数据表的结构，如添加、修改或删除列、修改数据类型等

   xxxxxxxxxx
   # >表< 添加字段(列)
   mysql> ALTER TABLE students ADD location varchar(255);
   
   # >表< 删除字段(列)
   mysql> ALTER TABLE students DROP classroom_number;
   
   # >表< 修改字段名称
   mysql> ALTER TABLE students CHANGE student_name Sname varchar(255);
   
   # >表< 查看表结构
   mysql> DESC students;
   +-------------+--------------+------+-----+---------+----------------+
   | Field       | Type         | Null | Key | Default | Extra          |
   +-------------+--------------+------+-----+---------+----------------+
   | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
   | Sname       | varchar(255) | YES  | MUL | NULL    |                |
   | student_sex | varchar(10)  | YES  |     | NULL    |                |
   | location    | varchar(255) | YES  |     | NULL    |                |
   +-------------+--------------+------+-----+---------+----------------+
   4 rows in set (0.00 sec)
   

    

3. DROP：用于删除数据库对象。

   • DROP DATABASE：删除数据库及其相关的对象

   • DROP TABLE：删除数据表

   xxxxxxxxxx
   # 创建两个临时的数据库用于实验
   mysql> CREATE DATABASE tmp_pension;
   mysql> CREATE DATABASE tmp_bions;
   
   mysql> USE tmp_pension;
   mysql> CREATE TABLE beijing (
       id INT PRIMARY KEY AUTO_INCREMENT,
       username VARCHAR(255) NOT NULL COMMENT "姓名",
       regions VARCHAR(255) NOT NULL COMMENT "区县",
       begin_year DATE NOT NULL,
       evidence LONGTEXT
   );
   
   # 删除数据库
   mysql> DROP DATABASE tmp_bions;
   
   # 删除表
   mysql> USE tmp_pension;
   mysql> DROP TABLE beijing;
   

    

4. TRUNCATE：用于快速删除表中的所有数据，但保留表的结构

   • TRUNCATE TABLE：删除表中的所有数据，但保留表的结构和定义，数据一旦被清空将无法恢复。

   xxxxxxxxxx
   mysql> TRUNCATE TABLE beijing;
   

DDL语句在执行时会直接影响数据库的结构，因此在使用时需要谨慎。一些DDL操作可能会导致数据丢失或造成数据库对象的不一致。因此，建议在执行DDL语句之前进行备份，并确保对数据库结构变更的影响有适当的计划和测试。

总之，DDL语句是用于定义和管理数据库对象结构的语句，包括创建、修改和删除数据库对象。它们对于数据库的设计和维护起着重要的作用，允许管理员和开发人员对数据库进行结构上的变更。

2.2 DML 语句

DML（数据操作语言）是数据库中用于操作和管理实际数据的语句。DML语句用于执行数据的插入、更新、删除和查询等操作。下面是一些常见的DML语句及其功能：

1. SELECT：用于从数据库中查询数据

   • SELECT * FROM table_name：查询表中的所有数据

   • SELECT column1, column2 FROM table_name：查询指定列的数据

   • SELECT column1, column2 FROM table_name WHERE condition：根据条件查询数据

   xxxxxxxxxx
   mysql> SELECT username,regions FROM tmp_pension.beijing where id=2;
   
   mysql> SELECT username,regions FROM tmp_pension.beijing WHERE username LIKE '%tom%';
   

    

2. INSERT：用于向数据库表中插入新的数据

   • INSERT INTO table_name (column1, column2) VALUES (value1, value2)：插入一行新数据

   • INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM another_table：从另一个表中插入数据

   xxxxxxxxxx
   mysql> CREATE DATABASE hiopsicu;
   mysql> USE hiopsicu;
   mysql> CREATE TABLE orders (
           order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
           order_date DATE NOT NULL COMMENT "订单日期",
           customer_id BIGINT COMMENT "订单号" 
          );
   
   mysql> USE hiopsicu;
   mysql> INSERT INTO orders (customer_id, order_date) VALUES (110125120562364091, CURDATE());
   
   mysql> INSERT INTO orders VALUES(2, CURDATE(), 110125120562364093);
   

    

3. UPDATE：用于修改数据库表中的现有数据

   • UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition：根据条件更新数据

   xxxxxxxxxx
   mysql> USE hiopsicu;
   mysql> SELECT * FROM orders;
   +----------+------------+--------------------+
   | order_id | order_date | customer_id        |
   +----------+------------+--------------------+
   |        1 | 2023-07-17 | 110125120562364091 |
   +----------+------------+--------------------+
   1 row in set (0.00 sec)
   
   mysql> UPDATE orders SET order_date=CURDATE(),customer_id=110125120562364092 WHERE order_id=1;
   
   mysql> SELECT * FROM orders;
   +----------+------------+--------------------+
   | order_id | order_date | customer_id        |
   +----------+------------+--------------------+
   |        1 | 2023-07-17 | 110125120562364092 |
   +----------+------------+--------------------+
   1 row in set (0.00 sec)
   

    

4. DELETE：用于从数据库表中删除数据

   • DELETE FROM table_name WHERE condition：根据条件删除数据

   xxxxxxxxxx
   mysql> USE hiopsicu;
   mysql> DELETE FROM orders WHERE order_id=1;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> SELECT * FROM orders;
   Empty set (0.00 sec)
   

    

DML语句允许用户对数据库中的数据进行操作，可以执行单个操作或批量操作。这些语句可以根据条件过滤数据、插入新数据、更新现有数据或删除不需要的数据。在执行DML语句时，需要小心处理事务（Transaction）和数据完整性的问题。事务可以用来保证DML操作的一致性和可靠性，确保多个操作要么全部成功，要么全部失败回滚。此外，还应该注意数据的一致性和完整性，避免意外删除或修改重要数据。

总之，DML语句是用于操作和管理数据库中实际数据的语句。它们允许用户进行数据的插入、更新、删除和查询等操作，以满足不同的数据处理需求

2.3 DCL 语句

DCL（数据控制语言）是数据库中用于管理用户访问权限和控制数据库安全性的语句。DCL语句用于授权或收回用户对数据库对象的权限，以及管理数据库用户和角色。下面是一些常见的DCL语句及其功能：

全局权限（Global privileges）

数据库级权限（Database privileges）

表级权限（Table privileges）

1. GRANT：用于授予用户或用户组特定的数据库访问权限

   • GRANT privilege ON object TO user：授予用户对特定对象的权限

   • GRANT privilege1, privilege2 ON object TO user：同时授予用户多个权限

   • GRANT ALL PRIVILEGES ON object TO user：授予用户对特定对象的所有权限

   xxxxxxxxxx
   # 创建数据库用户
   mysql> CREATE USER `username`@`10.9.12.%` identified by 'Qfcloud456!!';
   
   mysql> GRANT SELECT,UPDATE,ALTER,INDEX ON hiopsicu.* TO `username`@`10.9.12.%`;
   mysql> SHOW GRANTS FOR 'username'@'10.9.12.%';
   +------------------------------------------------------------------------------+
   | Grants for username@10.9.12.%                                                |
   +------------------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO 'username'@'10.9.12.%'                                 |
   | GRANT SELECT, UPDATE, INDEX, ALTER ON `hiopsicu`.* TO 'username'@'10.9.12.%' |
   +------------------------------------------------------------------------------+
   2 rows in set (0.00 sec)
   
   mysql> GRANT ALL PRIVILEGES ON hiopsicu.* TO 'username'@'10.9.12.%';
   

    

2. REVOKE：用于收回用户或用户组的数据库访问权限

   • REVOKE privilege ON object FROM user：收回用户对特定对象的权限

   • REVOKE privilege1, privilege2 ON object FROM user：同时收回用户多个权限

   • REVOKE ALL PRIVILEGES ON object FROM user：收回用户对特定对象的所有权限

   xxxxxxxxxx
   mysql> REVOKE SELECT,ALTER ON hiopsicu.* FROM 'username'@'10.9.12.%';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> SHOW GRANTS FOR 'username'@'10.9.12.%';
   +---------------------------------------------------------------+
   | Grants for username@10.9.12.%                                 |
   +---------------------------------------------------------------+
   | GRANT USAGE ON *.* TO 'username'@'10.9.12.%'                  |
   | GRANT UPDATE, INDEX ON `hiopsicu`.* TO 'username'@'10.9.12.%' |
   +---------------------------------------------------------------+
   2 rows in set (0.00 sec)
   

    

3. CREATE USER：用于创建新的数据库用户

   • CREATE USER username IDENTIFIED BY password：创建一个新的数据库用户并设置密码

   xxxxxxxxxx
   mysql> CREATE USER 'devops'@'192.168.12.21' IDENTIFIED BY 'Qfcloud456!!';
   

    

4. DROP USER：用于删除数据库用户

   • DROP USER username：删除指定的数据库用户

   xxxxxxxxxx
   mysql> DROP USER 'devops'@'192.168.12.21';
   

    

DCL语句用于确保数据库的安全性和访问控制，通过授权和收回权限，可以限制用户对数据库对象的访问和操作。角色的使用可以简化权限管理，将权限授予角色，然后将角色分配给用户，减少了直接授权给每个用户的工作量。需要注意的是，执行DCL语句通常需要具备管理员或具有特定权限的用户身份。对于敏感的数据库操作和权限管理，应谨慎处理，以防止未经授权的访问和数据泄露。总之，DCL语句用于管理用户访问权限和控制数据库安全性。通过授权和收回权限，以及管理用户和角色，可以确保数据库的安全访问和操作。

2.4 DQL 语句

DQL（数据查询语言）是数据库中用于从数据库中查询和检索数据的语句。DQL语句主要用于执行各种查询操作，以获取所需的数据结果集。下面是一些常见的DQL语句及其功能：

xxxxxxxxxx
# 准备几个表用于查询使用
$ wget https://mirrors.qfcc.online/scripts/mysql/backup.sql --no-check-certificate
$ mysql -uroot -p'Qfcloud120!!'

mysql> CREATE DATABASE class;
mysql> source backup.sql

mysql> desc Student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Sno   | int(11)      | YES  | UNI | NULL    |       |
| Sname | varchar(256) | YES  | UNI | NULL    |       |
| Ssex  | varchar(128) | YES  |     | NULL    |       |
| Sage  | int(11)      | YES  |     | NULL    |       |
| Sdept | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc SC;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Sno   | int(11) | YES  |     | NULL    |       |
| Cno   | int(11) | YES  |     | NULL    |       |
| Grade | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc Course;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Cno    | int(11)      | YES  | UNI | NULL    |       |
| Cname  | varchar(256) | YES  | UNI | NULL    |       |
| Credit | int(11)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1. SELECT：用于从数据库中查询数据

   • SELECT * FROM table_name：查询表中的所有数据

   • SELECT column1, column2 FROM table_name：查询指定列的数据

   • SELECT column1, column2 FROM table_name WHERE condition：根据条件查询数据

    

2. FROM：用于指定要查询的数据表

   • SELECT * FROM table_name：从指定表中查询所有数据

    

3. WHERE：用于指定查询条件，对数据进行过滤

   • SELECT * FROM table_name WHERE condition：根据指定条件查询数据

   • 运算符：等于（=）、不等于（<>）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）

   • 逻辑运算符： and并且、or或者、not非(取反)

    

4. ORDER BY：用于对查询结果进行排序。

   • SELECT * FROM table_name ORDER BY column1 ASC：按照指定列进行升序排序

   • SELECT * FROM table_name ORDER BY column1 DESC：按照指定列进行降序排序

    

5. 函数: 可以对数据进行基本的运算

   • sum(fields_name) 求和

   • avg(fields_name) 求平均值

   • min(fields_name) 求最小值

   • max(fields_name) 求最大值

   • count(fields_name) 求出现的个数

   • Example: select Student.Sname, avg(SC.Grade) as '平均分' from Student, SC where Student.Sno=SC.Sno and Student.Sname='潘瑞';

    

6. JOIN：用于将多个表根据关联列进行连接操作

   • SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2：根据指定的关联列将两个表连接起来

    

7. group by ... having .....

   • SELECT SC.Sno, sum(SC.Grade) as '总分' from SC group by

    

8. limit: 限制数据条目数

   • SELECT * FROM table1 WHERE LIMIT 10;

    

9. like % : 范围查找，模糊查询；like 像。。。

   • SELECT * FROM table1 WHERE fields_name LIKE '%keyword%';

    

10. Between: 在。。。。之间 (不推荐使用)

• SELECT * FROM table1 WHERE fields_name BETWEEN 最小值 AND 最大值;

查询所有学生各科目得分(要显示学生姓名、课程名称、成绩、对所有学生按某个课程的成绩倒序排序)

xxxxxxxxxx
select Student.Sname, Course.Cname, SC.Grade from Student left join SC on (Student.Sno=SC.Sno) left join Course on (SC.Cno=Course.Cno) where Course.Cname='这里写课程的名称' order by SC.Grade;

查询平均成绩大于70分的学生姓名、学号以及平均分数(需要用到临时表)

xxxxxxxxxx
select Student.Sname, SP.Sno, SP.avgs from Student left join (select Sno, avg(Grade) as avgs from SC group by Sno)SP on (Student.Snumber=SP.Sno) where SP.avgs>=70 order by SP.avgs;

查询出所有学生的总分并进行排名，从大到小，并过滤出总分超过四百分的学生

xxxxxxxxxx
SELECT Student.Sname, SUM(SC.Grade) AS grades FROM Student LEFT JOIN SC ON (Student.Sno=SC.Sno) GROUP BY Sname HAVING SUM(SC.Grade) > 400 ORDER BY SUM(SC.Grade) DESC;

DQL语句允许用户灵活地查询数据库中的数据，并可以使用多种操作符、函数和聚合函数来处理数据。通过使用DQL语句，可以实现复杂的数据检索和分析，以满足不同的数据需求。需要注意的是，DQL语句只用于查询和检索数据，不会对数据库中的数据进行修改。如果需要对数据进行插入、更新或删除操作，应使用DML（数据操作语言）中的INSERT、UPDATE和DELETE语句。

总之，DQL语句用于从数据库中查询和检索数据。通过SELECT语句及其相关子句，可以指定要查询的数据表、条件、排序方式等，以获取所需的数据结果集。

第三章 数据库备份及恢复

3.1 逻辑备份及恢复

MySQL的逻辑备份是通过将数据库的结构和数据以逻辑形式导出为SQL语句的方式进行备份。逻辑备份可以使用mysqldump工具来完成，而恢复过程则是将备份文件中的SQL语句重新执行以还原数据库。

输入以下命令，使用mysqldump进行逻辑备份，并将备份保存到指定文件中：

xxxxxxxxxx
$ mysqldump -u your_username -p your_password your_database [your_table] > backup.sql

输入以下命令，使用mysql命令执行备份文件中的SQL语句来恢复数据库：

xxxxxxxxxx
$ mysql -u your_username -p your_password your_database < backup.sql

下面是一个备份的shell脚本，该脚本应该放在计划任务中进行周期执行

xxxxxxxxxx
#!/bin/bash

# MySQL连接信息
MYSQL_USER="your_username"
MYSQL_PASSWORD="your_password"
DATABASE_NAME="your_database"

# 备份文件路径和名称
BACKUP_DIR="/path/to/backup/"
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S).sql"

# 执行备份
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > $BACKUP_FILE

# 完成备份
echo "Backup completed: $BACKUP_FILE"

可以将脚本添加到计划任务（例如使用cron）中，以在指定的时间间隔自动执行备份操作。例如，将脚本设置为每天凌晨执行备份：

xxxxxxxxxx
$ crontab -e
0 0 * * * /bin/bash /path/to/backup_script.sh

3.2 物理备份及恢复

二进制日志备份和恢复是MySQL的物理备份方法，它通过记录和重放数据库操作的二进制日志来实现。这种备份方法具有以下优势：

1. 实时增量备份：只备份自上次备份以来的更改部分。这意味着可以更快地备份数据，并且可以在任意时间点进行恢复

2. 高效性：二进制日志备份是基于物理级别的备份，直接备份和恢复数据库的原始二进制数据文件，因此备份和恢复的过程非常高效

3. 精确恢复：通过重放二进制日志，可以精确还原数据库中的操作，包括插入、删除等。这使得在特定时间点进行数据恢复成为可能

4. 跨版本兼容性：二进制日志备份和恢复方法通常在不同的MySQL版本之间兼容性较好，这意味着可以将备份文件从一个版本的MySQL服务器迁移到另一个版本的MySQL服务器

⚠️ 确保MySQL服务器的二进制日志启用并处于活动状态。可以通过修改MySQL配置文件来启用二进制日志

xxxxxxxxxx
# 恢复命令(清空或者创建库)
mysql> show binlog events; # 主要查看其中的position的起止位置

$ mysqlbinlog --database=class --start-position=?  --stop-position=? /var/lib/mysql/mysql-bin. | mysql -uroot -p

Xtrabackup 物理备份

Percona XtraBackup（简称PXB） 是 Percona公司开发的一个用于MySQL数据库物理热备份的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份， 在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周日用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobackupex。以下是它的优点:

• 备份速度快，物理备份可靠

• 备份过程不会打断正在执行的事务（无需锁表）

• 能够基于压缩等功能节约磁盘空间和流量

• 自动备份校验

• 还原速度快

• 可以将备份传输到另外一台机器上

• 在不增加服务器负载的情况备份数据

xxxxxxxxxx
##>> 安装percona xtrabackup 备份工具到机器中
$ wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

$ yum -y install percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

##>> 验证是否安装成功🏅️
$ xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86_64) (revision id: 44a8f7b)

xxxxxxxxxx
##>> 设置备份用户
$ mysql -uroot -p
password:

mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Qfcloud120!!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'backup'@'localhost';

xxxxxxxxxx
##>> 开始备份
$ mkdir -p /data/mysql/backup/
$ innobackupex --defaults-file=/etc/my.cnf \
    --user=backup --password='Qfcloud120!!' \
    /data/backup/

xxxxxxxxxx
##>> 测试备份是否能够进行恢复
$ mysql -uroot -p
password:

mysql> DROP DATABASE class;

$ systemctl stop mysqld
$ rm -rf /var/lib/mysql/*
$ innobackupex --defaults-file=/etc/my.cnf --user=backup --use-memory=1G --apply-log /data/backup/2023-08-09_21-07-16/
$ innobackupex --defaults-file=/etc/my.cnf --user=backup --copy-back /data/backup/2023-08-09_21-07-16/
$ chown -R mysql:mysql /var/lib/mysql
$ systemctl start mysqld

xxxxxxxxxx
##>> 增量备份
$ mkdir -p /data/mysql/backup/increments

###>>> 模拟数据增加的操作

$ innobackupex --defaults-file=/etc/my.cnf --user=backup --password='Qfcloud120!!' \
    --incremental-basedir=/data/backup/2023-08-09_21-07-16/ --incremental /data/backup/increments/

$ mysql -uroot -p'Qfcloud120!!'

$ innobackupex --defaults-file=/etc/my.cnf --user=backup --apply-log --redo-only /data/backup/2023-08-09_21-07-16/
$ innobackupex --defaults-file=/etc/my.cnf --user=backup --apply-log --redo-only /data/backup/2023-08-09_21-07-16/ --incremental-dir=/data/backup/increments/2023-08-09_21-28-40/

$ systemctl stop mysqld
$ rm -rf /var/lib/mysql/*

$ innobackupex --defaults-file=/etc/my.cnf --user=backup --copy-back /data/backup/2023-08-09_21-07-16/
$ chown -R mysql:mysql /var/lib/mysql
$ systemctl start mysqld

第四章 数据库高可用架构

主从复制（Master-Slave Replication）是一种常见的数据库复制技术，用于将一个MySQL数据库服务器（主服务器）的数据和操作复制到其他MySQL服务器（从服务器）上。主从复制提供了数据的冗余备份、读写分离以及分布式数据处理等功能。

MySQL的高可用架构无论是社区还是官方，一直在技术上进行探索，这么多年提出了多种解决方案，比如 MMM, MHA, NDB Cluster, Galera Cluster, InnoDB Cluster, 腾讯的PhxSQL, MySQL Fabric., aliSQL。MySQL官方在2017年4月推出了一套完整的、高可用的Mysql解决方案 - MySQL InnoDB Cluster, 即一组MySQL服务器可以配置为一个MySQL集群。在默认的单主节点模式下，集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过MySQL Router连接到主服务程序。如果主服务连接失败，则次要的节点自动提升为主节点，MySQL Router请求到新的主节点。

4.1 主从复制架构

⚠️ 原理: 主库开启二进制日志, 通过mysqldumpthread进程与从库的IO线程进行数据共享, 从库IO线程将读取到的数据写入到本地中继日志中, 从库的SQL线程将中继日志中的数据回放到从库中, 以此完成数据同步

这里准备出三台 2c2G 的机器即可, 以此来完成 一主两从 的结构; MySQL服务器的安装参考第一章中的源码安装方式, 这里就不多赘述了。主从复制的目的是为了保证数据的高可用性以及数据安全性, 这在生产架构中起到至关重要的作用, 对于原理和搭建逻辑一定要了熟于心

对于主库(master)而言, 将其配置文件增加下面的内容即可:

xxxxxxxxxx
$ vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#标识唯一id（必须），一般使用ip最后位
server-id=21
#不同步的数据库，可设置多个
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
#指定需要同步的数据库（和slave是相互匹配的），可以设置多个
binlog-do-db=test
#日志清理时间
expire_logs_days=7
#日志大小
max_binlog_size=100m
#缓存大小
binlog_cache_size=4m
#最大缓存大小
max_binlog_cache_size=521m

$ service mysqld restart

对于主库(master)而言, 开启让从库读取数据的权限也是必要的

xxxxxxxxxx
$ mysql -uroot -p
password:

mysql> CREATE USER 'rep'@'192.168.19.%' IDENTIFIED BY 'Qfcloud120!!';
mysql> GRANT FILE ON *.* TO 'rep'@'192.168.19.%';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.19.%';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

$ service mysqld restart
$ mysql -uroot -p
password:

mysql> SHOW MASTER STATUS;

对于从库(slave)而言, 配置也是需要进行修改的

xxxxxxxxxx
$ vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
server-id=22
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
#与主库配置保持一致
replicate-do-db=test
replicate-ignore-db=mysql
slave-skip-errors=all
slave-net-timeout=60

$ service mysqld restart

在修改完配置文件后, 即可开始连接主库

xxxxxxxxxx
$ mysql -uroot -p
password: 

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO 
         master_host='192.168.19.21',
         master_user='rep',
         master_password='HiOpsICU120!!',
         master_log_file='mysql-bin.000002',
         master_log_pos=154;
mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

4.2 MySQL高可用复制架构 (MySQL InnoDB Cluster)

MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysq Innodb Cluster是利用组复制的 pxos 协议，保障数据一致性，组复制支持单主模式和多主模式。MySQL InnoDB 集群由以下几部分组成：

• MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性

• MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器

• MySQL Shell：通过内置的管理API创建及管理Innodb集群

组复制提供了内置的组成员管理、数据一致性保证、冲突检测和处理、节点故障检测和数据库故障转移相关操作的本地高可用性，无需人工干预或自定义工具。组复制同时实现了带自动选主的单主模式及任意更新的多主模式。通过使用一个强大的新的组通信系统，它提供了流行的Pxos算法的内部实现，来自动协调数据复制、一致性、membership。这提供了使MySQL数据库高度可用所需的所有内置机制。

通过组复制，一组服务器协调在一起形成一个组。组成员是动态的，服务器可以自愿或强制的地离开及随时加入。组将根据需要自动重新配置自己，并确保任何加入成员与组同步。这样就可以方便地在需要时快速地调整数据库的总容量。

安装步骤:

• 初始化每个服务器(ALL)

  xxxxxxxxxx
  $ wget https://mirrors.qfcc.online/scripts/mysql/innodb-cluster-node-initial.sh
  $ bash innodb-cluster-node-initial.sh
  

• 下载并安装 mysql-shell(ALL)、mysql-router(dbrouter)的包

  xxxxxxxxxx
  $ wget https://mirrors.qfcc.online/packages/mysql/mysql-router-2.1.6-linux-glibc2.12-x86-32bit.tar.gz
  $ tar xf mysql-router-2.1.6-linux-glibc2.12-x86-32bit.tar.gz
  $ mv mysql-router-2.1.6-linux-glibc2.12-x86-32bit /usr/local/mysql-router
  
  $ wget https://mirrors.qfcc.online/packages/mysql/mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz
  $ tar xf mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz
  $ mv mysql-shell-1.0.11-linux-glibc2.12-x86-64bit /usr/local/mysql-shell
  
  $ vim /etc/profile.d/mysql-router.sh
  export PATH=$PATH:/usr/local/mysql-router/bin:/usr/local/mysql-shell/bin
  $ source /etc/profile
  
  $ mysqlprovision --version
  mysqlprovision version 2.1.0
  
  $ mysqlrouter --version
  MySQL Router v2.1.6 on Linux (32-bit) (GPL community edition)
  
  $ mysqlsh --version
  mysqlsh   Ver 1.0.11 for Linux on x86_64 - for MySQL 5.7.20 (MySQL Community Server (GPL))
  

• 修改三台机器的mysql配置文件(三台机器并没有做主从复制等结构), 其次修改mysql-node-b、mysql-node-c机器中的配置文件, 但是server_id、loose-group_replication_local_address 两个参数要记得修改, 每台机器都是不一样的。

  xxxxxxxxxx
  $ cp /etc/my.cnf /etc/my.cnf.bak
  $ >/etc/my.cnf
  
  $ vim /etc/my.cnf
  #复制框架
  skip_ssl
  symbolic-links = 0
  gtid_mode = ON
  enforce_gtid_consistency = ON
  binlog_checksum = NONE
  log_slave_updates = ON
  binlog_format = ROW
  master_info_repository = TABLE
  relay_log_info_repository = TABLE
  
  #组复制设置
  # server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
  transaction_write_set_extraction = XXHASH64
  # 告知插件加入或创建组命名，UUID
  loose-group_replication_group_name = "369f4b15-0991-4cae-9333-63c9ab0a8485"
  # server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组, 所以设置为OFF。
  loose-group_replication_start_on_boot = off
  # 告诉插件使用IP地址，端口24901用于接收组中其他成员转入连接
  loose-group_replication_local_address = "192.168.19.21:24901"
  # 启动组server，子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
  loose-group_replication_group_seeds = "192.168.19.21:24901,192.168.19.22:24901,192.168.19.23:24901"
  loose-group_replication_bootstrap_group = off
  # 使用MGR的单主模式
  loose-group_replication_single_primary_mode = on
  disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
  report_port = 3306
  
  $ service mysqld restart
  

• 在mysql-node-a中执行以下步骤来逐步创建innodb-cluster.

  另外两台机器一样(但要增加loose-group_replication_allow_local_disjsont_gtids_join=on配置到另外两个节点中)

  xxxxxxxxxx
  $ mysqlsh
  MySQL Shell 1.0.11
  Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective owners.
  Type '\help' or '\?' for help; '\quit' to exit.
  Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
  
  mysql-js> shell.connect('root@localhost:3306')
  Please provide the password for 'root@localhost:3306':
  Creating a Session to 'root@localhost:3306'
  Your MySQL connection id is 3
  No default schema selected; type \use <schema> to set one.
  
  mysql-js> dba.configureLocalInstance();
  Please provide the password for 'root@localhost:3306':
  
  Detecting the configuration file...
  Found configuration file at standard location: /etc/my.cnf
  Do you want to modify this file? [Y|n]:  [Y|n]:  # 直接回车
  MySQL user 'root' cannot be verified to have access to other hosts in the network.
  
  1) Create root@% with necessary grants
  2) Create account with different name
  3) Continue without creating account
  4) Cancel
  Please select an option [1]: 1
  Password for new account:  # 设置密码
  Confirm password:
  Validating instance...
  
  The instance 'localhost:3306' is valid for Cluster usage
  You can now use it in an InnoDB Cluster.
  
  {
      "status": "ok"
  }
  mysql-js> # 此处停留不要退出
  

• 通过dbrouter机器连接mysql-node-a机器来创建cluster

  xxxxxxxxxx
  mysqlsh
  MySQL Shell 1.0.11
  
  Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type '\help' or '\?' for help; '\quit' to exit.
  
  Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
  mysql-js> shell.connect('root@mysql-node-a:3306');
  Please provide the password for 'root@mysql-node-a:3306':
  Creating a Session to 'root@mysql-node-a:3306'
  Your MySQL connection id is 21
  No default schema selected; type \use <schema> to set one.
  mysql-js> var cluster = dba.createCluster('myCluster');
  A new InnoDB cluster will be created on instance 'root@mysql-node-a:3306'.
  
  Creating InnoDB cluster 'myCluster' on 'root@mysql-node-a:3306'...
  Adding Seed Instance...
  
  Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
  At least 3 instances are needed for the cluster to be able to withstand up to
  one server failure.
  
  mysql-js> cluster.status();
  {
      "clusterName": "myCluster",
      "defaultReplicaSet": {
          "name": "default",
          "primary": "mysql-node-a:3306",
          "status": "OK_NO_TOLERANCE",
          "statusText": "Cluster is NOT tolerant to any failures.",
          "topology": {
              "mysql-a:3306": {
                  "address": "mysql-a:3306",
                  "mode": "R/W",
                  "readReplicas": {},
                  "role": "HA",
                  "status": "ONLINE"
              }
          }
      }
  }
  
  mysql-js> cluster.addInstance('root@mysql-node-b:3306')
  mysql-js> cluster.addInstance('root@mysql-node-c:3306')
  mysql-js> cluster.status();
  

• 启动管理节点的route

  进入 mysql-router 管理节点中 mysql-router安装目录中, 配置并启动 router

  xxxxxxxxxx
  # 生成默认的mysql-router的配置文件在当前目录中
  $ /usr/local/mysql-router/bin/mysqlrouter --bootstrap root@mysql-node-a:3306 -d myrouter --user=root
  
  # 启动mysqlroute服务
  $ /root/myrouter/start.sh
  

• 这样就可以使用MySQL客户端连接router了. 下面验证下连接router:

   

  ⚠️⚠️⚠️在连接之前需要对 主库(R/W权限)中 创建一个管理员用户，比如 “dba”@“这里写mysql-router的IP地址”

   

  a) 管理节点本机mysql-shell连接:

  xxxxxxxxxx
  $ mysqlsh --uri root@localhost:6446
  

  b) 管理节点本机mysql连接:

  xxxxxxxxxx
  $ mysql -u root -h 127.0.0.1 -P 6446 -p
  

  c) 远程客户机通过route连接mysql

  xxxxxxxxxx
  $ mysql -u root -h 192.168.19.24 -P 6446 -p