为了不错过每一期干货强烈建议关注我写技术文章纯属“为爱发电”更新不易希望大家能够多多支持1️⃣ 点赞的人今年都升职加薪了2️⃣ 点在看人今年一定会发财3️⃣ 评论区交流技术每条留言都会回复问题现象从库提示 error reconnecting to master repuser10.20.17.101:3306 - retry-time: 60 retries: 346 message: Your password has expired. To log in you must change it using a client that supports expired pass MySQL 8.0.32 从库提示 SHOW REPLICA STATUS\G 截取部分 Replica_IO_State: Reconnecting after a failed source event read Source_Host: 10.20.17.101 Source_User: repuser Source_Port: 3306 Connect_Retry: 60 Source_Log_File: weerp-binlog.000007 Read_Source_Log_Pos: 5828 Relay_Log_File: weerp-relay.000021 Relay_Log_Pos: 755 Relay_Source_Log_File: weerp-binlog.000007 Replica_IO_Running: Connecting Last_IO_Error: error reconnecting to master repuser10.20.17.101:3306 - retry-time: 60 retries: 346 message: Your password has expired. To log in you must change it using a client that supports expired passwords.解决思路根因明确主库10.20.17.101上的复制用户repuser密码已过期Password Expired导致从库 IO 线程无法连接主库。MySQL 8.0 默认启用密码过期策略复制用户一旦过期从库就会报ERROR 1862并持续重连。一、修复步骤在主库执行登录主库10.20.17.101重置repuser密码-- 方法1保持原密码不变推荐对从库无影响ALTERUSERrepuser%IDENTIFIEDBY原密码PASSWORD EXPIRE NEVER;-- 方法2如果不知道原密码直接设置新密码之后需同步修改从库ALTERUSERrepuser%IDENTIFIEDBYrootrootPASSWORD EXPIRE NEVER;PASSWORD EXPIRE NEVER表示该用户密码永不过期避免以后再次中断复制。二、如果从库密码也变了在从库更新复制凭据如果主库修改了新密码必须在从库更新-- MySQL 8.0.23 语法STOP REPLICA;CHANGEREPLICATIONSOURCETOSOURCE_PASSWORDrootroot;-- 启动复制STARTREPLICA;-- 检查状态SHOWREPLICASTATUS\G Replica_SQL_Running_State: Replica hasreadallrelay log;waitingformore updates如果主库只是用IDENTIFIED BY 原密码重置了相同密码从库无需任何操作IO 线程会在下次重试60 秒内自动连上。三、验证修复结果在从库执行SHOWREPLICASTATUS\G正常后应显示Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Errno: 0Last_IO_Error:空四、预防措施避免复制用户密码再次过期在主库执行-- 查看当前密码过期策略SHOWVARIABLESLIKEdefault_password_lifetime;-- 设置全局密码永不过期0 表示不过期单位天SETGLOBALdefault_password_lifetime0;-- 或仅设置 repuser 永不过期ALTERUSERrepuser%PASSWORD EXPIRE NEVER;建议复制专用账号务必设置PASSWORD EXPIRE NEVER业务账号按需设置过期策略。
主从复制突然“失联”？5分钟搞定MySQL 8.0密码过期大坑！
为了不错过每一期干货强烈建议关注我写技术文章纯属“为爱发电”更新不易希望大家能够多多支持1️⃣ 点赞的人今年都升职加薪了2️⃣ 点在看人今年一定会发财3️⃣ 评论区交流技术每条留言都会回复问题现象从库提示 error reconnecting to master repuser10.20.17.101:3306 - retry-time: 60 retries: 346 message: Your password has expired. To log in you must change it using a client that supports expired pass MySQL 8.0.32 从库提示 SHOW REPLICA STATUS\G 截取部分 Replica_IO_State: Reconnecting after a failed source event read Source_Host: 10.20.17.101 Source_User: repuser Source_Port: 3306 Connect_Retry: 60 Source_Log_File: weerp-binlog.000007 Read_Source_Log_Pos: 5828 Relay_Log_File: weerp-relay.000021 Relay_Log_Pos: 755 Relay_Source_Log_File: weerp-binlog.000007 Replica_IO_Running: Connecting Last_IO_Error: error reconnecting to master repuser10.20.17.101:3306 - retry-time: 60 retries: 346 message: Your password has expired. To log in you must change it using a client that supports expired passwords.解决思路根因明确主库10.20.17.101上的复制用户repuser密码已过期Password Expired导致从库 IO 线程无法连接主库。MySQL 8.0 默认启用密码过期策略复制用户一旦过期从库就会报ERROR 1862并持续重连。一、修复步骤在主库执行登录主库10.20.17.101重置repuser密码-- 方法1保持原密码不变推荐对从库无影响ALTERUSERrepuser%IDENTIFIEDBY原密码PASSWORD EXPIRE NEVER;-- 方法2如果不知道原密码直接设置新密码之后需同步修改从库ALTERUSERrepuser%IDENTIFIEDBYrootrootPASSWORD EXPIRE NEVER;PASSWORD EXPIRE NEVER表示该用户密码永不过期避免以后再次中断复制。二、如果从库密码也变了在从库更新复制凭据如果主库修改了新密码必须在从库更新-- MySQL 8.0.23 语法STOP REPLICA;CHANGEREPLICATIONSOURCETOSOURCE_PASSWORDrootroot;-- 启动复制STARTREPLICA;-- 检查状态SHOWREPLICASTATUS\G Replica_SQL_Running_State: Replica hasreadallrelay log;waitingformore updates如果主库只是用IDENTIFIED BY 原密码重置了相同密码从库无需任何操作IO 线程会在下次重试60 秒内自动连上。三、验证修复结果在从库执行SHOWREPLICASTATUS\G正常后应显示Replica_IO_Running: YesReplica_SQL_Running: YesLast_IO_Errno: 0Last_IO_Error:空四、预防措施避免复制用户密码再次过期在主库执行-- 查看当前密码过期策略SHOWVARIABLESLIKEdefault_password_lifetime;-- 设置全局密码永不过期0 表示不过期单位天SETGLOBALdefault_password_lifetime0;-- 或仅设置 repuser 永不过期ALTERUSERrepuser%PASSWORD EXPIRE NEVER;建议复制专用账号务必设置PASSWORD EXPIRE NEVER业务账号按需设置过期策略。
