MySQL Replication Setup

• Replication used to replicate data from the Master node to a slave node[Replica].
• By default Replication is asynchronous.
• It uses binary logs for reading data from the Master node and relay log on slave[Replica].
• Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Methods:

• File-based Replication:

The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave.

• GTID Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave.

 

Configuration variables for replication:

MASTER Conf:

server_id=145

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin145

relay-log=/mysql/log/relay/relay-bin145

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

SLAVE conf:

server_id=146

gtid-mode=on

enforce-gtid-consistency=1

log-slave-updates=1

log-bin=/mysql/log/binary/mysql-bin146

relay-log=/mysql/log/relay/relay-bin146

relay_log_recovery= on

master_info_repository = TABLE

relay_log_info_repository = TABLE

# don’t replicate the mysql database

replicate_wild_ignore_table = mysql.%

read-only

skip-slave-start

 

Replication setup:

1. Create replication user on MASTER with replication privileges.

CREATE USER IF NOT EXISTS ‘rpluser’@’%’ IDENTIFIED BY ‘rpluser@1234’;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘rpluser’@’%’;

2. On SLAVE: setup replication as follows :

File-based Replication:

CHANGE MASTER TO MASTER_HOST='<MASTER_IP’,MASTER_USER=’rpluser’,MASTER_PASSWORD=’rpluser1234′,MASTER_PORT=3306,MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=690;

GTID Replication:

CHANGE MASTER TO MASTER_HOST='<MASTER_IP’,MASTER_USER=’rpluser’,MASTER_PASSWORD=’rpluser1234′,MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

3. Start slave

START SLAVE;

4. Check slave status

SHOW SLAVE STATUS;

Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’

Ref: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html

 

MySQL 5.7 Binary install on Linux

In Generic binary Installation method, we are extracting compressed tar files of MySQL and later we can do customization for MySQL setup as per our requirement. Like having different data directory, log directory etc.

MySQL Generic Binaries can be used to install MySQL on Unix/Linux platforms.

MySQL Installation has a dependency on the libaio library. Make sure that is there on a server before mysql install.

• Create a mysql user and group, which we will use as a service account for the mysql server.

shell> groupadd mysql

shell> useradd -r -g mysql -s /bin/false mysql

• Extract MySQL binaries TAR to mysql base directory for example in /mysql dir

• Create require directories and set appropriate permissions.

shell> cd mysql

shell> mkdir data mysql-files log tmp socket pid

shell> chown -R mysql:mysql .

shell> chmod 750 mysql-files

Sample my.cnf

• Add/update the following variables to the appropriate configuration section of my.cnf.

[mysqld]

#GENERAL

user = mysql

port = 3306

server_id = 1

skip-name-resolve

bind-address= 0.0.0.0

default_storage_engine = InnoDB

character_set_server = utf8

socket = /mysql/socket/mysql.sock

pid-file = /mysql/pid/mysqld.pid

# DATA STORAGE #

basedir= /mysql

datadir = /mysql/data

#INNODB

innodb_file_per_table = 1

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend

innodb_log_buffer_size = 16M

innodb_log_file_size = 100M

innodb-log-files-in-group = 3

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_tmpdir= /mysql/tmp

#MyISAM

key_buffer_size = 64M

#Logging

log_error = /mysql/log/mysqld.log

master_info_repository = TABLE

relay_log_info_repository = TABLE

log-bin = /mysql/log/binary/mysql-bin1

relay-log = /mysql/log/relay/relay-bin1

relay_log_recovery = on

log-slave-updates = 1

expire_logs_days = 15

gtid-mode = on

enforce-gtid-consistency = 1

binlog_format = row

binlog_row_image = minimal

# General logs (only enable for debugging – it use too much I/o)

#general-log = on

#general-log-file = /mysql/log/general-query.log

# Slow query logs (optional)

slow_query_log = on

long_query_time= 3

slow_query_log_file = /mysql/log/slow-query.log

max_allowed_packet=300M

[mysql]

socket = /mysql/socket/mysql.sock

[client]

socket = /mysql/pid/mysql.sock

port = 3306

 

• Initialize mysql server with my.cnf file configuration.

shell> bin/mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql —basedir=/mysql/ –datadir=/mysql/data

NOTE: –defaults-file should be the first option always while using a command-line option.

START MySQL

shell> bin/mysqld -–defaults-file=/etc/my.cnf –user=mysql

NOTE: If it is not working try to start mysql with mysqld_safe, this for Syetem V platforms which needs mysqld_safe to run mysqld. 

shell> bin/mysqld_safe -–defaults-file=/etc/my.cnf –user=mysql

MySQL SHUTDOWN

shell> mysqladmin –u -p shutdown

Run MySQL as a service

shell> cp support-files/mysql.server /etc/init.d/mysql

MySQL START/STOP/STATUS

shell> /etc/init.d/mysql start/stop/status

Ref: https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html