Introduction
MySQL is a widely-used open-source relational database management system. Often, interacting with a MySQL database begins through a command-line interface (CLI). This tutorial will guide you through the process of connecting to a MySQL server from your terminal, enabling you to execute SQL queries and manage your databases directly.
Prerequisites
Before you begin, ensure you have:
- MySQL Server Installed: You need a MySQL server running on your local machine or a remote server.
- MySQL Client: The MySQL client is typically installed alongside the server. If not, you may need to install it separately using your operating system’s package manager.
- MySQL User Credentials: You will need a valid MySQL username and password with the appropriate permissions to connect to the server and database.
Connecting to MySQL
The primary command for connecting to MySQL from the command line is mysql. Here’s the basic syntax:
mysql -u <username> -h <hostname> -p <database_name>
Let’s break down each part:
-u <username>: Specifies the MySQL username you want to connect with. Replace<username>with your actual username (e.g.,root,myuser).-h <hostname>: Specifies the hostname or IP address of the MySQL server. If the server is running on your local machine, you can uselocalhostor127.0.0.1. For remote servers, use the server’s IP address or domain name.-p: This option prompts you to enter the password. Important: Do not include a space between-pand the password. Entering the password directly on the command line is generally discouraged for security reasons, as it might be visible in your shell history.<database_name>: Specifies the database you want to connect to. If you omit this, you’ll connect to the MySQL server but won’t be using any specific database. You can then select a database using theUSE <database_name>;command within the MySQL shell.
Example:
To connect to a MySQL server running on your local machine as the user root, without specifying a database, use:
mysql -u root -h localhost -p
The system will then prompt you for the password.
To connect to a database named mydatabase as user myuser on a remote server with IP address 192.168.1.100, you’d use:
mysql -u myuser -h 192.168.1.100 -p mydatabase
Secure Password Handling
As mentioned previously, avoid including the password directly on the command line. A more secure approach is to use the -p option without the password. This prompts you for the password interactively, preventing it from being stored in your shell history or visible in process listings.
mysql -u <username> -h <hostname> -p
Connecting with Port Specification
If your MySQL server is listening on a non-default port (the default is 3306), you need to specify the port number using the -P (uppercase P) option:
mysql -u <username> -h <hostname> -P <port_number> -p
For example, if the server is listening on port 3307:
mysql -u root -h localhost -P 3307 -p
After Connecting
Once connected, you’ll be presented with the MySQL command-line client. You can now execute SQL queries, manage databases, and perform other database operations. To exit the MySQL client, type exit or quit and press Enter.
Summary
This tutorial covered the basics of connecting to a MySQL server from the command line. Remember to prioritize security by avoiding storing passwords in your shell history and to specify the correct hostname, port (if necessary), and database name. By mastering these fundamental concepts, you’ll be well-equipped to interact with your MySQL databases effectively from the terminal.