easy mysql replication setup and troubleshooting
DESCRIPTION
MySQL Replication 101 From Percona Live 2014TRANSCRIPT
![Page 1: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/1.jpg)
Easy MySQL ReplicationSetup and Troubleshooting
Bob BurgessSalesforce ExactTarget Marketing Cloud (Radian6)
![Page 2: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/2.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 3: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/3.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 4: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/4.jpg)
Slides
• I will post these slides on the conference site and slideshare
• No need to copy them down
![Page 5: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/5.jpg)
Who Am I?
• MySQL user since 2007• Radian6: Social Media Monitoring• Now part of
Salesforce ExactTarget Marketing Cloud• Large Data Set• Dozens of Databases • Replication is CRUCIAL for us
![Page 6: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/6.jpg)
Why Am I Doing This?
• Replication isn’t hard• Show simplest replication case• Share how we deal with common problems
![Page 7: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/7.jpg)
Our First Replication
• Set up a separate database for running reports, to separate back-end from user-facing activity
• Alexander Rubin of MySQL Support in 2008
![Page 8: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/8.jpg)
What is “Replication”?
![Page 9: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/9.jpg)
Why Replication?
• Database Failure• Share Read Traffic (Scale-Out)• Reporting/Analytics Databases• Network Load• Backups
![Page 10: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/10.jpg)
Replication Compared
• DRBD– Block-level– Replica is unusable until switched to
• Galera / Percona Cluster– More complicated– Not WAN-Friendly for High Transaction Rate
• Tungsten Replicator• Others..?
![Page 11: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/11.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 12: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/12.jpg)
Replication Described
Master Replica
![Page 13: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/13.jpg)
Replication Described
Master
table1table2
mysql
Update table1 set...Insert into table2...
Binlogs:•Events in commit order•Contains timestamps and auto-increment IDs
client
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
![Page 14: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/14.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 15: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/15.jpg)
Replication Described
log_bin=/data/log/mysql-bin
Config items
Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
![Page 16: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/16.jpg)
Replication Described
server_id=1 server_id=2
Config items
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
![Page 17: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/17.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 18: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/18.jpg)
Replication Commands
• change master to• start slave• stop slave• reset slave• show slave status\G• show master status• mysqlbinlog• grant replication slave
![Page 19: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/19.jpg)
Replication Commandschange master to... Which server? Port? Credentials? Log file & position?
(1) Master
table1table2
mysql
client
Replica (2)
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
Update table1 set...Insert into table2...
![Page 20: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/20.jpg)
Replication Commands• change master to...
Which server? Port? Credentials? Log file & position?•change master to master_host=‘Master’, master_port=3306, master_user=‘repl’, master_password=‘pass’, master_log_file=‘mysql_bin.000001’, master_log_pos=4563744;
![Page 21: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/21.jpg)
Replication Commands
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
mysql
![Page 22: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/22.jpg)
Replication Commandschange master to master_host=‘Master’, master_port=3306, master_user=‘repl’, master_password=‘pass’, master_log_file=‘mysql_bin.000001’, master_log_pos=4563744;
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
IO Thread
Update table1 set...Insert into table2...
mysql
![Page 23: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/23.jpg)
Replication Commandsstart slave;
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
![Page 24: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/24.jpg)
Replication Commandsstop slave;
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
Update table1 set...Insert into table2...
mysql
![Page 25: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/25.jpg)
Replication Commandsreset slave;
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
mysql
![Page 26: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/26.jpg)
Replication Commands• show slave status\G• Manual troubleshooting• Use in scripts for alarming
LAG=$(mysql -s -e’show slave status\G’|grep ‘Seconds_Behind_Master’|cut -f2 -d:| tr -d ‘ ‘)
IO_UP=$(mysql -s -e’show slave status\G’|grep ‘Slave_IO_Running’|cut -f2 -d:| tr -d ‘ ‘)
![Page 27: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/27.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
![Page 28: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/28.jpg)
Slave_IO_State: Waiting for
master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
What it’s doing
![Page 29: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/29.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0
Master_Server_Id: 1
Where it’s connected
![Page 30: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/30.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3
Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Where the IO Thread has read up to
![Page 31: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/31.jpg)
Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843
![Page 32: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/32.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843
Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Where the SQL Thread has executed up to (in relaylog)
![Page 33: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/33.jpg)
Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548
![Page 34: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/34.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548
Relay_Master_Log_File: mysql-bin.000001
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: 23456234
Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Where the SQL Thread has executed up to (corresponding position in Master’s binlog)
![Page 35: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/35.jpg)
Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
Relay_Master_Log_File: mysql-bin.000001 Exec_Master_Log_Pos: 23456234
![Page 36: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/36.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001
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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
If the IO and SQL Threads are running
![Page 37: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/37.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Replication Filters in effect
![Page 38: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/38.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Replication Lag (how long ago was timestamp of latest-processed query?)
Can be “NULL”
![Page 39: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/39.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:
Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Replication Error (SQL Thread)
![Page 40: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/40.jpg)
Slave_IO_State: Waiting for master to send event
Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 28762843 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000001 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: 23456234 Relay_Log_Space: 52 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: 8Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1
Replication Error (IO Thread)
![Page 41: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/41.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 42: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/42.jpg)
Basic Setup Walk-Through
• Unique server_id values
my.cnf on master[mysqld]server_id=1
my.cnf on replica[mysqld]server_id=2
![Page 43: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/43.jpg)
Basic Setup Walk-Through
• Replication UserOn Master:
grant replication slave on *.* to ‘repl’ identified by ‘pass’;
![Page 44: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/44.jpg)
Basic Setup Walk-Through
• Point to the correct binlog position• On master:show master status;
...once both databases are equal• On Replica:change master to ...
![Page 45: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/45.jpg)
Basic Setup Walk-Through
• Start it up• On replica:start slave;
• Check it• On replica:show slave status\G
![Page 46: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/46.jpg)
And that’s it.
![Page 47: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/47.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 48: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/48.jpg)
Limiting Tables or Schemas
• If you don’t need all the tables• In my.cnf:replicate-do-table=schema.tableNamereplicate-skip-table=schema.otherTable
• If you don’t need all the schemas• In my.cnfreplication-do-db=schemaOnereplication_skip_db=schemaTwo
![Page 49: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/49.jpg)
Limiting Tablesreplicate-do-table=schema.tableName
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
table1table2
IO Thread SQL Threadwith filter
Update table1 set...Insert into table2...
mysql
![Page 50: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/50.jpg)
Limiting Columns or Rows
• If you don’t need all columns• Subset of table for reporting or analytics
![Page 51: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/51.jpg)
Limiting Columns or Rows
• Original tablecreate table x (a int primary key, b int, c int) engine=InnoDB;
• Blackhole table on replica like master tablecreate table x (a int, b int, c int) engine=blackhole;
• Target table on replicacreate table y (a int primary key, b int) engine=InnoDB;
![Page 52: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/52.jpg)
Limiting Columns or Rows
• Before-insert trigger on replica to put data into second “real” table
• Only columns a and b, and only for b=1create trigger x_bi before insert on x for each row insert into y (a, b) select new.a, new.b from dual where new.b=1;
![Page 53: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/53.jpg)
Limiting Columns or Rows
(1) Master
table1table2
mysql
Update table1 set...Insert into table2...
client
Replica (2)
x y
IO Thread SQL Thread
Update table1 set...Insert into table2...
mysql
TRG
![Page 54: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/54.jpg)
Multi-Level
• Local and Remote DR Site• Spreading load over many servers
M R M R
M R
R
RR
R
R
R
R
...
![Page 55: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/55.jpg)
Multi-Level
• Configlog_slave_updates=1
![Page 56: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/56.jpg)
Multi-LevelMaster Replica
table1table2
table1table2
mysql IO Thread SQL Thread
Update table1 set...Insert into table2...
Update table1 set...Insert into table2...
Replica
table1table2
IO Thread SQL Thread
Update table1 set...Insert into table2...
Update table1 set...Insert into table2...
mysql
![Page 57: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/57.jpg)
Types of Binlogs
• Statement-Based– Contains the actual statements run– Data drift is more likely (code modified by triggers,
etc)• Row-based– Replicates changes for each row– Mostly unreadable by humans– Unaffected by how the change is done (data drift less
likely)– mysqlbinlog -v
![Page 58: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/58.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 59: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/59.jpg)
Fault: Lag
• Long-running transactions• High concurrency on master becomes single
session on replica• show slave status\G• Easy to alarm on
![Page 60: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/60.jpg)
Fault: Stopping from Error
• Any error stops SQL Thread!• Duplicate Key• Unknown Function• slave_exec_mode=idempotent
...suppresses duplicate-key and no-key-found errors
• show slave status\G
![Page 61: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/61.jpg)
Faults: Can’t Connect to Master
• Fault shows in slave status as soon as you start slave.
• Did you create replication slave user?• Correct user/password?• Correct hostname/port?• Master is running and reachable?
![Page 62: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/62.jpg)
Fault: Can’t Find Binlog
• Clearly stated in the slave status• Can happen if replica has been stopped (or is
lagged) and the binlogs are removed from the master
• Execute from archiveOR• Rebuild replica and restart from a known good
binlog position
![Page 63: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/63.jpg)
Faults: Configuration
• replication-do-table• replication-do-schema• log_slave_updates
![Page 64: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/64.jpg)
Faults: server_id
• Must be unique!• If equal: “Seconds behind” jumps everywhere.
![Page 65: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/65.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 66: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/66.jpg)
Gotcha: functions
• Functions will be run on the replica even if only appearing in a select on the master
• Make sure all functions exist on replica DB, even if only a dummy version
create function myFtn (a int, b int) returns int deterministic return 0;
![Page 67: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/67.jpg)
Gotcha: Reconnection
• When a replica is stopped and reset, until the database is restarted, replication may reconnect to a master after “reset slave”
change master to master_port=1234;
![Page 68: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/68.jpg)
Gotcha: Master Crash
• Replication becomes unstable if master crashes• Error: unknown instruction at position xxx• Often need to use mysqlbinlog to find a
suitable position in the binlog to restart replication from
stop slave; reset slave;change master to ...;start slave;
![Page 69: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/69.jpg)
Gotcha: MySQL Versions
• Replication not completely compatible between versions (especially later=>earlier)
• 5.5 master to 5.1 replica: character set error “sometimes”
• Stops replication
![Page 70: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/70.jpg)
Impossible
• One replica can have only one master(until 5.7)
• Can’t change table or schema filters dynamically
• Can’t put binlogs back on master server, for recovery purposes
![Page 71: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/71.jpg)
• Introduction• Description• Configuration Items• Commands• Setup• Beyond the Basics• Troubleshooting Faults• Gotchas• Closing
![Page 72: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/72.jpg)
The Book
![Page 73: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/73.jpg)
Alternatives
• Tungsten Replicator from Continuent• Others?
![Page 74: Easy MySQL Replication Setup and Troubleshooting](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b7239b4a795916198b45e2/html5/thumbnails/74.jpg)
Questions?