MySQL Master – Slave Replication on CentOS

Introduction

MySQL master-slave replication is quite simple and direct way to scale-out reads optimally. Retaining multiple copies of master across slaves also guarantee reliability and maximum availability, Replication is never an substitute or alternative for database backup and disaster recovery. In this post I am explaining simple step-by-step MySQL master-salve replication.

This blog post will be using following IP addresses:

MySQL Master – 192.168.56.11

MySQL Slave – 192.168.56.12

Configure MySQL master (192.168.56.11)

To configure a master to use binary log file based replication, Configure binary logging first and include a unique server ID. Once done these steps restart MySQL server. Binary logging is enabled by default (log_bin variable is set to ON). It’s always a best practice to create binary log files with a non-default base name to avoid confusion as your database infrastructure grows. To configure binary logging, first shutdown the MySQL and edit my.cnf or my.ini

[mysqld]
server-id=1
bind-address=192.168.56.11
log-bin=mysql-bin
binlog-do-db=sakila

To guarantee the ultimate transaction durability and consistency in the replication setup please confirm innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file. You must also confirm skip-networking option is not enabled on your replication master.

Configure MySQL slave (192.168.56.12)

MySQL slave must have a unique server ID, You must restart MySQL server after setting (this must be done in [mysqld] section of the configuration file) this in my.cnf . A slave is not required to have binary logging enabled to setup an simple MySQL master-slave  replication but if you are building an relay-slave MySQL replication where server A serves as the master for slave B, and B serves as the master for slave C then slave B binary logging must be enabled. In addition to binary logging, this kind of replication topology requires the –log-slave-updates option to be enabled. You can disable binary logging on slave by configuring –skip-log-bin and –skip-log-slave-updates options in my.cnf

[mysqld]
server-id=2

Creating user for MySQL replication

The slave connect to master using MySQL username and password, There must a user account on the master that slave can use to connect. This user account created in the master must be granted with REPLICATION SLAVE privilege. You may create different account for each slave or connect to master using the same account for each slave. Though it’s not compulsory to create user account specifically for replication, You must be aware that the replication user name and password is stored in plain text in the master info data dictionary table mysql.slave_master_info so from an MySQL security perspective it is recommended to have a dedicated user account for successful replication.

Please create the user below in MySQL master – 192.168.56.11

mysql> CREATE USER 'repl'@'192.168.56.12' IDENTIFIED BY 'MySQLDBA19/47';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.12';

Collecting MySQL replication Master Binary Log Coordinates

MySQL slave must be configured to start replication from the correct point, To do this task successfully you must note down master’s current coordinates with its binary log info. To collect MYSQL master binary log coordinates please FLUSH TABLES WITH READ LOCK, which blocks COMMIT operations for InnoDB tables (data consistency in replication is very important )Please run the below SQL in MySQL master – 192.168.56.11

mysql> FLUSH TABLES WITH READ LOCK;

Now from different MySQL session on the master run SHOW MASTER STATUS statement to collect current binary log file name and it’s position

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000147 | 450      | sakila       | manual,mysql     |
+------------------+----------+--------------+------------------+

This is the position (Position 450) from which the slave database will start replication, Take a note the number as you need them while setting up slave for MySQL replication. Now you are ready to proceed with exporting your database using mysqldump from a new window  ( please confirm you are not typing this command from MySQL, It’s done from bash shell )

mysqldump -u root -p --opt sakila > sakila-backup.sql

Now unlock tables in the master

UNLOCK TABLES;

Restore the mysqldump backup to slave (192.168.56.12)

mysql -u root -p sakila < sakila-backup.sql

Connecting slave to master

Run the script below from MySQL terminal in the slave

CHANGE MASTER TO 
MASTER_HOST='192.168.56.11', 
MASTER_USER='repl', 
MASTER_PASSWORD='MySQLDBA19/47', 
MASTER_LOG_FILE='mysql-bin.000147', 
MASTER_LOG_POS=  450;

Start MySQL slave

START SLAVE;

To confirm the successful slave replication please run below command on slave terminal

SHOW SLAVE STATUS\G;

Congrats, All done for an successful MySQL master-slave replication on CentOS !