Folks, I've got a problem with a straight-forward master->slave replication setup that has failed to replicate some data to the second server (or so it appears). Below is a bunch of info including the last few lines of mysqld.log from both servers, specs of the boxes, cron entries, etc... The odd stuff I'm doing in cron on server2 stems from the fact that I'm doing log-slave-updates and have a script running every 30 minutes on that box taking a snapshot of new inserts/updates/deletes and shipping them off-site to a 3rd server. Needless to say, when some data on server1 was missing on server2 it was also missing on the 3rd server. Anyway, a "flush-logs" is done on the 2nd server every 30 minutes to stop using that binary log file and to start using a new one. Then a mysqlbinlog of the previous binary log file is done to get a plain text list of ins/upd/del statements and these files appear to be left around forever if I don't manually delete them, which is done each night at 4AM via a "flush master" statement. That's just some additional info, the primary thing is that server1 and server2 do straight regular replication and today we discovered about 210 records missing in a table on server2 compared to server1. I determined this via a "select count(*)" from that table and nothing else. Didn't have time to analyze it closely, just dumped that table from 1 and imported it on 2 quickly since the boxes are somewhat mission critical. Server1 gets all updates and server2 is a hot-redundant backup server only with no clients attaching to it at all so no way the records were deleted from it that I can see.
I see an error at 3AM on the 10th in the log for server2 but that's well past the time new data was being inputted. It should have been in perfect sync at that point. Heikki Tuuri at innodb.com worked with me a few months back concerning a lot of innodb errors in the log files which was traced to a SCSI driver doing some odd things. That type of error has not reappeared in any logs since that update and per his suggestion I deleted the ibdata and iblog files and let them be recreated and then re-imported the data. A few months ago, been stable best I can tell since then until now. PS. My intent is to get both servers back in perfect sync and nightly dump databases on both and basically "diff" them. Any suggestions on any other good way to compare them? Found a PERL script to compare database structures on freshmeat but not data. Could write one I guess but if the wheel has already been invented... Here's the specifics: Specs: ------ MySQL 3.23.49a Two IBM xSeries 350 servers with dual P3 700 Xeons, 1.5GB RAM, IBM RAID5 internal on IBM 10KRPM drives, IBM gigabit ethernet cards, RedHat 7.2 with full patches and kernel 2.4.9-31 as a RedHat update RPM, etc... /var/log/mysqld.log pertinent lines: ------------------------------------ Server2 (Slave) --------------- 020408 4:20:01 Slave thread killed while reading event 020408 4:20:01 Slave thread exiting, replication stopped in log 'shelby1-bin.001' at position 73 020408 4:20:01 InnoDB: Starting shutdown... 020408 4:20:12 InnoDB: Shutdown completed 020408 4:20:12 /usr/sbin/mysqld-max: Shutdown Complete 020408 04:20:12 mysqld ended 020408 04:30:01 mysqld started 020408 4:30:13 InnoDB: Started /usr/sbin/mysqld-max: ready for connections 020408 4:30:13 Slave: connected to master 'repl@shelby1:3306', replication started in log 'FIRST' at position 4 020409 4:20:00 /usr/sbin/mysqld-max: Normal shutdown 020409 4:20:00 Slave thread killed while reading event 020409 4:20:00 Slave thread exiting, replication stopped in log 'shelby1-bin.001' at position 9837727 020409 4:20:00 InnoDB: Starting shutdown... 020409 4:20:25 InnoDB: Shutdown completed 020409 4:20:26 /usr/sbin/mysqld-max: Shutdown Complete 020409 04:20:26 mysqld ended 020409 04:30:01 mysqld started 020409 4:30:14 InnoDB: Started /usr/sbin/mysqld-max: ready for connections 020409 4:30:14 Slave: connected to master 'repl@shelby1:3306', replication started in log 'FIRST' at position 4 020410 3:03:23 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020410 3:03:23 Slave: Failed reading log event, reconnecting to retry, log 'shelby1-bin.001' position 484175932 020410 3:03:23 Slave: reconnected to master 'repl@shelby1:3306',replication resumed in log 'shelby1-bin.001' at position 484175932 020410 4:20:00 /usr/sbin/mysqld-max: Normal shutdown 020410 4:20:00 Slave thread exiting, replication stopped in log 'shelby1-bin.001' at position 516583385 020410 4:20:01 InnoDB: Starting shutdown... 020410 4:20:16 InnoDB: Shutdown completed 020410 4:20:16 /usr/sbin/mysqld-max: Shutdown Complete 020410 04:20:16 mysqld ended 020410 04:30:01 mysqld started 020410 4:30:08 InnoDB: Started /usr/sbin/mysqld-max: ready for connections 020410 4:30:08 Slave: connected to master 'repl@shelby1:3306', replication started in log 'FIRST' at position 4 Server1 (Master) ---------------- 020310 17:38:23 /usr/sbin/mysqld-max: Normal shutdown 020310 17:38:24 InnoDB: Starting shutdown... 020310 17:38:44 InnoDB: Shutdown completed 020310 17:38:44 /usr/sbin/mysqld-max: Shutdown Complete 020310 17:38:44 mysqld ended 020310 17:41:30 mysqld started 020310 17:41:38 InnoDB: Started /usr/sbin/mysqld-max: ready for connections 020401 19:35:22 /usr/sbin/mysqld-max: Normal shutdown 020401 19:35:24 InnoDB: Starting shutdown... 020401 19:35:44 InnoDB: Shutdown completed 020401 19:35:45 /usr/sbin/mysqld-max: Shutdown Complete 020401 19:35:45 mysqld ended 020401 19:35:46 mysqld started 020401 19:35:54 InnoDB: Started /usr/sbin/mysqld-max: ready for connections cron entries: ------------- Server2: -------- 20 4 * * * /etc/rc.d/init.d/mysql stop 1>/dev/null 2>/dev/null # 25 4 * * * /bin/rm -f /var/lib/mysql/master.info 1>/dev/null 2>/dev/null # 30 4 * * * /etc/rc.d/init.d/mysql start 1>/dev/null 2>/dev/null Server1: -------- 0 4 * * * /usr/bin/mysql -uroot -pxxxxx </var/lib/mysql/fl 1>/dev/null 2>/dev/null Note: File "fl" contains "flush master;" /etc/my.cnf files: ------------------ Server1: -------- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=1 default-table-type=innodb innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata 6:2000M innodb_data_home_dir = /var/lib/innodb/ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /var/lib/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /var/lib/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=40M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 tmpdir = /data/tmp [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Server2: -------- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock master-host=shelby1 master-user=repl master-password=xxxxx server-id=2 log-bin=/var/lib/mysql/shelby-bin binlog-do-db=registrydb_tn log-slave-updates default-table-type=innodb innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata 6:2000M innodb_data_home_dir = /var/lib/innodb/ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /var/lib/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /var/lib/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=40M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Miscellaneous: -------------- # uptime 2:58pm up 30 days, 20:08, 5 users, load average: 2.16, 2.40, 2.43 <- both servers same # of days --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
