Setup MySQL Master-Slave Replication

Replication enables the data from one MySQL database server (master)to be copied to one or more servers ( slaves ). MySQL Replication is Async by default; slaves do not need to be connected permanently to receive updates from a source.

Replication can be configured for all the databases, selected databases, or even a tables within the database

Advantages of replication

Scale-out – This will act as a load balancer for the MySQL server, the reads can be spread among the multiple replicas to improve performance. All writes will be performed on the master server and the reads may take place in one or more slaves. This will improve the Reads dramatically as it is spread across multiple nodes and also the writes as the master is dedicated for the Updates.

DR Copy – The replication can be used to setup the DR copy to the long distance data server ( remote site )

Consistent Backup without Locking the Master Server: Replica nodes can be used to take the backup, the replica can be paused and take the backup and restart the process

Setup

With the sudo privilege install the MySQL Server on two nodes

sudo apt-get install mysql-server

Create User on Master Server

Pick one server as a master server and create an user and who can login from any computer ( % ) and Enable the ReplicationAdmin role to the user

shell> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%' IDENTIFIED BY 'password';

Follow up with flush privileges

shell> FLUSH PRIVILEGES;

Enable the Bin Log mode

Bin Log or Binary Log of the MySQL Server contains “events” that describe database changes such as table creation operations or changes to table data.

It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the events contained in its binary log to its replicas, which execute those events to make the same data changes that were made on the source.

Note : Running a server with binary logging enabled makes performance slightly slower.

To enable the binary log, start the server with the –log-bin=<base_name> option. If no base_name value is given, the default name is the value of the –pid-file option (which by default is the name of host machine) followed by -bin.

If the base name is given, the server writes the file in the data directory unless the base name is given with a leading absolute path name to specify a different directory. It is recommended that you specify a base name explicitly rather than using the default of the host name

Edit the mysql configuration files (/etc/my.cnf) and add below lines

log-bin=nodename

Set the Server ID

server_id is set to 0 by default, On a replication source server and the replica servers you must specify the server_id to establish the unique replication ID in the range from 1 to 2^32

So to the master set the ID to 1 and for all replicas set unique server ID starts from 2

shell> set global server_id =1

To confirm the Server ID run the below query

shell> show variable where variable_name = 'server_id';

Then Restart the MySQL Server

sudo service mysql restart

Get the master status and get the position

shell> show master status

Run the above query and get the position

Configuring the Slave

Take the dump of the MySQL Server and restore the database using the dump in the slave node

Stop the slave

shell> stop slave

Run the below query replacing the value to match with your configuration to designate the current node as slave to the master, and provides the credential details and also let the slave where to start replicating from

change master to master_host = 'vignesh-n1',
master_user = 'replica', master_password='admin@123',
master_log_file = 'VIGNESH-N1-bin.000001';

Activate the Slave

shell> START SLAVE;

Get the status of the slave

shell> SHOW SLAVE STATUS\G

if there is an issue in connecting, you can try starting slave with a command to skip over it:

shell> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

---

Also published on Medium.