【数据库】MySQL基本操作语句

一、SQL语句

1.1 SQL分类

1.2 SQL语言规范

1.3 数据库对象与命名

1.3.1 数据库的组件(对象)：

1.3.2 命名规则：

1.4 SQL语句分类

二、基本命令

2.1 查看帮助信息

2.2 查看支持的字符集

2.3 查看默认使用的字符集

2.4 修改默认字符集

2.5 管理数据库的命令

2.5.1 创建数据库

2.5.2 if no exists

2.6 修改数据库

2.7 删除数据库

2.8 查看数据库列表

2.9 数据类型

2.10 修饰符

2.11 管理数据库表的命令

2.11.1创建数据库表

2.11.2 修改字段即表结构

2.12 DML语句

2.12.1 INSERT语句

2.12.2 update语句

2.12.3 delete语句

2.13 DQL语句

2.13.1 where过滤查询

2.13.2 distinct去除重复行

2.13.3 like模糊查询

2.13.4 数学函数

2.13.5 聚合函数

2.13.6 字符串函数

2.13.7 group by分组与having

2.13.8 order by排序与limit

2.14 DQL语句多表查询

三、用户管理

3.1 存放用户信息的表

3.2 查看当前使用的用户名

3.3 新建用户

3.4 修改用户名

3.5 删除用户

3.6 修改密码

3.7 破解密码

3.8 授权

四、存储引擎与索引

4.1 innodb存储引擎与myisam对比

4.2 索引结构

4.3 查看、建立索引

4.4 explain

五、事务与锁

5.1 事务的特性

5.2 事务的隔离级别

5.3 锁

5.4 手动加锁

5.5 解锁

5.6 全局锁

六、日志

6.1 事务日志

6.1.1 双一操作

6.2 错误日志

6.3 通用日志

6.4 慢查询日志

6.5 二进制日志

七、备份

7.1 备份类型

7.2 完全备份

7.2.1 冷完全备份

7.2.2 mysqldump单机备份与恢复

7.2.3 生产环境实战备份策略

7.3 增量备份

八、主从复制

8.1 原理

8.2 作用

8.3 相关线程

8.4 主从复制实现

8.4.1 主从复制开启之前的数据如何复制

8.5 复制错误解决方法

8.6 级联主从

8.7 半同步复制

九、读写分离mycat

9.1 Mycat应用场景

9.1.1 mycat 可以简单概括为

9.2 mycat部署

mycat安装目录结构说明

Mycat的常用配置文件

Mycat日志

mycat 实现读写分离

1.环境准备

2.初始化环境

3.部署主从复制

5.修改 mycat 配置文件 /apps/mycat/conf/server.xml

十、MHA高可用演示

1.关闭防火墙selinux

2.主节点（7-5）安装管理和客户端工具

3.其余所有节点（7-2，7-3，7-4）安装客户端

4. 所有节点基于key验证

5.主节点（7-5）建立mha文件夹和配置文件

6.准备切换脚本

7实现主从复制

7.1主服务器操作（7-2）

7.2从服务器设置（7-3,7-4）

7.2.1 服务器7-3配置

7.2.2服务器7-4配置

8.设置虚拟地址

9在运行前需要先检测环境是否符合

9.1 检测 ssh 免密登录是否成功

9.2 检测主从复制是否可以

9.3 查看状态未开启

10 开启mha

11测试

11.1 mha 如何发现主节点宕机

11.2 查看 mha 服务的日志

11.3 模拟 mysql 主节点故障

12.再次开启MHA

一、SQL语句

1.1 SQL分类

数据库：database
表：table，行：row 列：column
索引：index
视图：view
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
用户：user
权限：privilege

1.2 SQL语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写 select drop create
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性

1.3 数据库对象与命名

1.3.1 数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

1.3.2 命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,table select show databases

1.4 SQL语句分类

DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

软件开发：CRUD

DQL：Data Query Language 数据查询语言

SELECT

DCL：Data Control Language 数据控制语言

GRANT，REVOKE

TCL：Transaction Control Language 事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

二、基本命令

2.1 查看帮助信息

help create;

2.2 查看支持的字符集

show charset;

##最好使用utf8mb4版本

2.3 查看默认使用的字符集

show variables like 'char%';

#新建的数据库默认使用的是拉丁字符集

2.4 修改默认字符集

修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4


show create database test;

2.5 管理数据库的命令

2.5.1 创建数据库

create database [if not exists] 'DB_NAME' CHARACTER SET '字符集名' collate 'collate name';

举例：

create database db1;
#建立数据库db1

show create database db1;
#查看数据库的基本信息

create database db2 character set 'utf8mb4';
#指定字符集

create database db3 character set 'utf8' collate utf8_bin;
#字符集utf8不区分大小写

2.5.2 if no exists

create database if not exists db1;

show warnings;

#先判断数据库是否存在，若存在则不创建，不存在则创建

2.6 修改数据库

alter database DB_NAME character set utf8mb4;
#修改字符集

alter指令用于修改数据库中的各种参数

2.7 删除数据库

drop database '数据库名';

cd /home/mysql/
#数据库就是一个文件夹，删除文件夹就等于删除了数据库

2.8 查看数据库列表

show databases;

show create databases db1;
#会展示出数据库db1的结构

2.9 数据类型

varchar(50)能存放几个UTF8编码的汉字？

50个。

2.10 修饰符

名称	含义
NULL	数据列可包含NULL值，默认值
NOT NULL	数据列不允许包含NULL值，*为必填选项
DEFAULT	默认值
PRIMARY KEY	主键，所有记录中此字段的值不能重复，且不能为NULL 一张表中只有一个主键
UNIQUE KEY	唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET	name 指定一个字符集
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号

2.11 管理数据库表的命令

2.11.1创建数据库表

create table student (uid int primary key auto_increment unsigned, name varchar(10) not null, gender enum('M','F') default 'M');
#创建student表


desc student 或 show create table student;
#查看表结构

2.11.2 修改字段即表结构

add

alter table student add phone varchar(11) not null;

change

alter table student change phone mobile char(11);
#将phone字段改为mobile并且数据类型改为char(11)

drop

alter table student drop mobile;
#删除mobile字段

2.12 DML语句

DML：INSERT、DELETE、UPDATE

2.12.1 INSERT语句

功能：一次插入一行和多行数据

insert student(name,age,gender) values('cxk','24','M');
#插入单条数据

insert student(uid,name,age,gender) values('2','wyf','18','M'),('3','lb','22','F');
#插入多条数据，数据与数据之间用,隔开

2.12.2 update语句

update 表名 set 字段=修改的值 指定哪条记录(过滤条件，否则修改全部数据);

一定要添加限制条件，否则会修改整个字段的值！！

2.12.3 delete语句

delete from 表名 指定条件


delete from student where name='cxk';

同样要添加限制条件，否则会删除整个表的数据！！

2.13 DQL语句

DQL：SELECT

2.13.1 where过滤查询

select * from student where id=2;
select * from student where gender='F';
select * from student where name='cxk';
select * from student where id>1 and gender='M';
select * from student where name='cxk' or id=1;
select * from student where phone is not null;
select * from student where id in(1,2,3);

2.13.2 distinct去除重复行

select name,age from student;
#会显示所有学生的名字和年龄


select distinct age from student;
#会显示student表中的年龄，相同年龄只会显示一次

2.13.3 like模糊查询

select * from student where name like 'L%';
#查找student表中，名字以'L'开头的信息

select * from student where name like '_i%';
#查找student表中，名字的第二个字符为'i'的信息

例子：sql注入

create table user (id int primary key auto_increment,name varchar(20) not null,password varchar(30) not null);

insert user values(null,'admin','123456');
insert user values(null,'test','123456');


select * from user where name='admin' and password='123456';   #找到数据就是 用户名密码正确。找不到就是错误
select * from user where name='admin' and password='' or '1'='1';

#其他例子：
select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='123';      #--代表注释的意思
select * from user where name='admin'; # ' and password='123';

2.13.4 数学函数

函数名	函数值
abs(x)	返回 x 的绝对值
rand()	返回 0 到 1 的随机数
mod(x,y)	返回 x 除以 y 以后的余数
power(x,y)	返回 x 的 y 次方
round(x )	返回离 x 最近的整数
round(x,y)	保留 x 的 y 位小数四舍五入后的值
sqrt(x)	返回 x 的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2...)	返回集合中最大的值，也可以返回多个字段的最大的值
least(x1,x2...)	返回集合中最小的值，也可以返回多个字段的最小的值

2.13.5 聚合函数

函数名	函数意
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum()	返回指定列的所有值之和

2.13.6 字符串函数

函数名	函数意义
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

2.13.7 group by分组与having

select age,avg(age) from student group by age;
#注意，凡是在group by后面出现的字段，必须在select后面出现，反之同理


select age,avg(age) from student group by age having uid>=10;
#查找student表中，uid大于等于10的学生的年龄与平均年龄并且以age分组

2.13.8 order by排序与limit

select * from student where gender='M' order by age [ASC默认升序,DESC倒序];


select * from student where gender='F' limit 0,5;
#查找student表中，性别为F的数据，跳过0个数据，显示跳过数据的后5个数据

2.14 DQL语句多表查询

三、用户管理

3.1 存放用户信息的表

select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

3.2 查看当前使用的用户名

select user();

3.3 新建用户

create user 'name'@'host' [identified by 'password'];

#通配符： %为任意长度字符，_为单个字符

mysql  -u用户名  -p密码   -h远程主机  -P端口号

3.4 修改用户名

rename user '旧名字' to '新名字;

rename user 'zhangsan'@'192.168.91.%' to 'lisi'@'192.168.91.%';

3.5 删除用户

drop  user   用户名@主机名

drop user liwu@'%';

3.6 修改密码

密码有安全性策略可以修改取消
set global validate_password_policy=0;
set global validate_password_length=1;

SET PASSWORD = PASSWORD('abc123');  #只能改自己当前
set password for '用户' = 'password';    #修改别人密码

3.7 破解密码

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
#数据库的单用户模式   此模式下权限受到限制，很多功能无法使用， 除了破解密码不要加此项
skip-networking  #MySQL8.0不需要

#然后清空密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
#注意刷新后生效
flush privileges;

3.8 授权

grant all on 数据库名.表名 to 'name'@'host' [identified by 'password'];

