How can I move MySQL tables from one physical server to another?
Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size.
I want to move it to a new server, what's the most efficient way to do this?
My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance,
You can do all databases with
The only problem is when the database is too big and the pipe collapses. In that case, you can do table by table or any of the other methods mentioned below.
You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
I can't remember if mysqldump handles users and permissions, or just the data ... but even if it does, this is way faster than doing a dump & running it. I'd only use that if I needed to dump a mysql database to then re-insert into some other RDBMS, if I needed to change storage options (innodb vs. myisam), or maybe if I was changing major versins of mysql (but I think I've done this between 4 & 5, though)
I recently moved a 30GB database with the following stragegy:
According to the MySQL 5.0 Certification Study Guide, Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following:
There are two major ways based on storage engine to move individual tables.
For the given example we will suppose the following:
If mydb.mytable uses the MyISAM storage engine, the table will physically be manifested as three separate files
The .frm contains the table structure
These files are used interdependently to represent the table from a logical standpoint in mysql. Since these file have no further logical association attach to it, migrating a table from one DB server to another. You can even to this from a Windows server to a Linux Server or a MacOS. Of course, you could shutdown mysql and copy the 3 table files. You could run the following:
in one ssh session to hold table as read only and hold the lock for 24 hours. One second later, perform the copy in another ssh session. Then kill the mysql session with the 24 hour lock. You need not wait 24 hours.
Based on the aforementioned quote from the Certification book, there are many factors that govern how to backup a specific InnoDB table. For sake of simplicity, clarity, and brevity, simply perform a mysqldump of the desired table using the --single-transaction parameters to have perfect point-in-time dump of the table. No need to cncern yourself with InnoDB semantics if you just want one table. You can reload that dumpfile to any MySQL server of your choose.
Since two questions were merged here (jcolebrand): EDIT
If you are more than willing to live with some slow DB performance, you can perform a series of rsyncs from the old server (ServerA) to the new server (ServerB) even while mysql is still running on ServerA.
Step 01) install the same version of mysql on ServerB that ServerA has
Step 02) On ServerA, run
Step 03) rsync /var/lib/mysql of ServerA to /var/lib/mysql on ServerB
Step 04) Repeat Step 03 until an rsync takes less than 1 minute
Step 06) Perform one more rsync
Step 07) scp ServerA:/etc/my.cnf to ServerB:/etc/.
Give it a Try !!!
You can create a replication slave like this. Just remember to have server-id explcitly set in the master /etc/my.cnf and a different number for server-id in the slave /etc/my.cnf
There might be this posibility where you move the actual database files ( for my install they are located at /var/lib/mysql ) , but i'm not realy shure how it will act/work out .
If you just want to move a specific table try:
You can specify more table names above, in the same command. Once the command completes, move the databasename.tablename.sql file to the other server and then restore using:
Note that the back .sql file is created using the mysqldump program, and the restore is done directly into mysql.
You're going to need to take a downtime. It's going to take a while depending on what your network speed is. I'm going to assume your running MySQL on Linux/Unix. Here's the process I use:
Then proceed as usual getting the local MySQL set up.
*Note: you can also use the -c parameter with rsync to add a checksum to the transfer, however this will be sloooow depending on CPU speed.
I think all of the earlier answers probably work fine, but don't really address the issue of setting a database name during the transfer.
This is how I just did it with bash:
You might be better off using
On my source server:
On my destination server:
On either machine to see progress:
This all assume you have MySQL configuration file in your home directory on both machines and set the permissions:
are you moving it to another mysql server db ? if so use, do an export on it
Generic linux method:
edit the datadir (and socket) for both mysqld and mysqld_safe (if applicable) to point to the new location, then
I posted this because no one seemed simply list out the least amount of steps to do this and I feel it's the simplest way personally.
I can confirm that DTest's method also works for copying between ubuntu and osx.
To copy all of the databases without having to do any dumping or similar:
Make sure you have a clean mysql of mysql (installed the dmg downloaded from mysql http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.63-osx10.6-x86_64.dmg), that (VERY IMPORTANT) has never been run.
Copy the /var/lib/mysql/ folder contents from the ubuntu machine on top of /usr/local/mysql/data/ contents on the mac. To get access to get folder on the ubuntu machine I had to use sudo i.e.:
I copied the folder using scp.
Before you start take a copy of the mysql folder on the mac to make sure you don't mess up anything.
After copying the folder, do the following on the mac machine:
Start the mysql server for the first time (from the preferences pane under System Preferences->mysql). All users and databases should now be set up correctly.
This worked with mysql 5.1.61 on ubuntu 64 bit 11.10 and mysql 5.1.63 on osx lion (macbook pro).
Perhaps this is a better way of doing it:
Version 1: data-files copy (MYISAM only)
Version 2: mysqldump
Version 3: master/slave + mysqldump/file-copy
to copy small tables use:
You can try a GUI tool to copy database or table. You can try SQLyog. Which has a copy Database/Table option. Click Structure Only to copy the selected object(s) with no rows (the destination table is empty) or click Structure And Data to copy the object(s) along with its data in the destination database.
If you want a free version you can download from here
I would suggest the two simple steps to transfer the entire database from one server to another.
Step 1: Do a full-backup of databases in source server using mysqldump.
Step 2: You can use rsync command to transfer the entire databases to the destination server.