Install MySQL 9.7 on Ubuntu 24.04 Master Slave Setup

Title: How to Install MySQL 9.7 on Ubuntu 24.04 and Move Data Directory to a Custom Location

Introduction

This guide walks you through installing MySQL 9.7 from the official community bundle on Ubuntu 24.04, and then moving the database storage to a separate disk or custom folder (e.g., /mysql9.7). This is useful when you want to separate database files from the root filesystem.

Following topics are covered:

Part 1: Prepare the Disk for MySQL
Part 2: Download and Extract the MySQL Bundle
Part 3: Install MySQL Packages
Part 4: Move MySQL Data Directory to a Custom Location
Part 5: Configure AppArmor Permissions
Part 6: Start MySQL and Verify
Part 7: Prepare the Replica Directories
Part 8: Configure AppArmor for the Replica
Part 9: Configure the Primary (Master) Instance
Part 10: Create the Replica (Slave) Configuration
Part 11: Initialize the Replica Data Directory
Part 12: Launch the Replica Daemon
Part 13: Create a Replication User on the Primary
Part 14: Link the Replication Channels
Part 15: Test the Replication
Part 16: Test the Read-Only Rule on the Replica
Part 17: Stop the Slave Database
Part 18: Create a Systemd Service for the Replica
Part 19: Move MySQL Data Directory to Custom Subdirectories
Part 20: Change the MySQL Password Policy

Part 21: Useful Commands for Slave Database Management

Prerequisites

Ubuntu 24.04 server
Root or sudo privileges
An additional disk (e.g., /dev/sdb) if you want to store data on a separate drive

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Step 1: Prepare the Disk (Optional)

If you want to use a dedicated disk for MySQL data, follow these steps.

Check available disks:

fdisk -l

Create a new partition on /dev/sdb:

fdisk /dev/sdb
n
p
w

Format the partition with XFS:

mkfs.xfs /dev/sdb1
blkid /dev/sdb1

Copy the UUID from the blkid output, then edit /etc/fstab:

vi /etc/fstab

Add this line (replace the UUID with your own):

UUID=1ebc231c-4d0c-426b-8b1e-3cf6f447b759 /mysql9.7 xfs defaults 0 0

Mount the new filesystem:

mkdir /mysql9.7
mount -a

Step 2: Download and Extract MySQL Bundle

Check available packages:

apt list --upgradable
apt list mysql*

Download the MySQL Community Server bundle from the official MySQL website:

mysql-server_9.7.2-1ubuntu24.04_amd64.deb-bundle.tar

Extract the bundle:

tar -xvf mysql-server_9.7.2-1ubuntu24.04_amd64.deb-bundle.tar

Step 3: Install MySQL Packages

Which packages to install?

mysql-common, mysql-community-client-plugins, mysql-community-client-core, mysql-community-client, mysql-client, mysql-community-server-core, mysql-community-server

Pre-configure the database server:

sudo dpkg-preconfigure mysql-community-server_*.deb

When prompted, set the MySQL root password (example: testtest). Use a strong password in production.

Install core dependencies:

sudo apt update
sudo apt install libaio1t64 libmecab2 psmisc libnuma1 -y

Install the selected MySQL packages in strict order:

dpkg -i \
  mysql-common_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-community-client-plugins_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-community-client-core_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-community-client_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-client_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-community-server-core_9.7.2-1ubuntu24.04_amd64.deb \
  mysql-community-server_9.7.2-1ubuntu24.04_amd64.deb

Verify the installation:

dpkg -l | grep -i mysql

Or:

apt list --installed | grep mysql

Step 4: Move MySQL Data Directory to a Custom Location

By default, MySQL stores data in /var/lib/mysql. We will move it to /mysql9.7.

Stop the MySQL service:

systemctl stop mysql

Create the new directory and set permissions:

sudo mkdir /mysql9.7
sudo chown -R mysql:mysql /mysql9.7
sudo chmod 750 /mysql9.7

Copy the existing data to the new location:

sudo rsync -av /var/lib/mysql/ /mysql9.7

Edit the MySQL configuration file:

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

Find the datadir line and change it to:

datadir = /mysql9.7

Step 5: Configure AppArmor Permissions

Ubuntu uses AppArmor to restrict software from accessing non-standard folders. If you skip this step, MySQL will fail to start.

Edit the AppArmor profile for MySQL:

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Find the lines that reference /var/lib/mysql/. Add these two lines right below them:

