mysql数据库学习笔记

mysql数据库

关系型数据库

是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据
关系型数据库这一系列的行和列被称为表，一组表组成了数据库。
用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

关系模型可用简单的“实体-关系”(E-R)图来表示
E-R图中包含了实体(数据对象)、关系和属性三个要素

关系型数据库中的相关概念

• 关系：( Relational ) 这里的关系，指的是数学上的定义，是集合论中的关系模型，而不是我们日常生活中的人与人，人与物之间的关系。
• 行：( Row ) 表中的每一行，又称为一条记录 record。
• 列 ：( column ) 表中的每一列，称为属性，字段，域 field。
• 主键：( Primary key ) 一个或多个字段的组合，用于惟一确定一个记录的字段，一张表只有一个主键，主键字段不能为空 NULL。
• 唯一键：( Unique key ) 一个或多个字段的组合，用于惟一确定一个记录的字段，一张表可以有多个UK，而且UK字段可以为NULL。
• 域：( domain ) 属性的取值范围，如，性别只能是’男’和’女’两个值，人类的年龄只能0-150。

常见的关系型数据库系统

MySQL：MySQL，MariaDB，Percona Server
PostgreSQL：PgSQL，EnterpriseDB
Oracle：Oracle
SQLServer：MS SQLServer
DB2

实体间的联系有三种类型：

一对一联系 ( 1:1 )：例如，一个学号只能分配给一个同学，每个同学都有一个学号，则学号与同学的联系是一对一。
一对多联系 ( 1:n )：例如，一个老师可以教授多门课程，每门课程只能有一个老师教授，则老师与课程的联系是一对多。
多对多联系 ( m:n )：例如，一个同学可以报名多门课程，每门课程下有多个同学，则同学与课程的联系是多对多。

三范式

• 第一范式 1NF (确保每列保持原子性)
  第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。其核心是要保证数据表中的列里面没有重复值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性
• 第二范式 2NF (确保表中的每列都和主键相关)
  第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• 第三范式 3NF (确保每列都和主键列直接相关，而不是间接相关)
  第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
  必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

关系数据库优缺点

（１）易于维护：都是使用表结构，格式一致；
（２）使用方便：SQL语言通用，可用于复杂查询；
（３）复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

（１）读写性能比较差，尤其是海量数据的高效率读写；
（２）固定的表结构，灵活度稍欠；
（３）高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

mysql安装

包管理器安装

默认开启3306监听端口

mysql-crud

DDL：数据定义语言

创建，删除 数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database ying;
Query OK, 1 row affected (0.01 sec)
mysql> drop database ying;
Query OK, 0 rows affected (0.00 sec)

创建 查看 删除表

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb;
Database changed
mysql> create table host(id int unsigned auto_increment primary key,host varchar(24) ,ip varchar(16),cname varchar(24));
Query OK, 0 rows affected (0.01 sec)

