Just reading the header you put in the question shows something interesting. In fact, the question shows three things:
MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binariesServer version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.- You wanted to load the mysqldump file into
Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1
What a jumble of versions to do this with.
If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.
mysqldump --no-data --all-databases ... > MySQLSchema.sql
This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,
mysqldump --no-create-info --all-databases ... > MySQLData.sql
Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql
BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.
Give it a Try !!!
Based on our chat conversation, here is what was discussed
- Server1 is Stand Alone
- Server2 is a Master
- Server3 is a Slave to Server2
This implies that binary logging is enabled in Server2.
To Make Server1 a Master of Server2, perform the following:
STEP 01 : On Server2, add this to /etc/my.cnf
[mysqld]
log-slave-updates
STEP 02 : On Server3, run STOP SLAVE;
STEP 03 : On Server2, run service mysql restart
STEP 04 : On Server3, run START SLAVE;
STEP 06 : On Server1, add this to /etc/my.cnf
[mysqld]
log-bin=mysql-bin
STEP 07 : On Server1, run service mysql restart
STEP 08 : Set Replication From Server1 to Server2
See Clarification about master slave configuration in mysql
OPTIONAL
Once you have MySQL Replication Going From Server1 to Server2 to Server3, your can properly load all data into all three MySQL Instances by doing the following on Server1:
mysqldump -u... -p... --all-databases --routines --triggers > mysqldata.sql
mysql -u... -p... < mysqldata.sql
This will do three(3) things
- Repopulate everything into Server1
- MySQL Replication will handle populating Server2 from Server1
- MySQL Replication will handle populating Server3 from Server2
Since your data is 50MB in total, this should be execute very quickly.
Best Answer
It's impossible to tell without investigation. Potentially it could be mistakes when you created the slave, it could be direct writes to the slave, bugs in replication, bugs in an app with
SET binlog=0, etc.Worse, it's very hard to investigate post-factum. You need to prepare for the data drift troubleshooting.
pt-table-checksumwould be your first step. It's a mature tool, safe to use on prod, but problems are possible, too. Most likely, locking issues, deadlocks. If need to be extra paranoid, I set--chunk-timeto something like 0.1 or even less.Find differences in the data, review it (you may want to use twindb_table_compare to find which rows are missing/extra/different), and fix the mismatches (with
pt-table-syncor rebuild the slave depending how big the data drift is).And put
pt-table-checksumin cron. If one run takes long time (say 1 - 2 days) I recommend to split discovery and alerting parts (i.e. the heavypt-table-checksumpopulatespercona.checksumsand a light script checkspercona.checksumsand alerts if inconsistencies are found). On my systems I runpt-table-checksumcontinuously.Then, when an inconsistency is discovered inspect the data, the binlog and then you'll figure out where it comes from.