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.
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.
Related Posts:
