Linux下Mysql主從服務(wù)器雙向同步

1、Linux 下 Mysql主、從雙向同步注意：（一般建議每天手動(dòng)備份數(shù)據(jù)。）主、從雙向同步是實(shí)時(shí)性的，所以操作數(shù)據(jù)庫時(shí)要謹(jǐn)慎，以免數(shù)據(jù)丟失。使用工具：Putty、SecureCRT等遠(yuǎn)程工具適用系統(tǒng)：Linux 系列系統(tǒng)操作過程：1.兩臺(tái)安裝好數(shù)據(jù)庫服務(wù)器主機(jī)，互相可以訪問（由于MySQL不同版本之間的(二進(jìn)制日志)binlog格式可能會(huì)不一樣，因此最好的搭配組合是Master的MySQL版本和Slave的版本相同，或者Slave版本高于Master的版本。）例如：Master: DaoBiDaoA （10.135.28.112）Slave: DaoBiDaoB （10.132.21

2、.19）2.設(shè)置Master服務(wù)器在Mysql配置文件f （一般是存放在 /etc/f ） 中，找到 server-id 編輯123server-id        = 1binlog-do-db     = daobidaobinlog-ignore-db = mysql其中：server-id 是設(shè)置數(shù)據(jù)庫服務(wù)編號(hào)，binlog-do-db 是設(shè)置需要記錄二進(jìn)制日志的數(shù)據(jù)庫（如果是多個(gè)數(shù)據(jù)庫需要記錄二進(jìn)制日志，就在添加一條此信息），binlog-ignore-db 是設(shè)置

3、不需要記錄二進(jìn)制日志的數(shù)據(jù)庫。注意：需要開啟log-bin二進(jìn)制日志文件1log-bin=mysql-bin啟動(dòng)Mysql服務(wù)，添加同步數(shù)據(jù)庫服務(wù)用戶信息12345678910111213mysql> CREATE USER 'daobidao''10.132.21.19' IDENTIFIED BY 'daobidao'Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON daobidao.* TO 'daobidao''10.132.21

4、.19'Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES ;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    53Current database: * NONE * Query OK, 0 rows affected (0.00 sec)其中：命令： CREATE USER 'username&

5、#39;'host' IDENTIFIED BY 'password'  是創(chuàng)建用戶；例如命令：CREATE USER 'daobidao''10.132.21.19' IDENTIFIED BY 'daobidao'  創(chuàng)建用戶daobidao，并且指定允許登錄ip地址為10.132.21.19（此ip地址為Slave的ip地址），密碼為daobidao。GRANT ALL ON daobidao.* TO 'daobidao''10.132.21.19' 此命

6、令是授權(quán)daobidao用戶對數(shù)據(jù)庫daobidao有所有權(quán)限。3.配置Slave服務(wù)器f12345678910server-id        = 2binlog-do-db     = daobidaobinlog-ignore-db = mysql master-host      = 10.135.28.112master-user      = daobidaomas

7、ter-password  = daobidaomaster-port      = 3306replicate-ignore-db = mysql  replicate-do-db = daobidao其中：master-host = 10.135.28.112 是設(shè)置Master的服務(wù)器地址，master-user = daobidao 同步到用戶名，master-password = daobidao 同步用戶的密碼，master-port = 3306 Master的端口，replicate-ignore-db =

8、mysql 指定不進(jìn)行同步的數(shù)據(jù)庫，replicate-do-db = daobidao 指定進(jìn)行同步的數(shù)據(jù)庫，如需要同步多個(gè)數(shù)據(jù)庫，就寫多條此信息。4.查看Master的數(shù)據(jù)庫信息mysql> show master status; 12345678910No connection. Trying to reconnect.Connection id:    1Current database: * NONE * +-+-+-+-+| File       

9、      | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000007 |     6520 | daobidao     | mysql            |+-+-+-+-+1 row in set (0.00 sec)其中：需要看到是File和Position的

10、記錄信息。5.Slave設(shè)置同步（此步操作完成，就實(shí)現(xiàn)了Slave同步Master數(shù)據(jù)庫）1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556mysql> slave stop;Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='10.135.28.112',  master_user='da

11、obidao', master_password='daobidao', master_log_file='mysql-bin.000007', master_log_pos=6520; ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    43Current database: * NONE * Query OK, 0 rows affected (0.14 s

12、ec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG;* 1. row *               Slave_IO_State: Waiting to reconnect after a failed master event read    

13、0;             Master_Host: 10.135.28.112                  Master_User: daobidao           

14、;       Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000007 

15、0;        Read_Master_Log_Pos: 6520               Relay_Log_File: AY130424102832Z-relay-bin.000001              

16、60; Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000007             Slave_IO_Running: No            Slave_SQL_Running:

