在生產(chǎn)環(huán)境中有時候需要修改復(fù)制用戶賬戶的密碼，比如密碼遺失，或者由于多個不同的復(fù)制用戶想統(tǒng)一為單獨一個復(fù)制賬戶。對于這些操作應(yīng)盡可能慎重以避免操作不同導(dǎo)致主從不一致而需要進行修復(fù)。本文描述了修改復(fù)制賬戶密碼以及變更復(fù)制賬戶。?1、更改復(fù)制賬戶密碼[sql] view plaincopyprint?--演示環(huán)境，同一主機上的2個實例，主3406，從3506??--當(dāng)前版本，注：master賬戶表明是對主庫進行相關(guān)操作，slave則是對從庫進行相關(guān)操作??master@localhost[(none)]>?show?variables?like?'version';??+---------------+------------+??|?Variable_name?|?Value??????|??+---------------+------------+??|?version???????|?5.6.12-log?|??+---------------+------------+????--主庫上的記錄??master@localhost[test]>?select?*?from?tb1;??+------+-------+??|?id???|?name??|??+------+-------+??|????1?|?robin?|??+------+-------+????--從庫上的記錄??slave@localhost[test]>?select?*?from?tb1;??+------+-------+??|?id???|?name??|??+------+-------+??|????1?|?robin?|??+------+-------+????--當(dāng)前從庫上的狀態(tài)信息??slave@localhost[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???--僅復(fù)制了test以及sakila數(shù)據(jù)庫????????????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?????????????--主庫上復(fù)制賬戶的信息??master@localhost[test]>?show?grants?for?'repl'@'192.168.1.177';??+----------------------------------------------------------------------------------------------------------------+??|?Grants?for?repl@192.168.1.177??????????????????????????????????????????????????????????????????????????????????|??+----------------------------------------------------------------------------------------------------------------+??|?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'192.168.1.177'?IDENTIFIED?BY?PASSWORD?'*A424E797037BF191C5C2038C039'?|??+----------------------------------------------------------------------------------------------------------------+????--修改復(fù)制賬戶密碼??master@localhost[test]>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'192.168.1.177'?IDENTIFIED?BY?'replpwd';????--如下查詢密碼已更改??master@localhost[test]>?select?user,host,password?from?mysql.user?where?user='repl';??+------+---------------+-------------------------------------------+??|?user?|?host??????????|?password??????????????????????????????????|??+------+---------------+-------------------------------------------+??|?repl?|?192.168.1.177?|?*4A04E4FD524292A79E3DCFEBBD46094478F178EF?|??+------+---------------+-------------------------------------------+????--更新記錄??master@localhost[test]>?insert?into?tb1?values(2,'fred');????--重庫上可以查詢到剛剛被更新的記錄??slave@localhost[test]>?select?*?from?tb1;??+------+-------+??|?id???|?name??|??+------+-------+??|????1?|?robin?|??|????2?|?fred??|??+------+-------+????slave@localhost[test]>?stop?slave;??Query?OK,?0?rows?affected?(0.02?sec)????slave@localhost[test]>?start?slave;??Query?OK,?0?rows?affected?(0.01?sec)????--再次查看狀態(tài)出現(xiàn)了錯誤提示??slave@localhost[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?'repl@192.168.1.177:3406'?-?retry-time:?60??retries:?1????--更改重庫連接密碼，該信息記錄在從庫master.info文件中??????????????????slave@localhost[test]>?stop?slave;????slave@localhost[test]>?change?master?to?????????????????????????->?master_user='repl',??????????????->?master_password='replpwd';???Query?OK,?0?rows?affected,?2?warnings?(0.00?sec)????--修改密碼后，從庫狀態(tài)正常，以下檢查結(jié)果不再列出??slave@localhost[test]>?start?slave;????--查看master.info，密碼已更改且為名文??slave@localhost[(none)]>?system?grep?repl?/data/inst3506/data3506/master.info??repl??replpwd??2、更換復(fù)制賬戶及密碼[sql] view plaincopyprint?master@localhost[test]>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl2'@'192.168.1.177'?IDENTIFIED?BY?'Repl2';??Query?OK,?0?rows?affected?(0.00?sec)??????slave@localhost[test]>?stop?slave;??Query?OK,?0?rows?affected?(0.28?sec)????master@localhost[test]>?insert?into?tb1?values(3,'jack');??Query?OK,?1?row?affected?(0.00?sec)????slave@localhost[test]>?change?master?to???????->?MASTER_USER='repl2',??????->?MASTER_PASSWORD='Repl2';??Query?OK,?0?rows?affected,?2?warnings?(0.01?sec)????slave@localhost[test]>?system?more?/data/inst3506/data3506/master.info??23??inst3406bin.000001??3294834??192.168.1.177??repl2??Repl2??3406????..........????slave@localhost[test]>?start?slave;??Query?OK,?0?rows?affected?(0.01?sec)????slave@localhost[test]>?select?*?from?tb1?where?id=3;??+------+------+??|?id???|?name?|??+------+------+??|????3?|?jack?|??+------+------+??1?row?in?set?(0.00?sec)????slave@localhost[(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、關(guān)于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_PASSWORD='new3cret';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_POS=4 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)

小編推薦：欲學(xué)習(xí)電腦技術(shù)、系統(tǒng)維護、網(wǎng)絡(luò)管理、編程開發(fā)和安全攻防等高端IT技術(shù)，請 點擊這里注冊賬號，公開課頻道價值萬元IT培訓(xùn)教程免費學(xué)，讓您少走彎路、事半功倍，好工作升職加薪！

免責(zé)聲明：本站系公益性非盈利IT技術(shù)普及網(wǎng)，本文由投稿者轉(zhuǎn)載自互聯(lián)網(wǎng)的公開文章，文末均已注明出處，其內(nèi)容和圖片版權(quán)歸原網(wǎng)站或作者所有，文中所述不代表本站觀點，若有無意侵權(quán)或轉(zhuǎn)載不當(dāng)之處請從網(wǎng)站右下角聯(lián)系我們處理，謝謝合作！

總結(jié)

以上是生活随笔為你收集整理的mysql 复制用户_MySQL修改复制用户及密码的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。