四、存储引擎与索引

4.1 innodb存储引擎与myisam对比

					myisam				innodb
最大存储		     256T				    64T
事务				不支持					支持
外键				不支持					支持
mvcc			不支持				    支持
缓存				不支持					支持
锁级别			表级					    行级

4.2 索引结构

二叉树：

类似于二分法，生成了一个0 - 100的随机数，猜大猜小会有提示，大部分先从50 开始猜，继续2分法不断去猜。

最多两个个分支

缺点：数据偏移，不够平衡

红黑树

B-Tree索引

缺点：

1.连续范围查找都要从头开始，效率不稳定，快的很快，慢的就比较慢

2.所存数据量越大，查找次数越多

B+Tree索引

按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

4.3 查看、建立索引

show index from 表名;

create index name on 表名(列名[(length)]);

4.4 explain

explain select * from students;

可以查看是通过索引查询的还是普通查询

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE\|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

type访问类型

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

五、事务与锁

5.1 事务的特性

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，只要有一个失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)
I：Isolation隔离性；一个事务所做出的操作在提交之前，能不能为其它事务所见；和隔离级别有关系 4个隔离级别
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

5.2 事务的隔离级别

READ UNCOMMITTED（未提交可读脏读）可读取到未提交数据，产生脏读
READ COMMITTED（提交可读，每次读取数据不一致）可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ 可重复读幻读可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE 串读可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

隔离级别	脏读	可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

5.3 锁

锁类型：

读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务），多个读互不阻塞只能读不能写别人也能看
写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写写锁别人看都看不了

特点：

S（读）锁和 S（读）锁是兼容的，X （写）锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

读锁其他人只能看不可以修改

锁粒度：

表级锁：MyISAM
行级锁：InnoDB

实现

存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

演示例子：行级锁

update students set teacherid=1 where stuid=6；   #同时在两个窗口执行只有一个能成功


终端1:
mysql> update students  set teacherid=1  where stuid=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

终端2:
mysql> update students  set teacherid=1  where stuid=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

5.4 手动加锁

人为加锁比较少见，一般在备份的时候会加锁，备份期间数据一直在改，可能备份时与备份的内容不一致

语法：

LOCK TABLES 表名 [[AS] alias] 锁的类别  [, tbl_name [[AS] alias] lock_type] ...

lock  tables    表名    type;

type: read   或者   write

lock   tables   students   read;

例子：给 students 表加读锁只能看不能读

lock tables  students read;  #加上读锁那么只可以查不可以修改

mysql> insert students  values(2,"diyun","22","M",1,3);
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated


update students set classid=2 where stuid=24;

5.5 解锁

unlock  tables;     #释放锁
例子：

unlock  tables;     #释放锁

5.6 全局锁

通常在备份前加全局读锁

语法：

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
例子： 全局加锁

flush tables  with read lock;   #加锁

drop  table   students ;    #测试
 
unlock  tables;          #解锁

六、日志

6.1 事务日志

redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

show variables like '%innodb_log%';   #查看事务日志
innodb_log_file_size   50331648 #每个日志文件大小  字节
innodb_log_files_in_group 2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

ll -h /var/lib/mysql



vim  /etc/my.cnf
[mysqld]
innodb_log_file_size=503316480
innodb_log_files_in_group=3

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

select @@innodb_flush_log_at_trx_commit;
#查看默认值

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务执行完成后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

级别	0	1	2
安全性	较高	最高	最高
性能	最高	最差	较高

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

双1设置：

说明：

设置为1，同时sync_binlog = 1表示最高级别的容错（二进制日志）
innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6 后废弃）

6.1.1 双一操作

在MySQL配置中，sync_binlog和innodb_flush_log_at_trx_commit都设置为1通常被称作“双一”配置。

这两个选项都与数据的持久性和一致性有关：

sync_binlog=1：该设置会使得MySQL每次提交事务时都会同步二进制日志（binlog）到磁盘。这样可以确保在发生故障时，已提交的事务不会丢失，因为它们已经写入了磁盘。二进制日志是MySQL复制的基础，也用于增量备份等。

innodb_flush_log_at_trx_commit=1：该设置控制InnoDB存储引擎何时将事务日志信息（redo log）刷新到磁盘。当此设置为1时，每次事务提交都会导致日志被写入并刷新到磁盘。这提供了最高的数据持久性保证，因为即使MySQL突然崩溃或出现电源故障，最近提交的事务也不会丢失。

优点：
数据安全性：提高了数据的持久性和可靠性，减少了数据丢失的风险。
恢复能力：在发生崩溃后，系统能够利用binlog和redo log来恢复到最近的一致状态。
ACID兼容：帮助MySQL更好地符合ACID(Atomicity, Consistency, Isolation, Durability)事务特性中的Durability持久性特性。
缺点：
性能影响：每次事务提交都需要将数据写入磁盘，这可能导致额外的I/O开销，从而降低了数据库的总体性能。
延迟增加：对于写密集型应用，由于每次事务都要进行磁盘同步操作，可能会增加事务的响应时间。
总的来说，“双1”配置适合对数据持久性要求较高的场景，但可能需要牺牲一定的性能以确保数据的安全性和一致性。在实际部署时，还需要根据具体业务需求、系统负载和可接受的性能损耗程度来权衡这两者之间的关系。

