From: Tim Dunphy Date: April 13 2011 7:54pm Subject: replication is not automatic List-Archive: http://lists.mysql.com/replication/2079 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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