17、 Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:     

18、0;  Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0     

19、60;             Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 6520 

20、;             Relay_Log_Space: 106              Until_Condition: None               Until_

21、Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File: 

22、0;          Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:     &#

23、160;          Master_SSL_Key:         Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0

24、                Last_IO_Error:                Last_SQL_Errno: 0             

25、0; Last_SQL_Error: 1 row in set (0.00 sec)一般主要看到：Slave_IO_Running: Yes  Slave_SQL_Running: Yes  如果都是yes，那代表已經(jīng)在同步。但從以上信息可以看出： Slave_IO_Running: No 這個(gè)同步存在問題?？梢圆榭磎ysql的日志分析：1234130605  1:19:11 Note Slave I/O thread:Failed reading log event, reconnecting to retry,log 'm

26、ysql-bin.000007' at position 6520130605  1:19:11 ERROR Error reading packet from server: Access denied;  you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227)從錯(cuò)誤日志可以看出，Salve數(shù)據(jù)庫使用的用戶缺少權(quán)限，在Master數(shù)據(jù)庫中操作：1234567mysql> GRANT REPLICATION SLAVE ON *.*  TO &#

27、39;daobidao''10.132.21.19'                 ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    105Current database: * NONE * Query OK, 0 rows

28、 affected (0.00 sec)在訪問Salver數(shù)據(jù)庫：1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950mysql> slave stop;Query OK, 0 rows affected (0.00 sec) mysql> slave start;        Query OK, 0 rows affected (0.00 sec) mysql&

29、gt; show slave statusG;* 1. row *               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.135.2

30、8.112                  Master_User: daobidao                  Master_Port: 3306       

31、;         Connect_Retry: 60              Master_Log_File: mysql-bin.000007          Read_Master_Log_Pos: 6520    &#

32、160;          Relay_Log_File: AY130424102832Z-relay-bin.000002                Relay_Log_Pos: 251        Relay_Master_Log_File: mys

33、ql-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_

34、Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:

35、   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:  

36、;                Skip_Counter: 0          Exec_Master_Log_Pos: 6520              Relay_Log_Space: 41

37、6              Until_Condition: None               Until_Log_File:               

38、0; Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:   

39、0;           Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:    

40、     Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_

41、IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: 1 row in set (0.00 sec) ERROR: No query specified以上就實(shí)現(xiàn)了從數(shù)據(jù)庫同步主數(shù)據(jù)庫數(shù)據(jù)；6.設(shè)置

42、主數(shù)據(jù)庫，實(shí)現(xiàn)雙向同步1）Salve數(shù)據(jù)庫添加同步數(shù)據(jù)庫用戶123456789101112131415161718192021mysql> CREATE USER 'daobidao''10.135.28.112' IDENTIFIED BY 'daobidao'             ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying

43、to reconnect.Connection id:    76Current database: * NONE * Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON daobidao.* TO 'daobidao''10.135.28.112'ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id: 

44、;   77Current database: * NONE * Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE ON *.*  TO 'daobidao''10.135.28.112'Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES ;Query OK, 0 rows affected (0.00 sec)2）配置Master數(shù)據(jù)庫

45、fserver-id = 112345678910binlog-do-db     = daobidaobinlog-ignore-db = mysql master-host      = 10.132.21.19master-user      = daobidaomaster-password  = daobidaomaster-port      = 3306 repl

46、icate-ignore-db = mysql  replicate-do-db     = daobidao3 ）查看Salve數(shù)據(jù)庫信息123456789101112mysql> show master status;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    5Current database: * NONE * +-+-+-+-+| Fil

47、e             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000007 |      106 | daobidao     | mysql            |+-+-+-

48、+-+1 row in set (0.00 sec)4）Master設(shè)置同步12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364mysql> slave stop;Query OK, 0 rows affected (0.01 sec) mysql> change master to master_host='10.132.21.19', master_user='

49、daobidao', master_password='daobidao', master_log_file='mysql-bin.000007', master_log_pos=106; ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    5Current database: * NONE * Query OK, 0 rows affected (0.02 s

50、ec) mysql> slave start;Query OK, 0 rows affected (0.00 sec) mysql> show slave status G; ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect.Connection id:    8Current database: * NONE * * 1. row *      

51、60;        Slave_IO_State: Waiting for master to send event                  Master_Host: 10.132.21.19           &#

52、160;      Master_User: daobidao                  Master_Port: 3306                Connect_Retry: 60&

53、#160;             Master_Log_File: mysql-bin.000007          Read_Master_Log_Pos: 106               Relay_Log_

54、File: AY1212111202285f63122-relay-bin.000002                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000007         &

55、#160;   Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: daobidao         

56、; Replicate_Ignore_DB: mysql           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0