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;
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.
