List:Replication« Previous MessageNext Message »
From:Tim Dunphy Date:April 13 2011 7:54pm
Subject:replication is not automatic
View as plain text  
hello list

I am a new mysql admin and have setup master/slave replication for the first time.

I have tarred up each database in /var/lib/mysql and scp'd it to the same location on the
replication slave. After entering server-id's in the config for each server I restarted
the mysql service on each server. I then ran show master status on the master and noted
the log file and position and entered the correct values into 'change master to' on the
slave. 

 
 Initially, joy was had as I created tables on a test database on the server and saw them
on the slave when I ran show tables. It was great! :)

 However the next day I ran the same test on the master and the slave and did not see the
changes on the slave. I of course checked show slave status and everything seemed in
order. I then noticed that if I were to change information on the server and then run
STOP SLAVE and then START SLAVE on the replication host the changes would appear on the
slave database. 



## master db

mysql> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysqlmaster.000013 |   499353 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)





mysql> use snjh
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_snjh |
+----------------+
| a              |
| abcd           |
| acadaca        |
| b              |
| c              |
| d              |
| example        |
| ffff           |
| kkkk           |
| mmmmm          |
| newexample     |
| newprovidence  |
| t              |
| v              |
| w              |
+----------------+
15 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: snjh
Command: Sleep
   Time: 10120
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 8
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 10325
  State: Connecting to master
   Info: NULL
*************************** 3. row ***************************
     Id: 9
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 10325
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 4. row ***************************
     Id: 63
   User: root
   Host: localhost
     db: snjh
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
4 rows in set (0.00 sec)

http://pastie.org/1791242


## slave db


mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: virtcent09.example.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlmaster.000013
          Read_Master_Log_Pos: 312082
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 38385
        Relay_Master_Log_File: mysqlmaster.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 312082
              Relay_Log_Space: 122487
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)




mysql> use snjh
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_snjh |
+----------------+
| a              |
| abcd           |
| b              |
| c              |
| d              |
| example        |
| ffff           |
| kkkk           |
| mmmmm          |
| newexample     |
| newprovidence  |
| nnnn           |
| t              |
| u              |
| v              |
| w              |
| x              |
| y              |
| z              |
+----------------+
19 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: snjh
Command: Sleep
   Time: 10075
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 10150
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 16355
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 4. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: snjh
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
4 rows in set (0.00 sec)


I then checked the log file on the replication slave and found this rather interesting
message:


[root@virtcent10:~] #tail -f /var/log/mysqld.log
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.
110413 15:15:50  InnoDB: Error: page 1037 log sequence number 0 273491661
InnoDB: is in the future! Current system log sequence number 0 2229831.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.


So apparently there is some InnoDB information that is missing on the slave. 


Here is a listing of the data directory on the master:

[root@VIRTCENT09:/var/lib/mysql] #ls -lh
total 93M
drwx------ 2 mysql mysql 4.0K Mar 24 15:20 drupal
-rw-rw---- 1 mysql mysql  42M Apr 13 11:11 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Apr 13 11:11 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Apr 13 06:24 ib_logfile1
drwx------ 2 mysql mysql 4.0K Apr  6 18:17 jfwiki
drwx------ 2 mysql mysql 4.0K Mar 22 11:58 jokefire
drwx------ 2 mysql mysql 4.0K Mar 24 17:38 joomla
drwx------ 2 root  root  4.0K Apr  3 16:19 laconica
drwx------ 2 mysql mysql  20K Apr 12 15:30 magento
drwx------ 2 mysql mysql 4.0K Jan 11  2009 mail
-rw-rw---- 1 mysql mysql  119 Apr 13 08:58 master.info
drwx------ 2 mysql mysql 4.0K Mar 10 01:00 mysql
-rw-rw---- 1 mysql mysql  106 Apr 13 08:58 mysqld-relay-bin.000003
-rw-rw---- 1 mysql mysql   26 Apr 13 08:58 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql  11M Apr  6 08:31 mysqlmaster.000001
-rw-rw---- 1 mysql mysql 484K Apr  7 18:01 mysqlmaster.000002
-rw-rw---- 1 mysql mysql  13M Apr 10 15:59 mysqlmaster.000003
-rw-rw---- 1 mysql mysql 1.1M Apr 11 07:19 mysqlmaster.000004
-rw-rw---- 1 mysql mysql 3.6M Apr 12 06:27 mysqlmaster.000005
-rw-rw---- 1 mysql mysql 9.9M Apr 12 18:22 mysqlmaster.000006
-rw-rw---- 1 mysql mysql  11K Apr 12 18:47 mysqlmaster.000007
-rw-rw---- 1 mysql mysql 144K Apr 12 19:21 mysqlmaster.000008
-rw-rw---- 1 mysql mysql 199K Apr 13 03:01 mysqlmaster.000009
-rw-rw---- 1 mysql mysql  125 Apr 13 03:01 mysqlmaster.000010
-rw-rw---- 1 mysql mysql  125 Apr 13 03:03 mysqlmaster.000011
-rw-rw---- 1 mysql mysql  125 Apr 13 03:06 mysqlmaster.000012
-rw-rw---- 1 mysql mysql 490K Apr 13 06:24 mysqlmaster.000013
-rw-rw---- 1 mysql mysql 892K Apr 13 08:58 mysqlmaster.000014
-rw-rw---- 1 mysql mysql 1.2M Apr 13 11:11 mysqlmaster.000015
-rw-rw---- 1 mysql mysql  315 Apr 13 08:58 mysqlmaster.index
srwxrwxrwx 1 mysql mysql    0 Apr 13 08:58 mysql.sock
-rw-rw---- 1 mysql mysql   56 Apr 13 08:58 relay-log.info
drwx------ 2 mysql mysql 4.0K Apr  3 20:20 repltest
drwx------ 2 mysql mysql 4.0K Jan  4  2009 roundcube
drwx------ 2 mysql mysql  20K Apr 12 09:59 sales
drwx------ 2 mysql mysql 4.0K Apr 13 11:04 snjh
drwx------ 2 mysql mysql 4.0K Mar 29 22:15 snjh2
drwx------ 2 mysql mysql 4.0K Mar 10 01:00 test




