Mysql – Need help with Master Slave Replication

master-slave-replicationMySQLreplication

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

Best Answer

-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

Related Question