I have server with very big mysql database (160GB).
How easy way to move this DB to another server?
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 /var/lib/mysql
dir to the new server as it is or do an export and import process.
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 scp
to copy it to the other machine. Then untar the directory on the other machine and start up MySQL. If you want to do this copy on a regular basis you may want to consider doing a straight rsync
of the lib dir so that subsequent runs of the rsync
command only copies the differences across.
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).
mysqldump -u<user> -p<password> <database name> | gzip > db_dump.sql.gz
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:
nc -l 55555 | gzip -d -c | mysql <database name> -u<user> -p<password>
On the source machine
mysqldump -u<user> -p<password> <database name> | gzip | nc <ip of target server> 55555
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:
/var/lib/mysql
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
mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver
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.
mysqldump db-name | mysql -h remote-server db-name
Remember to prevent new writes to the database during this maneuver.
repair table
and may result in losing data, with InnoDB you will end up not being able to start MySQL on your new server. Make sure you shut down MySQL before doing this.