4 ms·

Nice writeup. I've been wrestling with MySQL replication for a few months, too. I have a multi-master setup with an additional slave off-site that stores backup

Nice writeup. I've been wrestling with MySQL replication for a few months, too. I have a multi-master setup with an additional slave off-site that stores backups. I've switched entirely to row-based replication because it offers one really nice feature: in the event of a desync, I can resync the slaves with a query like this on the master, create table new_table like bad_table; insert new_table select * from bad_table; drop table bad_table; rename table new_table to bad_table; ...and bam, everything's groovy. On a VPS, I can have the slaves resync'd within a few minutes this way on tables with > 1m rows. The other pretty important thing to have is fast notifications of trouble. It sucks waiting for the slaves to catch up on the binlog from the master. I wrote a shell script that runs from cron every few minutes, checks the replication status on all hosts, and lets me know if there's trouble. Being a few days behind in replication can take hours to catch up; being a few hours behind only takes a few minutes to catch up. I'm really looking forward to trying out Percona XtraDB-cluster and seeing if they handle any of this stuff better than stock MySQL.

robbles 14y ago
Could you explain why that technique is only viable for row-based replication? It looks really useful and I don't quite understand enough about the replication process to see why you couldn't rely on this for mixed replication as well.
- thaumaturgy 14y ago
  It might work for mixed too, but I'm not sure. Honestly, I read http://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html http://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html a few times and my eyes glazed over by the end every time. Finally I just decided that I could expect it to work forever with row-based, but I was less certain about whether it would work in mixed, or whether it might change in future versions of MySQL. I consider "I'm not sure what will happen" to be a scary answer in sysops, so I went with row-based.
