Common MySQL Commands and Practical Usage Guide

1. Connect to MySQL

Use the following command to connect to a local MySQL instance:

mysql -u username -p -h host_name -P port_number

For remote connections replace host_name with the remote IP and usually use port 3306: mysql -u username -p -h remote_host_ip -P 3306 remote_host_ip: IP address of the remote MySQL server.

3306: Default MySQL port (change if a different port is used).

2. View Running Threads

show processlist;

SHOW PROCESSLIST returns a list of active connection threads, their status, and running queries, useful for diagnosing performance issues and deadlocks.

3. Check System Variables

To see if the slow‑query log is enabled: show variables like 'slow_query_log'; Adding GLOBAL queries the global server variables instead of the session ones:

show global variables like 'slow_query_log';

show variables like 'slow_query_log';

checks the current session's setting. show global variables like 'slow_query_log'; checks the server‑wide configuration.

Other example variable: show global variables like 'sync_binlog'; sync_binlog controls how often the binary log is flushed to disk:

1 – flush after every write (maximum durability).

0 – rely on OS caching (better performance, possible data loss on crash).

4. View Lock Information (MySQL 8.0+)

SELECT * FROM performance_schema.data_locks\G;

This query shows current and waiting data locks, including type, status, and holder.

5. Check and Set Transaction Isolation Level

select @@tx_isolation;          -- current session isolation
select @@global.tx_isolation;    -- global isolation

To set the global isolation level:

set global TRANSACTION ISOLATION level read COMMITTED;

6. Index Operations

Show indexes of a table: show index from table_name; Example output (truncated) shows index name, column, uniqueness, etc.

To view the table definition (including indexes): show create table user_tab; Add a new index:

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

Delete an index:

ALTER TABLE table_name DROP INDEX index_name;

7. View InnoDB Deadlock Logs

show engine innodb status;

This command displays current InnoDB status, including lock information, active transactions, buffer pool usage, redo/undo logs, and row operation statistics, which are essential for deadlock analysis.

8. List Databases and Tables

show databases;

Select a database and list its tables:

use test_db;
show tables;

9. View Uncommitted Transactions

SELECT * FROM information_schema.innodb_trx;

This query lists active InnoDB transactions, useful for diagnosing long‑running or stuck transactions.

10. Check Supported Storage Engines

SHOW ENGINES;
SELECT * FROM information_schema.ENGINES;

11. Check Database Character Set and Collation

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
SELECT * FROM information_schema.schemata;

12. SQL Import/Export

Export specific tables:

mysqldump -u your_username -p your_database_name table1 table2 > export_file.sql

Export only the schema (no data):

mysqldump -u your_username -p --no-data your_database_name > structure_only.sql

Import a dump file:

mysql -u your_username -p your_database_name < import_file.sql

13. Create Table, Add Columns, Position Columns

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

ALTER TABLE users ADD COLUMN age INT;

ALTER TABLE users ADD COLUMN age INT AFTER email;

14. Stored Procedure for Bulk Inserts

DELIMITER //
CREATE PROCEDURE BatchInsertUsers(IN numUsers INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= numUsers DO
        INSERT INTO user_tab (username) VALUES (CONCAT('user', i));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL BatchInsertUsers(100);

15. Create a View

A view provides a virtual table based on a SELECT query:

CREATE VIEW all_users AS
SELECT id, username FROM user_table_1
UNION ALL
SELECT id, username FROM user_table_2;

Querying the view returns combined data from both underlying tables.