upload large database

alirezaok

hello
i want upload a large database to myhost.
i use from phpmyadmin (import sql file).
but it shows time out or other error because my database (sql file) is very large.
what do i do?
please help me
alse i don't want use of split mydatabase for upload.
i seem that i should use with this command:
mysql -u[dbusername] -p[dbpassword] [dbname] < [filename.sql]
but i dont know that what i use from this command on myhost that is not here
anyone help me plz?

richie

You cannot use phpmyadmin for large database tables.

If you have ssh account access, you can use

mysql -u[dbusername] -p[dbpassword] [dbname] < [filename.sql]

to make it run in batch mode.

An example will be:

mysql -u username -h localhost -ppassword < filename

You didn't say what platform you're on, but here are some examples if you're on Windows:

Open a DOS prompt:

c:\mysql\bin > mysql -u Username -pPassword -e "source c:\windows\desktop\filename.txt" dbName

The command should be self-explanatory, except maybe for the -e modifier. The -e means, --execute=... Execute command and quit.

What if you have a database with data, and you want to populate another database (located on a remote machine) with the same data?

c:\mysql\bin > mysqldump --opt -u username -pPassword database | mysql -u username -pPassword --host=remote-host-ip -C database

You must have a login account on the remote server of course. Let's say the ip address of the remote machine is 127.204.19.121, the database is called UserAccounts, username/password is malthus/theory. Your query then would be:

c:\mysql\bin > mysqldump --opt -u malthus -ptheory UserAccounts | mysql -u malthus -ptheory --host=127.204.19.121 -C UserAccounts

alirezaok

myhost platfrom is linux

i have a ssh account
but i don't know use from a ssh account
plz help me about login by ssh acount

also can i use of telnet as ssh?

thank you

tekky

http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe

use putty, its quite versatile and does SSH and TELNET

enter your host, click the SSH radio button, click connect, enter username then password and you're good to go

alirezaok

thank you

i could login with ssh or telnet
but when i run mysql command (mysql -u username -h localhost -ppassword < filename ) for import file.sql

it shows an error : No Such File Or Directory

i used of filename only and DOCUMENT_ROOT prompt
as: /home/virtual/site92/fst/var/www/html/file.sql
or file.sql
but boths shows the error

please help me for this error

tekky

is the file actually named file.sql? if not substitude file.sql with the name you used.....

alirezaok

yes. i replace file.sql with mynamefile.sql and i sure file name is correct.

mtimdog

it shows an error : No Such File Or Directory >>>>

This could be because you did not cd into the directory of mysql and the path to mysql is not on the PATH(?) var. Unless you run the server, you may not be able to.

Another way of looking at this would be to use a php script to upload the sql.

take out all the comments.

if all the queries are on their own line (readline-->mysql_query)

if they are not.....you could readline. Check for last char to be a semi-colon. If not set the string to a temp string. Readline...check, if not append string to temp....etc...until found. Then msyql_query(temp or readline depending on case).

Your only problem here is timeout error. I have solved this in the past by echoing out the query (so I know when it quits...and start from there), although the code runs much slower this way.

Also, after/while you query, echo out any problems so you know what queries to insert later.

alirezaok

ok very very thanks
my problem sloved

i have another question
if i want upload gzipped file this command

mysql -u[dbusername] -p[dbpassword] [dbname] < [filename.sql]

what is change for a gzipped file?

dopey

you can do:

gunzip filename.sql.gz

then:

mysql -u[dbusername] -p[dbpassword] [dbname] < filename.sql

or:

zcat filename.sql.gz | mysql -u[dbusername] -p[dbpassword] [dbname]

to do it in one line