In this post, we will be discussing the most frequently used admin commands specifically the SHOW commands. SHOW is a command that displays information about active connections and database objects.
SHOW STATUS;
SHOW STATUS WHERE variable_name = ‘threads_connected’;
The SHOW STATUS statement displays server status information. Along with this statement, the LIKE clause aids in matching the specific variable. The WHERE clause can be used to retrieve rows based on broad criteria. This statement does not necessitate any special permissions. In the above example, we are trying to fetch complete server-level status including aborted clients, aborted connections, bytes sent and received, the total number of threads connected and running, last query cost, etc. If any specific variable to be retrieved, then specify the variable name in the where clause.
SHOW VARIABLES;
SHOW VARIABLES WHERE Variable_name like “max_connections”;
SHOW VARIABLES LIKE “max_connections”;
SHOW VARIABLES displays all current configuration settings by displaying the values of MySQL system variables. This statement does not necessitate any special permissions. Only the ability to connect to the server is required. If a LIKE clause is present in SHOW VARIABLES, it specifies which variable names to match. A WHERE clause can be used to pick data based on broader criteria.
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
The MySQL process list displays the operations that are currently being carried out by the server’s threads. You can see all threads, including those belonging to other users, if you have the PROCESS privilege. Otherwise, anonymous users have no access to thread information, while nonanonymous users have access to information about their own threads but not threads for other users.
SHOW PROCESSLIST without the FULL keyword only shows the first 100 characters of each statement in the Info field.
If you get the “too many connections” error notice and want to figure out what’s going on, this statement comes in handy. To ensure that administrators may always connect and check the system, MySQL reserves one additional connection for accounts with the SUPER access.
SHOW DATABASES;
SHOW DATABASES LIKE ‘%sometest%’;
SHOW TABLES;
SHOW FULL TABLES;
SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB_TEMP%’;
The aforementioned commands are used to display a list of all MySQL databases and tables. The table type will be displayed as a second output column if the FULL modifier is used in the SHOW TABLES command.
SHOW TABLES FROM database name;
Use the FROM or IN clause followed by the database name to receive a list of the tables without switching to the database as shown below:
SHOW ENGINE INNODB STATUS;
is a variant of the SHOW ENGINE query that displays the InnoDB Monitor output, which contains a wealth of InnoDB data that might be helpful in troubleshooting.
SHOW OPEN TABLES WHERE In_Use>0;
The column “In_use” is checked to see if it is greater than 0. The table will be locked in that instance.
SHOW INDEX FROM `mysql`.`db`;
The above command is used to display the indexes created on the specific table.
SHOW CREATE TABLE <tableName>;
SHOW CREATE VIEW <viewName>;
The above statements return the definition of the table or the view. It will help in copying the specific schema of the table or view in another database/instance.
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 