Search code examples
mysqldatabasereplication

MYsql replication issues. "Error Table xxx.xxx doesnt exist.. Cant even start replication


Spent two days so far looking though stack overflow answers and google and I just cannot get it working..

Im trying to setup a master / slave replication in mysql/mariadb.. yet when I start the replication. Its just errors on the slave saying tables dont exist.

  • Do I need to create the database and tables first?
  • If so, what happens if a new table is made on the master? will this break replication?

This is the current state of the slave:

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: somedomain.com
                  Master_User: someuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 63687969
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 382
        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 'xxxxx.xxxx' doesn't exist' on query. Default database: 'xxxxxxxxxxxx'. Query: 'UPDATE xxxx SET lastused = NOW(), lingertime = 7 WHERE siteid = 'xxxxxxxxxxx''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 98
              Relay_Log_Space: 63763807
              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: 1146
               Last_SQL_Error: Error 'Table 'xxxxxxxx.xxxxx' doesn't exist' on query. Default database: 'xxxxxxx'. Query: 'UPDATE xxxxx SET lastused = NOW(), lingertime = 7 WHERE siteid = 'xxxxxxxxxx''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

Solution

  • Do I need to create the database and tables first?

    Yes, you have to do that first. You must have the same schemas and tables as you have on master.

    If so, what happens if a new table is made on the master? will this break replication?

    Every command you execute on master will be replicated on slave as well. This includes CREATE statements for tables and schemas. So, you won't have any problem.