/mysql9.7/ r,
/mysql9.7/** rwk,

Reload AppArmor to apply the new rules:

sudo systemctl reload apparmor

Step 6: Start MySQL and Verify

Start the MySQL service:

systemctl start mysql

mysql -u root -ptesttest

Check the data directory:

SELECT @@datadir;

You should see /mysql9.7/ as the output.

Introduction

This guide shows you how to configure a MySQL primary (master) and replica (slave) instance on the same physical host. This setup is useful for testing replication, learning MySQL internals, or running a local development environment without needing multiple servers.

We will use:

Primary instance: port 3306, data directory /var/lib/mysql (default)
Replica instance: port 3307, data directory /mysqlslave/data

Prerequisites

MySQL 9.7 already installed (see previous guide)
Root or sudo privileges
AppArmor enabled (default on Ubuntu)

Step 1: Prepare the Replica Directories

Create the folder structure for the slave instance:

sudo mkdir -p /mysqlslave/data
sudo mkdir -p /mysqlslave/logs
sudo mkdir -p /mysqlslave/run

Set ownership and permissions:

sudo chown -R mysql:mysql /mysqlslave
sudo chmod -R 750 /mysqlslave

Step 2: Configure AppArmor for the Replica

Ubuntu's AppArmor will block MySQL from accessing /mysqlslave unless you whitelist it.

Edit the AppArmor profile:

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Add these two lines just before the closing bracket }:

/mysqlslave/ r,
/mysqlslave/** rwk,

Reload AppArmor:

sudo systemctl reload apparmor

Step 3: Configure the Primary (Master) Instance

Edit the primary configuration file:

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

Ensure these lines exist inside the [mysqld] block:

server_id = 1
log_bin = mysql-bin
port = 3306
bind-address = 0.0.0.0

Save and exit, then restart the primary:

sudo systemctl restart mysql

Step 4: Create the Replica (Slave) Configuration

Create a dedicated configuration file for the replica instance:

sudo nano /etc/mysql/my_slave.cnf

Paste the following content:

[mysqld]
# Network and Identity
server_id = 2
port = 3307
socket = /mysqlslave/run/mysqld.sock
pid-file = /mysqlslave/run/mysqld.pid

# Custom data and log locations
datadir = /mysqlslave/data
log_error = /mysqlslave/logs/error.log

# Replication parameters
relay_log = /mysqlslave/logs/mysql-relay-bin
read_only = 1

Save and exit.

Step 5: Initialize the Replica Data Directory

Run the initialization command:

mysqld --defaults-file=/etc/mysql/my_slave.cnf --initialize-insecure --user=mysql

Note: The --initialize-insecure flag creates a blank root password for this instance. This is fine for local testing but never use it in production.

Step 6: Launch the Replica Daemon

Since the package manager only tracks the main systemd service, start the slave instance manually:

sudo -u mysql mysqld --defaults-file=/etc/mysql/my_slave.cnf &

Verify it is listening on port 3307:

ss -tulpn | grep 3307

You should see the mysqld process bound to port 3307.

Step 7: Create a Replication User on the Primary

mysql -u root -p -P 3306

Create a dedicated replication user:

CREATE USER 'repl'@'localhost' IDENTIFIED BY 'replpassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';
FLUSH PRIVILEGES;

Check the primary's binary log status:

SHOW MASTER STATUS;

Note down the File and Position values. You will need them in the next step.

Example output:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      157 |              |                  |
+------------------+----------+--------------+------------------+

Step 8: Link the Replication Channels

mysql -u root -P 3307 --socket=/mysqlslave/run/mysqld.sock

Configure the replica to connect to the primary:

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='replpassword',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=157;

Replace the MASTER_LOG_FILE and MASTER_LOG_POS values with the ones you noted from SHOW MASTER STATUS on the primary.

Start the replication:

START SLAVE;

Check the replication status:

SHOW SLAVE STATUS\G

Look for these two lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If both show Yes, replication is working.

Step 9: Test the Replication

On the primary (port 3306), create a test database:

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table VALUES (1, 'Hello Replication');

On the replica (port 3307), verify the data was replicated:

SHOW DATABASES;
USE test_replication;
SELECT * FROM test_table;

You should see the same data on the replica.

Troubleshooting Tips

If Slave_IO_Running is No, check the error log at /mysqlslave/logs/error.log.
If AppArmor blocks access, verify the rules in /etc/apparmor.d/usr.sbin.mysqld and reload AppArmor.
Ensure the repl user is allowed to connect from 127.0.0.1 (use 'repl'@'%' if needed for remote hosts).
If the replica fails to start, check that the mysql user owns all files in /mysqlslave.

Stopping the Replica

To stop the manually launched replica:

mysqladmin -u root -P 3307 --socket=/mysqlslave/run/mysqld.sock shutdown

Or find the PID and kill it:

pkill -f my_slave.cnf

Title: MySQL Replica Management, Read-Only Rules, Systemd Service, and Data Directory Relocation

Introduction

This guide covers advanced MySQL replica (slave) management tasks, including enforcing read-only rules, creating a systemd service for the replica, relocating the data directory to custom subdirectories, changing password policies, and useful replication commands. It is a continuation of the MySQL master-slave setup guide.

Part 1: Test the Read-Only Rule on the Replica

By default, the read_only parameter does not apply to users with the SUPER privilege (such as root). This means root can still write to a replica unless you enforce super_read_only.

Try to insert data as root on the replica:

INSERT INTO employees (first_name, department) VALUES ('Eve', 'Malicious');

If this succeeds, you need to enforce stricter read-only rules.

Temporary Fix (Runtime)

Run this on the replica:

SET GLOBAL super_read_only = 1;

Now if you try to insert data as root, you will get:

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Permanent Fix (Configuration File)

Edit the replica configuration:

sudo nano /etc/mysql/my_slave.cnf

Find the line read_only = 1 and add this line right below it:

read_only = 1
super_read_only = 1

Save and exit, then restart the replica.

Part 2: Stop the Slave Database

You can stop the replica using MySQL or Linux process control.

Method 1: Using MySQL SHUTDOWN

mysql -u root -P 3307 --host=127.0.0.1
SHUTDOWN;

Method 2: Using the Linux Process Control Approach

sudo kill $(cat /mysqlslave/run/mysqld.pid)

Verify it has stopped:

ss -tulpn | grep 3307

If no output appears, the replica has stopped successfully.

Part 3: Create a Systemd Service for the Replica

Instead of launching the replica manually in the background, create a proper systemd service so it starts automatically and is managed by the system.

Create the service file:

sudo nano /etc/systemd/system/mysql-slave.service

Paste the following content:

[Unit]
Description=MySQL Replica Server
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/mysql/my_slave.cnf
LimitNOFILE=65535
TimeoutSec=300
PrivateTmp=true

[Install]
WantedBy=multi-user.target

Note: The [Unit] and [Install] sections were missing in the original file. Without them, systemd cannot manage the service properly.

Kill the current manual background process (if running):

kill $(cat /mysqlslave/run/mysqld.pid) 2>/dev/null || true

Reload systemd and manage the service:

sudo systemctl daemon-reload
sudo systemctl start mysql-slave
sudo systemctl stop mysql-slave
sudo systemctl status mysql-slave

Enable it to start on boot:

sudo systemctl enable mysql-slave

Ensure permissions are correct:

sudo chown -R mysql:mysql /mysqlslave
sudo chmod -R 750 /mysqlslave

Part 4: Move MySQL Data Directory to Custom Subdirectories

This section shows how to relocate the main MySQL data directory and its related files (binlogs, undo logs, error logs, relay logs) to a custom location with separate subdirectories.

Stop the MySQL Service

sudo systemctl stop mysql

Create the New Directory Structure

sudo mkdir -p /mysql9.7/{data,binlog,undofile,logs,relay_log}
sudo chown -R mysql:mysql /mysql9.7
sudo chmod -R 750 /mysql9.7

Edit the Configuration File

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

Update the [mysqld] section with these directives:

[mysqld]
# 1. Main Data Directory
datadir = /mysql9.7/data

# 2. Binary Logs (Requires a base filename prefix)
log_bin = /mysql9.7/binlog/mysql-bin

# 3. Undo Logs (Requires a directory path)
innodb_undo_directory = /mysql9.7/undofile

# 4. Error Logs (Requires a specific file name, not just a folder)
log_error = /mysql9.7/logs/mysql-error.log

# 5. Relay Logs (For Replication - Requires a base filename prefix)
relay_log = /mysql9.7/relay_log/mysql-relay-bin

Adjust AppArmor (Critical Step)

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Add these lines before the closing bracket:

/mysql9.7/ r,
/mysql9.7/** rwk,

Reload AppArmor:

sudo systemctl reload apparmor

Start MySQL

sudo systemctl start mysql

Verify the new data directory:

mysql -u root -p
SELECT @@datadir;

Part 5: Change the MySQL Password Policy

MySQL 8.0 and later include a password validation component that enforces strong passwords. For local testing, you may want to relax this policy.

Check current settings:

SHOW VARIABLES LIKE 'validate_password%';

Temporary Fix (Runtime)

SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 4;

Permanent Fix (Runtime Persist)

SET PERSIST validate_password.policy = LOW;
SET PERSIST validate_password.length = 4;

Permanent Fix (Configuration File)

Edit the MySQL configuration:

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

Add these lines under [mysqld]:

validate_password.policy=LOW
validate_password.length=4

Restart MySQL:

sudo systemctl restart mysql

Part 6: Useful Commands for Slave Database Management

Ensuring Read-Only Status

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

Replication Control

STOP REPLICA;
RESET REPLICA ALL;   -- Caution: This clears all replication settings
SHOW MASTER STATUS;
START REPLICA;

Check Replication Status

SHOW REPLICA STATUS\G
SHOW BINARY LOG STATUS;

Start Individual Replication Threads

START REPLICA IO_THREAD;
START REPLICA SQL_THREAD;

Stop Individual Replication Threads

STOP REPLICA IO_THREAD;
STOP REPLICA SQL_THREAD;

Knowledge Hub

Tags

Thursday, October 1, 2026