Next Generation Telephony Systems and Services

Table of Contents

• Replication Overview
• Setting up Dual Server
  • Replication Setup
  • Checking Replication
  • Configuring Billing
• Maintenance of Dual Server Setup
• Troubleshooting Dual Server Setup
  • Case 1: Reports are not updating for some time
  • Case 2: When clicking any Reports, message is displayed "Reports Server is Unavailable"

Replication Overview

Setting up Dual Server

Replication Setup

1. Edit the my.cnf file on the master server:

   --master my.cnf:
   
   [mysqld]
   server-id = 1
   log-bin
   replicate-do-db = billing
   

2. Stop MySQL server on master
3. Copy all Billing database tables from MySQL home directory billing/ subdirectory
4. Start MySQL server. From this point forward there will be no downtime and primary server works as Replication Master.
5. On the master server grant replication permission to slave host:

   mysql> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO repl@"slave.host" IDENTIFIED BY 'password';
   

6. Pack copied directory and send to Slave server
7. Stop MySQL server on slave
8. Unpack and copy (by replacing) all files from master server into same position of slave server
9. Make sure file owner is MySQL (normally mysql:mysql)
10. Edit the my.cnf file on the slave:

    -slave my.cnf:
    
    [mysqld]
    server-id = 2
    replicate-do-db = billing
    

11. Start mysql on slave.
12. Tell slave how to connect to master and restart slave process:

    mysql> CHANGE MASTER to MASTER_HOST='master.host’,MASTER_PORT=3306,
    MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='',MASTER_LOG_POS=4;
    
    mysql> STOP SLAVE; #--If already started
    
    mysql> START SLAVE;
    

13. The slave should now be running
14. Grant access to Master to read reports from slave (required permissions: select, drop, create):

    mysql> GRANT SELECT, DROP, CREATE ON billing.* to reportreader@"master.host" IDENTIFIED BY 'newpassword'; 
    

Checking Replication

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.host
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 221307609
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 221307652
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: billing
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 221307609
            Relay_Log_Space: 221307652
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
              .............
      Seconds_Behind_Master: 0

Configuring Billing

billing.ini:

/* ==== DATABASE ==== */
$use_slave_db = 1;

$slavedbhost = "slave.host";
$slavedbuser = "reportreader";
$slavedbpass = "newpassword";
$slavedbname = "billing";

Maintenance of Dual Server Setup

• Check if replication is running by issing SHOW SLAVE STATUS\G query.
• Slave maintains log of queries between fetching them from master and executing on its own server. This log is not purged automatically and can grow very fast and take a lot of disk space. You should regulary check these files and remove older ones. DO NOT remove current file(s) which are in use by Slave. As a rule of thumb remove log files ONLY if Replication is running fine and there is no significant value of Seconds_Behind_Master. In this case it is usually safe to remove all but 2 most recent files. As extra precausion check file modification times before deleting. Make sure you delete oldest files only. Files are located in MySQL data directory and look like following:

  -rw-rw----  1 mysql  mysql  254199046 Mar 31 05:58 localhost-relay-bin.000002
  -rw-rw----  1 mysql  mysql         29 Mar 30 13:29 localhost-relay-bin.index
  

  NOTE: Do Not remove or modify relay-bin.index file!
• Similar log is maintained on master. It is called bin-log. It keeps records of all queries executed on master. Slave reads this log and repeats exactly same steps. It is also not cleaned up automatically. Same guidelines as with cleaning Slave's relay-log apply. Files are located in MySQL data directory and look like following:

  -rw-rw----  1 mysql  mysql  299782934 Mar 30 17:18 mysql-bin.000001
  -rw-rw----  1 mysql  mysql  265698071 Mar 31 11:18 mysql-bin.000002
  -rw-rw----  1 mysql  mysql         38 Mar 30 17:12 mysql-bin.index
  

  NOTE: Do Not remove or modify mysql-bin.index file!

Troubleshooting Dual Server Setup

• Data in Reports (Call detail, Traffic, etc.) has not updated, i.e. there is no data for most recent time (minutes, hours) despite the fact that traffic is present. This indicates that replication is broken and needs to be restored.
• When attempting to generate reports, the message "Reports Server is unavailable" appears. This shows that master server is unable to connect to slave due to one of the following:
  • Slave server has stopped or crashed;
  • There is no granted privilege for master to select from slave;

billing.ini:

$use_slave_db = 1;

Case 1: Reports are not updating for some time

1. Check Slave status with SHOW SLAVE STATUS\G query. Depending on status output, there can be several reasons:
   • Slave is lagging behind master:

             Slave_IO_Running: Yes, 
             Slave_SQL_Running: Yes
             Seconds_Behind_Master: (some very large number)
     		

     Appearently Slave is stuck with execution of some very large query (maybe report spanning over several months). Check processlist of Slave server:

     		mysql> SHOW PROCESSLIST;
     		

     If you notice some query which is executing abnormally long time, it is probably good and safe to kill it by: kill <process_id>
   • Slave has stopped its SQL thread:

             Slave_IO_Running: Yes, 
             Slave_SQL_Running: No
             Last_Error: Error '(error description follows...)
     		

     SQL Thread has stopped due to some SQL error. This may happen if data between master and slave is not consistent. The causes can be: manual data modification on slave, errors on master, crashes of master tables etc... If the Last_Error description has following format: Duplicate entry '1449968' for key 1'... it shows that record which we trying to insert already exists on slave. If you are sure of it, you can possibly tell slave to skip this particular query and start again:

     		mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;
     		mysql> START SLAVE;
     		mysql> SHOW SLAVE STATUS\G
     		

     You can repeat this procedure several times until slave is up and running. However there are case when it cannot help. That is indication of serious problem of current replcation setup and may require advanced debugging or setting up new replcation. Please see MySQL documentation for Replication troubleshooting.
   • Slave has stopped its I/O thread:

             Slave_IO_Running: No, 
             Slave_SQL_Running: Yes
     		

     Slave has stopped Input/Output thread due to being unable to read Masters bin log. This mostly happens when master bin-log files get corrupted by loss of power in master. This is difficult issue to fix and needs advanced experience. Please see MySQL documentation for Replication troubleshooting.

Case 2: When clicking any Reports, message is displayed "Reports Server is Unavailable"

1. Check if slave server is running, and if MySQL is running
2. Check connection to slave MySQL server from master using credentials supplied in billing.ini file:

   	master# mysql -h slave.host -u reportreader -pnewpassword billing
   	Reading table information for completion of table and column names
   	You can turn off this feature to get a quicker startup with -A
   
   	Welcome to the MySQL monitor.  Commands end with ; or \g.
   	Your MySQL connection id is 3890 to server version: 4.1.18
   
   	Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   
   	mysql> show tables; 
   	

   If access is not available, please check grants on slave and settings in masters billing.ini file
3. If there is access from command line, but web interface still shows "Reports Server is Unavailable", please enable error logging in billing.ini file:

   	billing.ini:
   
   	$report_sql_errors = 1;
   
   	$report_php_errors = 1;
   	

   and refresh web page. It will display possible error message. Possible errors are :
   • "Client does not support authentication protocol". You are trying to connect to MySQL version 4.1. or higher from pre-4.1 client. You will have to use old password scheme for this client. On slave please do:

     	mysql> SET PASSWORD FOR 'repotreader'@'master.host' = OLD_PASSWORD('newpassword');
     	

---