Administering MySQL from the Command Line

A database is a structured set of data, and the most common type you'll encounter in web hosting environments is a relational database. In this module, we’ll cover practical administration of MySQL/MariaDB using the command line on cPanel-powered servers—everything from structure to recovery and tuning.

---

🔄 Understanding Relational Databases

A relational database organizes data into tables, where relationships are established via fields. Think of a blog platform:

[users] ←── [posts] ←── [comments]
   ↑
   └────── [sessions]

Each record in posts and sessions maps back to a single user. This normalized structure makes data storage efficient and powerful.

📌 Tip: MySQL 8 and MariaDB 10.3+ offer native support for JSON columns, blurring lines between SQL and NoSQL.

---

⚙️ MySQL Storage Engines

MySQL/MariaDB supports multiple storage engines, but on cPanel systems, you’ll mostly work with:

1. MyISAM – Fast reads, no transactions, table-level locking

2. InnoDB – ACID compliant, row-level locking, transactions supported (recommended default)

---

🧰 cPanel-Integrated MySQL Utilities

---

🧪 Real-Time Monitoring with mytop

Run mytop to view live query activity and performance, just like top for Linux processes.

mytop -u root -p yourpassword

---

⚠️ Dealing With Common Errors

🔒 ACCESS DENIED

Check /etc/my.cnf or your user’s ~/.my.cnf for authentication/permission issues.

❌ mysql: unknown variable '...'

This means you've specified a variable not recognized by your current MySQL/MariaDB version. Clean up deprecated or unsupported entries in my.cnf.

---

🧱 Backup and Restore Techniques

🔄 Full Server Backup (Compressed)

mysqldump --opt -AER | gzip > /root/alldatabases.sql.gz

• -A: All databases

• -E: Include events

• -R: Include routines

• gzip: Compress to save space

🔁 Directory-Level Restore

1. Stop MySQL/MariaDB and monitoring

2. Rename existing data dir:

mv /var/lib/mysql /var/lib/mysql.bak

3. Restore backup:

cp /backup/date/system/dirs/_var_lib_mysql_.tar.gz /
cd /
tar -xzf _var_lib_mysql_.tar.gz

---

🐌 MySQL Slow Query Log – Performance Goldmine

The slow query log identifies underperforming queries.

Enable it in /etc/my.cnf:

slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 2

Why It Matters

• Find unoptimized queries

• Improve indexing

• Reduce load time

---

🧠 Pro Insights

• Monitor $(hostname).err files for startup issues:

    less /var/lib/mysql/$(hostname).err
  

• Use mysqladmin and mysqlshow for quick server checks

• Always validate restored directories with mysql_upgrade

---

✅ Key Takeaways

• Use InnoDB for most modern applications

• Monitor your MySQL instance using tools like mytop and error logs

• Optimize using the slow query log

• Automate backups, but always verify your restore strategy

  ---

  ✳️ If you found this helpful, follow me on LinkedIn for more technical insights and MySQL deep dives.