0

I have two master in replication

left master -> 5.5.47-log MySQL Community Server (GPL)
rigth master ->5.5.47-log MySQL Community Server (GPL)

"left master"

mysql> create table test.left (a int);
mysql> insert into test.left values (1),(2),(3);commit;
mysql> select * from test.left;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

when I check in "right master",table structure can be found but no data exists

mysql> select * from test.left;
Empty set (0.00 sec)

I checked "left master" binlog and I can find this DML "insert into test.left values (1),(2),(3);"

[root@backup_master logs]# mysqlbinlog --no-defaults relay-bin.000035|grep "insert" -3
DELIMITER /*!*/;
# at 4
--
# at 416
#160519 14:03:34 server id 132  end_log_pos 374         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1463637814/*!*/;
insert into test.left values (1),(2),(3)
/*!*/;
# at 520
#160519 14:03:34 server id 132  end_log_pos 401         Xid = 14

I checked "right master " relay log and I can also find this "insert statment"

[root@backup_master logs]# mysqlbinlog --no-defaults relay-bin.000035|grep -10 "insert" 
create table test.left (a int)
/*!*/;
# at 347
#160519 14:03:34 server id 132  end_log_pos 270         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1463637814/*!*/;
BEGIN
/*!*/;
# at 416
#160519 14:03:34 server id 132  end_log_pos 374         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1463637814/*!*/;
insert into test.left values (1),(2),(3)
/*!*/;
# at 520
#160519 14:03:34 server id 132  end_log_pos 401         Xid = 14
COMMIT/*!*/;
DELIMITER ;

and "show slave status","error log" and "show processlist" everything is ok!

"right" master

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.233.132
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 401
               Relay_Log_File: relay-bin.000035
                Relay_Log_Pos: 547
        Relay_Master_Log_File: mysql-bin.000009
             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: 401
              Relay_Log_Space: 843
              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: 132
1 row in set (0.00 sec)

"right" master

mysql> show processlist;
+----+-------------+-----------------------+------+-------------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host                  | db   | Command     | Time    | State                                                                       | Info             |
+----+-------------+-----------------------+------+-------------+---------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |                       | NULL | Connect     | -750466 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user |                       | NULL | Connect     |    3770 | Waiting for master to send event                                            | NULL             |
|  3 | root        | localhost             | NULL | Query       |       0 | NULL                                                                        | show processlist |
|  5 | repl        | 192.168.233.132:56047 | NULL | Binlog Dump |     860 | Master has sent all binlog to slave; waiting for binlog to be updated       | NULL             |
+----+-------------+-----------------------+------+-------------+---------+-----------------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

but WHY my time is -750466?

create table ,create database(DDL statement) is no problem! but insert data is not OK!!

"left master" my.cnf

#replication
server-id=132
log-bin=/data/mysql3306/logs/mysql-bin
log-bin-index=/data/mysql3306/logs/mysql-bin.index
binlog_format=mixed
#plugin-dir=/usr/local/mysql/lib/plugin/
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout= 10000

relay-log=/data/mysql3306/logs/relay-bin
relay-log-index=/data/mysql3306/logs/relay-bin.index
relay_log_info_file=/data/mysql3306/logs/relay-log.info

auto_increment_offset=1
auto_increment_increment=2

"right master" /etc/my.cnf

server-id=135
relay-log=/data/mysql3306/logs/relay-bin
relay-log-index=/data/mysql3306/logs/relay-bin.index
relay_log_info_file=/data/mysql3306/logs/relay-log.info
log-error=/data/mysql3306/logs/error.log

#plugin-dir=/usr/local/mysql/lib/plugin/
#rpl_semi_sync_slave_enabled


log-bin=/data/mysql3306/logs/mysql-bin
log-bin-index=/data/mysql3306/logs/mysql-bin.index
binlog_format=mixed

auto_increment_offset=2
auto_increment_increment=2

sync_binlog=1

when I try to stop "right master" ,an error occurs

mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

but I can't find any locks!

2
  • You -- your current connection -- is in a transaction or has locks... which may also be why you don't yet see the rows. Have you disabled autocommit, possibly? Regarding the negative time, is (or was) either server clock discrepant from the other? May 19 '16 at 22:52
  • yes,that's my autocommit problem! thank you
    – LawrenceLi
    May 21 '16 at 3:37
0

If you have autocommit disabled, you're always in a transaction, which would cause the error you see when trying to manage replication... and could prevent you from seeing recent DML depending on the state of your MVCC read view and isolation level.

1
  • A strong argument against ever turning off autocommit !
    – Rick James
    May 29 '16 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.