MySQL Administration: -
The following contains information on how to retrieve database information for MySQL objects such as databases, tables, views, users, indexes, and constraints.
The method of retrieving system information from MySQL is dependent on the MySQL version. For MySQL versions 5 and newer, MySQL has followed the lead of the enterprise databases such as Oracle, DB2, and SQL Server by providing an information schema with tables that contain data relating to system information. Prior to MySQL 5, special queries needed to be executed to get this type of information. When applicable, both sets of queries will be listed.
The following contains information on how to retrieve database information for MySQL objects such as databases, tables, views, users, indexes, and constraints.
The method of retrieving system information from MySQL is dependent on the MySQL version. For MySQL versions 5 and newer, MySQL has followed the lead of the enterprise databases such as Oracle, DB2, and SQL Server by providing an information schema with tables that contain data relating to system information. Prior to MySQL 5, special queries needed to be executed to get this type of information. When applicable, both sets of queries will be listed.
1- Show databases: - Use to list of database.
To execute below command first, login to MySQL server.
root@US16:~# mysql -u root -p
Enter password:********
mysql> show databases;
Now, you can execute below commands,
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eno8 |
| masterrep1 |
| mysql |
| performance_schema |
| soft |
| sys |
| tally |
+--------------------+
8 rows in set (0.12 sec)
2- Select databases: - To select specific database
mysql> use mysql;
Database changed
3- Show tables: -To show tables in a specific database first, we need to select that database using above command then we can run the command to list all the tables
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
4- Show field of tables: - To display field of tablet use below command.
mysql> select user,host,account_locked from user;
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| root | localhost | N |
| mysql.sys | localhost | Y |
| debian-sys-maint | localhost | N |
| replicator | % | N |
| mysql.session | localhost | Y |
+------------------+-----------+----------------+
5 rows in set (0.00 sec)
5- Show users host and database: - To check what user has permission on what database and accessible from which host,
mysql> select db,host,user from mysql.db; +--------------------+-----------+---------------+ | db | host | user | +--------------------+-----------+---------------+ | performance_schema | localhost | mysql.session | | sys | localhost | mysql.sys | +--------------------+-----------+---------------+ 2 rows in set (0.00 sec)
6- Show a user's information: It is very important to a MySQL administrator to know how to check specific user's information.
First, we need to select database mysql to execute the command as follow.
mysql> use mysql;
Database changed
mysql> select user,host,authentication_string from user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
7- Show all running process: - It is a very useful command to check all running process. It helps us to identify sleep and active queries.
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.03 sec)
8- Show all the variables: - Sometimes, we might require checking all the variable when we don't remember the name of variables. use the command below.
mysql> show variables \G
*************************** 1. row ***************************
Variable_name: auto_increment_increment
Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
Value: 1
*************************** 3. row ***************************
Variable_name: autocommit
Value: ON
*************************** 4. row ***************************
Variable_name: automatic_sp_privileges
Value: ON
*************************** 5. row ***************************
Variable_name: avoid_temporal_upgrade
Value: OFF
*************************** 6. row ***************************
Variable_name: back_log
Value: 80
*************************** 7. row ***************************
Variable_name: basedir
Value: /usr/
9- Display specific variables: - Sometimes, we may require checking the value of the specific variable for this case you need to put correct name of variables, use the command below to check value.
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
In the above example, we checked the value of max connections. In the same way, we can check any variables value.
10- Show variables with few characters: -Sometimes, We can't remember the name of variables, in that case, we can use few characters of the variable to list all related variables.
For example, I need to check the version of InnoDB but I don't know right variables name, let's use the command below to list all the version variables.
mysql> show variables like '%version%';
+-------------------------+-----------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------+
| innodb_version | 5.7.19 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.19-0ubuntu0.16.04.1-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-----------------------------+
11- Display process list for a specific user: - MySQL administrator may require listing all the running process for a specific user. In this case use below command.
mysql> SELECT * FROM information_schema.processlist WHERE USER='root';
+----+------+-----------+------+---------+------+-----------+----------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------------------------+
| 9 | root | localhost | NULL | Query | 0 | executing | SELECT * FROM information_schema.processlist WHERE USER='root' |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
12- Display all the sleep query: -Sometimes, MySQL administrator may experience show query against database in that case, We can check all the sleep query that may cause.
mysql> SELECT * FROM information_schema.processlist WHERE COMMAND='Sleep';
+--------+--------------+-----------+----------------------+---------+------+-------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+--------+--------------+-----------+----------------------+---------+------+-------+------+
| 144815 | mysqltestusr | localhost | mysq_ltest_database |Sleep | 139 | | NULL |
| 144874 | mysqltestusr | localhost | mysq_ltest_database |Sleep | 19 | | NULL |
| 144820 | mysqltestusr | localhost | mysq_ltest_database |Sleep | 79 | | NULL |
+--------+--------------+-----------+----------------------+---------+------+-------+------+
3 rows in set (0.00 sec)
13- Display database list without entering into MySQL prompt: - We can list or execute some command from server terminal only don't require to go MySQL prompt.
root@US16:~# mysql -u root -p -e "show databases"
Enter password: *****
+--------------------+
| Database |
+--------------------+
| information_schema |
| eno8 |
| masterrep1 |
| mysql |
| performance_schema |
| soft |
| sys |
| tally |
+--------------------+
14- Display process list count by user:
mysql> SELECT `USER`, COUNT(*) FROM information_schema.processlist GROUP BY `USER`;
+------+----------+
| USER | COUNT(*) |
+------+----------+
| root | 1 |
+------+----------+
1 row in set (0.01 sec)
14- Display process list count by HOST:
mysql> SELECT `HOST`, COUNT(*) FROM information_schema.processlist GROUP BY `HOST`;
+-----------+----------+
| HOST | COUNT(*) |
+-----------+----------+
| localhost | 1 |
+-----------+----------+
1 row in set (0.00 sec)
15- Display process list count by Database:
mysql> SELECT `DB`, COUNT(*) FROM information_schema.processlist GROUP BY `DB`; +------+----------+ | DB | COUNT(*) | +------+----------+ | Mysql| 1 | +------+----------+ 1 row in set (0.01 sec)
No comments:
Post a Comment