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