I recently posted about building MySQL & related forks from source just to test the process and a geeky kind of fun.
This is all currently just for demo and evaluation purposes. If you are eager for a production ready solution with circular replication check out the link below first about the tungsten-replicator.
Now I will set up circular replication across these different server environments. Why?
- Confirm how easy it can be done if it can be done.
- Biggest problem is auto-increment and I want 3 servers not just 2.
- Of course the real test are more with 5.6 and future releases.
- I expect errors because of different features.
- I am curious how often I run across this.
First I edited the related configuration file for this server. My source post references more if you need more information.
I edited the following:
- server-id = 1
- # Uncomment the following if you want to log updates
- log-bin=/var/lib/oracle_mysql/oracle_mysql-bin
- # binary logging format - mixed recommended
- binlog_format=mixed
> +------------+
| VERSION() |
+------------+
| 5.5.31-log |
+------------+
>show master status\G
*************************** 1. row ***************************
File: oracle_mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
SET GLOBAL auto_increment_offset=1;
SET GLOBAL auto_increment_increment=2;
> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
+--------------------------+-------+- server-id = 2
- log-bin=percona_mysql-bin
- binlog_format=mixed
>+------------+| VERSION() |+------------+| 5.5.30-log |+------------+
> show master status\G
*************************** 1. row ***************************
File: percona_mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)
> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 2 |
+--------------------------+-------+
CHANGE
MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='',
MASTER_PORT=3309,
MASTER_LOG_FILE='oracle_mysql-bin.000001',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=10;
mysql_yoda> start slave;
> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3309
Connect_Retry: 10
Master_Log_File: oracle_mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: percona-relay-bin.000002
Relay_Log_Pos: 260
Relay_Master_Log_File: oracle_mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- server-id = 3
- log-bin=maria_mysql-bin
- binlog_format=mixed
>+--------------------+| VERSION() |+--------------------+| 5.5.30-MariaDB-log |+--------------------+
> show master status\G
*************************** 1. row ***************************
File: maria_mysql-bin.000001
Position: 245
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
CHANGE
MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='',
MASTER_PORT=3307,
MASTER_LOG_FILE='percona_mysql-bin.000001',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=10;
> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: percona_mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mariadb-relay.000002
Relay_Log_Pos: 399
Relay_Master_Log_File: percona_mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
So this is a relayed replication chain at the moment and I was looking to do a circular replication.
Now if case you have not realized as of yet, offsetting the auto_increment values across 3 servers does not make much sense.
To complete the circle I am going to first just create a circle between MySQL & Percona. The Percona database will also replicate to MariaDB.
CHANGE
MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='',
MASTER_PORT=3307,
MASTER_LOG_FILE='percona_mysql-bin.000001',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=10;
> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: percona_mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: oracle_mysql-relay.000002
Relay_Log_Pos: 261
Relay_Master_Log_File: percona_mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replication is easy to set up with servers that have no traffic. So to start, I wanted to confirm that everything actually worked, even though the status showed positive results.
oracle_mysql>create database teamwork;
oracle_mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| teamwork |
| test |
+--------------------+
percona>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| teamwork |
| test |
+--------------------+
MariaDB>show databases;
+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+
Trouble already. We can see that the Oracle MySQL replicated to the Percona instance just fine.
So lets test this again.
percona>create database community;
MariaDB>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| community |
| mysql |
| performance_schema |
| test |
+--------------------+
oracle_mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| community |
| mysql |
| performance_schema |
| teamwork |
| test |
+--------------------+
So ironically MariaDB worked well with Percona and accepted the community when it did not accept teamwork that originated via Oracle_mysql. It should be noted that Oracle_mysql did also accept the community work from Percona.
So can we fix it and what is the problem?
Well I could just wait and test the MariaDB 10.0 version and allow it to use multi-master.
That might be a solution because I could have circular replication with Oracle_mysql & Percona and then use MariDB to keep them both in check by having data replicated to it and then use it for reporting. Yet, that could cause issues as well.
So what can be done to make everything happy amongst them all?
So first as a test, I will move Maria's master from Percona to MySQL.
CHANGE
MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='',
MASTER_PORT=3309,
MASTER_LOG_FILE='oracle_mysql-bin.000001',
MASTER_LOG_POS=708,
MASTER_CONNECT_RETRY=10;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
oracle_mysql>use community;
oracle_mysql>CREATE TABLE `tab1` (
-> `col1` int(11)
-> ) ;
.... After the percona insert below...
oracle_mysql>select * from tab1;
+------+
| col1 |
+------+
| 2 |
| 3 |
| NULL |
| 2 |
| 3 |
| 1 |
+------+
6 rows
percona>use community;
percona>INSERT INTO tab1 VALUE (2),(3),(NULL),(2),(3),(1);
Query OK, 6 rows affected (0.06 sec)
MariaDB>use community;
MariaDB>select * from tab1;
Empty set (0.00 sec)
So the circular replication works as long as I use MySQL for all writes. Once I write to Percona it fails to replicate to MariaDB via Oracle_mysql.
So time to dig into the logs....
# ./mysqlbinlog /var/lib/percona/percona-relay-bin.000008
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `tab1` (
`col1` int(11)
)
/*!*/;
DELIMITER ;
# End of log file
# ./mysqlbinlog /var/lib/percona/percona_mysql-bin.000001
use `community`/*!*/;
SET TIMESTAMP=1367775597/*!*/;
INSERT INTO tab1 VALUE (2),(3),(NULL),(2),(3),(1)
/*!*/;
# at 619
#130505 17:39:57 server id 2 end_log_pos 646 Xid = 111
COMMIT/*!*/;
DELIMITER ;
# End of log file
# ./mysqlbinlog /var/lib/oracle_mysql/oracle_mysql-relay.000002
use `community`/*!*/;
SET TIMESTAMP=1367775597/*!*/;
INSERT INTO tab1 VALUE (2),(3),(NULL),(2),(3),(1)
/*!*/;
# at 461
#130505 17:39:57 server id 2 end_log_pos 646 Xid = 111
COMMIT/*!*/;
DELIMITER ;
# End of log file
# ./mysqlbinlog /var/lib/oracle_mysql/oracle_mysql-bin.000003
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `tab1` (
`col1` int(11)
)
/*!*/;
DELIMITER ;
# End of log file
# ./mysqlbinlog /var/lib/mariadb/mariadb-relay.000002SET @@session.collation_database=DEFAULT/*!*/;CREATE TABLE `tab1` (`col1` int(11) )/*!*/;DELIMITER ;# End of log file
As you can see from the log data, with this current set up you will get circular replication and a slave BUT you will get a valid slave if you only write to ONE master. You can move that slave easily if the master crashed though. People often look to circular replication as the answer, this shows that it can help but does not solve everything. Again if you are eager for a production ready solution with circular replication check out the link below first about the tungsten-replicator.
For every action there is a consequence, what goes around comes around.
Karma will teach us all something, whether we like it or not.
