后端开发实战

Appearance

1.函数 ​

shell
date_format
#  %Y%m
%Y：4位数的年份
%m：2位数的月份
%d：2位数的日期
%H：2位数的小时（24小时制）
%i：2位数的分钟
%s：2位数的秒数
select date_format(now(),'%Y-%m-%d')

#  年月日
select CURRENT_DATE;
#  时分秒
select CURRENT_time;
#   年月日 时分秒
select  now();
#  函数从日期减去指定的时间间隔
select DATE_SUB(NOW(),INTERVAL 2 day)
# 计算两个日期相差的天数
select DATEDIFF('2024-08-25','2024-08-22')

2.查询某个表占据的磁盘容量 ​

powershell
SELECT table_name, ROUND(sum(data_length + index_length) / 1024 / 1024, 2) AS table_size
FROM information_schema.tables
WHERE table_schema = 'test'
and table_name ='users'
GROUP BY table_name;

3.导入sql 导出 ​

shell
mysql -uroot -proot databaseName <  sql 路径

mysqldump -uroot -proot databseName > 导出的sql 路径


ALTER TABLE 表名 RENAME TO 新表名;

4.导出csv ​

shell
修改 my.cnf 
#  csv 导出路径    否则 导出报错 
secure-file-priv=/tmp/

SELECT * INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n' FROM idms_user;

--  导出txt
select * from idms_user where id<10 into outfile '/tmp/10.txt'
--  txt 导入数据
load data  infile '/tmp/10.txt' into table travel.idms_user2
shell
use test;
-- 保理贷款
create table idms_bl_transaction(

    bl_id  int primary key comment '交易编号' ,
    --
    bl_amount decimal(22,6)  comment '贷款金额',
    bl_rate decimal(22,2)  comment '贷款利率',
    bl_time date  comment '贷款日期',
    interest_time date  comment '计算利息开始日期',
    bl_interest decimal(22,2)  comment '利息'

);


create table idms_zy_transaction(

    zy_id  int primary key comment '交易编号' ,
    --
    zy_amount decimal(22,6)  comment '贷款金额',
    zy_rate decimal(22,2)  comment '贷款利率',
    zy_time date  comment '贷款日期',
    interest_time date  comment '计算利息开始日期',
    zy_interest decimal(22,2)  comment '利息'

);

create table daily_balance(
    biz_id int primary key ,
    amount decimal(22,2),
    create_time date

);

insert into idms_bl_transaction(bl_id, bl_amount, bl_rate, bl_time, interest_time, bl_interest) values
(1,20000000,0.03,'2006-04-24','2006-05-01',2001);

insert into idms_bl_transaction(bl_id, bl_amount, bl_rate, bl_time, interest_time, bl_interest) values
(2,100000,0.003,'2008-05-24','2008-05-25',300);

insert into idms_zy_transaction(zy_id, zy_amount, zy_rate, zy_time, interest_time, zy_interest) values
(1,20000000,0.06,'2010-06-24','2010-10-01',3000);


insert into daily_balance(biz_id, amount, create_time) values
(1,20000,'2022-01-10'),(2,30000,'2023-01-10');

                                                              ;
drop view view_transaction;


create or replace view view_transaction as

    select  bl_id id ,bl_amount amount ,bl_rate rate,bl_time create_time,interest_time,bl_interest  interest , '保理贷款' biz_type from idms_bl_transaction

    union
    select  zy_id id , zy_amount amount, zy_rate rate , zy_time create_time , interest_time, zy_interest interest, '自营贷款' biz_type from idms_zy_transaction




select * from view_transaction;

show  tables like '%view_transaction%';
desc view_transaction;

 show create view view_transaction;

delete from view_transaction where id =1 and view_transaction.biz_type='自营贷款'

--  存储过程函数

create procedure  proc_amount_init()

begin

  update daily_balance set amount = amount+0.01 where biz_id=1;


end;


call proc_amount_init();
drop procedure  proc_amount_init;
--   命令行 创建存储过程
delimiter $
create procedure  proc_amount_init()

begin

  update daily_balance set amount = amount+0.01 where biz_id=1;


end $;


-- 创建函数

create function queryById()
returns varchar(100)

return  'hello world';

select  queryById();

show function status like 'queryById';

drop function if exists queryById;
drop function if exists queryView;

create  function queryView( )
returns decimal(22,2)
begin
declare total_amount decimal(22,2) default  0;

