Have you looked into the SELECT INTO OUTFILE syntax?
Here is a quote on the NULL string issue:
If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.
So make sure you indicate a FIELDS ESCAPED BY
clause to get NULLs written as \N
Hope this helps
The traditional way would be to use pt-table-checksum and pt-table-sync
I like doing things a little different. I immediately run pt-table-sync
with the --sync-to-master --print
options.
Here is the --sync-to-master option
--sync-to-master
Treat the DSN as a slave and sync it to its master.
Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS,
connect to the server’s master, and treat the master as the source and
the slave as the destination. Causes changes to be made on the master.
Sets --wait to 60 by default, sets --lock to 1 by default, and
disables --[no]transaction by default. See also --replicate, which
changes this option’s behavior.
Here is the --print option
--print
Print queries that will resolve differences.
If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.
This, if you do something like this:
echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql
The file /root/SQLChanges.sql
will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.
With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.
Best Answer
The
LOAD DATA INFILE
statement was not always replicated correctly to a slave running MySQL 5.1.42 or earlier from a master running MySQL 4.0 or earlier. When using statement-based replication, the LOAD DATA INFILE statement CONCURRENT option was not replicated. This issue was fixed in MySQL 5.1.43. This issue does not have any impact on CONCURRENT option handling when using row-based replication in MySQL 5.1 or later (See this bug report http://bugs.mysql.com/bug.php?id=34628)Also In MySQL 5.1.52 and later, LOAD DATA INFILE is considered unsafe. It causes a warning when using statement-based logging format, and is logged using row-based format when using mixed-format logging.
http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html