How to Connect to a MySQL Database

By Alex Carter on September 27, 2024

Connecting to a MySQL database requires specifying the correct parameters and choosing an appropriate method based on the system and use case. This includes using the MySQL command line, Python connectors, or configuration files. Different authentication options, connection types, and error-handling techniques can affect performance and security. Understanding these methods ensures proper access to databases while maintaining security and efficiency. For ongoing performance tracking and issue detection, MySQL database monitoring helps ensure database stability and efficiency.

Connecting Using MySQL Command Line

Client programs like mysql and mysqldump require specific parameters to connect to a MySQL server. These parameters include the host where the server is running, the user account, and the password. While each has a default value, command-line options allow customization.

Default Connection Behavior

Running the mysql command without any additional parameters applies the default settings:

• The default host is localhost. On Unix, this is interpreted differently than on Windows;
• The default username is ODBC on Windows or the Unix login name on Unix;
• No password is provided unless explicitly specified;
• A database is not selected by default unless specified as an argument.

Specifying Connection Parameters

To connect using specific credentials, options can be passed via the command line. A default database can also be specified:

mysql –host=localhost –user=username –password=password database_name

mysql -h localhost -u username -password database_name

For password-related options:

• If –password or -p is used with a value, it must be directly attached without spaces;
• If used without a value, the client prompts for input, hiding the password;
• To avoid password entry, –skip-password can be specified;
• Storing passwords directly in the command is discouraged for security reasons.

Connection Types Based on Host Specification

The connection type depends on how the host is specified:

• If no host or localhost is provided:
  • On Windows, shared memory is used if enabled;
  • On Unix, a Unix socket file is used unless overridden;
  • If TCP/IP is not enabled and no socket is specified, a named pipe is used on Windows if supported.
• If a remote host is specified, TCP/IP is used by default.

Controlling Connection Behavior with Protocol Options

The –protocol option enforces a specific transport method:

mysql –protocol=TCP

For local TCP/IP connections, specifying 127.0.0.1 instead of localhost ensures TCP/IP usage.

mysql –host=127.0.0.1

For IPv6, –host=::1 can be used if the server supports it.

Named-Pipe and Remote Connections on Windows

To force a named-pipe connection:

mysql –protocol=PIPE

A remote connection using TCP/IP:

mysql –host=remote.example.com

To specify a non-default port:

mysql –host=remote.example.com –port=13306

On Unix, specifying a port does not override socket usage unless explicitly set to TCP/IP:

mysql –port=13306 –protocol=TCP

Storing Connection Parameters

To avoid repeatedly entering parameters, they can be saved in an option file:

[client]

host=hostname

user=username

password=password

Alternatively, environment variables can be used:

• MYSQL_HOST sets the default host;
• On Windows, USER specifies the MySQL user.

Using these options effectively ensures secure and flexible connections to the MySQL server.

Connecting to MySQL Using Connector/Python

The connect() function establishes a connection to a MySQL server and returns a MySQLConnection object. Below is an example of how to connect to MySQL:

import mysql.connector

cnx = mysql.connector.connect(user=’scott’, password=’password’,

                              host=’127.0.0.1′,

                              database=’employees’)

cnx.close()

For details on connection arguments, refer to Section 7.1, “Connector/Python Connection Arguments.”

Using MySQLConnection Class

Connection objects can also be created using the MySQLConnection class:

from mysql.connector import connection

cnx = connection.MySQLConnection(user=’scott’, password=’password’,

                                 host=’127.0.0.1′,

                                 database=’employees’)

cnx.close()

While both methods are valid, using connect() is preferred and widely adopted in examples.

Handling Connection Errors

To manage connection issues, use a try-except block and catch exceptions using errors.Error:

import mysql.connector

from mysql.connector import errorcode

try:

    cnx = mysql.connector.connect(user=’scott’, database=’employ’)

except mysql.connector.Error as err:

    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:

        print(“Invalid username or password”)

    elif err.errno == errorcode.ER_BAD_DB_ERROR:

        print(“Database does not exist”)

    else:

        print(err)

else:

    cnx.close()

Using a Configuration Dictionary

Connection arguments can be defined in a dictionary and passed using the ** operator:

import mysql.connector

config = {

    ‘user’: ‘scott’,

    ‘password’: ‘password’,

    ‘host’: ‘127.0.0.1’,

    ‘database’: ’employees’,

    ‘raise_on_warnings’: True

}

cnx = mysql.connector.connect(**config)

cnx.close()

Implementing a Reconnection Routine with Logging

A connection method with logging and retry attempts can be implemented as follows:

import logging

import time

import mysql.connector

# Configure logger

logger = logging.getLogger(__name__)

logger.setLevel(logging.INFO)

formatter = logging.Formatter(“%(asctime)s – %(name)s – %(levelname)s – %(message)s”)

# Console handler

handler = logging.StreamHandler()

handler.setFormatter(formatter)

logger.addHandler(handler)

# File handler

file_handler = logging.FileHandler(“cpy-errors.log”)

file_handler.setFormatter(formatter)

logger.addHandler(file_handler)

def connect_to_mysql(config, attempts=3, delay=2):

    attempt = 1

    while attempt <= attempts:

        try:

            return mysql.connector.connect(**config)

        except (mysql.connector.Error, IOError) as err:

            if attempt == attempts:

                logger.info(“Connection failed, exiting: %s”, err)

                return None

            logger.info(“Connection failed: %s. Retrying (%d/%d)…”, err, attempt, attempts)

            time.sleep(delay ** attempt)

            attempt += 1

    return None

Connecting to the Sakila Database

Assuming the connect_to_mysql function is stored in myconnection.py, the following code connects to the Sakila database:

from myconnection import connect_to_mysql

config = {

    “host”: “127.0.0.1”,

    “user”: “user”,

    “password”: “pass”,

    “database”: “sakila”,

}

cnx = connect_to_mysql(config, attempts=3)

if cnx and cnx.is_connected():

    with cnx.cursor() as cursor:

        cursor.execute(“SELECT * FROM actor LIMIT 5”)

        rows = cursor.fetchall()

        for row in rows:

            print(row)

    cnx.close()

else:

    print(“Could not connect”)

Choosing Between Python and C Extensions

Connector/Python supports both a pure Python implementation and a C extension, which uses the MySQL C client library. The use_pure argument determines which implementation is used:

use_pure=False (default in MySQL 8) enables the C extension.

If the C extension is unavailable, use_pure defaults to True, using the Python implementation.

Example of setting use_pure to False:

import mysql.connector

cnx = mysql.connector.connect(user=’scott’, password=’password’,

                              host=’127.0.0.1′,

                              database=’employees’,

                              use_pure=False)

cnx.close()

Alternatively, the C extension can be accessed directly by importing _mysql_connector instead of mysql.connector.

Conclusion

Understanding how to connect to a MySQL database is essential for managing data efficiently. Different methods, including command-line tools and programming interfaces, provide flexibility based on system requirements and security considerations. Using proper authentication, connection parameters, and error-handling techniques ensures reliable access. Configuring connection settings through environment variables or option files can simplify repeated connections while maintaining security.