Home » How can I move a database from one server to another?

How can I move a database from one server to another?

Solutons:


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,

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword 

You can do all databases with

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

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.

I recently moved a 30GB database with the following stragegy:

Old Server

  • Stop mysql server
  • Copy contents of datadir to another location on disk (~/mysqldata/*)
  • Start mysql server again (downtime was 10-15 minutes)
  • compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
  • copy the compressed file to new server

New Server

  • install mysql (don’t start)
  • unzip compressed file (tar -xzvf mysqldata.tar.gz)
  • move contents of mysqldata to the datadir
  • Make sure your innodb_log_file_size is same on new server, or if it’s not, don’t copy the old log files (mysql will generate these)
  • Start mysql

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:

Binary portability is important if you
want to take a binary backup that was
made on one machine and use it on
another machine that has a different
architecture. For example, using a
binary backup is one way to copy
databases from one MySQL server to
another.

For MyISAM, binary portability means
that you can directly copy the files
for a MyISAM table from one MySQL
server to another on a different
machine and the second server will be
able to access the table.

For InnoDB, binary portability means
that you can directly copy the
tablespace files from a MySQL server
on one machine to another server on a
different machine and the second
server will be able to access the
tablespace. By default, all the InnoDB
tables managed by a server are stored
together in the tablespace, so
portability of the tablespace is a
function of whether all individual
InnoDB tables are portable. If even
one table is not portable, neither is
the tablespace.

MyISAM tables and InnoDB tablespaces
are binary portable from one host to
another if two conditions are met:

  • Both machines must use two’s-complement integer arithmetic
  • Both machines must use IEEE floating-point format or else the
    tables must contain no floating-point
    columns (FLOAT or DOUBLE)

In practice, those two conditions pose
little restriction. Two’s-complement
integer arithmetic and IEEE
floating-point format are the norm on
modern hardware. A third condition for
InnoDB binary portability is that you
should use lowercase names for tables
and databases. This is because InnoDB
stores these names internally (in its
data dictionary) in lowercase on
Windows. Using lowercase names allows
binary portability between Windows and
Unix, to force the use of lowercase
names, you can put the following lines
in an option file:

[mysqld]
lower_case_table_names=1

If you configure InnoDB to use
per-table tablespaces, the conditions
for binary portability are extended to
include the .ibd files for InnoDB
tables as well. (The conditions for
the shared tablespaces still appliy
because it contains the data
dictionary that stores information
about all InnoDB tables.)

If conditions for binary portability
are not satisfied, you can copy MyISAM
or InnoDB tables from one server to
another by dumping them using some
text format (for example, with
mysqldump) and reloading them into the
destination server.

There are two major ways based on storage engine to move individual tables.

For the given example we will suppose the following:

  1. datadir is /var/lib/mysql
  2. database called mydb
  3. table in mydb database called mytable.

MyISAM tables

If mydb.mytable uses the MyISAM storage engine, the table will physically be manifested as three separate files

  1. /var/lib/mysql/mydb/mytable.frm (.frm file)
  2. /var/lib/mysql/mydb/mytable.MYD (.MYD file)
  3. /var/lib/mysql/mydb/mytable.MYI (.MYI file)

The .frm contains the table structure
The .MYD contains the table data
The .MYI contains the table index page

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:

LOCK TABLES mydb.mytable READ;
SELECT SLEEP(86400);
UNLOCK TABLES;

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.

InnoDB tables

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 SET GLOBAL innodb_max_dirty_pages_pct = 0; from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step.

Step 03) rsync --archive --verbose --stats --partial --progress --human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql

Step 04) Repeat Step 03 until an rsync takes less than 1 minute

Step 05) service mysql stop on ServerA

Step 06) Perform one more rsync

Step 07) scp ServerA:/etc/my.cnf ServerB:/etc/

Step 08) service mysql start on ServerB

Step 08) service mysql start on ServerA (optional)

Give it a Try !!!

CAVEAT

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

Related Solutions

Why not drop the “auto” keyword? [duplicate]

Your proposal would be rejected on the basis of backward compatibility alone. But let's say for the sake of argument that the standards committee like your idea. You don't take into account the numerous ways you can initialize a variable widget w; // (a) widget...

Recursive to iterative using a systematic method [closed]

So, to restate the question. We have a function f, in our case fac. def fac(n): if n==0: return 1 else: return n*fac(n-1) It is implemented recursively. We want to implement a function facOpt that does the same thing but iteratively. fac is written almost in...

How can I match values in one file to ranges from another?

if the data file sizes are not huge, there is a simpler way $ join input1 input2 | awk '$5<$4 && $3<$5 {print $2, $5-$3+1}' B100002 32 B100043 15 B123465 3 This Perl code seems to solve your problem It is a common idiom: to load the entire...

Javascript difference between “=” and “===” [duplicate]

You need to use == or === for equality checking. = is the assignment operator. You can read about assignment operators here on MDN. As a quick reference as you are learning JS: = assignment operator == equal to === equal value and equal type != not equal !==...

Compiler complains about misplaced else [closed]

Your compiler complains about an misplaced else because, well, there is an else without a preceding if: // ... for (j=1; j<n-i; j++) { if(a[j]<=a[j+1]) { // ... } // END OF IF } // END OF FOR else { continue; } // ... The else in your code does not follow...

Bootstrap – custom alerts with progress bar

/* !important are just used to overide the bootstrap css in the snippet */ .alertContainer { border-radius: 0 !important; border-width: 0 !important; padding: 0 !important; height: auto !important; position: absolute !important; bottom: 15px !important; left:...

How to Garbage Collect an external Javascript load?

Yes, s.onload = null is useful and will garbage collect! As of 2019, it is not possible to explicitly or programmatically trigger garbage collection in JavaScript. That means it collects when it wants. Although there is cases where setting to null may do a GC...

Math programming with python

At first, what you are looking for is the modulo operator and the function math.floor() Modulo from wikipedia: In computing, the modulo operation finds the remainder after division of one number by another (sometimes called modulus). for example: 12%12=0...

Android slide over letters to create a word [closed]

Here some advice you can use: First for each cell you can create an object that represents the state of that cell: class Cell { char mChar; int row,column; boolean isSelected; } then you can create a 2D array of your cells Cell[][] mTable = ... For views you...

Sum two integers in Java

You reused the x and y variable names (hence the variable x is already defined in method main error), and forgot to assign the ints read from the Scanner to the x and y variables. Besides, there's no need to create two Scanner objects. public static void...

Extend three classes that implements an interface in Java

Using this simplified implementation of the library, using method() instead of M(): interface IFC { void method(); } class A implements IFC { public void method() { System.out.println("method in A"); }; } As akuzminykh mentions in their comment You'd write a...

How to set the stream content in PHPExcel? [closed]

Okey, First thing first PHPExcel_Worksheet_MemoryDrawing() can't solve your problem if you insist to use stream content and pass that to your worksheet your PDF will not render your image. But you can use `PHPExcel_Worksheet_Drawing()' if you want to render...

How to remove all files from a directory?

Linux does not use extensions. It is up to the creator of the file to decide whether the name should have an extension. Linux looks at the first few bytes to figure out what kind of file it is dealing with. To remove all non-hidden files* in a directory use: rm...

Hacker used picture upload to get PHP code into my site

Client side validation The validation code you have provided is in JavaScript. That suggests it is code that you use to do the validation on the client. Rule number one of securing webapps is to never trust the client. The client is under the full control of...

First Time HTML5/CSS Site

Semantically, I would suggest using HTML5 elements more. For example, instead of... <div id="header"> <div id="logo"></div> </div> Use instead: (the ID can stay if you want it to) <header> <div id="logo"></div>...