Bagaimana untuk menanyakan status sambungan pangkalan data dan maklumat sambungan dalam Mysql

Lihat dan paparkan semua pangkalan data

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| INVOICE            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 
mysql>

Lihat pangkalan data yang sedang digunakan

mysql> select database();
+------------+
| database() |
+------------+
| INVOICE    |
+------------+
1 row in set (0.00 sec)
 
mysql>

Lihat port yang digunakan oleh pangkalan data

mysql> show variables  like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

Lihat saiz pangkalan data semasa

Sebagai contoh, I Untuk menyemak saiz pangkalan data INVOIS, anda boleh menggunakan SQL berikut untuk melihat

mysql> use  information_schema
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
mysql> select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'
    -> from tables 
    -> where table_schema='INVOICE';
+-----------+
| DB Size   |
+-----------+
| 7929.58MB |
+-----------+
1 row in set, 1 warning (0.00 sec)

Lihat ruang yang diduduki oleh data

mysql> use information_schema;
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
mysql> select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'
    -> from tables
    -> where table_schema='INVOICE';
+-----------+
| DB Size   |
+-----------+
| 6430.26MB |
+-----------+
1 row in set, 1 warning (0.00 sec)
 
mysql>

Lihat ruang yang diduduki oleh indeks

mysql> select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 
    -> from tables 
    -> where table_schema='INVOICE';
+-----------+
| DB Size   |
+-----------+
| 1499.32MB |
+-----------+
1 row in set, 1 warning (0.13 sec)
 
mysql>

Lihat pengekodan pangkalan data

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

• set_karakter_pelanggan ialah kaedah pengekodan klien

• pengekodan_set_pelanggan yang digunakan; wujudkan sambungan;

• character_set_database ialah pengekodan pangkalan data; >

character_set_server ialah pengekodan pelayan pangkalan data;

• mysql> show variables like 'collation%';
  +----------------------+-------------------+
  | Variable_name        | Value             |
  +----------------------+-------------------+
  | collation_connection | utf8_general_ci   |
  | collation_database   | utf8_general_ci   |
  | collation_server     | latin1_swedish_ci |
  +----------------------+-------------------+
  3 rows in set (0.00 sec)

  Salin selepas log masuk

  status juga boleh melihat pengekodan pangkalan data

  mysql> status;
  --------------
  mysql  Ver 14.14 Distrib 5.6.20, for Linux (x86_64) using  EditLine wrapper
   
  Connection id:          1
  Current database:       INVOICE
  Current user:           root@localhost
  SSL:                    Not in use
  Current pager:          stdout
  Using outfile:          ''
  Using delimiter:        ;
  Server version:         5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
  Protocol version:       10
  Connection:             Localhost via UNIX socket
  Server characterset:    latin1
  Db     characterset:    latin1
  Client characterset:    utf8
  Conn.  characterset:    utf8
  UNIX socket:            /var/lib/mysql/mysql.sock
  Uptime:                 5 hours 18 min 51 sec
   
  Threads: 1  Questions: 10884  Slow queries: 0  Opens: 650  Flush tables: 1  Open tables: 268  Queries per second avg: 0.568
  --------------
  mysql>

  Salin selepas log masuk
Lihat maklumat jadual pangkalan data

