copy tables from one database to another

Question

I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't change that fact). So I thought I can just use the same db for the to projects, since they use different tables and not overlaps.

One would think there is a simple way to do so.

There are not a lot of tables, so I can copy it manually one by one, but it seems that there is also no simple command to copy a table including all of the table rule's and restrains. (Working on a mac, so cant use sql server management studio).

It seems unreasonable that there is no simple way to do this. Is there one?

Answer 1

Consider the source database name is databaseA and destination is databaseB.

Step-1. Take dump of tables to be loaded to new host:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA > databaseA.sql

Notes:

• --skip-drop-tables is used to avoid accidental override of tables from databaseA to databaseB.
• If there is no replication and active writes, you can ignore --single-transaction and --master-data options. (Harmless to keep them as well.)
• If you have only a few of the tables to be dumped them you can specify list of tables (space delimited) after database name in above command as follows:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA table1 table2 ... > databaseA.sql

Step-2. Backup the destination database: (Just in case something goes wrong)

mysqldump -uroot -pPASSWORD --routines --databases databaseB > databaseB.sql

Step-3. Load the database:

mysql -uroot -p databaseB -hHOSTNAME < databaseA.sql

Notes: - Here HOSTNAME is the hostname/ipaddress of the destination

Just to add, if your databases are on same machine, (which is not the case here, but still saying) you can use RENAME operations to move tables:

rename table databaseA.table1 to databaseB.table1;

You can generate these SQLs from information_schema database and source it to mysql to quick action.