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:
- http://dev.mysql.com/doc/refman/5.7/en/replication-problems.html
- mysql replication successful but slave not replicating
- https://serverfault.com/questions/194947/mysql-says-replication-is-fine-but-data-is-not-copied
- https://stackoverflow.com/questions/8733801/mysql-slave-not-replicating
- https://stackoverflow.com/questions/20710875/mysql-replication-is-not-working-master-slave
- https://stackoverflow.com/questions/11429561/mysql-replication-is-not-working
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 [email protected] identified by "xxx";
grant replication slave on *.* to [email protected];
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!
