Essential MySQL Guide: Login, Services, Transactions, Isolation Levels & More

1. How to log into MySQL database

mysql -u username -p

2. How to start/stop MySQL service

service mysql start / stop

3. Check MySQL status

service mysql status

4. Show all databases

show databases

5. Get column names and types of a table

describe table_name;

6. Does MySQL support transactions?

By default MySQL runs in autocommit mode, so it does not support transactions unless the storage engine is InnoDB or BDB. Setting SET AUTOCOMMIT=0 enables manual commit/rollback.

Example:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

7. What are MySQL’s characteristics compared with other databases?

MySQL is a lightweight relational DBMS, originally developed by MySQL AB and now owned by Sun. It runs on many OSes and, compared with Oracle, DB2, SQL Server, it has slightly weaker features but offers:

Handles tens of millions of rows.

Supports standard SQL syntax.

Easy to install and portable.

Good performance with strong community support.

Relatively simple debugging, management, and optimization.

8. Difference between VARCHAR and CHAR

CHAR is fixed‑length; VARCHAR is variable‑length.

9. Types of database transactions

Isolation, durability, consistency, atomicity.

10. InnoDB transaction isolation levels and differences

SQL standard defines four levels:

Read Uncommitted – can read uncommitted changes (dirty read).

Read Committed – can read only committed changes.

Repeatable Read – default in MySQL; same rows are seen in repeated reads, but may allow phantom reads.

Serializable – highest level; forces strict ordering to avoid conflicts.

Detailed explanations of each level, including phenomena such as dirty read, non‑repeatable read, and phantom read, are provided.

11. Differences between MyISAM and InnoDB storage engines

12. MySQL privilege tables

MySQL stores privileges in the mysql database: user, db, table_priv, columns_priv, host.

Descriptions:

user – global user accounts.

db – database‑level privileges.

table_priv – table‑level privileges.

columns_priv – column‑level privileges.

host – host‑level control, not affected by GRANT/REVOKE.

13. MySQL storage engines and how to change them

Common engines: MyISAM, MEMORY, MERGE, ARCHIVE, CSV, InnoDB.

To change engine:

ALTER TABLE tablename ENGINE=InnoDB;

14. MySQL table repair and recovery interview questions

Causes of table corruption: sudden power loss, forced shutdown without stopping MySQL.

Symptoms: “Incorrect key file for table”, “Table … is marked as crashed”, inability to open .MYI files.

Repair methods: use myisamchk –recover after stopping MySQL, or SQL commands REPAIR TABLE table_name, OPTIMIZE TABLE table_name.

15. MySQL performance analysis commands

Useful commands:

SHOW STATUS

SHOW SESSION STATUS LIKE 'Select%'

SET profiling=1; SHOW PROFILES; SHOW PROFILE;

Variables to monitor: Bytes_received, Bytes_sent, Com_*, Created_*, Handler_*, Select_*, Sort_*.

16. Recommended column type for monetary values

Use DECIMAL (or NUMERIC) to store exact monetary amounts, e.g., salary DECIMAL(9,2), which provides 9 total digits with 2 after the decimal point.