Blog

Server A

Our server is fresh meet, so will install MySQL:

# sudo yum install mysql-server

Let's edit the MySQL configuration file with our lovely editor - nano (you can use vi or what ever editor you like):

# sudo nano /etc/my.cnf

We will add the next config snippet to the pre-installed configuration (under [mysqld] section):

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 1 

The trick here is to avoid auto increment conflict, therefor each master have its own series of increment. Server A will start from 1 (auto-increment-offset variable) and increase by 2 (auto-increment-increment), means odd number (1, 3, 5,...). The second server. If you have more then two server, increase the auto-increment-increment variable accordingly.

Let's start the MySQL server or restart if you update running one (restart works for both cases):

# sudo service mysqld restart

Because this is fresh installation, we declare admin password:

# sudo mysqladmin -u root password 'verystrongpass'

Let's login to our MySQL server to declare the Master-Slave configuration:

# mysql -u root -p

In the MySQL console we should first configure the user that access to the replica:

> CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';

After that we will give the replica user (replicator) permission to access from the master:

> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Then will show details about Server A master status, for further use:

> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 495 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Pay Attention! mysql-bin.000008495 - will be used on Server B slave configuration.

Server B

After configure Server A, we will do almost the same for Server B.

Server B is also fresh meet and we will install MySQL: 

# sudo yum install mysql-server

Let's add the MySQL configuration snippet to declare the replication settings (under [mysqld] section)

# sudo nano /etc/my.cnf
#replica settings
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 2

Now let's restart (or start) the server and declare MySQL admin (root) password:

# sudo service mysqld restart
# sudo mysqladmin -u root password 'verystrongpass'

Let's login to our MySQL server to declare the Master-Slave configuration:

# mysql -u root -p

In the MySQL console we should first configure the user that access to the replica:

> CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';

After that we will give the replica user (replicator) permission to access from the master:

> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Now we should add declare Server A as the Master of Server B.

We need to stop to receive any binary transactions from the master to be able to declare new one, so, we are stopping any slave operations:

> SLAVE STOP;

And now for the magic trick, let's declare our master (Server A):

> CHANGE MASTER TO MASTER_HOST = 'SERVER-A-IP-OR-KNOWN-HOST-NAME', 
    MASTER_USER = 'replicator', 
    MASTER_PASSWORD = ‘reprep’, 
    MASTER_LOG_FILE = ‘mysql-bin.000008’, 
    MASTER_LOG_POS = 495;

Final step - start the slave to receive transactions from our new master (Server A):

> SLAVE START;

Now we declare Server A to be the Master of Server B. We need to to the opposite. For that reason let's show the master status of Server B to declare it as Master on Server A,

From the MySQL console of Server B:

> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 507 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Pay Attention! mysql-bin.000003, 507 - will be used on Server A slave configuration.

(Back To) Server A

As we described in the last note of Server B we required to declare Server B as Master of Server A, therefor, let's login to MySQL console on Server A:

# mysql -u root -p

 After that we will configure it the same way we did on Server B (3 steps):

> SLAVE STOP;
> CHANGE MASTER TO MASTER_HOST= ‘SERVER-A-IP-OR-KNOWN-HOST-NAME’, 
    MASTER_USER = ‘replicator’,
    MASTER_PASSWORD = ‘reprep’,
    MASTER_LOG_FILE = ‘mysql-bin.000003’;
    MASTER_LOG_POS = 507;
> SLAVE START;

Now you should have 2 servers configured as Master-Master replication. 

For verification just run the next commands from Server A MySQL console: 

> CREATE DATABASE testrep;
> use testrep;
> CREATE TABLE reptable (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`));
> INSERT INTO reptable (name) VALUES ('BillRun.net');
> INSERT INTO reptable (name) VALUES ('MySQL Replication');
> SELECT * FROM reptable;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | BillRun.net |
| 3 | MySQL Replication |
+----+-------------------+
2 rows in set (0.00 sec)

Pay attention that Server A add odd numbers only. It take the last value and the next in its series.

Now let's go to Server B and add more lines:

> INSERT INTO reptable (name) VALUES ('MySQL another Master');
> INSERT INTO reptable (name) VALUES ('MySQL is very cool');
> SELECT * FROM reptable;
+----+----------------------+
| id | name |
+----+----------------------+
| 1 | BillRun.net |
| 3 | MySQL Replication |
| 4 | MySQL another Master |
| 6 | MySQL is very cool |
+----+----------------------+
4 rows in set (0.00 sec)

Now you see that Server B take the last even number that is available after the last number inserted.

In case you need to enforce clear auto increment from both servers, you can insert directly the id with specific value. Nevertheless, the app should verify that it succeed to insert and take care of conflicts.

That's all fox. Next will be deal with the load balancing and web server.

SIGN UP FOR OUR NEWSLETTER NOW!

Start receiving monthly updates on new features and the latest industry trends.
Please let us know your email addresss.

DISCOVER MORE SOLUTIONS

We also offer you these services - click to learn more:

  • Number Portability Gateway (NPG)
  • Calls Generator
  • Telecom Anti-Fraud
  • And More

Read More

Download Datasheet

Find out whether you are using the best billing system available, that meets your business needs.
Please let us know your email addresss.
Invalid Input
Invalid Input

Get In Touch

 7a Derech Hashalom 

     Tel Aviv 6789208, Israel

 +1-917-7281607‎
    +353-76-6803306
    +972-58-4654666
    +44-161-3940149
    +1-914-2943161
    +972-58-5765666

 Contact Us

Company

Back To Top