Replication means making copy of the objects and moving transaction from primary server to secondary/standby/Slave server. There are two type of Replication
Master-Master Replication
Master-Slave Replication
Like other RDBMS MySQL works replication on Transactions. The pre requisite of replication is to enable the log-bin (bin log) transactions on the server. In MySQL when you enable the Bin log it will logged all the transactions from all the schemas/Databases, so we can setup a replication on all the databases. To enable replication for a specific database we have to make filter/specify the database or exclude the database for replication (we can even exclude/include the table/objects for replication).
Master-Master replication: in this type of replication both the server are accepting connections and changes can be performed on either of the server will replicate/effect to other node. Means on both the nodes you can make the changes.
For this Replication it could be possible that the conflict on the transactions/object may slow down or sometime it could be possible the replication may failed due to not managing the transactions (simultaneously)
Master-Slave Replication: In this type of replication master will be read-write and slave will be read only. So transactions will be moved from master to slave but slave will be worked as a reporting or taking a backup purpose to make master more efficient OLAP operations. This is most useful and commonly used replication.
Master should user with REPLICATION SLAVE permission on master and that user slave can use to connect to Master.
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
Finally once we backup the master and restore on to slave we need to configured the replication with log-bin position from where replication will start and Slave will start reading transactions from master.
SHOW MASTER STATUS;
Using this command we can get the Log-bin file name and the position of the log-bin to retrieve the transaction.
To enable or setup the replication
mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name‘,
-> MASTER_USER=’replication_user_name‘,
-> MASTER_PASSWORD=’replication_password‘,
-> MASTER_LOG_FILE=’recorded_log_file_name‘,
-> MASTER_LOG_POS=recorded_log_position;
*Replication cannot use UNIX socket files. You must be able to connect to the master MySQL server Host using TCP/IP
Start and stop slave/Replication can be done using following command:
mysql> START SLAVE;
mysql> START Stop;
There is other way of configuration replication using GUID
Reference:
https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/replication.html
Vinay Thakur 