Mysql
逻辑架构
5.0之后默认使用InnoDB引擎（支持引擎很多，自行百度学习），适合高并发，关注于事务，运行环境内存的大小影响着性能。
结构：1连接层、2服务层、3引擎层、4、存储层
安装
安装分为两种方式yum与rpm 但是最终殊途同归。
- 检查是否安装 rpm -qa|grep -i mysql
rpm 安装
- 官网地址(MySQL Community Server)：https://dev.mysql.com/downloads/mysql/5.7.html
- 解压：tar -zxvf mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar
下载得到mysql-5.7.33-1.el7.x86_64.rpm-bundle.tar 里面包含如下：
mysql-community-client-5.7.33-1.el7.x86_64.rpm //主要
mysql-community-common-5.7.33-1.el7.x86_64.rpm //主要
mysql-community-devel-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.33-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.33-1.el7.x86_64.rpm
mysql-community-libs-5.7.33-1.el7.x86_64.rpm //主要
mysql-community-libs-compat-5.7.33-1.el7.x86_64.rpm
mysql-community-server-5.7.33-1.el7.x86_64.rpm //主要
mysql-community-test-5.7.33-1.el7.x86_64.rpm
- 安装 按顺序执行，common-》libs-》client-》server
- 提示 可能出现错误 ‘mysql-community-common-5.7.37-1.el7.x86_64.rpm: *** **** Signature, 密钥 ID ***: *****’ 这个时候需要加上 ‘–force --nodeps’
rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm
//执行common的时候，可能并不会出现安装界面 需要执行 yum remove mysql-libs 即可。
rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm
//执行server 的时候可能会提示缺少 如下：
[root@localhost home]# rpm -ivh mysql-community-server-5.7.33-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.33-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
错误：依赖检测失败：
/usr/bin/perl 被 mysql-community-server-5.7.33-1.el7.x86_64 需要
net-tools 被 mysql-community-server-5.7.33-1.el7.x86_64 需要
perl(Getopt::Long) 被 mysql-community-server-5.7.33-1.el7.x86_64 需要
perl(strict) 被 mysql-community-server-5.7.33-1.el7.x86_64 需要
执行：yum -y install net-tools
执行：yum -y install perl
安装依赖之后再执行 mysql-community-server即可
yum 安装
- 官网地址( MySQL Yum Repository)：https://dev.mysql.com/downloads/repo/yum/
- 通过工具放入linux 系统中例如：mysql80-community-release-el7-3.noarch.rpm
- 安装源 yum -y install mysql80-community-release-el7-3.noarch.rpm
- 安装mysql 会自动下载相关依赖：yum -y install mysql-community-server
- 下载依赖可能会很慢，查看是哪些包下载慢然后去http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/ 下载对应的包，然后放入(默认yum缓存包路径/var/cache/yum//x86_64/7/rpm昵称/packages下面) 再重新执行 yum -y install mysql-community-server
命令
- 启动(并且开机默认启动服务)：systemclt start mysqld
- 关闭：systemclt stop mysqld
- 重启：systemclt restart mysqld
- 查看安装初始密码：cat /var/log/mysqld.log | grep “temporary password”
- 登入：mysql -u root -p
- 初次登录修改密码：set password for root@localhost = password(‘Admin12345.’);
- 密码复杂度：
set global validate_password_policy=0; //策略
set global validate_password_length=1; //长度
- 查看mysql当前提供引擎：show engines;
- 查看当前mysql默认使用的引擎：show variables like ‘%storage_engine%’;
- 创建用户：create user ‘xxx’@‘192.118.1.1’ identified by ‘123456’;
- 授权：GRANT ALL ON 库名.表明 TO ‘用户名称’@‘地址’;
- 立即生效：flush privileges;
JOIN
7种JSON
表A、B 两表交叉 例如 ：( A外 ( 内 ) B外 )
- 1、 inner join(内查询)
- 2 、left join(左查询)
- 3、 right join(右查询)
- 4、 left join… where 关联字段 is null (左外查询)
- 5、 right join… where 关联字段 is null (右外查询)
- 6、full outer join (全连接) 在mysql 中并不支持这种写法 实际做法是等于是 (左查询 union 右查询)
- 7、full outer join…A关联字段 is null or B关联字段 is null 在mysql 中并不支持这种写法 实际做法是等于是 ( 左外查询 union 右外查询)
示例如下：A员工表、B部门表两张表
//内查询
select * from A a inner join B b on a.部门id = b.id;
//左查询
select * from A a left join B b on a.部门id = b.id;
//右查询
select * from A a right join B b on a.部门id = b.id;
//左外查询
select * from A a left join B b on a.部门id = b.id where b.部门id is null;
//右外查询
select * from A a right join B b on a.部门id = b.id where a.部门id is null;
//全查询
select * from A a left join B b on a.部门id = b.id
union
select * from A a right join B b on a.部门id = b.id;
//左外查询+ 右外查询
select * from A a left join B b on a.部门id = b.id where b.部门id is null
union
select * from A a right join B b on a.部门id = b.id where a.部门id is null;
索引
索引是一种"排好序的快速查找数据结构" 两大功能：排序+查找。
优势：提高数据检索效率，降低数据IO成本。通过索引列对数据排序，降低数据 排序成本，降低CPU的消耗。
劣势：实际上索引也是一张表，存放主键于索引并指向实体表的记录，所以也是会占用空间的。并且随着io操作，涉及的数据产生了变动与之的索引也会变动，所以就会出现耗时的情况。所以io操作大的情况下尽量不会使用索引。
分类
- 单值索引: 一个索引只包含单个列，一个表多个单值索引（建议最多5个）
- 唯一索引: 索引列的值必须唯一，但允许有空值例如：身份证号。 主键也是唯一索引
- 复合索引: 一个索引包含多个列
写法：
索引适用场：
- 1.主键自动创建唯一索引
- 2.频繁作为查询条件的字段创建索引
- 3.查询中与其他表关联的字段，外键关系建立索引
- 4.频繁更新的字段不适合创建索引，因为每次更新不单单是数据便话，存储的索引也会发生变化 进而产生额外的耗时
- 5.where 一般用不到的字段不建立
- 6.单值/复合索引选着。（高并发情况下倾向复合索引）
- 7.用于排序的字段，通过创建索引可以提高排序速度
- 8.查询中统计或者分组的字段
索引不适用场：
- 1.数据太少
- 2.经常增删改的表
- 3.表内数据多，但是字段存放内容都是相同，数据重复率差异率不高 不建立索引
常见瓶颈
- CPU 数据装入内存或者读取磁盘数据的时候
- IO 装入数据远大于内存容量
- 服务器硬件的性能瓶颈
Explain(执行计划)
能干什么？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
explain使用
//普通查询语句
select * from user;
//使用Explain执行计划
explain select * from'
//结果：
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | jz_order | ALL | null | null| null | null | 312 |
字段说明
- id:当前执行sql 的执行顺序，三种情况：1、 id 相同从上至下 2、id不相同数字大 优先级越高 3、id 部分相同 部分相同 数字大优先，相同默认从上至下。
- select_type:当前查询类型：
| 值 | 说明 |
|---|---|
| SIMPLE | 简单的查询sql |
| PRIMARY | 复杂的查询sql 中最后执行的sql |
| SUBQUERY | 复杂的查询sql 中的子查询 |
| DERIVED | 多表的查询得到的虚拟表的sql |
| UNION | 被合并的sql |
| UNION RESULT | 被合并的sql 之后得到的虚拟表sql |
- table:对应执行的表
- type:访问类型8种: system->const->eq_ref->ref->range->index->ALL->NULL (提醒百万以上ALL表示性能最差需要索引优化)
举例 表A 用户表，B 部门表
- possible_keys:表示在机器读sql 的时候***可能***会使用的索引
- key: 表示在机器读的时候***实际***使用的索引
- key_len: 本次查询索引所用最大长度
- ref: 表示索引用到的哪个字段，或者可能是常量
- rows: 表示 当前sql 机器读 需要读取多少行之后才能得到结果
- Epxtra: 表示记录当前slq 机读之后做出的调优简述。
| 内容 | 说明 |
|---|---|
| Using filesort(严重) | 排序过程中使用索引，但是机读的时候并没有按照表内使用索引进项排序 |
| Using temporary(严重) | 机读的时候出现使用临时表保存中间结果，情况常出现在排序 与分组种中 |
| Using index(严重) | 覆盖索引 可以理解为显示的字段刚好与索引字段吻合。 |
| Using where | 表示使用了where 过滤 |
| Using join buffer | 使用了链接缓存，建议缓存调整大一点 |
| impossible where | 表示 where 条件结果不能为false 例如 where name = 3 and name = 4 |
| select tables optimized away | 忽略 |
| distinct | 忽略 |
索引分析
首先从几个字段是否需要优化：id ,type,possible_keys、key、Extra
示例表：article、book、class、phone SQL如下：
CREATE TABLE `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL DEFAULT '0',
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `book` (
`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`bookid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;'
CREATE TABLE `class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `phone` (
`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`phoneid`),
KEY `B` (`card`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
//数据自行添加
单表（article）对 explain select * from article WHERE category_id = 1 and comments > 1 ORDER BY views desc LIMIT 1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | article | ALL | 3 | Using where; Using filesort |
分析上面通过Explain对sql 性能查看发现：type 为ALL为查询整张表、possible_keys与key并没有索引参与、Extra存在Using filesort 排序问题必须处理。所以最后根据sql 查询用到的字段创建一个复合索引，该复合索引包括了category_id ，views 即可。comments因为在sql中是针对范围如果将comments也纳入复合索引那么会导致其他索引失效，最后创建完毕查询性能如下：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | article | ref | idx_article_x_x_x | idx_article_x_x_x | 4 | const | 2 | Using where |
双表/三表（book、class、phone）对explain select * from book b left JOIN class c ON c.card = b.card left JOIN phone p on b.card = p.card;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | b | ALL | 20 | |||||
| 1 | SIMPLE | c | ALL | 20 | Using where; Using join buffer (flat, BNL join) | ||||
| 1 | SIMPLE | p | ALL | 20 | Using where; Using join buffer (incremental, BNL join) |
如上分析遍历20+20+20 行且使用了Using join buffer 缓存，type 都是ALL 还有都没有使用索引。优化思路 将 book 作为驱动表，使用left join 对 class 与 phone 做索引结果如下；
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | b | ALL | 20 | |||||
| 1 | SIMPLE | c | ref | A | A | 4 | test.b.card | 1 | Using index |
| 1 | SIMPLE | p | ref | B | B | 4 | test.b.card | 1 | Using index |
索引优化
示例表：staffs
//创建表并且创建复合索引 含有`name`,`age`,`pos` 字段
CREATE TABLE `staffs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
索引失效案例
- 1.全值匹配示例
- 2.最佳左前缀法则(复合索引左侧第一个必须使用,后面其他索引可以随便使用)
mysql> explain select * from staffs where name = "July" and age = 23 and pos = "dev";
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_nameAgePos | idx_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
//失效
mysql> explain select * from staffs where age=25;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from staffs where pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where age=25 and pos = 'dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 3.不在索引列上做任何操作(计算、函数、自动或手动类型转换)，会导致索引失效而转向全表扫描
mysql> explain select * from staffs where name="July";
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_nameAgePos | idx_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
//失效 left(字段昵称,String.length) 表示字段的前几个值
mysql> explain select * from staffs where left(name,1) = "July";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 4.存储引擎不能使用索引中范围条件右边列
- 5.尽量使用覆盖索引(只访问索引的查询)，减少select*
- 6.mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描
- 7.is null,is not null也无法使用索引
- 8.like 以通配符开头(’%abc…’)mysql 索引失效会变成全表扫描操作,建议使用(‘abc%’) 或者(’%abc%’)才会避免索引失效
- 9.字符串不加单引号索引失效
- 10.少用or ,用它来连接时会索引失败
查询截取分析
慢查询
默认可关闭，因为比较消耗性能
- 查看是否开启： show variables like ‘%slow_query_log%’;
- 查看慢查询阙值：SHOW VARIABLES LIKE ‘long_query_time%’;
- 查看慢查询记录sql 数：show global status like ‘%Slow_queries%’;
代码示例如下：
//命令查看
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.01 sec)
//临时修改 重启服务自动恢复
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
//查看慢查询规则执行时间
SHOW VARIABLES LIKE 'long_query_time%';
//永久生效通过修改配置my.cnf文件,[mysqld]下新增
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=3
log_bin_trust_function_creators=1
- 慢查询日志分析工具：mysqldumpslow --help(实际调优中继续深究学习)
explain + sql
…后续
show profile
可以理解为比慢查询更精细的一种sql 分析方式，默认也是关闭。
- 查看是否开启：show variables like ‘profiling’;
- 开启：set profiling=1;
- 查看记录sql 信息：show profiles;
- 查看明细：show profile cpu,block io for query Query_ID
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00010250 | set variables profiling=1 |
| 2 | 0.00213575 | show variables like 'profiling' |
| 3 | 0.00023225 | set profiling=on |
| 4 | 0.00037125 | select * from staffs |
+----------+------------+---------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000090 | 0.000020 | 0.000063 | 0 | 0 |
| checking permissions | 0.000016 | 0.000004 | 0.000011 | 0 | 0 |
| Opening tables | 0.000040 | 0.000010 | 0.000030 | 0 | 0 |
| init | 0.000028 | 0.000006 | 0.000021 | 0 | 0 |
| System lock | 0.000011 | 0.000003 | 0.000009 | 0 | 0 |
| optimizing | 0.000007 | 0.000002 | 0.000005 | 0 | 0 |
| statistics | 0.000019 | 0.000004 | 0.000014 | 0 | 0 |
| preparing | 0.000015 | 0.000004 | 0.000011 | 0 | 0 |
| executing | 0.000004 | 0.000001 | 0.000003 | 0 | 0 |
| Sending data | 0.000080 | 0.000019 | 0.000060 | 0 | 0 |
| end | 0.000006 | 0.000002 | 0.000005 | 0 | 0 |
| query end | 0.000010 | 0.000002 | 0.000007 | 0 | 0 |
| closing tables | 0.000010 | 0.000003 | 0.000007 | 0 | 0 |
| freeing items | 0.000017 | 0.000004 | 0.000013 | 0 | 0 |
| cleaning up | 0.000020 | 0.000004 | 0.000015 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
//如果查看sql 明细 Status出现以下四种情况那么可以反应给DBA/经理
converting HEAP to MyISAM 查询数据太大，内存不够用往磁盘上面搬
Creating tmp table 创建了零食表 ：1拷贝数据到临时表，2用完再删除
Copying to tmp table on disk 内存中临时表复制到磁盘，很危险！！
locked
全局查询日志
只允许测试环境，抓取所有sql
- 命令开启设置：set global general_log=1;
- 命令输出格式：set global log_output=‘TABLE’; 此后，你所编写的sql 语句，将会记录到mysql库里的general_log表，可以用下面的命令查看：select * from mysql.general_log;
- 配置：my.cnf中
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
函数、存储过程、视图
函数与函数过程相似，简单的可以理解成，函数是用于编写单个实现的方法，存储过程是综合函数过后的综合的方法，比如函数生成code,存储过程完成批量添加数据其中数据通过函数插入。一般在测试阶段需要数据支持来完成压力测试
MySql 索机制
三锁
- 表锁:偏读
- 行锁:偏写
- 页锁:了解即可
表锁
- 加锁(读read、写write)：lock table 表昵称 read, 表昵称 write;
- 查看：show open tables;
- 释放：unlock tables;
mysql> lock table mylock read,staffs write;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| db1 | mylock | 1 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| db1 | staffs | 1 | 0 |
| performance_schema | table_handles | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
122 rows in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| db1 | mylock | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| db1 | staffs | 0 | 0 |
| performance_schema | table_handles | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
122 rows in set (0.00 sec)
- 查看表锁： show status like ‘table%’;
举例子：表a,b,c…多张表客户端，操作链接db1,db2…多个链接。当db1对a进行读锁 read，没用释放锁的情况下那么就会出现: 1 db1不能对其他表做操作直接报错、2 其他链接不能对a表做修改插入操作会进入阻塞状态
举例子：表a,b,c…多张表客户端，操作链接db1,db2…多个链接。当db1对a进行写锁 write，没用释放锁的情况下那么就会出现: 1 db1不能对其他表做操作直接报错、2 其他链接不能对a表做修改插入查找操作会进入阻塞状态
总结：读锁导致，其他链接不能修改进入阻塞，写锁导致，其他链接读写都不能操作进入阻塞
行锁
- 查看行锁：show status like ‘innodb_row_lock%’;
- 手动关闭自动提交：set autocommit=0;
- 行锁：select * from 表昵称 where 条件锁定某一行 for update;
- 手动提交：commit;
页锁
主从复制
基本原理：slave会从master读取binlog来进行数据同步
三步骤+原图
- 1.master将改变记录到二进制日志(binary log)。这些过程叫做二进制日志事件，binary log events;
- 2.slave将master的binary log events拷贝到中继日志(relay log)
- 3.slave重做中继日志中的事件，将改变应用到自己的数据库中。mysql复制是异步的且串行化的
配置
主机配置
- 1.修改my.ini/my.cnf
#主从配置
server-id=134
log-bin=mysql-bin#开启二进制日志
- 2.重启mysql 服务
- 3.授权从机使用的账号
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.0.120' IDENTIFIED BY '密码';
- 4.查看主库二进制日志文件信息：show master status;
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2062 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
从机配置
- 1.修改my.cnf
server-id=134
- 2.重启mysql 服务
- 3.命令配置主机连接信息
CHANGE MASTER TO MASTER_HOST='主机IP', MASTER_USER='账号', MASTER_PASSWORD='密码', MASTER_LOG_FILE='主机查看二进制文件昵称', MASTER_LOG_POS=Position值;
- 4.开启：start slave; 关闭stop slave （如果主机需要导入数据，需要暂停从机）
- 5.查看从机状态：show slave status\G;(部分代码)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.43.134
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2062
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 2228
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #主要是这两个值为YES表示成功
Slave_SQL_Running: Yes #
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
扫一扫
6118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
