mysql 常用命令

最新推荐文章于 2023-07-12 02:12:35 发布

thlzjfefe

最新推荐文章于 2023-07-12 02:12:35 发布

阅读量636

点赞数

分类专栏： MySQL 文章标签： mysql 常用命令

本文链接：https://blog.csdn.net/thlzjfefe/article/details/102526455

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

1、查看隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

2、查看线程连接情况

3、查看事务表，看是否有锁定的线程，如果该锁定的线程id存在于show full processlist 的sleep状态中，则表明此线程被卡住了，需要kill掉

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 202348
trx_state: RUNNING
trx_started: 2019-10-12 20:04:22
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 6
trx_mysql_thread_id: 7706 //此即为被锁定的线程id
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
trx_rows_modified: 4
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

4、kill线程

kill 7706

5、查看是否自动提交事务：0-不自动提交 1-自动提交

mysql> select @@autocommit; 或 show variables like 'autocommit';
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

6、修改是否自动提交事务

set global autocommit=1;

7、查看使用的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

8、查看行级锁的争夺情况

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 551475 |
| Innodb_row_lock_time_avg | 1519 |
| Innodb_row_lock_time_max | 51008 |
| Innodb_row_lock_waits | 363 |
+-------------------------------+--------+

InnoDB 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
InnoDB_row_lock_current_waits：当前正在等待锁定的数量；
InnoDB_row_lock_time：从系统启动到现在锁定总时间长度；
InnoDB_row_lock_time_avg：每次等待所花平均时间；
InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
InnoDB_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是InnoDB_row_lock_time_avg（等待平均时长），InnoDB_row_lock_waits（等待总次数）以及InnoDB_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
锁冲突的表、数据行等，并分析锁争用的原因。具体方法如下：

mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

然后就可以用下面的语句来进行查看：

mysql> show engine InnoDB status;

监视器可以通过发出下列语句来停止查看：

mysql> drop table InnoDB_monitor;

设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“--console”选项来启动服务器以关闭写日志文件

9、查看表中字段情况

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

10、手动控制事务

1）设置不自动提交事务

set autocommit=0;

2）开启事务

start transaction;

11、提交或混滚事务

commit 或 rollback

12、查询超时时间

show global variables like 'wait_timeout';

二、慢查询

1、慢查询配置查看

show variables like '%slow_query_log%';

2、慢查询日志存放路径

show variables like 'slow_query_log_file';

3、开启慢查询

1）set global slow_query_log=1；此方法只对当前数据库有效，且重启后会失效

2）永久生效，就必须修改配置文件my.cnf，修改如下

slow_query_log = 1 slow_query_log_file = /tmp/mysql_slow.log

4、查看慢查询时间配置

show variables like 'long_query_time%';

5、修改慢查询时间

set global long_query_time=5;

6、慢查询输出方式，默认输出到文件

show variables like '%log_output%';

7、设置慢查询输出方式

set global log_output='TABLE';

三、重建索引

optimize table student；

十二、加锁、解锁、查看锁

1、查询锁等待超时时间

show global variables like 'innodb_lock_wait_timeout';

2、解锁

1）show processlist

找到id，然后kill掉

2）unlock tables;

3、锁表

1）锁定数据表，避免在备份过程中，表被更新

LOCK TABLES tbl_name READ;

2）为表增加一个写锁定

LOCK TABLES tbl_name WRITE;

4、

1、查询是否锁表
show OPEN TABLES where In_use > 0;

2、查询进程
show processlist
查询到相对应的进程===然后 kill id

补充：
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SELECT * from information_schema.INNODB_TRX
kill trx_mysql_thread_id

5、查看表的状态

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 2979 |

| Table_locks_waited | 0 |

+-----------------------+-------+

2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

获取InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

+-------------------------------+-------+

| Variable_name | Value |

+-------------------------------+-------+

| InnoDB_row_lock_current_waits | 0 |

| InnoDB_row_lock_time | 0 |

| InnoDB_row_lock_time_avg | 0 |

| InnoDB_row_lock_time_max | 0 |

| InnoDB_row_lock_waits | 0 |

+-------------------------------+-------+

5 rows in set (0.01 sec)

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

6、SHOW INNODB STATUS

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施

7、查看线程情况

show global status like 'thread%';

查询服务器 thread_cache_size 配置

show variables like 'thread_cache_size';

threads_created表示创建过的线程数，很明显，threads_created过大，表明mysql服务器一直在创建线程，这也是比较耗资源，说明服务器不健康

解决方法：

适当增加配置文件中thread_cache_size值，在my.cnf文件中直接加上thread_cache_size=64；

重启Mysql服务，问题及解决。

thread_cache_size作用：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）

参考 https://blog.csdn.net/hxpjava1/article/details/79407961

thlzjfefe

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 常用命令

1、查看隔离级别mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set, 1 warning (0.00 sec)2、查看线程连接情况mysql&...
复制链接

扫一扫

专栏目录