请教一个关于mysql同步的问题[已解决]

mchdba · 发表于 2011-11-14 16:14

本帖最后由 mchdba 于 2011-11-14 16:15 编辑

飞鸿无痕_cu 发表于 2011-11-14 15:47
你在mysql命令行直接测试就知道了，执行sql之前你先use到别的数据库，然后再执行相应的sql！

20是主机，40是从机

C:\Documents and Settings\Skyman.Man>mysql -h192.xxx.xxx.20 -uskyman.man -pxxxxgood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2066667
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| csf             |
| csf_check       |
| csf_cms          |
| csf_home          |
| csf_maec          |
| csf_private       |
| csf_pub          |
| csf_sector       |
| mysql             |
| reuters_dss       |
| reuters_interface  |
| reuters_rkd       |
+--------------------+
13 rows in set (0.00 sec)
mysql> use csf;
Database changed
mysql> insert into csf_cms.t(id,name) select 1,'name';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> exit
Bye
C:\Documents and Settings\Skyman.Man>mysql -h192.xxx.xxx.40 -uskyman.man -pxxxxgood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 263
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 192.168.250.20
               Master_User: rel
               Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000893
      Read_Master_Log_Pos: 1020576681
            Relay_Log_File: relay-bin.001146
            Relay_Log_Pos: 1020576826
      Relay_Master_Log_File: mysql-bin.000893
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
            Replicate_Do_DB:
      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: 1020576681
            Relay_Log_Space: 1020577018
            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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>

mchdba · 发表于 2011-11-14 16:16

本帖最后由 mchdba 于 2011-11-14 16:17 编辑

楼主，你看我的信息，我按照你的方法在生产环境测试了下，我的从库可是双Yes啊，没有报错啊，为什么呢？

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

飞鸿无痕_cu · 发表于 2011-11-14 16:22

mchdba 发表于 2011-11-14 16:09
哦，明白了，你在命令行直接操作啊，我说呢，应用中，没有出现这个问题啊！那不相当于dba自己操作吗？

...

我建议你用mysql命令行是最快的发现这个问题，我们应用中有一个业务在跑，然后发现主从数据库的数据不同步，然后在二进制log中追踪不同步的数据，才发现是没有use db的问题。

mchdba · 发表于 2011-11-14 16:24

飞鸿无痕_cu 发表于 2011-11-14 16:22
我建议你用mysql命令行是最快的发现这个问题，我们应用中有一个业务在跑，然后发现主从数据库的数据不同步 ...

恩，我刚刚用命令行，跑了一遍，我的从库还是没有报错，不知道我的试验过程跟你的过程有何差别呢？

飞鸿无痕_cu · 发表于 2011-11-14 16:25

本帖最后由飞鸿无痕_cu 于 2011-11-14 16:28 编辑

mchdba 发表于 2011-11-14 16:16
楼主，你看我的信息，我按照你的方法在生产环境测试了下，我的从库可是双Yes啊，没有报错啊，为什么呢？

...

我猜测应该是没有use db的语句直接被从服务器忽略了，当然不会报错。你可以通过追踪二进制log和从上复制的log进行比较

mchdba · 发表于 2011-11-14 16:26

飞鸿无痕_cu 发表于 2011-11-14 16:25
应该是没有use db的语句直接被从服务器忽略了，当然不会报错。你可以通过二进制log和从上复制的log进行比 ...

我用了，你看我的操作，先use csf库，然后往csf_cms库里面插入数据。

mysql> use csf;
Database changed
mysql> insert into csf_cms.t(id,name) select 1,'name';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

飞鸿无痕_cu · 发表于 2011-11-14 16:30

mchdba 发表于 2011-11-14 16:26
我用了，你看我的操作，先use csf库，然后往csf_cms库里面插入数据。

mysql> use csf;

那你在从上能查询到在主上插入的对应数据吗？

mchdba · 发表于 2011-11-14 16:32

飞鸿无痕_cu 发表于 2011-11-14 16:30
那你在从上能查询到在主上插入的对应数据吗？

可以的啊：

C:\Documents and Settings\Skyman.Man>mysql -h192.168.250.20 -uskyman.man -pmanchungood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2066667
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| csf             |
| csf_check       |
| csf_cms          |
| csf_home          |
| csf_maec          |
| csf_private       |
| csf_pub          |
| csf_sector       |
| mysql             |
| reuters_dss       |
| reuters_interface  |
| reuters_rkd       |
+--------------------+
13 rows in set (0.00 sec)
mysql> use csf;
Database changed
mysql> insert into csf_cms.t(id,name) select 1,'name';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> exit
Bye
C:\Documents and Settings\Skyman.Man>mysql -h192.168.250.40 -uskyman.man -pmanchungood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 263
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 192.168.250.20
               Master_User: rel
               Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000893
      Read_Master_Log_Pos: 1020576681
            Relay_Log_File: relay-bin.001146
            Relay_Log_Pos: 1020576826
      Relay_Master_Log_File: mysql-bin.000893
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
            Replicate_Do_DB:
      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: 1020576681
            Relay_Log_Space: 1020577018
            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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from csf_cms.t;
+----+------+
| id | name |
+----+------+
|  1 | name |
+----+------+
1 row in set (0.00 sec)
mysql>

飞鸿无痕_cu · 发表于 2011-11-14 16:35

mchdba 发表于 2011-11-14 16:32
可以的啊：

试一下登录上去后不执行use命令，然后直接执行插入看看从服务器还能同步不？

mchdba · 发表于 2011-11-14 16:42

飞鸿无痕_cu 发表于 2011-11-14 16:35
试一下登录上去后不执行use命令，然后直接执行插入看看从服务器还能同步不？

C:\Documents and Settings\Skyman.Man>mysql -h192.xxx.xxx.20 -uskyman.man -pxxxgood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2070307
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into csf_cms.t select 2,'name2';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from csf_cms.t;
+----+-------+
| id | name  |
+----+-------+
|  1 | name  |
|  2 | name2 |
+----+-------+
2 rows in set (0.01 sec)
mysql> eixt
-> exit
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'eixt
exit' at line 1
mysql> exit
Bye
C:\Documents and Settings\Skyman.Man>mysql -h192.xxx.xxx.40 -uskyman.man -pxxxgood
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 267
Server version: 5.1.53-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from csf_cms.t;
+----+-------+
| id | name  |
+----+-------+
|  1 | name  |
|  2 | name2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 192.168.250.20
               Master_User: rel
               Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000893
      Read_Master_Log_Pos: 1030729039
            Relay_Log_File: relay-bin.001146
            Relay_Log_Pos: 1030729184
      Relay_Master_Log_File: mysql-bin.000893
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
            Replicate_Do_DB:
      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: 1030729039
            Relay_Log_Space: 1030729376
            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:
1 row in set (0.02 sec)
ERROR:
No query specified
mysql>

[FAQ] 请教一个关于mysql同步的问题[已解决]

浏览过的版块