| replication of load data infile [message #1876] |
Sat, 15 September 2007 09:37  |
 natbro
Messages: 1
Registered: September 2007 |
Junior Member |
|
|
anybody know of an option to cause LOAD DATA INFILE to replicate purely as a statement and not transfer the contents of the file through the binlog?
e.g. you could out-of-band pre-populate your master & slaves with a horrendously large .CSV file in /tmp and then issue the LOAD DATA INFILE without incurring a binlog and replication lag hit, just IO?
we sometimes have some very large bulk updates happening, and the congestion they cause an otherwise healthy replication chain would be nice to avoid.
i seem to remember such an option or default behavior in the misty past of MySQL 3, but it seems to replicate contents now in 5.0.
yes, i know doing such a thing fairly obviates the point of a binlog to completely recover your state.
if not, the only alternative i can think of is to do LOAD DATA INFILE into replication excluded tables and manually issue the statement on the master & each slave.
thanks for any insights.
|
|
|
| Re: replication of load data infile [message #3478 is a reply to message #1876 ] |
Fri, 29 August 2008 15:53  |
RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
I don't believe there is an option for that, but I have had similar needs and solved it with a interim load. Since the interim load effectively doubles the I/O and processing required, I use a dev server. Here is a pseudo-transcript of what the process may look like:
workstation:~$ scp new_data.csv devbox:/dev/shm #because I like using tmpfs (~RAMDISK) for this
workstation:~$ ssh devbox
devbox:~$ mysqldump -h prodbox --no-data schema_name table_name | mysql test_schema
devbox:~$ mysql -e "create schema temp_schema; use temp_schema; load data local infile '/dev/shm/new_data.csv' into table table_name"
devbox:~$ mysqldump --no-create-info temp_schema table_name | mysql -h prodbox schema_name
devbox:~$ mysql -e "drop schema temp_schema"
Another alternative which I have used in the past is to write a custom AWK script that converts each line of the csv file into an insert statement. I'll post it here if I can find it.
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
Members
Search
Help
Register
Login
Home
