The Most Useful MySQL Commands You Need in Your Toolbox

Prerequisites

MySQL server installed and running on your machine
MySQL user account with the necessary permissions to access databases and execute commands
You can access MySQL from a terminal (Linux/Mac) or command prompt (Windows)
SQL Syntax Familiarity

Introduction

Whether you're a seasoned database administrator or just getting started with MySQL, knowing the right commands can drastically improve your efficiency when managing databases. MySQL is a powerful and widely-used relational database management system, but it can be overwhelming at times due to the sheer number of commands and operations available.

In this guide, we'll cover the most essential MySQL commands that will quickly become your go-to tools for common database tasks. From creating and managing databases to querying data, optimizing performance, and handling security, these commands are indispensable for anyone working with MySQL databases. By mastering them, you'll be able to navigate your database environment with confidence and precision.

Let's dive in and explore the key MySQL commands every developer and administrator should have in their toolbox.

What is mysql_secure_installation?

Invoke mysql_secure installation without arguments to improve mysql installation security. This utility enables you to improve the security of your MySQL installation in the following ways:

• Set a Strong Root Password
• Remove Anonymous Users
• Disable Root Login Remotely
• Remove Test Databases
• Reload Privilege Tables

How to Run mysql_secure_installation?

Here’s a step-by-step on how to invoke the script:

Open your terminal or command prompt and run the command:

sudo mysql_secure_installation

Follow the prompts:

• Enter a current root password
• Set a root password: choose a strong root password for your user.
• Remove anonymous users: It's recommended to answer "Yes" to remove anonymous accounts.
• Disallow root login remotely: It's recommended to answer "Yes" to prevent remote login as root.
• Remove test databases: Answer "Yes" to remove the test database, which isn't needed in most environments.
• Reload privilege tables: Confirm that the changes should be applied immediately.

When Should You Run mysql_secure_installation?

• After installing MySQL: It’s crucial to run this script immediately after installing MySQL on a new server or fresh installation.
• Before Putting MySQL in Production: Running this script ensures your MySQL server is more secure and ready for production environments.

MySQL Command Line Interface (CLI)

Open a terminal or command prompt

Use the mysql command with the appropriate flags:

mysql -u username -p

• -u username: Replace username with your MySQL username
• -p: This will prompt you to enter the password for the user.

After entering the password, you should be connected to the MySQL server.

Database Management

To list all databases on a MySQL server

show databases;

Create a new database

create database database_name;

Switch to a specific database using the use statement

use database_name;

Delete an existing SQL database

drop database database_name;

Table Management

To list tables in the selected database

show tables;

To get information about a specified table

describe table_name;

The syntax for creating a table in SQL is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

The following SQL query creates a table called "City" with the specified columns and data types.

CREATE TABLE City (
    ID INT PRIMARY KEY,               -- Unique ID for each location (integer)
    Name VARCHAR(35),                 -- Name of the location (string up to 35 characters)
    CountryCode VARCHAR(3),           -- Country code (string with 3 characters)
    District VARCHAR(20),             -- Name of the district (string up to 20 characters)
    Population INT                    -- Population of the location (integer)
);

The syntax for altering a table to add a column in SQL is as follows:

ALTER TABLE table_name
ADD column_name column_data_type;

The following SQL query will add a new column named TimeZone to the City table. The TimeZone column will hold text values (strings) that can be up to 50 characters long, such as "GMT+1", "PST", or "UTC". This allows you to store the time zone for each location in the City table.

ALTER TABLE City
ADD TimeZone VARCHAR(50);  -- Time zone of the location

Delete a table

drop table table_name;

Rename a table

rename table old_table_name to new_table_name;

Data Management

Show all data in a table

select * from table_name;

The syntax for inserting data into a table in SQL is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The following SQL query is an insert statement that adds a new record (or row) into the City table in a database. Here's a breakdown of what each part of the query means:

• INSERT INTO City: This indicates that you are inserting data into the City table.
• (Name, CountryCode, District, Population): This specifies the columns in the City table where the values will be inserted.
• VALUES ('Nashville', 'USA', 'East Nashville', 30000): These are the actual values that will be inserted into the corresponding columns.

In SQL, text values must be enclosed in single quotes (' ') for proper syntax. While some database systems may also allow the use of double quotes (" "), it is generally considered best practice to use single quotes for text values. On the other hand, numeric values such as integers and decimals should not be enclosed in quotes.

INSERT INTO City (Name, CountryCode, District, Population)
VALUES ('Nashville', 'USA', 'East Nashville', 30000);

The syntax for updating data into a table in SQL is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

