Re: [Netpass-users] Network Arch Opinions/Questions

[Jeff M. <jcm...@os...>]

On Tue, 2005-06-21 at 13:56 -0400, Don Rugh wrote:

> In this case, I'm thinking of running either MySQL Cluster or master- 
> slave replication, 

we ran (and are still running until we upgrade in a couple of weeks)
mysql replication. it's worked reliably. the doc all references Cluster
because thats the easiest way to scale to more than 2 servers.

here's some doc on M/S and M/M configurations....





Setup Mysql Replication (Master/Slave)
Note: Master = npw1.cit.buffalo.edu and Slave = npw2.cit.buffalo.edu
     1. Add the following line to /etc/my.cnf on npw1 right under
        [mysqld]
        
        log-bin
        
     2. Optionally you can add the following line to my.cnf on npw1 to
        excluded databases from being replicated
        
        binlog-ignore-db = database_name
        
     3. Start mysqld on npw1.
     4. Create an account on npw1 called repl and grant that user
        replication privileges
        
        mysql> GRANT REPLICATION SLAVE ON *.*
        -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'password';
        
     5. Run the following cmd on npw1
        
        mysql> FLUSH TABLES WITH READ LOCK;
        
     6. Run the following cmd on npw1, and record the logfile name and
        logfile position
        
        mysql> SHOW MASTER STATUS
        
     7. On npw1 in a seperate terminal do a mysqldump of the databases
        you wish to replicate, then copy the files over to npw2. Dont
        exit out of the mysql session where the READ LOCK cmd was issued
        becuase the lock will be released. 
     8. Release the READ LOCK on npw1
        
        mysql> UNLOCK TABLES;
        
     9. Start mysqld on npw2 
    10. Restore the databases from the dump file on npw2
    11. Shutdown mysqld server on npw2
    12. Add the following lines to /etc/my.cnf right under [mysqld], on
        npw2
        
        master-host = npw1.cit.buffalo.edu
        master-user = repl
        master-password = password
        master-port = 3306
        
        
    13. Start the mysqld server on npw2
    14. Run the following cmd on npw2 to tell mysql what logfile and
        what position in the logfile to start replicating from. The
        logfile name and logfile position were obtained in a prior step
        by running the "SHOW MASTER STATUS" command on npw1.
        
        mysql> CHANGE MASTER TO
        MASTER_LOG_FILE='recorded_log_file_name',
        MASTER_LOG_POS='recorded_log_position'
        
    15. Stop and Start the slave mysql server on npw2
        
        mysql> SLAVE STOP;
        mysql> SLAVE START;
        
    16. Test and make sure it works




Setup Mysql Replication (Master/Master)



Note: The following procedure assumes that the slave mysql server is a
fresh install without any entries having been into the database.
Note: Master = npw1.cit.buffalo.edu and Slave (2nd Master server) =
npw2.cit.buffalo.edu


     1. Do all the steps in the previous section "Setup Mysql
        Replication (Master/Slave)"
     2. Shutdown mysql on npw2 and add the following line under [mysqld]
        in /etc/my.cnf
        
        log-bin
        
     3. Start mysqld on npw2
     4. Create an account on npw2 called repl and grant that user
        replication privileges
        
        mysql> GRANT REPLICATION SLAVE ON *.*
        -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'password';
        
     5. Run the following cmd on npw2, and record the logfile name and
        logfile position
        
        mysql> SHOW MASTER STATUS
        
     6. Shutdown mysqld on npw1
     7. Add the following lines to /etc/my.cnf right under [mysqld], on
        npw1
        
        master-host = npw2.cit.buffalo.edu
        master-user = repl
        master-password = password
        master-port = 3306
        
        
     8. Start mysqld on npw1
     9. Run the following cmd on npw1 to tell mysql what logfile and
        what position in the logfile to start replicating from. The
        logfile name and logfile position were obtained in a prior step
        by running the "SHOW MASTER STATUS" command on npw2.
        
        mysql> CHANGE MASTER TO
        MASTER_LOG_FILE='recorded_log_file_name',
        MASTER_LOG_POS='recorded_log_position'
        
    10. Stop and Start the slave mysql server on npw1
        
        mysql> SLAVE STOP;
        mysql> SLAVE START;
        
    11. Test and make sure it works