公司注册网站模板,快手服务商平台,提交收录网站,dede网站地图html一. MySQL基本知识
1. 目录的功能
通用 Unix/Linux 二进制包的 MySQL 安装下目录的相关功能
目录目录目录binMySQLd服务器#xff0c;客户端和实用程序docs信息格式的 MySQL 手册manUnix 手册页include包括#xff08;头#xff09;文件lib图书馆share用于数据库安装的错…一. MySQL基本知识
1. 目录的功能
通用 Unix/Linux 二进制包的 MySQL 安装下目录的相关功能
目录目录目录binMySQLd服务器客户端和实用程序docs信息格式的 MySQL 手册manUnix 手册页include包括头文件lib图书馆share用于数据库安装的错误消息、字典和 SQLsupport-files其他支持文件
2.文件功能 db.opt 数据库选项 database option 作用告诉我们这个库使用的字符串是什么 [rootsc-mysql sc]# cat db.opt
default-character-setutf8 默认字符集
default-collationutf8_general_ci 默认字符集对应的校对规则-排序的时候使用的索引 index 也是数据是描述数据的数据告诉我们数据存放的哪里一本里的目录-帮助我们可以快速的查询到数据提升查询的效率 使用默认的innodb存储引擎建表产生的文件 student_info.frm --》表结构文件 frame 框架student_info.ibd --》表的数据和索引 后缀ibd就是innodb data MySQL默认使用的存储引擎是innodb 将MySQL内存里的数据存放到磁盘将磁盘里的数据读取到内存 存储引擎是捆绑到表上的 使用myisam存储引擎建表产生的文件 rootsc 15:38 mysqlcreate table weicanyu(id int,name varchar(10)) enginemyisam;weicanyu.frm --》表结构文件 frame 框架weicanyu.MYD --》myisam存储引擎存放数据的 dataweicanyu.MYI --》myisam存储引擎存放索引 index
3.进程间的关系
3.1 三个进程 mysqld_safe ysqld_safe是mysqld的父进程管理mysqld mysqld MySQL的主要进程 mysql是客户端的连接程序-连接到mysqld
3.2 配置文件my.cnf 3.3 进程的6种通信方式 socket 是进程是进程之间通信的方式 --槽 1.文件socket socket/data/mysql/mysql.sock-实现mysql和mysqld的通信 2.网络socket 格式ipport 192.168.0.163:3309-网络中通过ip地址找到对方是实现不同的电脑之间的不同的进程之间的通信的 pipe 管道 信号处理异步事件的方式 kill -9 信号量进程间通信处理同步互斥机制-相当于锁 共享内存 消息队列
4. 数据类型
4.1 数值 整数 int Unsigned 无符号数 如4050100 signed 有符号数 如100-50 类型存储字节最小值有符号最小值无符号最大值有符号最大值无符号tinyint1-1280127255smallint2-3276803276765535mediumint3-83886080838860716777215int4-2147483648021474836474294967295bigint8-2630263-1264-1 float 浮点型 有近似值不精准 decimal 定点型 数值非常精确不会有近似值 函数
4.2 字符串 char和varchar char和varchar的区别 1.存储上的区别 char是定长即存储的时候会根据指定的字符数量存储不足字符数量的会以空格进行填充而varchar是变长只是在后面多填充一个空格。 2.取数据的区别 varchar还是char类型在读取的时候都会删除自动填充的空格不是自动填充的空格varchar会保留 3.允许存储的长度 char长度0255varchar长度065535 text longtexttinytextmediumtexttext blob 二进制的文本图片音频视频 enum 枚举 set 集合 函数
4.3 日期和时间
year 年data 年月日 如出生time 小时分钟秒datatime 年月日小时分钟秒 如:考勤 datetime所能存储的时间范围为‘1000-01-01 00:00:00.000000’ 到 9999-12-31 23:59:59.999999对于datatime不做任何改变基本上是原样输入和输出 timestamp 时间戳 年月日时分秒 timestamp所能存储的时间范围为‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。对于timestamp它把客户端插入的时间从当前时区转化为UTC世界标准时间进行存储。查询时将其又转化为客户端当前时区进行返回。注册账号交易下订单
5.密码破解
5.1 方法一 第1步停止MySQL进程的运行 [rootmysql-2 mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS! 第2步修改配置文件 [rootmysql mysql]# vim /etc/my.cnf
[mysqld]
usermysql #指定启动MySQL进程的用户
skip-grant-tables #跳过密码验证
#validate-passwordoff #需要禁用密码复杂性策略第3步启动MySQL进程 [rootmysql mysql]# service mysqld start 启动MySQL进程第4步登录MySQL不接密码 [rootmysql-2 mysql]# mysql -uroot -p
mysql flush privileges; 刷新权限会加载原来没有加载的权限表--》用户名和密码所在的表user等
mysql set password for rootlocalhost Sanchuang1234#; --》修改密码指定用户名为rootlocalhost第5步重新修改mysql的配置文件 [mysqld]
socket/data/mysql/mysql.sock
#usermysql --》注释掉
#skip-grant-tables --》注释掉第6步刷新服务 [rootmysql-2 mysql]# service mysqld restart #重新刷新服务第7步验证修改密码是否成功 [rootmysql-2 mysql]# mysql -uroot -pSanchuang1234#5.2 方法二 就是使用其他的管理员账号给别的用户重新设置密码可以在SQLyog里操作 SET PASSWORD FOR rootlocalhost Sanchuang123#;
alter user rootlocalhost identified by Sanchuang123#;6 三个类别 数据定义语言DDL DDLData Definition Languages语句数据定义语言这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 c r e a t e 、 d r o p 、 a l t e r create、drop、altercreate、drop、alter 等。 数据操纵语句DML DMLData Manipulation Language语句数据操纵语句用于添加、删除、更新和查询数据库记录增删改查并检查数据完整性常用的语句关键字主要包括 i n s e r t 、 d e l e t e 、 u d p a t e insert、delete、udpateinsert、delete、udpate 和 s e l e c t selectselect 等。 数据控制语句DCL DCLData Control Language语句数据控制语句用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 g r a n t 、 d e n y 、 r e v o k e 、 c o m m i t 、 s a v e p o i n t 、 r o l l b a c k grant、deny、revoke、commit、savepoint、rollbackgrant、deny、revoke、commit、savepoint、rollback 等。
二. MySQL命令
1.基本命令 关键字不区别大小写库名和表名区分大小写 登录 mysql -uroot -p’123456’ -h 192.168.2.129 -p 3306 -p 指定密码 -h指定ip -P指定端口 show processlist 查看有哪些人已经连接到mysql里了 help 查询使用手册 select 查询操作 select version();查看MySQL的版本select now;查询当前时间select * from ejiao1; 查询ejiao1里面的任意字段select lengthfrom ejiao1统计字符储存空间消耗的空间字节select char_lengthfrom ejiao1统计字符的长度字符的个数 delete 删除操作 delete from city_name; 删除整个表里的数据delete from city_name where id9; 添加了条件的删除语句 if exists关键字 create database if exists pzz;
create databse if not exists pzz;使用后如果创建的数据库可能已经存在或者是要删除的数据库可能不存在不进行报错
2. 用户管理
2.1 创建用户 create user root(none) 16:36 mysqlcreate user liaobo% identified by 123456;cali‘%’ % 是通配符代表任意的字符串 查看所有用户 root(none) 18:31 mysqlselect user from mysql.user;2.2 删除用户 drop user 删除用户 root(none) 16:37 mysqldrop user liaobo% 2.3 用户权限 权限全局权限数据库权限表权限 全局权限 如mysql.user 存放所有的用户名和密码数据库权限 如mysql.db 只能操作某个库表权限 如mysql.table_priv 只能操作某个表mysql.columns 只能对某个列进行操作 grant 授权 root(none) 16:37 mysqlgrant all on *.* to liaobo%;grant 是mysql里授权的命令 all 代表授予所有的权限 select insert update delete等 on . 第1个* 代表库 第2个*代表表 所有表所有库 o ‘cali’‘%’ 给具体的用户 with grant option 授予授权的权力 grant all on *.* to tang3% with grant option;flush privileges 刷新权限 revoke 取消权限 格式revoke 权限 on 库/表 from 用户名 取消某用户在库或表上面的的什么权限 revoke all on *.* from heyachen%;2.4 四个默认库 information_schema 信息库 数据字典库–》资产库存放MySQL里的资产例如有多少表库视图触发器存储过程等 information_schema是一个信息数据库它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名数据库的表表栏的数据类型与访问权 限等 数据字典–》元数据描述其他数据的数据 中央情报局统计局收录了整个MySQL里的信息能统计的一切信息 performance_schema 性能架构库 主要用于收集数据库服务器性能参数。 执行某些操作会有性能相关的参数 存放MySQL运行起来后相关的数据例如登陆用户变量内存的消耗等 sys MySQL系统库 Sys库所有的数据源来自performance_schema。目标是把performance_schema的把复杂度降低让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。 mysql 存放的是MySQL程序相关的表登录用户表、时间相关表、db、权限表 mysql的核心数据库类似于sql server中的master表主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
3. 库命令
3.1 建库 create database 建库 root(none) 15:11 mysqlcreate database sc;3.2 删除库 drop database 删除库 rootxieshan 16:00 scmysqldrop database AOJIAO;3.3 查看库 show databases 展示库列表-相当于ls root(none) 15:11 mysqlshow databases;use sc 进入sc这个库-相当于cd
4. 表命令
4.1 建表 create table 建表 rootsc 15:14 mysqlcreate table student_info(id int,name varchar(20),sex char(10));rootxieshan 15:23 scmysqlcreate table wang(- id int not null primary key,- name varchar(20) not null,- sex char(1) )- ;not null 表示不能为空这个字段primary key 表示这个字段为主键这个字段里的数据不能重复primary 主要的create temporary table 建临时表 临时表 只是临时在内存里存在,使用show tables查看不到用户退出MySQL马上会删除用户新建的临时表其他用户不能看到你创建的临时表只能自己可见。
4.2 查看表 show tables 查看表名 rootsc 15:14 mysqlshow tables;
rootsc 15:14 mysqlshow creat table student_info;desc 查看表的结构 rootsc 15:16 mysqldesc student_info; show create database 查看表的结构 root(none) 14:45 scmysqlshow create database aojiao;查看表内容 root(none) 14:45 scmysqlselect * from student;4.3 insert插入数据
rootxieshan 15:07 scmysqlinsert into ejiao1(id,name) values(1,hepang);
insert into teacher values (1,LiHong,man);
insert into teacher (id,name) values (2,WangQin);
insert into teacher values (3,Bob,man),(4,LiLi,woman); #一次性插入多条数据4.4 alter修改数据 修改表结构 增加字段 alter table Students add sex char(1) ;add 删除字段 drop 修改字段的类型或者长度 modify 修改类型 修改名字 alter table Students change name username varchar(30);change 修改数据库字符集 alter database song default character set utf8mb4;
4.5 删除表与数据 drop table 删除表与字段 删除表
rootxieshan 16:00 scmysqldrop table chenyulin;删除字段
alter table teacher drop yeardelete删除数据 delete from teacher where id 1;
delete from teacher; #删除所有数据like 复制表的结构 roothunan 15:20 scmysqlcreate table new_city like city_name;as 复制一个表的结构和数据 roothunan 15:21 scmysqlcreate table new_city2 as select * from city_name;三. 变量和选项
1. 变量 系统变量 使用进行设置 SET auto_increment_offset 10, -- 设置自增起始值auto_increment_increment10; -- 每次自增10自定义变量 使用进行设置 roothunan 15:04 scmysqlset sg liangliang;
roothunan 15:05 scmysqlinsert into city_name(name) values(sg)2.选项约束
选项-字段属性
signed 有符号整数 unsigned 无符号整数not null 不能为空zerofill 补零自动转为为unsignedprimary key 主键 不允许为空不允许重复分类复合主键单列主键设置主键的好处给表建立索引–》主键索引在查询数据的时候先查询索引然后根据索引去查询数据速度会非常快 unique 唯一性约束 不允许重复空值只能出现一次以出现很多NULL值default 默认值auto_increment 自增 初始值为1 步长值偏移量默认为1if not exists 如果不存在不显示错误comment 注释-sqlyog查看foreign key 外键 多表连接查询使用缺点产生临时表进行关联消耗内存空间和cpu
3. 存储引擎engine
3.1 基本存储引擎
roothunan 16:02 scmysqlshow engines; 查看存储引擎InnoDB 默认存储引擎 表结构t1.frm 索引和数据t1.ibd MyISAM 表结构t1.frm 索引t1.MYI 数据:t1.MYD csv csv文件 是一个文本文件里面的字段以逗号作为分割符号 --》文件存储的文件 --》数据分析:数据处理和清洗 memory 数据保存在内存里特别适用于适用临时表的场景–》数据分析 blackhole 解决了主从复制架构里让很多的从服务器直接到配置了blackhole存储引擎的master上拿二进制日志让最上层的master的负载降低-》其实就是帮忙主从架构传递二进制日志自己不执行二进制日志只是传递
3.2 Innodb和myisam存储引擎的优缺点
1) 事务支持 **MyISAM不支持事务而InnoDB支持。**InnoDB的AUTOCOMMIT默认是打开的即每条SQL语句会默认被封装成一个事务自动提交这样会影响速度所以最好是把多条SQL语句显示放在begin和commit之间组成一个事务去提交。
MyISAM是非事务安全型的而InnoDB是事务安全型的默认开启自动提交宜合并事务一同提交减小数据库多次提交导致的开销大大提高性能。
2) 存储结构 MyISAM每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB所有的表都保存在同一个数据文件中也可能是多个文件或者是独立的表空间文件InnoDB表的大小只受限于操作系统文件的大小一般为2GB。
3) 存储空间 MyISAM可被压缩存储空间较小。支持三种不同的存储格式静态表(默认但是注意数据末尾不能有空格会被去掉)、动态表、压缩表。
InnoDB需要更多的内存和存储它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
4) 可移植性、备份及恢复 MyISAM数据是以文件的形式存储所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB免费的方案可以是拷贝数据文件、备份 binlog或者用 mysqldump在数据量达到几十G的时候就相对痛苦了。
5) 表锁差异 MyISAM只支持表级锁用户在操作myisam表时selectupdatedeleteinsert语句都会给表自动加锁如果加锁以后的表满足insert并发的情况下可以在表的尾部插入新的数据。 InnoDB支持事务和行级锁是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁只是在WHERE的主键是有效的非主键的WHERE都会锁全表的。
4.数据库三个核心性能指标 TPS适用innodb每秒处理的事务数 Transactions Per Second每秒传输的事物处理个数即服务器每秒处理的事务数。 TPS包括一条消息入和一条消息出加上一次用户数据库访问。业务TPS CAPS × 每个呼叫平均TPS TPS是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时收到服务器响应后结束计时以此来计算使用的时间和完成的事务个数。 一般的评价系统性能均以每秒钟完成的技术交易的数量来衡量。系统整体处理能力取决于处理能力最低模块的TPS值。 QPS同时适用与InnoDB和MyISAM 引擎 每秒处理的查询数 每秒查询率QPS是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准在因特网上作为域名系统服务器的机器的性能经常用每秒查询率来衡量。 对应fetches/sec即每秒的响应请求数也即是最大吞吐能力。 IOPS 每秒磁盘进行的I/O操作次数 IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数)是衡量磁盘性能的主要指标之一。IOPS是指单位时间内系统能处理的I/O请求数量一般以每秒处理的I/O请求数量为单位I/O请求通常为读或写数据操作请求。
5.字符集
5.1 定义
字符集定义了字符以及字符的编码规定了字符在数据库中的存储格式比如占用多少空间支持那些字符等等。
5.2 MySQL常用字符集
字符集长度说明latin11MySQL默认字符集最早由MySQL使用的字符集ASCII1共收录128个字符包括空格、标点符号、数字、大小写等UTF-83国际通用字符集收录地球上所有编码并且在不断扩充。采用变长编码的方式utf8mb44完全兼容UTF-8用四个字节存储更多的字符GBK2支持中文但是不是国际通用字符集
5.3 MySQL字符集操作 查看字符集 roothunan 16:54 mysqlshow character set; 查看字符集
roothunan 16:54 mysqlSHOW variables like %CHARACTER%; 查看你正在使用哪些字符集更改默认字符集 alter database 表名 default character set utf8;文本类型的数据会牵涉到字符集-varchar char text 继承库-表-列
四. SQL语句
1. selcet 查询
1.1 基本查询
select id,name from student;distinct 查询去重 select distinct sex from student;空值查询 select * from student where major is null;
select * from student where major is not null;2.2 where条件查询 等于、不等与或! select * from student where sexman;
select * from student where majorMath;大于、小于、大于等于、小于等于 select * from student where age 20;
select from student where age10 and sexman;与and、或or、非not、异或xor 介于A和B之间between A and B select * from student where age between 10 and 20;2.3 like模糊查询 select模糊查询所使用的是like与not like关键字 select * from student where name like Li_;
select * from student where name like Li%;
select * from student where name not like Li%;%表示0个或者多个字符_只表示一个任意的字符 两个\转义
2.4 order by排序查询
select * from student order by age; #默认升序
select * from student order by age desc; #desc降序limit 限制数量 如limit 3 限制三个limit 35 取第三个以后的5个不包括第三个 SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
按照工资从高到低的顺序仅返回前3个结果2.5 group by分组查询 group by单独使用时后面跟字段名即可 SELECT * FROM user_info GROUP BY name;GROUP BY 也可以和 GROUP_CONCAT() 函数一起使用,这样可以把分组的某一字段的每个字段值都显示 SELECT 分组的字段名, GROUP_CONCAT(传入需要全部显示的字段名)
FROM 表名
GROUP BY 分组的字段名;在数据统计时GROUP BY 关键字经常和聚合函数一起使用。聚合函数包括 COUNT()SUM()AVG()MAX() 和 MIN() COUNT() 用来统计记录的条数SUM() 用来计算字段值的总和AVG() 用来计算字段值的平均值MAX() 用来查询字段的最大值MIN() 用来查询字段的最小值。 SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex; --统计指定的字段的数量having 可以用来过滤分组的一些信息类似where SELECT b, count(*) as c from t1 GROUP BY b HAVING b1002. 多表连接查询
连接类型内连接外连接交叉连接
2.1 内连接
基于两个或多个表之间的关联条件仅返回在所有关联表中都存在匹配的行不显示不满足条件的行
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column table2.column; 2.2 外连接 左连接left join 用于返回左表中的所有行以及与右表中匹配的行。如果右表中没有匹配的行则右表的列将显示为NULL SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column table2.column;右连接right join右连接用于返回右表中的所有行以及与左表中匹配的行。如果左表中没有匹配的行则左表的列将显示为NULL。 SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column table2.column;2.3 交叉连接(笛卡尔积)
交叉连接用于返回两个表的笛卡尔积即将左表的每一行与右表的每一行进行组合
SELECT columns
FROM table1
CROSS JOIN table2;2.4 join 语句 using关键字 USING关键字用于指定连接的列这些列在连接的两个表中具有相同的名称。USING关键字仅用于指定连接的列而不需要在列名前面指定表名或表别名。USING关键字将根据指定的列进行连接并自动排除重复列。 SELECT *
FROM table1
JOIN table2 USING (common_column);on关键字 ON关键字用于指定连接的条件可以根据多个列或表达式进行连接。ON关键字要求在连接条件中明确指定连接的列包括表名或表别名。ON关键字提供了更灵活的连接选项可以在连接条件中使用各种逻辑运算符、比较运算符和函数。 SELECT *
FROM table1
JOIN table2 ON table1.column1 table2.column2;2.5 where
#从student表中查询与“吴刚”来源地相同的所有学生学号、姓名和所属院系。
SELECT st1.ID AS 学号, st1.name AS 姓名, st1.institute AS 所属院系
FROM student AS st1, student AS st2
WHERE st2.name吴刚
AND st1.originst2.origin;3. 子查询
如果一个查询语句嵌套在另一个查询语句里面那么这个查询语句就称之为子查询子查询不是必须的可以使用其他的语句代替 根据位置不同可以分为where型、from型、exists型 根据结果集的行列数不同 标量子查询结果及只有一行一列列子查询结果及只有一列多行行子查询结果集有一行多列或多行多列表子查询结果集一般为多行多列 标量子查询Scalar Subquery标量子查询返回的是单个值可以作为查询语句中的一个常量使用。列子查询Column Subquery列子查询返回的是一列数据可以和主查询的结果进行比较或者连接行子查询Row Subquery行子查询返回的是一行数据可以作为一个整体进行处理或者与其他行进行比较表子查询Table Subquery表子查询返回的是一个表或视图可以嵌套在另一个查询语句中使用。
3.1 where型
where型子查询是将子查询语句放在where后面
SELECT name
FROM company
WHERE id (SELECT comid FROM menber WHERE name 小刘)3.2 from型
from子查询是把查询结果当成表
SELECT c.name
FROM (SELECT * FROM menber WHERE name 小刘) t1,company c
WHERE t1.comid c.id3.3 exists型
exists是如果子查询语句能够至少查询到一条数据就返回TRUE否则就返回FALSE
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno tc.cid)3.4 in、all、any in in的意思就是指定的一个值是否在这个集合中如何在就返回TRUE否则就返回FALSE了。 SELECT * FROM tb_bookWHERE row_no IN (SELECT row_no FROM tb_row);all all的意思是“对于子查询返回的列中的所有值如果比较结果为TRUE则返回TRUE”。 SELECT * FROM tb_bookWHERE row_no ALL(SELECT row_no FROM tb_row);any any关键词的意思是“对于子查询返回的列中的任何一个数值如果比较结果为TRUE就返回TRUE”就是任意一个只要条件满足任意的一个就返回TRUE。 SELECT * FROM tb_bookWHERE row_no ANY(SELECT row_no FROM tb_row);3.5 不相关子查询
子查询的查询条件不依赖于父查询称为不相关子查询子查询可以单独运行
3.6 相关子查询
如果子查询的查询条件依赖于父查询这类子查询称为相关子查询子查询不可以单独运行并且先运行外查询再运行子查询。
#找出每个学生超过他自己选修课程的平均成绩的课程号。
SELECT Sno,Cno
FROM SC x
WHERE Grade (SELECT AVG(Grade)FROM SC yWHERE y.Snox.Sno);#查询工资高于其所在岗位的平均工资的员工
select * from emp e where sal (select avg(sal) from emp where jobe.job);4. 索引
4.1 相关知识 作用加快查询的速度 缺点 修改了数据mysql会去修改索引的导致mysql的性能下降维护索引的开销是比较大 查询的对象对于主键及唯一性约束MySQL自动创建索引 重复性低的具有高选择性的列适合建立索引 底层技术 B 树索引 btree索引 哈希hash索引
4.2 hash索引和B树索引的区别 4.3 相关命令 创建索引 create index 索引名 on 表名alter table 表名 add key字段名 删除索引drop index 索引名 on 表名 索引类型 主键索引primary 唯一 not null 唯一性索引unique 全文索引fulltext 普通索引 复合索引 最左匹配原则
五.DML和事务
1.DML基本语句 insert 插入语句出现主键冲突不会插入 valuse 字句 除了使用字面量还可以使用函数计算标量子查询等 replace 替代语句出现主键冲突会替换 update 更新语句 需要接where条件不然全部都会更新 delete 删除语句 需要接where条件不然全部都会一行一行地删除会产生二进制日志 truncate 整张表删除速度更快不会产生二进制日志
2.数据库事务
2.1 数据库中的操作 commit 自动提交功能设置 conn pymysql.connect(user root,passwd 1999,db dep2,host 127.0.0.1,port 3306,charset utf8,autocommit True # 自动提交确认
)rollback
2.2 事务的ACID特性 2.3 并发事务的4个问题
脏读不可重复读-重点是修改幻读-新增或删除丢失更新
2.4 解决并发问题的4个隔离级别 读未提交 读已经提交 可重复读 可串行化
2.5 锁
2.5.1 根据行为划分 读锁 READ当前会话和其它会话都可以读表但是不能修改表 写锁 WRITE当前会话可以读写表但是其它会话既不能读也不能写 注意当使用lock tables语句时其后的操作只能访问被锁定的表而不能访问未被锁定的表 共享锁 SELECT * FROM parent WHERE NAME Jones LOCK IN SHARE MODE;排他锁 START TRANSACTION;
SELECT * FROM players_copy1 FOR UPDATE;2.5.2 根据颗粒度划分 表锁 读锁 read 是共享锁当前会话能读其他会话也能读都不能写 写锁 read 是排他锁当前会话能读写其他会话不能读也不能写 行锁 读锁 read --》其他事务可以读不能写 --》共享锁写锁 write --》其他事务不能读也不能写 --》排他锁
2.5.3 其他的锁 死锁 死锁 在竞争资源的时候必须满足2个条件多个进程同时访问A进程获得了1个条件B进程获得了第2个条件A和B都没有同时满足2个条件互相又都不释放已经掌握的条件导致2个进程僵死都不能访问。 如何避免死锁 1.设计流程必须先拿到第1个条件然后才可以去拿第2个条件 2.设计一个单独的进程去检查是否发生死锁如果发生了根据一个算法权衡利弊考虑杀死一个进程释放资源。 活锁 在竞争资源的时候一直得不到活活被锁住一直等待 悲观锁 悲观锁正如其名具有强烈的独占和排他特性。 乐观锁 乐观锁机制避免了长事务中的数据库加锁开销操作员 A和操作员 B 操作过程中都没有对数据库数据加锁大大提升了大并发量下的系统整体性能表现 互斥锁
六.日志 为什么需要日志日志用来做什么 1.用来排错 2.用来做数据分析 3.了解程序的运行情况是否健康–》了解MySQL的性能运行情况 1.错误日志 log_err 功能 登录失败会记录到错误日志 配置文件出错也会记录 启动过程出问题也会记录 存放目录和查看 默认开启存放在数据目录下/data后缀名是.err 查看开启状态 root(none) 15:42 mysqlshow variables like %err%;在/etc/my.cnf中配置erro
2.通用日志 general_log 功能 记录所有的SQL操作但是会消耗大量的磁盘空间cpu内存 存放目录和查看 默认不开始默认存放在数据目录下名字是主机名.log 查看开启状态 root(none) 15:52 mysqlshow variables like general_log;开启并在/etc/my.cnf中配置存放路径 临时开启 mysql set global general_log 1;永久开启 #开启general log
general_log
#配置存放路径
general_log_file/data/mysql/sanchuang_mysql_ge.log3. 慢日志 slow query log 功能 记录消耗时间比较长的SQL语句为数据库性能提升提供了线索 面试题最近数据库压力负载特别高客户反应网站或者应用使用特别慢领导要求你查明原因 1.SQL语句需要优化在数据库里启用慢日志找出执行时间比较长的SQL 2.业务量太大了硬件已经达到极限了 top、glances、dstat 存放目录和查看 默认不开始存放在数据目录下名字主机名slow.log 查看开启状态 root(none) 15:52 mysqlshow variables like long_query%;查看最低记录时间默认是10秒 mysql show variables like %long_query%;开启并在/etc/my.cnf中配置存放路径 #开启slow query log
slow_query_log 1
#配置最低记录时间
long_query_time 0.001 4. 二进制日志 log_bin 二进制的作用 1.恢复数据增量 2.主从复制需要使用 3、日志审计场景用户可以通过二进制日志中的信息来进行审计判断是否有对数据库进行注入攻击。 存放目录/etc/my.cnf和查看日志 默认不开始存放在数据目录下名字主机名-bin.00000* sc-mysql-bin.index文件 存放累计有多少个二进制文件的 查看开启状态 root(none) 15:59 mysqlshow variables like log_bin;查看哪个二进制日志正在记录 root(none) 16:47 mysqlshow master status;查看二进制日志内容默认一个二进制文件最大只能一个1G [rootmaster mysql]# mysqlbinlog master-bin.000001 开启二进制日志 #开启二进制日志
log_bin
server_id 1server_id 是服务器的唯一标识在主从复制的时候使用每天服务器的id不能一样不然会导致主从复制失败 删除二进制日志 清除所有的二进制日志 rootTENNIS 15:15 scmysqlreset master;purge 清除 #将指定时间之前的日志清理
rootTENNIS 15:15 scmysqlpurge binary logs before ‘2023-08-07’;
#将指定日志文件之前的日志清理
rootTENNIS 15:15 scmysqlpurge binary logs to mysql-bin.000003;自动清除日志 产生二进制文件 #重启MySQL
mysqlservice mysqld restart
#刷新logs
mysqlflush logs刷盘时机 是指什么时候通过什么策略将内存日志写入到磁盘中 sync_binlog0 表示刷新binlog时间点由操作系统自身来决定操作系统自身会每隔一段时间就会刷新缓存数据到磁盘这个性能最好。–》容易丢失数据 sync_binlog1MySQL默认刷盘时机 表示每次事务提交都要调用fsync()刷新binlog写入到磁盘。–》能快速的存储数据不容易丢失数据 sync_binlogN 表示N个事务提交才会调用 fsync()进行一次binlog刷新写入磁盘。 SQL注入 通过把SQL命令插入到Web表单提交或输入域名或页面查询的查询字符串最终达到欺骗服务器执行恶意的SQL命令。 用户黑客可以提交一段数据库查询代码根据程序返回的结果获得某些他想得知的数据这就是所谓的SQL Injection即SQL注入。
5.redo和undo innodb存储引擎的架构 redo log和undo log是innodb 存储引擎产生的日志先执行redo然后执行undo redo log记录的是脏数据的变化commit事务执行不成功就会redo–》buffer pool里的 作用 MySQL意外宕机重启也不要紧。只要在重启时解析redo log中的事务而后重做一遍。将Buffer Pool中的缓存页重作成脏页。后续再在合适的时机将该脏页刷入磁盘便可。 存放位置/data/mysql ib_logfile0和ib_logfile1 undo log记录某数据被修改前的值rollback事务执行撤销 作用 方便回滚 rollback --》相当于做了一个快照备份 存放位置/data/mysql ibdata1
七. 备份和还原
1.备份方案 完全备份 备份了全部的内容备份的东西比较多的话时间比较长 增量备份 备份的好处是每次备份需要备份的数据较少耗时较短占用的空间较小坏处是数据恢复比较麻烦 差异备份 差异备份最开始进行一次完全备份但是和增量备份不同的是每次差异备份都备份和原始的完全备份不同的数据
2.热备
MySQL服务是运行的最普遍使用的方式
2.1 mysqldump 备份库 #全部备份
mysqldump -uroot -pSanchuang1234# --all-databases all_db.sql
#备份几个库
mysqldump --databases db1 db2 db3 dump.sql
mysqldump -uroot -pSanchuang1234# --databases hunan liangliang hunan_liangliang.sql备份表 mysqldump test t1 t3 t7 dump.sql
mysqldump -uroot -pSanchuang123# TENNIS PLAYERS tennis_players.sql2.2 xtrabackup
xtrabackup备份过程中先备份innodb表再备份非innodb表 3.冷备
关闭MySQL服务不关闭机器 scp tar
4.异地备份
4.1 scp
4.2 rsync和sersync 简介 rsync 全称 remote synchronize即 远程同步Sersync可以记录下被监听目录中发生变化的包括增加、删除、修改具体某一个文件或者某一个目录的名字然后使用rsync同步的时候只同步发生变化的文件或者目录因此效率更高。主要应用场景为数据体积大并且文件很多 实验操作 在备份服务器上操作 1、在备份服务器上创建备份文件夹/backup mkdir /backup2、关闭 selinux和Linux防火墙 关闭 selinux
[rootsc-mysql2 backup]# getenforce
Permissive
[rootsc-mysql2 backup]# vim /etc/selinux/config
SELINUXdisabled
关闭防火墙
[rootsc-mysql2 backup]# service firewalld stop3、安装rsync服务端软件并且设置开机启动 [rootsc-mysql2 backup]# yum install rsync xinetd -y
[rootsc-mysql2 backup]# vi /etc/rc.d/rc.local # #设置开机启动
/usr/bin/rsync --daemon --config/etc/rsyncd.conf # 添加开机启动[rootsc-mysql2 backup]# chmod x /etc/rc.d/rc.local
[rootsc-mysql2 backup]# systemctl start xinetd #启动xinetdxinetd是一个提供保姆服务的进程rsync是它照顾的进程 独立的服务sshdhcpmysql 非独立的服务非独立的服务需要依赖其他的服务来管理rsync就是一个非独立的服务依赖xinetd来管理 4、创建rsyncd.conf配置文件 [rootsc-mysql2 backup]# vim /etc/rsyncd.conf 添加下面的配置
uid root
gid root
use chroot yes
max connections 0
log file /var/log/rsyncd.log
pid file /var/run/rsyncd.pid
lock file /var/run/rsync.lock
secrets file /etc/rsync.pass
motd file /etc/rsyncd.Motd
[back_data] #配置项名称自定义path /backup #备份文件存储地址 --需要提前在备份服务器上新建不然后面会报错comment A directory in which data is storedignore errors yesread only nohosts allow 192.168.98.131 #允许的ip地址(数据源服务器地址)5、创建用户认证文件 $ vi /etc/rsync.pass # 配置文件添加以下内容添加允许传输用户和密码sunline:sunline # 格式用户名:密码可以设置多个每行一个用户名:密码sc:sc1234566、设置文件权限 $ chmod 600 /etc/rsyncd.conf #设置文件所有者读取、写入权限
$ chmod 600 /etc/rsync.pass #设置文件所有者读取、写入权限7、启动rsync和xinetd [rootsc-mysql2 backup]# /usr/bin/rsync --daemon --config/etc/rsyncd.conf
[rootsc-mysql2 backup]# ps aux|grep rsync
root 9455 0.0 0.0 114844 584 ? Ss 16:13 0:00 /usr/bin/rsync --daemon --config/etc/rsyncd.conf
root 9457 0.0 0.0 112824 988 pts/0 S 16:13 0:00 grep --colorauto rsync[rootsc-mysql2 backup]# systemctl start xinetd
[rootsc-mysql2 backup]# ps aux|grep xinetd
root 9425 0.0 0.0 25044 584 ? Ss 16:00 0:00 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid
root 9465 0.0 0.0 112824 988 pts/0 S 16:14 0:00 grep --colorauto xinetd8.查看rsync监听的端口号 [rootsc-mysql2 backup]# netstat -anplut数据源服务器操作 1、关闭 selinux和防火墙 [rootsc-mysql2 backup]# getenforce
Permissive
[rootsc-mysql2 backup]# vim /etc/selinux/config
SELINUXdisabled[rootsc-mysql2 backup]# service firewalld stop2、安装rsync客户端软件 3、安装rsync服务端软件
[rootsc-mysql2 backup]# yum install rsync xinetd -y
[rootsc-mysql2 backup]# vi /etc/rc.d/rc.local # #设置开机启动
/usr/bin/rsync --daemon --config/etc/rsyncd.conf # 添加开机启动[rootsc-mysql2 backup]# chmod x /etc/rc.d/rc.local 3、创建rsyncd.conf配置文件 [rootsc-mysql backup]# vim /etc/rsyncd.conf
log file /var/log/rsyncd.log
pid file /var/run/rsyncd.pid
lock file /var/run/rsync.lock
motd file /etc/rsyncd.Motd
[Sync]comment Syncuid rootgid rootport 873
[rootsc-mysql2 backup]# systemctl start xinetd #启动CentOS中是以xinetd来管理rsync服务的4、创建认证密码文件 [rootsc-mysql backup]# vim /etc/passwd.txt
sc123456 #编辑文件添加以下内容该密码应与目标服务器中的/etc/rsync.pass中的密码一致 [rootsc-mysql backup]# chmod 600 /etc/passwd.txt #设置文件权限只设置文件所有者具有读取、写入权限即可5、测试数据同步 数据源服务器192.168.98.131 到备份服务器192.168.98.139之间的数据同步 [rootsc-mysql backup]# rsync -avH --port873 --progress --delete /backup root192.168.98.139::back_data --password-file/etc/passwd.txt /backup(要备份的数据源目录 ) root192.168.98.139::back_data(rsyncd.conf文件配置名称)增加文件或者删除文件测试是否可以增量备份 接下来安装sersync工具实现自动的实时的同步–》安装到数据源服务器上 1、修改inotify默认参数inotify默认内核参数值太小 修改参数 临时修改
[rootsc-mysql backup]# sysctl -w fs.inotify.max_queued_events99999999
fs.inotify.max_queued_events 99999999
[rootsc-mysql backup]# sysctl -w fs.inotify.max_user_watches99999999
fs.inotify.max_user_watches 99999999
[rootsc-mysql backup]# sysctl -w fs.inotify.max_user_instances65535
fs.inotify.max_user_instances 65535永久修改参数
[rootsc-mysql backup]# vim /etc/sysctl.conf
fs.inotify.max_queued_events99999999
fs.inotify.max_user_watches99999999
fs.inotify.max_user_instances655352、安装sersync [rootsc-mysql backup]# yum install wget -y
[rootsc-mysql backup]# wget http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
[rootsc-mysql backup]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
[rootsc-mysql backup]# mv GNU-Linux-x86/ /usr/local/sersync3、创建rsync [rootsc-mysql backup]# cd /usr/local/sersync/
[rootsc-mysql sersync]# ls
confxml.xml sersync2备份配置文件防止修改错了不知道哪里出错好还原
[rootsc-mysql sersync]# cp confxml.xml confxml.xml.bak
[rootsc-mysql sersync]# cp confxml.xml data_configxml.xml
[rootsc-mysql sersync]# ls
confxml.xml confxml.xml.bak data_configxml.xml sersync2
#data_configxml.xml 是后面需要使用的配置文件4、修改配置 data_configxml.xml 文件 第24行后的配置
localpath watch/backupremote ip192.168.2.197 nameback_data/!--remote ip192.168.8.39 nametongbu/--!--remote ip192.168.8.40 nametongbu/--/localpathrsynccommonParams params-artuz/auth startfalse usersroot passwordfile/etc/passwd.txt/userDefinedPort startfalse port874/!-- port874 --timeout startfalse time100/!-- timeout100 --ssh startfalse/5、启动服务 [rootsc-mysql sersync]# PATH/usr/local/sersync/:$PATH
[rootsc-mysql sersync]# which sersync2
/usr/local/sersync/sersync2[rootsc-mysql sersync]# echo PATH/usr/local/sersync/:$PATH /root/.bashrc
[rootsc-mysql sersync]# sersync2 -d -r -o /usr/local/sersync/data_configxml.xml[rootsc-mysql backup]# ps aux|grep sersync验证去/backup目录下新建一些文件或者文件夹测试是否在备份服务器上可以看到 6、设置sersync监控开机自动执行 [rootsc-mysql backup]# vim /etc/rc.local
/usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/data_configxml.xml错误的问题 1.配置文件没有写内容或者打错 2.两边的服务器没有新建目录/backup --特别是备份服务器上没有新建/backup 排错查看日志 [rootsc-mysql2 backup]# cat /var/log/rsyncd.log
2022/08/18 16:13:19 [9455] rsyncd version 3.1.2 starting, listening on port 873
2022/08/18 16:35:08 [9472] name lookup failed for 192.168.2.196: Name or service not known
2022/08/18 16:35:08 [9472] connect from UNKNOWN (192.168.2.196)
2022/08/18 17:16:37 [9532] rsync: chroot /backup2 failed: No such file or directory (2) 错误信息5.恢复备份
5.1 mysqlbinlog 根据时间点来恢复 [rootsc-mysql ysql]#mysqlbinlog --start-datetime2005-04-20 9:55:00 --stop-datetime2005-04-20 10:05:00 /var/log/mysql/bin.123456 /tmp/mysql_restore.sql[rootsc-mysql ysql]#mysqlbinlog --start-datetime2020-08-13 11:50:07 --stop-datetime2020-08-13 11:50:24 /data/mysql/zabbix-4-centos7-bin.000002 |mysql -uyangst -pyang123#根据位置号来恢复 [rootsc-mysql ysql]# mysqlbinlog --start-position154 --stop-position1063 sc-mysql-bin.000005|mysql -uroot -pSanchuang123#八. 主从复制
1.主从复制的原理 1.首先在master主服务器上开启二进制日志 2.master上数据发生变化进行DML操作的时候会产生二进制日志 3.master上的dump线程会通知slave上的IO线程来拿二进制日志IO线程拿到二进制日志后会写入到slave上的中继日志然后SQL线程会去读取新产生的中继日志重演二进制日志里的操作从而达到slave和master上的数据一模一样实现数据的一致性。 从服务器的master-info文件的作用-给IO线程用的 记录master的ip连接过去复制二进制日志的账号和密码二进制日志的名字和位置号记录IO线程取日志结束的pos位置号日志文件的名字 从服务器的relay-log.info文件的作用-给SQL线程用的 记录SQL线程结束后的中继日志的文件名和位置点 主从复制的好处 1.做读写分离构建一个集群提高并发提升性能 2.备份
2.主从复制模式
2.1 异步复制 缺点 有延迟会丢失数据 步骤 1.安装好2台数据库服务器的系统然后安装好MySQL软件 2.在master上开启二进制日志 3.统一2台服务器的基础数据 [rootsc-master ~]# mysqldump -uroot -p123456 --all-databases all_db.SQL
[rootsc-master ~]# scp all_db.SQL root192.168.2.197:/root
[rootsc-slave ~]# mysql -uroot -p123456 all_db.SQL 4.清除所有的二进制日志因为有全备不需要二进制日志了 root(none) 11:18 scmysqlreset master;
root(none) 11:19 scmysqlshow master status;5.在master上新建一个授权用户给slave来复制二进制日志 root(none) 11:19 scmysqlgrant replication slave on *.* to liaobo% identified by 123456;6.在slave上配置master info的信息 CHANGE MASTER TO MASTER_HOST192.168.2.196 ,
MASTER_USERrenxj,
MASTER_PASSWORDSanchuang1234#,
MASTER_PORT3306,
MASTER_LOG_FILEsc-master-bin.000001,
MASTER_LOG_POS154;7.查看slave是否配置成功 root(none) 11:29 mysqlshow slave status\G;8.启动slave root(none) 11:30 mysqlstart slave;
root(none) 11:31 mysqlshow slave status\G;9.测试主从复制的效果主上面建表建库从上面查看 master.info 和 relay-log.info在哪里 [rootsc-slave mysql]# pwd
/data/mysql
[rootsc-slave mysql]# ls *.info
master.info relay-log.info
[rootsc-slave mysql]# cat master.info
[rootsc-slave mysql]# cat relay-log.info 2.2 半同步复制 步骤 1.在master上安装配置半同步的插件 root(none) 09:59 scmysqlINSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so;2.配置半同步复制超时时间 临时设置半同步复制超时时间为1秒默认为10秒 root(none) 11:18 scmysqlSET GLOBAL rpl_semi_sync_master_timeout 1;
root(none) 11:20 scmysqlSET GLOBAL rpl_semi_sync_master_enabled 1;永久配置半同步复制超时时间修改配置文件 [rootsc-master ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled1 #添加
rpl_semi_sync_master_timeout1000 # 1 second 添加3.刷新mysql服务 [rootsc-master ~]# service mysqld restart4.在从服务器上配置安装半同步的插件 root(none) 11:22 mysqlINSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so;
root(none) 11:23 mysqlSET GLOBAL rpl_semi_sync_slave_enabled 1;5.永久修改从服务器上的配置文件 [rootsc-slave ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled1 #添加6.刷新mysql服务 [rootsc-slave ~]# service mysqld restart7.在master和slave上执行SQL查看是否激活半同步 root(none) 11:27 scmysql
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE %semi%;
-------------------------------------
| PLUGIN_NAME | PLUGIN_STATUS |
-------------------------------------
| rpl_semi_sync_master | ACTIVE |
-------------------------------------8.验证的过程跟异步模式是一样的在master上建表建库测试在slave上查看是否生效
2.3 同步复制 组复制-同步复制实现的技术 最低3台最多9台 具体操作 https://www.cnblogs.com/kevingrace/p/10260685.html
3 延迟备份 步骤 1.停止同步服务 root(none) 16:37 mysqlstop slave;2.设置延迟时间 root(none) 16:37 mysqlCHANGE MASTER TO MASTER_DELAY 10; #延迟10秒3.开始同步时间 root(none) 16:37 mysqlstart slave;4.查看 roothepang123 11:47 mysqlshow slave status\G;4.主从切换 什么时候需要主从切换主从切换如何实现 主服务器挂了需要提升原来的从为主 --》主从切换故障切换 完全手工去操作 步骤 1.stop slave 2.reset master 3.开启二进制日志 4.建立授权复制的用户 5.再启动一台机器做从配置master信息去拉取二进制日志 自动实现主从切换 使用脚本实现 1.监控master 在另外一台机器扫描端口nc 3306 直接访问: mysql -h ip -uroot -p’**’ -e ‘show databases;’ 每秒钟监控一次 2.马上执行手工操作的步骤脚本自动执行 如何将网站的写的流量切到新的master上 1.直接修改web里的代码里的ip换成新的master的ip 2.修改域名对应的ip为新的master的ip 3.如果使用中间件需要在中间件里调整
5.GTID主从复制
5.1 GTID简介 什么是GTID? 全局事务标识符GTID的全称为Global Transaction Identifier是在整个复制环境中对一个事务的唯一标识。目的在于能够实现主从自动定位和切换而不像以前需要指定文件和位置。使用GTID复制时主库上提交事务时创建事务对应的GTID从库在应用中继日志时用GTID识别和跟踪每个事务。在启动新从库或因故障转移到新主库时可以使用GTID来标识复制的位置极大地简化了这些任务。–在新的主从切换的时候新的从服务器知道哪些事务已经做了哪些没有做。 GTID格式 ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 特点 1、全局唯一一个事务对应一个GTID 2、替代传统的binlogpos复制使用master_auto_position1自动匹配GTID断点进行复制 3、MySQL5.6开始支持 4、在传统的主从复制中slave端不用开启binlog但是在GTID主从复制中必须开启binlog 5、slave端在接受master的binlog时会校验GTID值 6、为了保证主从数据的一致性多线程同时执行一个GTID 工作原理 1、master更新数据时会在事务前产生GTID一同记录到binlog日志中。 2、slave端的i/o 线程将变更的binlog写入到本地的relay log中。 3、sql线程从relay log中获取GTID然后对比slave端的binlog是否有记录。 4、如果有记录说明该GTID的事务已经执行slave会忽略。 5、如果没有记录slave就会从relay log中执行该GTID的事务并记录到binlog。 6、在解析过程中会判断是否有主键如果没有就用二级索引如果没有就用全部扫描
5.2 配置基于GTID的半同步主从复制 步骤 1.在master上安装配置半同步的插件,再配置 root(none) 09:59 scmysqlinstall plugin rpl_semi_sync_master SONAME semisync_master.so;#卸载命令
root(none) 09:59 scmysqluninstall plugin rpl_semi_sync_master;[rootsc-master ~]# vim /etc/my.cnf [mysqld]
#二进制日志开启
log_bin
server_id 1#开启半同步需要提前安装半同步的插件
rpl_semi_sync_master_enabled1
rpl_semi_sync_master_timeout1000 # 1 second
#gtid功能
gtid-modeON
enforce-gtid-consistencyON[rootsc-master mysql]# service mysqld restart2.在从服务器上配置安装半同步的插件配置slave root(none) 11:22 mysqlinstall plugin rpl_semi_sync_slave SONAME semisync_slave.so;
root(none) 11:23 mysqlset global rpl_semi_sync_slave_enabled 1;[rootsc-slave mysql]# vim /etc/my.cnf [mysqld]
#log bin 二进制日志
log_bin
server_id 2
expire_logs_days 15
#开启gtid功能
gtid-modeON
enforce-gtid-consistencyON
log_slave_updatesON
#开启半同步需要提前安装半同步的插件
rpl_semi_sync_slave_enabled1[rootsc-slave mysql]# service mysqld restart3.在master上新建一个授权用户给slave来复制二进制日志 root(none) 11:19 scmysqlgrant replication slave on *.* to liaobo192.168.98.% identified by 123456;4.在slave上配置master info的信息 #停止
root(none) 16:33 scmysqlstop slave;
#清空
root(none) 16:33 scmysqlreset slave all;
#配置
root(none) 16:33 scmysqlCHANGE MASTER TO MASTER_HOST192.168.2.197 ,- MASTER_USERrenxj,- MASTER_PASSWORDSanchuang1234#,- MASTER_PORT3306,- master_auto_position1; #复制时的位置信息1表示起点154
#开启
root(none) 16:33 scmysqlstart slave;CHANGE MASTER TO MASTER_HOST192.168.98.138 ,
MASTER_USERmaster,
MASTER_PASSWORD123456,
MASTER_PORT3306,
master_auto_position1;
CHANGE MASTER TO MASTER_DELAY 60;5.查看 在slave上查看
root(none) 16:34 scmysqlshow slave status\G;在master上查看
root(none) 16:35 mysqlshow variables like %semi_sync%;在slave上查看
root(none) 16:35 scmysqlshow variables like %semi_sync%;6.验证GTID的半同步主从复制 gtid和组复制的使用场景 业务量小推荐使用gtid的半同步主从复制只要2~3台服务器 业务量大 推荐使用组复制至少35,79台服务器 log_slave_updatesON 开启级联同步 但是当我们需要实现级联同步时即以这样的一个模式ABC实现三级同步时AB库除了需要设置log-bin参数还需要添加一个参数log-slave-updates log-slave-updates参数默认时关闭的状态如果不手动设置那么bin-log只会记录直接在该库上执行的SQL语句由replication机制的SQL线程读取relay-log而执行的SQL语句并不会记录到bin-log那么就无法实现上述的三级级联同步。
5.3 架构
主主复制多实例复制
九. 项目
9.1 简介 项目名称基于keepalivedgtid半同步主从复制的MySQL集群 项目环境centos7.9mysql5.7.30mysqlrouter8.0.21keepalived 1.3.5ansible 2.9.27等 项目描述 本项目的目的是构建一个高可用的能实现读写分离的高效的MySQL集群确保业务的稳定能沟通方便的监控整个集群同时能批量的去部署和管理整个集群。 项目步骤 1.配置好ansible服务器并建立免密通道一键安装好5台MySQL服务器系统并安装好半同步相关的插件在master上导出基础数据到ansible上发布到所有slave服务器上并导入 2.开启gtid功能启动主从复制服务配置延迟备份服务器从slave1上拿二进制日志 3.在master上创建一个计划任务每天2:30进行数据库的备份脚本使用rsyncsersync远程同步到slave4异地备份服务器上 4.安装部署mysqlrouter中间件软件实现读写分离安装keepalived实现高可用配置2个vrrp实例实现双vip的高可用功能 5.搭建DNS域名服务器配置一个域名对应2个vip实现基于DNS的负载均衡访问同一URL解析出双vip地址 6.使用sysbench整个MySQL集群的性能cpu、IO、内存等进行压力测试了解系统性能的瓶颈并调优 项目心得 1.一定要规划好整个集群的架构配置要细心脚本要提前准备好边做边修改 2.防火墙和selinux的问题一定要多注意 3.对MySQL的集群和高可用有了深入的理解对自动化批量部署和监控有了更加多的应用和理解 4.keepalived的配置需要更加细心对IP地址的规划有了新的认识 5.对双vip有了更深的使用添加2条负载均衡记录实现dns轮询达到向2个vip负载均衡器上分流 遇到的问题及解决方法 问题 1.导入数据的时候GTID问题先不开启gtid功能数据导入同步后再开启gtid功能 2.Slave_IO_Running: No 原因是 slave上的GTIDs编号比master上的还大意味着slave上的数据比master还新导致IO线程启动不成功 解决的方法 在所有slave上清除master信息和slave的信息 reset master reset slave all 第9步的计划任务 [rootsc-master backup]# crontab -l
30 2 * * * bash /backup/backup_alldb.sh
[rootsc-master backup]# cat backup_alldb.sh
#!/bin/bashmkdir -p /backup
mysqldump -uroot -pSanchuang1234# --all-databases --triggers --routines --events /backup/$(date %Y%m%d%H%M%S)_all_db.SQL
scp /backup/$(date %Y%m%d%H%M%S)_all_db.SQL 192.168.2.103:/backup #可使用rsync远程同步9.2 mysqlroute MySQL Router就实现了MySQL的读写分离对MySQL请求进行了负载均衡;是官方给我们提供的一个读写分离的轻量级MySQL中间件 既然MySQL Router是一个数据库的中间件那么MySQL Router必须能够分析来自前面客户端的SQL请求是写请求还是读请求以便决定这个SQL请求是发送给master还是slave以及发送给哪个master、哪个slave。这样MySQL Router就实现了MySQL的读写分离对MySQL请求进行了负载均衡。因此MySQL Router的前提是后端实现了MySQL的主从复制。 MySQL Router很轻量级只能通过不同的端口来实现简单的读/写分离且读请求的调度算法只能使用默认的rr(round-robin)轮询算法。 读写分离将对数据库的增删改查等操作进行分离读操作往slave上进行读写操作都可以在master上进行 本质上起到了负载均衡的作用–》读写分离器、负载均衡器 目的是解决大并发的场景下将流量分散到所有的MySQL服务器上提升整个MySQL集群的处理能力避免资源的闲置提高数据库的响应能力提高用户使用的满意度-让整个数据库的访问过程非常顺畅不卡顿。 好处 dba负责数据库的业务开发人员不需要了解直接访问读写分离的服务器就可以了 更加好的进行业务上的切割 安装部署MySQLrouter 1.上传或者去官方网站下载软件 https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.23-1.el7.x86_64.rpm 2.安装 [rootmysql-router-1 ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm 3.修改配置文件 [rootmysql-router-1 ~]# cd /etc/mysqlrouter/ 进入存放配置文件的目录
[rootmysql-router-1 mysqlrouter]# vim mysqlrouter.conf[DEFAULT]
logging_folder /var/log/mysqlrouter
runtime_folder /var/run/mysqlrouter
config_folder /etc/mysqlrouter[logger]
level INFO[keepalive]
interval 60[routing:slaves]
#bind_address 192.168.98.140:7001
bind_address 0.0.0.0:7001
destinations 192.168.98.135:3306,192.168.98.138:3306
mode read-only
connect_timeout 1[routing:masters]
#bind_address 192.168.98.140:7002
bind_address 0.0.0.0:7002
destinations 192.168.98.131:3306
mode read-write
connect_timeout 14.启动MySQL router服务 [rootmysql-router-1 ~]# service mysqlrouter start
mysqlrouter监听了7001和7002端口
[rootmysql-router-1 ~]# netstat -anplut|grep mysql
tcp 0 0 192.168.2.106:7001 0.0.0.0:* LISTEN 2258/mysqlrouter
tcp 0 0 192.168.2.106:7002 0.0.0.0:* LISTEN 2258/mysqlrouter 5.在master上创建2个测试账号一个是读的一个是写的 root(none) 15:34 mysqlgrant all on *.* to write% identified by 123456;
root(none) 15:35 mysqlgrant select on *.* to read% identified by 123456;6.在客户端上测试读写分离的效果使用2个测试账号 #实现读功能
[rootnode1 ~]# mysql -h 192.168.98.140 -P 7001 -uread -p123456
#实现写功能
[rootnode1 ~]# mysql -h 192.168.98.140 -P 7002 -uwrite -p123456读写分离的关键点其实是用户的权限让不同的用户连接不同的端口最后任然要到后端的mysql服务器里去验证是否有读写的权限 mysqlrouter只是做了读写的分流让应用程序去连接不同的端口–》mysqlrouter只是一个分流的工具 主要是用户权限的控制有写权限的用户走读的通道也可以写读的用户走写的通道只能读
9.3 keepalived
9.3.1 介绍 high availability(HA) 高可用 不会有单点故障一个坏了另外的能顶替不影响工作有备份。 3个经典的HA软件heartbeat、keepalived、HAproxy Keepalived 的2大核心功能 1.loadbalance 负载均衡LBipvs–》lvs软件在linux内核里已经安装不需要单独安装 2.high-availability 高可用HA vrrp协议
9.3.2 vrrp协议
vrrp协议虚拟路由器冗余协议 介绍 一组路由器协同工作担任不同的角色有master角色也有backup角色 master角色的路由器的接口承担实际的数据流量转发任务 Backup路由器侦听Master路由器的状态并在Master路由器发生故障时接替其工作从而保证业务流量的平滑切换。 随时候命是备胎 vip 虚拟ip -在一个VRRP 组内的多个路由器接口共用一个虚拟IP地址该地址被作为局域网内所有主机的缺省网关地址。 VRRP协议报文 VRRP协议报文使用固定的组播地址224.0.0.18进行发送 帧的组播地址目的地址[Destination Address] 01:00:5E:00:00:12 vrrp协议工作在网络层 vrrp协议的组播地址 封装角度 帧 源mac目的mac vrrp协议 封装 ip协议 vrrp协议的工作原理 选举的过程 1.所有的路由器或者服务器发送vrrp宣告报文进行选举必须是相同vrid和认证密码的优先级高的服务器或者路由器会被选举为master其他的机器都是backup 2.master定时Advertisement Interval发送VRRP通告报文以便向Backup路由器告 知自己的存活情况。 默认是间隔1秒 3.接收Master设备发送的VRRP通告报文判断Master设备的状态是否正常。 如果超过1秒没有收到vrrp报文就认为master挂了开始重新选举新的mastervip会漂移到新的master上
9.3.3 脑裂 脑裂:多台机器出现vip 脑裂原因 1.vrid虚拟路由id不一样 2.网络通信有问题中间有防火墙阻止了网络之间的选举的过程vrrp报文的通信 3.认证密码不一样也会出现脑裂 脑裂有没有危害如果有危害对业务有什么影响 没有危害能正常访问反而还有负载均衡的作用 脑裂恢复的时候还是有影响的会短暂的中断影响业务的
9.3.4 keepalived实验 配置keepalived的步骤 1.在2台MySQLrouter上都安装keepalived软件 [rootmysql-router-1 keepalived]# yum install keepalived -y
[rootmysql-router-2 keepalived]# yum install keepalived -y2.修改配置文件 #配置介绍
vrrp_instance VI_1 { #定义一个vrrp协议的实例 名字叫VI_1 第一个vrrp实例state MASTER #做master角色interface ens33 #指定监听网络的接口其实就是vip绑定到那个网络接口上virtual_router_id 151 #虚拟路由器id --》帮派 51是帮派的编号 0~255之间priority 120 #优先级 0~255advert_int 1 #宣告消息的时间间隔 1秒authentication {auth_type PASS #密码认证 passwordauth_pass 1111 #具体密码}virtual_ipaddress { #vip 虚拟ip地址192.168.98.88}[rootmysql-router-1 ~]# cd /etc/keepalived/
[rootmysqlrouter-1 keepalived]# vim keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 {state backupinterface ens33virtual_router_id 80priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.88}
}[rootmysql-router-2 keepalived]# vim keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 {state backupinterface ens33virtual_router_id 80priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.88}
}3.启动服务 [rootmysql-router-1 keepalived]# service keepalived start
[rootmysqlrouter-2 keepalived]# service keepalived start4.可以验证vip漂移 关闭master上的keepalived服务 service keepalived stop 在backup服务器上看是否有vip
9.3.5 keepalived的3个进程
keepalived的3个进程keepalived进程去封装vrrp协议报文负责接收和发送keepalived正常启动的时候共启动3个进程 一个是父进程负责监控其子进程一个是VRRP子进程另外一个是checkers子进程 两个子进程都被系统watchdog看管两个子进程各自负责自己的事。 Healthcheck子进程检查各自服务器的健康状况例如http,lvs。如果healthchecks进程检查到master上服务不可用了就会通知本机上的VRRP子进程让他删除通告并且去掉虚拟IP转换为BACKUP状态。 9.3.6 主从切换时如何设置邮件通知 /etc/keepalived/keepalived.conf 配置文件中设置截取部分 vrrp_script send_mail {script /mail/sendmail.shinterval 3 #每隔3秒钟就执行一次这个脚本
}vrrp_instance VI_1 {state MASTERinterface ens33virtual_router_id 80priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}
#追踪执行脚本只要成为master发送vrrp宣告消息就执行脚本
track_script {send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.shvirtual_ipaddress {192.168.2.187
}}在vrrp实例里的配置只要启动keepalived进程就会每隔3秒执行一次/mail/sendmail.sh不管你是master还是backup notify | # (1)任意状态改变后执行的脚本
9.4 keepalived实现双vip功能 思路搞2个vrrp实例2个vip2个实例互为主备 第1台服务器上的配置 [rootmysql-router-1 keepalived]# cat keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}vrrp_instance VI_1 {state MASTERinterface ens33virtual_router_id 80priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.88}
}vrrp_instance VI_2 {state backupinterface ens33virtual_router_id 81priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.89}
}第2台服务器的配置 [rootmysqlrouter-2 keepalived]# cat keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}
vrrp_instance VI_1 {state backupinterface ens33virtual_router_id 80priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.88}
}vrrp_instance VI_2 {state masterinterface ens33virtual_router_id 81priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.98.89}
}9.5 压力测试工具 sysbench 1.使用yum安装,使用epel-release源去安装sysbench [rootnfs-server ~]# yum install epel-release -y
[rootnfs-server ~]# yum install sysbench -y2.在master数据库里新建sbtest的库 [rootsc-slave ~]# mysql -uwrite -p123456 -h 192.168.98.141 -P 7002write(none) 21:50 mysqlcreate database sbtest;3.建10个sbtest表 [rootlocalhost sysbench]# sysbench --mysql-host192.168.98.141 --mysql-port7002 --mysql-userwrite --mysql-password123456 /usr/share/sysbench/oltp_common.lua --tables10 --table_size10000 prepare4.压力测试 [rootlocalhost sysbench]# sysbench --threads4 --time20 --report-interval5 --mysql-host192.168.98.141 --mysql-port7002 --mysql-userwrite --mysql-password123456 /usr/share/sysbench/oltp_read_write.lua --tables10 --table_size100000 runmysql性能测试工具——tpcc 1.下载安装包并解压然后打开目录进行make wget http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz
tar xf tpcc-mysql-src.tar
cd tpcc-mysql/src
make之后会生成两个二进制工具tpcc_load提供初始化数据的功能和tpcc_start(进行压力测试) [rootnfs-server src]# cd ..
[rootnfs-server tpcc-mysql]# ls
add_fkey_idx.sql drop_cons.sql schema2 tpcc_load
count.sql load.sh scripts tpcc_start
create_table.sql README src
[rootnfs-server tpcc-mysql]# 3、初始化数据库 在master服务器上连接到读写分离器上创建tpcc库需要在测试的服务器上创建tpcc的库 [rootsc-slave ~]# mysqladmin -uwrite -p123456 -h 192.168.98.141 -P 7002 create tpcc需要将tpcc的create_table.sql 和add_fkey_idx.sql 远程拷贝到master服务器上 [rootnfs-server tpcc-mysql]# scp create_table.sql add_fkey_idx.sql root192.168.98.131:/root然后在master服务器上导入create_table.sql 和add_fkey_idx.sql 文件 mysql -uroot -p123456 tpcc create_table.sql
mysql -uroot -p123456 tpcc add_fkey_idx.sql4、加载数据 注意server是要测试的服务器dbuserpassword也是要测的服务器上mysql的信息 ./tpcc_load [server] [db] [user] [password] [warehouse] 服务器名 数据库名 用户名 密码 仓库数量 真实测试中数据库仓库一般不少于100个如果配置了ssd建议最少不低于1000个 [rootnfs-server tpcc-mysql]# ./tpcc_load 192.168.98.141:7002 tpcc write Sanchuang1234# 1505、进行测试 ./tpcc_start -h 192.168.98.141 -p 7002 -d tpcc -u write -p 123456 -w 150 -c 12 -r 300 -l 360 -f test0.log -t test1.log - test0.out注意server等信息与步骤4中保持一致 各个参数用法如下 -h server_host: 服务器名 -P port : 端口号默认为3306 -d database_name: 数据库名 -u mysql_user : 用户名 -p mysql_password : 密码 -w warehouses: 仓库的数量 -c connections : 线程数默认为1 -r warmup_time : 热身时间单位:s默认为10s , 热身是为了将数据加载到内存。真实测试中建议热身时间不低于5分钟 -l running_time: 测试时间单位:s默认为20s -i report_interval: 指定生成报告间隔时长真实测试中不低于30min -f report_file: 测试结果输出文件一般命名为xxx.log -t trx输出文件 out1: 将控制台输出存入文件out1中
