数据库运维常用命令

关于锁的三张表：

## 当前运行的所有事务
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 613963
                 trx_state: LOCK WAIT                  #事务状态
               trx_started: 2019-02-22 10:48:48
     trx_requested_lock_id: 613963:460:3:4
          trx_wait_started: 2019-02-22 11:08:41
                trx_weight: 2
       trx_mysql_thread_id: 140
                 trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 7                          #事务锁住的行数
         trx_rows_modified: 0
   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
*************************** 2. row ***************************
                    trx_id: 613962
                 trx_state: RUNNING
               trx_started: 2019-02-22 10:46:29
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 138
                 trx_query: select * from information_schema.innodb_trx
       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: 1
         trx_rows_modified: 1
   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
2 rows in set (0.00 sec)

## 当前出现的锁
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 613963:460:3:4
lock_trx_id: 613963
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: PRIMARY
 lock_space: 460
  lock_page: 3
   lock_rec: 4
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 613962:460:3:4
lock_trx_id: 613962
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: PRIMARY
 lock_space: 460
  lock_page: 3
   lock_rec: 4
  lock_data: 3
2 rows in set, 1 warning (0.00 sec)

## 锁等待的对应关系 
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 613963             
requested_lock_id: 613963:460:3:4         #请求锁的锁ID
  blocking_trx_id: 613962                 #当前拥有锁的事务ID
 blocking_lock_id: 613962:460:3:4
1 row in set, 1 warning (0.00 sec)

查看锁：

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1      |
| Innodb_row_lock_time          | 479764 |
| Innodb_row_lock_time_avg      | 39980  |
| Innodb_row_lock_time_max      | 51021  |
| Innodb_row_lock_waits         | 12     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

解释如下：
Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数  



# 查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | tx1   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

杀死进程：

# 1.查询进程
mysql> show processlist\G                   # \G 结构旋转90度变成纵向
*************************** 1. row ***************************
     Id: 138
   User: root
   Host: localhost:55106
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 140
   User: root
   Host: localhost:56158
     db: test
Command: Sleep   # 正在等待客户端向它发送执行语句
   Time: 145
  State:
   Info: NULL
2 rows in set (0.00 sec)

# 2.杀死对应进程ID 
mysql> kill 140;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 138
   User: root
   Host: localhost:55106
     db: test
Command: Query             #该线程正在执行一个语句            Sleep：线程正在等待客户端向其发送新的语句。
   Time: 0
  State: starting
   Info: show processlist
1 row in set (0.00 sec)

数据库的默认设置:

# 查看数据库的全局变量
mysql > SHOW GLOBAL VARIABLES;

# 查看数据库默认存储引擎
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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

# 查看当前数据库默认隔离级别
mysql> select @@global.tx_isolation; 
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 查看当前session中的autocommit
mysql > SELECT @@autocommit;
# 查看全局的默认自动提交事务
mysql> show global variables like 'autocommit';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

# 查看最大链接数
mysql> SHOW VARIABLES LIKE 'max_connections';