文章目录
MySQL 安装(Linux)
参考 Linux 环境搭建中的{{1.13 安装 mysql}}
MySQL 启动（Mac）
mac 下启动 mysql
##### 启动MySQL服务
> sudo /usr/local/MySQL/support-files/mysql.server start
##### 停止MySQL服务
> sudo /usr/local/mysql/support-files/mysql.server stop
##### 重启MySQL服务
> sudo /usr/local/mysql/support-files/mysql.server restart
##### 终端连接MySQL
> mysql -uroot -proot
##### 强制关闭mysql
> sudo pkill -9 mysql
MySQL 连接信息
#查看最大连接数，默认是151
show variables like '%max_connection%';
#重新设置最大连接数
set global max_connections=1000;
#在/etc/my.cnf里面设置数据库的最大连接数
# max_connections = 1000
#查看连接数
show status like 'Threads%';
#查看当前用户的连接100之内
show processlist;
#查看当前用户所有的连接
show full processlist;
MySQL 配置文件
必须在 /etc 新建 my.cnf 文件
sudo vim /etc/my.cnf
添加 {{my.cnf 文件内容}}
# 修改文件读写权限
sudo chmod 664 my.cnf
# mysql 认为666权限不安全会忽略掉
参考文章：https://blog.csdn.net/jyongchong/article/details/77862819
mysql 位置信息
#mac
/usr/local/mysql/data/
/var/lib/mysql/
基本命令
database
#创建
create database lane;
#使用
use lane;
table
#创建
create table dept (
dept_id int primary key,
deptName varchar(200)
) engine = innodb;
查看表数据结构信息
desc user;
查看表创建信息
show create table user;
二进制日志（binary log）
show variables like '%log_bin%'; //是否开启
show variables like '%binlog%';//参数查看 show binary logs;//查看日志文件
慢查询日志（Slow query log）
# 记录所有执行时间超时的查询SQL，默认是10秒。
show variables like '%slow_query%'; //是否开启
show variables like '%long_query_time%'; //时长
查询缓存
show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等
show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等
存储引擎 innoDB 命令
存储引擎
show engines //命令，就可以查看当前数据库支持的引擎信息。
Buffer Pool 配置参数
show variables like '%innodb_page_size%'; //查看page页大小
show variables like '%innodb_old%'; //查看lru list中old列表参数
show variables like '%innodb_buffer%'; //查看buffer pool参数
# 建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，
# innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。
Change Buffer
# ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来 进行调整。
# 参数innodb_change_buffer_max_size;
show variables like '%innodb_change_buffer_max_size%';
Log Buffer
show variables like '%innodb_log_buffer_size%';
show variables like '%innodb_log%';
show variables like '%innodb_flush_log_at_trx_commit%'
set global innodb_flush_log_at_trx_commit =2
innodb_flush_log_at_trx_commit 参数控制日志刷新行为，默认为 1
0： 每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer-->OS cache，刷盘 OS cache--> 磁盘文件），最多丢失 1 秒数据
1： 事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作
2： 事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作
通用表空间
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间
查看线程
show variables like '%innodb_purge_threads%';
show variables like '%innodb_page_cleaners%';
查看文件存储格式
# \G 表示以列的形式展示
show table status \G;
# 通过 information_schema 查看指定表的文件格式
select * from information_schema.innodb_sys_tables \G;
修改文件格式
ALTER TABLE 表名 ROW_FORMAT=格式类型;
# 只有在重新创建表及其索引才生效
Undo Log 信息
show variables like '%innodb_undo%';
Redo Log 信息
show variables like '%innodb_log%';
系统日志命令
Binlog 状态查看
show variables like 'log_bin';
开启 Binlog 功能
mysql> set global log_bin=mysqllogbin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
#需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql-bin，重启 MySQL服务。
#可以开启之后通过命令行设置记录的类型
set global binlog_format='ROW';
#开启binlog日志大概会有1%的性能损耗。
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
server-id = 1
# expire_logs_days = 7 //binlog过期清理时间
# max_binlog_size 100m //binlog每个日志文件大小
常用 Binlog 命令
# 查看所有binlog日志列表
show binary logs; //等价于show master logs;
#查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
show master status;
show binlog events;
show binlog events in 'mysql-bin.000001';
#刷新log日志，自此刻开始产生一个新编号的binlog日志文件
flush logs;
#重置(清空)所有binlog日志
reset master;
purge binary logs to 'mysql-bin.000001'; //删除指定文件
purge binary logs before '2021-07-08 00:00:00'; //删除指定时间之前的文件
#可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。
#设置为1表示超出1天binlog文件会自动删除掉。
恢复 binlog 数据
cd /usr/local/mysql/data
#执行命令
mysqlbinlog --start-position=219 --stop-position=1496 mysql-bin.000001 | mysql -uroot -proot
#出现错误
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
mysqlbinlog --no-defaults --start-position=219 --stop-position=1496 mysql-bin.000001 | mysql -uroot -proot
# mysqlbinlog --no-defaults --start-position=219 --stop-position=1496 --database=lanebin mysql-bin.000001 | mysql -uroot -proot -v lanebin
常用参数选项解释：
–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
–start-position：从二进制日志中读取指定 position 事件位置作为开始。
–stop-position：从二进制日志中读取指定 position 事件位置作为事件截至
–database=lanebin 指定只恢复 zyyshop 数据库(一台主机上往往有多个数据库，只限本地 log 日志)
不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取 binlog 日志
–read-from-remote-server 从某个 MySQL 服务器上读取 binlog 日志
索引相关命令
查看索引
show index from table_name \G;
show index from dept \G;
删除索引
drop index indexName ON table_name;
drop index deptcode_1 on dept;
drop index deptcode_2 on dept;
drop index deptcode_3 on dept;
创建普通索引
创建表的时候添加
CREATE TABLE tablename ( [...], INDEX 索引的名字 (字段名));
create table dept (
id int primary key ,
deptname varchar(200),
deptcode varchar(200),
index deptname_1(deptname),
index deptcode_1(deptcode)
) engine =innodb charset =utf8;
修改表结构添加
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
alter table dept add index deptcode_2(deptcode);
直接创建索引
CREATE INDEX indexName ON tableName (字段名);
create index deptcode_3 on dept(deptcode);
创建唯一 索引
CREATE UNIQUE INDEX 索引的名字 ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX 索引的名字 (字段名);
CREATE TABLE tablename ( [...], UNIQUE 索引的名字 (字段名) ;
创建主键索引
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
#可以看出一般创建表的时候指定了primary key则默认创建主键索引
创建复合索引
CREATE INDEX 索引的名字 ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX 索引的名字 (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX 索引的名字 (字段名1，字段名2...) );
创建全文索引
CREATE FULLTEXT INDEX 索引的名字 ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT 索引的名字 (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY 索引的名字 (字段名) ;
全文索引语法
#和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字进行等值匹配
#注意在innodb引擎模糊匹配至少要有3个字符最多84个字符，否则失效
select * from user where match(name) against('aaa');
布尔模式
#采用布尔模式可以模糊匹配，否则是等值匹配
select * from user where match(name) against('a*' in boolean mode);
自适应哈希
show engine innodb status \G;
show variables like '%innodb_adaptive%';
EXPLAIN 命令
EXPLAIN SELECT * from user WHERE id < 3;
慢查询
是否开启慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
开启慢查询
SET global slow_query_log = ON;
SET global slow_query_log_file = 'slow_query_log.log';
#记录不使用索引的查询
SET global log_queries_not_using_indexes = ON;
#记录超过查询时间阈值
SET long_query_time = 0.0001;
查询慢查询日志
show variables like '%slow_query%';
分页查询
查看 profiling 是否开启
#默认关闭
show variables like 'profiling'
开启 profiling 功能
set profiling = 1
查询 sql 执行时间
show profiles
分页查询
SELECT * FROM 表名 LIMIT [offset,] rows
#偏移量从0开始
select * from user limit 1000,10; //查询前1000条数据之后的10条，既是1001~1010
事务相关
查看事务隔离级别
show variables like 'tx_isolation';
#或者
select @@tx_isolation;
设置事务隔离级别
#会话设置
set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';
#全局设置加上global
set global tx_isolation='READ-UNCOMMITTED';
开启事务
#开启事务
begin;
#提交事务
commit;
#回滚事务
rollback；
添加锁的语句
在 RR 隔离级别，InnoDB 对于记录加锁行为都是先采用 Next-Key Lock，但是当 SQL 操作含有唯一索引 时，Innodb 会对 Next-Key Lock 进行优化，降级为 RecordLock，仅锁住索引本身而非范围。
1）select ... from 语句：InnoDB 引擎采用 MVCC 机制实现非阻塞读，所以对于普通的 select 语句， InnoDB 不加锁
2）select ... from lock in share mode 语句：追加了共享锁，InnoDB 会使用 Next-Key Lock 锁进行处 理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。
3）select ... from for update 语句：追加了排他锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫 描发现唯一索引，可以降级为 RecordLock 锁。
4）update ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以 降级为 RecordLock 锁。
5）delete ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降 级为 RecordLock 锁。
6）insert 语句：InnoDB 会在将要插入的那一行设置一个排他的 RecordLock 锁。
表锁相关
#添加表锁的语句
lock table 表名称 read|write,表名称2 read|write;
lock table user read ;
#删除表锁的语句
unlock tables;
#查看表锁的语句
show open tables;
共享锁（行级锁-读锁）
select ... lock in share mode
select * from user where id =1 lock in share mode
排他锁（行级锁-写锁）
#排他锁的方法是在 SQL 末尾加上 for update，innodb 引擎默认会在 update，delete 语句加上 for update
select * from user where id =1 for update
死锁相关
#查看死锁日志
show engine innodb status \G;
#查看锁状态变量
show status like 'innodb_row_lock%'
#Innodb_row_lock_current_waits：当前正在等待锁的数量
#Innodb_row_lock_time：从系统启动到现在锁定总时间长度
#Innodb_row_lock_time_avg： 每次等待锁的平均时间
#Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
#Innodb_row_lock_waits：系统启动后到现在总共等待的次数
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
