DEV Community: Tianzhou

🚀Top mysql Commands with Examples🦄

Tianzhou — Mon, 25 Sep 2023 06:26:12 +0000

MySQL is the world's most popular open-source relational database management system that uses SQL to manage data.
It's known for its scalability, reliability, and ease of use.

mysql is the official CLI client to command the MySQL database. It supports interactive and noninteractive use.

Below we list the 10 most commonly used mysql commands with examples.

1. Connect to a database - mysql -u xxx -p -h xxx -P xxx db

Connects to the local MySQL server via socket /tmp/mysql.sock as the specified user and prompts for a password.

mysql -u username -p

Connects to the MySQL server on the specified host at port 3306 and prompts for a password.

mysql -u username -p -h hostname

Connects to the MySQL server on the specified host and port and prompts for a password.

mysql -u username -p -h hostname -P portnumber

Connects to the specified database on the specified host and port as the specified user and prompts for a password.

mysql -u username -p -h hostname -P portnumber -D databasename
# can also omit -D
mysql -u username -p -h hostname -P portnumber databasename

2. Run a single command - mysql -e

Use -e to execute a single statement.

mysql -u username -p -h hostname -P portnumber databasename -e "SELECT 1"

Alternatively, you can pipe the statements from a file.

mysql -u username -p -h hostname -P portnumber databasename < filename.sql

3. List all databases - SHOW DATABASES

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# Support LIKE
mysql> SHOW DATABASES LIKE '%schema';
+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+

4. Switch to another database - USE xxx

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

5. List all tables under a database - SHOW TABLES

# Support LIKE
mysql> SHOW TABLES LIKE 'time%';
+---------------------------+
| Tables_in_mysql (time%)   |
+---------------------------+
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+

6. Describe table schema - DESCRIBE xxx

mysql> DESCRIBE time_zone;
+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| Time_zone_id     | int unsigned  | NO   | PRI | NULL    | auto_increment |
| Use_leap_seconds | enum('Y','N') | NO   |     | N       |                |
+------------------+---------------+------+-----+---------+----------------+

# DESC also works
mysql> DESC time_zone;
+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| Time_zone_id     | int unsigned  | NO   | PRI | NULL    | auto_increment |
| Use_leap_seconds | enum('Y','N') | NO   |     | N       |                |
+------------------+---------------+------+-----+---------+----------------+

7. List user and grants - SHOW GRANTS;

SHOW GRANTS [FOR user_or_role [USING role [, role] ...]]

# Show grants for the current user
mysql> SHOW GRANTS;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                 |

# Show grants for a particular user
mysql> SHOW GRANTS FOR root@localhost;

# List all users and grants
mysql> SELECT User, Host, Grant_priv, Super_priv FROM mysql.user;
+------------------+-----------+------------+------------+
| User             | Host      | Grant_priv | Super_priv |
+------------------+-----------+------------+------------+
| root             | %         | Y          | Y          |
| healthchecker    | localhost | N          | N          |
| mysql.infoschema | localhost | N          | N          |
| mysql.session    | localhost | N          | Y          |
| mysql.sys        | localhost | N          | N          |
| root             | localhost | Y          | Y          |
+------------------+-----------+------------+------------+

8. Show connections - SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST

Without the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field.

mysql> SHOW PROCESSLIST;
+-------+-----------------+------------------+------+---------+--------+------------------------+-------------------+
| Id    | User            | Host             | db   | Command | Time   | State                  | Info              |
+-------+-----------------+------------------+------+---------+--------+------------------------+-------------------+
|     5 | event_scheduler | localhost        | NULL | Daemon  | 696323 | Waiting on empty queue | NULL              |
| 21238 | root            | 172.17.0.1:41440 | NULL | Query   |      5 | User sleep             | SELECT SLEEP(100) |
| 21239 | root            | 172.17.0.1:41448 | NULL | Query   |      0 | init                   | SHOW PROCESSLIST  |
+-------+-----------------+------------------+------+---------+--------+------------------------+-------------------+

