I have server with very big mysql database (160GB).
How easy way to move this DB to another server?
migrated from stackoverflow.com Mar 8 '12 at 12:10
This question came from our site for professional and enthusiast programmers.
When you say "move to another server" we are assuming you want to move to another server running MySQL and not move to a different SQL server (i.e. postrgres or MSSQL).
There are broadly two options. Transfer the
Copying the whole mysql directory will mean less data being transferred and an exact replication of the database from one machine to the other. However, the MySQL versions have to be the same (I believe that in theory minor versions can alter but I wouldn't count on this). You can stop MySQL, tar up the directory and use
Doing an export/import process will create a massive text file with all your data saved as SQL insert statements (and with the table create statements at the beginning to set the database up). This should be importable into a different MySQL database version but, again, if the versions of the database are massively different then things could go wrong. It is usually best to keep MySQL versions the same. The main problem with doing it this way is, with 160GB of data already in your database, the dump will be very large as there will be extra data from the SQL statements. You can use some of the pipe commands above that people have listed to send the data to the remote machine or you could dump the file out (piping it through gzip to compress it) and scp it across (assuming you have enough disk space).
Another technique you could use is to pipe the mysqldump command through gzip, then through netcat (which sends it over the network) to another netcat process running on the server machine which pipes it into gzip there and finally into mysql. This has the benefit of compressing the data as it goes over the wire and doesn't create temporary files.
On the target machine:
On the source machine
Hope this helps explain some of the issues. If you think I've made a mistake in here, post a comment. Double check the man page for commands as I don't have a MySQL database handy to test this with.
Having such a big database, I would recommend to copy the datadir of MySQL instead doing a dump:
You can find the path to your MySQL data dir in the MySQL config file (my.cnf).
Be aware, that you should use the same version of MySQL on both servers! If not, you should export and import your data using MySQL dumps.
MySQL databases are stored in a very simple method:
dir = database names files in dir are tables and indexes etc.
so locate where your databases are located - possibly something like:
In here you'd have all your database dir's, in those dir's the tables.
Now that you have found them stop MySQL and just tar zip the directory structure.
tar -zcf mysqldatabases.tar.gz *
Now you have all your MySQL stuff in one zipped file. Copy this to the new server's data directory and unzip / untar it.
You can pipe the output of the sqldump
This is possible only if you have remote access to the database.
A dump and restore over the network can be accomplished using this syntax.
Remember to prevent new writes to the database during this maneuver.