mysql> desc host;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| host  | varchar(24)  | YES  |     | NULL    |                |
| ip    | varchar(16)  | YES  |     | NULL    |                |
| cname | varchar(24)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> create table ffbb (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwwd char(48) DEFAULT ' ',PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| ffbb             |
| host             |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table ffbb;
Query OK, 0 rows affected (0.01 sec)

DML管理表中的数据记录

insert ——插入
update——修改、更新数据表中的数据记录

• UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

delete——删除

mysql> CREATE TABLE student (
    -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL,
    -> age tinyint UNSIGNED, #height DECIMAL(5,2),
    -> gender ENUM('M','F') default 'M'
    -> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert student (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.01 sec)

mysql> select  * from  student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
+----+----------+------+--------+
1 row in set (0.00 sec)

mysql> update student set  age=18 where id=10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from  student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   18 | M      |
+----+----------+------+--------+
1 row in set (0.00 sec)

mysql> delete from student ;
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  student;
Empty set (0.00 sec)

drop删除和delete删除的区别

• 功能不同
  delete 用于从表中删除行或数据，其中可以根据特定条件删除选定的行或数据。delete 会将数据装入事务日志，并且能够回退操作。删除后还可以进行 rollback 操作，将删除的数据重新插入到表中。
  drop 用于删除整张表，也可以删除其他数据库对象，例如视图或索引。drop 操作是不可恢复的，也就是说，删除了对象就无法再恢复，因此在使用 drop 命令时应谨慎操作。
• 执行速度和效率不同
  由于不同的操作方式， delete 和 drop 对数据库性能和效率的影响也不同。
  delete 执行速度较慢，因为它需要遍历整个表来查找要删除的行
  drop 执行速度较快，因为它仅需要删除表和表的元数据，不会对表数据进行查找或删除。

因此，如果只需要删除几行记录，可以使用 delete，但是如果要删除整张表或其他数据库对象，则应该使用 drop。

DQL查询数据记录

SELECT 字段名1,字段名2[,…] FROM 表名[WHERE 条件表达式];

#查询数据表信息
SELECT * FROM stu;
SELECT id,name,score FROM stu WHERE id=2;
#以列表方式竖向显示
select name from stu \G 
#只显示头2行
select * from stu limit 2;
#显示第2行后的前3行
select * from stu limit 2,3;     

多表联合查询

用户及权限管理

MySQL8.0中默认没有可以远程登录的用户
mysql默认 root可以不用使用密码

mysql> select Host,User,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| Host      | User             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             |                                                                        |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>  set password for root@'localhost'='123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select Host,User,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| Host      | User             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql存储引擎

生产默认InnoDB 存储引擎

MYISAM的特性

（1）MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。
（2）访问速度快，对事务完整性没有要求
（3）MyISAM 适合查询、插入为主的应用场景（对存储引擎没有安全要求）
（4）MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名不同
（5）表级锁定形式，数据在更新时锁定整个表
数据库在读写过程中相互阻塞——串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来，会在数据写入的过程阻塞用户数据的读取也会在数据读取的过程中阻塞用户的数据写入表级锁定形式特性:数据单独写入或读取，速度过程较快且占用资源相对少
（6）MyIsam 是表级锁定，读或写无法同时进行好处是:分开执行时，速度快、资源占用相对较少(相对)

InnoDB 存储引擎特点

• 支持事务，适合处理大量短期事务
• 行级锁定
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持表分区，支持表空间
• 从MySQL5.5 后支持全文索引
• 从MySQL5.5.5 开始为默认的数据库引擎

InnoDB 存储引擎适用场景

• 数据读写都较为频繁的业务
• 需要事务支持的业务
• 对并发要求较高的业务
• 对数据一致性要求较高的业务

查看 mysql 支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看当前默认的存储引擎
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)

查看表使用的存储引擎

mysql> show table status from testdb where name='student'\G
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 11
    Create_time: 2024-03-12 23:00:39
    Update_time: 2024-03-12 23:09:31
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

索引

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键 key，索引通过存储引擎实现。

优点

• 大大加快数据的检索速度;
• 创建唯一性索引，保证数据库表中每一行数据的唯一性;
• 加速表和表之间的连接;
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

• 索引需要占物理空间。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

索引类型

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

事务

事务是一组具有原子性的 SQL 语句，或者说一个独立单元。可以理解为一个事务对应的是一组完整的业务，这个业务有一条或多条 SQL 语句组成。所谓原子性是指，这一组业务中的 SQL 语句不可分割，所以，要么全部 SQL 语句都执行成功，事务也就执行成功；只要有一条 SQL 语句执行失败，则整个事务都要回滚到事务开始前。

事务的（ACID）特点

四个特性

• 原子性( Atomicity )
  原子性又称不可分割性。一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性( Consistency )
  事务的执行结果，必须是符合预期的，这表示在事务中进行的数据读写，完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性( Isolation )
  隔离性又称独立性。MySQL 允许多个事务并发，同时进行写操作，隔离性用于实现事务的并发控制，防止多个事务并发执行时的交叉执行而导致的数据不一致。事务的隔离分为不同的级别，包括读未提交( Read uncommitted )，读提交( read committed )，可重复读( repeatable read )，串行化( Serializable )。
• 持久性( Durability )
  事务执行成功后，其对于数据的修改会永久保存于数据库中。

事务隔离级别

MySQL 的事务隔离级别一共有四个，分别是读未提交、读已提交、可重复读以及可串行化。
MySQL 的隔离级别的作用就是让事务之间互相隔离，互不影响，这样可以保证事务的一致性。

• 读未提交（READ UNCOMMITTED）
  在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。可能发生脏读、不可重复读和幻读问题，一般很少使用此隔离级别。
• 读已提交（READ COMMITTED）
  在读已提交隔离级别下，事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题，一般很少使用此隔离级别。
• 可重复读（REPEATABLE READ）
  在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据。可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题。
• 可串行化（SERIALIZABLE）
  可串行化，又称序列化。各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写
  锁）。

以上四种隔离级别，从上往下，隔离强度逐渐增强，性能逐渐变差，需要消耗的 MySQL 的资源越多，所以并不是隔离强度越高越好，采用哪种隔离级别要根据系统需求权衡决定，MySQL 中默认的隔离级别是可重复读。

事务并发中可能出现的问题

• 脏读（Dirty Read）
  脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。（脏读只在读未提交隔离级别才会出现）
• 不可重复读（Non-Repeatable Read）
  不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。（不可重复读在读未提交和读已提交隔离级别都可能会出现）
• 幻读（Phantom）
  一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。（幻读在读未提交、读已提交、
  可重复读隔离级别都可能会出现）

#禁止自动提交
SET AUTOCOMMIT=0;	
#开启自动提交，Mysql默认为1
SET AUTOCOMMIT=1;	
#查看Mysql中的AUTOCOMMIT值
SHOW VARIABLES LIKE 'AUTOCOMMIT';

日志管理

事务日志

InnoDB 引擎使用 force log at commit 机制实现事务的持久性，即在事务提交的时候，必须先将与其相关的日志写到磁盘上的 redo log file 和 undo log file 文件中进行持久化。
事务日志是连续的磁盘空间，因此IO是顺序的，性能比较高，可以保证数据及时写入事务日志。InnoDB 引擎使用日志来减少提交事务时的开销。

• redo log （重做日志）
  InnoDB 引擎对数据更新时，先将更新记录写入到 redo log 的 buffer 中，而后在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。然后再将 commit 的事务的相关数据落盘，也就是说，先写日志，再去修改对应的 MySQL 中的数据文件，这就是所谓的预写式技术（Write Ahead logging）。如果事务在 commit 之后数据落盘时失败，则下次启动 MySQL 时，可以根据己经保存的 redo log 再次进行操作，保证 commit 提交成功。当然，如果 redo log 也写失败了，那么这段时间内的commit 就丢失了。
  redo log 通常是物理日志，用来保证事务的原子性，持久性。
• undo log（回滚日志）
  保存与执行的操作相反的操作，即记录某数据被修改前的值，可以用来在事务失败时进行回滚到某行记录的某个版本，其具体流程与 redo log 相似，也要先于 commit 数据落盘，更改对应的 MySQL 数据文件之前保存。
  undo log 通常是逻辑日志，用来保证事务的原子性， 帮助事务回滚以及MVCC功能。

相关变量

mysql>  show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |
| innodb_log_files_in_group          | 2        |
| innodb_log_group_home_dir          | ./       |
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.00 sec)

