Learn MySQL - Top and Common Commands to Get Started

Essential MySQL Commands for Beginners

Contents

1. About MySQL

2. Set Up

3. Application

4. Access

5. User Management

6. Basic Operations

7. Data Operations

8. Maintenance

---

About MySQL

Overview 

• MySQL is an open-source relational database management system

• It is currently owned by Oracle

• It uses a structured query language (SQL) syntax for database management (retrieval and storage)

• Supports various storage engines

• Features ACID-compliance for transaction management

Key Features

• High Performance

  • Optimized for speed and efficiency 

  • Support for indexing, caching, and partitioning

• Scalability

  • Suitable for small to large-scale applications

  • Supports horizontal and vertical scaling

• Security

  • Offers robust security features like authentication, SSL support, and granular access controls

• Replication & Clustering

  • Provides master-slave replication and clustering for high availability and load balancing

• Cross-Platform Support

  • Compatible with various operating systems including Windows, Linux, and macOS

• Extensive Community Support

  • Large user base and active community 

  • Large ecosystem of documentation and community support 

Comparison to Alternatives

• PostgreSQL

  • More feature-rich with advanced data types

  • Better compliance with SQL standards

  • Superior concurrency control

  • MySQL is generally faster for read-heavy operations

• SQLite

  • Lightweight and serverless

  • Ideal for embedded applications

  • MySQL offers more features and better performance for larger databases

• MariaDB

  • A fork of MySQL with additional features and optimizations

  • MySQL has broader enterprise support and official Oracle backing

• MongoDB

  • A NoSQL database 

  • Designed for high flexibility and scalability with unstructured data

  • MySQL is better suited for structured data and complex queries

---

Set-Up

Check Installation and Version

$ mysql --version

Installation 

Install MySQL:

https://packages.ubuntu.com/noble/mysql-server

$ sudo apt update
$ sudo apt install mysql-server

Uninstallation 

Remove MySQL packages:

• This removes MySQL server, client and related packages

• This also removes configuration files

$ apt purge package-name removes a package alongside with its configuration files on your system

• It performs a complete uninstallation, including the removal of files on your system

• When an apt remove is performed, configuration files remain on your system

$ sudo apt purge \
  mysql-server \
  mysql-client \
  mysql-common \
  mysql-server-core-* \
  mysql-client-core-*

Remove data directories:

• Removes MySQL data directories (including databases)

• You can delete these manually as well

• Note - Be cautious when executing this command, since it will delete all MySQL data, including database contents

$ sudo rm -rf /etc/mysql /var/lib/mysql

---

Application 

Start-Up

Start the MySQL service:

$ sudo service mysql start

Check Status

Check the status of the MySQL service:

$ sudo service mysql status

Command output:

● mysql.service - MySQL Community Server

     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

     Active: active (running) since Sat 2024-06-29 10:01:15 PDT; 1 day 14h ago

   Main PID: 1285 (mysqld)

     Status: "Server is operational"

      Tasks: 39 (limit: 38243)

     Memory: 431.8M

        CPU: 5min 48.282s

     CGroup: /system.slice/mysql.service

             └─1285 /usr/sbin/mysqld

Jun 29 10:01:15 eduardo-ubuntu systemd[1]: Starting MySQL Community Server...

Jun 29 10:01:15 eduardo-ubuntu systemd[1]: Started MySQL Community Server.

Check Port

Check which port MySQL is listening on:

• Note - MySQL listens, by default, on port 3306

$ sudo netstat -tuln | grep 3306

Power-Off

$ sudo service mysql stop 

---

Access

Single Login into Database

Log in to MySQL as root:

$ mysql -u root -p

Log In - Forgot Root Password

If you forgot the MySQL root password, you can reset it in the following steps:

1. Stop the MySQL service

2. Start MySQL is Safe Mode

3. Connect to MySQL as Root

4. Reset the Root Password

5. Exit MySQL and Restart

6. Start MySQL Service

Stop the MySQL service

$ sudo service mysql stop

Start MySQL is Safe Mode

$ sudo mysqld_safe --skip-grant-tables &

Connect to MySQL as Root

$ mysql -u root

Reset the Root Password

-- Inside MySQL
$ USE mysql;

UPDATE user 
SET authentication_string=PASSWORD('new_password') 
WHERE User='root';

FLUSH PRIVILEGES;

Exit MySQL and Restart

-- Inside MySQL
EXIT;

Start MySQL Service

$ sudo service mysql start

Persist Login into Database

Save MySQL credentials for quick login:

• This method is not recommended for security reasons

• Replace root with the username 

• Replace the password for your actual MySQL root user password

$ echo "[client]

user=root
password=your_mysql_root_password" > ~/.my.cnf

$ chmod 600 ~/.my.cnf

---

User Management

Create User

CREATE USER 'username'@'localhost' 
IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES 
ON database_name.* 
TO 'username'@'localhost';

Revoke Privileges

REVOKE ALL PRIVILEGES 
ON database_name.* 
FROM 'username'@'localhost';

Modify User

ALTER USER 'username'@'localhost' 
IDENTIFIED BY 'new_password';

Delete User

DROP USER 'username'@'localhost';

---

Basic Operations

List Databases 

SHOW DATABASES;

Create Database

CREATE DATABASE company;
USE company;

Delete Database

DROP DATABASE database_name;

---

Data Operations

Create Table

CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);

Example:

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_email VARCHAR(100) UNIQUE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Insert Data

INSERT INTO table_name (column1, …) VALUES (value1, …);

Example:

INSERT INTO departments (dept_name) VALUES
    ('Finance'),
    ('Human Resources'),
    ('Marketing');
INSERT INTO employees (emp_name, emp_email, dept_id) VALUES
    ('John Doe', 'john.doe@example.com', 1),
    ('Jane Smith', 'jane.smith@example.com', 2),
    ('Michael Johnson', 'michael.johnson@example.com', 3);

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET emp_email = 'john.doe.new@example.com'
WHERE emp_name = 'John Doe';

Delete Data

DELETE 
FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE emp_name = 'Michael Johnson';

Clear Database Data

DELETE 
FROM table_name;

Example:

DELETE FROM employees;

Query Data

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT emp_name, emp_email, dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

---

Maintenance

Backup Database

Backup database:

• Replace username with the username

• Replace database_name with the database name

• Replace the backup.sql with the path location and filename

$ mysqldump -u username -p database_name > backup.sql

Restore Database

$ mysql -u username -p database_name < backup.sql

Check Logs

MySQL logs are stored in /var/log/mysql/

$ tail -f /var/log/mysql/error.log

---