☰

掌握

tag-icon • tag-icon linux mysql
掌握

我按规矩设置了主服务器和从服务器。我LOAD DATA FROM MASTER;在从服务器上运行了初始程序，运行正常。但是当我将数据插入主服务器时，数据根本没有复制到从服务器上。我已经尝试重新启动主服务器和从服务器的 mysqld 进程，并在从服务器上执行“从服务器停止/从服务器启动”。发生了什么？

掌握

配置：

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
  binlog_do_db            = pchelp
  binlog_ignore_db        = mysql
  binlog_ignore_db        = test

mysql>从 pchelp.test_table 中选择*；

+----+---------+
| id | sometxt |
+----+---------+
|  1 | x       |
|  2 | x       |
|  3 | y       |
|  4 | z       |
|  5 | p       |
|  6 | i       |
+----+---------+
6 rows in set (0.00 sec)

mysql> 显示主状态 \G

*************************** 1. row ***************************
            File: mysql-bin.000009
        Position: 106
    Binlog_Do_DB: pchelp
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

奴隶

配置：

server-id               = 2
  master-host               = hidden.x.xx
  master-user               = replication
  master-password           = hidden
  master-port               = 3308
  replicate_do_db           = pchelp

mysql>从 pchelp.test_table 中选择*；

+----+---------+
| id | sometxt |
+----+---------+
|  1 | x       |
|  2 | x       |
|  3 | y       |
|  4 | z       |
|  5 | p       |
|  6 | i       |
+----+---------+
6 rows in set (0.00 sec)

mysql> 显示从属状态 \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hidden.x.xx
                  Master_User: replication
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pchelp
          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: 106
              Relay_Log_Space: 407
              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.01 sec)

掌握

插入 pchelp.test_table (id,sometxt) 值 (7,'q');

Query OK, 1 row affected (0.00 sec)

mysql>从 pchelp.test_table 中选择*；

+----+---------+
| id | sometxt |
+----+---------+
|  1 | x       |
|  2 | x       |
|  3 | y       |
|  4 | z       |
|  5 | p       |
|  6 | i       |
|  7 | q       |
+----+---------+
7 rows in set (0.01 sec)

mysql> 显示主状态 \G

*************************** 1. row ***************************
            File: mysql-bin.000009
        Position: 106
    Binlog_Do_DB: pchelp
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

从属（在主服务器上插入后）

mysql>从 pchelp.test_table 中选择*；

+----+---------+
| id | sometxt |
+----+---------+
|  1 | x       |
|  2 | x       |
|  3 | y       |
|  4 | z       |
|  5 | p       |
|  6 | i       |
+----+---------+
6 rows in set (0.01 sec)

mysql> 显示从属状态 \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: hidden.x.xx
                  Master_User: replication
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pchelp
          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: 106
              Relay_Log_Space: 407
              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)

有人知道哪里可能出错了吗？用户“复制”具有完全权限（在主服务器和从服务器上都运行了该权限）；

grant replication slave on *.* to replication@'%' identified by 'hidden';
GRANT ALL PRIVILEGES ON pchelp.* TO replication;

再次，LOAD DATA FROM MASTER;命令运行正常..我不明白。

答案1

或者binlog_do_db可能binlog_ignore_db不会按照您想象的方式工作。 以下是 Baron Schwarz 解释的原因。我的猜测是，在编写该命令之前，您要么写了 ，USE mysql要么写了USE test。由于这两个数据库被忽略，因此您在使用该数据库时运行的任何语句（即使它们写入另一个数据库）都将被忽略。

主服务器甚至没有将该INSERT语句写入二进制日志的一个提示来自SHOW MASTER STATUS\G命令的输出。语句之前和之后均有提示INSERT：

Position: 106

从属设备也具有相同的立场，这解释了为什么它认为自己工作正常：

Read_Master_Log_Pos: 106

建议：删除binlog_do_db和binlog_ignore_db配置选项。如果需要过滤，请在从属服务器上执行。

相关内容