And here is a listing of the data directory on the slave:

[root@virtcent10:/var/lib/mysql] #ls -lh
total 54M
drwxr-xr-x 2 root  root  4.0K Apr 13 03:10 bak
drwx------ 2 mysql mysql 4.0K Mar 24 15:20 drupal
-rw-rw---- 1 mysql mysql  42M Apr 13 15:16 ibdata1
-rw-rw---- 1 mysql    88 5.0M Apr 13 15:16 ib_logfile0
-rw-rw---- 1 mysql    88 5.0M Apr 13 06:53 ib_logfile1
drwx------ 2 mysql mysql 4.0K Apr  6 18:17 jfwiki
drwx------ 2 mysql mysql 4.0K Mar 22 11:58 jokefire
drwx------ 2 mysql mysql 4.0K Mar 24 17:38 joomla
drwx------ 2 root  root  4.0K Apr  3 16:19 laconica
drwx------ 2 mysql mysql  20K Apr 12 15:30 magento
drwx------ 2 mysql mysql 4.0K Jan 11  2009 mail
-rw-rw---- 1 mysql    88   91 Apr 13 15:15 master.info
drwx------ 2 mysql mysql 4.0K Mar 10 01:00 mysql
-rw-rw---- 1 mysql    88 449K Apr 13 15:15 mysqld-relay-bin.000011
-rw-rw---- 1 mysql    88 690K Apr 13 15:15 mysqld-relay-bin.000012
-rw-rw---- 1 mysql    88   52 Apr 13 15:15 mysqld-relay-bin.index
srwxrwxrwx 1 mysql    88    0 Apr 13 07:18 mysql.sock
-rw-rw---- 1 mysql    88   60 Apr 13 15:15 relay-log.info
drwx------ 2 mysql mysql 4.0K Apr  3 20:20 repltest
drwx------ 2 mysql mysql 4.0K Jan  4  2009 roundcube
drwx------ 2 mysql mysql  20K Apr 12 09:59 sales
drwx------ 2 mysql mysql 4.0K Apr 13 15:15 snjh
drwx------ 2 mysql mysql 4.0K Mar 29 22:15 snjh2
drwx------ 2 mysql mysql 4.0K Mar 10 01:00 test



I would greatly appreciate any tips you may have on how to resolve this issue.

Thanks in advance!
Tim



Thread
replication is not automaticTim Dunphy13 Apr
  • Re: replication is not automatica.smith13 Apr
    • Re: replication is not automaticTim Dunphy15 Apr
      • Re: replication is not automatica.smith15 Apr
        • Re: replication is not automaticTim Dunphy15 Apr
          • Re: replication is not automatica.smith19 Apr
          • Re: replication is not automaticars k25 Apr
            • Re: replication is not automaticTim Dunphy4 May
        • Re: replication is not automaticSuresh Kuna16 Apr
          • Re: replication is not automaticTim Dunphy16 Apr
            • Re: replication is not automaticSuresh Kuna16 Apr
              • Re: replication is not automaticTim Dunphy16 Apr
                • Re: replication is not automaticSuresh Kuna16 Apr
                  • Re: replication is not automaticTim Dunphy16 Apr
                    • Re: replication is not automaticSuresh Kuna16 Apr