I have a web app with 2 web servers and 2 database servers. The dbs are setup for multi master replication. (this is the primary environment)
I also have the exact same setup on a different location acting as standby, in case the primary env fails. (this is the backup env)
What I need is for the backup env to be in sync with the dbs of the primary site. However, all dbs in both environments have already replication configured.
How can I achieve my goal?
Thanks
If this is standard MySQL rather than MySQL Cluster (and from your setup I think is has to be), you can't AFAIK.
If you have hierarchical replication then you could make it work, but with multimaster you can't. The basic problem is that a slave can only have one master which is set by the CHANGE MASTER TO command.
MySQL Cluster operates in a more complex manner, you have several servers in each cluster and then the cluster can be replicated to another cluster... or something.
Not very helpful I'm afraid.
You can sync the backup servers to one of the other masters, but the backup servers would not be masters to each other until you have a problem and then you change the master slave relationships yourself.
1 Configure The Master
First we have to edit /etc/mysql/my.cnf. or /etc/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):
#skip-networking
skip-external-locking
bind-address=0.0.0.0
log-bin=mysql-bin.log
binlog-do-db=exampledb (database name)
server-id=1
Then we restart MySQL:
/etc/init.d/mysql restart
Create a user with replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;
Take dump of database(exampledb) and run command
SHOW MASTER STATUS
It will give you result like
---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183 | database name| |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec
Write down this information, we will need it later on the slave!
Then leave the MySQL shell:
quit;
2 Configure The Slave
On the slave we first have to create the sample database exampledb:
mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;
store databse dump on slave
Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf or /etc/my.cnf if file doesnot exists copy from other location
server-id=2
master-host=192.168.0.100(ip address of master host machine)
master-user=slave_user(user name)
master-password=secret (password)
master-connect-retry=60
replicate-do-db=exampledb (database name)
Then we restart MySQL:
/etc/init.d/mysql restart
Finally, we must do this:
mysql -u root -p
Enter password:
SLAVE STOP;
In the next command (still on the MySQL shell) you have to replace the values appropriately:
CHANGE MASTER TO MASTER_HOST=’master ip address’, MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
Now all that is left to do is start the slave. Still on the MySQL shell we run
START SLAVE;
quit;
That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!
