MySQL is the world’s most widely used relational database management system (RDMS), maintaining greater than 40% market penetration and leveraged by a majority of the cloud-based applications we use every day.
It was released to the public in 1996 as open-source software, and despite being purchased by Oracle, it maintains an open-source status even now.
Needless to say, I’m a huge fan and an avid user of the technology.
Let’s go over some of the more important MySQL commands that you should probably know.
I’d like to add right at the outset, though, that the purpose of this article is to provide a quick overview of frequently used and important MySQL commands. There are numerous places online that can provide you with more detailed information.
More often than not we just want a quick example of how a command is used to refresh our memory.
Is MySQL Installed?
If you don’t already have MySQL installed, you’ll need to do this. Here’s the way you’d do it using Ubuntu.
$ sudo apt update
$ sudo apt install mysql-server
$ sudo mysql_secure_installation
It’s critically important that after you install MySQL, that you secure it. Running “mysql_secure_installation” in a Linux environment after installation will help you to do this.
Effectively you’ll be restricting access to MySQL from anonymous users, removing the “test” database for debugging, and strengthening your MySQL account-passwords.
NOTE: The above command installed MySQL. A drop-in replacement is MariaDB, which was developed by the original MySQL gang who split away. The commands in this document work for either platform, but most folks I’ve talked to prefer MariaDB, which they’ve called MySQL on steroids.
Important MySQL Commands
How to Log In to MySQL Without Specifying Your Password on the Command Line
Our first step will be to log in to the MySQL command-line client with an account that has administrative permissions. I’m going to use the “root” user (the default MySQL account) via the below command, but you’re free to use any account you like.
$ sudo mysql -u root -p
The “-u” flag specifies the user and the “-p” flag specifies that you’ll enter the appropriate password when prompted.
Once you’ve logged in, you’ll see the following prompt.
mysql>
From this prompt, you can input commands.
Perhaps you’d like to see all of your databases?
mysql> show databases;
Or perhaps you’d like to create a new database called “recipes?”
mysql> create database recipes;
If you’d like to make changes to this newly created database, you’ll need to tell MySQL that you want to “use” it first.
mysql> use recipes;
Creating a new table is easy. In this example, we’re creating a table named “italian.”
mysql> create table if not exists italian (
italian_id int auto_increment primary key,
title varchar(75) not null,
recipe varchar(255) not null,
date_recipe_discovered date,
record_created_at timestamp default current_timestamp
);
Creating a new user is also easy. Don’t be confused by the term “identified by.” This is just how we define a password on account creation.
mysql> create user [email protected] identified by ‘7c1fcec22adac4b2a8b157c21’;
After you’ve created the above user, you can then provide the user with permissions (see the table below for a complete list). Let’s do so using the “italian” table we created above.
A best practice is to only provide necessary permissions to your users; 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.
mysql> grant create, select, insert, update, delete on recipes.* to ‘luigi’@’localhost’;
mysql> grant all on recipes.* to ‘luigi’@’localhost’;
If you want to revoke (remove) permissions from a user you’d do it this way.
mysql> revoke all on recipes.* from ‘luigi’@’localhost’;
To see the permissions you’ve set for a user, you’d do this.
mysql> show grants for ‘luigi’@’localhost’;
If you have any problems or just want to start over, you can remove the user this way (and start over).
mysql> drop user ‘luigi’@’localhost’;
Default MySQL Users
If you’d like to see all of the users maintained by your MySQL installation, issue this command.
mysql> select user, authentication_string, plugin, host from mysql.user;
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, its set with a login method of “auth_socket,” which effectively means you have to use “sudo” to log in. This is good and more secure.
You could issue the following command to change this, although I’d personally recommend against it.
mysql> alter user ‘root’@’localhost’ identified with mysql_native_password by ‘your-password-here’;
mysql> flush privileges;
I’d then run the “sudo mysql_secure_installation” script (hopefully again) and you’d now be able to log in with the root user without using sudo.
I’d like to caution you to have a very good reason for changing your root account login method as it’s not a best practice to do so. But in the event you’re curious why you need to use sudo to get access to mysql using root, you now know.
Password Policy within MySQL
mysql> show variables like ‘validate_password%’;