473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Weekly Mass Data Relocation

I've got a table located on my website and localhost. The table
maintains a size of about 200MB. The table holds a running 4 weeks of
data (50MB/week). I have a winapp that updates my localhost db each
week and currently, I run a backup on the table using the MySql
Administrator... then I run restore on my website's mysql server.
This deletes the whole table and reinserts each row. This isn't needed
and is a long process.
Is there a way to select a range of rows from the localhost table and
"migrate" those rows to my website's mysql server without following the
process I just described? Thanks in advance!

Greg

Apr 5 '06 #1
2 1570
Bac2Day1 wrote:
I've got a table located on my website and localhost. The table
maintains a size of about 200MB. The table holds a running 4 weeks of
data (50MB/week). I have a winapp that updates my localhost db each
week and currently, I run a backup on the table using the MySql
Administrator... then I run restore on my website's mysql server.
This deletes the whole table and reinserts each row. This isn't needed
and is a long process.
Is there a way to select a range of rows from the localhost table and
"migrate" those rows to my website's mysql server without following the
process I just described? Thanks in advance!


mysqldump has an option "--where" which allows you to specify a
condition for selecting a subset of rows. I assume this is to be used
when you specify a single table when backing up.

See http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Unfortunately, there does not seem to be a GUI for specifying this
option in MySQL Administrator's Backup screen. I'd recommend writing a
BAT script to run the backup. For example, if your records have a
column called `data_entry_date` which records the date the row was created:

mysqldump --no-create-db --no-create-info --extended-insert
--complete-insert --where='data_entry_date > CURDATE() - INTERVAL 4
WEEK' database_name table_name > four_week_dump.sql

Note the use of the --no-create-* options, which omit the CREATE
DATABASE and CREATE TABLE statements from the dump output.

Regards,
Bill K.
Apr 5 '06 #2
Would an incremental solution be better?

Keep 5 weeks of data on local (4 previous + current week)

Each day:

mysqldump --no-create-db --no-create-info --extended-insert
--complete-insert --where='data_entry_date between CURDATE() - INTERVAL
1
DAY and CURDATE()' database_name table_name > 1_dump.sql
## gets yesterdays data

ftp to web server and apply plus:
delete from table_name where data_entry_date < CURDATE() - interval 4
week;

You will incur no downtime by having to truncate the table and reinsert
all 200MB. Your processing will be much more efficient.

Depending on your version you may have to use INTERVAL 30 DAY

Apr 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: w00t | last post by:
I am running Suse 9.0 and have been working with postgresql 7.3.3 When I run psql as psql -v or psql -V and even psql --help I get a relocation error psql: relocation error:...
1
by: willie | last post by:
Hi all: I have a clustering SQL Server on Node1 and Node2, the Node1 has named Instance1 and Node2 has named Instance2, no default instance. We tested it that everthing is OK, then we decide to...
1
by: kartik | last post by:
Hi , I am in need to find a way, to mass copy the data from db2 table running in mainframe to universal database db2 on NT. I know that DB2 connect gives the flexiblity of connecting and...
0
by: sylvain | last post by:
http://groups.yahoo.com/group/HiTechUnited (Source of articles about the HiTech Underemployment Crisis) HiTechUnited is a weekly newsletter, delivered Mondays, written by and for...
3
by: Strasser | last post by:
In Access2000 mass emailing worked perfectly (very powerful tool!). Doesn't work when using XP version of both Access and Outlook, even though I checked the box to ensure that I was sending the...
4
by: termiflyer | last post by:
Does anyone have any recommendations (books, links to class web pages, etc) for straight-forward learning about everything in between C programming and assembler? I know C fairly well, and I know...
0
by: Nadav | last post by:
Hi I am trying to intercept Win32 API, to achieve that I wish to manipulate the relocation table ( the API addresses table that is being created upon process startup/DLL Loading ), I have...
1
by: Timothy Larson | last post by:
I've been scanning groups and Googling and can't figure out why I'm getting this error. I've read about using the -fPIC flag but I've been doing that. Cannot load...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.