Multi-master setup, BNL-PDSF

Current multi-master FC setup scheme:

1. one master server at BNL + two local slaves attached to it;
2. one master server at PDSF + one local slave attached to it;
3. both BNL FC master and PDSF FC master are slaves to each other;

BNL master : fc1.star.bnl.gov:3336
PDSF master : pdsfdb05.nersc.gov:3336

MySQL config details :

• BNL master has the following extra lines in my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 1
  • log-bin = mysql.bin
  • log-slave-updates
  • expire_logs_days = 7
  • binlog-ignore-db = mysql
  • binlog-ignore-db = test
• PDSF master has the following extra lines in my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 2
  • log-bin = mysql.bin
  • log-slave-updates
  • expire_logs_days = 7
  • binlog-ignore-db = mysql
  • binlog-ignore-db = test

All local slaves should have "read-only" parameter in my.cnf to avoid occasional writes to slaves.

Privileged user, for replication :

BNL:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'fcrepl'@'%.nersc.gov' IDENTIFIED BY 'starreplica';
FLUSH PRIVILEGES;

PDSF:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'fcrepl'@'%.bnl.gov' IDENTIFIED BY 'starreplica';
FLUSH PRIVILEGES;

server-id: at STAR, we assign mysql server id using the following scheme: [xx.yy].[port number], where xx.yy is the last half of server IP address. So, for server running at [ip-130.100.23.45]:[port-3306], server id is "23453306".

SETUP PROCESS FOR LBNL

1. copy FC snapshot from dbuser@alpha.star.bnl.gov:/db01/zrm-backup/daily-fc-logical/20091015135118/ to pdsf;
2. install mysql 5.0.77 and equip it with /etc/init.d/mysqld_multi script (see attached file);
3. configure my.cnf for one master running at port 3336, and one slave running at port 3306 (example my.cnf is attached). Don't forget to change server-id to some other unique numeric id - it should not overlap with existing STAR ids;
4. start master at pdsfdb05.nersc.gov:3336 (command: "service mysqld_multi start 3336"), and load snapshot .sql file; DO NOT START SLAVE MODE YET!
5. grant privileges to BNL/local replication user (fcrepl, see above);
6. issue "GRANT SHUTDOWN ON *.* TO 'myadmin'@'localhost' IDENTIFIED BY PASSWORD '*20843655CB400AA52C42FF5552B9BA26DE401B4B' " to enable automatic mysqld_multi script access;
7. start slave at pdsfdb05.nersc.gov:3306, and load same snapshot .sql file, run "CHANGE MASTER TO <bla>" to setup LOCAL replication process using same "fcrepl" user;
8. issue "GRANT SHUTDOWN ON *.* TO 'myadmin'@'localhost' IDENTIFIED BY PASSWORD '*20843655CB400AA52C42FF5552B9BA26DE401B4B' ";
9. start local replication and check its state with "SHOW SLAVE STATUS\G" command;
10. start BNL->PDSF replication on master, check its state with "SHOW SLAVE STATUS\G" command;
11. stop replication ("STOP SLAVE"), and send results of "SHOW MASTER STATUS" command to Dmitry.