Installing and Configuring MySQL

Tutorial Overview

This tutorial provides a step-by-step guide for installing and configuring MySQL on Debian-based distributions, covering both installation basics and initial security configurations.

Prerequisites

• Root or sudo access.
• A Debian-based Linux system.

Steps

Step 1: Update and Install MySQL Server

1. Update your package index:

sudo apt update

2. Install the MySQL server package:

sudo apt install mysql-server -y

3. Confirm the installation by checking the MySQL service status:

sudo systemctl status mysql

• The output should indicate that the MySQL service is active and running.

Step 2: Secure the MySQL Installation

1. Run the MySQL security script:

sudo mysql_secure_installation

2. The script will guide you through several steps:

• Set the root password: Choose a strong password.
• Remove anonymous users: Recommended for security.
• Disallow root login remotely: Recommended unless remote access is needed.
• Remove test database and access to it: Prevent unauthorized access.
• Reload privilege tables now: Apply changes immediately.

Step 3: Configure MySQL for Remote Access (Optional)

1. Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Locate the line bind-address = 127.0.0.1 and change it to:

bind-address = 0.0.0.0

• This change allows MySQL to listen for connections on all network interfaces.

3. Restart MySQL to apply changes:

sudo systemctl restart mysql

Step 4: Create a New MySQL User and Grant Privileges

1. Log into MySQL as the root user:

sudo mysql -u root -p

2. Create a new user with remote access (replace username and password):

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

3. Grant the new user permissions for a specific database or all databases:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

4. Reload the privilege tables to apply changes:

FLUSH PRIVILEGES;

5. Test Remote Connection: Use a client like MySQL Workbench to confirm remote access with the newly created user.