Today we will discuss how setup a replication:
- Master – Slave Replication:
Requirement:
- 2 (Linux) System (MasterServer and SlaveServer)
- MySQL Installed
- Connection to both servers are accessible.
On Master Server:
- Edit my.cnf with following information:
[mysqld]
Log-Bin =MySQL_Binlog
Server_ID=1
- Create a replication login which has access to REPLICATION SLAVE
mysql> CREATE USER ‘repl’@’%.%’ IDENTIFIED BY ‘mypass’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.%’;
- Make sure before backup gather data for replication. Like bin-log file name and position.
mysql > SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql_Binlog.006 | 100 | mysql | |
+——————+———-+————–+——————+
This information is important from this information only we can setup the replication correctly(making both master and slave in sync).
- Copy the data from master to slave and restore it to slave
mysqldump –all-databases –master-data > Master_Dump.bkp
It is important to include –master-data option which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication.
We have to restart the mysql to effect my.cnf (bin-log enable) and other settings.
sudo /etc/init.d/mysql restart
On Slave Server:
- Make system aware that this is slave, by making server-id =2 in my.cnf.
[mysqld]
server-id=2
Restore the database
mysql –uroot –p < Master_Dump.bkp
Now we are ready to setup the replication configuration and provide the relation between master an stave. Here we have to provide information which we captured from show master status
mysql> CHANGE MASTER TO
-> MASTER_HOST=’ MasterServer,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’mypass’,
-> MASTER_LOG_FILE= ‘mysql_Binlog.006’,
-> MASTER_LOG_POS=100;
Once we run change master to command replication is setup between master and slave.
Now to start the replication we have to start slave
mysql> START SLAVE;
That it we have replication running.
To see the status of replication
mysql> SHOW SLAVE Status\G;
It will show all the information, some of the important information would be:
Slave_IO_State: Waiting for master to send event
Means replication is running good and no lag
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Means slave MySQL is running and connections is also good – replication is active.
Replicate_Do_DB: DBReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:
This Variables/options will filter or provide information about database/table specific replication.
Last_Error:
Shows error occurred lastly.
Seconds_Behind_Master: 0
Another very important variable to validate replication is up to date
Read_Master_Log_Pos: xxx Relay_Log_File: xxx.xx Relay_Log_Pos: xx Relay_Master_Log_File: xxx.xx
Provide information about Slave logging, how slave is working on the logs received from Master.
For setup master – master replication:
You have to enable bin-log at slave as well (second master).
And server_id=1 for that server.
Follow the same show master status parameters create repl user
And change master to configuration with master status parameters.
Reference:
https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/replication.html
