1

I have server with very big mysql database (160GB).

How easy way to move this DB to another server?

3
  • I doubt there is an easy way Mar 8, 2012 at 12:09
  • 1
    Just a warning about copying the datadir directly as some of the answers have suggested: you must shut down MySQL first. If you don't shut your MySQL server down first, with MyISAM tables this can corrupt the tables as they are copied and you will need to repair them with 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.
    – Ladadadada
    Apr 5, 2012 at 9:40
  • Also, if you are using InnoDB and want to keep the server online while you transfer the data, using XtraBackup.
    – Ladadadada
    Apr 5, 2012 at 9:42

5 Answers 5

2

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.

2

Having such a big database, I would recommend to copy the datadir of MySQL instead doing a dump:

  1. Shutdown the MySQL services on server A
  2. Compress the MySQL data directory (might be found under /var/lib/mysql), which contains all the data
  3. Copy your MySQL configuration (might be found under /etc/mysql/my.cnf)
  4. Copy both via SSH/SCP to server B
  5. Extract the data, place the config file in the same directories
  6. (Re)start MySQL services on server B

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.

1

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.

1

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.

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .