Yeah, yeah .. I know, it’s weekend. But I usually can think much better when no one is rattling my cage. So I had another look at my replication problems.
- Don’t you never ever change InnoDB settings when migrating between hardware,
because InnoDB is rather sensitive regarding those parameters. - When you’re setting up the replication (don’t ask me why) and copying over the database to the second replication partner, be aware if you’re using wild cards you’re gonna get seriously bitten in the back.
Now, let’s look at the constellation.
As you can see on the graph above (hah, sometimes Visio is rather useful 😛 ), we do have two MySQL nodes, each serving as master (as in we’re doing “normal” master-master replication).
Here’s what we’re gonna do first:
- Setup the user mysql_repl for mysql%.home.barfoo.org, granting REPLICATION SLAVE.
- Setup the user mysql_slave for mysql1.home.barfoo.org and mysql2.home.barfoo.org, also granting REPLICATION SLAVE.
Afterwards, we’re gonna copy the mysql database (either via tar and scp, or just via rssh — which is rsync via ssh) to both nodes.
|
1 2 3 4 5 6 |
mysql1> GRANT REPLICATION SLAVE ON *.* TO 'mysql_slave'@'mysql1.home.barfoo.org' IDENTIFIED BY 'hans'; mysql1> GRANT REPLICATION SLAVE ON *.* TO 'mysql_slave'@'mysql2.home.barfoo.org' IDENTIFIED BY 'hans'; mysql1> GRANT REPLICATION SLAVE ON *.* TO 'mysql_repl'@'mysql%.home.barfoo.org' IDENTIFIED BY 'hans'; |
Now, then lets copy the database over to the second node, and start the MySQL daemon there too.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql1> show master statusG; *************************** 1. row *************************** File: binlog.000001 Position: 354 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) ERROR: No query specified mysql2> show master statusG; *************************** 1. row *************************** File: binlog.000005 Position: 354 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) ERROR: No query specified |
Ok, we need these statements to start the replication.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
mysql1> CHANGE MASTER TO MASTER_HOST="mysql2.home.barfoo.org", MASTER_PORT=3306, MASTER_USER="mysql_slave", MASTER_PASSWORD='hans', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=354; mysql1> START SLAVE; mysql1> SHOW SLAVE STATUSG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql2.home.barfoo.org Master_User: mysql_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000005 Read_Master_Log_Pos: 354 Relay_Log_File: relay.000002 Relay_Log_Pos: 232 Relay_Master_Log_File: binlog.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 354 Relay_Log_Space: 232 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) ERROR: No query specified |
As you can see, the replication started without errors and didn’t fail. The log also confirms this:
|
1 2 3 4 5 6 |
090216 9:08:30 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000005' at position 354, relay log '/var/lib/mysql/slave3/relay.000001' position: 4 090216 9:08:30 [Note] Slave I/O thread: connected to master 'mysql_slave@mysql2.home.barfoo.org:3306', replication started in log 'binlog.000005' at position 354 |
Now then, let’s try using the other replication user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
mysql1> STOP SLAVE; mysql1> RESET SLAVE; mysql1> CHANGE MASTER TO MASTER_HOST="mysql2.home.barfoo.org", MASTER_PORT=3306, MASTER_USER="mysql_repl", MASTER_PASSWORD='hans', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=354; mysql1> START SLAVE; mysql1> SHOW SLAVE STATUSG; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: mysql2.home.barfoo.org Master_User: mysql_repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 354 Relay_Log_File: relay.000001 Relay_Log_Pos: 98 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 354 Relay_Log_Space: 98 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) ERROR: No query specified |
Now, again the log tells us why:
|
1 2 3 4 |
090216 9:30:01 [ERROR] Slave I/O thread: error connecting to master 'mysql_repl@mysql2.home.barfoo.org:3306': Error: 'Access denied for user 'mysql_repl'@'mysql2.home.barfoo.org' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400 |
At this point, I don’t have the slightest clue, why MySQL is behaving this way. I’m completely out of ideas, as to why so I’m just gonna do it using what I described in turn #1.