Transferring database from phpmyadmin to directadmin

[Egon]

I´m transferring my website from a hosting company with Plesk to a hosting company with DirectAdmin 1.22.6.

The database is roughly 5,5Mb large and cant therefore be restored using the SQL-functionality in PhpMyAdmin.

I have tried to use the create/restore backups functionality in DirectAdmin but the moment i press on restore i get this error

File is not of type tar.gz: /usr/bin/tar: This does not look like a tar archive /usr/bin/tar: Skipping to next header /usr/bin/tar: Archive contains obsolescent base-64 headers /usr/bin/tar: Error exit delayed from previous errors

Probably i´m doing something wrong. What do i need to do to get my database from phpmyadmin(plesk) to Directadmin? Do i need to set the compression. I must say i´ve tried gzipped.

 

[jmstacey]

You can backup the database and restore it ussing ssh.

 

[Egon]

How do I do that? I´m just a customer and not the owner of the server? Do I need to use a programme like PuTTY?

Probably think so! Just found out that SSH has not been enabled on my account. Have asked the hosting company to grant me SSH access.

Havent used Putty before so hope it will be an easy job. Otherwise I need to cut up the sql (80mb) in to pieces of max 2mb to upload them with phpmyadmin.

 

[jmstacey]

Yep, you would have to use a program like putty.
http://help.directadmin.com/item.php?id=10

If your account has ssh enabled on both servers you can run this command to backup your database, although you should be able to backup a large database through phpmyadmin since its only uploads that have the 2mb limit.

mysqldump -u [uname] -p [pass] [dbname] > [backupfile.sql]

[uname] - your database username
[pass]- the password for your database
[dbname] - the name of your database
[backupfile.sql] - the filename for your database backup

Rhen you can transfer the .sql file that was created to your new server and run this command which should restore the database for you

mysql -u [uname] -p [pass] [dbname] < backup.sql

Its been a long time since I've done it this way, but if I recall correctly you will need the database you will be restoring to already created but empty. You can do that part through phpMyAdmin.

If you don't have ssh on the server you are trying to restore some things to try, did you make sure that when you backed up the file through phpmyadmin on your old server that you saved it in the same format DirectAdmin is trying to restore it as? (Creating the database should be part of it I believe, not just the tables)

 

[sander815]

if you don't have ssh access you can try splitting the db file up into smaller pieces with wordpad or so
I had to import a 40 mb Db long time ago, and always did it that way. LOt of work, but it worx

 

[fusionictnl]

You can also make a user on your DB (temporarly) that can connect remotely to the database from a specific IP. And use a GUI to import it in the Database MysqlGUI or something

 

[Egon]

Thanks for all the information. A few questions about the options presented:

..If your account has ssh enabled on both servers you can run this command to backup your database, although you should be able to backup a large database through phpmyadmin since its only uploads that have the 2mb limit.
..

If i can get SSH enabled where do i need to put the sql file. Do i just upload it by ftp? And to what folder, or is a special SSH folder created after SSH is enabled.

You can also make a user on your DB (temporarly) that can connect remotely to the database from a specific IP. And use a GUI to import it in the Database MysqlGUI or something

Can you tell step-by-step how to do that? Do i need to have certain permissions? I have downloaded MysqlGUI and what do I need to then? Its asking me for port where mysql is listening to and set by default to port 3306. How do I found out what port its listening to?

Furthermore I added a test user to my database who has all permissions, but how do set the specific ip to connect remotely to the database?

 

[jmstacey]

when you log into ssh it will dump you at /home/username

If you upload the .sql to your primary domain's public_html directory you would do something like

cd public_html (or cd domains/yourdomain/public_html)

And then execute the mysql command in the same directory as the .sql file is located

 

[fusionictnl]

Can you tell step-by-step how to do that? Do i need to have certain permissions? I have downloaded MysqlGUI and what do I need to then? Its asking me for port where mysql is listening to and set by default to port 3306. How do I found out what port its listening to?

Furthermore I added a test user to my database who has all permissions, but how do set the specific ip to connect remotely to the database?

You would have to ask an DA Administrator of you're hosting company to grand acces to your account from a differend IP than Localhost. or ask for SSH access.

The port is standard in most cases.

 

[Egon]

Thanks guys for all the information. But i did some research on the internet and found a script called Bigdump by Alexey Ozerov.

It did the job for me. I just adjusted the database connection settings in bigdump.php, downloaded the dump from my old server and uploaded it to the new one. (gzip-format), including the script. Run the script and voila job was done.

Allthough i´m not related to him I encourage this script for everyone who is trying to import a huge MySQL Dump.

Bigdump :

Description: Staggered import of large and huge MySQL Dumps (like phpMyAdmin 2.x dumps and even GZip compressed dumps). Do you want to restore the huge backup of your mySQL database (or a part of it) into the new or the same mySQL database? You can't access the server shell and you can't import the dump using phpMyAdmin due to hard memory/runtime limit of the web-server. BigDump will do it even through the webservers with hard runtime limit and those in safe mode in a couple of short sessions.

Script can be found here: http://www.ozerov.de/bigdump.php

 

[congkai]

Rhen you can transfer the .sql file that was created to your new server and run this command which should restore the database for you

mysql -u [uname] -p [pass] [dbname] < backup.sql

if we have a file called "backup.sql" which contains an export of the database records with the SQL INSERT commands, we would populate the database called "testdb" with username "johndoe" and password "1234" by issing the following at the command prompt:

mysql -u johndoe -p1234 testdb < backup.sql


notice that there is no space between the "-p" switch and the password of "1234" -- this is intentional. if you put a space, mysql won't realize it's a password and you will be prompted to enter your password at a Password: prompt.