Transportable tablespaces allows copying individual
InnoDB tables from
one MySQL instance to another.Here is a basic example of it's usage.
I will create a table and put some data in it and then move it to a new databases, using MySQL transportable tablespaces:
DB156> use sinrec
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
DB156> create table sb2 select * from sb;
Query OK, 22528 rows affected (1.35 sec)
Records: 22528 Duplicates: 0 Warnings: 0
root [sinrec]> show tables;
+------------------+
| Tables_in_sinrec |
+------------------+
| sb |
| sb2 |
+------------------+
2 rows in set (0.00 sec)
root [sinrec]> show create table sb2;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| sb2 | CREATE TABLE `sb2` (
`col1` int(11) DEFAULT NULL,
`col2` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root [sinrec]> select count(*) from sb2;
+----------+
| count(*) |
+----------+
| 22528 |
+----------+
1 row in set (0.01 sec)
Set the target location of the table. Create a new database and create an empty table with the correct definition:
root [(none)]> create database sinrec2;
Query OK, 1 row affected (0.02 sec)
root [sinrec2]> CREATE TABLE `sb2` ( `col1` int(11) DEFAULT NULL, `col2` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
Not we can start the process:
DB156> flush table sb2 for export;
Query OK, 0 rows affected (0.01 sec)
In the mysqld.log
2013-05-13 15:54:23 11099 [Note] InnoDB: Sync to disk of '"sinrec"."sb2"' started.
2013-05-13 15:54:23 11099 [Note] InnoDB: Stopping purge
2013-05-13 15:54:23 11099 [Note] InnoDB: Writing table metadata to './sinrec/sb2.cfg'
2013-05-13 15:54:23 11099 [Note] InnoDB: Table '"sinrec"."sb2"' flushed to disk
Copy the .ibd and .cfg files to another location. You don't need to copy the .frm:
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec ]$ cp sb2.ibd ~/tmpfiles/
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec ]$ cp sb2.cfg ~/tmpfiles/
DB156> unlock tables;
Query OK, 0 rows affected (0.00 sec)
2013-05-13 15:58:03 11099 [Note] InnoDB: Deleting the meta-data file './sinrec/sb2.cfg'
2013-05-13 15:58:03 11099 [Note] InnoDB: Resuming purge
root [(none)]> use sinrec2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root [sinrec2]> alter table sb2 discard tablespace;
Query OK, 0 rows affected (0.04 sec)
Once this commands completes the .idb file for the new table is deleted.
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec ]$ ls -lrt ../sinrec2
total 16
-rw-rw---- 1 mysql mysql 61 May 13 15:53 db.opt
-rw-rw---- 1 mysql mysql 8590 May 13 15:53 sb2.frm
Copy the .ibd and .cfg files to the new database location
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec2 ]$ cp ~/tmpfiles/sb2.cfg .
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec2 ]$ cp ~/tmpfiles/sb2.ibd .
[mysql@mysql56node1 DB156 /db/DB1/data/sinrec2 ]$ ls -rlt
total 9252
-rw-rw---- 1 mysql mysql 61 May 13 15:53 db.opt
-rw-rw---- 1 mysql mysql 8590 May 13 15:53 sb2.frm
-rw-rw---- 1 mysql mysql 424 May 13 16:00 sb2.cfg
-rw-rw---- 1 mysql mysql 9437184 May 13 16:00 sb2.ibd
root [sinrec2]> alter table sb2 import tablespace;
Query OK, 0 rows affected (0.26 sec)
In the mysqld.log:
2013-05-13 16:08:45 11099 [Note] InnoDB: Importing tablespace for table 'sinrec/sb2' that was exported from host 'mysql56node1'
2013-05-13 16:08:45 11099 [Note] InnoDB: Phase I - Update all pages
2013-05-13 16:08:45 11099 [Note] InnoDB: Sync to disk
2013-05-13 16:08:45 11099 [Note] InnoDB: Sync to disk - done!
2013-05-13 16:08:45 11099 [Note] InnoDB: Phase III - Flush changes to disk
2013-05-13 16:08:46 11099 [Note] InnoDB: Phase IV - Flush complete
Test it works.
root [sinrec2]> select count(*) from sinrec2.sb2;
+----------+
| count(*) |
+----------+
| 22528 |
+----------+
1 row in set (0.02 sec)
So the basic commands are:
Source database
flush
table <name> for export;
- this
creates a .cfg for the table in the datadir area
copy
the .cfg and .idb file to a another location.
unlock
tables
Target database
alter
table <name> discard tablespace;
- removes
the .idb file
copy the
.cfg and .idb to the correct location
alter
table <name> import tablespace;
So the concept is straight forward, use the table files to make a copy of the table in another location.
Update - If the table I am copying the data into has a different name I have renamed the .idb and .cfg to the new table name and run a 'alter table <name> import tablespace' command with success.
Partitioned Table Example
A customer has a large partitioned table which we want to be able to move to another environment. This enabling a quick refresh options for various environments. So here goes testing:
root [sinrec]> create database blog;
Query OK, 1 row affected (0.00 sec)
root [sinrec]> use blog;
Database changed
root [blog]> CREATE TABLE `testpart` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `fname` varchar(25) NOT NULL,
-> `lname` varchar(25) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1821 DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY RANGE (id)
-> (PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
-> PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
-> PARTITION p2 VALUES LESS THAN (3000) ENGINE = InnoDB,
-> PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.09 sec)
root [blog]> insert into testpart select * from parttest.testpart;
Query OK, 6448 rows affected (0.23 sec)
Records: 6448 Duplicates: 0 Warnings: 0
root [blog]> select count(*) from testpart;
+----------+
| count(*) |
+----------+
| 6448 |
+----------+
1 row in set (0.00 sec)
root [(none)]> create database blogthecopy;
Query OK, 1 row affected (0.02 sec)
root [(none)]> use blogthecopy;
Database changed
root [blogthecopy]> CREATE TABLE `testpart` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `fname` varchar(25) NOT NULL,
-> `lname` varchar(25) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1821 DEFAULT CHARSET=utf8
(PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
-> /*!50100 PARTITION BY RANGE (id)
-> (PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
-> PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
-> PARTITION p2 VALUES LESS THAN (3000) ENGINE = InnoDB,
-> PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
-> ;
Query OK, 0 rows affected (0.11 sec)
So the environment is setup. Lets try moving it:
root [blogthecopy]> use blog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root [blog]> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| testpart |
+----------------+
1 row in set (0.00 sec)
root [blog]> flush table testpart for export;
Query OK, 0 rows affected (0.00 sec)
2013-05-13 16:38:24 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart" /* Partition "p0" */' started.
2013-05-13 16:38:24 11099 [Note] InnoDB: Stopping purge
2013-05-13 16:38:24 11099 [Note] InnoDB: Writing table metadata to './blog/testpart#P#p0.cfg'
2013-05-13 16:38:24 11099 [Note] InnoDB: Table '"blog"."testpart" /* Partition "p0" */' flushed to disk
2013-05-13 16:38:24 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart" /* Partition "p1" */' started.
2013-05-13 16:38:24 11099 [Note] InnoDB: Writing table metadata to './blog/testpart#P#p1.cfg'
2013-05-13 16:38:24 11099 [Note] InnoDB: Table '"blog"."testpart" /* Partition "p1" */' flushed to disk
2013-05-13 16:38:24 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart" /* Partition "p2" */' started.
2013-05-13 16:38:24 11099 [Note] InnoDB: Writing table metadata to './blog/testpart#P#p2.cfg'
2013-05-13 16:38:24 11099 [Note] InnoDB: Table '"blog"."testpart" /* Partition "p2" */' flushed to disk
2013-05-13 16:38:24 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart" /* Partition "p3" */' started.
2013-05-13 16:38:24 11099 [Note] InnoDB: Writing table metadata to './blog/testpart#P#p3.cfg'
2013-05-13 16:38:24 11099 [Note] InnoDB: Table '"blog"."testpart" /* Partition "p3" */' flushed to disk
[mysql@mysql56node1 DB156 /db/DB1/data/blog ]$ ls -lrt
total 708
-rw-rw---- 1 mysql mysql 61 May 13 16:28 db.opt
-rw-rw---- 1 mysql mysql 32 May 13 16:28 testpart.par
-rw-rw---- 1 mysql mysql 8620 May 13 16:28 testpart.frm
-rw-rw---- 1 mysql mysql 245760 May 13 16:32 testpart#P#p3.ibd
-rw-rw---- 1 mysql mysql 147456 May 13 16:32 testpart#P#p2.ibd
-rw-rw---- 1 mysql mysql 131072 May 13 16:32 testpart#P#p1.ibd
-rw-rw---- 1 mysql mysql 147456 May 13 16:32 testpart#P#p0.ibd
-rw-rw---- 1 mysql mysql 456 May 13 16:38 testpart#P#p3.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:38 testpart#P#p2.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:38 testpart#P#p1.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:38 testpart#P#p0.cfg
[mysql@mysql56node1 DB156 /db/DB1/data/blog ]$ cp *.ibd ~/tmpfiles/
[mysql@mysql56node1 DB156 /db/DB1/data/blog ]$ cp *.cfg ~/tmpfiles/
[mysql@mysql56node1 DB156 /db/DB1/data/blog ]$ ls -lrt ~/tmpfiles/
total 9924
-rw-rw---- 1 mysql mysql 9437184 May 13 15:57 sb2.ibd
-rw-rw---- 1 mysql mysql 424 May 13 15:57 sb2.cfg
-rw-rw---- 1 mysql mysql 245760 May 13 16:39 testpart#P#p3.ibd
-rw-rw---- 1 mysql mysql 147456 May 13 16:39 testpart#P#p2.ibd
-rw-rw---- 1 mysql mysql 131072 May 13 16:39 testpart#P#p1.ibd
-rw-rw---- 1 mysql mysql 147456 May 13 16:39 testpart#P#p0.ibd
-rw-rw---- 1 mysql mysql 456 May 13 16:40 testpart#P#p3.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:40 testpart#P#p2.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:40 testpart#P#p1.cfg
-rw-rw---- 1 mysql mysql 456 May 13 16:40 testpart#P#p0.cfg
root [blog]> unlock tables;
Query OK, 0 rows affected (0.01 sec)
2013-05-13 18:17:32 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart#P#p0.cfg'
2013-05-13 18:17:32 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart#P#p1.cfg'
2013-05-13 18:17:32 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart#P#p2.cfg'
2013-05-13 18:17:32 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart#P#p3.cfg'
2013-05-13 18:17:32 11099 [Note] InnoDB: Resuming purge
root [blog]> use blogthecopy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root [blogthecopy]> alter table testpart discard tablespace;
ERROR 1031 (HY000): Table storage engine for 'testpart' doesn't have this option
So the parition storage engine type doesn't support transportable tablespaces.
So the workaround is to create a table for each partition. Then apply the above example. We have a database called 'blog'. A partitioned table called testpart.
Create a table per partition. I do apprecaited a table with alot of partitions is going to be more complex, but could be scripted.
root [blog]> CREATE TABLE `testpart_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
root [blog]> CREATE TABLE `testpart_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
root [blog]> CREATE TABLE `testpart_2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
root [blog]> CREATE TABLE `testpart_3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
Insert into each table from the partitioned table. This again could be time consuming if the partitioned table is large. This is suited to one of the 5.6 partitions features.
root [blog]> insert into testpart_0 select * from testpart partition (p0);
Query OK, 971 rows affected (0.02 sec)
Records: 971 Duplicates: 0 Warnings: 0
root [blog]> insert into testpart_1 select * from testpart partition (p1);
Query OK, 641 rows affected (0.02 sec)
Records: 641 Duplicates: 0 Warnings: 0
root [blog]> insert into testpart_3 select * from testpart partition (p3);
Query OK, 3836 rows affected (0.06 sec)
Records: 3836 Duplicates: 0 Warnings: 0
root [blog]> insert into testpart_2 select * from testpart partition (p2);
Query OK, 1000 rows affected (0.02 sec)
Records: 1000 Duplicates: 0 Warnings: 0
Transporting the separate tables to another database:
root [blog]> flush table testpart_0 for export;
Query OK, 0 rows affected (0.01 sec)
root [blog]> flush table testpart_1 for export;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
So the procedure is to export one table at a time:
root [blog]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
root [blog]> flush table testpart_1 for export;
Query OK, 0 rows affected (0.00 sec)
root [blog]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
root [blog]> flush table testpart_2 for export;
Query OK, 0 rows affected (0.00 sec)
root [blog]> unlock tables;
Query OK, 0 rows affected (0.01 sec)
root [blog]> flush table testpart_3 for export;
Query OK, 0 rows affected (0.00 sec)
root [blog]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
root [blog]> use blogthecopy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root [blogthecopy]> CREATE TABLE `testpart_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
root [blogthecopy]> CREATE TABLE `testpart_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
root [blogthecopy]> CREATE TABLE `testpart_2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
root [blogthecopy]> CREATE TABLE `testpart_3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
root [blogthecopy]> show tables;
+-----------------------+
| Tables_in_blogthecopy |
+-----------------------+
| testpart |
| testpart_0 |
| testpart_1 |
| testpart_2 |
| testpart_3 |
+-----------------------+
5 rows in set (0.00 sec)
root [blogthecopy]> alter table testpart_0 discard tablespace;
Query OK, 0 rows affected (0.02 sec)
root [blogthecopy]> alter table testpart_1 discard tablespace;
Query OK, 0 rows affected (0.00 sec)
root [blogthecopy]> alter table testpart_2 discard tablespace;
Query OK, 0 rows affected (0.01 sec)
root [blogthecopy]> alter table testpart_3 discard tablespace;
Query OK, 0 rows affected (0.00 sec)
2013-05-13 19:32:29 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart_0.cfg'
2013-05-13 19:32:29 11099 [Note] InnoDB: Resuming purge
2013-05-13 19:32:49 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart_1"' started.
2013-05-13 19:32:49 11099 [Note] InnoDB: Stopping purge
2013-05-13 19:32:49 11099 [Note] InnoDB: Writing table metadata to './blog/testpart_1.cfg'
2013-05-13 19:32:49 11099 [Note] InnoDB: Table '"blog"."testpart_1"' flushed to disk
2013-05-13 19:33:21 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart_1.cfg'
2013-05-13 19:33:21 11099 [Note] InnoDB: Resuming purge
2013-05-13 19:33:30 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart_2"' started.
2013-05-13 19:33:30 11099 [Note] InnoDB: Stopping purge
2013-05-13 19:33:30 11099 [Note] InnoDB: Writing table metadata to './blog/testpart_2.cfg'
2013-05-13 19:33:30 11099 [Note] InnoDB: Table '"blog"."testpart_2"' flushed to disk
2013-05-13 19:33:55 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart_2.cfg'
2013-05-13 19:33:55 11099 [Note] InnoDB: Resuming purge
2013-05-13 19:34:10 11099 [Note] InnoDB: Sync to disk of '"blog"."testpart_3"' started.
2013-05-13 19:34:10 11099 [Note] InnoDB: Stopping purge
2013-05-13 19:34:10 11099 [Note] InnoDB: Writing table metadata to './blog/testpart_3.cfg'
2013-05-13 19:34:10 11099 [Note] InnoDB: Table '"blog"."testpart_3"' flushed to disk
2013-05-13 19:34:44 11099 [Note] InnoDB: Deleting the meta-data file './blog/testpart_3.cfg'
2013-05-13 19:34:44 11099 [Note] InnoDB: Resuming purge
At the o/s:
[mysql@mysql56node1 DB156 /home/mysql/tmpfiles ]$ cp testpart_* /db/DB1/data/blogthecopy/
root [blogthecopy]> alter table testpart_0 import tablespace;
Query OK, 0 rows affected (0.87 sec)
root [blogthecopy]> alter table testpart_1 import tablespace;
Query OK, 0 rows affected (0.40 sec)
root [blogthecopy]> alter table testpart_2 import tablespace;
Query OK, 0 rows affected (0.87 sec)
root [blogthecopy]> alter table testpart_3 import tablespace;
Query OK, 0 rows affected (0.64 sec)
2013-05-13 19:48:35 11099 [Note] InnoDB: Importing tablespace for table 'blog/testpart_0' that was exported from host 'mysql56node1'
2013-05-13 19:48:35 11099 [Note] InnoDB: Phase I - Update all pages
2013-05-13 19:48:35 11099 [Note] InnoDB: Sync to disk
2013-05-13 19:48:35 11099 [Note] InnoDB: Sync to disk - done!
2013-05-13 19:48:35 11099 [Note] InnoDB: Phase III - Flush changes to disk
2013-05-13 19:48:36 11099 [Note] InnoDB: Phase IV - Flush complete
2013-05-13 19:48:36 11099 [Note] InnoDB: "blogthecopy"."testpart_0" autoinc value set to 972
2013-05-13 19:48:50 11099 [Note] InnoDB: Importing tablespace for table 'blog/testpart_1' that was exported from host 'mysql56node1'
2013-05-13 19:48:50 11099 [Note] InnoDB: Phase I - Update all pages
2013-05-13 19:48:50 11099 [Note] InnoDB: Sync to disk
2013-05-13 19:48:50 11099 [Note] InnoDB: Sync to disk - done!
2013-05-13 19:48:50 11099 [Note] InnoDB: Phase III - Flush changes to disk
2013-05-13 19:48:50 11099 [Note] InnoDB: Phase IV - Flush complete
2013-05-13 19:48:50 11099 [Note] InnoDB: "blogthecopy"."testpart_1" autoinc value set to 1822
2013-05-13 19:49:03 11099 [Note] InnoDB: Importing tablespace for table 'blog/testpart_2' that was exported from host 'mysql56node1'
2013-05-13 19:49:03 11099 [Note] InnoDB: Phase I - Update all pages
2013-05-13 19:49:03 11099 [Note] InnoDB: Sync to disk
2013-05-13 19:49:03 11099 [Note] InnoDB: Sync to disk - done!
2013-05-13 19:49:03 11099 [Note] InnoDB: Phase III - Flush changes to disk
2013-05-13 19:49:04 11099 [Note] InnoDB: Phase IV - Flush complete
2013-05-13 19:49:04 11099 [Note] InnoDB: "blogthecopy"."testpart_2" autoinc value set to 3000
2013-05-13 19:49:19 11099 [Note] InnoDB: Importing tablespace for table 'blog/testpart_3' that was exported from host 'mysql56node1'
2013-05-13 19:49:19 11099 [Note] InnoDB: Phase I - Update all pages
2013-05-13 19:49:19 11099 [Note] InnoDB: Sync to disk
2013-05-13 19:49:19 11099 [Note] InnoDB: Sync to disk - done!
2013-05-13 19:49:19 11099 [Note] InnoDB: Phase III - Flush changes to disk
2013-05-13 19:49:20 11099 [Note] InnoDB: Phase IV - Flush complete
2013-05-13 19:49:20 11099 [Note] InnoDB: "blogthecopy"."testpart_3" autoinc value set to 6836
Once the tables has been moved to the new database. The definiton of the original table can be used to create a copy of the table and the data can be inserted into it:
root [blogthecopy]> insert into testpart partition (p0) select * from testpart_0;
Query OK, 971 rows affected (0.02 sec)
Records: 971 Duplicates: 0 Warnings: 0
root [blogthecopy]> insert into testpart partition (p1) select * from testpart_1;
Query OK, 641 rows affected (0.01 sec)
Records: 641 Duplicates: 0 Warnings: 0
root [blogthecopy]> insert into testpart partition (p2) select * from testpart_2;
Query OK, 1000 rows affected (0.05 sec)
Records: 1000 Duplicates: 0 Warnings: 0
root [blogthecopy]> insert into testpart partition (p3) select * from testpart_3;
Query OK, 3836 rows affected (0.07 sec)
Records: 3836 Duplicates: 0 Warnings: 0
Testing the results:
root [blogthecopy]> select count(*) from blogthecopy.testpart;
+----------+
| count(*) |
+----------+
| 6448 |
+----------+
1 row in set (0.01 sec)
root [blogthecopy]> select count(*) from blog.testpart;
+----------+
| count(*) |
+----------+
| 6448 |
+----------+
1 row in set (0.00 sec)
No comments:
Post a Comment