mysql-其他常用命令

mysql其他常用命令

SHOW VARIABLES -- 显示mysql系统信息

SELECT now(), user(), version(); --显示当前时间、用户名、数据库版本

system clear 清屏 -- linux下的命令，windows下不行

select sleep(4); 

MySQL数据库备份还原

备份： ummysqldp -u用户名 -p密码 数据库名称 > 保存的路径

还原：
	1. 登录数据库
	2. 创建数据库
	3. 使用数据库
	4. 执行文件 source 文件路径

查看事务

查看当前运行得知事务

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1653
                 trx_state: RUNNING
               trx_started: 2022-07-20 19:38:50
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 24   (事务所在线程ID:)
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           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: 10000
1 row in set (0.00 sec)

mysql>

SHOW PROCESSLIST -- 显示哪些线程正在运行

mysql> show processlist; 
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 21 | root | localhost | test | Query   |    0 | NULL  | show processlist |
| 24 | root | localhost | test | Sleep   |   47 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

kill id值; -- 根据查到的id终止连接当前正在执行的语句，连接会断开。

mysql> select sleep(100);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select sleep(100);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    23
Current database: test

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

MySQL配置文件详细

来自： 元动力