1

I'm trying to set up a one way MySQL replication. All sources I looked for errors seemed fine but no data is transferred. The following sites couldn't help me:

This are my preparations for the replication:

Master config changes:

#bind-address       = 127.0.0.1
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
binlog_do_db        = records;

Slave config changes:

server-id       = 2
replicate_do_db     = records;

I know that *_do_db is considered as unstable often and how I need to deal with it.

On master:

create user repl@y.y.y.y identified by "xxx";
grant replication slave on *.* to repl@y.y.y.y;

On slave:

change master to master_host="x.x.x.x",
                 master_user="repl",
                 master_password="xxx",
                 master_log_file="mysql-bin.000002",
                 master_log_pos=83115;
slave start;

Ofcourse I verified the binlog file and pos on the master before. The master is already on a further state than the slave so the slave should replicate from an older binlog on the master. When the slave is started the binlog file and pos jumps to the newest position on the master but doesn't apply changes. After the jump no changes are applied either.

Here is everything I tested:

On the master:

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      107 | records;     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show processlist;
+-----+------+-----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+
| Id  | User | Host                  | db      | Command     | Time   | State                                                                 | Info             |
+-----+------+-----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+
|  84 | repl | y.y.y.y:39220 | NULL    | Binlog Dump | 192333 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 229 | tw   | localhost:49459       | records | Sleep       |   2909 |                                                                       | NULL             |
| 231 | root | localhost             | records | Query       |      0 | NULL                                                                  | show processlist |
| 233 | tw   | localhost:49461       | records | Sleep       |    494 |                                                                       | NULL             |
+-----+------+-----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

On the slave:

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000014
                Relay_Log_Pos: 210
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: records;
          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: 107
              Relay_Log_Space: 513
              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 processlist;
+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db      | Command | Time   | State                                                                       | Info             |
+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL    | Connect |  48517 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user |           | NULL    | Connect | 192784 | Waiting for master to send event                                            | NULL             |
| 50 | root        | localhost | records | Query   |      0 | NULL                                                                        | show processlist |
+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Hi hope I gave enough information and you can help me. Thanks for help in advance!

closed as off-topic by Jack Douglas Nov 30 '15 at 9:36

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • what display - show process list; on Slave server? do You have some information in error log? and what version of MySQL? – a_vlad Nov 29 '15 at 20:55
  • Please flag for re-opening if it turns out that the "records;" typo is not the issue. – Jack Douglas Nov 30 '15 at 9:36
  • @JackDouglas About "closed" -- Yes, it is a typo, but he needed help in finding it. – Rick James Dec 12 '15 at 22:38
  • @Rick he did, but are you suggesting the question should be re-opened? Because it's a typo, I think it is "…unlikely to help other users…" (see the close reason above). That doesn't mean we shouldn't help the OP and it's good he got an answer, it just means we don't want the question to rank on Google (it won't disappear with an up-voted accepted answer). – Jack Douglas Dec 13 '15 at 12:16
1

My.cnf says replicate-do-db:

"records;" 

You don't need that extra semi-colon as far as I know.

  • When you say "You don't need that extra semi-colon", are you suggesting that it is the cause of the problem? – Jack Douglas Nov 30 '15 at 7:24
  • Yes.. if you see show slave status output it is expecting a database name with semicolon. – mysql_user Nov 30 '15 at 7:26

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