mysql> select 50331648/1024/1024;
+--------------------+
| 50331648/1024/1024 |
+--------------------+
|        48.00000000 |
+--------------------+
1 row in set (0.01 sec)

错误日志

mysql>  select @@log_error;
+---------------------------+
| @@log_error               |
+---------------------------+
| /var/log/mysql/mysqld.log |
+---------------------------+
1 row in set (0.01 sec)

通用日志

记录对数据库的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。默认情况下，通用查询日志功能是关闭的。可以通过配置开启此日志，并决定将日志存储到文件或数据表中。如果选择记录到数据表中，则具体的表是mysql.general_log

#相关配置项
general_log=0|1 是否开启通用日志
general_log_file=/path/log_file 日志文件
log_output=FILE|TABLE|NONE 记录到文件中还是记录到表中

mysql>  select @@general_log,@@general_log_file,@@log_output;
+---------------+---------------------------+--------------+
| @@general_log | @@general_log_file        | @@log_output |
+---------------+---------------------------+--------------+
|             0 | /var/lib/mysql/rocky2.log | FILE         |
+---------------+---------------------------+--------------+

[root@rocky2 ~]# ll /var/lib/mysql/rocky2.log
ls: cannot access '/var/lib/mysql/rocky2.log': No such file or directory

#开启日志
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)