If you want to apply filtering, then query the underlying INFORMATION_SCHEMA.PROCESSLIST table.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root';
+-------+------+------------------+------+---------+------+------------+------------------------------------------------------------------+
| ID    | USER | HOST             | DB   | COMMAND | TIME | STATE      | INFO                                                             |
+-------+------+------------------+------+---------+------+------------+------------------------------------------------------------------+
| 21238 | root | 172.17.0.1:41440 | NULL | Query   |    2 | User sleep | SELECT SLEEP(100)                                                |
| 21239 | root | 172.17.0.1:41448 | NULL | Query   |    0 | executing  | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' |
+-------+------+------------------+------+---------+------+------------+------------------------------------------------------------------+

9. Kill connection - KILL

KILL [CONNECTION | QUERY] processlist_id

KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given processlist_id, after terminating any statement the connection is executing. This can be useful if you want to terminate a long-running or problematic connection that is causing issues for other users or processes.

mysql> KILL 123;

KILL QUERY terminates the statement the connection is currently executing, but leaves the connection itself intact. This can be useful if you have a specific query that is causing issues or is taking too long to execute, and you want to terminate only that query without affecting other queries or processes running on the same connection.

mysql> KILL QUERY 123;

10. Quit - \q, quit, exit, Ctrl+D/Ctrl+Z

mysql> \q
Bye

mysql> exit
Bye

mysql> quit
Bye

Alternatively, you can use the shortcut key Ctrl+D (or Ctrl+Z on Windows).

mysql> ^DBye

Other Tips

Displaying query results vertically - \G

Some query results are much more readable when displayed vertically using \G.

mysql> SHOW GRANTS\G;
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@%: GRANT PROXY ON ``@`` TO `root`@`%` WITH GRANT OPTION

How to install MySQL Client on Your Mac, Ubuntu, CentOS, and Windows

Tianzhou — Mon, 17 Jul 2023 07:10:24 +0000

Introduction

The official MySQL Client mysql, also known as MySQL CLI is a CLI to interact with your MySQL server. Below describe how to install it on Mac, Ubuntu, Debian and Windows respectively.

Before You Start

Before you start, you should confirm that you don't have MySQL client installed. If you have installed MySQL server before, likely you will have MySQL client installed as well. If you see something like below, then MySQL client is already installed and you can stop reading now.

$ mysql --version
mysql  Ver 8.0.31 for macos13.0 on arm64 (Homebrew)

Test Connection

After you install the MySQL client following the below instruction, you can test the connection to your MySQL server using

mysql -h hostname -u username -p

Install on Mac

Homebrew

To install MySQL Shell using Homebrew, you need to install Homebrew on your Mac first. If you aren’t sure if you have installed Homebrew already, open your terminal and run the following command to check.

brew -v
If not installed, run the following command to install Homebrew first:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Once Homebrew is installed, run the following command to update the list of available packages:

brew update

To install the MySQL client, run the following command:

brew install mysql-client

After the installation is complete, you can verify that the MySQL client is installed by running the following command:

mysql --version

Note: If you have previously installed the MySQL server using Homebrew, you may need to run the following command to link the MySQL client:

brew link --overwrite mysql-client

DMG Package

Go to the MySQL website and download the DMG package for the MySQL client. The current version can be found at https://dev.mysql.com/downloads/mysql/.
Double-click the downloaded DMG file to mount it. A new window will appear with the MySQL package.
Double-click the package to start the installation process, and follow the on-screen instructions to complete the installation.
Once the installation is complete, you can verify that the MySQL client is installed by opening a Terminal window and running the following command:

mysql --version

This should display the version number of the MySQL client that you just installed.

Note: If you have previously installed the MySQL server using a package manager like Homebrew, you may need to specify the path to the MySQL client binary when running commands. The default location for the MySQL client binary installed with the DMG package is /usr/local/mysql/bin/mysql.

Ubuntu (apt-get)

Open a terminal window on your Ubuntu machine. You can do this by pressing Ctrl+Alt+T on your keyboard.
Update the package list and upgrade any installed packages by running the following commands:

