#asteriskuser

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)