• mysql> show tables;
  +---------------------------------------+
  | Tables_in_information_schema          |
  +---------------------------------------+
  | CHARACTER_SETS                        |
  | COLLATIONS                            |
  | COLLATION_CHARACTER_SET_APPLICABILITY |
  | COLUMNS                               |
  | COLUMN_PRIVILEGES                     |
  | ENGINES                               |
  | EVENTS                                |
  | FILES                                 |
  | GLOBAL_STATUS                         |
  | GLOBAL_VARIABLES                      |
  | KEY_COLUMN_USAGE                      |
  | OPTIMIZER_TRACE                       |
  | PARAMETERS                            |
  | PARTITIONS                            |
  | PLUGINS                               |
  | PROCESSLIST                           |
  | PROFILING                             |
  | REFERENTIAL_CONSTRAINTS               |
  | ROUTINES                              |
  | SCHEMATA                              |
  | SCHEMA_PRIVILEGES                     |
  | SESSION_STATUS                        |
  | SESSION_VARIABLES                     |
  | STATISTICS                            |
  | TABLES                                |
  | TABLESPACES                           |
  | TABLE_CONSTRAINTS                     |
  | TABLE_PRIVILEGES                      |
  | TRIGGERS                              |
  | USER_PRIVILEGES                       |
  | VIEWS                                 |
  | INNODB_LOCKS                          |
  | INNODB_TRX                            |
  | INNODB_SYS_DATAFILES                  |
  | INNODB_LOCK_WAITS                     |
  | INNODB_SYS_TABLESTATS                 |
  | INNODB_CMP                            |
  | INNODB_METRICS                        |
  | INNODB_CMP_RESET                      |
  | INNODB_CMP_PER_INDEX                  |
  | INNODB_CMPMEM_RESET                   |
  | INNODB_FT_DELETED                     |
  | INNODB_BUFFER_PAGE_LRU                |
  | INNODB_SYS_FOREIGN                    |
  | INNODB_SYS_COLUMNS                    |
  | INNODB_SYS_INDEXES                    |
  | INNODB_FT_DEFAULT_STOPWORD            |
  | INNODB_SYS_FIELDS                     |
  | INNODB_CMP_PER_INDEX_RESET            |
  | INNODB_BUFFER_PAGE                    |
  | INNODB_CMPMEM                         |
  | INNODB_FT_INDEX_TABLE                 |
  | INNODB_FT_BEING_DELETED               |
  | INNODB_SYS_TABLESPACES                |
  | INNODB_FT_INDEX_CACHE                 |
  | INNODB_SYS_FOREIGN_COLS               |
  | INNODB_SYS_TABLES                     |
  | INNODB_BUFFER_POOL_STATS              |
  | INNODB_FT_CONFIG                      |
  +---------------------------------------+
  59 rows in set (0.00 sec)

  Salin selepas log masuk

  Atau gunakan pernyataan SQL berikut untuk melihat maklumat jadual sesuatu pangkalan data.

  select * from information_schema.tables where table_schema=‘databasename';

  Salin selepas log masuk
Lihat maklumat jadual tertentu

select * from information_schema.tables where table_name =‘table_name'

Lihat semua maklumat pengguna dalam pangkalan data

mysql>  select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;
+-------------------------------------+
| query                               |
+-------------------------------------+
| user: 'root'@'127.0.0.1';           |
| user: 'root'@'::1';                 |
| user: 'root'@'gettesx20.test.com'; |
| user: 'root'@'localhost';           |
+-------------------------------------+
4 rows in set (0.00 sec)
 
mysql>

Lihat kebenaran pengguna tertentu

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Lihat pangkalan data Bilangan maksimum sambungan

mysql>  show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
 
mysql>

Semak bilangan sambungan semasa dan konkurensi pangkalan data.

mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached: Mewakili bilangan benang terbiar dalam cache thread pada masa ini.

Threads_connected: Mewakili bilangan sambungan yang diwujudkan pada masa ini Kerana satu sambungan memerlukan satu utas, ia juga boleh dianggap sebagai bilangan utas yang sedang digunakan.

Threads_created: mewakili bilangan urutan yang dibuat sejak permulaan perkhidmatan terakhir.

Ayat ini boleh ditulis semula sebagai: Threads_running menunjukkan bilangan utas yang sedang aktif, iaitu, tidak tidur. Ia tidak mewakili bilangan utas yang digunakan Kadangkala sambungan telah diwujudkan, tetapi sambungan berada dalam keadaan tidur, dan utas yang sepadan di sini juga dalam keadaan tidur.

Lihat laluan storan fail data

mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /mysqldata/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)
 
mysql>

Atas ialah kandungan terperinci Bagaimana untuk menanyakan status sambungan pangkalan data dan maklumat sambungan dalam Mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!