How to import or export a large database?

WaveLength posted 3 years ago in Import/Export
I am trying to move our website to a new server. This requires moving a fairly large database. I was advised to use phpMyAdmin, but that program gave me a timeout error. Anyway, I prefer HeidiSQL.

Unfortunately, I don't know how to proceed. Here is what I have already tried:

I started by creating a new database on the destination folder. It has no tables yet. In HeidiSQL, I logged into both the old and the new databases, and attempted to use Maintenance / Export. I clicked on the old database in the left pane, but when I select the correct database in the output field in the right pane, the "database" field says "Same as on source server". I believe this could be a problem, because my old and new web hosting servers require the database to be named differently. Proceeding regardless, I get a 1044 access denied error.

Next, I moved the database tables to my local server, renamed the database to match the database name on the new server, and tried exporting again. But again, I got an 1044 access denied error.

Next, I exported the database to a SQL file on my local drive. In HeidiSQL, I clicked on the new empty database, and then click Tools / Load SQL File. A warning pop up appeared, saying "One or more of the selected files are larger than 5 MB". I have two choices: YES, to run the file without using the MySQL editor, or NO, to run the file with the MySQL editor. When I clicked the NO button, I got an out of memory error. And when I clicked the YES button, I got a 1046 "No Database Selected" error.

What should I do?

Thanks in advance for any suggestions.

More info:
Size of my database SQL file: 272 MB
My local computer is Win XP SP3 running Xampp 2.5.
I am attempting to copy the database from a remote Windows web server to a remote Linux web server.
I am using the current version of HeidiSQL, as of February 2012.

kalvaro posted 3 years ago
1. If the source database is foo and the target database is bar, don't choose Same as on source server: choose bar.

2. The 1044 access denied error is triggered by the MySQL server when the given user and password combination is invalid (the exact details are in the part of the message you've stripped). This contradicts your previous statement that you've logged into both databases.

3. The 1046 No Database Selected means that you tried to run a SQL query that uses a table but you didn't tell MySQL what database to use. Choose a database first.

Sorry if I can't figure out you exact doubts.
WaveLength posted 3 years ago
Kalvaro, thanks for your advice. After more testing, this is what I found:

If I open the remote database by itself, there is no problem. If I open the local database and the remote db together, then I get the error.

The solution: Open just the remote db, add a table (the db had no tables yet), then open the local database too, and there is no problem. Exporting can begin.

I burned a full day on this, so I hope this post saves someone some time.

Please login to leave a reply, or register at first.