Re: replication, table db already exists

[Jim Grill]

jabbott@stripped wrote:
> Hello All,
> Here is a problem I have been trying to debug for a few days.  I had a mysql server
> running for about a month.  I did a mysql dump of all the tables on the master and setup a
> second machine to be the slave, loading all the tables.  I set the replication stuff up in
> the slave my.cnf.  Now as soon as I start the slave I get good data flow between the two
> machines but the slave does not update with the error below.  Is this because I did not do
> a binary snapshot of the master machine to setup the slave?  I originally was not going to
> setup replication, just going to do dumps of tables, but then changed directions.
> 
> Thanks!  If you need more info, please ask.
> 
> --ja
> 
> mysql> show slave status \G;
> *************************** 1. row ***************************
>              Slave_IO_State: Waiting for master to send event
>                 Master_Host: 192.168.1.100
>                 Master_User: repster
>                 Master_Port: 3306
>               Connect_Retry: 60
>             Master_Log_File: rhea-bin.000017
>         Read_Master_Log_Pos: 405948310
>              Relay_Log_File: dione-relay-bin.000002
>               Relay_Log_Pos: 121
>       Relay_Master_Log_File: rhea-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: 1050
>                  Last_Error: Error 'Table 'db' already exists' on query.
> Default database: 'mysql'. Query: ' CREATE TABLE db (   Host char(60) binary
> DEFAULT '' NOT NULL,   Db char(64) binary DEFAULT '' NOT NULL,   User
> char(16) binary DEFAULT '' NOT NULL,   Select_priv enum('N','Y') DEFAULT 'N'
> NOT NULL,   Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,   Update_priv
> enum('N','Y') DEFAULT 'N' NOT NULL,   Delete_priv enum('N','Y') DEFAULT 'N'
> NOT NULL,   Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,   Drop_priv
> enum('N','Y') DEFAULT 'N' NOT NULL,   Grant_priv enum('N','Y') DEFAULT 'N'
> NOT NULL,   References_priv enum('N','Y') DEFAULT 'N' NOT NULL,   Index_priv
> enum('N','Y') DEFAULT 'N' NOT NULL,   Alter_priv enum('N','Y') DEFAULT 'N'
> NOT NULL,   Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
> Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host
> (Host,Db,User), KEY User (User) ) comment='Database privileges''
>                Skip_Counter: 0
>         Exec_Master_Log_Pos: 79
>             Relay_Log_Space: 11454541057
>             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: 12172794
> 1 row in set (0.00 sec)
> 
> 
> 
I'm guessing but... It looks like you did not set the master log 
position properly. Your slave is at position 79 in the master log.  You 
can tell by looking at Relay_Log_Space and Seconds_Behind_Master (140 
days) that is it far behind the master.  That would account for the 
couple of months that the server was up before you decided to replicate it.

Did you get the log position of your snap shot and use `change master 
to` to sync the slave to the correct position?  You can either reset the 
master logs using RESET MASTER or you need to do a SHOW MASTER STATUS to 
get the log file position at the time of your snap shot.  When you set 
up the slave you can provide the log file position or, if you RESET 
MASTER, you can set MASTER_LOG_FILE='the_name_the_log_file' and 
MASTER_LOG_POS=4, which is the default start position for a fresh binary 
log.

Here is some good reading:
http://dev.mysql.com/doc/mysql/en/replication.html
http://dev.mysql.com/books/hpmysql-excerpts/ch07.html

Best of luck to you.

Regards,

Jim Grill