云服务器网站建设,外链建设的方式有哪些,褚明宇wordpress,公司图标设计大全免费在生产环境中有时候需要修改复制用户账户的密码#xff0c;比如密码遗失#xff0c;或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导致主从不一致而需要进行修复。本文描述了修改复制账户密码以及变更复制账户。 1、更改复制…在生产环境中有时候需要修改复制用户账户的密码比如密码遗失或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导致主从不一致而需要进行修复。本文描述了修改复制账户密码以及变更复制账户。 1、更改复制账户密码[sql] view plaincopyprint?--演示环境同一主机上的2个实例主3406从3506 --当前版本注master账户表明是对主库进行相关操作slave则是对从库进行相关操作 masterlocalhost[(none)] show variables like version; --------------------------- | Variable_name | Value | --------------------------- | version | 5.6.12-log | --------------------------- --主库上的记录 masterlocalhost[test] select * from tb1; ------------- | id | name | ------------- | 1 | robin | ------------- --从库上的记录 slavelocalhost[test] select * from tb1; ------------- | id | name | ------------- | 1 | robin | ------------- --当前从库上的状态信息 slavelocalhost[test] show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 Read_Master_Log_Pos: 3296006 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 811 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test,sakila --仅复制了test以及sakila数据库 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: 3296006 Relay_Log_Space: 978 --主库上复制账户的信息 masterlocalhost[test] show grants for repl192.168.1.177; ---------------------------------------------------------------------------------------------------------------- | Grants for repl192.168.1.177 | ---------------------------------------------------------------------------------------------------------------- | GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.177 IDENTIFIED BY PASSWORD *A424E797037BF191C5C2038C039 | ---------------------------------------------------------------------------------------------------------------- --修改复制账户密码 masterlocalhost[test] GRANT REPLICATION SLAVE ON *.* TO repl192.168.1.177 IDENTIFIED BY replpwd; --如下查询密码已更改 masterlocalhost[test] select user,host,password from mysql.user where userrepl; ---------------------------------------------------------------- | user | host | password | ---------------------------------------------------------------- | repl | 192.168.1.177 | *4A04E4FD524292A79E3DCFEBBD46094478F178EF | ---------------------------------------------------------------- --更新记录 masterlocalhost[test] insert into tb1 values(2,fred); --重库上可以查询到刚刚被更新的记录 slavelocalhost[test] select * from tb1; ------------- | id | name | ------------- | 1 | robin | | 2 | fred | ------------- slavelocalhost[test] stop slave; Query OK, 0 rows affected (0.02 sec) slavelocalhost[test] start slave; Query OK, 0 rows affected (0.01 sec) --再次查看状态出现了错误提示 slavelocalhost[test] show slave status \G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 Read_Master_Log_Pos: 3296438 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1243 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: test,sakila .................... Last_IO_Errno: 1045 Last_IO_Error: error connecting to master repl192.168.1.177:3406 - retry-time: 60 retries: 1 --更改重库连接密码该信息记录在从库master.info文件中 slavelocalhost[test] stop slave; slavelocalhost[test] change master to - master_userrepl, - master_passwordreplpwd; Query OK, 0 rows affected, 2 warnings (0.00 sec) --修改密码后从库状态正常以下检查结果不再列出 slavelocalhost[test] start slave; --查看master.info密码已更改且为名文 slavelocalhost[(none)] system grep repl /data/inst3506/data3506/master.info repl replpwd 2、更换复制账户及密码[sql] view plaincopyprint?masterlocalhost[test] GRANT REPLICATION SLAVE ON *.* TO repl2192.168.1.177 IDENTIFIED BY Repl2; Query OK, 0 rows affected (0.00 sec) slavelocalhost[test] stop slave; Query OK, 0 rows affected (0.28 sec) masterlocalhost[test] insert into tb1 values(3,jack); Query OK, 1 row affected (0.00 sec) slavelocalhost[test] change master to - MASTER_USERrepl2, - MASTER_PASSWORDRepl2; Query OK, 0 rows affected, 2 warnings (0.01 sec) slavelocalhost[test] system more /data/inst3506/data3506/master.info 23 inst3406bin.000001 3294834 192.168.1.177 repl2 Repl2 3406 .......... slavelocalhost[test] start slave; Query OK, 0 rows affected (0.01 sec) slavelocalhost[test] select * from tb1 where id3; ------------ | id | name | ------------ | 3 | jack | ------------ 1 row in set (0.00 sec) slavelocalhost[(none)] show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl2 Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 --Author :Leshami Read_Master_Log_Pos: 3296871 --Blog : http://blog.csdn.net/leshami Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 501 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test,sakila 3、关于change masterCHANGE MASTER TO changes the parameters that the slave server uses for connecting to the masterserver, for reading the master binary log, and reading the slave relay log. It also updates the contentsof the master info and relay log info repositories (see Section 16.2.2, “Replication Relay and StatusLogs”). To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVEif necessary). In MySQL 5.6.11 and later, gtid_next [2060] must also be set to AUTOMATIC (Bug#16062608). Options not specified retain their value, except as indicated in the following discussion. Thus, in mostcases, there is no need to specify options that do not change. For example, if the password to connectto your MySQL master has changed, you just need to issue these statements to tell the slave about thenew password: STOP SLAVE; -- if replication was runningCHANGE MASTER TO MASTER_PASSWORDnew3cret;START SLAVE; -- if you want to restart replication MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to theslave about how to connect to its master: Note: Replication cannot use Unix socket files. You must be able to connect to themaster MySQL server using TCP/IP. If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the masterserver is different from before (even if the option value is the same as its current value.) In thiscase, the old values for the master binary log file name and position are considered no longerapplicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement,MASTER_LOG_FILE and MASTER_LOG_POS4 are silently appended to it. Setting MASTER_HOST (that is, setting its value explicitly to an empty string) is not the same asnot setting MASTER_HOST at all. Beginning with MySQL 5.5, trying to set MASTER_HOST to an emptystring fails with an error. Previously, setting MASTER_HOST to an empty string caused START SLAVEsubsequently to fail. (Bug #28796)小编推荐欲学习电脑技术、系统维护、网络管理、编程开发和安全攻防等高端IT技术请 点击这里注册账号公开课频道价值万元IT培训教程免费学让您少走弯路、事半功倍好工作升职加薪免责声明本站系公益性非盈利IT技术普及网本文由投稿者转载自互联网的公开文章文末均已注明出处其内容和图片版权归原网站或作者所有文中所述不代表本站观点若有无意侵权或转载不当之处请从网站右下角联系我们处理谢谢合作
