This post may contain affiliate links, please read our affiliate disclosure to learn more.
MySQL Essentials: A Guide to Key Commands

MySQL Essentials: A Guide to Key Commands

Author
 By Charles Joseph | Cybersecurity Researcher
Clock
 Published on December 26th, 2023
This post was updated on December 28th, 2023

MySQL is the world’s most widely used relational database management system (RDBMS), boasting over 40% market penetration. It’s the backbone of many cloud-based applications we interact with daily.

Released as open-source software in 1996, MySQL has maintained its open-source status even after being acquired by Oracle.

NordVPN for bypassing geo-restrictions, a no logs policy, streaming, and anonymous payment

Stay One Step Ahead of Cyber Threats

Want to Be the Smartest Guy in the Room? Get the Latest Cybersecurity News and Insights.
We respect your privacy and you can unsubscribe anytime.

As an enthusiastic supporter and frequent user of MySQL, I’m excited to share some essential commands that are invaluable in day-to-day operations.

This article aims to provide a quick overview of frequently used and important MySQL commands. For those seeking more detailed information, numerous resources are available online. Often, we just need a quick refresher on how a command is used, and this guide serves that purpose.

Is MySQL Installed?

Before diving into commands, ensure MySQL is installed on your system. For Ubuntu users, the installation is straightforward:

$ sudo apt update 
$ sudo apt install mysql-server 
$ sudo mysql_secure_installation

Securing your MySQL installation is paramount. The mysql_secure_installation script helps you safeguard your installation by restricting anonymous access, removing the test database, and strengthening your MySQL account passwords.

MariaDB note: The above commands install MySQL, however, a popular alternative is MariaDB, developed by the original MySQL team. The commands in this guide apply to both MySQL and MariaDB.

Important MySQL Commands

1. Logging In (Without Specifying Your Password on the Command Line)

To access the MySQL command-line client with administrative privileges, use:

Here, -u specifies the user, and -p prompts for a password.

2. Viewing Databases

3. Creating a New Database

4. Selecting a Database:

5. Creating a New Table:

6. Creating a New User:

7. Granting Permissions:

MySQL privileges -- select, insert, update, delete, index, create, alter, drop, grant option, all
MySQL privileges from which to choose

A best practice is only to provide the most restrictive permissions necessary; for our demo, let’s look at two options: one where we specify limited permissions and one where we give a user all of them.

Limited permissions on the recipes database:

All permissions on the recipes database:

8. Revoking Permissions:

9. Viewing User Permissions:

10. Removing a User

11. See Your MySQL Users

To view all users in your MySQL installation:

MySQL, by default, reserves 4 user accounts – mysql.session, mysql.sys, debian-sys-maint, and root. The others are the ones you (or another admin) created.

According to the MySQL documentation, the users — mysql.session and mysql.sys, are locked and can’t be used for client connections. This is why you see the value “*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE” under the column labeled “authentication_string.”

The debian-sys-maint account is used by MySQL to control the service.

And lastly, your “root” user is your system-wide administrative account. By default, it’s set with a login method of “auth_socket,” which effectively means you have to use “sudo” to log in. This is more secure and a best practice.

You could issue the following command to change this, although I’d personally recommend against it.

12. Changing Root Login Method

While not recommended, you can change the root account login method:

If you do this, run sudo mysql_secure_installation again to ensure your installation remains secure. You can now log in with the root user without using sudo.

I’d like to caution you again; please have a good reason for changing your root account login method, as it’s not a best practice.

13. Password Policy in MySQL

To view MySQL’s password policy:

QUOTE:
"Amateurs hack systems, professionals hack people."
-- Bruce Schneier, a renown computer security professional
Scroll to Top