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
badwolfffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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 .........
0
arnoldCommented:
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
0
badwolfffAuthor Commented:
Thanks will try and let you know.
0
Tomas Helgi JohannssonCommented:
Hi!

You could also take a look at the MariaDB Galera Cluster. It's easy to setup.
https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster/
https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-cluster/
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

To upgrade from MySQL 5.1 on Centos 6 to MariaDB 10.1 you should follow this tutorial
https://mariadb.com/blog/upgrading-mysql-51-mariadb-100-centos-6

To install Galera Cluster on Centos you can look at this
http://blog.laimbock.com/2014/07/08/howto-setup-mariadb-galera-cluster-10-on-centos/comment-page-1/

Known limitations on Galera Cluster
https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

Regards,
     Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
badwolfffAuthor Commented:
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. :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.