#MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

#MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

#MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

#MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

#MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

#MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

#MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

#MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

#MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

#MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

#MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

#MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

#MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

#MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

1. Setup the slave to replicate from one master. We'll call this the current master.
2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
4. Wait until the relay log is empty.
5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
8. Restart the slave threads using START SLAVE.
9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

• Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python

#MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key