MYSQL COMMANDS FROM THE LINUX TERMINAL

MySQL is one of the most popular relational databases utilized in  applications that need to store data.  As a system administrator or a dev ops engineer, it would be very productive to access our MySQL databases from the terminal as it makes tasks easier.  Accessing it from the terminal doesn‘t mean any less privileges than using it in the code or from the admin interface.

We can also perform tasks such as setting the root password, monitoring MySQL processes, checking server status and so on and so forth.  However to be able to proceed with the commands in this tutorial, you need to have MySQL installed on your system. That is beyond the scope of this article, a tutorial which we have covered before.

Let‘s get into the tutorial.

To connect to the database:

Where root is your MySQL username, however it could be possible to have a different username in place of root. After typing the command in, you would be prompted to type in a password.

You should get an output similar to:

  Welcome to the MySQL monitor.  Commands end with ; or g.  Your MySQL connection id is 13  Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu)    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.    Oracle is a registered trademark of Oracle Corporation and/or its  affiliates. Other names may be trademarks of their respective  owners.    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.    mysql>   

If your MySQL is a new install, there are chances that you would not have a password.

To set a new password:

If your MySQL is fresh install, you would need to set a password which can be done with the command below:

Now you can log into your MySQL database.

To change an existing password:

If you already have an existing password and you want to change or update it, you can do so with the following command:

Here, the oldie should be replaced with your old password and the newie should be replaced with your new password.

It should be known that the -p that comes before oldie is just an argument and is not a part of the old password.

To check if MySQL is running:

Before we can do anything with the MySQL server, we have to check to see if the server is live.

To do this we use the command:

It comes up with a password prompt, and if it is live you would see a message saying:

Now, I believe we should have everything up and running and we should be ready to run our MySQL commands from the command line.

To view databases:

Remember that we are logged into MySQL and we should be having the interactive shell up.

To view databases, use the command below(leaving out the mysql> because we are in an interactive shell).

  mysql> show databases;  

We would get the output below:

To create a database:

Creating a new database for use, requires the interactive shell to be active. Then we use the comand below:

  mysql> create database newdata;  

Here newdata is the name of our new database, and we would get the output below:

This shows the command was successful, and we would have a different output when we run the show databases; command.

To work with a database:

We would have to work on the data in our database at some point, so to select a database we use the command below:

  mysql> use newdata;  

newdata  in this situation is a database that already exists in our server, and the name can be changed for any other database present on the server.

If the command was successful, we would get the following output:

To view database in use:

Sometimes we could get lost while making use of the server, meaning we may not have an idea of what database we are making use of.

To find our way, we can simply use the command below:

mysql> select database();

The database in use would be returned to the screen. Something in the form of:

Here, newdata is the name of the database in use.

To view tables:

After selecting a database to work on, we need to see the tables present in a database.

This can be done using the command below:

  mysql> show tables;  

We have this type of output below, because it is a new database and no tables have been created in this database.

To create a table in a database:

Now let‘s create a table in the selected database, then we would check to see if there is a change in the results.

  mysql> CREATE TABLE newtable (newcolumn1 VARCHAR(120), newcolumn2 DATETIME);  

Here newtable is the name of our new table, newcolumn1 is the name of our first column here of the type VARCHAR meaning characters with a maximum length of 120. newcolumn2 is the name of the second column of the type DATETIME which is used specifically for dates and times.  You can create as many columns as you want.

If it was successful, we get the output below:

Now, checking the tables in this database with the command mysql> show tables; we get the output:

To check the table structure:

To view the structure of our table, we can do that using the command below:

  mysql> describe newtable;  

This returns an output describing the table as shown below:

To add a column to an existing table:

More often than not, we would need to add new columns to tables that we have already created.  Nothing to be worried about, we can easily add a new column by using the command below:

  mysql> ALTER TABLE newtable ADD COLUMN newcolumn VARCHAR(120);  

Here newtable is the name of the existing table and newcolumn is the name of the column to be added.
If successful, you would get the output below:

These are just some basic MySQL commands that can be run from the command line. It doesn‘t end here, as there are other commands that can be used while using MySQL from the command line.

To round up, let‘s look at how our MySQL server can be shutdown safely from the command line so as to prevent any unpleasant occurrence in future.

This would come up with a password prompt and then would shutdown the MySQL server.

With a few of these basic commands you should be productive with MySQL in no time.