Mastering MySQL: Basics, Advanced Commands, and Best Practices

Introduction

MySQL is one of the most widely used relational database management systems (RDBMS) in the world. Whether you’re a beginner or an experienced database administrator, mastering MySQL can help optimize performance, ensure security, and manage large datasets efficiently.

In this guide, we’ll cover: ✅ Basic and advanced MySQL commands with examples ✅ Connecting to MySQL from your laptop when running on an AWS EC2 instance ✅ Best practices for database management ✅ Common issues and troubleshooting ✅ FAQs

🔹 MySQL Basics

1️⃣ Install MySQL on Linux (Ubuntu/Debian)

sudo apt update
sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql

2️⃣ Secure MySQL Installation

sudo mysql_secure_installation

This will prompt you to set a root password and remove unnecessary settings for security.

3️⃣ Log into MySQL

mysql -u root -p

4️⃣ Create a Database

CREATE DATABASE company_db;
SHOW DATABASES;

5️⃣ Create a User and Grant Permissions

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON company_db.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

🔹 Advanced MySQL Commands

1️⃣ Optimize Table Performance

OPTIMIZE TABLE employees;

2️⃣ Backup a MySQL Database

mysqldump -u root -p company_db > backup.sql

3️⃣ Restore from a Backup

mysql -u root -p company_db < backup.sql

4️⃣ Create an Index for Faster Queries

CREATE INDEX idx_lastname ON employees (last_name);

5️⃣ View Running Queries (Identify Slow Queries)

SHOW PROCESSLIST;

6️⃣ Kill a Running Query

KILL <process_id>;

🔹 Connecting to MySQL Running on an AWS EC2 Instance

1️⃣ Allow Remote Access

Modify MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change bind-address from 127.0.0.1 to 0.0.0.0:

bind-address = 0.0.0.0

Restart MySQL:

sudo systemctl restart mysql

2️⃣ Open MySQL Port in AWS Security Group

Navigate to EC2 > Security Groups
Edit Inbound Rules and allow port 3306 from your IP (your-laptop-ip/32)

3️⃣ Connect from Your Laptop

mysql -h <EC2-PUBLIC-IP> -u admin -p

🔹 Best Practices for MySQL

✅ Use Strong Passwords — Ensure all MySQL users have secure passwords.

✅ Enable Backups — Use mysqldump or automated backup solutions.

✅ Use Indexing – Improve performance by indexing frequently queried columns.

✅ Limit User Privileges – Grant only necessary permissions.

✅ Monitor Slow Queries – Enable slow query logging to optimize queries.

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;

✅ Regularly Update MySQL — Keep your installation up to date.

❓ FAQs

1️⃣ How do I reset the MySQL root password?

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

2️⃣ How do I check the size of my database?

SELECT table_schema AS 'Database',
SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

3️⃣ How do I grant remote access to a MySQL user?

CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON company_db.* TO 'admin'@'%';
FLUSH PRIVILEGES;

4️⃣ My MySQL server is running, but I can’t connect remotely. What should I check?

Ensure MySQL is listening on 0.0.0.0
Verify port 3306 is open in the security group
Check firewall settings: sudo ufw allow 3306/tcp

5️⃣ How do I automatically restart MySQL if it crashes?

sudo systemctl enable mysql

🚀 Conclusion

MySQL is an essential database for developers, DevOps engineers, and database administrators. Understanding basic and advanced commands, optimizing performance, and ensuring security are key to running a reliable database.

By following these best practices and troubleshooting techniques, you can efficiently manage MySQL databases, whether locally or on AWS EC2.

💡 Next Steps:

🔹 Implement automated backups

🔹 Optimize queries using EXPLAIN

🔹 Secure MySQL with IAM-based authentication (for AWS RDS)

Do you have more questions about MySQL? Drop them in the comments below! 🚀

📢 Have questions or feedback? Drop a comment below or connect with me on Twitter/X@spysood!

Originally published on Medium.