MySQL社区

标题: mysql5.5双节点互为主从遇到的问题 [打印本页]

作者: qinghan 时间: 2013-7-31 11:49
标题: mysql5.5双节点互为主从遇到的问题
问题:
当我在一个节点上
create database php_db;
show databases;
这个时候是有php_db的
但是slave stop;
start slave;
flush privileges;
show databases;
这个php_db就没有了
跑到对面节点去了
究竟是哪里出了问题呢?!

配置情况如下
节点一(在这个节点执行的创建php_db命令)
mysql> slave stop;
mysql> create database php_db;
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| php_db          |
| test             |
+--------------------+
5 rows in set (0.01 sec)
mysql> start slave;
mysql> flush privileges;
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 172.16.18.50
               Master_User: master1_1
               Master_Port: 1869
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
      Read_Master_Log_Pos: 3634
            Relay_Log_File: xiaohe-relay-bin.000019
            Relay_Log_Pos: 253
      Relay_Master_Log_File: mysql-bin.000003
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
            Replicate_Do_DB: php_db
      Replicate_Ignore_DB: mysql
         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: 3634
            Relay_Log_Space: 674
            Until_Condition: None
            Until_Log_File:
            Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
         Master_SSL_Cipher:
            Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
            Last_SQL_Errno: 0
            Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| test             |
+--------------------+
4 rows in set (0.00 sec)

节点二
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| php_db          |
| test             |
+--------------------+
5 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 172.16.18.51
               Master_User: master1_2
               Master_Port: 1869
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000006
      Read_Master_Log_Pos: 3018
            Relay_Log_File: hanxin-relay-bin.000015
            Relay_Log_Pos: 415
      Relay_Master_Log_File: mysql-bin.000006
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
            Replicate_Do_DB: php_db
      Replicate_Ignore_DB: mysql
         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: 3018
            Relay_Log_Space: 718
            Until_Condition: None
            Until_Log_File:
            Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
         Master_SSL_Cipher:
            Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
            Last_SQL_Errno: 0
            Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
         Master_Server_Id: 2
1 row in set (0.00 sec)

求高人指点

作者: kider 时间: 2013-8-1 11:00
没详细看，为啥要在写数据时停slave，刷权限，然后写了启slave?
给几个提示吧：
注意下参数：log-slave-updates
检查下用户权限
另外这种互为主从一般不用，因为可能会产生数据冲突问题，如果你能完全控制数据写入的单点还好。

欢迎光临 MySQL社区 (http://www.mysqlpub.com/)