Hi all,

i need to move one of my sites to a new server and it has a database with 88525 tables (quite a few). As i cant even access the database in phpmyadmin, i know there is a way you can backup via ssh and then move it over.

Does anybody have any guides or tips on how i can do this?

Thanks in advance,

Rick.M

Tables or rows/records? That's a huge number of tables, if so. Never had to move one that large, but this is a typical backup / restore via SSH.

Backup [drop table if exists]:
mysqldump --opt -u username -p database_name > dumpfile.sql

Restore:
mysql -u username -p database_name < dumpfile.sql

Great, thanks Bear i will try that :) (and its tables...very big, i know)

That seems to be an aweful lot of tables. Warning bells about DB design should be going off, unless its a highly specialized database.

Anyhow, you can also directly transfer the database to the remote server if you have remote DB access at both servers (not likely, but hey why not ask).

In addition, for future considerations -- try the excellent MySQL Administrator (http://dev.mysql.com/downloads/administrator/index.html) program from MySQL AB. It allows for more 'managed' backups and has no limit on the size of databases. You would need to have remote access to your database, since its a Windows application and you probably want to run it from a remote client. In addition, it also allows you to manage your database server (for example, setup replication and slaves, add users, etc.) Give it a shot, its free.

Thanks fyrestrtr, i will give it a shot.

I cant help the size of the database as its a free php nuke hosting service and every user takes up around 25-30 tables each...with around 700 users its quite a shove on the server & database.

Good grief. You are a brave soul if you are dealing with free php-nuke hosting service.

Nevermind what I said about highly specialized. I didn't realize you were talking about nuke. :P

Forgot to mention on my earlier post, there is a way to run MySQL Administrator (and really, any MySQL frontend) if you have SSH access, but not remote MySQL access. You can tunnel the MySQL port to your local computer. By default, MySQL runs on port 3306. So in your favorite SSH client, forward that port to your local computer, then just give 'localhost' as the server in your MySQL client program. Of course, ensure nothing else is running on 3306 on your local box (like a MySQL server).

Other ways to do the same is to add your static IP in the 'access hosts' option in the MySQL settings of cpanel -- you would not need to do the tunneling then.

I really should write up a tutorial on this.

I know...i like going out on the limb :p

I have fortress installed and all sites are monitored...there shouldnt be too much of a problem :)