Skip to main content
Database Administrators

Return to Answer

added 818 characters in body
Source Link
RolandoMySQLDBA
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

##UPDATE 2012-05-07 12:22 EDT

The only reason to use auto_increment_offset/auto_increment_increment in this setup would be to do writes to all masters and reads from any master.

There is only one scenrio with this setup where you are not obligated to use auto_increment_offset/auto_increment_increment:

For any given database mydb

  • If you write to mydb on ServerA only, then read from mydb on ServerA
  • If you write to mydb on ServerB only, then read from mydb on ServerB
  • If you write to mydb on ServerC only, then read from mydb on ServerC

##UPDATE 2012-05-07 12:22 EDT

The only reason to use auto_increment_offset/auto_increment_increment in this setup would be to do writes to all masters and reads from any master.

There is only one scenrio with this setup where you are not obligated to use auto_increment_offset/auto_increment_increment:

For any given database mydb

  • If you write to mydb on ServerA only, then read from mydb on ServerA
  • If you write to mydb on ServerB only, then read from mydb on ServerB
  • If you write to mydb on ServerC only, then read from mydb on ServerC
added 396 characters in body
Source Link
RolandoMySQLDBA
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531
  • ServerA IP Address 10.1.1.20
  • ServerB IP Address 10.1.1.30
  • ServerC IP Address 10.1.1.40
  • You will create a user for replication
  • username replicator@'10.1.1.%'
  • password r3plic4t0R
  • ServerA's Master will be ServerC
  • ServerB's Master will be ServerA
  • ServerC's Master will be ServerB
  • There are no databases on the three Servers
  • There are three tables to ignore
  • db1.tb1
  • db2.tb2
  • db3.tb3
[mysqld]
server-id=20
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3

[mysqld]
server-id=30
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3

[mysqld]
server-id=40
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3
  • ServerA IP Address 10.1.1.20
  • ServerB IP Address 10.1.1.30
  • ServerC IP Address 10.1.1.40
  • You will create a user for replication
  • username replicator@'10.1.1.%'
  • password r3plic4t0R
  • ServerA's Master will be ServerC
  • ServerB's Master will be ServerA
  • ServerC's Master will be ServerB
  • There are no databases on the three Servers
[mysqld]
server-id=20
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates

[mysqld]
server-id=30
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates

[mysqld]
server-id=40
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
  • ServerA IP Address 10.1.1.20
  • ServerB IP Address 10.1.1.30
  • ServerC IP Address 10.1.1.40
  • You will create a user for replication
  • username replicator@'10.1.1.%'
  • password r3plic4t0R
  • ServerA's Master will be ServerC
  • ServerB's Master will be ServerA
  • ServerC's Master will be ServerB
  • There are no databases on the three Servers
  • There are three tables to ignore
  • db1.tb1
  • db2.tb2
  • db3.tb3
[mysqld]
server-id=20
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3

[mysqld]
server-id=30
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3

[mysqld]
server-id=40
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3
Source Link
RolandoMySQLDBA
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

For this example, let assume the following

  • ServerA IP Address 10.1.1.20
  • ServerB IP Address 10.1.1.30
  • ServerC IP Address 10.1.1.40
  • You will create a user for replication
  • username replicator@'10.1.1.%'
  • password r3plic4t0R
  • ServerA's Master will be ServerC
  • ServerB's Master will be ServerA
  • ServerC's Master will be ServerB
  • There are no databases on the three Servers

Step 01) Add these options under [mysqld] in /etc/my.cnf on ServerA

[mysqld]
server-id=20
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates

Step 02) Add these options under [mysqld] in /etc/my.cnf on ServerB

[mysqld]
server-id=30
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates

Step 03) Add these options under [mysqld] in /etc/my.cnf on ServerC

[mysqld]
server-id=40
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates

Step 05) service mysql restart on ServerA, ServerB, ServerC

Step 06) Create GRANT for replication user on ServerA, ServerB, ServerC

GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT
ON *.* to replicator@'10.1.1.%'
IDENTIFIED BY 'r3plic4t0R';

Step 07) Run SHOW MASTER STATUS; on ServerC

Step 08) Run this CHANGE MASTER TO command on ServerA

CHANGE MASTER TO
MASTER_HOST='10.1.1.40',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep07>;

Step 09) Run SHOW MASTER STATUS; on ServerA

Step 10) Run this CHANGE MASTER TO command on ServerB

CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep09>;

Step 11) Run SHOW MASTER STATUS; on ServerB

Step 12) Run this CHANGE MASTER TO command on ServerC

CHANGE MASTER TO
MASTER_HOST='10.1.1.30',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep11>;

Step 13) START SLAVE; on ServerA

Step 14) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerA

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working

Step 15) START SLAVE; on ServerB

Step 16) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerB

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working

Step 17) START SLAVE; on ServerC

Step 18) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerC

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working