sagar patel February 2016

How to fetch & store over 100k records to DB from another DB

I have school database who having more then 80 000 records and I want to update and insert into my newSchool database using php, whenever I try to run query update or insert almost 2 000 records and after some time its stopped automatically please help

Answers


mmm February 2016

You could (should) do a full dump and import that dump later. I'm not sure how to do it with php - and think you'd be better doing this with those commands on the cli:

mysqldump -u <username> -p -A -R -E --triggers --single-transaction > backup.sql

And on your localhost:

mysql -u <username> -p < backup.sql 

The backup statement flags meanings from the docs:

-u 

DB_USERNAME

-p

DB_PASSWORD

Don't paste your password here, but enter it after mysql asks for it. Using a password on the command line interface can be insecure.

-A 

Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

-E

Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases.

The output generated by using --events contains CREATE EVENT statements to create the events. However, these statements do not include attributes such as the event creation and modification timestamps, so when the events are reloaded, they are created with timestamps equal to the reload time.

If you require events to be created with their original timestamp attributes, do not use --events. Instead, dump and reload the contents of the mysql.event table directly, using a MySQL account that has appropriate privileges for the mysql database.

-R

Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELE


abdiel February 2016

Be sure to do commit after a few commands, for example after 500 rows. That save memory but has the problem that in case of rollback only going back to the last commit.

Post Status

Asked in February 2016
Viewed 2,329 times
Voted 9
Answered 2 times

Search




Leave an answer