How to use the command `mysqlsh` (with examples)

MySQL Shell is an advanced command-line client for MySQL that supports SQL, JavaScript, and Python. It offers features for managing InnoDB clusters and document store collections. This article will provide examples of various use cases for the mysqlsh command.

Use case 1: Start MySQL Shell in interactive mode

Code:

mysqlsh

Motivation: Starting MySQL Shell in interactive mode allows users to execute SQL queries, JavaScript, or Python scripts directly and get instant results. It is useful for exploring and interacting with the database in real-time.

Example Output:

MySQL Shell 8.0.23

Type '\help' or '\?' for help; '\quit' to exit.

Use case 2: Connect to a MySQL server

Code:

mysqlsh --user username --host hostname --port port

Motivation: Connecting to a MySQL server enables users to access and manage databases remotely. By specifying the username, hostname, and port, users can establish a connection to the desired MySQL server.

Explanation:

• --user: Specifies the username to use for the connection.
• --host: Specifies the hostname or IP address of the MySQL server.
• --port: Specifies the port number on which the MySQL server is running.

Example Output:

Enter password:
Creating a session to 'username@hostname:port'
Connection successful

Use case 3: Execute a SQL statement on the server and exit

Code:

mysqlsh --user username --execute 'sql_statement'

Motivation: Sometimes users need to quickly execute a specific SQL statement without entering interactive mode. This use case allows users to execute a single SQL statement and exit immediately, making it useful for automation or scripting purposes.

Explanation:

• --user: Specifies the username to use for the connection.
• --execute: Executes the provided SQL statement.

Example Output:

Enter password:
Executing SQL statement: sql_statement

Use case 4: Start MySQL Shell in JavaScript mode

Code:

mysqlsh --js

Motivation: Starting MySQL Shell in JavaScript mode allows users to utilize JavaScript as the scripting language for interacting with the database. JavaScript offers familiar syntax and is widely known, making it suitable for developers who are more comfortable with JavaScript for database operations.

Example Output:

MySQL Shell 8.0.23

JavaScript mode enabled

Use case 5: Start MySQL Shell in Python mode

Code:

mysqlsh --py

Motivation: Starting MySQL Shell in Python mode allows users to utilize Python as the scripting language for interacting with the database. Python’s extensive libraries and wide community support make it a popular choice for data manipulation and analysis.

Example Output:

MySQL Shell 8.0.23

Python mode enabled

Use case 6: Import JSON documents into a MySQL collection

Code:

mysqlsh --import path/to/file.json --schema schema_name --collection collection_name

Motivation: Importing JSON documents into a MySQL collection allows users to quickly and easily transfer data from JSON files into a structured database format. This can be useful when migrating data or working with datasets in JSON format.

Explanation:

• --import: Specifies the path to the JSON file to import.
• --schema: Specifies the schema (database) name.
• --collection: Specifies the collection (table) name.

Example Output:

Importing JSON file 'path/to/file.json' into collection 'schema_name.collection_name'
Import completed successfully.

Use case 7: Enable verbose output

Code:

mysqlsh --verbose

Motivation: Enabling verbose output provides detailed information during the execution of MySQL Shell commands. It is useful for troubleshooting and understanding the internal processes of MySQL Shell.

Example Output:

Verbose output enabled.

Conclusion:

The mysqlsh command is a versatile tool that enables users to interact with MySQL servers using SQL, JavaScript, or Python. It offers various use cases, from starting the shell in different modes to importing data or executing SQL statements. By understanding these use cases, users can leverage MySQL Shell effectively to manage their databases.