- 在 /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
京公网安备 11010502036488号