Frequently asked questions

Back
Keyword:  

How do I upload many rows of MySQL data?

There are two ways to insert a large amount of data without reaching the upload limits, both with a low risk of causing resource problems:

1. Bulk insert

MySQL support data insertion of several rows per insert statement. Example:

insert into login (userid,ipaddress,time)
values
(124,'192.168.1.9','2006-03-22 10:14:14),
(125,'192.168.12.4','2006-03-22 10:19:21),
(101,'192.168.97.24','2006-04-01 01:01:01);

The mysldump program, which is installed on our shell server, creates files optimized for bulk inserts.

See also the MySQL documentation for INSERT.

2. Staggered upload

You can also use a technique called "staggered insert/upload". Staggered inserts means that the data are uploaded in intervals. You can find several free scripts on the web to do this, such as the much-used bigdump.php (ozerov.de). Make sure that access to bigdump.php is password protected.

Open bigdump.php in a text editor and change the following lines:

$db_server   = 'myname.mysql.domeneshop.no';
$db_name     = 'myname';
$db_username = 'myname';
$db_password = 'mypassword';

$filename           = '';
$csv_insert_table   = ''; 
$csv_preempty_table = false; 
$ajax               = true;
$linespersession    = 800; // lines per session
$delaypersession    = 100000; // milliseconds (1/1000 sec)

Replace all occurrences of myname with your username and mypassword with your database password. The variables $linespersession and $delaypersession are set in order to avoid the resource limits on our MySQL server. The database dump file can either (1) be placed the same folder as bigdump.php or (2) uploaded by starting the script in a web browser. Note that Javascript must be enabled.

Bigdump.php

Be aware that this may take a long time depending on the file size. When the script has completed inserting data the following message will be displayed: Congratulations: End of file reached, assuming OK.

See also:

© 2024 Domeneshop AS · About us · Terms · Privacy policy