MySQL Master and Slave replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous, This means your Slave servers do not need to be connected permanently to receive updates from the Master, For example, you could stop the Slave thread on the Slave server and start it back up at a later time and it would automatically catch itself up to the Master.

mysql master and slave

How to setup MySQL Master and Slave Replication?

We have tested MySQL Master and Slave replication with two centOS servers.

Master : 192.168.1.133

mysql2 : 192.168.1.136

master my.cnf

vi my.cnf

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

mysql2

[mysqld]
log-bin=mysql-bin
server-id=2
Master

mysql> create user ‘replicator’@’%’ identified by ‘password’;
mysql>grant replication slave on *.* to ‘replicator’@’%’;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 342 | | |
+——————+———-+————–+——————+
then goto mysql2 and login mysql with root privileges.
mysql> create user ‘replicator’@’%’ identified by ‘password’;
mysql> grant replication slave on *.* to ‘replicator’@’%’;

mysql> slave stop; CHANGE MASTER TO MASTER_HOST = ‘192.168.1.133’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS = 342; slave start;

mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 106 | | |
+——————+———-+————–+——————+
Goto Master server and run
mysql>slave stop; CHANGE MASTER TO MASTER_HOST = ‘192.168.1.136’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS = 106; slave start;
Thats all

mysql>create database example;

mysql>create table example.dummy (`id` varchar(10));

check database example with mysql2 server.