Importing a large database...

[Zacarias]

I need some help here. I have a dump of a sql database that's rather large (over 25MB). I split it up into 3 sql files that are all under the 15MB quota (4MB, 10MB and 9MB). I try to upload the first sql file, and after a long wait, it does nothing.

The database that I'm importing is a forum database, so that's why it's so big. I've also tried copying and pasting the sql queries, and still, nothing. Can someone help me out here?:confused:

[Zacarias]

Just a bump, something I found... If I upload the database sql file to my account, will someone from Powweb put it on one of my databases for me? Just wondering.

[Pig]

You can certainly ask and see. I believe you can also do from a shell command, if you are comfortable with that.

[Zacarias]

Shell command? Can you explain a bit? ^_^

[mitchind]

If you can zip it or gzip it, you can run it from my online restore version. PM me for login info.

http://www.purespeculation.com/test/restore.php5

Or you can download my script and modify it yourself.
http://www.purespeculation.com/download/upload_and_restore.zip

[Zacarias]

It seems that I'll have to do it myself. No response yet from my trouble ticket. =(

[Zacarias]

Well.. I used yor program, and this is the error I got, after the initial file was uploaded.

ERROR 1226 (42000) at line 30748: User 'myusername' has exceeded the 'max_questions' resource (current value: 72000)

