I am in the process of transitioning my website from my local machine to a dedicated server in a far away land. The part that's tough for me is getting the mySQL databases transferred. I have 4 databases that I need to transfer.
So far I have done the following. I went into mySQL Administrator, went to "backup", clicked on the four databases I wanted to backup, then hit "Execute backup now". The program then spit out a 23GB file called "ABC 20060706 1234" (name changed). It's a SQL file and opens with notepad. Have I gone down the right path thus far? I know the file is big, and I'm hoping I'm able to FTP it to my server.
If I've done the wrong thing, can someone tell me the right thing to do? If I'm on the right track, what do I do now? How do I load the dbs onto my new server so that they work?
Any help would be appreciated.
Loading databases onto server
Moderator: General Moderators
I have tables with data for 32 states, with about 10 columns per state with FLOAT entries. There are between 3 and 14 million rows per state, as the data is based on coordinates and covers the entire breadth of each state. Unfortunately, much of the size (I think) is due to a really big ibdata1 file (it's 14.7 GB). Unfortunately mySQL never decreases the size of that file, so after all of the adding and deleting that went on while forming these tables, the file kept growing. I hear that the file starts over when you load it onto another server, so hopefully that's true. I'm using mySQL version 4.1.10a-nt.
Any ideas?
Any ideas?
Ah. I don't know that you need to transfer the ibdata1 file, it's an InnoDB specific file generated by the server. I am not certain, but I believe simply transfering your actual data to the server is all you'll need to do. You should ask the server admin on the other end.
As far as the other data is concerned, since it's non binary data and alot of float values, it shouldn't be difficult to seperate that data and do it in small chunks, i.e. each table / database in it's own file, depending on which makes more sense. The 'mysqldump' command line utility is great and offers alot of options. I might be tempted to do a network transfer, such as
This allows you to pipe the output from the dump directly onto the other server. You can also use mysqldump to output data into seperate files for easy upload.
As far as the other data is concerned, since it's non binary data and alot of float values, it shouldn't be difficult to seperate that data and do it in small chunks, i.e. each table / database in it's own file, depending on which makes more sense. The 'mysqldump' command line utility is great and offers alot of options. I might be tempted to do a network transfer, such as
Code: Select all
mysqldump -u user1 -p databasename | mysql -u user2 -p -h mysql.remote.com -C databasenameSo if I do decide to save the data into individual files, how would I go about uploading them once I get them to the other server? And is there a certain place I should put the files on the server (mySQL data folder, etc)? Sorry if I seem a little slow; this is my first time transferring a database.
Thanks.
Thanks.
Ok, no worries. You should never copy datafiles over into the mysql/data directory (in fact, unless you're 'root' on the other machine or an administrator, you can't). You use 'mysqldump' for dumping the data into an ascii file you can upload to the server, then once on the server you use the 'mysql' command line utility to load the data into MySQL. My mention of piping data straight through to the server does this in one step. Otherwise, you do
Step 1, dump the data from your databases or individual tables:
(assumes Windows, which I'm basing on your first post)
etc etc until you're done.
Step 2, upload these .sql files via SCP (secure copy) or SFTP (secure FTP) to the new server. I strongly recommend using one of these methods vs standard FTP, especially if dealing with sensitive data. Once uploaded, they should be in a directory you can access on the server.
Step 2.5, I should mention is important. If you have shell access to the new server, this is the best and easiest, because you should be able to simply reverse the process with the 'mysql' command line utility on the new server itself. If you don't, using something like phpMyAdmin will hopefully enable you to use the files you've uploaded. Again, these are important details you want to discuss with the server admin on the other end before you do anything.
Step 3, use one of the methods mentioned to upload the data into MySQL itself.
Step 1, dump the data from your databases or individual tables:
(assumes Windows, which I'm basing on your first post)
Code: Select all
mysqldump -u user1 -p database1 > c:\path\to\file\database1.sql
mysqldump -u user1 -p database2 > c:\path\to\file\database2.sqlStep 2, upload these .sql files via SCP (secure copy) or SFTP (secure FTP) to the new server. I strongly recommend using one of these methods vs standard FTP, especially if dealing with sensitive data. Once uploaded, they should be in a directory you can access on the server.
Step 2.5, I should mention is important. If you have shell access to the new server, this is the best and easiest, because you should be able to simply reverse the process with the 'mysql' command line utility on the new server itself. If you don't, using something like phpMyAdmin will hopefully enable you to use the files you've uploaded. Again, these are important details you want to discuss with the server admin on the other end before you do anything.
Step 3, use one of the methods mentioned to upload the data into MySQL itself.