MySQL Master Slave Replication
MySQL Master Slave Replication is one way to spread the load across multiple servers.Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master). Its just an one-way communication (Master to Slave), Slave just receiving any event or update from Master, so if Master updated Slave will automaticly updated but if the update did on Slave ofcourse Master is won’t updated. If you want update Multiple Server simultaneously each other, you must try MySQL Master Master Replication.
Master address 192.168.10.1
Slave address 192.168.10.2
Setup Master
linux:~# vi /etc/my.cnf
log-bin=mysql-bin
server-id=1
binlog-ignore-db="mysql"
* Restart MySQL Master Service
linux:~# killall mysql mysqld mysqld_safe linux:~# /usr/local/mysql/bin/mysqld_safe --user=mysql &
* Create an user on the Master server that allows replication on the Slave.
mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@"192.168.10.2" IDENTIFIED BY 'password@@@'; mysql> flush privileges; mysql> quit
* Export database that you want to put in the Slave server. (in this case, i want to export wordpress database)
linux:~# mysqldump -u root -p wordpress > wordpress.sql Enter password :
* And then lets see the Master status
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 141741 | | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> exit Bye
Note the File row and Position row for use on the Slave server.
* Copy your exported database file to the Slave server.
linux # scp database.sql 192.168.10.2:/root/