transition_plan.markdown

The plan

Initial status:

M1  -->  S1       <- Old hardware running 5.0 to be replaced 


S2       S3       <- New hardware runnign 5.5 to replace the old.

Transitional status:

M1  -->  S1
|
V
S2  -->  S3

After which mastery is moved from M1 to S2 and M1 and S1 are decommissioned.

The process

1. All 4 servers have different server_id.

2. STOP SLAVE on S1.

3. SHOW SLAVE STATUS on S1 and note its Relay_Master_Log_File + Exec_Master_Log_Pos (S1 is 5.0 so mysqldump doesn't have --dump-slave).

4. Dump S1's databases

5. Load the dumped data on S2 and S3, both of which are running with log-slave-updates

6. Run mysql_upgrade --skip-write-binlong on S2 and S3.

7. Restart mysqld on S2 and on S3.

8. CHANGE MASTER TO on S2 using M1 as master host and the previously noted Relay_Master_Log_File + Exec_Master_Log_Pos.

9. Do similar on S3 but using S2 as as master host.

10. Switch clients from M1 to S2. All at once!

11. Turn off M1.

12. RESET SLAVE on S2.

13. Stop using now obsolete and confusing names S2 and S3.

Wisdom is the domain of the wiz, which is extinct

Confusing terms

16:12 tom[]: the terms master and relay are confusing me. i guess i need to understand how a slave actually works

16:13 mgriffin: tom[]: master sees binary log is enabled and starts writing changes to a binary log (in your case, the original SQL statement that caused some change). slave connects to master with IO thread and says "give me your binlogs starting with file X at pos Y). io thread spools these binary logs on the slave and calls them relay logs. sql thread on slave waits for relay logs to exist and executes the contents, removing the relay log when all contained events are processed. the io thread is told by the master which binary log the master is currenty writing to, this is called Master_Log_File when you display show show slave status. the sql thread knows which master binary log an event was originally stored in, this is called Relay_Master_Log_File.

Good practice for slaves

15:54 mgriffin: tom[]: as always when making a slave, set s3 to read-only=1 and verify that no users besides root and monitoring tools have super (select user, host from mysql.user where super_priv='y'). if some web app user has super, you can REVOKE SUPER ON *.* FROM user@host; (grant all on *.* implies super and the revoke changes the grant to SELECT,UPDATE,LOCK,.....)

Pro tip

16:23 mgriffin: tom[]: protip, when you have run stop slave on s1, do not start mysqldump if show status like 'Slave_open_temp_tables' is not 0. in that case, run start slave; stop slave;