慢查询日志

记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。慢查询日志默认不开启。

二进制日志

记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，但不包含查询操作语句
自 MySQL8.0 开始，默认开启了二进制日志功能

事务日志和二进制区别

• 事务日志可以看作是在线日志，二进制日志可以看作是离线日志
• 事务日志记录事务执行的过程，包括提交和未提交，二进制日志记录只记提交的过程
• 事务日志只支持 InnoDB 存储引擎，二进制支持 InnoDB 和 MyISAM 存储引擎

二进制日志记录三种格式

• Statement：基于语句的记录模式，日志中会记录原生执行的 SQL 语句，对于某些函数或变量，不会替换。
• Row：基于行的记录模式，会将 SQL 语句中的变量和函数进行替换后再记录。
• Mixed：混合记录模式，在此模式下，MySQL 会根据具体的 SQL 语句来分析采用哪种模式记录日
  志。

二进制日志相关配置设置

sql_log_bin=1|0 #是否开启二进制日志，可动态修改，是系统变量，而非服务器选项
log_bin=/path/file_name #是否开启二进制日志，两项都要是开启状态才表示开启，此项是服务器选项，指定的是日志文件路径
log_bin_basename=/path/log_file_name #binlog文件前缀
log_bin_index=/path/file_name #索引文件路径
binlog_format=STATEMENT|ROW|MIXED #log文件格式
max_binlog_size=1073741824 #单文件大小，默认1G，超过大小会自动生成新的文件,重启服务也会生成新文件
binlog_cache_size=4m #二进制日志缓冲区大小，每个连接独占大小
max_binlog_cache_size=512m #二进制日志缓冲区总大小，多个连接共享
sync_binlog=1|0 #二进制日志落盘规则，1表示实时写，0 表示先缓存，再批量写磁盘文件
expire_logs_days=N #二进制文件自动保存的天数，超出会被删除，默认0，表示不删除

#MySQL8.0以后默认开启二进制日志
mysql> select  @@log_bin,@@sql_log_bin,@@log_bin_basename,@@log_bin_index,@@binlog_format,@@max_binlog_size\G
*************************** 1. row ***************************
         @@log_bin: 1
     @@sql_log_bin: 1
@@log_bin_basename: /var/lib/mysql/binlog
   @@log_bin_index: /var/lib/mysql/binlog.index
   @@binlog_format: ROW
 @@max_binlog_size: 1073741824
1 row in set, 1 warning (0.00 sec)


[root@rocky2 ~]# ll -h /var/lib/mysql/binlog*
-rw-r-----. 1 mysql mysql 2.8K Mar 12 23:09 /var/lib/mysql/binlog.000001
-rw-r-----. 1 mysql mysql   16 Mar 12 22:30 /var/lib/mysql/binlog.index

刷新二进制日志文件

默认情况下，当前使用的二进制文件达到设置大小后，会重新生成新的二进制文件。
除此之外，也可以手动刷新二进制日志文件

 flush logs;

mysql备份和恢复

数据库备份内容

• 数据库中的数据
• 二进制日志，InnoDB 事务日志
• 用户账号，权限配置，程序代码（视图，存储过程，触发器，事件调度器）
• 相关配置文件

冷备份

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录

