Linux下Mysql主从服务器双向同步

1、Linux 下 Mysql主、从双向同步注意：（一般建议每天手动备份数据。）主、从双向同步是实时性的，所以操作数据库时要谨慎，以免数据丢失。使用工具：Putty、SecureCRT等远程工具适用系统：Linux 系列系统操作过程：1.两台安装好数据库服务器主机，互相可以访问（由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同，或者Slave版本高于Master的版本。）例如：Master: DaoBiDaoA （10.135.28.112）Slave: DaoBiDaoB （10.132.21

2、.19）2.设置Master服务器在Mysql配置文件f （一般是存放在 /etc/f ） 中，找到 server-id 编辑123server-id        = 1binlog-do-db     = daobidaobinlog-ignore-db = mysql其中：server-id 是设置数据库服务编号，binlog-do-db 是设置需要记录二进制日志的数据库（如果是多个数据库需要记录二进制日志，就在添加一条此信息），binlog-ignore-db 是设置

3、不需要记录二进制日志的数据库。注意：需要开启log-bin二进制日志文件1log-bin=mysql-bin启动Mysql服务，添加同步数据库服务用户信息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'  是创建用户；例如命令：CREATE USER 'daobidao''10.132.21.19' IDENTIFIED BY 'daobidao'  创建用户daobidao，并且指定允许登录ip地址为10.132.21.19（此ip地址为Slave的ip地址），密码为daobidao。GRANT ALL ON daobidao.* TO 'daobidao''10.132.21.19' 此命

6、令是授权daobidao用户对数据库daobidao有所有权限。3.配置Slave服务器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 是设置Master的服务器地址，master-user = daobidao 同步到用户名，master-password = daobidao 同步用户的密码，master-port = 3306 Master的端口，replicate-ignore-db =

8、mysql 指定不进行同步的数据库，replicate-do-db = daobidao 指定进行同步的数据库，如需要同步多个数据库，就写多条此信息。4.查看Master的数据库信息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设置同步（此步操作完成，就实现了Slave同步Master数据库）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，那代表已经在同步。但从以上信息可以看出： Slave_IO_Running: No 这个同步存在问题。可以查看mysql的日志分析：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)从错误日志可以看出，Salve数据库使用的用户缺少权限，在Master数据库中操作：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数据库：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以上就实现了从数据库同步主数据库数据；6.设置

42、主数据库，实现双向同步1）Salve数据库添加同步数据库用户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数据库

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数据库信息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设置同步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