MySQL replication of Asterisk Users
I've been asked to do some SQL configuration for one of the VoIP operator. The goal was to distribute the GUI users via all servers. I am not an expert in SQL area but after this task I've started to like DB programming :) .
First thing that came to my mind was: "Is there a synchronization feature in MySQL ?" I checked google and after few pages I realized I need the MySQL Cluster to do the full sync (both way exchange). But the goal was to have the master-slave architecture. It means the current db is distributed via other servers and it won't be updated in other places. So I found a MySQL Replication feature that fits to my need. ;)
This tutorial shows the configuration the master and slave MySQL server.
1. Master Server Configuration:
We need enable the network access to server:
/etc/my.cnf
#skip-networking
#bind-address = 127.0.0.1
Furthermore we specify DB that has to be replicated and the log file that use Slave to update its own DB:
binlog-do-db=asteriskuser
log-bin=/var/log/mysql/mysql-bin.log
and the server id:
server-id=1
After this changes we restart the MySQL server:
/etc/init.d/mysql restart
Now we have to login to mysql and grant privileges for a replication user:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
We specify the db which we want to use and lock it:
USE asteriskuser
FLUSH TABLES WITH READ LOCK;
We need some information for the slave configuration which are here:
SHOW MASTER STATUS;
We will use the details about the bin file (mysql-bin.000001) and position (98).
Afterwords we have to copy the master's server db to slave. We will simply dump it.
mysqldump -u root -p --opt asteriskuser > asteriskuser_db.sql
scp asteriskuser_db.sql 'user'@'slave_server':/'location'/
To finish Master config we unblock the db:
UNLOCK TABLES;
OK, Master server is DONE.
2. Slave(s) server configuration:
First again we have to edit mysql config file with detail about master:
/etc/my.cnf
server-id=2
master-host=master_server_ip_address
master-user=repl
master-password=password
master-connect-retry=60
replication-do-db=asteriskuser
Restart mysql server.
In MySQL create a 'asteriskuser' db:
CREATE DATABASE asteriskuser;
Upload the sql dump file to db:
mysql -u root -p asteriskuser < asteriskuser_db.sql
In MySQL we set some info:
SLAVE STOP;
CHANGE MASTERTO MASTER_HOST='master_server_ip_address', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin-000001', MASTER_LOG_POS='98';
SLAVE START;
SHOW SLAVE STATUS\G;
Last command show you the status of slave client:
Slave_IO_State: Waiting for master to send event
If it show:
Slave_IO_State: Connecting to master
Make sure your firewall does not block port 3306.
(iptables -I INPUT -p tcp --dport 3306 -j ACCEPT)
I hope this short tutorial give you a basic information about the MySQL Replication.