select sum(amount) into total_amount from view_transaction where id=1  and biz_type='保理贷款';

return  total_amount;


end;

select queryView();
select *  from view_transaction where id=1  and biz_type='保理贷款';
select amount  from view_transaction where id=1  and biz_type='保理贷款';
-- select sum(amount) into total_amount from view_transaction where id=1  and biz_type='保理贷款';

5.备份 ​

shell
查询耗时 11 s
创建表格  79s
-- 备份 单个数据库
mysqldump -uroot -proot  travel  > travel.sql
--  备份 所有数据库
mysqldump -uroot -proot  --all-databases  > all.sql
-- 备份  指定的数据库
mysqldump -uroot -proot  --databases test  travel  >  2023.sql
-- 备份 指定数据库 的单表
mysqldump -uroot -proot     travel  idms_user >  idms.sql

image.pngimage.png

image.png

image.png

image.png mysql pump image.png mydumper https://blog.csdn.net/lijuncheng963375877/article/details/119989577

shell
mydumper -u root -p root  -B  test  -o $PWD
mydumper -u root -p root  -B  travel  -T travel.idms_user -o $PWD

myloader -u root -p root  -B  travel  -d $PWD

6.用户管理 ​

shell
--  创建用户 并指定密码
CREATE USER 'gitea'@'localhost' IDENTIFIED BY '123456';
-- 给用户授权
grant SELECT,delete,create,update ,insert on  gitea.* to 'gitea'@'localhost'  
--   撤销权限
REVOKE  CREATE ON travel.*  FROM 'appview'@'localhost'  ;

flush privileges;

7.mysql 通过ibd 恢复数据 数据迁移 ​

https://www.cnblogs.com/hxlasky/p/17412311.html

shell
 在新的数据库服务器上  创建数据库  并导入表的 表结构

 脱离表空间
 alter table sys_user discard tablespace;
 把对应的 文件 复制到 目录下
 chown -R mysql:mysql ./ry
--  最后一步 执行命令后查询表数据是否导入
 alter table sys_user import tablespace;
--  通过 ibd 文件 获取 表结构信息
 ibd2sdi  -d sys_user /var/lib/mysql/ry/sys_user.ibd

8.限制用户使用的资源 ​

image.png 杂项 image.png

image.pngimage.png

image.pngimage.png mysql redolog 日志 刷盘策略

innodb_flush_log_at_trx_commit=1 设置为1 事务提交则刷盘，保证数据不丢失 ​

保证事务的持久性 ： 记录页的修改，比如某个页面某个偏移量处修改了几个字节的值以及具体被修改的内容是什么。在事务提交时，我们会将 redo log 按照刷盘策略刷到磁盘上去，物理日志

binlog 逻辑日志 binlog_format 记录内容是语句的原始逻辑，数据备份、主备、主主、主从都离不开binlog 三种格式：

  • **statement **SQL语句原文 如果使用now这种函数 会导致数据从无
  • **row **记录的内容不再是简单的SQL语句了，还包含操作的具体数据 证同步数据的一致性 更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。
  • **mixed **MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。 binlog_cache_size参数控制单个线程 binlog cache 大小 sync_binlog=1 保证 事务提交后刷盘 redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。 binlog（归档日志）保证了MySQL集群架构的数据一致性。

9.undo log ​

保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚,回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。MVCC 的实现依赖于：隐藏字段、Read View、undo log。

10.MVCC➕Next-key-Lock 防止幻读 ​

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题： 执行普通 select，此时会以 MVCC 快照读的方式读取数据, 在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读” 执行 select...for update/lock in share mode、insert、update、delete 等当前读 在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lockopen in new window 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

11.mysql 锁的分类 ​

表锁 和行锁 表锁 锁定粒度最大的一种锁 加锁快，不会出现死锁 。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。 行锁： 锁定粒度最小的一种锁， 针对索引字段加的锁 只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突 其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。 InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！ InnoDB 行锁的分类
**记录锁： Record Lock v **，属于单个行记录上的锁。 间隙锁 ： Gap Lock）：锁定一个范围，不包括记录本身 临键锁（Next-Key Lock），锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。 在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排他锁 ​

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类： 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容） 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

|

S 锁X 锁
S 锁不冲突冲突
X 锁冲突冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

shell
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁 ​

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁 前必须先取得该表的 IS 锁 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。 意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。 意向锁之间是互相兼容的。

