MYSQL database for Voipswitch replication

In this article, I will show you how to set up replication between two MySql servers for Voipswitch database purpose. Database replication is very useful for server failure issues. If the main server comes down you can easily switch to replicated one and have a full database on it. Ok, let’s get started.

Master Server

Master MySql server settings are located in my.ini file depend on server distribution and installation on Windows should be located in C:\Program Files\MySQL\MySQL Server 5.1 for higher MySql versions location is C:\ProgramData\MySQL\MySQL Server 5.7 as I mention before all depend on installation path that you choose it.  Master settings for replication are:

log-bin
expire_logs_days = 3
binlog-do-db=voipswitch  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test
replicate-ignore-table=voipswitch.registered_users,voipswitch.history,voipswitch.voipbox_history,voipswitch.servicelog,voipswitch.currentcalls
# input tables that should be ignored for replication
server-id=1

The setting should be in the file section [mysqld]. Save the settings and restart MySql server. Now we can check the master status:

show master status;

mysql_voipswitch_replication_01_in4system

Slave Server

On Slave server we need to create replication user by MySql command:

grant replication slave on *.* to 'replication'@master_IP identified by 'new_password';

Please change the master IP and some strong password.

Slave server my.ini config should be:

server-id=2

master-host = ip # put the IP of the master server
master-user = replication # created user for replication
master-password = replication_password # password for replication user
master-port = 3306

replicate-do-db=voipswitch  # input the database which should be replicated
replicate-ignore-db=mysql            # input the database that should be ignored for replication
replicate-ignore-db=test
replicate-ignore-table=voipswitch.registered_users,voipswitch.history,voipswitch.voipbox_history,voipswitch.servicelog,voipswitch.currentcalls #tables that will be ignored for replicaion

auto_increment_increment = 2
auto_increment_offset = 2
relay_log_space_limit=4G
relay-log=in4system-bin.000001  # master relay log file taken from the master server status
expire_logs_days = 3
master-connect-retry=60

After you save the settings you need to restart the MySql server. Now we can check the replication status:

show slave status \G

You should see complete info about the slave server. If you don’t see any errors the job is done.

Troubleshooting

If you check the slave server status and you see some errors  with replication that means some of the tables didn’t replicate  correctly you can stop the slave:

stop slave;

Update the relay-log file from the master with new master_log_pos:

CHANGE MASTER TO master_host='master_IP', master_port=3306, master_user='replication',master_password='replication_password', master_log_file='in4system-bin.000024', master_log_pos=86407316;

After you can start slave again:

start slave;

And check the status if there are no more errors.

show slave status \G

I hope this will make a view on how to set up a replication server for Voipswitch database. Please make a comment for any queries or doubts. Enjoin.

,

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu