Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Note

Applies to VoipNow 3.X.X!

Every time an API request is sent or a call report is generated, the SQL server receives a query. As a result, the load on the server is affected by the number of requests and the volume of data that should be returned by the SQL server.

By default, VoipNow is able to send all the read requests to a slave node and all the inserts to the master node. Basically, every time a user wants to generate a call report for an entire month, his request will be directed to the slave server. This pattern could cause problems if the replication of the data does not happen in real time and the slave remains behind the master. The MWI and presence notifications in particular are most affected.

To avoid this kind of issues, the latency between those two nodes must be as smaller as possible. This article will help you set up a replication between two SQL servers. 

Step-by-step guide

Before taking each step, make sure that you have both PBX nodes installed as recommended in our documentation for distributed environments. Another mandatory condition is that both nodes are Online. Your interface settings should look as shown in the image below. 


 

  1. Edit MySQL config file on the master server: /etc/my.cnf. At the end of the file, add the following lines. Then save changes and restart MySQL.

    Code Block
    #replication
    expire_logs_days=30
    server-id               = 1
    log-bin=/var/lib/mysql/mysql-bin.log
    log_bin_trust_function_creators=1
    binlog_format=MIXED
  2. Add the MySQL user destined for replication with the appropriate rights. Log in on MySQL CLI and run the following command:

    Code Block
    GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replica@'%' identified by 'PYvrVSSpFj1yl'; FLUSH PRIVILEGES;
  3. Stop the MySQL service on the slave server.

  4. Go back to the master node and run the following command. In order to synchronize the folders, you will have to provide the root password for connecting via SSH on the slave server.

    Code Block
    rsync -varPe ssh  /var/lib/mysql root@<slave_ip>:/var/lib/ 
  5. Lock the database to prevent any new changes.

    Code Block
    FLUSH TABLES WITH READ LOCK;


  6. Check status on master server.

    Code Block
    mysql> show master status;
    +------------------------------+----------+--------------+------------------+
    | File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------------------+----------+--------------+------------------+
    | centos-6-b64-test-bin.000001 | 43311833 |              |                  |
    +------------------------------+----------+--------------+------------------+


  7. Again, synchronize the MySQL home directory on the slave server. Run the rsync command on the master server one more time.

    Code Block
    rsync -varPe ssh  /var/lib/mysql root@<slave_ip>:/var/lib/ 


  8. Unlock tables.

    Code Block
    UNLOCK TABLES;
    QUIT;


  9. Edit MySQL config file on the slave server: /etc/my.cnf. At the end of the file, add the following lines:  

    Code Block
    expire_logs_days=30
    log_bin                 = /var/lib/mysql/mysql-bin.log
    server-id               = 2
    max_allowed_packet=128M
    binlog_format=MIXED
    log_bin_trust_function_creators=1
    


  10. Start MySQL service on the slave server, connect to MySQL CLI and run the following command:

    Code Block
    slave stop;


  11.  Set the master parameters on the slave server.

    Code Block
    change master to master_host='10.150.12.32', master_user='replica', master_password='PYvrVSSpFj1yl', MASTER_LOG_FILE='centos-6-b64-test-bin.000001', MASTER_LOG_POS=43311833;
    Code Block
    start slave;

     

    Note

    The MASTER_LOG_POS should be Position from the previous step.


  12. Check the slave status.

    Code Block
    mysql> show slave status\G;
    *************************** 1. row ***************************
    ..
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ..
    Exec_Master_Log_Pos: 43315418
    ..

 

Info

Parameters: Slave_IO_Running and Slave_SQL_Running should be Yes. If either one is with No, then it's bad.

Content by Label
showLabelsfalse
max5
spaces4PSAKB
sortmodified
showSpacefalse
reversetrue
typepage
labelsSQL replication MySQL PBX nodes master slave

Page properties
hiddentrue
Related issues 

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 4.0 International.