sudo apt-get update
sudo apt-get upgrade

Install the MySQL client by running the following command:

sudo apt-get install mysql-client

After the installation is complete, you can verify that the MySQL client is installed by running the following command:

mysql --version

CentOS (yum)

Open a terminal window on your CentOS machine. You can do this by pressing Ctrl+Alt+T on your keyboard.
Update the package list and upgrade any installed packages by running the following commands:

sudo yum update

Install the MySQL client by running the following command:

sudo yum install mysql

After the installation is complete, you can verify that the MySQL client is installed by running the following command:

mysql --version

Windows

Go to the MySQL website and download the MySQL Installer for Windows at https://dev.mysql.com/downloads/installer/.
Run the MySQL Installer and select the "Custom" installation type.
In the "Select Products and Features" screen, expand the "MySQL Servers" option and uncheck all the server components.
In the same screen, expand the "Applications" option and check the "MySQL Shell" and "MySQL Workbench" options. These applications include the MySQL client.
Complete the rest of the installation process.
After the installation is complete, you can verify that the MySQL client is installed by opening a Command Prompt or PowerShell window and running the following command:

mysql --version

More MySQL Tools

The classic MySQL client is still the most widely used MySQL client. Meanwhile you can also check out following tools

MySQL Shell mysqlsh introduced in MySQL 8.0 to provide more advanced features over mysql
Top MySQL GUI client
Top MySQL Schema Compare Tool to Diff and Sync Database
Top Free Open Source SQL Clients

What is a Database Schema?

Tianzhou — Mon, 28 Nov 2022 03:59:31 +0000

A database management system (DBMS) consists of three main components:

The database server
The database data
The database schema

The database server is a computation component and is stateless. The database data holds the state while the database schema holds the structure of the state (metadata). Database users often use SQL to instruct the database server to manipulate the underlying database data. SQL stands for Structured Query Language which is based on the structure that the database schema provides.

The Database Server

The database server is a process or a set of processes running inside a computation environment. The computation environment provides processing power. It could be a physical computer, a virtual machine or a lightweight container.

The Database Data

The database data records the business application information such as order information, customer profile, etc. The database data is stored on the physical disk. People always use a cylinder to represent a database because, in old days, database data is stored there. Oracle, the world’s largest database company, headquarters are a set of cylinder buildings.

The Database Schema

The database schema describes the logical structure of the database data. It often resides together with the database data. A more general term is metadata, but in the database domain, it’s always referred to as database schema or just schema. Other synonyms include data dictionary, data catalog, etc.

In the formal definition, a database schema is a set of formulas called integrity constraints. These constraints are defined as various database objects such as tables, fields, relationships, views, indexes, stored procedures, triggers, etc.

Above is a database schema for a database storing the employee data. The database schema is the most critical information because it’s the blueprint of how the database data is organized. A DBMS can have a database schema with empty data, but not vice versa. The database data is meaningless without the database schema.

Database schema evolves as the business evolves. In the next post, we will explain the evolution process, the database schema change.

DEV Community: Tianzhou

🚀Top mysql Commands with Examples🦄

1. Connect to a database - mysql -u xxx -p -h xxx -P xxx db

2. Run a single command - mysql -e

3. List all databases - SHOW DATABASES

4. Switch to another database - USE xxx

5. List all tables under a database - SHOW TABLES

6. Describe table schema - DESCRIBE xxx

7. List user and grants - SHOW GRANTS;

8. Show connections - SHOW PROCESSLIST

9. Kill connection - KILL

10. Quit - \q, quit, exit, Ctrl+D/Ctrl+Z

Other Tips

Displaying query results vertically - \G

Further Readings

How to install MySQL Client on Your Mac, Ubuntu, CentOS, and Windows

Introduction

Before You Start

Test Connection

Install on Mac

Homebrew

DMG Package

Ubuntu (apt-get)

CentOS (yum)

Windows

More MySQL Tools

What is a Database Schema?

The Database Server

The Database Data

The Database Schema