1

I want to set up a Master-Slave Replication. Therefore I followed the MySQL manual. Once I was I wanted to test it. I've let me show the slave status with was as following;

Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 326
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1410
              Relay_Log_Space: 2176
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1051
               Last_SQL_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

As you can see, the slave was waiting. That's the reason, why I made a test INSERT on the MASTER. As the INSERT did not show on my slave it made me think, that I was missing something while setting it up. Also if I want to uncomment the bind-address and restart the mysql-service, it fails to start.

Now I'll show you what I have done:

1. Edit my.cnf on Master -> 
set server-id = 1
set binlog_do_db = database name
2. Restart mysql service on Master
3. Create user for replication:
CREATE USER 'slave'@'master IPv4' IDENTIFIED BY 'password';
4. Grant replication slave:
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
5. Restart mysql service on Master

Now on to the slave:

1. Edit my.cnf on Slave -> 
set server-id = 2
2. Restart mysql service on Slave
3. Stop slave
4. CHANGE MASTER TO MASTER_HOST='Master IPv4', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=827;
5. Start slave

After this configuration the slave was showing: Slave_IO_State: Waiting for master to send event, but id did not receive any changes from the Master. Would be glad if anyone could help me with this.

I use MySQL 5.5.47 and Ubuntu 14.04.1

| improve this question | | | | |
  • @JimParks the master is actually a copy of the slave. I know it sounds weird, but just assume, its a new setup. Shouldn't make any difference right? – XtremeBaumer Mar 10 '17 at 8:11
  • @JimParks i know, but i also stated below the answer what i have done in the end. i chose the answer, because its detailed and structured and also includes a good part of what i did to get it working – XtremeBaumer Mar 10 '17 at 12:50
  • Do you have binlog_do/ignore_...? What USE existed before the DROP? – Rick James Mar 13 '17 at 21:39
  • @RickJames the table test was only on master and not replicated to the slave. and binlog_do/ignore were set properly, meaning do was set and ignore not – XtremeBaumer Mar 14 '17 at 12:43
  • @XtremeBaumer - But, the binlog_ignore filtering applies to what you say in a USE statement. (This is a common pitfall.) It does not look at, say FROM db.tbl syntax. – Rick James Mar 14 '17 at 14:53
1

-I think the Bind-Address in the my.cnf can be set to 0.0.0.0 or <Servers_ip>

-From what I understand your replication never once worked? Your master is sending the files to the salve I see that in the Last_SQL_Error: Error 'Unknown table ...

So what the problem is with the configuration of the salve reading the log or you did not dump the database from the master to the salve.

1- Dumping the database from the master to the salve

On your master in mysql

FLUSH PRIVILEGES;
USE db_name;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Then copy the Master's values. And now open a new window/terminal without closing the current window you are using. then backup your database

mysqldump -u root -p --opt db_name > BACKUP_file.sql

Now close this second window/terminal. Then unlock the tables

UNLOCK TABLES; \q;

Now you should recreate the the slaves database by typing the following commands

DROP DATABASE db_name
CREATE DATABASE db_name

2- Configuration of the salve At this point I believe both databases are the same but the replication is not reading the master's log files at the right position so Stop the salve, Reset it , Update the MASTER_LOG_FILE and MASTER_LOG_POS And Start it again (This is the safest way I assume should work).

STOP SLAVE;
RESET SLAVE;

CHANGE MASTER TO     MASTER_HOST='masters_ip', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  999;

The values in the MASTER_LOG_POS and MASTER_LOG_FILE are the ones you copied from your master server

START SLAVE;

Now just check if Slave_SQL_Running and Slave_IO_Running are yes then Try your insert again.

Now that I told you the long story the other way would be to try fixing the slave without recreating the database... read the end of the error log file then make your slave either 1) skip the SQL statements that gives you a problem or 2) Stop the Slave, update the MASTER_LOG_FILE and MASTER_LOG_POS to the last working position ... which can be found in the error log file. log_error = /var/log/mysql/error.log its defined in the my.cnf file. I hope this is helpful. other resources

| improve this answer | | | | |
  • very nice and detailed answer. i ended up simply resetting slave and master and reestablishing the connection. now its working – XtremeBaumer Mar 10 '17 at 8:25
1

On the Slave:

  • stop slave
  • reset slave

On the Master

  • reset master

On the Slave

  • start slave
  • show slave status

If you can include info like which version/flavour of MySQL and OS you are using that will help people responding to your query.

| improve this answer | | | | |
  • 1
    You are missing the Change Master to statement once slave is reset. – XtremeBaumer Mar 10 '17 at 8:24

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.