6.2 错误日志

错误日志

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;

6.3 通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

通用日志相关设置：

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE


范例: 启用通用日志并记录至文件中

select @@general_log;     #默认没开启
set global general_log=1; #开启

SHOW GLOBAL VARIABLES LIKE 'log_output';
#默认通用日志存放在文件中
select @@general_log_file;
#通用日志存放的文件路径

6.4 慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s    才会记录
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除


set  global slow_query_log=1;
#开启
set long_query_time=1;


select sleep(10)

6.5 二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

二进制日志记录三种格式

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少

基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式

混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

show variables like 'binlog_format';

二进制日志文件格式

有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin

sql_log_bin=ON|OFF：
#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项，一般默认开启
select  @@sql_log_bin;

log_bin

vim /etc/my.cnf
log_bin=mysql-bin      #默认是关闭
#  指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以    
#  此选项作用是指明 日志文件的位置所在
#  需要加  server-id = 1   数据库编号
server-id  = 1
# 数据库的 编号   区别数据库



mysql> select  @@log_bin;
#变量 log_bin 和服务器选项是两个意思

查看二进制文件

linux中

mysqlbinlog [选项] 二进制文件路径
选项：
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]


mysql中

show master status;

删除二进制日志

purge binary logs to 'mysql-bin.000002';
#代表删除002 之前的  日志

reset  master;
#彻底清空二进制日志

flush logs;
#刷新日志

七、备份

7.1 备份类型

完全备份，部分备份

完全备份：整个数据集

部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

增量备份还原规则就是先备先还

差异备份：仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单，直接还原最新的备份

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行

MyISAM：温备，不支持热备不支持事务

InnoDB：都支持

备份数据

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

7.2 完全备份

7.2.1 冷完全备份

#迁移数据库先不要启动数据库， 复制好文件后再启动

systemctl stop mysqld
scp -r  /var/lib/mysql/    192.168.91.101:/data/
#将数据库文件整个打包
scp  /etc/my.cnf   192.168.91.101:/etc/
#如果修改了配置文件需要一起复制

#模拟破坏数据
rm -rf /var/lib/mysql

#备份节点
scp -r  /opt/mysql/    192.168.91.100:/var/lib/

systemctl restart mysqld

7.2.2 mysqldump单机备份与恢复

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

vim /etc/my.cnf
log_bin=/data/mysql-bin	#开启二进制日志
server-id=1

chown -R mysql.mysql /data/
systemctl restart mysqld

#完全备份
mysqldump -uroot -p123123 -A -F --single-transaction --master-data=2 > /opt/all_`data +%F`.sql

#完全备份后数据发生的更新
insert students values(null,'rose',20,'M'),(null,'jack',18,'M');

#10:00 误删students
drop table students;

#后续其他的更新操作
insert teachers values('jkl',20,'F');

#10.10发现错误，进行还原，停止数据库访问
grep -i '\-\- change master to' /opt/all_*.sql
#找到完全备份后的二进制日志节点

mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /opt/inc_`data +%F`.sql
#将二进制日志变为sql语句

grep -in 'drop' /opt/inc_*.sql
#找出sql中，误删操作在哪一行
sed -i.bak '行号d' /opt/inc_*.sql
#删除误删操作的行
sed -i.bak '/^drop/d' /opt/inc_*.sql
#或者直接删除以drop开头的行

mysql -uroot -p
#登陆mysql
set sql_log_bin=0;
#先临时关闭二进制日志

source /opt/all_*.sql	#先还原完全备份
source /opt/inc_*.sql #再还原增量备份

set sql_log_bin=1;
#再开启二进制日志

show tables in hellodb;
#查看students表是否回来了
select * from hellodb.teachers;
#查看teachers表中添加的数据是否存在

mysqldump 常见通用选项：