Oh great. It seems that this will never happen. :(

[mitchind]

Well ... that's a nice little modification I guess I could make - No one's ever exceeded that til now.

Do you have multiple usernames setup for your database? You can setup three usernames and that will give you about 200,000 queries per hour.

[Zacarias]

Nope, just one.

Well, I tried it again, and it uploads... then hangs at conversion.

Conversion Status


Restoring file ... please wait
0 %

Current Status :
Time Remaining:


It's been like this for nearly 20 minutes.

[mitchind]

If you've hit the 72,000 query limit with your one user - you might as well wait til the next hour. Add two users, setup your SQL files to limit them to 72000 inserts each and try again with one file for each user...

.. or get Powweb Support to do it for you

[ticoroman]

Well ... that's a nice little modification I guess I could make - No one's ever exceeded that til now.

Do you have multiple usernames setup for your database? You can setup three usernames and that will give you about 200,000 queries per hour.I wrote it before in some thread, but it seems to got lost in the space without anyone noticing.

To avoid the 72,000 max query limit when importing databases there are more effective ways then just create 3 users. The solutions is multiple-row INSERTs.

http://forum.powweb.com/showthread.php?t=44213#post256543

[Zacarias]

That's ingenious. Powweb should have this information posted somewhere... maybe sticky a thread or something?

Oh, and I had Powweb staff do it for me already, lol.

[RTH10260]

That's ingenious. Powweb should have this information posted somewhere... maybe sticky a thread or something?

Oh, and I had Powweb staff do it for me already, lol.
The sticky thread with MySql information can be found in the PHP forum section ...

The limits are listed in the Terms Of Service, changes had been published in the Announcement section fo the forums..

[extras]

I wrote a restore script that uses standard .sql dumpfile
and operate with LOAD DATA LOCAL INFILE.

Compared to using .sql like: mysql -uUSER -pPASS -hHOST -DDB <dump.sql
this one took less than a half (or even less) of time to restore.

It can be used as a CGI or at command line (or invoked by other scripts).
As a command restore.cgi dump.sql
As a CGI: http://example,com/protected/restore.cgi?dump.sql

Any volunteer willing to test?
(You must be aware that it may destroy DB. I tested, but maybe there are few corner cases left...)
If you want to try, create a new DB in the OPS and play with it.

Basically, it simply reads .sql file and converts the INSERT lines to a datafile,
and put LOAD DATA LOCAL INFILE command with correct field format.
It looks a little complicated because I tried to make it fast and also usable with big DB without consuming lots of memory.
It can be used for restoring single table, if you dumped single table or if you modify the script a little.

EDIT: The latest version will be available at http://check-these.info/tools/restore_cgi.txt


#!/usr/local/bin/perl
# restore.cgi v0.15
# Put it in a well protected directory, and set permission of 700 (Not 755)
# Edit and replace configuration section.
#
# To use it via browser: http://example.com/protected/restore.cgi?dump.sql
# As a command or from other script: ./restore.cgi dump.sql
#
# Please read MySQL manual for more info:
# http://dev.mysql.com/doc/mysql/en/mysql.html
#
select(STDOUT);
$| = 1;
print "Content-type: text/plain\n\nRestoring mysql: ";

######## Setup section ############
my $U = 'USERNAME'; # Username for MySQL
my $P = 'PASSWORD'; # Password for MySQL
my $H = 'mysql0X.powweb.com'; # MySQL server name
my $D = 'DBNAME'; # Database name
my $sqld = "backup.sql"; # Default backup file name
######### End of setup ############

$sqld = $ARGV[0] if $ARGV[0];
print "Dumpfile=$sqld\n\n";
my $dat = '';
my $dat2 = '';
my $pos = 0;
my $blen = 8160;

# Open the dump file
print "Opening $sqld ...";
open(FH, "$sqld") || die("Failed to open MySql dumpfile: $sqld\n");
print "done.\n";

# Open temp file that hold mysql commands
print "Opening restore.tmp ...";
open(TMP, ">restore.tmp") || die("Failed to open temp file\n");
print "done.\n\n";

read(FH, $dat, $blen) || die("Dump file format error: missing INSERT\n");
while(1){
$pos = index($dat, "\nINSE"); ### Find \nINSE ###
#print "START pos=$pos\n";
if($pos < 0){ # Get next chunk if not found in the buffer
$datlen = length($dat);
print TMP substr($dat,0, $datlen-4);
read(FH, $dat2, $blen) || last;
$dat = substr($dat,$datlen-4).$dat2;
next;
}else{
print TMP substr($dat,0,$pos+1);
$dat = substr($dat,$pos +1);
} ### \nINSE found ###

if( ($posy = index($dat, "(")) < 0 ){ ### Find ( ###
read(FH, $dat2, $blen) || last;
$posy = index($dat2, "(");
last if $posy < 0;
$posy += length($dat);
$dat .= $dat2;
} ### \nINSEERT .... ( found ###

substr($dat,0,$posy) =~m/^INSERT INTO \`([^\`]+)\` VALUES /;
$t = $1;
print "Table=$t : ";

### Replace the line with 'INSERT INTO' with LOAD DATA LOCAL INFINLE ...'
print TMP "LOAD DATA LOCAL INFILE '$t.dat' INTO TABLE $t FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"'\" LINES TERMINATED BY '),(';\n";

### Create a data file for the table removing up to the first (, and last );\n
print "Opening $t.dat ...";
open(FT,">$t.dat") || die("Failed to open for write: $t.dat\n");
print "done.\ndat=".substr($dat,0,80)."\n\n";

$x = 0;
while(index($dat,'I') == 0){ ### Treat all INSERT lines ###
print FT '),(' if $x; # Connect INSERT lines with ),(
$x++;
$pos = index($dat, '('); ### Find ( --- the start of a dataline ###
if($pos < 0){
read(FH, $dat2, $blen) || last;
$pos = index($dat2, "(");
last if $pos < 0;
$pos += length($dat);
$dat .=$dat2;
} #### ( found ###

$dat = substr($dat,$pos+1);
$posy = index($dat, ");\n"); ### Find );\n --- the end of a dataline ###
if( $posy < 0){
while(read(FH, $dat2, $blen)){
$posy = index(substr($dat, length($dat) -2).$dat2, ");\n");
last if $posy >= 0;
print FT $dat;
$dat = $dat2;
} ### Found );\n after multiple attempts ###
$posy -= 2;
if($posy < 0){
print FT substr($dat,0,length($dat)+$posy);
}else{
print FT $dat;
print FT substr($dat2,0,$posy);
}
$dat = substr($dat2, $posy+3);
}else{
print FT substr($dat,0,$posy);
$dat = substr($dat,$posy+3);
} ### Found );\n at the first attempt ###
} ### Data file done ###
close(FT);
}
print TMP $dat;
close(FH);
close(TMP);

print "Finished preparing restore.tmp and data file(s).\nStarting to restore ...\n\n";
#print `cat restore.tmp 2>&1`;
#exit; # If you just want to create data file(s) and restore.temp, end here.

# Do actual restore using 'mysql' command. Add/Modify options as you want.
# For more detail ==> http://dev.mysql.com/doc/mysql/en/mysql.html
print `time mysql -v -v -v -u$U -p$P -h$H -D$D <restore.tmp 2>&1`;
print "All done\n";
exit
1;



I tested up to .sql file of about 8 Mbyte.
It took less than 10 seconds.
If you have lots of index or complicated structure, it may take a lot longer.
Test first with smaller file.

Also, please avoid testing when the server is busy/loaded.

[extras]

Other codes, I wrote&tested (in shell script):


Backup command (for a single table):

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=mysql01.powweb.com
D=DBANME
T=TABLENAME
mysql -s -u"$U" -p"$P" -h"$H" -e "select * from $T;" "$D" >"$T".dat 2>backup.err


Restore (The DB and table must be there in this example):

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=mysql01.powweb.com
D=DBANME
T=TABLENAME
mysql -u"$U" -p"$P" -h"$H" -e "TRUNCATE $T;
LOAD DATA LOCAL INFILE '$T.dat' INTO TABLE $T;" "$D" 2>restore.err




This should backup all tables in given DB.
It will create "table.create" (CREATE TABEL sql lines),
and TABLENAME.dat for each table, all in the current directory.

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=mysql0X.powweb.com
D=DBNAME

M="mysql -s -u$U -p$P -h$H -D$D -e"
TL=`$M "SHOW TABLES"`
for t in $TL
do
C=$C"SHOW CREATE TABLE $t;"
$M "SELECT * FROM $t;" >$t.dat
done
$M "$C" |cut -f 2 >table.create


Restore entire DB
It needs "table.create" (CREATE TABEL sql lines),
and TABLENAME.dat for each table, all in the current directory.

#!/bin/sh
echo
U=USERNAME
P=PASSWORD
H=mysql0X.powweb.com
D=DBNAME

M="mysql -v -v -v -u$U -p$P -h$H -D$D -e"
while : ;do
read -r ct || break
t=${ct#*TABLE ?}
t=${t%%? *}
$M "DROP TABLE IF EXISTS $t; $ct;
LOAD DATA LOCAL INFILE '$t.dat' INTO TABLE $t"
done <table.create


I tested these with a DB containing 2 tables each having 171,244 rows.
Datafile for each table.dat were about 3.3 Mbytes.
Backup took a few seconds, and restore took several seconds (One table had an index).
Using single row INSERT, they would have hit query limit, probably.

[ticoroman]

It can be used as a CGI or at command line (or invoked by other scripts).
As a command restore.cgi dump.sql
As a CGI: http://example,com/protected/restore.cgi?dump.sql

Any volunteer willing to test?
(You must be aware that it may destroy DB. I tested, but maybe there are few corner cases left...)
If you want to try, create a new DB in the OPS and play with it.
I could not make it to work. I chmoded it to 700, loaded a test SQL-dump.

The script outputs
Restoring mysql:
Dumpfile=mboard.sql

and creates a file named "restore.tmp" 318 KB, which is about 4% of the SQL-dump. It contains the first part of the SQL-dump.

That's it.

The setup section should be OK.

The SQL-dump contains so called multiple-row INSERTs, and is dumped from PHPMyAdmin. I will try with a "regular" dump, without mrI.

Update:
Nothing. Same again.

[mitchind]

I wrote a restore script that uses standard .sql dumpfile
and operate with LOAD DATA LOCAL INFILE.

Compared to using .sql like: mysql -uUSER -pPASS -hHOST -DDB <dump.sql
this one took less than a half (or even less) of time to restore.

It can be used as a CGI or at command line (or invoked by other scripts).
As a command restore.cgi dump.sql
As a CGI: http://example,com/protected/restore.cgi?dump.sql

Any volunteer willing to test?

Yeah baby! Now THAT'S what I'm talking about!

I was just thinking about wading my way thru doing something like this and ... BOOM .. it's done... I'll have some time to play with it later but it does sound promising!

Good job .. AGAIN, extras!

[extras]

I could not make it to work. I chmoded it to 700, loaded a test SQL-dump.

The script outputs
Restoring mysql:
Dumpfile=mboard.sql

and creates a file named "restore.tmp" 318 KB, which is about 4% of the SQL-dump. It contains the first part of the SQL-dump.
Yes. This script separates SQL command part and the Data (for each table).
Can you check if it has created files with tablename + '.dat' ?
example: If the DB has table t1, t2, and t3, it creates t1.dat, t2.dat, and t3.dat.


That's it.

The setup section should be OK.

The SQL-dump contains so called multiple-row INSERTs, and is dumped from PHPMyAdmin. I will try with a "regular" dump, without mrI.

Update:
Nothing. Same again.

It should work with multi-row ... But it may not work if LOCK ... UNLOCK isn't there.
Can you post or link the .sql file? I'll check the format.

EDIT:

I modified the code so that it skips parsing LOCK and use INSERT instead,
as some dump files may not contain LOCK.

[bnizzie]

I dunno if your problem has already been solved, but I ended up uploading the SQL files VIA ftp then executing this little PHP script, changing the vales as needed.

Good luck, hope it helps.

Go Go Go<br />
<?php
function query($sql) {
print substr($sql,0,20)." &lt;...&gt; ".substr($sql, -20, 20)."<br />";
mysql_query($sql) or die(mysql_error());
ob_flush();
flush();
}
$DB = mysql_connect('mysql13.powweb.com', 'USERNAME', 'PASSWORD') or die('suxor!');
mysql_select_db('DATABASE_NAME') or die ('database suxor!');
$file = fopen('../SQL_FILE.sql','r') or die('file suxor!');

$buffer = '';
while($line=fgets($file))
{
if (preg_match('/;\w*$/', $line))
{
query($buffer.$line);
$buffer = '';
}
else
$buffer .= $line;
}
if ($buffer != '')
{
query($buffer);
}
?>

[ticoroman]

It should work with multi-row ... But it may not work if LOCK ... UNLOCK isn't there.
Can you post or link the .sql file? I'll check the format.

EDIT:

I modified the code so that it skips parsing LOCK and use INSERT instead,
as some dump files may not contain LOCK.
Thanks extras, I could restore SQL-dumps from mysqldump successfully. However I could not restore dumps from phpmyadmin (no matter how the tables were exported). I guess it's some formatting error, as the table exported contains ', " and a lot other "dangerous" characters. (Same tables were restored successfully if they were dumped using mysqldump).

Bye
7.26 real 0.01 user 0.32 sys
All doneWhat does the real, user and sys mean? I guess it's the server load.

When I restored (in testing purposes) a huge dump with 2 x 10^6 rows the real went up almost to 80 and sys to 2. Hopefully the server is not too busy this period of day. How much (load) is too much?

[extras]

Thank you for testing the script. :)

If you can show me the format of failing .sql file, probably I can adjust the script.
I didn't know phpmyadmin output different format, as I use 'mysql' command or
my own web interface for mysql command. http://check-these.info/tools/mysql_cgi.txt

This output 7.26 real 0.01 user 0.32 sys is coming from the 'time' command,
and it'snot the load, but time.
http://www.freebsd.org/cgi/man.cgi?query=time

If it says 80 (for the real), it took 80 seconds.
(It's long, but it would have taken more with INSERT... And php based tool might have failed, too)

I don't know if the loadaverage of the mysql server can be checked.
We can know the 'uptime' and other status via 'STATUS' SQL command.
Adding this line just befor close(TMP); will add the status output if you like to see.
print TMP "STATUS;\n";

EDIT:

I finally tried phpmyadmin, and I saw some of options for exporting .sql file.
Probably, it failes because of "CREATE DATABASE ..." and commenting out the line with -- (two dashes)
would make the file compatible with my script.

I'll test and see what I can do.

EDIT2:

Also, it needs DROP TABLE ... if the tables with the same name are there ....

[extras]

I dunno if your problem has already been solved, but I ended up uploading the SQL files VIA ftp then executing this little PHP script, changing the vales as needed.,

Thank you for the tip.
We were talking about how to make the importing more efficient, by avoiding costly "INSERT".
(Also, each INSERT counts for the SQL query limit.)

If we have a small .sql file, I would use shellscript or CGI shell interface to execute .sql file.

#!/bin/sh
echo
mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1


The same thing can be done through php (although it's less efficient.)

<?php
echo `mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1`;
?>

[bnizzie]

I thought that was resolved earlier. I definately saw someone mention running mysqldump with the --extended-insert flag, which will make the inserts less bulky.

I have found phpmyadmin's dumps to be, in general, really bad.

[extras]

I thought that was resolved earlier. I definately saw someone mention running mysqldump with the --extended-insert flag, which will make the inserts less bulky.

I have found phpmyadmin's dumps to be, in general, really bad.
Even with the --extended-insert flag, it uses INSERT, which is slow.
http://check-these.info/tools/restore_cgi.txt solves that issue, as well.

And there are other methods.
http://check-these.info/MysqlTips.html

[bnizzie]

If it says 80 (for the real), it took 80 seconds.
(It's long, but it would have taken more with INSERT... And php based tool might have failed, too)

To extend the length of a running PHP script:
http://us2.php.net/function.set-time-limit

I would suspect that if you use extended inserts, that 80 seconds could be cut considerably.

[extras]

To extend the length of a running PHP script:
http://us2.php.net/function.set-time-limit

I would suspect that if you use extended inserts, that 80 seconds could be cut considerably.
bnizzie, the script is in Perl, and it has nothing to do with php and its problems/slowness.
Also, the script doesn't use INSERT. It uses LOAD DATA LOCAL INFILE.
Multiple row INSERT will improve the data conversion portion, but it will have no effect on actual import.

If you test several methods shown is this thread, you will see the differences. ;)

[bnizzie]

Even with the --extended-insert flag, it uses INSERT, which is slow.
http://check-these.info/tools/restore_cgi.txt solves that issue, as well.

And there are other methods.
http://check-these.info/MysqlTips.html

Extended inserts are much faster than normal, per-row inserts. Perhaps not as good as your solution. Takes less than 3 pages to explain, tho. :)

[extras]

Extended inserts are much faster than normal, per-row inserts. Perhaps not as good as your solution. Takes less than 3 pages to explain, tho. :)
Yeah, yeah, I know.

But INSERT is still slower than LOAD DATA.
http://dev.mysql.com/doc/mysql/en/insert-speed.html

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

--- snip -------

You can speed up INSERT operations that are performed with multiple statements by locking your tables:

LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;

This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally there would be as many index buffer flushes as there are INSERT statements. Explicit locking statements are not needed if you can insert all rows with a single statement.

For transactional tables, you should use BEGIN and COMMIT instead of LOCK TABLES to obtain faster insertions.

Locking also lowers the total time for multiple-connection tests, although the maximum wait time for individual connections might go up because they wait for locks. For example:

Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts

If you do not use locking, connections 2, 3, and 4 finish before 1 and 5. If you use locking, connections 2, 3, and 4 probably do not finish before 1 or 5, but the total time should be about 40% faster.

INSERT, UPDATE, and DELETE operations are very fast in MySQL, but you can obtain better overall performance by adding locks around everything that does more than about five inserts or updates in a row. If you do very many inserts in a row, you could do a LOCK TABLES followed by an UNLOCK TABLES once in a while (each 1,000 rows or so) to allow other threads access to the table. This would still result in a nice performance gain.

INSERT is still much slower for loading data than LOAD DATA INFILE, even when using the strategies just outlined.



And it was easy to confirm that by testing. :)
As long as you are using tiny DB, it doesn't matter.
But it makes difference when DB/tables are bigger.

[bnizzie]

bnizzie, the script is in Perl, and it has nothing to do with php and its problems/slowness.
Also, the script doesn't use INSERT. It uses LOAD DATA LOCAL INFILE.
Multiple row INSERT will improve the data conversion portion, but it will have no effect on actual import.

If you test several methods shown is this thread, you will see the differences. ;)

I was responding to your 'php script might have failed' comment. I said nothing of Perl's ineffeciency. Gosh, everyone knows Perl is the best, always, evar. :p

I was simply trying to help someone who might be looking for a simpler solution that would yield better results than phpMyAdmin. Not trying to compete with your Perl script in any way.

[bnizzie]

Yeah, yeah, I know.

But INSERT is still slower than LOAD DATA.

When did I ever argue this point? I agree. I never meant to argue with you about anything.

[extras]

I was responding to your 'php script might have failed' comment.
Most probably, the script you showed would fail with big .sql file, running out of memory, for example.


I was simply trying to help someone who might be looking for a simpler solution that would yield better results than phpMyAdmin. Not trying to compete with your Perl script in any way.

When did I ever argue this point? I agree. I never meant to argue with you about anything.

I see. Now, you are arguing that you never meant to argue about anything. :)

[bnizzie]

Most probably, the script you showed would fail with big .sql file, running out of memory, for example.

That's funny, it ran my 75mb sql file just fine. hmm.

[bnizzie]

I see. Now, you are arguing that you never meant to argue about anything. :)

I'm sorry, I am using the dictionary definition of argue, when clearly any discussion must be an argument.. My bad.
http://dictionary.reference.com/search?q=argue
http://dictionary.reference.com/search?q=discuss

When I initially posted, and my initial responses, up to this point, have merely been discussion. You're the one who jumped into Perl Zealotry(tm). Totally second-guessing what the script was doing with concerns about memory. I am guessing perhaps you don't understand why one would use fopen() and fgets() instead of something along the lines of file_get_contents(), and the implications on memory usage when you do things in php such as $buffer = ''.

Do you care about helping someone get something done, or proving your methos is the best in all cases? Honestly, the level of complexity of your solution, though technically superior, is perhaps daunting for a more novice user.

Now I am argugin, but not on technical merit, but on your approach to 'helping'. You shouldn't turn down other approaches to a problem, if it gets the job done. Even if it's in a language you don't like.

[extras]

I'm sorry, I am using the dictionary definition of argue, when clearly any discussion must be an argument.. My bad.
http://dictionary.reference.com/search?q=argue
http://dictionary.reference.com/search?q=discuss

When I initially posted, and my initial responses, up to this point, have merely been discussion. You're the one who jumped into Perl Zealotry(tm). Totally second-guessing what the script was doing with concerns about memory. I am guessing perhaps you don't understand why one would use fopen() and fgets() instead of something along the lines of file_get_contents(), and the implications on memory usage when you do things in php such as $buffer = ''.
I noticed you are using fgets() and then using slow preg_match in the loop. ;)

If the line (or SQL command) goes longer than certain amount, your script will run out of memory.
Probably, phpmyadmin/mysqldump spilts the line to keep it under certain size,
and that's why it went without problem.


Do you care about helping someone get something done, or proving your methos is the best in all cases?
Honestly, the level of complexity of your solution, though technically superior, is perhaps daunting for a more novice user.
I showed you the far easier, simpler solution, which does basically the same thing as yours. :)

<?php
echo `mysql -v -v -v -uUSERNAME -pPASSWORD -hmysql0x.powweb.com DBNAME <SQL_FILE.sql 2>&1`;
?>


Also, the perl script only need the configuration part to be edited.
So, your argument about the code complexity is rather pointless.


Now I am argugin, but not on technical merit, but on your approach to 'helping'. You shouldn't turn down other approaches to a problem, if it gets the job done. Even if it's in a language you don't like.
Then, you shouldn't turn down the solutions I presented. I even presented one in php. :)
Also, I'm expecting many helpful posts of yours in future.

[mitchind]

Closing thread.

Lots of good information in this one is getting lost in the banter.
Save it for Private Messaging.