MySQL Replication in 5 Minutes

MySQL Replication in 5 Minutes

Configure MySQL replication is extremely simple. This article demonstrates how to create a master in minutes replicating it for a slave. Replication is a native MySQL feature and has multiple uses, such as backup, high availability, redundancy and geographical data distribution, horizontal scalability, among others.

For this simple test, we use Linux and configure replication in MySQL 5.6 between two instances: a master and a slave. We will create two instances MySQL from scratch, that is, without data. They will be on the same machine, but responding in different ports: 3310 and 3311.

The only requirement is to have MySQL 5.6 installed.

• If you have it installed, simply use the path where resides bin/mysqld as basedir in the steps below. For example, in Oracle Linux 7 or RHEL 7 the binary is located in /usr/sbin/mysqld, therefore basedir=/usr;

•  If you do not have MySQL 5.6 binaries, just download the tar file and unpack it in a convenient directory that will be your basedir, such as /opt/mysql/mysql-5.6:

# mkdir /opt/mysql
# cd /opt/mysql
# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# tar xvzf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# rm mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql-5.6

Note: In this case, consider the steps below basedir=/opt/mysql/mysql-5.6; always try to work with the latest versions — replace 5.6.23 in the above commands if a brand new one is available in http://dev.mysql.com/downloads/mysql.

Simple replication

Create an instance for the master:

# mkdir /opt/mysql/master /opt/mysql/master/data /opt/mysql/master/tmp
# cd /opt/mysql/master
# nano master.cnf
[client]
port=3310
socket=/opt/mysql/master/tmp/my-master.sock
[mysql]
prompt=master>\\_
[mysqld]
server-id=10
port=3310
basedir=/usr
datadir=/opt/mysql/master/data
socket=/opt/mysql/master/tmp/my-master.sock
log-bin=master-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# chown mysql:mysql *
# /usr/bin/mysql_install_db --defaults-file=/opt/mysql/master/master.cnf --user=mysql

Start and test the new instance:

# /usr/bin/mysqld_safe --defaults-file=/opt/mysql/master/master.cnf &
# mysql --defaults-file=/opt/mysql/master/master.cnf -uroot -p
master> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3310 |
+---------------+-------+

Note: To stop the MySQL process when necessary, make a clean shutdown:

# mysqladmin --defaults-file=/opt/mysql/master/master.cnf -uroot –p shutdown

Open another terminal and create another instance to be the Slave:

# mkdir /opt/mysql/slave /opt/mysql/slave/data /opt/mysql/slave/tmp
# cd /opt/mysql/slave
# nano slave.cnf
[client]
port=3311
socket=/opt/mysql/slave/tmp/my-slave.sock
[mysql]
prompt=slave>\\_
[mysqld]
server-id=11
port=3311
basedir=/usr
datadir=/opt/mysql/slave/data
socket=/opt/mysql/slave/tmp/my-slave.sock
log-bin=slave-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# chown mysql:mysql *
# /usr/bin/mysql_install_db --defaults-file=/opt/mysql/slave/slave.cnf --user=mysql
Start and test the new instance:
# /usr/bin/mysqld_safe --defaults-file=/opt/mysql/slave/slave.cnf &
# mysql --defaults-file=/opt/mysql/slave/slave.cnf -uroot -p
slave> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3311 |
+---------------+-------+

Now that we have two instances with different server-ids and log-bin enabled, create a user in the master instance so the slave can connect to it:

master> CREATE USER repl_user@127.0.0.1;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user@127.0.0.1 IDENTIFIED BY 'repl_user_password';

Note: In an actual installation, the slave instance will probably be in another host — replace 127.0.0.1 with the host IP where your slave instance is.

Before starting replication, check the Master status:

master> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: master-bin.000003
Position: 433

Use the data status above and initiates replication on the slave:

slave> CHANGE MASTER TO
MASTER_HOST=’127.0.0.1′,
MASTER_PORT=3310,
MASTER_USER=’repl_user’,
MASTER_PASSWORD=’repl_user_password’,
MASTER_LOG_FILE=’master-bin.000003′,
MASTER_LOG_POS=433;
slave> START SLAVE;

Basic test:

master> CREATE DATABASE teste_repl;
master> CREATE TABLE teste_repl.simples (id INT NOT NULL PRIMARY KEY);
master> INSERT INTO teste_repl.simples VALUES (999),(1),(20),(5);
slave> SELECT * FROM teste_repl.simples;

References:
http://dev.mysql.com/doc/refman/5.6/en/binary-installation.html
http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html

HackTheSecurity
HackTheSecurity Twitter
HackTheSecurity Facebook