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
