- allow a user to quickly change master
- allow the logic to be ran on a multimaster → does not have GTID enabled for PC
Description
Description
Details
Details
Related Changes in Gerrit:
| Subject | Author | Repo | Branch | Lines +/- | |
|---|---|---|---|---|---|
| mysql: update replication source | Federico Ceratto | operations/cookbooks | master | +422 -3 |
Related Objects
Related Objects
Event Timeline
Comment Actions
@FCeratto-WMF can you start working on this soonish?
The idea behind this would be a cookbook which would do something like:
sre.mysql.change-master -h HOST_TO_CHANGE -n $NEW_MASTER
By default it should enable GTID by default, but if we don't want to, maybe we can use something like `--no-gtid to avoid it.
It should basically do the same as the moving slaves part of db-switchover but between two hosts.
Comment Actions
fceratto opened https://gitlab.wikimedia.org/repos/sre/wmfmariadbpy/-/merge_requests/18
Draft: Allowing changing a host's replication master
Comment Actions
Change #1238368 had a related patch set uploaded (by Federico Ceratto; author: Federico Ceratto):
[operations/cookbooks@master] mysql: simple cookbook to update replication source
Comment Actions
fceratto closed https://gitlab.wikimedia.org/repos/sre/wmfmariadbpy/-/merge_requests/18
Draft: Allowing changing a host's replication master
Comment Actions
Pending final review. If wanted we can test it again on db-test* in a shared session or elsewhere.
Comment Actions
I did some more replication changes on the testbed to fix the replication and it's catching up. Most changes requires truncating the heartbeat table, and for better ergonomics we could later on add an optional --truncate flag.
Comment Actions
From:
From:
test-cookbook --change 1238368 sre.mysql.update-replication --src db-test2001 --dst db-test2002
... but that looks like it might have been a delayed update to the UI as the lag has gone.
@Marostegui note that the code is issuing a TRUNCATE
We probably need some more visibility in this though:
% test-cookbook --change 1238368 sre.mysql.update-replication --src db-test2001 --dst db-test2002
INFO:__main__:Change exists in project operations/cookbooks with latest patch set being 22
INFO:__main__:Setting up Cookbooks change 1238368 patch set 22 for testing
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 status --porcelain
INFO:__main__:No local modification found, fetching change from Gerrit
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 fetch https://gerrit.wikimedia.org/r/operations/cookbooks refs/changes/68/1238368/22
From https://gerrit.wikimedia.org/r/operations/cookbooks
* branch refs/changes/68/1238368/22 -> FETCH_HEAD
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 rev-parse --verify change-1238368-22
d77ba729da0e1db7db209e3bdbcbf2939e4e5f46
INFO:__main__:Switching to already existing branch change-1238368-22
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 checkout change-1238368-22
Already on 'change-1238368-22'
INFO:__main__:==================================================
INFO:__main__:Executing: sudo cookbook -c /home/cwilliams/cookbooks_testing/config.yaml sre.mysql.update-replication --src db-test2001 --dst db-test2002
INFO:__main__:==================================================
Acquired lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-17 13:03:56.167277+00:00', 'owner': 'cwilliams@cumin1003 [44231]', 'ttl': 1800}
START - Cookbook sre.mysql.update-replication
{'Slave_IO_State': 'Waiting for master to send event', 'Master_Host': 'db-test2001.codfw.wmnet', 'Master_User': 'repl2024', 'Master_Port': 3306, 'Connect_Retry': 60, 'Master_Log_File': 'db-test2001-bin.000004', 'Read_Master_Log_Pos': 9905910, 'Relay_Log_File': 'db-test2002-relay-bin.000002', 'Relay_Log_Pos': 6985, 'Relay_Master_Log_File': 'db-test2001-bin.000004', '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': 3570612, 'Relay_Log_Space': 2171915, 'Until_Condition': 'None', 'Until_Log_File': '', 'Until_Log_Pos': 0, 'Master_SSL_Allowed': 'Yes', 'Master_SSL_CA_File': '', 'Master_SSL_CA_Path': '', 'Master_SSL_Cert': '', 'Master_SSL_Cipher': '', 'Master_SSL_Key': '', 'Seconds_Behind_Master': 5201, 'Master_SSL_Verify_Server_Cert': 'No', 'Last_IO_Errno': 0, 'Last_IO_Error': '', 'Last_SQL_Errno': 0, 'Last_SQL_Error': '', 'Replicate_Ignore_Server_Ids': '', 'Master_Server_Id': 180355088, 'Master_SSL_Crl': '', 'Master_SSL_Crlpath': '', 'Using_Gtid': 'Slave_Pos', 'Gtid_IO_Pos': '171970587-171970587-14933044,171974676-171974676-1272056,180355088-180355088-8217911,180360463-180360463-9020259,171966607-171966607-5012086', 'Replicate_Do_Domain_Ids': '', 'Replicate_Ignore_Domain_Ids': '', 'Parallel_Mode': 'optimistic', 'SQL_Delay': 0, 'SQL_Remaining_Delay': None, 'Slave_SQL_Running_State': 'Commit', 'Slave_DDL_Groups': 0, 'Slave_Non_Transactional_Groups': 0, 'Slave_Transactional_Groups': 16402, 'Replicate_Rewrite_DB': ''}
Done
Released lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-17 13:03:56.167277+00:00', 'owner': 'cwilliams@cumin1003 [44231]', 'ttl': 1800}
END (PASS) - Cookbook sre.mysql.update-replication (exit_code=0)Perhaps also a section lock?
/spicerack/locks/cookbooks/sre.mysql.update-replication
@FCeratto-WMF is there a reason that you chose src and dst when they don't seem to do that ... e.g.
--src db-test2001 --dst db-test2002
This puts 2002 under 2001, but dst seems an unusual term for that to me, I had to read the code.
Trying the reverse now:
% test-cookbook --change 1238368 sre.mysql.update-replication --src db2230 --dst db-test2002
INFO:__main__:Change exists in project operations/cookbooks with latest patch set being 22
INFO:__main__:Setting up Cookbooks change 1238368 patch set 22 for testing
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 status --porcelain
INFO:__main__:No local modification found, fetching change from Gerrit
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 fetch https://gerrit.wikimedia.org/r/operations/cookbooks refs/changes/68/1238368/22
From https://gerrit.wikimedia.org/r/operations/cookbooks
* branch refs/changes/68/1238368/22 -> FETCH_HEAD
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 rev-parse --verify change-1238368-22
d77ba729da0e1db7db209e3bdbcbf2939e4e5f46
INFO:__main__:Switching to already existing branch change-1238368-22
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 checkout change-1238368-22
Already on 'change-1238368-22'
INFO:__main__:==================================================
INFO:__main__:Executing: sudo cookbook -c /home/cwilliams/cookbooks_testing/config.yaml sre.mysql.update-replication --src db2230 --dst db-test2002
INFO:__main__:==================================================
Acquired lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-17 13:20:27.215592+00:00', 'owner': 'cwilliams@cumin1003 [45319]', 'ttl': 1800}
START - Cookbook sre.mysql.update-replication
db2230 is showing an alarm: wmfdbagent.service on db2230:9100
==> Continue?
Type "go" to proceed or "abort" to interrupt the execution
>I don't see anything in Icinga:
{F94228351}
I think that you should check more that if there is just _anything_:
{
"instances": [
{
"dc": "codfw",
"fqdn": "db2230.codfw.wmnet",
"hostname": "db2230",
"instance_group": "test",
"instance_name": "db2230",
"last_start": null,
"mariadb_version": "10.11.18-MariaDB-log",
"port": 3306,
"section": "test-s4",
"alerts": [
{
"alertname": "SystemdUnitFailed",
"severity": "critical",
"hostname": "db2230",
"status": "suppressed",
"description": "wmfdbagent.service on db2230:9100",
"starts_at": "2026-07-17T11:31:54",
"ingested_at": "2026-07-17T13:26:44"
}
],
"candidate_score": 0,
"is_candidate_on_dbctl": null,
"is_lagging": false,
"lag": 0.79273,
"pooled_value": null,
"role": "",
"kernel_version": "6.12.95-1",
"uptime_s": null,
"uptime_human": "1 h",
"tags": [
"SystemdUnitFailed wmfdbagent.service on db2230:9100"
],
"preferred_candidate": false
}
]
}Comment Actions
@FCeratto-WMF which host(s) did you test with in the test-s4 cluster?
I tried with db-test2002 and it failed:
% test-cookbook -c 1238368 sre.mysql.update-replication --master db2230 --replica db-test2002
INFO:__main__:Change exists in project operations/cookbooks with latest patch set being 32
INFO:__main__:Setting up Cookbooks change 1238368 patch set 32 for testing
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 status --porcelain
INFO:__main__:No local modification found, fetching change from Gerrit
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 fetch https://gerrit.wikimedia.org/r/operations/cookbooks refs/changes/68/1238368/32
From https://gerrit.wikimedia.org/r/operations/cookbooks
* branch refs/changes/68/1238368/32 -> FETCH_HEAD
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 rev-parse --verify change-1238368-32
b545f39cbc778041a35cbed6ed58fda334946a68
INFO:__main__:Switching to already existing branch change-1238368-32
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 checkout change-1238368-32
Already on 'change-1238368-32'
INFO:__main__:==================================================
INFO:__main__:Executing: sudo cookbook -c /home/cwilliams/cookbooks_testing/config.yaml sre.mysql.update-replication --master db2230 --replica db-test2002
INFO:__main__:==================================================
Acquired lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-29 14:10:00.365691+00:00', 'owner': 'cwilliams@cumin1003 [3641285]', 'ttl': 1800}
START - Cookbook sre.mysql.update-replication
Incorrect replica status: key 'Last_IO_Errno' expected '0' found '1236'
Incorrect replica status: key 'Slave_IO_Running' expected 'Yes' found 'No'
{'Slave_IO_State': '', 'Master_Host': 'db2230.codfw.wmnet', 'Master_User': 'repl2024', 'Master_Port': 3306, 'Connect_Retry': 60, 'Master_Log_File': '', 'Read_Master_Log_Pos': 4, 'Relay_Log_File': 'db-test2002-relay-bin.000001', 'Relay_Log_Pos': 4, 'Relay_Master_Log_File': '', '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': 4, 'Relay_Log_Space': 256, 'Until_Condition': 'None', 'Until_Log_File': '', 'Until_Log_Pos': 0, 'Master_SSL_Allowed': 'Yes', 'Master_SSL_CA_File': '', 'Master_SSL_CA_Path': '', 'Master_SSL_Cert': '', 'Master_SSL_Cipher': '', 'Master_SSL_Key': '', 'Seconds_Behind_Master': None, 'Master_SSL_Verify_Server_Cert': 'No', 'Last_IO_Errno': 1236, 'Last_IO_Error': "Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 180355088-180355088-9258670, which is not in the master's binlog'", 'Last_SQL_Errno': 0, 'Last_SQL_Error': '', 'Replicate_Ignore_Server_Ids': '', 'Master_Server_Id': 180360463, 'Master_SSL_Crl': '', 'Master_SSL_Crlpath': '', 'Using_Gtid': 'Slave_Pos', 'Gtid_IO_Pos': '171970587-171970587-14933044,171974676-171974676-1272056,180355088-180355088-9258670,180360463-180360463-10061022,171966607-171966607-6052849', 'Replicate_Do_Domain_Ids': '', 'Replicate_Ignore_Domain_Ids': '', 'Parallel_Mode': 'optimistic', 'SQL_Delay': 0, 'SQL_Remaining_Delay': None, 'Slave_SQL_Running_State': 'Slave has read all relay log; waiting for more updates', 'Slave_DDL_Groups': 0, 'Slave_Non_Transactional_Groups': 0, 'Slave_Transactional_Groups': 3143890, 'Replicate_Rewrite_DB': ''}
Unexpected replica status
SystemExit(1) raised while executing cookbook sre.mysql.update-replication:
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/spicerack/_menu.py", line 265, in _run
raw_ret = runner.run()
^^^^^^^^^^^^
File "/home/cwilliams/cookbooks_testing/cookbooks/cookbooks/sre/mysql/update-replication.py", line 132, in run
sys.exit(1)
SystemExit: 1
Released lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-29 14:10:00.365691+00:00', 'owner': 'cwilliams@cumin1003 [3641285]', 'ttl': 1800}
END (ERROR) - Cookbook sre.mysql.update-replication (exit_code=1)However, putting it back did work:
% test-cookbook -c 1238368 sre.mysql.update-replication --master db-test2001 --replica db-test2002
INFO:__main__:Change exists in project operations/cookbooks with latest patch set being 32
INFO:__main__:Setting up Cookbooks change 1238368 patch set 32 for testing
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 status --porcelain
INFO:__main__:No local modification found, fetching change from Gerrit
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 fetch https://gerrit.wikimedia.org/r/operations/cookbooks refs/changes/68/1238368/32
From https://gerrit.wikimedia.org/r/operations/cookbooks
* branch refs/changes/68/1238368/32 -> FETCH_HEAD
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 rev-parse --verify change-1238368-32
b545f39cbc778041a35cbed6ed58fda334946a68
INFO:__main__:Switching to already existing branch change-1238368-32
INFO:__main__:Executing command /usr/bin/git -C /home/cwilliams/cookbooks_testing/cookbooks-1238368 checkout change-1238368-32
Already on 'change-1238368-32'
INFO:__main__:==================================================
INFO:__main__:Executing: sudo cookbook -c /home/cwilliams/cookbooks_testing/config.yaml sre.mysql.update-replication --master db-test2001 --replica db-test2002
INFO:__main__:==================================================
Acquired lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-29 14:12:43.554308+00:00', 'owner': 'cwilliams@cumin1003 [3641504]', 'ttl': 1800}
START - Cookbook sre.mysql.update-replication
Done
Released lock for key /spicerack/locks/cookbooks/sre.mysql.update-replication: {'concurrency': 20, 'created': '2026-07-29 14:12:43.554308+00:00', 'owner': 'cwilliams@cumin1003 [3641504]', 'ttl': 1800}
END (PASS) - Cookbook sre.mysql.update-replication (exit_code=0)A while back, I restored all of test-s4 from the primary, so I am wondering where the issue has come from, i.e. did it pre-date any testing that you have been doing, or possibly as a result of it?
One possibility is orchestrator, given that it does automatic failover for this cluster, so as part of the new cookbook I can retest this once all should be clean.
Comment Actions
The error in
Incorrect replica status: key 'Last_IO_Errno' expected '0' found '1236' Incorrect replica status: key 'Slave_IO_Running' expected 'Yes' found 'No'
is from the very recent change where the output of SHOW REPLICA STATUS started being checked in this changeset https://gerrit.wikimedia.org/r/c/operations/cookbooks/+/1238368/28..29 which is after the tests done on test-s4 and I think it might be too strict around Last_IO_Errno, and I'm not sure why it's showing Slave_IO_Running=No given:
_run_query(dry_run, replica, "START REPLICA") status: dict = replica.show_slave_status()
Maybe we need a little sleep after start replica @Marostegui ? I'll try to replicate the same issue if the testbed is available.
(BTW should we move the conversation in the CR instead?)
Comment Actions
is from the very recent change where the output
@FCeratto-WMF no, this was broken replication. Reversing the operation cleared it.
To make it clear:
Error: connecting slave requested to start from GTID 180355088-180355088-9258670, which is not in the master's binlog'
A while back, I restored all of test-s4 from the primary, so I am wondering where the issue has come from, i.e. did it pre-date any testing that you have been doing, or possibly as a result of it?
One possibility is orchestrator, given that it does automatic failover for this cluster, so as part of the new cookbook I can retest this once all should be clean.
Please can you confirm which hosts in test-s4 you have been testing this cookbook with.
Comment Actions
The problem may be the mess with have with GTID and gtid_domain_id
The way we move slaves with db-switchover is:
- we disable GTID
- Move the host
- Enable GTID
Which is sort of what I mentioned at: T373436#11511926
(BTW should we move the conversation in the CR instead?)
It is easier to do it here, especially with long pastes - gerrit is terrible for that.
Comment Actions
The testbed had stale data that had to be removed to continue testing the cookbook. The current tests involved db-test2002 and db-test1001 being reparented under db-test1002 or db2230 or db1176 and run fine.
Comment Actions
Change #1238368 merged by jenkins-bot:
[operations/cookbooks@master] mysql: update replication source
Comment Actions
Added a summary page https://wikitech.wikimedia.org/wiki/MariaDB/Cookbooks#sre.mysql.sanitize-wiki - closing this task.