This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a simple solution to move your InnoDB table from one database to another database in MySQL.
Before a few days ago, one of my team members was looking for this solution, and I found that many of Database Administrators do not know about this simple solution.
The solution is, we have to just rename the table name by specifying a database name.
The MySQL InnoDB engine has two types of tablespaces, one is a shared table space and second is an individual tablespace for each table.
This solution works for both the types of tablespaces.
Important Note: ibdata1 file or all .ibd file should be in one MySQL Data Directory at the same location.
After this demonstration, you can visit InnoDB Tablespace related articles.
Create TWO Sample Databases:
|
1 2 |
CREATE DATABASE ABC; CREATE DATABASE XYZ; |
Create a sample table in database ABC:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE ABC; CREATE TABLE tbl_Students ( RNO INT ,StudName VARCHAR(20) ); INSERT INTO tbl_Students VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Jeeny'),(4,'Roy'); |
Simplest way to move tbl_Students from database ABC to XYZ:
|
1 |
RENAME TABLE ABC.tbl_Students TO XYZ.tbl_Students; |
Check tbl_Students in XYZ database:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE XYZ; SELECT *FROM tbl_Students; +------+----------+ | RNO | StudName | +------+----------+ | 1 | Anvesh | | 2 | Neevan | | 3 | Jeeny | | 4 | Roy | +------+----------+ |
MySQL: Why InnoDB Engine stores all databases in one file (ibdata1)?
