MySQL Replication

From DooxSwitch wiki
Jump to: navigation, search

Configiure the MySQL DB redunduncy

In order to have the MySql DB up while one of the PBX servers is down one of the following can be done:

  • have remote DB server, both PBXs are working with the same remote MySQL (cluster?)
  • Each server has its own MySQL DB, MySQL is replicated from the activve to the slave

We will discuss the second option:

Configure MySQL replication on my.cnf

/etc/my.cnf on PBX-147 should be:

 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 user=mysql
 # Default to using old password format for compatibility with mysql 3.x
 # clients (those using the mysqlclient10 compatibility package).
 old_passwords=1
 # add for replication from here
 server-id=2
 master-host = 192.168.0.148
 master-user = replication
 master-password = slave
 master-port = 3306
 #
 log-bin
 binlog-do-db=asteriskrealtime
 #
 [mysql.server]
 user=mysql
 basedir=/var/lib
 ################################################################
 [mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

/etc/my.cnf on PBX-148 should be:

 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 user=mysql
 # Default to using old password format for compatibility with mysql 3.x
 # clients (those using the mysqlclient10 compatibility package).
 old_passwords=1
 # add for replication from here
 log-bin
 binlog-do-db=asteriskrealtime
 binlog-ignore-db=mysql
 binlog-ignore-db=test
 #
 server-id=1
 #
 master-host = 192.168.0.147
 master-user = replication
 master-password = slave
 master-port = 3306
 #
 [mysql.server]
 user=mysql
 basedir=/var/lib
 ########################################################################
 [mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

Configure MySQL permissions

on PBX-147, enter to mysql by

 mysql -p
 enter root password

permit the local "replication" user and the "replication" user from remore server

 GRANT ALL PRIVILEGES ON *.* TO 'replication'@'localhost' IDENTIFIED BY 'slave' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON `asteriskrealtime`.* TO 'replication'@'localhost';
 GRANT ALL PRIVILEGES ON *.* TO 'replication'@'192.168.0.148' IDENTIFIED BY 'slave' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON `asteriskrealtime`.* TO 'replication'@'192.168.0.148';
 commit;

on PBX-148, enter to mysql by

 mysql -p
 enter root password

permit the local "replication" user and the "replication" user from remore server

 GRANT ALL PRIVILEGES ON *.* TO 'replication'@'localhost' IDENTIFIED BY 'slave' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON `asteriskrealtime`.* TO 'replication'@'localhost';
 GRANT ALL PRIVILEGES ON *.* TO 'replication'@'192.168.0.147' IDENTIFIED BY 'slave' WITH GRANT OPTION;
 GRANT ALL PRIVILEGES ON `asteriskrealtime`.* TO 'replication'@'192.168.0.147';
 commit;


Start the MySQL replication

On server PBX-147 and PBX-148 do the following: enter to the mysql with the replication user:

 mysql -u replication -p
SLAVE STOP;
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=106;
SLAVE START;

start the replication

 start slave;

on one of the servers, copy the DB to start from the same point:

 load data from master;

check status on each server:

 show master status;
 show slave status\G;

if you want to start replication when stop working:

 reset slave;

debug using:

 cat /var/log/mysqld.log 
 or
 tail -f /var/log/mysqld.log