- blantonl 14y ago
  this should work in a mixed setup, remember replication in MySQL is pretty much a replay of SQL statements.
  - toast0 14y ago
    I don't know how mixed mode decides to use row or query based for a given query; but if it chose query based, the queries would work, but you wouldn't be resynchronized. (In query based mode, I've done resynchs by iterating over each row, and setting a not super important column to a different value, and then back, including values for all the columns that need to be resynchronized. It's a lot more queries, but it's also easier to rate limit if your tables are big enough that doing it in two queries is going to back everything up)
blantonl 14y ago
I'd love to see your shell script for checking a slave status.
- thaumaturgy 14y ago
  Sure. It was a little fiddly to set up but nothing too fancy: #!/bin/sh # MySQL Status check module # Retrieve mysql status from target server statusinfo=`sudo -u tron ssh "tron@$server" "sudo tron mysqlpass \"$mysqlpass\" mysqlstatus"` # Status of Slave_IO_Running status_io=`echo "$statusinfo" | grep -o -e "Slave_IO_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Status of Slave_SQL_Running status_sql=`echo "$statusinfo" | grep -o -e "Slave_SQL_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Seconds behind master status_sec=`echo "$statusinfo" | grep -o -e "Seconds_Behind_Master:[[:space:]]*[[:alnum:]]*" | cut -d ' ' -f 2` # Verify status, send notification if it's bad. status_ok=1 if [ "$status_io" != "Yes" ]; then status_ok=0 echo "Slave_IO_Running on $server: $status_io\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_IO_Running on $server" rob@associatedtechs.com fi if [ "$status_sql" != "Yes" ]; then status_ok=0 echo "Slave_SQL_Running on $server: $status_sql\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_SQL_Running on $server" rob@associatedtechs.com fi if [ "$status_sec" -gt "300" ]; then status_ok=0 echo "Seconds_Behind_Master on $server: $status_sec\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Seconds_Behind_Master on $server" rob@associatedtechs.com fi if [ "$status_ok" -eq "1" ]; then echo "OK" fi This is a module from a set of scripts that I collectively refer to as "tron" (they "fight for my users" :-). The "mysqlstatus" command on the remote server gets routed to a tron client that ends up running, mysql_status () { echo "show master status\G; show slave status \G" | mysql -u root -p"$mysqlpass" } It's probably a bad idea to pass the mysql root user password as a parameter in a command (you have to be certain it's not getting logged anywhere), so don't do that if you decide to make something similar. I prefer it to having a readable file on the server with the password, but it would probably be better yet to set up a specific MySQL user with limited access to specific stuff. It's on my to-do list.
  - 14y ago
jyap 14y ago
Yes, that should also work for Mixed replication. The rule for Mixed replication is that Statement based replication is performed UNLESS the statement is non-deterministic. Eg. If you are performing an update on a random row ID then a Row-based statement is performed on the slaves.

robbles 14y ago

Could you explain why that technique is only viable for row-based replication? It looks really useful and I don't quite understand enough about the replication process to see why you couldn't rely on this for mixed replication as well.

thaumaturgy 14y ago
It might work for mixed too, but I'm not sure. Honestly, I read http://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html http://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html a few times and my eyes glazed over by the end every time. Finally I just decided that I could expect it to work forever with row-based, but I was less certain about whether it would work in mixed, or whether it might change in future versions of MySQL. I consider "I'm not sure what will happen" to be a scary answer in sysops, so I went with row-based.
blantonl 14y ago
this should work in a mixed setup, remember replication in MySQL is pretty much a replay of SQL statements.
- toast0 14y ago
  I don't know how mixed mode decides to use row or query based for a given query; but if it chose query based, the queries would work, but you wouldn't be resynchronized. (In query based mode, I've done resynchs by iterating over each row, and setting a not super important column to a different value, and then back, including values for all the columns that need to be resynchronized. It's a lot more queries, but it's also easier to rate limit if your tables are big enough that doing it in two queries is going to back everything up)

blantonl 14y ago

I'd love to see your shell script for checking a slave status.

thaumaturgy 14y ago
Sure. It was a little fiddly to set up but nothing too fancy: #!/bin/sh # MySQL Status check module # Retrieve mysql status from target server statusinfo=`sudo -u tron ssh "tron@$server" "sudo tron mysqlpass \"$mysqlpass\" mysqlstatus"` # Status of Slave_IO_Running status_io=`echo "$statusinfo" | grep -o -e "Slave_IO_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Status of Slave_SQL_Running status_sql=`echo "$statusinfo" | grep -o -e "Slave_SQL_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Seconds behind master status_sec=`echo "$statusinfo" | grep -o -e "Seconds_Behind_Master:[[:space:]]*[[:alnum:]]*" | cut -d ' ' -f 2` # Verify status, send notification if it's bad. status_ok=1 if [ "$status_io" != "Yes" ]; then status_ok=0 echo "Slave_IO_Running on $server: $status_io\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_IO_Running on $server" rob@associatedtechs.com fi if [ "$status_sql" != "Yes" ]; then status_ok=0 echo "Slave_SQL_Running on $server: $status_sql\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_SQL_Running on $server" rob@associatedtechs.com fi if [ "$status_sec" -gt "300" ]; then status_ok=0 echo "Seconds_Behind_Master on $server: $status_sec\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Seconds_Behind_Master on $server" rob@associatedtechs.com fi if [ "$status_ok" -eq "1" ]; then echo "OK" fi This is a module from a set of scripts that I collectively refer to as "tron" (they "fight for my users" :-). The "mysqlstatus" command on the remote server gets routed to a tron client that ends up running, mysql_status () { echo "show master status\G; show slave status \G" | mysql -u root -p"$mysqlpass" } It's probably a bad idea to pass the mysql root user password as a parameter in a command (you have to be certain it's not getting logged anywhere), so don't do that if you decide to make something similar. I prefer it to having a readable file on the server with the password, but it would probably be better yet to set up a specific MySQL user with limited access to specific stuff. It's on my to-do list.
- 14y ago

jyap 14y ago

Yes, that should also work for Mixed replication. The rule for Mixed replication is that Statement based replication is performed UNLESS the statement is non-deterministic. Eg. If you are performing an update on a random row ID then a Row-based statement is performed on the slaves.