Installation and Initial Setup

This guide covers the process of deploying multiple MySQL instances on a single Linux server and configuring asynchronous replication between a master and slave node. The procedure uses MySQL 5.5.53 on a 64-bit Linux system, but the concepts apply to newer versions with minor adjustments.

Extracting and Preparing MySQL Distribution

Begin by downloading and extracting the MySQL binary distribution to the stendard installation directory:

cd /usr/local/src/
tar zxvf mysql-5.5.53-linux2.6-x86_64.tar.gz
mv mysql-5.5.53-linux2.6-x86_64 /usr/local/mysql

Verify that the mysql user exists on the system. If not, create a dedicated system account for running the MySQL daemon:

grep mysql /etc/passwd
useradd -s /sbin/nologin mysql

Add the MySQL binary directory to the system PATH to simplify command execution:

vim /etc/profile.d/mysql_path.sh
#!/bin/bash
export PATH=$PATH:/usr/local/mysql/bin

Copy the sample configuration file and startup script to their respective system locations:

cd /usr/local/mysql/
cp support-files/my-small.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld

Configuring the Master Instance

Edit the master configuration file to specify the installation and data directories:

vim /etc/my.cnf
basedir=/usr/local/mysql
datadir=/var/lib/mysql/master

Create the data directory with appropriate ownership permissions:

mkdir -p /var/lib/mysql/master
chown -R mysql:mysql /var/lib/mysql/master

Initializing and Starting the Master Database

Enitialize the database files and start the MySQL service:

./scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql/master
/etc/init.d/mysqld start

Deploying the Slave Instance

Create a complete copy of the MySQL installation directory for the slave instance:

cd /usr/local/
cp -r mysql mysql_slave
cd mysql_slave
cp /etc/my.cnf ./my.cnf

Configuring the Slave Instance

Modify the slave configuration file to use distinct network and storage settings:

vim my.cnf
[mysqld]
port = 3307
socket = /tmp/mysql_slave.sock
datadir = /var/lib/mysql/slave
server-id = 2

Initializing the Slave Database

Prepare the data directory for the slave instance:

mkdir -p /var/lib/mysql/slave
chown -R mysql:mysql /var/lib/mysql/slave
./scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql/slave

Creating Slave Startup Script

Duplicate the master startup script and modify it for the slave instance:

cp /etc/init.d/mysqld /etc/init.d/mysqld_slave
vim /etc/init.d/mysqld_slave
basedir=/usr/local/mysql_slave
datadir=/var/lib/mysql/slave
conf=$basedir/my.cnf

Register the slave service and initiate it:

chkconfig --add mysqld_slave
/etc/init.d/mysqld_slave start
netstat -lnp | grep mysql

Preparing Test Data

Create a test database on the master server to validate the replication configuration. First, export the system database for reference:

mysqldump -S /tmp/mysql.sock mysql > system_db.sql

Create the test database on the master:

mysql -S /tmp/mysql.sock -e "CREATE DATABASE IF NOT EXISTS testdb"

Configuring Master-Slave Replication

Master Server Configuration

Update the master configuration file to enable binary logging and assign a unique server identifier:

vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin=mysql-bin
binlog-do-db=testdb
binlog-ignore-db=information_schema

Restart the master service to apply changes:

service mysqld restart

Creating Replication User

Connnect to the master instance and create a dedicated user for replication operations:

mysql -S /tmp/mysql.sock
CREATE USER 'repl_user'@'127.0.0.1' IDENTIFIED BY 'SecurePass123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'127.0.0.1';
FLUSH PRIVILEGES;

Obtain the current binary log position for configuring the slave:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      120 | testdb       | information_schema |
+------------------+----------+--------------+------------------+
UNLOCK TABLES;

Configuring the Slave Server

Create the test database on the slave instance and import the data:

mysql -S /tmp/mysql_slave.sock -e "CREATE DATABASE IF NOT EXISTS testdb"
mysql -S /tmp/mysql_slave.sock testdb < system_db.sql

Connect to the slave instance and configure the replication parameters using the values obtained from the master:

mysql -S /tmp/mysql_slave.sock
STOP SLAVE;
CHANGE MASTER TO 
    MASTER_HOST='127.0.0.1',
    MASTER_PORT=3306,
    MASTER_USER='repl_user',
    MASTER_PASSWORD='SecurePass123',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=120;
START SLAVE;
SHOW SLAVE STATUS\G

Verify successful replication by checking the status indicators:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Both values must display "Yes" to confirm that the replication pipeline is functioning correctly. The master server can now be unlocked if it was previously locked for backup purposes.