mysql replication/cluster

MySQL replication is an important feature provide by mysql not only for database backup/replication but also for load balancing. For example one can use the Database1 for production purpose and the Database2 for billing purpose. In other case all the Insert/Update Query will executed on Database 1 and Select Query will execute on Database2.

MySQL Replication is very easy to configure.

How To Replicate MySQL SERVER?

First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

vi /etc/my.cnf

#skip-networking

#bind-address = 127.0.0.1

Master Server Configaration

Then put the following entries in the /etc/my.cnf of the Master Server

[mysqld]

log-bin=mysql-bin

binlog-do-db=<DATABASE NAME>

server-id=1

Run the following command in the Master server

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

Chk the result

U need ti copy the database server from master server to slave server using mysqldump or SQLYOG or Operting System CP command.

mysqldump -u<username> --password=<password> <databasename> <tablename> <filename with path>

mysqldump --lock-all-tables -uroot --password=dialnet asterisk test_table > /home/asterisk.sql

or Shut Down the Master Database and use CP command to copy the DATABASE to the SLAVE

Then look up for the permission of the Files and Folder in the Slave Server

Slave Server Configuration

Put the Following entries in your Slave server Configuration File /etc/my.cnf or my.ini file

[mysqld]

server-id=2

master-host=10.201.1.120

master-user=root

master-password=dialnet

master-connect-retry=60

replicate-do-db=MYCALL

Restart MySQL server in SLAVE

then use the following command

Thats all...