MySQL - Connection

MySQL - Connecting to the Server

To interact with a MySQL database, a client application must establish a connection to the server. This process requires providing credentials and connection details. This tutorial covers how to connect to MySQL securely from both the command line and various programming languages.

Connection Parameters

Every MySQL connection requires the following core parameters:

• Host: The IP address or hostname where the MySQL server is running (e.g., localhost, 127.0.0.1, or a remote IP).
• User: The name of the database user you are connecting as (e.g., root, app_user).
• Password: The password for the specified user.
• Port: The TCP/IP port the server is listening on. The default is 3306.
• Database (Optional): The specific database you want to work with upon connecting.

Connecting with the mysql Command-Line Client

The mysql command-line tool is the most direct way to interact with your server. It’s essential for administration and debugging.

Syntax

mysql -h [hostname] -u [username] -P [port] -p [database_name]

The -p flag will securely prompt you for your password. Never type your password directly on the command line like -pYourPassword, as it will be visible in your command history.

Example

To connect to a local server as the root user:

# The command
mysql -u root -p

# The server will then prompt for your password
Enter password: ********

Upon successful connection, you will see the mysql> prompt.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 8.0.30

mysql>

To disconnect, type exit or quit.

mysql> exit
Bye

Modern Password Management

The root password is typically set during the MySQL installation process. If you need to change it or any other user’s password, use the ALTER USER statement.

-- Connect as a user with sufficient privileges (e.g., root)
-- Then execute this command to change the root user's password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'a-new-secure-password';

The SET PASSWORD command from older tutorials still works but ALTER USER is the recommended, modern syntax.

Connecting from Application Code

Below are modern, secure examples for connecting to MySQL from popular programming languages.

Best Practice: Secure Credential Management

WARNING: Never hardcode credentials (host, user, password) directly in your source code. This is a major security risk. Instead, load them from environment variables or a dedicated secrets management service (like HashiCorp Vault or AWS Secrets Manager).

PHP (using PDO)

PDO (PHP Data Objects) provides a consistent, secure interface for database access.

<?php
// Load credentials from environment variables
$host = getenv('DB_HOST') ?: '127.0.0.1';
$db   = getenv('DB_NAME') ?: 'my_app_db';
$user = getenv('DB_USER') ?: 'app_user';
$pass = getenv('DB_PASS') ?: 'secret';
$port = getenv('DB_PORT') ?: '3306';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;port=$port;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Connected to database successfully!";
} catch (\PDOException $e) {
     // In a real app, log the error and show a generic message
     error_log($e->getMessage());
     http_response_code(503);
     die("Service unavailable. Please try again later.");
}
?>

Node.js (using mysql2/promise)

The mysql2 library is a popular, high-performance choice. Using it with promises and async/await leads to clean, modern code. For real applications, always use a connection pool.

import mysql from 'mysql2/promise';

// In a real app, use a connection pool
// const pool = mysql.createPool({ ...config });

async function testConnection() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'app_user',
            password: process.env.DB_PASS || 'secret',
            database: process.env.DB_NAME || 'my_app_db'
        });

        console.log('Connected to MySQL successfully!');
        // You can now execute queries, e.g., await connection.execute('SELECT 1');

    } catch (error) {
        console.error('Failed to connect to MySQL:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

testConnection();