Managing MySQL databases and users through the MySQL Command Line is often more efficient than using graphical tools. Administrators gain faster navigation, reduced overhead, and a deeper understanding of core functionality. This post explores how to create databases, build tables, list existing objects, update data, and handle user accounts from the MySQL Command Line.
TL;DR
Using the MySQL Command Line involves creating databases, building tables, editing records, and adjusting user privileges. This approach offers control over database structures and user access without relying on external interfaces. Proper command line usage ensures effective resource management and faster response times.
MySQL Command Line Overview
The MySQL Command Line provides direct access to core features such as database creation, table management, and user administration. Basic familiarity with MySQL’s official documentation is recommended before proceeding. Make sure you have valid credentials and a stable connection to the server.
Creating a Database
Creating a database is straightforward once you are connected:
CREATE DATABASE my_app_db; Replace my_app_db with your chosen name. The command allocates space for a new project or environment.
Listing Databases
To view all existing databases on your server, use:
SHOW DATABASES; This command displays a list of available databases, including system-related ones like information_schema or mysql, which contain internal data.
Creating a Table
After switching to your target database:
USE my_app_db; Define a table with fields:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
role VARCHAR(50),
hire_date DATE
);This creates an employees table with columns for ID, name, role, and hire date.
Listing Tables
To confirm that your new table exists, run:
SHOW TABLES;You should see employees listed as part of your database objects. This is a quick way to verify table creation.
Managing MySQL Databases: Checking Table Details
You can review table structure and confirm column definitions:
DESCRIBE employees; This command displays each column, data type, and additional attributes like AUTO_INCREMENT or PRIMARY KEY.
Adding a Record to a aTable
Inserting a new row into a table can be done with an INSERT statement:
INSERT INTO employees (name, role, hire_date)
VALUES ('Alice Johnson', 'Developer', '2025-01-13'); Omit the id field if it is set to auto-increment. The row now exists in the employees table.
Deleting a Record
If you need to remove an entry, use DELETE:
DELETE FROM employees
WHERE id = 1; Always ensure you provide a WHERE clause to limit deletions to specific records. Otherwise, you risk removing all data in the table.
Updating a Record
To modify existing data, rely on UPDATE:
UPDATE employees
SET role = 'Senior Developer'
WHERE id = 2; This updates the role column for the employee whose id is 2. Use filters carefully when updating data to avoid unintended changes.
Managing Users
User management includes creation, privilege assignment, and revocation. For instance, to create a user:
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'StrongPassword123'; Grant privileges on a specific database:
GRANT ALL PRIVILEGES ON my_app_db.* TO 'db_user'@'localhost';
FLUSH PRIVILEGES;Revoke ALL PRIVILEGES with granular permission levels if you prefer. To revoke privileges, use:
REVOKE ALL PRIVILEGES ON my_app_db.* FROM 'db_user'@'localhost';
FLUSH PRIVILEGES; 
