mysql 基本命令使用

在 /etc/my.cnf 中添加 prompt

[mysql]
prompt=(\\u@\\h)[\\d]>\\_

再次连接数据库后，提示由mysql> 变为 (root@localhost)[(none)]>,这种形式可以看到对应的用户名@主机名以及采用的数据库名

查看数据库

(root@localhost)[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

use 数据库名，进入指定数据库

(root@localhost)[(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost)[mysql]>

mysql 配置文件 my.cnf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld] 下面的参数，MySQL 服务器启动时，所用的参数 [mysql] 下面的参数，使用 mysql 命令进入时，所用参数

通过 mysql 命令进入后，如何查看其他参数？

(root@localhost)[(none)]> show variables;

查看所有的参数

可以查看某一部分配置参数


SHOW VARIABLES LIKE 'innodb%';

mysql 配置参数分为两种类型

（1）session

参数只针对当前数据库连接有效

（2）global

全局的针对所有连接有效，但需要注意：如果修改了全局配置参数，已有连接的global参数生效，session参数不生效，新的连接的session和global生效。

8. show variables 只能看到当前连接的参数，show global variables 可以看到全局的配置参数，但是在当前连接下，如何查看其他连接的配置参数呢？

(root@localhost)[(none)]> show DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

(root@localhost)[(none)]> use `performance_schema`;
Database changed

(root@localhost)[performance_schema]> SHOW TABLES LIKE '%variables%';
+--------------------------------------------+
| Tables_in_performance_schema (%variables%) |
+--------------------------------------------+
| global_variables                           |
| session_variables                          |
| user_variables_by_thread                   |
| variables_by_thread                        |
+--------------------------------------------+
4 rows in set (0.00 sec)

(root@localhost)[performance_schema]> SELECT * from variables_by_thread WHERE variable_name = 'long_query_time';
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------+-----------------+----------------+
|        34 | long_query_time | 10.000000      |
|        35 | long_query_time | 10.000000      |
|        39 | long_query_time | 10.000000      |
|        42 | long_query_time | 10.000000      |
|        43 | long_query_time | 10.000000      |
|        45 | long_query_time | 10.000000      |
|        46 | long_query_time | 5.000000       |
|        50 | long_query_time | 5.000000       |
|        51 | long_query_time | 5.000000       |
|        52 | long_query_time | 5.000000       |
+-----------+-----------------+----------------+
10 rows in set (0.01 sec)

THREAD_ID 如何和连接对应？

(root@localhost)[performance_schema]> SHOW PROCESSLIST;
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
| Id | User | Host                | db                 | Command | Time  | State    | Info             |
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
|  9 | root | 192.168.159.1:12455 | NULL               | Sleep   | 12388 |          | NULL             |
| 10 | root | 192.168.159.1:4371  | mysql              | Sleep   | 12372 |          | NULL             |
| 14 | root | localhost           | performance_schema | Query   |     0 | starting | SHOW PROCESSLIST |
| 17 | root | 192.168.159.1:13847 | NULL               | Sleep   |  4656 |          | NULL             |
| 18 | root | 192.168.159.1:5751  | mysql              | Sleep   |  2995 |          | NULL             |
| 20 | root | 192.168.159.1:5757  | mysql              | Sleep   |  4569 |          | NULL             |
| 21 | root | 192.168.159.1:5759  | mysql              | Sleep   |  3016 |          | NULL             |
| 25 | root | 192.168.159.1:9864  | NULL               | Sleep   |  3740 |          | NULL             |
| 26 | root | 192.168.159.1:9865  | mysql              | Sleep   |  3737 |          | NULL             |
| 27 | root | 192.168.159.1:9867  | performance_schema | Sleep   |    31 |          | NULL             |
+----+------+---------------------+--------------------+---------+-------+----------+------------------+
10 rows in set (0.00 sec)

显示 MySQL数据库下的连接ID

通过以下命令，查看PROCESSLIST_ID对应的线程THREAD_ID

(root@localhost)[performance_schema]> SELECT * from threads where PROCESSLIST_ID = 27\G;
*************************** 1. row ***************************
          THREAD_ID: 52
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 27
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: 192.168.159.1
     PROCESSLIST_DB: performance_schema
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 205
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 43705
1 row in set (0.00 sec)

ERROR:
No query specified