|

IS 锁IX 锁
IS 锁兼容兼容
IX 锁兼容兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

|

IS 锁IX 锁
S 锁兼容互斥
X 锁互斥互斥

sql 优化分析

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息

select_type ​

  • SIMPLE：简单查询，不包含 UNION 或者子查询。
  • PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
  • **SUBQUERY **子查询中的第一个 SELECT。
  • UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
  • DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
  • UNION RESULT：UNION 查询的结果

type ​

shell
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > 
index_subquery > range > index > ALL


const ：   表中最多只有一行匹配的记录，
一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref： 当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式
，常用于使用主键或唯一索引的所有字段作为连表条件。

ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时
，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。
possible_keys 表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，
则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列
，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。key（重要）
key（重要） key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。
key_len：  key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时
，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。rows
rows    rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）
Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。	
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

12.获取 表的元信息 ​

sql
SELECT t.column_name,
             t.data_type,
              CAST(SUBSTR(t.column_type, INSTR(t.column_type,  '(') + 1, INSTR(t.column_type, ')') - INSTR(t.column_type,  '(') - 1)  AS  CHAR(20)) data_length,
              CAST(t.column_type  AS  CHAR(20)) column_type,
             t.column_comment,
              IF (t.is_nullable= 'YES',1,0) is_nullable,
              IF (t.column_key =  'PRI', 1, 0) is_key
FROM information_schema.columns t
WHERE t.table_schema =  SCHEMA()  AND
            t.table_name =  'app_report'
ORDER  BY t.ordinal_position;

13.表锁 ​

bash
-- 加 读锁
lock tables  course read;
unlock tables
读锁会阻塞其他线程的 写请求  读请求还是可以的  当前线程也不能做写入操作 但是可以读
lock tables  course write;
写锁会阻塞其他线程的 写请求  读请求  当前线程也可写 可读

14.事务隔离级别 ​

  1. 读未提交
  2. 读已提交
  3. 可重复读
  4. 串行化

15.mysql yum 安装 ​

sh
##  上传 mysql57-community-release-el7-11.noarch.rpm
rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
##  安装mysql  
yum -y install mysql mysql-server --nogpgcheck
##  查看 mysql 版本
mysql -V
## 启动mysql 
systemctl start mysqld
##  获取临时密码
grep 'temporary password' /var/log/mysqld.log
#  登录  并输入临时密码
mysql -uroot -p
# 设置 密码策略 长度只需要满足8位  生产环境建议默认
set global validate_password_policy=LOW;
## 修改密码  默认密码策略复杂
ALTER USER 'root'@'localhost' IDENTIFIED  BY '12345678';
use  mysql;
update user set user.Host='%' where user.User='root';
----------------------------------------------------
flush privileges;
#mysql 5.7  在配置文件 设置    否则密码策略
## LOW  只验证长度 
validate_password_policy = LOW
##  长队最小值
validate_password.length=4
## 密码中的数字要求，默认值为1。可以增加或减少数字的要求。
validate_password.number_count=0
## 密码中特殊字符的要求，默认值为1。可以增加或减少特殊字符的要求。
validate_password.special_char_count=1
##密码中大写字母和小写字母的要求，默认值为1。可以增加或减少大写字母和小写字母的要求。
validate_password.mixed_case_count=1
##

16.mysql ini 常用配置 ​

sh
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
##basedir=/usr/local/mysql/
# 设置mysql数据库的数据的存放目录
## datadir=/usr/local/mysql/mysqldb
# 允许最大连接数  默认 151
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=20
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 默认是128M  
innodb_buffer_pool_size=128M 
# 1 开启 - 关闭
slow_query_log=1
# 慢查询 日志时间设置  默认 10s
long_query_time=5
#事务提交则刷盘，保证数据不丢失
innodb_flush_log_at_trx_commit=1
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

17.sysbench 压测 ​

sh
## 准备数据
sysbench /usr/share/sysbench/oltp_common.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root    --mysql-password=123456 --tables=20 --table-size=1000000 prepare

##  10个线程  时间 60秒 生成一个csv
sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --threads=10 --time=60 --report-interval=1 run > results2.csv
## 
sysbench --db-driver=mysql --time=60 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=10 --table_size=1000000 oltp_insert --db-ps-mode=disable run > res.csv
##
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run > res101.csv
##
sysbench --db-driver=mysql --time=60 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run > res102.csv
##  参考 
https://blog.csdn.net/julielele/article/details/132188742