选项	含义
-A, --all-databases	#备份所有数据库，含create database
-B, --databases db_name…	#指定备份的数据库，包括create database语句
-E, --events：	#备份相关的所有event scheduler
-R, --routines：	#备份所有存储过程和自定义函数
--triggers：	#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8	#指定字符集
--master-data[=#]：	#此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x \| --lock-all-tables功能（除非开启-- single-transaction）
-F, --flush-logs	#备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact	#去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data	#只备份表结构,不备份数据,即只备份create table
-t, --no-create-info	#只备份数据,不备份表结构,即不备份create table
-n,--no-create-db	#不备份create database，可被-A或-B覆盖
--flush-privileges	#备份mysql或相关时需要使用
-f, --force	#忽略SQL错误，继续执行
--hex-blob	#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick	#不缓存查询，直接输出，加快备份速度

7.2.3 生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -pabc123 -A -F -E -R --triggers --single-transaction --master-data=1  >${BACKUP}/fullbak_${BACKUP_TIME}.sql

脚本案例：

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=test
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

7.3 增量备份

1开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin = mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT


systemctl restart mysqld
ls -l /var/lib/mysql-bin.*

2可每周对数据库或表进行完全备份
mysqldump -u root -pAdmin@123 hellodb students > /opt/my_h_s_$(date +%F).sql
mysqldump -u root -p --databases hellodb > /opt/my_$(date +%F).sql

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs

4．插入新数据，以模拟数据的增加或变更
use kgc;
insert into students values(3,'user3','male','game');
insert into students values(4,'user4','female','reading');

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6．查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v  >/opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

恢复

1．一般恢复
（1）模拟丢失更改的数据的恢复步骤
use kgc;
delete from info1 where id=3;
delete from info1 where id=4;

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -pAdmin@123

（2）模拟丢失所有数据的恢复步骤
use kgc;
drop table info1;



2．断点恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例：
# at 302
#201122 16:41:16
插入了“user3”的用户数据

# at 623
#201122 16:41:24
插入了“user4”的用户数据


（1）基于位置恢复
#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='682' /opt/mysql-bin.000002 | mysql -uroot -p

（2）基于时间点恢复
#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p


如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

八、主从复制

8.1 原理

1.主节点负责用户的写操作，用户发起写操作后，会修改数据库
2.数据库修改后，会更新主节点上的二进制日志
3.从服务器会开启io线程，主动请求获取主服务器的二进制日志
4.主服务器会产生一个dump线程，一边读取二进制日志一边将二进制日志通过网络传给从服务器
5.io线程会将主服务器的二进制日志写入中继日志，这时只是生成了一个文件，并没有同步
6.从服务器再开启 sql线程将中继日志中操作写入数据库完成更新

8.2 作用

负载均衡读操作
备份
高可用和故障切换
数据分布
MySQL升级

8.3 相关线程

主从复制相关线程

主节点：

dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events从节点：

I/O Thread：向Master请求二进制日志事件，并保存于中继日志中

SQL Thread：从中继日志中读取日志事件，在本地完成同步

跟复制功能相关的文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器（Master）保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

文件名的格式是：从服务器名 - relay-bin.序号。中继日志还有一个索引文件：从服务器名 - relay-bin.index，用来定位当前正在使用的中继日志。

8.4 主从复制实现

#关闭防火墙和selinux
systemctl stop firewalld
setenforce 0

##########主服务器#########
#开启二进制日志，配置server-id，保证主从不同
vim /etc/my.cnf
[mysqld]
server-id=10
log-bin=/data/mysql/mysql-bin

mkdir /data/mysql/ -p
chown -R mysql.mysql /data/
systemctl restart mysqld

#登陆mysql
mysql
#查看同步文件与节点
show master status;
#创建新用户，用于主从间的连接同步
grant replication slave on *.* to 'name'@'host' identified by 'password';


#########从服务器##########
#开启二进制日志
vim /etc/my.cnf
[mysqld]
server-id=20
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin	#开启中继日志
relay-log-index=slave-relay-bin.index
read only  #只读可加

mkdir /data/mysql/ -p
chown -R mysql.mysql /data/
systemctl restart mysqld

#登陆mysql
mysql
#添加主服务器信息
help change master to;
#输入命令
CHANGE MASTER TO
  MASTER_HOST='主服务器IP',
  MASTER_USER='创建的用户名',
  MASTER_PASSWORD='用户密码',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',	#哪一个二进制日志
  MASTER_LOG_POS=154;	#从哪个节点开始
  
#开启io线程和sql线程
start slave;
#查看是否成功
show processlist;
# Seconds_Behind_Master: NULL    #目前数据差

#查看从服务器状态
show slave status\G;

#若发现同步失败，先停止再重置配置
stop slave;
reset slave all;

8.4.1 主从复制开启之前的数据如何复制

######主服务器######
#开启二进制日志
vim /etc/my.cnf
server-id=10
log-bin=/data/mysql-bin
mkdir -p /data/
chown -R mysql.mysql /data/
systemctl restart mysqld

#做完全备份
mysqldump -uroot -p -A -F --master-data=1 --signle-trasaction > /data/all.sql

#登录mysql
mysql -uroot -p
#新建用户
create   user  test@'192.168.91.%' identified by "Admin@123";
#授权主从复制权限
grant replication slave on *.*  to test@'192.168.91.%';

#传给从服务器all.sql
scp  /data/all.sql   192.168.91.102:/opt

#####从服务器#####
#开启二进制日志
vim /etc/my.cnf
server-id=20
log-bin=/data/mysql-bin
mkdir -p /data/
chown -R mysql.mysql /data/
systemctl restart mysqld

#修改备份脚本
vim /opt/all.sql
#  找到    CHANGE MASTER TO  的行添加内容如下
CHANGE MASTER TO
  MASTER_HOST='主服务器IP',
  MASTER_USER='主从复制账户用户名',
  MASTER_PASSWORD='密码',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; #自动生成
#由于之前再备份的时候加入了    --master-data=1 选项    就添加了主从复制的选项000002文件的154位置往后开始主从复制
#000002文件154位置之前的  配置由  备份文件自行实现 

mysql  -uroot -pabc123
#进入数据库

mysql> set sql_log_bin=0;            #关闭二进制日志

mysql> source /opt/all.sql           #导入数据库

mysql> start  slave;

mysql> show slave status\G;        #查看从节点的状态

8.5 复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

范例：复制冲突的解决

create table info (id int,name char(10),age char(10));
#先在从上建表


#再在主上建表  插入信息
create table info (id int,name char(10),age char(10));
insert info values(1,'a',10);

#此时报错 从主机会报错
show  slave  status\G

#可以跳过错误
stop slave;
set global sql_slave_skip_counter=1;
start slave;

#方法2
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL 

#systemctl restart mysqld


#清楚所有从配置
reset slave  all;
#清空主从配置

8.6 级联主从

主服务器的slave为从服务器1，从服务器1的slave为从服务器2

############################主服务器############################
#开启二进制日志
vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=100
log-bin=/data/mysql/mysql-bin
mkdir /data/mysql/   -p
#建立文件夹
chown mysql.mysql /data/ -R
#注意修改权限
systemctl restart mysqld
#登录mysql
mysql -uroot -p
#查看二进制日志位置
show master status;
#建立复制用户
grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';
#查看线程
show processlist;


#######################从服务器1##############################
vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=101
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin		#开启中继日志
relay-log-index=slave-relay-bin.index
log_slave_updates	#######开启级联的重要配置
#read only  #只读可加
#登录mysql
mysql -uroot -p

CHANGE MASTER TO
  MASTER_HOST='主服务器IP',
  MASTER_USER='test',
  MASTER_PASSWORD='abc123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=604;
  
show slave status\G;
#查看设置的状态
start slave;
#开启线程，开启主从复制
show master logs;
#查看  从节点的复制位置 查看最新的节点，用于从服务器2的配置


#######################从服务器2##############################
vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=102
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
#read only  #只读可加

mkdir /data/mysql/   -p
#建立文件夹
chown mysql.mysql /data/ -R
#注意修改权限
systemctl restart mysqld
#登录mysql
mysql -uroot -p

CHANGE MASTER TO
  MASTER_HOST='从服务器1的IP',
  MASTER_USER='test', 
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=448;	#从服务器1的最新节点
  
show slave status\G;
#查看设置的状态
start slave;
#开启线程，开启主从复制

8.7 半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

主服务器，从服务器1，从服务器2
############################主服务器############################
#登录mysql
mysql -uroot -p
#安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#此为卸载插件，不必理会
#UNINSTALL PLUGIN rpl_semi_sync_master;
#修改文件
vim  /etc/my.cnf
[mysqld]
server_id=100
log-bin=/data/mysql/mysql-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
#修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动 开启半同步
#设置3s内无法同步，也将返回成功信息给客户端
#登录mysql
mysql -uroot -p
SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s
SHOW GLOBAL VARIABLES LIKE '%semi%';
#查看半同步状态
show global status like '%semi%';
#查看半同步客户端
show  master  status;
#建立复制用户并授权
grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';

#######################从服务器1##############################
#登录mysql
mysql -uroot -p
#安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#修改文件
vim /etc/my.cnf
[mysqld]
server-id=101
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动
#登录mysql
mysql -uroot -p
CHANGE MASTER TO
  MASTER_HOST='192.168.254.10',
  MASTER_USER='master-slave_copy',
  MASTER_PASSWORD='158436',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
#开启从服务
start slave;	
#查看状态  主从状态
show global status like '%semi%';
#临时修改变量
SET GLOBAL rpl_semi_sync_slave_enabled=1; 
SHOW GLOBAL VARIABLES LIKE '%semi%';

#######################从服务器2##############################
#登录mysql
mysql -uroot -p
#安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#修改文件
vim /etc/my.cnf
[mysqld]
server-id=102
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动
#登录mysql
mysql -uroot -p
CHANGE MASTER TO
  MASTER_HOST='192.168.254.10',
  MASTER_USER='master-slave_copy',
  MASTER_PASSWORD='158436',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;
#开启从服务
start slave;	
#查看状态  主从状态
show global status like '%semi%';
#临时修改变量
SET GLOBAL rpl_semi_sync_slave_enabled=1; 
SHOW GLOBAL VARIABLES LIKE '%semi%';

九、读写分离mycat

9.1 Mycat应用场景

Mycat适用的场景很丰富，以下是几个典型的应用场景
- 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
- 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
- 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化报表系统，借助于Mycat的分表能力，处理大规模报表的统计
- 替代Hbase，分析大数据，作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择
Mycat长期路线图
- 强化分布式数据库中间件的方面的功能，使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具，实现在线数据扩容、迁移等高级功能进一步挺进大数据计算领域，深度结合Spark Stream和Storm等分布式实时流引擎，能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力，并集成一些热门常用的实时分析算法，让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能不断强化Mycat开源社区的技术水平，吸引更多的IT技术专家，使得Mycat社区成为中国的Apache，并将Mycat推到Apache基金会，成为国内顶尖开源项目，最终能够让一部分志愿者成为专职的Mycat开发者，荣耀跟实力一起提升

9.1.1 mycat 可以简单概括为

一个彻底开源的，面向企业应用开发的大数据库集群

支持事务、ACID、可以替代MySQL的加强版数据库

一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群

一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

一个新颖的数据库中间件产品

9.2 mycat部署

mycat安装目录结构说明

bin ：mycat命令，启动、重启、停止等运行目录
catlet： catlet为Mycat的一个扩展功能
conf ：mycat 配置信息,重点关注
lib：mycat引用的jar包，Mycat是java开发的
logs ：日志文件，包括Mycat启动的日志和运行的日志
version.txt ：mycat版本说明

Mycat的常用配置文件

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

server.xml ：Mycat软件本身相关的配置文件，设置账号、参数等
schema.xml：Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制
rule.xml：Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

Mycat日志

Mycat的日志文件都在logs目录里面

wrapper.log ：mycat启动日志
mycat.log ：mycat详细工作日志

mycat 实现读写分离

1.环境准备

mycat服务器上不能装mysql

master服务器	192.168.254.20
slave1服务器	192.168.254.30
mycat服务器	192.168.254.10
客户机	192.168.254.40

2.初始化环境

#每台服务器上都初始化，关闭防火墙
systemctl stop --now firewalld
systemctl disable firewalld
setenforce 0

3.部署主从复制

###################### 主mysql服务器配置（192.168.91.100）########################
[root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=100
log-bin=/data/mysql/mysql-bin

[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld

select @@server_id;
#可以查看serverid  默认都是1
show master status;
#查看二进制日志位置


grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';
#建立复制用户

show processlist;
#查看线程


############################# 从mysql服务器配置（192.168.91.101）################
[root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=101
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
#read only  #只读可加


[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld



下面修改配置 命令较长可以使用帮助
help change master to


CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=448;
  注意最后分号

CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;


show slave status\G;
#查看设置的状态
Seconds_Behind_Master: NULL    #目前数据差

start slave;
#开启线程，开启主从复制



create database cxk;
#在主节点上建立数据测试


####################### 在master服务器上上传库文件，并在从服务器上验证主从同步 ############

#在主服务器上下载hellodb库文件，source后加绝对路径
[root@localhost ~]#mysql -uroot -pAdmin@123
#下载hellodb库文件
source /bak/hellodb.sql
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#在从库中查看库文件hellodb是否已经同步
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4.安装mycat

（1）主机上安装java（mycat基于java）
#yum安装java
[root@localhost ~]#yum install java -y
#确认安装成功
[root@localhost ~]#java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

（2）切换至opt目录，下载mycat安装包
[root@localhost ~]#cd /opt
[root@localhost ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

（3）创建/apps文件夹，并解压mycat包至/apps下
[root@localhost ~]#mkdir /apps
[root@localhost ~]#tar zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

（4）设置变量环境
[root@localhost ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost ~]#source /etc/profile.d/mycat.sh

（5）启动mycat，查看日志文件，最后可以看到启动成功
[root@localhost ~]#mycat start
#注意内存小于2G 起不来
Starting Mycat-server...

[root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
#启动成功日志末尾会出现successfully
STATUS | wrapper  | 2021/12/09 21:04:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/12/09 21:04:10 | Launching a JVM...
INFO   | jvm 1    | 2021/12/09 21:04:11 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/12/09 21:04:11 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/12/09 21:04:11 | 
INFO   | jvm 1    | 2021/12/09 21:04:12 | MyCAT Server startup successfully. see logs in logs/mycat.log


(6)客户端连接数据库
#这里密码初始为123456   需要加端口
[root@localhost bin]#mysql -uroot -p123456 -h 192.168.59.114 -P8066

5.修改 mycat 配置文件 /apps/mycat/conf/server.xml

[root@localhost ~]#vim /apps/mycat/conf/server.xml

#去掉44行行注释，对应的在51行行末注释，删除50行行末注释，5 * 60 * 1000L; //连接空>    闲检查

#修改45行端口号为3306
45 <property name="serverPort">3306</property>

#配置Mycat的连接信息(账号密码)，在110 和111行， 可以修改，这边不修改了

####参数解释说明####
110         <user name="root" defaultAccount="true">
111                 <property name="password">123456</property>
112                 <property name="schemas">TESTDB</property>
113                 <property name="defaultSchema">TESTDB</property>

116                 <!-- 表级 DML 权限设置 -->
117                 <!-- 
118                 <privileges check="false">
119                         <schema name="TESTDB" dml="0110" >
120                                 <table name="tb01" dml="0000"></table>
121                                 <table name="tb02" dml="1111"></table>
122                         </schema>
123                 </privileges>   
124                  -->

127         <user name="user">
128                 <property name="password">user</property>
129                 <property name="schemas">TESTDB</property>
130                 <property name="readOnly">true</property>
131                 <property name="defaultSchema">TESTDB</property>

user    用户配置节点
name    逻辑用户名，客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。
password     客户端登录MyCAT的密码
schemas      数据库名，这里会和schema.xml中的配置关联，可配置多个，多个用逗号分开，例如:db1,db2
privileges   配置用户针对表的增删改查的权限
readOnly mycat   逻辑库所具有的权限。true为只读，false为读写都有，默认为false

##注意
1.#server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
2.#逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！

6.修改 mycat 配置文件/apps/mycat/conf/schema.xml

schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。

[root@localhost ~]#vim  /apps/mycat/conf/schema.xml
#删除所有内容，重新写入以下
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        #schema标签：数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应。
        #name:逻辑数据库名，与server.xml中的schema对应；
        #checkSQLschema: 数据库前缀相关设置，这里为false；
        #sqlMaxLimit:  select时默认的limit，避免查询全表，否则可能会遇到查询量特别大的情况造成卡 死；
        #dataNode:表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name
</schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        #dataNode标签: 定义mycat中的数据节点，也是通常说的数据分片，也就是分库相关配置
        #name: 定义数据节点的名字，与table中dataNode对应
        #datahost: 物理数据库名，与datahost中name对应，该属性用于定义该分片属于哪个数据库实例
        #database: 物理数据库中数据库名，该属性用于定义该分片属性哪个具体数据库实例上的具体库
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        #dataHost标签： 物理数据库，真正存储数据的数据库
        #name: 物理数据库名，与dataNode中dataHost对应
        #maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标  签都会使用这个属性的值来实例化出连接池的最大连接数
        #minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小
        #balance: 均衡负载的方式
       
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                  #writeType: 写入方式
                  #dbType: 数据库类型
                  #dbDriver指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。用 native 的话，因为这个值执行的是二进制的 mysql 协议，所以可以使用 mysql 和maridb。其他类型的数据库则需要使用 JDBC 驱动来支持。
                  #switchType:  “-1” 表示不自动切换； “1” 默认值，自动切换； “2” 基于 MySQL主从同步的状态决定是否切换心跳语句为 show slave status； “3” 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1）心跳语句为 show status like ‘wsrep%’.
                <heartbeat>select user()</heartbeat>
                #heartbeat: 心跳检测语句，注意语句结尾的分号要加
                <writeHost host="host1" url="192.168.59.113:3306" user="root" password="123456">
                #host：用于标识不同实例，一般 writeHost 我们使用*M1，readHost 我们用*S1。
                #url：后端实例连接地址。Native：地址：端口 JDBC：jdbc的url
                #user：后端存储实例需要的用户名字
                #password:后端存储实例需要的密码
                 <readHost host="host2" url="192.168.59.112:3306" user="root" password="123456"/>

                </writeHost>
        </dataHost>
</mycat:schema>


#schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1" 
#schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型，目前的取值有 4 种：
##balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上,即读请求仅            发送到writeHost上
##balance="1"：一般用此模式，读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1, S2 都参与 select 语句的负载均衡
##balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发
##balance="3"：读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发wiriterHost 对应的 readhost 执行, writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有

###writeHost和readHost标签，这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。在一个dataHost内可以定义多个writeHost和eadHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去   
               
#PS：Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="192.168.91.100:3306" user="root" password="Admin@123">
                 <readHost host="host2" url="192.168.91.101:3306" user="root" password="Admin@123"/>
                </writeHost>
        </dataHost>
</mycat:schema>

7. 主服务器上授权

[root@localhost ~]#mysql -uroot -p123123
#授权
GRANT ALL ON *.* TO 'root'@'192.168.91.%' IDENTIFIED BY 'Admin@123';

#查看创建成功
use mysql;
select user,host from user;

8.重启mycat服务，客户机连接mycat

（1）在mycat服务器上，重启mycat服务,查看启动日志，文末出现successfully
 [root@localhost ~]#mycat restart  
 [root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
 INFO   | jvm 1    | 2021/12/09 21:15:40 | 
INFO   | jvm 1    | 2021/12/09 21:15:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper  | 2021/12/09 21:16:38 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2021/12/09 21:16:39 | <-- Wrapper Stopped
STATUS | wrapper  | 2021/12/09 21:16:40 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/12/09 21:16:40 | Launching a JVM...
INFO   | jvm 1    | 2021/12/09 21:16:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/12/09 21:16:40 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/12/09 21:16:40 | 
INFO   | jvm 1    | 2021/12/09 21:16:41 | MyCAT Server startup successfully. see logs in logs/mycat.log

(2)查看3306端口，可以监听到主从服务器（192.168.59.113、192.168.59.112）
 [root@localhost ~]# ss -antp|grep 3306
 LISTEN     0      128         :::3306                    :::*                   users:(("java",pid=74936,fd=185))
TIME-WAIT  0      0        ::ffff:192.168.59.114:37344                ::ffff:192.168.59.112:3306               
ESTAB      0      0        ::ffff:192.168.59.114:58160                ::ffff:192.168.59.113:3306                users:(("java",pid=74936,fd=195))

 
（3）在客户机上登录mycat，这时可以不加端口直接进入数据库了
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.59.114
#看是否能查到表   
create table student (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
show databases;
use TESTDB;
show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

#查看当前的查询来自哪台服务器，可以看到查询功能来自id为2的从服务器
select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

9.客户端测试读写分离

（1）在主从服务器上都打开通用日志
[root@localhost ~]#mysql -uroot -p123123
#打开通用日志
set global general_log=1;
#查看通用查询日志是否开启
show variables like 'general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+

set global general_log=1;
show variables like 'general%';

（2）在主从服务器上实时查看通用日志
[root@localhost ~]#tail -f /usr/local/mysql/data/localhost.log

（3）在客户机上的表中插入数据，并查看主从服务器实时日志，可以看到只有主服务器上有日志变化显示
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.59.114
insert into teachers values(5,'Xiao Ming',46,'F');

（4）在客户机上select查表，并查看主从服务器实时日志，可以看到只有从服务器上有日志变化显示，从而实现了读写分离
select * from teachers;