As part of maintaining accurate and up-to-date demographic information, it's essential to periodically update population figures in the database to reflect actual growth.

UPDATE City
SET Population = 40000
WHERE District = 'East Nashville';

The syntax for deleting data into a table in SQL is as follows:

DELETE FROM table_name
WHERE condition;

To delete the city Nashville from the City table, you would use the delete statement. Here's the SQL query:

DELETE FROM City
WHERE Name = 'Nashville' AND CountryCode = 'USA' AND District = 'East Nashville';

The where clause specifies the conditions for the rows that you want to delete. In this case, you're deleting the row where:

• The Name is 'Nashville',
• The CountryCode is 'USA',
• The District is 'East Nashville'.

Delete all data from a table (without removing the table)

truncate table table_name;

Data Querying and Filtering

The count(*) function represents the count of all rows present in the table

select count(*) from table_name;

Select with conditions

select * from table_name where condition;

Select specific columns

select column1, column2 from table_name;

Order results

select * from table_name order by column_name asc | desc;

Limit the number of results

select * from table_name limit 10;

Use and, or, for complex conditions

• select * from table_name where condition1 and condition2;
• select * from table_name where condition1 or condition2;

Index and Key Management

Create an index

create index index_name on table_name (column_name);

Drop an index

drop index index_name on table_name;

Show indexes on a table

show indexes from table_name;

Users and Privileges Management

Create a new user. Be sure to change user to your preferred username and password to a strong password of your choosing.

create user 'username'@'host' identified by 'password';

Grant privileges to a user

Grant your MySQL user all privileges. Anyone with access to this MySQL user will have complete control over every database on the server.

grant all privileges on *.* to 'username'@'host';

Revoke your MySQL user all privileges

revoke all privileges on *.* to 'username'@'host';

Grant privileges to a user

grant all privileges on database_name.* to 'username'@'host';

Revoke privileges from a user

revoke all privileges on database_name.* from 'username'@'host';

Show user privileges

show grants for 'username'@'host';

Reload the grant tables' privileges, ensuring that any changes made to user permissions are immediately applied

flush privileges;

Delete a user

drop user 'username'@'host';

Explanation:

• All privileges: Grants all available privileges to the user (e.g., select, insert, update, delete, etc.).
• *.*: Refers to all databases (*) and all tables (*) within those databases.
• 'username': Replace with the name of the MySQL user.
• 'host': Replace with the host where the user will connect from. Use % for any host (this means the user can connect from any IP address).
• 'password': Replace with the user's password (if you want to set it at the same time) .

Backup and Restore

Export a single MySQL database in the form of a .sql file. The -p option prompts for the password of the MySQL user.

mysqldump -u username -p database_name > backup.sql

Import an existing dump file into MySQL

mysql -u username -p database_name < backup.sql

Miscellaneous

Show all MySQL users:

select user from mysql.user;

Show MySQL users and hosts they are allowed to connect from:

select user, host from mysql.user;

Change user password

alter user 'username'@'hostname' identified by 'newpassword';

Show current connection info

show status;

Leave the MySQL prompt

exit

You'll be prompted for a password. If you get an Access denied error, your username or password are incorrect. Otherwise, all the databases will show that the specified user has access to.

mysqlshow -u database_username -p

Check the MySQL status on a CentOS 9 server

systemctl status mysqld

Restarting MySQL on a CentOS 9 server

systemctl restart mysqld

Check MySQL version

mysql -V

Clear the MySQL screen console window

system clear;

Test connection. If the connection is successful, you see the message: Connected to <host_IP>. If the connection fails, you see the message: Unable to connect to remote host. The default port of MySQL is 3306

telnet host_IP port_number

For a step-by-step breakdown of how to connect to your MySQL database in VS Code, head over to our comprehensive guide. It’ll help you get started right away!

Find the MySQL configuration file. The primary configuration file for the MySQL database server is called my.cnf

find '/' -name "my.cnf"

To learn MySQL more quickly and effectively, use the Sakila sample database available on mysql.com. The Sakila sample database is one of the most widely used sample databases. Other sample databases on mysql.com include the world sample database and employees sample database.

How to read .mysql_history containing \040

sed "s/\\040/ /g" < .mysql_history

Final Thoughts

In conclusion, we've covered the syntax of some of the most useful MySQL commands, providing you with a solid foundation for managing databases efficiently. From querying data to managing user privileges, these commands are essential tools for working with MySQL. By understanding and practicing these basics, you're now equipped to handle a wide range of tasks and can dive deeper into more advanced database operations as needed.