18.mysql 用户管理 授权 等等你 ​

sql
create user 'root'@'%' identified with mysql_native_password by 'root';
grant all privileges on *.* to 'root'@'%' with grant option;

19.mysql ubuntu ​

sh
sudo apt-get update
sudo apt-get install mysql-server
## 全部选 no
sudo mysql_secure_installation
sudo mysql -uroot -p
##  修改root密码 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
## mysql 默认密码策略为 长度8个字符以上 字母大小写 数字混合
##  设置为low  只验证长度   MEDIUM 验证长度、数字、大小写、特殊字符；  STRONG 验证长度、数字、大小写、特殊字符、字典文件
set global  validate_password.policy=LOW;

20.proxysql ​

sh
# ubuntu   安装 proxysql proxysql_2.6.4-dbg-ubuntu24_amd64.deb
dpkg -i proxysql_2.6.4-dbg-ubuntu24_amd64.deb
#sudo apt-get install proxysql
sudo systemctl status proxysql
##  配置文件
/etc/proxysql.cnf
##  管理端 
mysql  -uadmin -padmin -P6032 -h127.0.0.1
use main;
## 增加mysql 配置   hostgroup_id 10 写组 20 读组
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'127.0.0.1',3306);
load mysql servers to runtime;
save mysql servers to disk;
## 登录mysql主机  创建用户  并授权
create user 'app'@'%' identified  by '123456';
grant all privileges on *.* to 'app'@'%' with grant option;
## 在proxysql添加用户
insert into mysql_users(username,password,default_hostgroup)values('app','123456',10);
##  设置并生效
load mysql users to runtime;
save mysql users to disk;
#########################################################
######### 配置管理规则  拦截 sql###################
-- 插入新的DELETE规则 (无WHERE子句)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) 
VALUES (101, 1, '^DELETE\s+FROM\s+[^\s]+\s*$', 'DELETE语句必须包含有效的WHERE条件.', 1);
 
-- 插入新的DELETE规则 (无效WHERE子句)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) 
VALUES (102, 1, '^DELETE\s+FROM\s+[^\s]+\s+WHERE\s+[0-9]+=[0-9]+$', 'DELETE 语句必须包含有效的WHERE条件.', 1);
 
-- 插入新的UPDATE规则 (无WHERE子句)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply)
VALUES (103, 1, '^UPDATE\s+[^\s]+\s+SET\s+[^\s]+\s*$', 'UPDATE语句必须包含WHERE条件', 1);
 
-- 插入新的UPDATE规则 (无效WHERE子句)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply)
VALUES (104, 1, '^UPDATE\s+[^\s]+\s+SET\s+[^\s]+\s+WHERE\s+[0-9]+=[0-9]+$', 'UPDATE 语句必须包含有效的WHERE条件', 1);
#############################登录管理端
mysql -uapp -p123456 -P6033 -h127.0.0.1

21 锁

sql
## 添加一个表锁   是写锁  其他线程均不可读 写
lock tables yc_account_detail write;
## 添加一个表锁   是读锁  其他线程均不可写  update yc_account_detail set amount=16 where id=1;
lock tables  yc_account_detail read;
## update yc_account_detail  set amount=11 where id =1; 
## 解锁 该命令可以释放当前会话中所有的表级锁。
unlock tables;
# 查找持有表锁的会话
show processlist;
# 终止会话
kill 'id';

############### 行锁
执行 select * from table for update 
 update delete
mvcc 多版本并发控制 解决的问题是 在并发场景下 数据的一致性 (一个数据有多个版本 是无锁的)
锁机制也可以解决  但是锁的问题在于 写的时候不允许读  易导致数据不一致问题
解决这个问题在于 加读写锁 ，
psql 
select txid_current()

21.mysql 1主2从搭建 ​

