Top Five MySQL Commands

The Essential 5 MySQL/MariaDB Commands Every Sys Admin Needs to Know (Plus a Bonus!)

Hi there! This is Darren O’Neill from Darren’s Tech Tutorials.

If you’re a sys admin, you know that our job demands a broad, sometimes superficial, knowledge of an enormous variety of systems. We might spend one day deep in networking and the next troubleshooting a critical database connection. That’s why having a handful of rock-solid commands for key technologies is essential.

In this guide, we’re diving into the database world to arm you with the top five MySQL and MariaDB commands that will handle 90% of your day-to-day administrative tasks—from creating new databases to quickly checking table schemas. Plus, I’ve thrown in a crucial bonus command for setting up user permissions.

Ready to level up your database management skills? Let’s dive in!

Your Foundation: Getting Started

Whether you’re working with MySQL or its popular fork, MariaDB, the core syntax for basic management remains consistent. These commands will give you the essential ability to quickly inspect, create, and retrieve information when a user or application owner comes calling.

🛠️ Need a quick reference? Be sure to grab the complete, downloadable cheat sheet for these commands right here: https://darrenoneill.eu/?p=4363

Command 1: Creating a New Database

The most foundational task is setting up space for a new application. If you’ve been asked to provision a database for a new system, this is your starting point.

Purpose

To quickly and cleanly create a new, empty database instance on the server.

Syntax Example

Let’s imagine we need to set up a new instance for a WordPress site.

CREATE DATABASE wordpress;

Note: Always remember the semicolon (;) at the end of every command to execute it!

Command 2: Checking Your Inventory

Once you start creating databases, you need a quick way to list everything that resides on your server. This is vital for security audits, cleanup, and simply confirming that the database you just created exists.

Purpose

Lists all databases currently available on the server instance.

Syntax Example

SHOW DATABASES;

Running this command will return a list, likely including system databases like information_schema as well as any user-created databases (like wordpress and moodle).

Command 3: Selecting Your Focus

When you want to run administrative commands (like showing tables or selecting data), you must first tell MySQL which database you are working within. This command changes your session’s context.

Purpose

Selects a specific database for active use. All subsequent commands will be executed against this chosen database until you select a different one.

Syntax Example

If we wanted to investigate the existing moodle database, we would type:

USE moodle;

You will notice your command prompt change (or the system will respond with Database changed), confirming you are now operating inside the moodle database.

Command 4: Viewing Database Schema

Now that you are inside the database, how do you know what data structures exist? You use the SHOW TABLES command to see the available tables within your selected database.

Purpose

Lists all the tables within the database that is currently selected (using the USE command).

Syntax Example

SHOW TABLES;

This is incredibly helpful for quickly understanding the structure of a system—especially if you’re dealing with an application you haven’t seen before.

Command 5: Retrieving Data

The ultimate goal of any database is to store and retrieve data. The SELECT statement is the core of data retrieval. For basic administration and troubleshooting, you often just need to quickly verify that a table contains data and understand its structure.

Purpose

Retrieves specific columns or, in this example, all information (*) from a specified table.

Syntax Example

Let’s assume we are in the moodle database and want to see the contents of a table named mdl_course:

SELECT * FROM mdl_course;

The * symbol is database shorthand for “all columns.” While you’ll use more specific SELECT statements for complex reports, SELECT * is the fastest way to confirm basic data integrity and structure.

🎁 Bonus Command: Granting User Permissions

This command is absolutely critical when setting up applications like WordPress within a standard LAMP or WAMP stack. Applications rarely use the root user; they require a dedicated, least-privilege user to interact with their specific database.

Purpose

Creates a new user and assigns specific permissions to a database.

Syntax Breakdown

Syntax Example

GRANT ALL ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'wordpresspass';

When you later go through the graphical installation wizard for WordPress, you will input wordpressuser and wordpresspass into the configuration fields, allowing the application to function securely.

Summary and Next Steps

These six commands—the core five plus the essential bonus permission command—provide you with the basic confidence needed to administer MySQL and MariaDB databases in your sysadmin role. You now know how to provision databases, inspect the schema, and troubleshoot basic data issues.

The best way to master these is to try them out! Spin up a test environment or a virtual machine and practice running these commands until they become second nature.

Don’t forget to grab the official cheat sheet so you have these essential commands handy: Download Your Cheat Sheet Here!

If this tutorial was helpful, please hit that like button and subscribe to Darren’s Tech Tutorials for more clear, practical guides. Thanks for watching!