MySQL 是一个开源的关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）进行数据库的增删查改等操作，广泛应用于 Web、数据仓库、日志系统等场景
一、MySQL 基础
1 、安装 MySQL（以 CentOS 7 为例）
# 安装 MySQL 官方 Yum 仓库 yum -y install wget wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm sudo rpm -ivh mysql80-community-release-el7-1.noarch.rpm # 安装 MySQL 服务 sudo yum install mysql-server -y # 启动并设置开机自启 sudo systemctl start mysqld sudo systemctl enable mysqld
2、MySQL tar.gz 安装步骤
cd /usr/local/src wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.34-linux-glibc2.28-x86_64.tar.xz # 解压到 /usr/local 目录 tar -xf mysql-8.0.34-linux-glibc2.28-x86_64.tar.xz -C /usr/local # 重命名目录方便使用 cd /usr/local mv mysql-8.0.34-linux-glibc2.28-x86_64 mysql创建 mysql 用户和数据目录
# 创建用户 groupadd mysql useradd -r -g mysql -s /sbin/nologin mysql # 创建数据目录 mkdir -p /data/mysql chown -R mysql:mysql /data/mysql初始化数据库
cd /usr/local/mysql # 初始化并指定数据目录 bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql # 会生成临时 root 密码，记下它！ # eg: A temporary password is generated for root@localhost: Abc!defg123配置环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile source /etc/profile创建配置文件
/etc/my.cnfcat > /etc/my.cnf <<EOF [mysqld] basedir=/usr/local/mysql datadir=/data/mysql port=3306 socket=/tmp/mysql.sock pid-file=/data/mysql/mysqld.pid user=mysql symbolic-links=0 log-error=/var/log/mysqld.log [client] socket=/tmp/mysql.sock EOF启动 MySQL
# 拷贝启动脚本 cp support-files/mysql.server /etc/init.d/mysql # 启动 MySQL /etc/init.d/mysql start登录并修改 root 密码
# 登录（使用初始化时生成的临时密码） mysql -u root -p # 修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';ps -ef | grep mysqld
目录 作用 /usr/local/mysqlMySQL 程序安装目录 /data/mysql数据库存储目录 /etc/my.cnf配置文件 /tmp/mysql.sock套接字文件 /var/log/mysqld.log错误日志
netstat -tunlp | grep 3306
3、编写mysql的systemctl启动文件（包安装）
vim /etc/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf #注意路径 LimitNOFILE=5000 systemctl daemon-reload
二、数据类型
- 整数类型：
- tinyint(m) 1个字节 范围(-128~127)
- int(m) 4个字节 范围(-2147483648~2147483647)
- bigint(m) 8个字节 范围(+-9.22*10的18次方)- 浮点类型：
- float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位- 字符串类型：
- char(n) 固定长度，最多255个字符
- varchar(n) 固定长度，最多65535个字符
- longtext 可变长度，最多2的32次方-1个字符 存储评论信息
三、属性约束
约束：
PK（primary key） 表示主键约束，非空且唯一（表中只能有一个主键）
UK（unique key） 表示唯一约束
NN（not null） 表示非空约束
FK（foreign key） 表示外键约束，多表之间关联使用 约束能力
属性:（辅助表信息录入）
default 设定默认数据信息，可以实现自动填充
auto_increment 设定数值信息自增，可以实现数值编号自增填充（一般配合主键使用） comment 设定数据注释信息
unsigned 设定数值信息非负，可以实现数值信息列不能出现负数信息
补充知识：数据库外键约束说明--FK
外键也称之为外键约束: foreign key
外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.</u> 外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)
1、外键设置
#添加外键：方式一 foreign key(外键字段) references 主表(主键); 实际操作： # 创建外键关联的父表 create table class( id int primary key auto_increment, name varchar(10) not null comment "班级名字，不能为空", room varchar(10) comment '教室：允许为空' ) charset utf8; # 创建子表使用外键 create table student( id int primary key auto_increment, number char(10) not null unique comment "学号：不能重复", name varchar(10) not null comment "姓名", c_id int, foreign key(c_id) references class(id) ) charset utf8; -- 增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键） #添加外键：方式二 alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段) 实际操作： create table t_foreign( id int primary key auto_increment, c_id int )charset utf8; alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);
1.1、删除外键
# 删除外键语法格式 alter table 表名 drop foreign key 外键名; # 删除表（t_foreign）中外键信息 alter table t_foreign drop foreign key class_foreign;
1.2、查看外键
desc t_foreign; show create table t_foreign;
四、数据库操作
1 、创建/查看/删除数据库
-- 创建数据库 CREATE DATABASE testdb; -- 查看所有数据库 SHOW DATABASES; -- 使用数据库 USE testdb; -- 删除数据库 DROP DATABASE testdb; #创建新的数据库，并修改调整默认的字符编码 mysql > create database oldboy character set utf8mb4; mysql > create database oldboy charset utf8 collate utf8_general_mysql500_ci; #修改数据库服务字符集编码信息与字符编码排序规则信息 mysql > alter database test charset utf8mb4; mysql > alter database test charset utf8 collate utf8_general_mysql500_ci;
2、表操作
完整建表语句参考：
CREATE TABLE `student` ( `id` int NOT NULL COMMENT '学号信息', `name` varchar(45) NOT NULL COMMENT '学生名', `age` tinyint unsigned NOT NULL COMMENT '学生年龄', `gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学生性别', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'创建一个学生信息表：
# 切换数据库环境 mysql > use xiaoq; mysql > select database(); -- 查看是否切换数据库成功 # 创建数据表信息 mysql > create table stu1( id int(10) not null, name varchar(20) not null, age tinyint(2) NOT NULL default '0', dept varchar(16) default NULL ); # 查看数据表信息 mysql > show tables； mysql > desc stu1;-- 查看所有表 SHOW TABLES; -- 查看表结构 DESCRIBE users; -- 删除表 DROP TABLE users;
2.1修改表 alter
# 修改表名称（审计工作） mysql> rename table stu1 to stu2; mysql> alter table stu2 rename stu1; # 修改表字符编码 mysql> alter table stu1 charset utf8mb4; # 修改数据表结构信息具体实际操作过程（添加新的表结构字段） mysql > alter table stu1 add column telno char(11) not null unique key comment '手机号'; -- 在学生表中，添加新的表结构字段列（追加字段列-单列操作） mysql > alter table stu1 add column wechat varchar(64) not null unique key comment '微信号' after age; -- 在学生表中，添加新的表结构字段列（插入字段列-单列操作） mysql > alter table stu1 add column sid int not null unique key comment '微信号' first; -- 在学生表中，添加新的表结构字段列（插入首行列-单列操作） mysql > desc stu1； -- 查看表结构字段信息变化 # 具体实际操作过程（删除已有表结构字段） mysql > alter table stu1 drop column sid; -- 在学生表中，删除已有表结构字段列（删除指定字段列-单列操作） # 具体实际操作过程（修改已有表结构字段） mysql > alter table stu1 modify name varchar(64); -- 在学生表中，修改已有表结构字段列（修改表结构数据类型） mysql > alter table stu1 modify name varchar(64) not null comment '学生名'; -- 在学生表中，修改已有表结构字段列，最后带有保持原有配置的属性信息，否则其他属性信息会被还原为默认 mysql > alter table stu1 change name stuname varchar(64) not null comment '学生名'; 或者 mysql > alter table stu1 change column name stuname varchar(64) not null comment '学生名'; -- 在学生表中，修改已有表结构字段列（修改表结构字段名称） mysql > alter table stu1 modify name varchar(64) not null unique comment '学生名称'; -- 在学生表中，修改已有表结构字段列（修改表结构属性信息）了解即可 mysql > alter table stu1 drop index `name`; -- 在学生表中，修改已有表结构字段列（删除表结构属性信息）了解即可 mysql > desc stu1； -- 查看表结构字段信息变化
2.2删除表 drop
# 数据表删除命令语法 mysql > drop table <表名>； # 具体实际操作过程 mysql > drop table stu1; -- 删除操作过程，会将定义的表结构和表中数据内容一并删除 mysql > truncate table stu1; -- 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息 mysql > delete from stu1; -- 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息
2.3查询表desc
desc stu1; -- 查看表结构（常用命令） show create table stu1; -- 查看表的创建命令
3.数据操作（DML）
CREATE TABLE `xiaop`.`stu1` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键列', `name` VARCHAR(10) NOT NULL, `age` TINYINT UNSIGNED NOT NULL, `dept` ENUM('Linux', 'net', 'go') NOT NULL DEFAULT 'Linux', PRIMARY KEY (`id`));
3.1增-insert
#语法： insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]； 插入数据信息： # 具体实际操作过程 mysql> desc stu1; mysql> insert into stu1(id,name,age,dept) values(1,'xiaop',35,'net sec'); -- 插入单行信息标准方法（信息输入不要重复，且特定信息不要为空） mysql> insert into stu1(id,name,age,dept) values(0,'xiaop',35,'net sec'); mysql> insert into stu1(id,name,age,dept) values(null,'xiaop',35,'net sec'); -- 插入单行信息标准方法（自增列信息可以填入0或null，表示默认实现自增效果） mysql> insert into stu1 values(2,'oldgirl',25,'Linux'); -- 插入单行信息可以不含有表字段信息 mysql> insert into stu1 values(0,'littlegirl',2,'net'),(0,'littleboy',1,'Linux'); -- 插入多行信息可以不含有表字段信息 mysql> insert into stu1(name,age) values('xiaop',35); -- 插入当行信息可以只含部分字段信息，但是省略字段信息必须具有自增特性 或 可以为空 或有默认值输入 mysql> insert into stu1 values(0,'小彭',32,'go,linux'); -- 插入中文信息 # 检查信息是否插入成功 mysql> select * from stu1;
3.2修改-update
mysql> update 表名 set 字段=新值,… where 条件 # 具体实际操作过程 mysql> update stu1 set name="zhangsan" where id=6; -- 修改表数据内容标准方式，修改时一定要加条件信息（条件信息建议为主键或具有唯一性信息） # 检查信息是否修改成功 mysql> select * from stu1;
3.3删除-delete
# 数据表数据删除命令语法 mysql> delete from 表名 where 表达式； -- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息删除，并且按照条件删除，默认全表删除 # 具体实际操作过程 mysql> delete from stu1 where id=6; mysql> delete from stu1 where id<3; mysql> delete from stu1 where age=2 or age=1; mysql> delete from stu1; -- 删除表信息时，如果不加条件会进行逐行删除全表信息（效率比较慢） # 检查信息是否删除成功 mysql> select * from stu1; # 真实删除数据信息操作举例 mysql> delete from stu1 where id=6; # 伪删除数据信息操作举例 mysql> alter table stu1 add state tinyint not null default 1; -- 在原有表中添加新的状态列 mysql> update stu1 set state=0 where id=6; -- 将原本删除列信息的状态改为0，实现伪删除效果 mysql> select * from stu1 where state=1; -- 实现查询时不要获取状态为0的信息，即不查看获取伪删除数据信息
3.4查-select
SELECT * FROM Students; --选择Students表中的所有数据 SELECT Name, Grade FROM Students; --选择Students表中的Name和Grade数据
五、数据库索引
索引是数据库中用来提高数据读取性能（select update delete）；
提高查询数据的性能，主要是减少多IO CPU 内存的消耗；
1、索引信息创建
#单列索引创建：
主键索引：
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
辅助索引：
alter table 表名 add index idx_name(列名);
唯一索引：
ALTER TABLE 表名 ADD UNIQUE (列名);
前缀索引：
alter table 表名 add index ix_n(列名(10));
#多列索引创建
alter table 表名 add index idx_na_po(列名01,列名02);
#索引信息查看：
desc 表名;
show index from 表名;
#索引信息删除：
# 删除索引信息
alter table 表名 drop index 索引名;
-- 删除辅助索引
alter table 表名 drop index 索引名;
-- 删除联合索引
alter table 表名 drop index 列名;
-- 删除唯一索引
alter table 表名 drop primary key;
-- 删除聚簇索引
2、效果检验
#数据库压力测试方法： 一、没有索引情况： mysqlslap --defaults-file=/etc/my80.cnf --concurrency=100 --iterations=1 --create-schema='xiao' --query="select * from xiaop.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p123456 -h192.168.200.10 -verbose Running for engine rbose Average number of seconds to run all queries: 274.680 seconds Minimum number of seconds to run all queries: 274.680 seconds Maximum number of seconds to run all queries: 274.680 seconds Number of clients running queries: 100 Average number of queries per client: 20 #二、设置索引情况： alter table xiaop.t100w add PRIMARY KEY (id); alter table xiaop.t100w add index idx_k2(k2); mysqlslap --defaults-file=/etc/my80.cnf --concurrency=100 --iterations=1 --create-schema='xiao' --query="select * from xiaop.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p123456 -h192.168.200.10 -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 2.612 seconds Minimum number of seconds to run all queries: 2.612 seconds Maximum number of seconds to run all queries: 2.612 seconds Number of clients running queries: 100 Average number of queries per client: 20
六、存储引擎
1、数据库存储引擎特性（Innodb）
###大部分场景下都会使用innodb存储引擎，是因为innodb存储引擎具有一定优秀特性：
01 数据访问特性 支持多版本并发控制特性(MVCC)，支持行级锁控制并发
02 数据索引特性 支持聚簇索引/辅助索引特性，可以组织存储数据和优化查询(IOT)
03 数据事务特性 支持事务概念特性，可以实现数据的安全保证
04 数据缓冲特性 支持多缓冲区功能，自适应hash索引(AHI)
05 数据迁移特性 支持复制数据中的高级功能特性，支持数据备份恢复的热备
06 服务自愈特性 支持自动故障恢复(CR-Crash Recovery)
07 数据存储特性 支持数据双写机制(Double write) 数据存储有关的安全机制
2、存储引擎应用
#命令操作： 存储引擎设置： 永久修改默认存储引擎信息： vim /etc/my.cnf [mysqld] default_storage_engine=InnoDB #存储引擎查看： show engines; -- 查看数据库可以应用的存储引擎 select @@default_storage_engine; -- 查看默认存储引擎配置
2.1数据表存储引擎信息查看
# 查看建表语句获取存储引擎信息 mysql > show create table city; # 查看information_schema数据库获取存储引擎信息 mysql > select table_schema,table_name,engine from information_schema.tables where table_schema not in('sys','mysql','information_schema','performance_schema')
2.2存储引擎配置修改
# 创建表时设置存储引擎 mysql > create table xxx (id int) engine=innodb charset=utf8mb4; # 修改表示设置存储引擎 mysql > alter table world.xxx engine=myisam; mysql > alter table world.xxx engine=innodb;
3、存储引擎结构
3.1类型一：共享(系统)表空间
扩容共享表空间操作： 扩容前共享表空间信息查看： mysql> select @@innodb_data_file_path; +-------------------------------------+ | @@innodb_data_file_path | +-------------------------------------+ | ibdata1:12M:autoextend | +-------------------------------------+ 1 row in set (0.00 sec) -- 可以在初始安装好数据库服务后，进行修改配置为两个ibdate文件，每个共享表空间文件占用2G，总共占用4个G空间 mysql> select @@innodb_autoextend_increment; +---------------------------------------------+ | @@innodb_autoextend_increment | +---------------------------------------------+ | 64 | +---------------------------------------------+ 1 row in set (0.00 sec) -- 查看参数信息说明：ibdata1文件，默认初始大小12M，不够用会自动扩展，默认每次扩展64M
共享表空间的扩容操作方法：
# 编写数据库配置文件信息 vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend -- 需要注意的是ibdata1文件大小必须和实际数据库要存储的数据相匹配，否则会出现如下报错信息 [ERROR] [MY-012264] [InnoDB] The innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the .cnf file! -- 表示ibdate1指定大小超过了原有ibdata1实际的大小尺寸 # 查看配置信息是否生效 mysql> select @@innodb_data_file_path; +---------------------------------------------------------------------+ | @@innodb_data_file_path | +---------------------------------------------------------------------+ | ibdata1:12M;/data02/ibdata2:100M;/data03/ibdata3:100M:autoextend | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
七、事务机制
1、特性一：原子性（Atomicity）
原子性：表示一个事务生命周期中的DML语句，要么全成功要么全失败，不可以出现中间状态；
语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log
2、特性二：一致性(Consistency)
一致性：表示一个事务发生前、中、后，数据都最终保持一致，即读和写都要保证一致性；
事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障；
3、特性三：隔离性（Isolation）
隔离性：表示一个事务操作数据行的时候，不会受到其他事务的影响，主要利用锁机制来保证隔离性；
4、特性四：持久性（Durability）
持久性：表示一旦事务进行了提交，即可永久生效（落盘）
保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log
可以在数据库服务异常时，将内存中未落盘的数据进行恢复
5、存储事务生命周期
begin DML;DML;DML;DML commit -- 完整生命周期 begin DML;DML;DML;DML rollback -- 完整生命周期 #在运用事务机制完成相关工作任务时，对于事务使用是存在生命周期概念的，标准显示的事务生命周期控制语句有: # 开启事务机制 begin; start transaction; # 提交事务任务 commit; # 回滚事务操作 rollback;事务生命周期中，只能使用DML语句，其中包括：select、update、delete、insert；DDL语句会隐式进行提交
5.1生命周期操作演示：
# 进行测试数据库查询数据 mysql> use world; mysql> select * from city limit 10; # 进行测试数据库数据撤销修改 mysql> begin; mysql> update city set population=10 where id=1; mysql> update city set population=10 where id=2; -- 由于是采用事务进行的修改，所以只是在内存层面进行的修改，并没有对磁盘上的数据进行修改； mysql> select * from city limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 10 | | 2 | Qandahar | AFG | Qandahar | 10 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ -- 由于是采用事务进行的修改，此时看到的数据信息只是内存层面的修改信息 mysql> rollback; -- 由于是采用事务进行的撤销，会读取undo文件信息，将事务操作撤回到事务开始前的状态 mysql> select * from city limit 10; -- 由于是采用事务进行的修改，当撤销操作执行完，看到数据信息还是原来的； +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ # 进行测试数据库数据永久修改 mysql> begin; mysql> update city set population=10 where id=1; mysql> update city set population=10 where id=2; -- 由于是采用事务进行的修改，所以只是在内存层面进行的修改，并没有对磁盘上的数据进行修改； mysql> select * from city limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 10 | | 2 | Qandahar | AFG | Qandahar | 10 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ -- 由于是采用事务进行的修改，此时看到的数据信息只是内存层面的修改信息 mysql> commit; -- 由于是采用事务进行的提交，会加载redo文件信息，将事务内存层面的修改同步到磁盘中（完成了D特性） mysql> select * from city limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 10 | | 2 | Qandahar | AFG | Qandahar | 10 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ -- 由于是采用事务进行的修改，当执行操作执行完，看到数据信息将永久保存下载；
5.2存储事务提交方式
方式一 ：
在事务生命周期管理过程中，事务的提交机制可以采用自动提交方式（auto_commit）
---事务自动提交方式作用说明：
事务自动提交表示在没有显示的使用begin语句的时候，执行DML操作语句时，会在DML操作语句前自动添加begin； 并在DML操作语句执行后自动添加commit； 在生产环境中，若处于频繁事务业务场景中，建议关闭autocommit自动提交功能，或者每次事务执行的时候； 都进行显示的执行begin和commit--事务自动提交方式参数信息:
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ -- 在事务自动提交功能设置修改时，设置为1表示开启自动提交，设置为0表示关闭自动提交--事务自动提交方式参数修改：
# 临时关闭事务自动提交功能 mysql> set global autocommit=0; -- 配置调整后，重新登录mysql数据库生效 # 永久关闭事务自动提交功能 [root@xiaoQ-01 ~]# vim /etc/my.cnf [mysqld] autocommit=0 -- 配置调整后，重新启动mysql数据库生效方式二 ：
在事务生命周期管理过程中，事务的提交机制可以采用隐式提交方式：
在进行事务操作时，需要注意操作语句必须都是DML语句，如果中间插入了DDL语句，也会造成之前的事务操作自动提交；
begin; DML1; DML2; DDL1; COMMIT; DML3; COMMIT; -- 这种情况出现会破坏原本事务的原子性
序号 语句类型 涉及命令 01 DDL语句类型 alter、create、drop 02 DCL语句类型 grant、revoke、set password 03 锁定语句类型 lock tables、unlock tables 04 其他语句类型 truncate table、load data infile、select for update
6、事务隔离级别类型:
类型一：RU（READ-UNCOMMITTED 表示读未提交）
可以读取到事务未提交的数据，隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题;
类型二：RC（READ-COMMITTED 表示读已提交）
可用可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题；
类型三：RR（REPEATABLE-READ 表示可重复读）
默认可以防止脏读（当前内存读），防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差；
会使用next lock锁进制，来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能；
类型四：SR（SERIALIZABLE 可串行化）
隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了；
这是事务的最高级别，在每条读的数据上，加上锁，使之不可能相互冲
2、脏读
脏读主要表示在一个事务窗口中，没有数据修改提交操作前，另一个事务就可以看到内存中数据页的修改；
简单理解：在一个事务窗口中，可以读取到别人没有提交的数据信息；
利用隔离级别RU解读：
# 设置事务隔离级别 mysql> set global transaction_isolation='READ-UNCOMMITTED'; mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED | +-------------------------+ -- 重新开启两个SQL会话窗口 # 数据库A会话窗口操作 mysql> begin; mysql> update t1 set a=10 where id=1; -- 只是在内存层面进行数据页中数据修改 mysql> rollback; -- 进行事务回滚操作 # 数据库B会话窗口操作 mysql> begin; mysql> select * from t1 where id=1; +----+----+---+----+ | id | a | b | c | +----+----+---+----+ | 1 | 10 | a | aa| +----+----+---+----+ 1 row in set (0.01 sec) -- 在A会话窗口没提交的事务修改，被B会话窗口查询到了 mysql> select * from t1 where id=1; +----+----+---+----+ | id | a | b | c | +----+----+---+----+ | 1 | 5 | a | aa | +----+----+---+----+ 1 row in set (0.01 sec) -- 在A会话窗口进行回滚后，在B窗口查询的数据又恢复了
3、不可重复读
不可重复读表示在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的；
利用隔离级别RU解读：
# 数据库B会话窗口操作 mysql> begin; mysql> select * from t1 where id=1; +----+----+---+----+ | id | a | b | c | +----+----+---+----+ | 1 | 10 | a | aa| +----+----+---+----+ 1 row in set (0.01 sec) -- 在B会话事务窗口进行数据第一次查询看到数据信息：a=10 mysql> select * from t1 where id=1; +----+----+---+----+ | id | a | b | c | +----+----+---+----+ | 1 | 5 | a | aa | +----+----+---+----+ 1 row in set (0.01 sec) -- 在B会话事务窗口进行数据第二次查询看到数据信息：a=5利用隔离级别RC解读
# 设置事务隔离级别 mysql> set global transaction_isolation='READ-COMMITTED'; mysql> select @@transaction_isolation; +---------------------------------+ | @@transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ mysql> set global autocommit=0; mysql> select @@autocommit; -- 重新开启两个SQL会话窗口 # 数据库A会话窗口操作 mysql> use xiaop; mysql> begin; mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 5 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- A窗口事务查询信息 = B窗口事务查询信息 mysql> update t1 set a=10 where id=1; -- A窗口事务进行修改 mysql> commit; -- A窗口事务进行提交 # 数据库B会话窗口操作 mysql> use xiaop; mysql> begin; mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 5 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- A窗口事务查询信息 = B窗口事务查询信息 mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 5 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题； mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 10 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- A窗口事务提交之后，B窗口事务查询信息和之前不同了利用隔离级别RR解读：
# 设置事务隔离级别 mysql> set global transaction_isolation='REPEATABLE-READ'; mysql> select @@transaction_isolation; +---------------------------------+ | @@transaction_isolation | +---------------------------------+ | REPEATABLE-READ | +---------------------------------+ mysql> set global autocommit=0; mysql> select @@autocommit; -- 重新开启两个SQL会话窗口 # 数据库A会话窗口操作 mysql> use xiaop; mysql> begin; mysql> select * from t1; -- 确认初始数据信息 mysql> update t1 set a=10 where id=1; -- A窗口事务进行修改 mysql> commit; -- A窗口事务进行提交 # 数据库B会话窗口操作 mysql> use xiaop; mysql> begin; mysql> select * from t1; -- 确认初始数据信息 mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 5 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题； mysql> select * from t1 where id=1; +----+---+---+----+ | id | a | b | c | +----+---+---+----+ | 1 | 5 | a | aa | +----+---+---+----+ 1 row in set (0.00 sec) -- A窗口事务提交之后，B窗口事务查询信息和之前是相同的； -- 在RR级别状态下，同一窗口的事务生命周期下，每次读取相同数据信息是一样，避免了不可重复读问题 mysql> commit; mysql> select * from t1 where id=1; -- 在RR级别状态下，同一窗口的事务生命周期结束后，看到的数据信息就是修改的了
4、幻读
利用隔离级别RC解读：
# 设置事务隔离级别 mysql> set global transaction_isolation='READ-COMMITTED'; mysql> select @@transaction_isolation; +---------------------------------+ | @@transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ mysql> set global autocommit=0; mysql> select @@autocommit; -- 重新开启两个SQL会话窗口 # 数据库A会话窗口操作（重新进入） mysql> use xiaop; mysql> select * from t1; +----+----+---+-----+ | id | a | b | c | +----+----+---+-----+ | 1 | 10 | a | aa | | 2 | 7 | c | ab | | 3 | 10 | d | ae | | 4 | 13 | g | ag | | 5 | 14 | h | at | | 6 | 16 | i | au | | 7 | 20 | j | av | | 8 | 22 | k | aw | | 9 | 25 | l | ax | | 10 | 27 | o | ay | | 11 | 31 | p | az | | 12 | 50 | x | aze | | 13 | 60 | y | azb | +----+----+---+-----+ 13 rows in set (0.00 sec) -- 查看获取A窗口表中数据 mysql> alter table t1 add index idx(a); -- 在A窗口中，添加t1表的a列为索引信息 mysql> begin; -- 在A窗口和B窗口中，同时做开始事务操作； mysql> update t1 set a=20 where a<20; -- 在A窗口中，将a<20的信息均调整为20 mysql> commit; -- 在A窗口中，进行事务提交操作，是在B窗口事务没有提交前 mysql> mysql> select * from t1; -- 在A窗口中，查看数据信息，希望看到的a是没有小于20的，但是结果看到了a存在等于10的（即出现了幻读） # 数据库B会话窗口操作（重新进入） mysql> use xiaop; mysql> select * from t1; +----+----+---+-----+ | id | a | b | c | +----+----+---+-----+ | 1 | 10 | a | aa | | 2 | 7 | c | ab | | 3 | 10 | d | ae | | 4 | 13 | g | ag | | 5 | 14 | h | at | | 6 | 16 | i | au | | 7 | 20 | j | av | | 8 | 22 | k | aw | | 9 | 25 | l | ax | | 10 | 27 | o | ay | | 11 | 31 | p | az | | 12 | 50 | x | aze | | 13 | 60 | y | azb | +----+----+---+-----+ 13 rows in set (0.00 sec) -- 查看获取B窗口表中数据 mysql> begin; mysql> insert into t1(a,b,c) values(10,'A','B') -- 在B窗口中，插入一条新的数据信息 a=10 mysql> commit; -- 在B窗口中，进行事务提交操作利用隔离级别RR解读：
# 设置事务隔离级别 mysql> set global transaction_isolation='REPEATABLE-READ'; mysql> select @@transaction_isolation; +---------------------------------+ | @@transaction_isolation | +---------------------------------+ | REPEATABLE-READ | +---------------------------------+ mysql> set global autocommit=0; mysql> select @@autocommit; -- 重新开启两个SQL会话窗口 # 数据库A会话窗口操作 mysql> use xiaop; mysql> select * from t1; -- 查看获取A窗口表中数据 mysql> alter table t1 add index idx(a); -- 在A窗口中，添加t1表的a列为索引信息 mysql> begin; mysql> update t1 set a=20 where a>20; -- 在A窗口中，将a>20的信息均调整为20 # 数据库B会话窗口操作 mysql> use xiaop; mysql> select * from t1; -- 查看获取B窗口表中数据 mysql> begin; mysql> insert into t1(a,b,c) values(30,'sss','bbb'); -- 在B窗口中，插入一条新的数据信息 a=30，但是语句执行时会被阻塞，没有反应； mysql> show processlist; -- 在C窗口中，查看数据库连接会话信息，insert语句在执行，等待语句超时（默认超时时间是50s） -- 因为此时在RR机制下，创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock -- 区域间隙锁 < 左闭右开(可用临界值) ; 区域间隙锁 > 左开右闭（不可用临界值）
5、隔离性与隔离级别
提到事务，肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）
我们就来说说其中 I，也就是“隔离性”。
当数据库上有多个事务同时执行的时候，就可能出现以下问题：
脏读（dirty read）
不可重复读（non-repeatable read）
幻读（phantom read）
为了解决这些问题，就有了“隔离级别”的概念。在谈隔离级别之前，首先要知道，隔离得越严实，效率就会越低。
因此很多时候，都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：
隔离级别 英文描述 解释说明 读未提交 RU-read uncommitted 一个事务还没提交时，它做的变更就能被别的事务看到。 读提交 RC-read committed 一个事务提交之后，它做的变更才会被其他事务看到。 可重复读 RR-repeatable read 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。 当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 串行化 serializable 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
八、服务日志
在MySQL数据库服务中，有4种不同的日志是最常用的日志类型，这些日志记录这数据库在不同方面的踪迹；
mysql> show variables like '%log%';
1、常用日志信息介绍
序号 日志名称 解释说明 01 general_log 表示查询日志（通用日志），默认日志状态处于关闭，可以进行在线调整配置 作用：记录了客户端从会话连接开始，执行过的所有SQL语句信息； 02 log_error 表示错误日志（运行日志），默认日志状态处于激活 作用：记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息； 03 log_bin 表示二进制日志（binlog日志），默认日志状态处于激活（8.0之后） 作用：记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；语句以事件的形式保存，描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用。 04 slow_query_log 表示慢查询日志，记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小于min_examined_row_limit的所有SQL语句的日志。
2、服务日志信息配置
2.1通用日志（general_log）
general_log=OFF -- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开； general_log_file=/data/3306/data/xiaoQ-01.log -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离； # 修改日志默认状态（激活日志）： mysql > set global general_log=1;
2.2错误日志（log_error）
log_error=./xiaoQ-01.edu.err -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离； # 修改日志存储路径（永久配置）： [root@xiaoq ~]# vim /etc/my.cnf log_error=/tmp/mysql3306.err -- 配置文件编写完毕后，需要重启数据库服务生效 # 模拟故障日志应用 [root@db02 ~]# ll /data/3306/data/ibdata1 -rw-r-----. 1 mysql mysql 12582912 Apr 21 14:19 /data/3306/data/ibdata1 [root@db02 ~]# chmod 000 /data/3306/data/ibdata1 [root@db02 ~]# /etc/init.d/mysqld restart Shutting down MySQL... SUCCESS! Starting MySQL. ERROR! The server quit without updating PID file (/data/3306/data/db02.pid). #查看错误日志 [root@db02 data]#tail -20 /data/3306/data/db02.err 2025-04-21T06:35:53.637782Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-04-21T06:35:53.638022Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable 2025-04-21T06:35:53.638131Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable 2025-04-21T06:35:53.638241Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine 2025-04-21T06:35:53.638584Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2025-04-21T06:35:53.638829Z 0 [ERROR] [MY-010119] [Server] Aborting 2025-04-21T06:35:53.640117Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL -- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；
2.3二进制日志（log_bin）
在进行增量恢复数据时，需要先了解什么是binlog日志，此日志文件其实就是用于记录对数据库进行操作更改的语句信息的；
并且记录更改的语句信息以事件形式进行记录，但是需要注意的是查询相关的语句是不会被记录的，比如：select、show；
然而作为所有对数据库的改操作事件信息都会被记录，比如：insert、update、create、drop。。。
2.3.1查看数据库binlog日志配置参数
mysql> show variables like '%log_bin%'; Connection id: 8 Current database: xiaop +---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------+ | log_bin | ON | | log_bin_basename | /data/3306/data/binlog | | log_bin_index | /data/3306/data/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------+ 6 rows in set (0.00 sec) --- 通过以上输出信息可以看到log_bin为ON状态，表示binlog日志功能开启 # 已开启binlog日志功能后，查看系统binlog功能配置参数状态 mysql> show variables like '%log_bin%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | log_bin | ON | | sql_log_bin | ON | +-----------------------------------------+-------+ 3 rows in set (0.00 sec) --- 通过以上输出信息可以看到log_bin为on状态，表示binlog日志功能已经开启日志信息基本配置
server_id=6 -- 进行主从操作时，需要进行此信息配置； log_bin=ON -- 默认日志功能处于关闭状态 log_bin_basename=/data/3306/data/binlog -- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离； # 配置信息简写方式：开启数据库binlog日志记录功能 [root@xiaoq ~]# vim /etc/my.cnf -- 激活binlog日志记录功能，需要对数据库服务配置文件进行编辑修改 [mysqld] server_id=6 log_bin=/data/3306/binlog/mysql-bin -- 进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效； chown -R mysql:mysql /data/3306/binlog/mysql-bin/ 或者 log_bin=binlog -- 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中； # 配置文件修改后需要重启数据库服务，加载配置文件改动的信息： [root@xiaoQ ~]# /etc/init.d/mysqld restart [root@xiaoQ xiaoQ]# ll -h /data/3306/data/binlog* -rw-rw----. 1 mysql mysql 245 6月 24 02:19 /data/3306/data/binlog.00000N -rw-rw----. 1 mysql mysql 16 6月 24 02:19 /data/3306/data/binlog.index -- 数据库服务重启后，已经可以在数据库的数据存储目录中，看到binlog日志文件的踪影
2.3.2日志信息查看方法：
可以通过查看方式，获取binlog日志里的数据信息，一般在数据库启动时，日志记录功能就开启了；
可以利用日志中记录信息，将数据库服务的数据信息恢复到指定的时间点，同时也可以支持主从数据复制（在其它机器上回放日志）；
查看方式一：确认数据库binlog日志数量
mysql> show binary logs; +------------------+-------------+--------------+ | Log_name | File_size | Encrypted | +------------------+-------------+--------------+ | binlog.000001 | 156 | No | +------------------+-------------+--------------+ -- 获取数据库服务运行过程中，使用的binlog日志的情况 mysql> flush logs; Query OK, 0 rows affected (0.12 sec) -- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能； mysql> show binary logs; +------------------+-------------+--------------+ | Log_name | File_size | Encrypted | +------------------+-------------+--------------+ | binlog.000001 | 200 | No | | binlog.000002 | 156 | No | +------------------+-------------+--------------+ 2 rows in set (0.00 sec)查看方式二：确认数据库binlog日志状态
mysql> create database test_binlog; Query OK, 1 row affected (0.03 sec) -- 模拟数据服务有修改操作 mysql> select * from world.city limit 1; Query OK, 1 row affected (0.03 sec) -- 模拟数据服务有修改操作 mysql> show binary logs; +------------------+-------------+--------------+ | Log_name | File_size | Encrypted | +------------------+-------------+--------------+ | binlog.000001 | 200 | No | | binlog.000002 | 362 | No | +------------------+-------------+--------------+ 2 rows in set (0.00 sec) -- 可以看到binlog日志的存储量发生了变化，但是在做查询操作时，binlog日志的存储量并未发生变化 mysql> show master status; +------------------+------------+------------------+-----------------------+------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set +------------------+------------+------------------+-----------------------+------------------- | binlog.000002 | 362 | | | | +------------------+------------+------------------+-----------------------+------------------- 1 row in set (0.00 sec) -- 查看获取当前使用的binlog日志情况，以及产生的日志量字节大小；查看方式三：查看数据库binlog日志信息
mysql> show binlog events in 'binlog.000002'; -- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息 -- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息） -- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；
列号 列信息 解释说明 01 Log_name 表示指定查看的binlog日志文件名称信息 02 Pos 表示binlog日志事件开始的位置点，用于截取二进制日志信息标识 05 End_log_pos 表示binlog日志事件结束的位置点，用于截取二进制日志信息标识 06 Info 表示binlog中具体的事件内容信息
查看方式四：筛选数据库binlog日志事件
# 模拟生成binlog日志事件信息 mysql> source ~/world.sql; mysql> drop database world; mysql> source ~/world.sql; # 获取删除数据库的事件信息： # 筛选数据库日志方式一： [root@xiaoq data]# mysql -e "show binlog events in 'binlog.000002'"|grep "drop database" binlog.000002 722789 Query 1 722896 drop database world /* xid=5363 */ -- 获取指定事件信息产生的起点位置和终点位置信息； # 筛选数据库日志方式二： mysql> pager less -- 在数据库中定义pager功能，数据库连接会话退出即失效； mysql> show binlog events in 'binlog.000002'; -- 此时查看日志事件信息具有了翻页功能 /drop database | binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */ mysql> pager grep "drop database" PAGER set to 'grep "drop database"' -- 表示开启数据库pager的过滤功能 mysql> show binlog events in 'binlog.000002'; | binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */ -- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
2.3.3获取数据库binlog日志记录信息异常
进行数据库服务数据信息更改操作，随后查看binlog日志信息的变化：
# 进行数据库创建操作 mysql> create database xiaop; mysql> show databases; # 查看获取binlog日志记录信息 [root@xiaoQ ~]#mysqlbinlog /data/3306/binlog/mysql-bin.000001 mysqlbinlog: unknown variable 'default-character-set=utf8mb4' -- 由于在数据库在客户端配置文件中添加了default-character-set=utf8mb4字符编码信息，因此造成无法查看binlog [root@xiaoQ ~]# cat /etc/my.cnf.d/client.cnf [client] #default-character-set=utf8mb4 [client-mariadb] #default-character-set=utf8mb4 -- 可以临时调整先将客户端的字符编码配置信息注释， [root@xiaoQ ~]# mysqlbinlog /var/lib/mysql/binlog.000001 ... 省略部分信息 ... # at 494 #220624 2:35:02 server id 1 end_log_pos 579 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1656009302/*!*/; create database xiaoQ /*!*/; ... 省略部分信息 ... -- 在binlog日志文件中，已经记录了之前的创建xiaoQ的更改操作记录信息
九、GTID概念介绍
GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性；
1、server_uuid信息查看
mysql> select @@server_uuid +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | e0147cef-0953-11f0-858f-000c2966eb77 | +--------------------------------------+ 1 row in set (0.00 sec) -- 表示数据库每次初始化之后自动生成，不建议手工进行修改； [root@db02 ~]# cat /data/3306/data/auto.cnf [auto] server-uuid=e0147cef-0953-11f0-858f-000c2966eb77 -- 在数据库的数据目录文件中也可以查询到
GTID功能作用：
利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；
利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；
简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；
GTID功能配置：
# GTID功能参数信息介绍（3个重要的配置参数） mysql> select @@gtid_mode; +-------------------+ | @@gtid_mode | +-------------------+ | OFF | +-------------------+ 1 row in set (0.00 sec) -- 设置是否开启显示gtid信息功能（在5.7之后是有个匿名的gtid，是数据库系统自己维护的） mysql> select @@enforce_gtid_consistency; +-------------------------------------+ | @@enforce_gtid_consistency | +-------------------------------------+ | OFF | +-------------------------------------+ 1 row in set (0.00 sec) -- 设置是否开启GTID强制一致性功能 -- 对某些 SQL 会有限制，例如 CREATE TABLE … SELECT 必须得分成两条语句执行。 -- OFF： 表示事务允许违反 GTID 一致性。 -- ON： 表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常。 -- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG。 mysql> select @@log_slave_updates; +----------------------------+ | @@log_slave_updates | +----------------------------+ | 1 | +----------------------------+ 1 row in set, 1 warning (0.01 sec) -- 和配置主从有关（在8.0.26开始 推荐配置log_replica_updates替代log_slave_updates参数） -- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中GTID功能相关参数激活：
[root@xiaoq ~]# vim /etc/my.cnf [mysqld] gtid_mode=on enforce_gtid_consistency=1 log_slave_updates=on -- 配置文件信息修改完毕后，重启数据库服务使配置生效GTID信息查看：
mysql> show master status; +------------------+-----------+-------------------+-----------------------+------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set +------------------+-----------+-------------------+-----------------------+------------------- | binlog.000004 | 156 | | | +------------------+-----------+-------------------+-----------------------+------------------- 1 row in set (0.03 sec) -- 在GTID功能被激活后，就会在Executed_Gtid_Set列中显示GTID集合信息； mysql> create database test3; Query OK, 1 row affected (0.08 sec) -- 模拟创建数据库，产生新的事件信息 mysql> show master status; +------------------+----------+--------------+------------------+------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------ | binlog.000004 | 344 | | | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1 | +------------------+----------+--------------+------------------+------------------------------ 1 row in set (0.01 sec) -- GTID信息随着新的事件产生，随之发生变化 mysql> create database test4; Query OK, 1 row affected (0.03 sec) -- 模拟创建数据库，产生新的事件信息 mysql> show master status; +---------------+----------+--------------+------------------+--------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+--------------------------------- | binlog.000004 | 532 | | | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1-2 | +---------------+----------+--------------+------------------+--------------------------------- 1 row in set (0.00 sec) -- GTID信息随着新的事件产生，随之发生变化 mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+-------------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info +------------------+-----+----------------+-----------+-------------+-------------------------- | mysql-bin.000002 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 | | mysql-bin.000002 | 125 | Previous_gtids | 6 | 156 | | mysql-bin.000002 | 156 | Gtid | 6 | 233 | SET @@SESSION.GTID_NEXT= 'e0147cef-0953-11f0-858f-000c2966eb77:1' | | mysql-bin.000002 | 233 | Query | 6 | 344 | create database twst3 /* xid=4 */ | | mysql-bin.000002 | 344 | Gtid | 6 | 421 | SET @@SESSION.GTID_NEXT= 'e0147cef-0953-11f0-858f-000c2966eb77:2' | | mysql-bin.000002 | 421 | Query | 6 | 532 | create database test4 /* xid=6 */ | +------------------+-----+----------------+-----------+-------------+-------------------------- 6 rows in set (0.00 sec) -- 在每个数据库操作事件之前，会显示GTID的唯一标识信息
十、慢日志（slow_log)
慢日志主要是用于以文本形式记录数据库服务运行过程中，执行过程较慢的语句；
利用慢日志信息生成的信息，可以在日常巡检过程中，通过日志定位SQL语句性能问题
1、日志信息基本配置
mysql> select @@slow_query_log; +-------------------------+ | @@slow_query_log | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) -- 此参数配置信息，表示是否激活启动慢日志记录功能，默认处于关闭状态 mysql> select @@slow_query_log_file; +--------------------------------------------+ | @@slow_query_log_file | +--------------------------------------------+ | /data/3306/data/xiaoQ-01-slow.log | +--------------------------------------------+ 1 row in set (0.00 sec) -- 此参数配置信息，表示慢日志文件保存的路径信息；建议日志文件路径与数据存放路径进行分离； mysql> select @@long_query_time; +---------------------------+ | @@long_query_time | +---------------------------+ | 10.000000 | +---------------------------+ 1 row in set (0.00 sec) -- 此参数信息配置，表示记录慢日志的条件，默认是大于10s执行的语句，就会记录为慢查询语句；(建议时间为0.01~0.1) mysql> select @@log_queries_not_using_indexes; +---------------------------------------------+ | @@log_queries_not_using_indexes | +---------------------------------------------+ | 0 | +---------------------------------------------+ 1 row in set (0.00 sec) -- 此参数信息配置，表示慢日志中会记录没有使用索引的语句信息； # 修改日志默认状态（激活日志）： mysql> set global slow_query_log=1; mysql> set global long_query_time=0.01; mysql> set global log_queries_not_using_indexes=1; -- 可以对以上参数信息进行在线调整，也可以将以上参数编写到数据库my.cnf配置文件中，作为永久配置；
2、日志应用配置核实
mysql> use xiaop; mysql> show index from t100w; mysql> alter table t100w drop index idx; -- 删除数据表中索引信息 mysql> select * from t100w limit 100; mysql> select * from t100w where id=10; mysql> select * from t100w where id=20; mysql> select count(*) from t100w group by num limit 10; ... -- 模拟执行慢查询的操作语句 # 查看核实慢日志文件是否生成 [root@xiaoQ-01 ~]# ll /data/3306/data/xiaoQ-01-slow.log -rw-r----- 1 mysql mysql 6842 11月 22 23:54 /data/3306/data/xiaoQ-01-slow.log [root@xiaoQ-01 ~]# cat /data/3306/data/xiaoQ-01-slow.log /usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 2022-11-22T15:41:03.849261Z # User@Host: root[root] @ localhost [] Id: 490 # Query_time: 0.000446 Lock_time: 0.000143 Rows_sent: 100 Rows_examined: 100 use xiaop; SET timestamp=1669131663; select * from t100w limit 100; # Time: 2022-11-22T15:41:05.677310Z # User@Host: root[root] @ localhost [] Id: 490 # Query_time: 0.000282 Lock_time: 0.000083 Rows_sent: 100 Rows_examined: 100 SET timestamp=1669131665; select * from t100w limit 100; # Time: 2022-11-22T15:41:06.630012Z # User@Host: root[root] @ localhost [] Id: 490 # Query_time: 0.000242 Lock_time: 0.000075 Rows_sent: 100 Rows_examined: 100 SET timestamp=1669131666; select * from t100w limit 100; -- 会按照执行语句的操作时间顺序，进行慢查询日志信息的记录；
3、日志信息分析方法
[root@xiaoQ-01 data]# mysqldumpslow -s c -t 3 /data/3306/data/xiaoQ-01-slow.log -- 按照慢查询语句的重复执行次数(c)进行排序(-s)，取出其中靠前(t)的前三名慢查询语句 -- 还可以扩展使用pt-query-digest更好的分析慢查询日志，支持图形化展示 -- what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time
十一、数据库服务备份恢复
1、恢复方式
数据库服务备份数据方式：
在企业中实现数据库服务数据备份的方式主要有两种方式：
① 物理方式
采用拷贝物理文件数据进行备份的方式，数据库服务物理数据文件存放路径是：/var/lib/mysql
实现方式：
可以在某个特定时间点停机或停止业务访问，然后利用cp和tar命令将物理数据文件备份或打包；
可以在任意时间节点在不停机不停止业务时，然后利用专业的xtrabackup(Percona Xtrabackup)热备工具进行数据库数据备份；
应用场景：
当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）；
② 逻辑方式
可以采用以SQL语句形式把数据库的数据导出保存备份为数据库文件（xxx.sql），文件中会含有大量SQL语句信息；
实现方式：
可以在任意时间节点在不停机不停止业务时，然后利用专业的mysqldump(MDP)逻辑备份工具进行数据备份；
可以在任意时间节点在不停机不停止业务时，然后利用二进制日志binlog文件实现逻辑备份数据操作；
可以在任意时间节点在不停机不停止业务时，然后利用主从数据库架构实现备份数据信息；
应用场景：
当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）；
02 数据库服务备份恢复职责：
设计数据库备份策略：备份数据周期、选择的备份工具、应用的备份方式（全备 增量..）；
定期数据库备份检查：核实是否存在、确认备份文件大小；
安排数据库恢复演练：真实确认备份的数据，是否能够准确的做数据恢复；
真实数据库恢复能力：在数据库服务出现异常情况时，可以将数据库服务修复，并恢复丢失的数据信息；
关于数据库迁移升级：可以采用Mergeing方式（主从架构）、可以单独备份数据信息到新的数据库节点做恢复（逻辑导出）；
2、逻辑备份实践
工具命令使用语法：
[root@db02 ~]# mysqldump -u数据库用户 -p数据库密码 [备份参数] > /路径信息/数据库备份文件.sql -- 在执行mysqldump命令时，也会用到数据库连接登录的基础参数：-u -p -S -h -P
序号 参数信息 官方说明 解释说明 01 -A Dump all the databases 表示备份所有库中数据信息 02 -B Dump several databases. 表示备份指定库中数据信息 03 -F Flush logs file in server before starting dump 表示在备份启动前自动刷新日志文件
3、数据库备份恢复练习环境准备：
[root@db02 ~]# mkdir -p /database_backup
3.1、全库备份操作 -A
#我这个没有密码 [root@db02 ~]# mysqldump -uroot -p -A >/database_backup/all_database.sql [root@db02 ~]# ll -h /database_backup/all_database.sql -rw-r--r--. 1 root root 1.5M Apr 30 16:05 /database_backup/all_database.sql -- 利用mysqldump命令备份的数据文件是纯文本文件，是可以进行查看或过滤的
3.2、单个数据库进行备份（-B）
# 进行数据库单库备份操作 [root@db02 ~]# mysqldump -uroot -p -B xiaop > /database_backup/xiaop.sql [root@db02 ~]# ll -h /database_backup/xiaop.sql -rw-r--r--. 1 root root 1.7K Apr 30 16:08 /database_backup/xiaop.sql # 过滤部分内容后查看备份数据库文件信息： [root@db02 ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/xiaop.sql SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'e0147cef-0953-11f0-858f-000c2966eb77:1-15'; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xiaop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `xiaop`; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;以上指定数据库备份完毕后，可以模拟删除相应数据，利用备份的数据库文件进行数据库恢复操作：
mysql> use xiaop mysql> show tables; +------------------------+ | Tables_in_xiaop | +------------------------+ | stu1 | +------------------------+ 1 row in set (0.00 sec) mysql> drop table stu2; mysql> show tables; Empty set (0.00 sec) -- 模拟删除数据库中数据表信息，造成数据库中数据损坏 # 进行数据库数据复原恢复操作： # 方式一：在数据库系统中加载数据库备份文件 mysql> source /database_backup/xaiop.sql; # 方式二：在操作系统命令行执行数据恢复命令 [root@db02 ~]# mysql -uroot -p123456 xiaop </database_backup/xiaop.sql # 数据信息恢复完毕后检查数据库情况 mysql> show tables; +------------------------+ | Tables_in_xiaop | +------------------------+ | stu1 | +------------------------+ 1 row in set (0.00 sec) mysql> select * from stu1; +----+-------+-----+------+ | id | name | age | dept | +----+-------+-----+------+ | 1 | xiaop | 22 | nan | +----+-------+-----+------+ 1 row in set (0.01 sec) 2 rows in set (0.00 sec)
3.3、多个数据库进行备份（-B）
[root@db02 ~]# mysqldump -uroot -p123456 -B xiaop world >/database_backup/xiaop_world.sql # 过滤部分内容后查看备份数据库文件信息： [root@db02 ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/xiaop_world.sql
3.4、数据表部分备份操作练习实践命令
将数据库中单个数据表进行备份
# 备份指定数据库中的单个数据表： [root@db02 ~]# mysqldump -uroot -p xiaop stu1 >/databases_backup/xiaop_tables_stu1.sql # 恢复指定数据库中的单个数据表： [root@db02 ~]# mysql -uroot -p xiaop </databases_backup/xiaop_tables_stu1.sql
将数据库中多个数据表进行备份
# 备份指定数据库中的多个数据表： [root@db02 ~]# mysqldump -uroot -p123456 world city country >/database_backup/world_tables_city_country.sql # 恢复指定数据库中的多个数据表： [root@db02 ~]# mysql -uroot -p world </database_backup/world_tables_city_country.sql
4、Xtrabackup数据备份方式01：实现全量备份
# 全量备份操作： [root@db02-01 ~]# mkdir /data/backup/full -p -- 进行物理备份的目标目录不能存在数据信息，需要指定一个空目录进行备份 [root@db02-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --target-dir=/data/backup/full 或者使用参数--datadir替换掉参数--defaults-file [root@db02-01 ~]# xtrabackup --datadir=/data/3306/data --user=root --password=123456 --port=3306 --backup --target-dir=/data/backup/full -- backup参数信息表示进行全备操作 # 物理备份命令执行输出信息说明： 221127 02:46:11 >> log scanned up to (277574297) -- 记录日志位置点信息，表示进行拷贝数据的checkpoint的SN号码，相当于磁盘当前数据页的SN号码； Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. 221127 02:46:11 [01] Copying ./ibdata1 to /data/backup/full/ibdata1 221127 02:46:11 [01] ...done 221127 02:46:11 [01] Copying ./sys/sys_config.ibd to /data/backup/full/sys/sys_config.ibd 221127 02:46:11 [01] ...done 221127 02:46:11 [01] Copying ./mysql.ibd to /data/backup/full/mysql.ibd ... 省略部分信息... -- 进行相关数据文件、日志文件、共享表空间文件、数据表空间文件等文件的拷贝，并且拷贝过程是不会进行锁表操作的； -- 数据表空间信息备份完毕后，会有提示字段信息，并且此时会锁定binlog日志文件，并将binlog日志文件复制到备份目录； -- 在进行binlog日志文件备份时，会生成xtrabackup_binlog_info文件信息，用于记录物理备份后的二进制日志位置点； -- 二进制日志文件备份完毕后，会释放binlog日志文件锁定状态，并再次检查checkpoint的SN号码，确认redo日志是否变化； [root@db02-01 backup]# ll /data/backup/full/ -- 可以看到将原有数据库的数据目录信息，已经基本迁移到指定的物理备份目录中；Xtrabackup数据备份工具在热备操作后产生的特殊数据文件说明：
序号 文件名称 解释说明 01 xtrabackup_binlog_info 表示用于存储备份时的binlog位置点信息 02 xtrabackup_checkpoints 表示用于记录备份时的数据页LSN信息，主要用于接下一次备份，需要保证连续性； 03 xtrabackup_info 表示整体物理备份信息的总览 04 xtrabackup_logfile 表示存储在备份数据期间产生的新的的redo日志的信息； 05 xtrabackup_tablespaces 表示用于存储表空间的其余信息
4.1、Xtrabackup数据恢复方式01：全量备份恢复
模拟进行数据库数据破坏性操作：
[root@db02 ~]# pkill mysqld [root@db02 ~]# rm -rf /data/3306/data/* [root@db02 ~]# rm -rf /data/3306/logs/* [root@db02 ~]# rm -rf /data/3306/binlog/*
进行数据库数据恢复的操作过程：
[root@db02 ~]# xtrabackup --prepare --target-dir=/data/backup/full ...忽略部分信息... Shutdown completed; log sequence number 19214860 221127 16:31:58 completed OK! -- 表示模拟CR过程，将redo日志进行前滚，undo日志进行回滚，让恢复数据信息保持一致性状态 [root@db02 ~]# xtrabackup --copy-back --target-dir=/data/backup/full -- 将进行物理备份后的数据，再次进行还原恢复到备份前的目录中（拷贝回数据） [root@db02 ~]# chown -R mysql.mysql /data/* [root@db02 ~]# /etc/init.d/mysqld start -- 重新设置数据目录权限，并重新启动恢复数据库业务
4.2、Xtrabackup数据备份方式02：实现增量备份
xtrabackup物理备份数据时，实现增量备份原理分析：
增量备份的实质是，基于上一次备份LSN变化过的数据页，进行相应的备份操作，从而可以不断实现增量备份操作；
在备份同时产生的新的变更，会将redo日志信息备份；
第一次增量备份时依赖于全量备份的，将来的恢复操作也要合并到全备中，再进行统一恢复；
说明：利用XPK增量备份数据，主要目的是减少频繁全备数据的时间开销，可以将每天增量的数据进行更快速的备份
# 增量备份准备： [root@db02-01 ~]# mkdir /data/backup/full -p -- 提前准备好全量备份的目录； [root@db02-01 ~]# mkdir /data/backup/inc -p -- 提前准备好增量备份的目录； # 进行备份操作： [root@db02-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/full -- 进行全量备份操作，并且开启并发线程备份功能（--parallel=4），从而提高备份效率（建议4~8个） mysql> create database pxb; mysql> use pxb mysql> create table t1(id int); mysql> insert into t1 values(1),(2),(3); mysql> commit; -- 模拟登陆数据库，进行相关操作，实现增量备份的效果 [root@db02-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full -- 进行增量备份操作 # 可以进行继续备份操作（了解） [root@db02-01 ~]# mkdir /data/backup/inc02 -p -- 提前准备好增量备份的增量备份目录； mysql> create database pxb02; mysql> use pxb02 mysql> create table t1(id int); mysql> insert into t1 values(1),(2),(3); mysql> commit; -- 模拟登陆数据库，进行相关操作，实现增量备份的效果 [root@db02-01 ~]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.30.101 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc02 --incremental-basedir=/data/backup/inc -- 进行增量备份操作的下一次增量备份Xtrabackup数据恢复方式02：增量备份恢复
模拟进行数据库数据破坏性操作：
[root@db02 ~]# pkill mysqld [root@db02 ~]# rm -rf /data/3306/data/* [root@db02 ~]# rm -rf /data/3306/logs/* [root@db02 ~]# rm -rf /data/3306/binlog/*进行数据库数据恢复的操作过程：
# 准备相关备份日志信息 [root@db02 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full -- 准备全量备份的日志； [root@db02 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/inc -- 准备增量备份的日志，并且将增量备份合并到全量备份中； [root@db02 ~]# xtrabackup --prepare --target-dir=/data/backup/full -- 在全量和增量数据合并后，在整体做日志信息的准备； # 进行数据备份拷回操作 [root@db02 ~]# xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full 或者 [root@db02 ~]# xtrabackup --copy-back --target-dir=/data/backup/full -- 将进行物理备份后的数据，再次进行还原恢复到备份前的目录中（拷贝回数据） # 重新启动恢复业务功能 [root@db02 ~]# chown -R mysql.mysql /data/* [root@db02 ~]# /etc/init.d/mysqld start -- 重新设置数据目录权限，并重新启动恢复数据库业务
十二、数据库服务主从复制
所在位置 线程名称 作用说明 主库涉及线程 binlog dump thread 用于将主库binlog日志信息进行传输投递的线程 · 可以实现与从库的信息交互 · 可以监控二进制日志的变化 · 可以进行二进制日志的投递 从库涉及线程 slave IO thread 可以用于连接主数据库服务 · 可以实现与主库线程的交互 · 可以接收和存储二进制日志（接收的二进制日志会存储在中继日志中） slave SQL thread 可以解析执行中继日志信息 主库线程信息查看：binlog dump
可以通过show processlist命令在主库上查看binlog dump线程，从binlog dump线程的状态可以看到，mysql的复制是主库主动推送日志到从库去的，是属于推日志的方式来做同步；
从库线程信息查看：slave io/slave sql
在从库上通过show processlist可以看到I/O线程和SQL线程;
I/O线程等待主库上的binlog dump线程发送事件并更新到中继日志relay log；
用此线程和主库建立连接，并与主库的dump thread线程进行交互，以及接收和存储主库推送过来的binlog日志信息到relay log；
SQL线程读取中继日志relay log并应用变更到数据库；
用此线程实现回放relay log中的日志信息，实现对从库的SQL语句操作；
从MySQL的复制流程可以得知MySQL的复制是异步的，从库上的数据和主库存在一定的延时；
1、复制原理涉及文件
所在位置 文件信息 解释说明 主库涉及文件 binlog 可以利用二进制日志信息变化，实现主从数据库的异步方式同步 从库涉及文件 relaylog 可以利用中继日志进行临时存储或接收主库投递的二进制日志信息，日志信息会定期自动清理 master.info 可以存储主库相关信息（主库地址 端口 用户 密码 二进制位置点-已经获取的 ）（和IO线程相关） relay-log.info 可以存储SQL线程回放过的日志信息（与SQL线程相关）
2、MySQL的复制原理
在从库上执行change master to命令，将主库连接信息和binlog位置信息写入master.info文件或 slave_master_info表中；
在从库上执行start slave命令，用于启动从库的IO和SQL线程功能；
从库IO线程主要用于读取主库连接信息，实现和主库建立连接，从而使主库派生出binlog dump线程（自动监控binlog）；
从库IO线程根据change master to命令所定义的数据位置点，获取最新的binlog日志信息
mysql主库在事务提交时会把数据变更为事件Events记录在二进制日志文件binlog中；
mysql主库上的sync_binlog参数控制binlog日志刷新到磁盘；
binlog dump线程会截取binlog日志并投递其日志给从库IO线程，此时主库并不关心投递日志信息的结果；
此时从库IO线程接收binlog投递信息（缓存），随之会立即更新master.info文件 或 slave_master_info数据表信息；
从库缓存的binlog日志数据信息会被写入到relaylog中继日志中；
主库推送二进制日志文件binlog中的事件到从库的中继日志relay log，之后从库根据中继日志relay log重做数据变更操作，
从库SQL线程将会读取relaylog.info文件或者slave_relay_log_info数据表中信息，获取上次数据回放同步位置点；
随之继续向后回放同步数据，一旦回放同步数据完成后，再次更新relay.info或slave_relay_log_info数据表信息；
在从库中回放过的relaylog日志信息，会被relay_log_purge线程定期删除处理这些日志；
通过逻辑复制以此来达到主库和从库的数据一致；
MySQL通过3个线程来完成主从库间的数据复制：其中binlog dump线程跑在主库上，I/O线程和SQL线程跑在从库上；
当在从库上启动复制（START SLAVE）时，首先创建I/O线程连接主库，主库随后创建binlog dump线程读取数据库事件；
并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志relay log中去，之后从库上的SQL线程读取中继日志relay log，
根据中继日志中的更新的数据库事件并应用；
3、主从复制实践
3.1操作步骤01：需要准备两台以上数据库实例
# 数据库节点01服务已经构建完毕 [root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3307/data/my.cnf & [root@xiaoQ-01 ~]# netstat -lntp|grep 3307 tcp6 0 0 :::3307 :::* LISTEN 114722/mysqld [root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock # 数据库节点02服务已经构建完毕 [root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3308/data/my.cnf & [root@xiaoQ-01 ~]# netstat -lntp|grep 3308 tcp6 0 0 :::3308 :::* LISTEN 122865/mysqld [root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
3.2、操作步骤02：主数据库二进制日志功能开启
# 主数据库需要开启binlog日志功能 mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | log_bin | ON | | log_bin_basename | /data/3307/data/binlog | | log_bin_index | /data/3307/data/binlog.index | | sql_log_bin | ON | +---------------------------------+--------------------------------------+ 6 rows in set (0.01 sec) -- 核实确认binlog日志功能在主库上已经开启
3.3操作步骤03：核实主从复制主机的信息情况
# 确认多个复制节点的服务标识不同（server id/server_uuid） mysql> set global server_id=7; Query OK, 0 rows affected (0.00 sec) mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 7 | +-------------+ 1 row in set (0.00 sec) mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 949efd68-6420-11ed-b1ad-000c2996c4f5 | +--------------------------------------+ 1 row in set (0.00 sec) -- 主库上server_id server_uuid信息（3307节点做检查） mysql> set global server_id=8; Query OK, 0 rows affected (0.00 sec) mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 8 | +-------------+ 1 row in set (0.00 sec) mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | ed6c1c6f-6f33-11ed-b630-000c2961cd06 | +--------------------------------------+ 1 row in set (0.00 sec) -- 从库上server_id server_uuid信息（3308节点做检查） -- 根据服务标识信息的不同，可以用于在进行主从复制时，辨别节点之间的关系 # 确认多个复制节点的时间信息同步 [root@xiaoQ-01 ~]# date 2022年 11月 30日 星期三 23:44:37 CST [root@xiaoQ-02 ~]# date 2022年 11月 30日 星期三 23:44:52 CST -- 核心确认主从节点时间信息同步 # 确认多个复制节点的版本信息一致 [root@xiaoQ-01 ~]# mysql -V mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) [root@xiaoQ-02 ~]# mysql -V mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) -- 部分实际应用场景中可以支持不一致，但是复制源端可以是低版本，复制目标端可以是高版本，反之不行；
3.4操作步骤05：创建主从数据同步的用户信息
# 在主库上创建复制同步数据用户 mysql> create user repl@'192.168.200.%' identified with mysql_native_password by '123456'; mysql> grant replication slave on *.* to repl@'192.168.200.%'; -- 从库会利用主库上的同步数据用户，进行数据同步传输操作
3.5操作步骤06：进行从库部分数据信息的同步
# 可以将主库上的部分数据在从库上先进行同步 [root@db02 tmp]# mysqldump -uroot -A -S /tmp/mysql.sock --master-data=2 --single-transaction >/tmp/full.sql -- 在3306主库上进行数据的全备（模拟企业环境的历史数据全备） scp /tmp/full.sql 192.168.200.146:/tmp/ [root@xiaoQ-01 ~]# mysql mysql> source /tmp/full.sql; -- 在从库上进行数据的恢复（模拟企业环境的历史数据恢复）两边数据一样 -- 将原有主机的数据先备份，然后从库中进行恢复一部分数据，随后再进行数据信息同步追加 -- 可以利用同步方式有很多：mysqldump xtrabackup clone_plugin
3.6操作步骤07：配置主从节点数据复制的信息
# 设置从库连接主库信息，定义从库连接主库同步位置点自动复制 mysql> help change master to -- 获取连接主库，以及定义同步位置点的数据库配置模板信息 [root@xiaoQ-01 ~]# vim /tmp/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=681; -- 通过备份文件获取同步位置点信息 SHOW MASTER STATUS; #查看MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1638, #注意MASTER_LOG_FILE='binlog.000001',文件名对不对 mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.145', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=881, MASTER_CONNECT_RETRY=10; -- 以上配置主从同步信息在 --从库 进行执行；
3.7操作步骤08：激活主从节点数据复制的线程
# 利用相应线程实现主从数据库的数据同步复制 mysql> start slave; -- 在从库上激活数据复制同步功能 # 若此时数据同步失败可以重新开启同步功能 mysql> stop slave; mysql> reset slave all; -- 在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能； # 进行核实主从同步功能是否实现 [root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock mysql> create database xiaop; -- 在主库模拟创建数据信息 [root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock mysql> show databases; -- 在从库模拟查看数据信息（确认是否同步数据） mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.30.101 Master_User: repl Master_Port: 3307 Connect_Retry: 10 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 869 Relay_Log_File: xiaoQ-01-relay-bin.000002 Relay_Log_Pos: 509 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes -- 从库上查看数据同步状态情况，看到上面的两个Yes信息，就表示主从数据同步功能设置成功了
####有待补充。。。。
扫一扫
5675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
