Page History

...

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.

...

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

...

Before taking each step, make sure that you have both PBX SQL 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
   language text title SQL Master - /etc/my.cnf
   #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
   language text title SQL Master - SQL console
   GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replica@'%' identified by 'PYvrVSSpFj1ylpassword'; FLUSH PRIVILEGES;

   
   

   Warning
   title Password change
   Replace the password string with a custom, complex password. You can generate one using http://passwordsgenerator.net/ or a similar site.

   
   

3. Stop the MySQL service on the slave server.:

   Code Block
   language text title SQL Slave - command line
   service mysql stop

   
   

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
   language text title SQL Master - commadn line
   rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/

   
   

   Warning
   title IP replacement
   Change the <slave_ip> with your SQL slave IP address.

   
   

5. Lock the database to prevent any new changes.

   Code Block
   language text title SQL Master - SQL console
   FLUSH TABLES WITH READ LOCK;

   
   

6. Check status on master server.:

   Code Block
   language text title SQL Master - SQL console
   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
   language text title SQL Master - command line
   rsync -varPe ssh /var/lib/mysql root@<slave_ip>:/var/lib/

   
   

8. Unlock tables.

   Code Block
   language text title SQL Server - SQL console
   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
   language text title SQL Slave - /etc/my.cnf
   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
    language text title SQL Slave - SQL console
    stop slave stop;

    
    

11.  Set the master parameters on the slave server.

    Code Block
    language text title SQL Slave - SQL console
    change master to master_host='10.150.12.32<master_ip>', master_user='sclavetereplica', master_password='PYvrVSSpFj1yl<password>', MASTER_LOG_FILE='<log_file>', MASTER_LOG_POS=<position>;

    
    

    Warning
    title Enter correct configuration parameters
    Replace <master_ip> with your SQL master IP address. Replace <password> with the previously generated password. Replace <log_file> with the log file name displayed in step 6 ( centos-6-b64-test-bin.000001 ', MASTER_LOG_POS=43311833; in our example). Replace <position> with the position index displayed in step 6 ( 43311833 in our example).

    Then disable the partition update event and start the slave node:

    Code Block
    language text title SQL slave - SQL console
    alter event partition_update disable;
    Code Block
    start slave;

     

    NoteThe MASTER_LOG_POS should be Position from the previous step.

12. Check the slave status.

    Code Block
    language text title SQL slave - SQL console
    mysql> show slave status\G; *************************** 1. row *************************** .. Slave_IO_Running: Yes Slave_SQL_Running: Yes .. Exec_Master_Log_Pos: 43315418 ..

Related articles

...

...