ip角色
192.168.83.122master
192.168.83.123slave1
192.168.83.124slave2
sh
##  安装 mysql  在三台机器上   参照15
## 关闭防火墙   
## 修改 三台机器上的 my.cnf文件
## 在master 上创建用户及授权
CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';#创建用户
GRANT REPLICATION slave ON *.* TO 'app'@'%';
flush privileges;
##  如果有不满足密码测的地方 做如下修改
set global validate_password_length=1;
set global validate_password_policy=LOW;
## 在master 上执行 
show  master status  
#获取binlog 进度信息 填写到下方位置
# MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1938;
# 在备机上执行如下命令  192.168.83.123  192.168.83.124都需要执行
CHANGE MASTER TO MASTER_HOST='192.168.83.122', MASTER_USER='app',MASTER_PASSWORD='12345678',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1938;
# 启动主从服务 192.168.83.123  192.168.83.124
start slave;
## 执行命令
show slave status\G;
##  下方两项都是 yes  则正常
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

master 配置文件 ​

sh
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 最大连接数
max_connections=200
# 最大连接数  字符集编码
character-set-server=utf8mb4
##  
innodb_buffer_pool_size=128M 
## 慢查询
slow_query_log=1
# 慢查询时间
long_query_time=5
## 每次事务提交后刷盘
innodb_flush_log_at_trx_commit=1
##  主服务器 配置为 1
server-id = 1
log-bin=mysql-bin
## 二进制格式
binlog_format=row
log-slave-updates=true

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

slave 配置文件 ​

sh
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=200
character-set-server=utf8mb4
innodb_buffer_pool_size=128M 
slow_query_log=1
long_query_time=5
innodb_flush_log_at_trx_commit=1
##  备机配置 为2  
server-id = 2
log_bin=mysql-bin
##  缓冲区配置
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

​

22.mha 高可用 mysql 1主2从 ​

ip角色
192.168.83.122master
192.168.83.123slave1
192.168.83.124slave2
192.168.83.125manager
sh
# 1. 参照  21  搭建完成主从配置
#  做好免密认证
# 安装  软件
yum  install -y net-tools
#  在当前机器下 执行命令 生成 私钥和公钥 ~/.ssh 目录下
ssh-keygen -t rsa 
#   执行如下命令 把公钥 放到 对应机器上的 ~/.ssh/authorized_keys    两两认证
ssh-copy-id 192.168.83.122
ssh-copy-id 192.168.83.123
ssh-copy-id 192.168.83.124
ssh-copy-id 192.168.83.125

修改主从各节点配置文件如下 ​

master

sh
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=200
character-set-server=utf8mb4
innodb_buffer_pool_size=128M 
slow_query_log=1
long_query_time=5
innodb_flush_log_at_trx_commit=1
server-id = 1
log-bin=mysql-bin
binlog_format=row
log-slave-updates=true

[mysql]
# 设置mysql客户端默认字符集

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306

slave 两个节点

sh
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=200
character-set-server=utf8mb4
innodb_buffer_pool_size=128M 
slow_query_log=1
long_query_time=5
innodb_flush_log_at_trx_commit=1
## 重点是下面几项
server-id = 2
log_bin=mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
relay_log_purge=0

备机操作 ​

sh
## 登录  mysql   slave1 slave2
## 创建用户   app   主从同步用户  在从服务器上做
grant all  on *.* to 'app'@'%' identified by '12345678';
FLUSH PRIVILEGES;
#  设置为只读 
set global read_only=1;
show  global variables like 'read_only';

master 操作 ​

sh
## 登录  mysql  创建用户  负责 manager  监控
grant all  on *.* to 'mha'@'%' identified by 'PSqwerasd@123';

安装软件 mha node ​

sh
# https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
## 四个节点都安装
yum install perl-DBD-MySQL -y
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#  rpm  安装
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

安装软件 mha manager ​

shell
#  manager   机器上操作
yum -y install epel-release
yum -y install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch perl-DBD-MySQL ncftp
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
############################################
mkdir -p /opt/mysql-mha
#编写脚本 master_ip_failover
chmod a+x /opt/mysql-mha/master_ip_failover
## 注意修改主机的 master_ip_failover vip 地址 
##
mkdir -p /opt/mysql-mha/mha
vim /opt/mysql-mha/mysql_mha.cnf

master_ip_failover 脚本

perl
#!/usr/bin/env perl 
use strict;
use warnings FATAL => 'all';
  
use Getopt::Long;

my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);
  
my $vip = '192.168.83.10';            #vip地址
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";        #绑定在指定的网卡上面
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";     #我的机器有两块网卡eth1是172网段的所有我把vip绑定在eth1上，我的eth0网段是10.0.0.%。
  
GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
);
  
exit &main();
  