mysqldump备份

准备数据

mysql> create table b (id int auto_increment primary key,name char(10),age int,sex char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into b values(1,'xiaoli',18,'F'),(2,'xiaozhao',19,'M');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *  from b;
+----+----------+------+------+
| id | name     | age  | sex  |
+----+----------+------+------+
|  1 | xiaoli   |   18 | F    |
|  2 | xiaozhao |   19 | M    |
+----+----------+------+------+
2 rows in set (0.01 sec)

备份指定数据库中的数据
mysqldump [OPTIONS] database [tables]

备份数据库结构和数据
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
#–databases 可以写成 -B
mysqldump -uuser1 -p123456 -B testdb2 > /data/testdb2-bak.sql

备份所有数据库 备份有物理数据的数据库
mysqldump [OPTIONS] --all-databases [OPTIONS]
#–all-databases 可以写成 -A
mysqldump -uuser1 -p123456 -A > /data/all-bak.sql

模拟实验

[root@rocky2 ~]# mysqldump -u root -p  needbk > /data/needbk_bak.sql
Enter password:
[root@rocky2 ~]# ll /data/needbk_bak.sql
-rw-r--r--. 1 root root 1959 Mar 13 03:30 /data/needbk_bak.sql

[root@rocky2 ~]# cat /data/needbk_bak.sql
-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: needbk
-- ------------------------------------------------------
-- Server version       8.0.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `b`
--

DROP TABLE IF EXISTS `b`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `b` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `b`
--

LOCK TABLES `b` WRITE;
/*!40000 ALTER TABLE `b` DISABLE KEYS */;
INSERT INTO `b` VALUES (1,'xiaoli',18,'F'),(2,'xiaozhao',19,'M');
/*!40000 ALTER TABLE `b` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-03-13  3:30:42

mysql> drop table b;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

[root@rocky2 ~]# mysql -u root -p  needbk < /data/needbk_bak.sql
Enter password:

查看 备份已恢复

xtrabackup备份

完全备份
xtrabackup -uuser1 -p123456 --backup --target-dir=/backup/base

增量备份，基于/backup/base 做增量备份
xtrabackup -uuser1 -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

cat  backup_mysql.sh
DB_HOST="备份的服务器"
MySQL_USER="mysql账户名"
MySQL_PASS="密码"
DATA_NAME="需要备份的数据库"
# 备份目录
BACKUP_DIR="/data/mysql_BACKUP"
# 当前日期
DATE=$(date +%Y%m%d)
# 完整备份文件名
FULL_BACKUP_FILE="$BACKUP_DIR/full_backup_$DATE.xbstream"
# 增量备份文件名
INCREMENTAL_BACKUP_FILE="$BACKUP_DIR/incremental_backup_$DATE.xbstream"
# 执行完整备份
xtrabackup --backup --target-dir=$FULL_BACKUP_FILE --host=$DB_HOST --user=MySQL_USER --password=MySQL_PASS --databases=DATA_NAME
# 执行增量备份（周二到周四）
if [ $(date +%u) -ge 2 ] && [ $(date +%u) -le 4 ]; then
    xtrabackup --backup --target-dir=$INCREMENTAL_BACKUP_FILE --host=$DB_HOST --user=MySQL_USER --password=MySQL_PASS --databases=DATA_NAME --incremental-basedir=$FULL_BACKUP_FILE
fi
[15:22:30root@localhost ~]# crontab -e
# 每周一和周五凌晨四点执行完整备份
0 4 * * 1,5 /root/backup_mysql.sh
                        
原文链接：https://blog.youkuaiyun.com/Luomu147/article/details/136532478

MySQL集群

服务性能扩展的两个方向

• 横向扩展： 加机器
  又称向外扩展，水平扩展，规模扩展等，常用英文 Scale Out，Scale horizontally 来表示，一般采用新增节点的方式，增加服务节点的规模来解决性能问题，比如，当一台机器不够用时，可以再增加一台机器或几台机器来分担流量和业务。
• 纵向扩展： 提升性能
  又称向上扩展，垂直扩展，性能扩展等，常用英文 Scale Up，Scale vertically 来表示，一般采用升级服务器硬件，增加资源供给，修改服务配置项等方式来解决性能问题，比如，将服务器由32G内存升级成128G内存，将服务最大并发数由128调整到256等。

主从复制

主从复制工作原理和架构

MySQL 的主从复制架构中主要有三个线程：Master 节点上的 binlog dump thread 线程，Slave 节点上的 I/O thread 线程和 SQL thread 线程。
Master 节点上会为每一个 Slave 节点上的 I/O thread 启动一个 dump thread，用来向其提供本机的二进制事件
Slave 节点上的 I/O thread 线程向 Master 节点请求该节点上的二进制事件，并将得到的内容写到当前节点上的 replay log 中
Slave 节点上的 SQL thread 实时监测 replay log 内容是否有更新，如果更新，则将该文件中的内容解析成SQL语句，还原到 Slave 节点上的数据库中去，这样来保证主从节点之间的数据同步。

复制架构

• Master > Slave 一主一从
• Master > Slaves 一主多从
• Master > Slave > Slaves 主>从>从 级联复制
• Masters > Slave 多主一从，分别从不同的主节点同步不同的数据库到从节点，要保证同一个库或表的数据只有一个来源。
• Master <> Master 双主架构互为主从，要保证不同的库或表在不同的节点上，即两个节点不要同时写一个库，双主加构一般配置为只写一个节点，这种架构的好处是可以随时将另一个节点设为主节点
• Ring 环状复制，使用较少

现有主机一主一从

主节点配置

[root@rocky2 my.cnf.d]# cat mysql-server.cnf
[mysqld]
server-id=1

#重启数据库
[root@rocky2 my.cnf.d]# systemctl restart mysqld

#创建用户
mysql>  create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

#查看主节点二进制日志
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      5476 | No        |
| binlog.000002 |       180 | No        |
| binlog.000003 |       682 | No        |
+---------------+-----------+-----------+

从节点配置

[root@rocky_8 my.cnf.d]# cat mysql-server.cnf
[mysqld]
server_id=150
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index

#查看从节点状态   还未建立连接
mysql> show slave status;
Empty set, 1 warning (0.01 sec)

#配置主从同步
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.152', MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000001',
    -> MASTER_LOG_POS=157;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

#查看从节点连接状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.0.0.152
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.152
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主节点

mysql> show processlist;
+----+-----------------+------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host             | db   | Command     | Time | State                                                           | Info             |
+----+-----------------+------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon      | 1172 | Waiting on empty queue                                          | NULL             |
| 10 | root            | localhost        | NULL | Query       |    0 | init                                                            | show processlist |
| 15 | repluser        | 10.0.0.150:38178 | NULL | Binlog Dump |   73 | Source has sent all binlog to replica; waiting for more updates | NULL             |
+----+-----------------+------------------+------+-------------+------+-----------------------------------------------------------------+------------------+

测试数据同步
master

mysql>  create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1;
Database changed
mysql> CREATE TABLE stu( id int unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL,  age  tinyint unsigned DEFAULT NULL, gender enum('M','F') DEFAULT 'M', PRIMARY KEY (id) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into stu (name,age,gender)values('user1',10,'M'), ('user2',20,'F'),('user3',30,'M');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *  from stu;
+----+-------+------+--------+
| id | name  | age  | gender |
+----+-------+------+--------+
|  1 | user1 |   10 | M      |
|  2 | user2 |   20 | F      |
|  3 | user3 |   30 | M      |
+----+-------+------+--------+
3 rows in set (0.00 sec)

slave

mysql> select *  from db1.stu;
+----+-------+------+--------+
| id | name  | age  | gender |
+----+-------+------+--------+
|  1 | user1 |   10 | M      |
|  2 | user2 |   20 | F      |
|  3 | user3 |   30 | M      |
+----+-------+------+--------+
3 rows in set (0.00 sec)