How do i copy a mysql database to another database?

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

How do I copy a database from one database to another in MySQL?

We need to follow these steps to copy a database to another database:.
First, use the CREATE DATABASE statement to create a new database..
Second, store the data to an SQL file. ... .
Third, export all the database objects along with its data to copy using the mysqldump tool and then import this file into the new database..

How do I copy a database from one database to another?

On either the source or destination SQL Server instance, launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases..
Move. Move the database to the destination server..
Copy. Copy the database to the destination server..
Source. ... .
Status. ... .
Refresh..

How do I copy and paste in MySQL?

First you start by selecting the window with the command line. This can be done by alt + tab . Then when the command line windows is active you can paste the content of your clipboard with: alt + space Then go trough the menu with e and then p .

Can I copy MySQL data directory to another server?

You have to shut down mysql server (which is not good, if it's a production server) You have to make sure the permission of data (mysql) directory is same as the previous one. You will have to monitor the mysql_error log while starting the second server.