Go MySQL View database related information

Original link: MySQL View database related information

When using MySQL, you need to know the current database, such as the current database size, character set, user, and so on. Here's a summary of some commands for viewing database-related information

1: View Show all databases

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | INVOICE            | | mysql              | | performance_schema | | test               

2: View the currently used database

Mysql> Select Database (); +------------+| Database () |+------------+| INVOICE    

3: View Database Usage port

Mysql> Show variables like  ' Port ', +---------------+-------+| variable_name | Value |+---------------+-------+| Port          | 3306  |+---------------+-------+1 row in Set (0.00 sec)

4: View current database size

For example, if I want to see the size of the invoice database, you can view it from the SQL below

mysql> Use  information_schemareading table information for completion of table and column namesyou can turn off thi  s feature to get a quicker startup with-a Database changedmysql> 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)

View the amount of space your data occupies

mysql> use INFORMATION_SCHEMA; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-a Database changedmysql> Select concat (Round (SUM (data_length)/(1024*1024), 2), ' MB ') as ' DB Size ', from    Tables    , where table_schema= ' INVOICE '; +-----------+| DB Size   

View the amount of space the index occupies

Mysql> Select concat (Round (SUM (index_length)/(1024*1024), 2), ' MB ') as ' DB Size ' from     tables     where table_schema= ' INVOICE '; +-----------+| DB Size   

5: View Database encoding

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)

Character_set_client is the client-side encoding method;

Character_set_connection the encoding used to establish the connection;

Character_set_database the encoding of the database;

Character_set_results is the encoding of the result set;

Character_set_server is the encoding of the database server;

As long as the above to ensure that the encoding method, there will be no garbled problem.

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)

Status can also view the encoding of the database

mysql> status;--------------mysql  Ver 14.14 distrib 5.6.20, for Linux (x86_64) using  editline Wrapper Connection ID:          1Current database:       invoicecurrent User:           [email Protected]ssl: Not in                    usecurrent pager:          stdoutusing outfile:          ' Using delimiter:        ; Server version:         5.6.20-enterprise-commercial-advanced MySQL Enterprise server-advanced Edition (commercial) Protocol version:       10Connection:             Localhost via UNIX socketserver characterset:    latin1db     CharacterSet:    latin1client characterset:    utf8conn.  CharacterSet:    Utf8unix Socket:            /var/lib/mysql/mysql.sockuptime:                 5 hours min sec threads:1  questions:10884  Slow queries:0  opens:650  Flush tables:1  Open tables:268  

6: View table information for the database

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)

Or, use the following SQL statement to view table information for a database.

SELECT * from Information_schema.tables where table_schema= ' databasename ';

View information for a specific table

SELECT * FROM information_schema.tables WHERE table_name = ' table_name '

7: View all user information for the database

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 ';           

8: View permissions for a specific user

Mysql> show grants for ' root ' @ ' localhost '; +-------------------------------------------------------------------- -------------------------------------------------------------+| Grants for [email protected]                                                                                                              |+------------------------------------------------------------------------------ ---------------------------------------------------+|  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)

9: View the maximum number of connections for a database

Mysql>  Show variables like '%max_connections% '; +-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   

10: View database Current number of connections, number of concurrent.

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: Represents how many idle threads are currently in the thread cache at this moment.

Threads_connected: Represents the number of currently established connections, because a connection requires a thread, so it can also be considered as the number of threads currently being used.

Threads_created: Represents the number of threads that have been created since the most recent service was started.

Threads_running: Represents the number of currently active (non-sleep) threads. Does not represent the number of threads in use, sometimes the connection is established, but the connection is in the sleep state, where the corresponding thread is the sleep state.

11: View data File storage path

Mysql> Show variables like '%datadir% '; +---------------+-------------------+| variable_name | Value             |+---------------+-------------------+| datadir       

Go MySQL View database related information