mysql-其他常用命令

连接Mysql （客户端工具NaviCat、phpMyAdmin、MySQL-Front）

mysql -h 地址 -P 端口 -u 用户名 -p 密码
mysql -h 172.16.16.45 -P 3306 -u root -p123  -- 注意：“-p”和“123” 之间不能有空格，u与root之间可以不用加空格

mysql -hip -uroot -p账户密码

mysql --host=ip --user=root --password=连接目标的密码

MySQL 连接本地数据库，用户名为“root”，指定所连接的数据库为“test”
mysql -h localhost -u root -p123 -D test

退出MYSQL命令： exit或者quit （回车）

mysql其他常用命令

SHOW VARIABLES -- 显示mysql系统信息

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

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

select sleep(4); 

查看事务

查看当前运行得知事务

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配置文件详细

来自： 元动力