sub main {
  
    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
  
    if ( $command eq "stop" || $command eq "stopssh" ) {
  
        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {
  
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK \n";
        exit 0;

}
    else {
        &usage();
        exit 1;
    }
}
sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
 sub stop_vip() {
     `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
      
sub usage {
       print
       "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

mysql_mha.cnf

注意 修改 ip 账号和密码信息

sh
[server default]
manager_log=/opt/mysql-mha/manager.log
manager_workdir=/opt/mysql-mha/mha
# 监控账户和密码
user=mha
password=12345678
port=3306
#  master  相关信息
master_binlog_dir=/var/lib/mysql

ping_interval=1
remote_workdir=/opt/mysql-mha/mha-node
# 主从复制账号密码
repl_user=app
repl_password=12345678
#  后面的是从机地址  
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.83.123 -s 192.168.83.124
##  ip 漂移脚本配置  注意编码 
master_ip_failover_script=/opt/mysql-mha/master_ip_failover
[server1]
candidate_master=1
check_repl_delay=0
hostname=192.168.83.122
port=3306
ssh_user=root

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.83.123
port=3306
ssh_user=root

[server3]
candidate_master=1
check_repl_delay=0
hostname=192.168.83.124
port=3306
ssh_user=root

master 节点操作

sh
## master_ip_failover   vip  地址
/sbin/ifconfig ens33:1 192.168.83.10/24

manager 操作

sh
#  验证  ip  ssh 
masterha_check_ssh   -conf=/opt/mysql-mha/mysql_mha.cnf
#  测试  主从复制情况
masterha_check_repl -conf=/opt/mysql-mha/mysql_mha.cnf
# 上述两个操作都正常后 在做如下操作 启动
nohup masterha_manager  \
--conf=/opt/mysql-mha/mysql_mha.cnf \
--remove_dead_master_conf \
--ignore_last_failover < /dev/null > /var/log/mha_manager.log 2>&1 &
# --remove_dead_master_conf：该参数代表当发生主从切换后，老的主库的 ip 将会从配置文件中移除
# --manger_log：日志存放位置。
# --ignore_last_failover：在缺省情况下，如果 MHA 检测到连续发生宕机，且两次宕机间隔不足 8 小时的话，则不会进行 Failover， 之所以这样限制是为了避免 ping-pong 效应。该参数代表忽略上次 MHA 触发切换产生的文件，默认情况下，MHA 发生切换后会在日志记目录，也就是上面设置的日志app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为--ignore_last_failover。
# 查看MHA状态
masterha_check_status --conf=/opt/mysql-mha/mysql_mha.cnf
# 查看MHA日志文件
 cat /opt/mysql-mha/manager.log | grep "current master"
#manager节点关闭manager服务
masterha_stop --conf=/opt/mysql-mha/mysql_mha.cnf

##############################################

当master 宕机后 manager 会自动完成把从库提升为主库 的操作  在30秒内
# 经过测试 把master 停止后 systemctl stop mysqld

192.168.83.10/24 是虚地址   给应用使用
这个虚地址仍然可用   数据全部被写入到 新的master 上 即原来的 slave1  slave1 被提升为主库
ping 192.168.83.10  也是可以 ping 通的

主从发生切换后 如需切换会原来的配置

sh
'Could not find first log file name in binary log index file'的解决办法
假设  122 master   123 slave1 124 slave2
经过切换今后  1234 master 124  slave
需要在 123  124  上做如下操作
# 登录mysql  全部操作
stop slave ;
#  122 恢复后 则   做如下操作
 flush logs;  
 show master  status
 重新记录 binlog  信息
##  在备机上重新设置   主从配置
CHANGE MASTER TO MASTER_HOST='192.168.83.122', MASTER_USER='app',MASTER_PASS
WORD='12345678',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1938;##  在备机上重新设置   主从配置

start slave;
show slave status\G;

则重新运行
## ansible 全部关机
ansible all -m command -a "shutdown -h now"

mysql 查询 事务隔离级别 ​

sh
#5.7
SELECT @@tx_isolation;
SHOW VARIABLES LIKE 'tx_isolation';
#8
SHOW VARIABLES LIKE 'transaction_isolation';
SELECT @@transaction_isolation;

mysql next-key lock ​

sh
start transaction;



 mysql  在  rr 的隔离级别下    next- key - lock 会把记录本身锁住   同时也会把范围内的也锁住
 导致无法插入数据  这用到了锁  性能肯定是会有影响的