Connecting to MySQL from the Command Line: Example Codes

Install MySQL:

• If you haven't already, download and install the MySQL server and client software from the official MySQL website ([invalid URL removed]).

Open a Terminal or Command Prompt:

• On Windows, open the Command Prompt.
• On macOS or Linux, open a Terminal.

Navigate to the MySQL Installation Directory:

• Use the cd command to change to the directory where MySQL is installed. This directory typically contains the mysql executable.

Start the MySQL Server:

• If the MySQL server isn't already running, start it using the following command:

  sudo service mysql start
  

  Replace sudo with su if you're not using a sudo-enabled system.

Connect to the MySQL Server:

• Use the mysql command followed by the necessary options to connect to the server:

  mysql -u your_username -p
  

  • Replace your_username with your MySQL username.
  • When prompted for a password, enter your MySQL password.

Specify the Database:

• If you want to work with a specific database, use the USE command after connecting:

  USE your_database_name;
  

  Replace your_database_name with the name of the database you want to use.

Perform Operations:

• Once connected to the database, you can execute various MySQL commands to interact with your data. For example, you can:
  • Create, modify, and delete tables using CREATE TABLE, ALTER TABLE, and DROP TABLE.
  • Insert, update, and delete data using INSERT INTO, UPDATE, and DELETE.
  • Query data using SELECT.
  • And more.

Example:

mysql -u root -p
Enter password: your_password
Welcome to the MySQL monitor. Commands must be terminated with ;
Server version: 8.0.32 MySQL Community Server - GPL

MariaDB [test]> USE my_database;
Database changed

MariaDB [my_database]> SELECT * FROM my_table;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
+----+------+
2 rows in set (0.00 sec)

Authentication:

• The -u and -p options in the mysql command are used for authentication.
• You must provide your MySQL username and password to establish a connection.
• The password is typically not displayed for security reasons.

Connecting to MySQL from the Command Line: Example Codes

Basic Connection:

mysql -u your_username -p

• mysql: The MySQL command-line client.
• -u your_username: Specifies the username to connect with.
• -p: Prompts for the password.

Connecting to a Specific Database:

mysql -u your_username -p your_database_name

• your_database_name: The name of the database you want to connect to.

Connecting to a Remote Host:

mysql -h your_hostname -u your_username -p your_database_name

• -h your_hostname: Specifies the hostname or IP address of the MySQL server.

Example with Password:

mysql -u your_username -p your_password your_database_name

• -p your_password: Directly provides the password.

Example with a Socket:

mysql -S /path/to/mysql.sock -u your_username -p

• -S /path/to/mysql.sock: Specifies the path to the MySQL socket file.

Example with SSL:

mysql --ssl --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u your_username -p

• --ssl: Enables SSL encryption.
• --ssl-ca: Specifies the path to the CA certificate.

Once connected, you can execute SQL queries:

SELECT * FROM your_table;

To exit the MySQL client:

\q

Alternative Methods for Connecting to MySQL from the Command Line

While the direct mysql command is a common approach, there are other methods that offer different functionalities or suit specific use cases:

MySQL Connector/J (Java)

• Usage: For Java applications.
• Benefits: Provides a robust and feature-rich API for interacting with MySQL databases from Java code.

import java.sql.*;

public class MySQLConnection {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection    connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/your_database",   
                    "your_username",
                    "your_password"
            );

            // Use the connection to execute SQL queries
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table");
            // ... process the result    set

            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

• Benefits: Offers a native C++ interface for interacting with MySQL databases.

#include <mysql.h>

int main() {
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;

    conn = mysql_init(NULL);
    if (mysql_real_connect(conn, "localhost", "your_username", "your_password", "your_database", 0, NULL, 0) == NULL) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
    }

    mysql_query(conn, "SELECT * FROM your_table");
    result = mysql_store_result(conn);

    while ((row = mysql_fetch_row(result))) {
        // Process the row data
    }

    mysql_free_result(result);
    mysql_close(conn);

    return 0;
}

MySQL Connector/Python (Python)

• Benefits: Integrates seamlessly with Python's standard database API.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="your_username",
  passwd="your_password",
  database="your_database"
)

mycursor = mydb.cursor()   

mycursor.execute("SELECT * FROM your_table")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

MySQL Workbench

• Usage: A graphical user interface for managing MySQL databases.
• Benefits: Provides a visual way to create, edit, and execute queries without needing to use the command line directly.