Step by step mysql replication

Replication Master: oel1.localdomain, 192.168.1.201/24
Replication Slave: oel2.localdomain, 192.168.1.202/24

Perform following task on master server(oel1.localdomain). 

Create MySQL user in master server that will be used as mysql replication slave.

mysql> grant replication slave on *.* to rep_admin@'192.168.1.202' identified by 'rep_password';
mysql> flush privileges;

Add following parameters on /etc/my.cnf

[mysqld]
server-id = 1
log-bin = oel1-bin

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Lock master database before generating dump.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Generate dump of master database.

mysqldump -u root -p --master-data=2 --all-databases > oel1_database.dmp

Release lock from mysql master database.

UNLOCK TABLES;

Ship dumpfile to slave server.

scp oel1_database.dmp dilli@192.168.1.202:

Perform following task on slave server(oel2.localdomain). 

Search for change master statement on dumpfile.

[root@oel2 dilli]# grep -i "change master" oel1_database.dmp 

Add following lines on the /etc/my.cnf file of slave server.

[mysqld]

server-id = 2

relay-log = /var/lib/mysql/mysql-relay-bin.log

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Start mysql server on replication client.

/etc/init.d/mysql start

Restore dumpfile on the slave server.

mysql -u root -p < oel1_database.dmp

Change master setting on slave.

CHANGE MASTER TO MASTER_HOST='192.168.1.201',MASTER_USER='rep_admin', MASTER_PASSWORD='rep_password', MASTER_LOG_FILE='oel1-bin.000001', MASTER_LOG_POS=120;

Begin log apply with Start slave.

start slave;

Verify replication setup. Add few rows on the master server and verify the change on slave server.

Insert rows on Master server.

Verify change on the slave server.

Additional my.cnf parameters.

# Master related Parameters

server-id=666

log-bin=oel1-bin

binlog_format=mixed

# Slave related Parameters

replicate-ignore-db=mysql

replicate-ignore-table=dlee.test

max_relay_log_size=104857600

skip-slave-start

read_only=1

# Connection related Parameters

max_connections=2048

max_allowed_packet=67108864

wait_timeout=60

# logging related parameters

long_query_time=5

slow_query_log=OFF

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

log_queries_not_using_indexes=OFF

# Innodb related parameters

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

innodb_file_per_table=1

innodb_buffer_pool_size=12G

innodb_buffer_pool_instances = 1

innodb_max_dirty_pages_pct = 70

innodb_log_file_size = 2024M

innodb_flush_log_at_trx_commit=0

innodb_flush_method=O_DSYNC

innodb_autoinc_lock_mode = 2

innodb_open_files = 2024

innodb_purge_threads = 2

innodb_purge_batch_size = 300

innodb_max_purge_lag = 5000000

innodb_io_capacity = 200

innodb_read_io_threads = 4

innodb_write_io_threads = 4

innodb_thread_concurrency=0

innodb_file_format = barracuda

innodb_table_locks = 0

innodb_stats_on_metadata = 0

innodb_change_buffering = ALL

innodb_log_buffer_size=28M

innodb_log_files_in_group=2

# Innodb flush parameters

innodb_flush_method=O_DIRECT