mysql:changemasters
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
mysql:changemasters [2015/06/23 16:48] – created rlunaro | mysql:changemasters [2015/06/24 18:59] – rlunaro | ||
---|---|---|---|
Line 14: | Line 14: | ||
http:// | http:// | ||
+ | < | ||
+ | mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -h MASTER-SERVERNAME -u root -p > ~/dump.sql | ||
+ | </ | ||
+ | As it is said in the document, take note of the value of MASTER_LOG_FILE and MASTER_LOG_POS: | ||
- | This explains how to change the master of a running mysql database by the mechanism of: | + | < |
+ | head dump.sql -n80 | grep " | ||
+ | </ | ||
- | - Create a new slave wihtout downtime | + | In the slave, edit the config file: |
- | - Stop the current master and start a new master (the last slave) | + | |
+ | < | ||
+ | server-id | ||
+ | binlog-format | ||
+ | log_bin | ||
+ | relay-log | ||
+ | log-slave-updates = 1 | ||
+ | read-only | ||
+ | </ | ||
+ | Restart future-master. | ||
+ | |||
+ | Import data: | ||
+ | |||
+ | < | ||
+ | mysql -h localhost -u root -p < .\20150623-total-dump-1114.sql | ||
+ | </ | ||
+ | |||
+ | Now, in the future-master server we must execute: | ||
+ | |||
+ | < | ||
+ | CHANGE MASTER TO MASTER_HOST=' | ||
+ | START SLAVE; | ||
+ | </ | ||
+ | |||
+ | The server future-master will begin to replicate the pending changes from the master server. To check | ||
+ | out the pending tasks it has: | ||
+ | |||
+ | < | ||
+ | show slave status\G | ||
+ | </ | ||
+ | |||
+ | <WRAP center round important 60%> | ||
+ | It's important that that \G must be written as-is: an uppercase G and no space between | ||
+ | the \ and the last character of the command, because otherwise it won't be understood. | ||
+ | </ | ||
+ | |||
+ | The synchronization will be finished when --for instance-- this value shows this: | ||
+ | |||
+ | < | ||
+ | Slave_SQL_Running_State: | ||
+ | </ | ||
+ | |||
+ | I've compared the value '' | ||
+ | make sure all the changes were updated. | ||
+ | |||
+ | ===== Second: make the slave to change the master ===== | ||
+ | |||
+ | We have this: | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | And now we want this: | ||
+ | |||
+ | {{: | ||
+ | |||
+ | For that, we have to: | ||
+ | |||
+ | - Stop the master | ||
+ | - Check that both slaves are up-to-date | ||
+ | - issue a [[https:// | ||
+ | |||
+ | ==== Stop the master ==== | ||
+ | |||
+ | Nothing new under the sun: | ||
+ | |||
+ | < | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | or: | ||
+ | |||
+ | < | ||
+ | net stop mysql | ||
+ | </ | ||
+ | |||
+ | ==== Check that both slaves are up-to-date ==== | ||
+ | |||
+ | Among the information that '' | ||
+ | both database servers are in the same position. | ||
+ | |||
+ | ==== Create (if it does not exist) a replication user in future-master ==== | ||
+ | |||
+ | < | ||
+ | grant replication slave, replication client on *.* to YOUR-REPLICATION-USERNAME identified by ' | ||
+ | </ | ||
+ | |||
+ | ==== Issue a change master command ==== | ||
+ | |||
+ | In the slave server I've issued a '' | ||
+ | |||
+ | < | ||
+ | stop slave; | ||
+ | |||
+ | change master to master_host=' | ||
+ | master_port=3306, | ||
+ | master_user=' | ||
+ | master_password=' | ||
+ | </ | ||
+ | |||
+ | And I've stopped and started the slave. | ||
- | https:// | ||
mysql/changemasters.txt · Last modified: 2022/12/02 22:02 by 127.0.0.1