MySql Primary-Primary Replication
Are you looking to increase the performance of DB and more reliability?
In one of the places I worked, I had to set up master-master replication which seemed like a huge project to me, not because of the fact it was but just because I had no idea what it takes to complete that. Didn’t you ever feel like it's a huge one when you don't know what it was but later it was a piece of cake?
Hopefully, this will give you guidance on how to set up in MySql 5.1(I know Upgrading to newer versions is not easy ;) )
For our requirement, only one master has received data from the queries, But this can be used for sharding. For example: Read load from one master and write load from another master.
Operating System: Centos 6, MySql 5.1
Step-by-step guide
Install Mysql server in both the replication servers for example Server A and Server B —
yum install mysql-server
when you are editing the auto-increment-offset one of the master will get the index values of even and one master will get index values of odd. If you have any specifications of controlling which master db should get what you can tweak them in the configuration file.
On Server A edit /etc/mysql/my.cnf
- server-id=1
- log-bin=”mysql-bin”
- relay-log=”mysql-relay-log”
- auto-increment-increment = 2
- auto-increment-offset = 1
Restart the MySQL service after editing the configuration file: service mysqld restart
Login as a root user: mysql -u root -p
Create a replicator user: CREATE USER ‘replicator’@’%’ IDENTIFIED BY ‘[replicator_password]’;
Grant permissions for replication user: GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’ IDENTIFIED BY ‘[replicator_password]’;
show master status;
Save Log file name and Log file position which will be displayed from above command.
On server B edit /etc/mysql/my.cnf
- server-id=2
- log-bin=”mysql-bin”
- relay-log=”mysql-relay-log”
- auto-increment-increment = 2
- auto-increment-offset = 2
Restart the MySQL service after editing the configuration file: service mysqld restart
Login as a root user: mysql -u root -p
Create a replicator user: CREATE USER ‘replicator’@’%’ IDENTIFIED BY ‘[replicator_password]’;
Grant permissions for replication user: GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’ IDENTIFIED BY ‘[replicator_password]’;
If there is already data in server A, dump the SQL to Server B and restore it.
stop slave;
CHANGE MASTER TO MASTER_HOST = ‘Server A IP Address’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘[replicator_password]’, MASTER_LOG_FILE = ‘log-file-name’, MASTER_LOG_POS = log-fileposition;
start slave;
On server B — show master status;
On server A- stop slave;
CHANGE MASTER TO MASTER_HOST = ‘Server B IP Address’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘replicator_password’, MASTER_LOG_FILE = server b file-name’, MASTER_LOG_POS = log-fileposition of server B;
start slave;
Getting log positions and log file name of each other and assigning it is really important as it starts getting replicated from that point in the file.
Make sure to look at mysql logs and do some tests like insertions and deletions, update and check whether the data is being replicated to each others master
