MySQL高级命令

2023-03-20 03:32| 来源: 网络整理| 查看: 265

MySQL 安装(Linux)MySQL 启动（Mac）mac 下启动 mysqlMySQL 连接信息MySQL 配置文件mysql 位置信息mac基本命令databasetable查看表数据结构信息查看表创建信息二进制日志（binary log）慢查询日志（Slow query log）查询缓存存储引擎 innoDB 命令存储引擎Buﬀer Pool 配置参数Change BuﬀerLog Buﬀer通用表空间查看线程查看文件存储格式修改文件格式Undo Log 信息Redo Log 信息系统日志命令Binlog 状态查看开启 Binlog 功能常用 Binlog 命令恢复 binlog 数据索引相关命令查看索引删除索引创建普通索引创建唯一索引创建主键索引创建复合索引创建全文索引自适应哈希EXPLAIN 命令慢查询是否开启慢查询开启慢查询查询慢查询日志分页查询查看 profiling 是否开启开启 profiling 功能查询 sql 执行时间分页查询事务相关查看事务隔离级别设置事务隔离级别开启事务添加锁的语句表锁相关共享锁（行级锁-读锁）排他锁（行级锁-写锁）死锁相关

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 连接信息#查看最大连接数，默认是151show 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 //命令，就可以查看当前数据库支持的引擎信息。

Buﬀer Pool 配置参数show variables like '%innodb_page_size%'; //查看page页大小 show variables like '%innodb_old%'; //查看lru list中old列表参数 show variables like '%innodb_buﬀer%'; //查看buﬀer pool参数 # 建议：将innodb_buﬀer_pool_size设置为总内存大小的60%-80%， # innodb_buﬀer_pool_instances可以设置为多个，这样可以避免缓存争夺。

Change Buﬀer# ChangeBuﬀer占用BuﬀerPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。# 参数innodb_change_buﬀer_max_size;show variables like '%innodb_change_buffer_max_size%';

Log Buﬀershow 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 =2innodb_ﬂush_log_at_trx_commit 参数控制日志刷新行为，默认为 10：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuﬀer-->OS cache，刷盘 OS cache--> 磁盘文件），最多丢失 1 秒数据1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作 2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作

通用表空间CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1CREATE 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=mixedserver-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

【本文地址】

公司简介

联系我们