2

First of all let me say there are no CLEAR tutorials for how to do this on windows, I'm a programmer and have been using MySQL and PHP for years, and have never seen so many different answers when it comes to linux, and absolutely nothing for windows users.

Anyway I have been working for 2 weeks trying to solve this myself, and I am at the same place no matter what tutorial, method or whatever I try. Basically I have two MySQL Servers. One Master, and One Slave (setup through phpMyAdmin)

I have both servers online and they can see each other and are connected to each other, however when it comes to syncing under the Slave Status table in phpMyAdmin I am constantly getting errors saying tables don't exist. But they do. Now I'm not sure if I am supposed to completely import the databases from my master manually, and then it updates it or whatever (again no clear tutorials are out there for windows). Below is the table i copied from phpMyAdmin.

Slave_IO_State  Waiting for master to send event
Master_Host XX.XX.XX.XX
Master_User XXXXXXXX
Master_Port 3306
Connect_Retry   60
Master_Log_File mysql-bin.000012
Read_Master_Log_Pos 1221
Relay_Log_File  *servername*-relay-bin.000003
Relay_Log_Pos   253
Relay_Master_Log_File   mysql-bin.000001
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  1146
Last_Error  Error 'Table '*databasename*.*tablename*' doesn't exist' on query. Default database: '*databasename*'. Query: 'DELETE FROM *tablename* WHERE expires
Skip_Counter    0
Exec_Master_Log_Pos 107
Relay_Log_Space 211493
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 (blank but would like this setup)

I should also add that this database that supposedly has missing tables is working perfectly on the master for an active website.

Any help to point me in the right direction would be appreciated.

PunkIsDaFunk
  • 75
  • 1
  • 13
  • @Pat I I have tried your suggestion, however I'm also getting an additional error about a duplicate record, Thank you for your answer by the way, i've been dealing with this for more than 2 weeks now and have gotten absolutely no where...This is the error I'm getting:Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY '*****************'' However there is no user named "Replication" on the master... – PunkIsDaFunk Oct 24 '13 at 15:36
  • Where is the error showing? In Last_Error? Or are you trying to create that user? If you know the user doesn't exist you could DROP USER then create it again – Pat I Oct 24 '13 at 19:31
  • @PatI It's under last error, but clearing the error doesn't fix it, just gives another error, i spent a few hours yesterday, clearing the errors, deleting the replication user, and trying to reset the slave, and yet the same thing keeps happening. – PunkIsDaFunk Oct 25 '13 at 12:47

1 Answers1

0

You want to take a full backup of the master database and load it onto your slave or manually copy them over while the master isn't doing work. When you run your CHANGE MASTER command on the slave before starting it you want to specify the first binary log file that was created after your backup was taken like this:

CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx', MASTER_PORT=3306, MASTER_USER='user', MASTER_PASSWORD='*secret*', MASTER_LOG_FILE='mysql-bin.004388', MASTER_LOG_POS = 0

The Seconds_Behind_Master will automatically show once the slave doesn't have an error.

That is all there is to it.

Pat I
  • 56
  • 4
  • This is the error I'm getting:Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY '*****************'' However there is no user named "Replication" on the master... – PunkIsDaFunk Oct 24 '13 at 15:34
  • A similar issue is shown here where dropping the user and flushing privs corrects the error: http://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost Did this happen directly after you copied the backup and started the slave? – Pat I Nov 01 '13 at 21:12