MySQL CLI Quick Reference

Connecting to MySQL

  • Login to MySQL:
  mysql -u username -p

Prompts for a password.

  • Specify Database on Login:
  mysql -u username -p -D database_name

Basic Commands

  • Show all databases:
  SHOW DATABASES;
  • Select a database:
  USE database_name;
  • Show all tables in the current database:
  SHOW TABLES;
  • Describe the structure of a table:
  DESCRIBE table_name;
  • Show the current MySQL user:
  SELECT USER();
  • Show current database:
  SELECT DATABASE();
  • Show process list:
  SHOW PROCESSLIST;
  • Show full process list (to see full queries):
  SHOW FULL PROCESSLIST;
  • Exit MySQL:
  EXIT;

CRUD Operations

  • Create a new database:
  CREATE DATABASE database_name;
  • Create a new table:
  CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
  );
  • Insert data into a table:
  INSERT INTO table_name (name, age) VALUES ('John', 30);
  • Select data from a table:
  SELECT * FROM table_name;
  • Update data in a table:
  UPDATE table_name SET age = 31 WHERE name = 'John';
  • Delete data from a table:
  DELETE FROM table_name WHERE name = 'John';
  • Drop a table:
  DROP TABLE table_name;

Loading .sql Files

To load and execute an SQL file that contains queries or database structure (e.g., schema.sql or data.sql):

Method 1: From Inside the MySQL Prompt
  1. Open MySQL:
   mysql -u username -p
  1. Select the database you want to load the file into:
   USE database_name;
  1. Load the SQL file:
   SOURCE /path/to/your/file.sql;
Method 2: Directly from the Command Line

You can execute the SQL file directly from the command line without entering the MySQL prompt:

mysql -u username -p database_name < /path/to/your/file.sql

This method runs the .sql file directly into the specified database.

Common Tips

  • View running MySQL queries:
    Use SHOW FULL PROCESSLIST; to view running queries, especially helpful for debugging long-running queries.
  • Check MySQL version:
  SELECT VERSION();
  • Enable SQL logging:
    Use SET GLOBAL general_log = 'ON'; to start logging all queries. To view the log, check the path from the following:
  SHOW VARIABLES LIKE 'general_log_file';

These commands and tips should help you navigate MySQL efficiently!


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Discover more from Jorge Saldívar

Subscribe now to keep reading and get access to the full archive.

Continue reading