How to Use the Command 'mysqlsh' (with Examples)

The mysqlsh command stands for MySQL Shell, an advanced command-line client for MySQL databases. It offers a multi-functional interface that supports SQL, JavaScript, and Python modes. It provides features for managing MySQL InnoDB clusters, using the document store model, and executing complex SQL operations with enhanced functionality and flexibility. MySQL Shell is particularly useful for developers and database administrators who are looking for an efficient, flexible tool to interact with MySQL databases.

Use case 1: Start MySQL Shell in Interactive Mode

Code:

mysqlsh

Motivation:
Starting the MySQL Shell in interactive mode allows users to perform database management tasks in a more efficient and flexible environment compared to traditional SQL clients. This is beneficial for users who need to frequently change between different database environments or who want to take full advantage of the extended capabilities that MySQL Shell provides.

Explanation:
This command initiates the MySQL Shell without any connections or commands. It opens an interactive shell session where commands can be entered directly. MySQL Shell supports queries in SQL, JavaScript, and Python modes, thus providing a versatile platform for database management tasks.

Example Output:

MySQL Shell 8.0.25

Copyright (c) 2016, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Use case 2: Connect to a MySQL Server

Code:

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

Motivation:
Connecting to a MySQL server is a typical first step in managing a database or running queries against it. This command provides a secure and straightforward way to establish a connection, enabling users to then perform necessary operations on the database.

Explanation:

• --user username: Specifies the username to log into the MySQL server. This is a required credential to authenticate the user.
• --host hostname: Indicates the hostname or IP address of the server hosting the MySQL database. This is necessary to direct the MySQL Shell to the correct location.
• --port port: Specifies the port number through which the connection to the server should be established. By default, MySQL uses port 3306, but this parameter provides flexibility in case a different port is used.

Example Output:

MySQL Shell 8.0.25
Password:
MySQL localhost:3306 ssl JS

Use case 3: Execute an SQL Statement on the Server and Exit

Code:

mysqlsh --user username --execute 'sql_statement'

Motivation:
Executing a specific SQL statement directly from the command line without entering the interactive shell mode can save time, especially for quick updates or retrievals. This feature is valuable for automated scripts where such operations are part of larger, automated workflows.

Explanation:

• --user username: Identifies the user account under which the SQL command will be executed.
• --execute 'sql_statement': Directs the MySQL Shell to execute a single SQL statement immediately upon establishing a connection. This avoids the need to manually enter the command after starting an interactive session.

Example Output:

1 row affected

Use case 4: Start MySQL Shell in JavaScript Mode

Code:

mysqlsh --js

Motivation:
For developers who prefer JavaScript or who are working with applications utilizing MySQL’s document store features, starting MySQL Shell in JavaScript mode provides access to MySQL’s enhanced collection functionalities alongside traditional database operations.

Explanation:

• --js: Switches the shell’s default execution mode to JavaScript. This mode allows executing JavaScript code within MySQL Shell, leveraging the benefits of a scripting language within the context of database administration.

Example Output:

MySQL Shell 8.0.25

MySQL localhost: JS >

Use case 5: Start MySQL Shell in Python Mode

Code:

mysqlsh --py

Motivation:
Similarly to JavaScript mode, MySQL Shell’s Python mode is especially beneficial for Python developers and data analysts. Python’s advanced data processing features integrate with MySQL Shell to provide a powerful and seamless workflow.

Explanation:

• --py: Changes the default shell environment to Python, enabling users to script and execute Python code directly within MySQL Shell. This mode is particularly advantageous for those accustomed to leveraging Python’s extensive libraries for data manipulation and analysis.

Example Output:

MySQL Shell 8.0.25

MySQL localhost: PY >

Use case 6: Import JSON Documents Into a MySQL Collection

Code:

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

Motivation:
With the increasing adoption of NoSQL databases storing JSON documents, having the ability to import JSON documents directly into MySQL is extremely advantageous. This allows seamless integration with existing applications while leveraging the robustness of relational MySQL databases.

Explanation:

• --import path/to/file.json: Specifies the file path of the JSON document(s) to be imported. This argument is crucial for locating and importing the correct dataset into the database.
• --schema schema_name: Signifies the database schema where the collection resides. The schema acts as a container within the database to organize the imported collections.
• --collection collection_name: Defines the collection (a synonym for table in MySQL document store context) into which the JSON documents will be imported. Collections allow for the flexible storage of varied data structures.

Example Output:

Number of JSON documents read: 1000
Number of JSON documents imported: 1000

Use case 7: Enable Verbose Output

Code:

mysqlsh --verbose

Motivation:
Verbose output is instrumental when debugging or monitoring processes as it provides detailed information about the operations being performed. This additional feedback can be crucial for understanding a system’s behavior and ensuring the command executes as expected.

Explanation:

• --verbose: This flag increases the quantity of output logged to the console during execution, providing comprehensive details about what the command is doing, step-by-step. The verbose mode allows users to better trace operations and troubleshoot if necessary.

Example Output:

Verbose logging enabled
Connecting to server on mysqlserver:3306
Using SSL for connection to mysqlserver:3306
Connection established successfully

Conclusion:

The mysqlsh command is an essential tool for those who work with MySQL databases, offering a range of functionalities to cater to users with varying requirements, from database management and scripting to data integration. Its versatility and support for different modes make it an advantageous choice for database professionals and developers. Whether executing SQL commands, managing connections, or working within JavaScript and Python environments, mysqlsh offers a comprehensive solution to effectively manage and interact with MySQL databases.