Moving large database (3.5 Gig) to a new server with PhpMyAdmin

[media]

Before upgrading 4.0 I am trying to move my board to a better server (I am still on 3.4.8)

Old server: Centos, Webmin

New server: Centos, Cpanel

I have tried to move with phpmyadmin, no success...

I tried every possible way to complete the job, but no luck....

I am able to download the database, but when I try to upload to new server with phpmyadmin, after certain number of  records, it does not upload anymore....

I have edited PHP post size and upload size on CPanel to 2047 Mb....

But somehow on phpmyadmin is not allowing after certain amount of records to be uploaded, anyone have a solution for this????

Only uploads about 2 gig and then stop without warning

Anyone knows any solution please let me know

[RevengeFNF]

Do you have SSH access?

[media]

Do you have SSH access?

​Yes I do; I have Putty on my Windows to connect with....

[Konrad Dąbek]

Maybe try to MySQL Workbench: http://dev.mysql.com/downloads/workbench/

Pretty easy and straightforward app, only disadvantage (for me) is that MySQL Workbench does not show any detailed progress. Dumping big database on slow connection can take ~30 mins.

[sijad]

​Yes I do; I have Putty on my Windows to connect with....

login to ssh, then run this:

mysql -u {username} -p {databasename} < path_to_file.sql

another option could be http://www.mysqldumper.net/

but ssh way is faster and more stable

 

[media]

login to ssh, then run this:

mysql -u {username} -p {databasename} < path_to_file.sql

another option could be http://www.mysqldumper.net/

but ssh way is faster and more stable

 

​Sijad,

Is that code for export or import???? Because I have to export first and then import to the new server...

Thanks

[Rhett]

mysqldump via ssh is the only good method for a large database and restore the same vai, via ssh.

[sijad]

​Sijad,

Is that code for export or import???? Because I have to export first and then import to the new server...

Thanks

​It's for import, please remember you can use your PHPMYADMIN exported backup for importing too but if you want export a new backup you can use mysqldump via ssh (in your old server):

mysqldump -u {username} -p {databasename} | gzip > backup.sql.gz

for copy backup to new server you can use scp ('~' is the path to {new_server_username} home directory you can change it to somewhere else if you wish):

scp backup.sql.gz {new_server_username}@{new_server_ip_address}:~

login to new server via ssh then run:

gunzip backup.sql.gz && mysql -u {username} -p {databasename} < backup.sql

 

[RevengeFNF]

What i alwasy use is:

Export the database in the old server:

mysqldump -u {user} -p{password} {database} > backup.sql

I then transfer the backup.sql to the new server and then:

mysql -u {user} -p{password} {database} < backup.sql

Easy.

[不中用]

.

and use gzip or other zip like compression !! It will make a big difference ..

like this ..

 

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

 

.

[media]

 

mysqldump -u {user} -p{password} {database} > backup.sql

user? MySQL? or database owner?

Where does this query save the backup.sql file?

mysql -u {user} -p{password} {database} < backup.sql

Same question from here too

user?

where am I going to put that backup.sql file to upload it to new server?????

[不中用]

 

mysqldump -u {user} -p{password} {database} > backup.sql

user? MySQL? or database owner?

Where does this query save the backup.sql file?

mysql -u {user} -p{password} {database} < backup.sql

Same question from here too

user?

where am I going to put that backup.sql file to upload it to new server?????

​.

ohh oooooooooooooooooohhhh

You have Linux access to your server ? Or how you are accessing your MySQL server ?

 

.

[RevengeFNF]

 

mysqldump -u {user} -p{password} {database} > backup.sql

user? MySQL? or database owner?

Where does this query save the backup.sql file?

mysql -u {user} -p{password} {database} < backup.sql

Same question from here too

user?

where am I going to put that backup.sql file to upload it to new server?????

​In shh you enter in the folder you want. That command will use that folder.

user and password, is the user login for your mysql.

[media]

​In shh you enter in the folder you want. That command will use that folder.

user and password, is the user login for your mysql.

​Thank you,

I will try and let you guys know what happen...

[media]

Ok guys finally done...

But I did it in a different way, after the ideas you guys gave me here, I researched a little bit and found this

Backup a local database and restore to remote server using single command:

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
                 -u root -ptmppassword --host=remote-server -C sugarcrm1
[Note: There are two -- (hyphen) in front of host]

It did the trick....

[Xelphos]

Ok guys finally done...

But I did it in a different way, after the ideas you guys gave me here, I researched a little bit and found this

Backup a local database and restore to remote server using single command:

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
                 -u root -ptmppassword --host=remote-server -C sugarcrm1
[Note: There are two -- (hyphen) in front of host]

It did the trick....

Interesting, I have always done it via:

To backup:

mysql -u(database_username) -p(database_user_password) (forum_database) < forum_backup.sql

And to restore:

mysql -u(database_username) -p(database_user_password) (forum_database) > forum_backup.sql

 

The .sql dump will be saved in the same directory that you are currently running the command in. In my case /home/(my_username). Restoring is basically the same process, just make sure the .sql file is in the same directory you are running the command in.

[Rhett]

You have those backwards   

 

backup is > 

restore is <

 

[RevengeFNF]

For the backup is better to use mysqldump instead of mysql.