Link to home
Start Free TrialLog in
Avatar of badwolfff
badwolfffFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How do I achieve a MySQL master-master replication in my particular setup?

I have two VPSes

1: Centos 7 x64, cPanel

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.23 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.23 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+




2: Centos6.5 x64

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.73-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+



I need to reliably mirror a database from server 1 to server 2 and keep it updated in real time. How do I achieve this in my setup?
A good tutorial with steps to follow is what I am after as I have no idea how or where even to start.

thanks in advance
Avatar of arnold
arnold
Flag of United States of America image
There are sever examples including I believe in MySQL.com if needed can locate post link, look at circular replication.  The important part (most)  in a master/master replication is to make sure that there is absolutely, positively, never, ever, ever a possibility that a new record created in any of the replicating database table could have been created on any other replication member.

This is achieved through the my.cnf configuration dealing with offset, and index.
The point being  in a two node replication, one replication node creates only even I'd columns, while the other only odds.
Since the possibility exist that once one sets up master/master it is possible that it could expand
The mechanism deals with each node starts their count increment as 10. Node1 starts at 1, node2 starts at 2, etc.

The complicity in the replication and monitoring of replication status as well as mamaging tge binary logs to avoid running out of space. Errors have to be corrected as soon as they occur delays in remedy could have adverse impact on .........
Avatar of arnold
arnold
Flag of United States of America image
Here is a link to a master/master circular replication explanation.  Consider your cluster is made up of a single node.

 http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-replication-multi-master.html

There is some complexity involved transitioning from a master/slave to master/master and that deals with timing and having info on what the current highest number of ID columns is.  Deals with forcing the next issued numbers to be unique by making the offset high enough as not having been issued. The difficulty this depends on what/how the ID column was defined .

http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks will try and let you know.
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks Tomas. But I can't use MariaDB as server 1 runs cPanel and server 2 runs a proprietary accounting software called Mexal Passepartout. Both need mySQL. :(