MYSQL tablespace migration

MYSQL tablespace migration. Table space migration. You may need to copy the InnoDB table to different database servers for the following reasons. Generate a report without increasing the production load and create a table with the same data as the production data on a new server for backup. In case of problems or errors, the report is used to restore data quickly. migrate from a server

MYSQL tablespace migration. Table space migration. You may need to copy the InnoDB table to different database servers for the following reasons. Generate a report without increasing the production load and create a table with the same data as the production data on a new server for backup. In case of problems or errors, the report is used to restore data quickly. migrate from a server

MYSQL tablespace migration.

Table space migration.

You may needInnoDB tables are copied to different database servers.

• Generate a report without increasing the production load
• Create a table with the same data as the production data on a new server
• Make a backup for recovery in case of a problem or error
• Quickly migrate data from one server to another

CommandFlush tables... FOREXPORTEnable. IbdFile status. Only when the file is in the consistent state can we copy it. This file also creates an extension.. Cfg. CommandAlter table... IMPORT TABLESPACEThe binary file is used to verify the import process.

For MySQL 5.6.8,Alter table... IMPORT TABLESPACEThe command no longer requires a. cfg binary file extension. However, if this file does not exist, we will receive the following warning.

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\

Test \ t. cfg ', will attempt to import without schema verification

1row in set (0.00 sec)

This feature is sometimes useful. For example, metadata cannot be retrieved from the import process that does not match the schema, or in some scenarios that need to be restored. if the ibd file is obtained, this command does not require. cfg binary files can be imported.

Restrictions on table space to be migrated:

• Innodb_file_per_tableMake sure to openON. This feature is not available for tables in the shared tablespace.
• When the table is in silent State, only read-only statements can use this table.
• When importing tablespaces, the page size of the target database must match the page size of the source database.
• DISCARD TABLESPACEPartition tables are not supported. If you use the command on a partition tableAlter table... DISCARD TABLESPACEYou will see the following ERROR: ERROR 1031 (HY000): The table engine does not have this option.
• DISCARD TABLESPACEThe command does not support tables with parent-child relationships. IfForeign_key_checksSet1Before using the command, we can set this parameter to 0.Foreign_key_checks = 0.
• Alter table... IMPORT TABLESPACEThe command does not check the relationship between the primary and Foreign keys when importing the table.
• For real-time replication,Innodb_file_per_tableMust be setON.

Here is an example:

On the source server, we can migrate the city table:

1. mysql> use test; C: \ ProgramData \ MySQL \ mysql server 5.6 \ data \ world

2. C: \ ProgramData \ MySQL server 5.6 \ data \ world> dir

3. Volume in drive C has no label.

4. Volume Serial Number is D0FA-F7A0

5. Directory of C: \ ProgramData \ MySQL Server5.6 \ data \ world

6. 10/08/2013 PM

.

7. 10/08/2013 PM

..

8. 10/08/2013 PM 8,710 city. frm

9. 10/08/2013 0:15 PM 273,293 city. MYD

10.10/08/2013 PM 43,008 city. MYI

11.10/08/2013 PM 9,172 country. frm

12.10/08/2013 PM 0 country. MYD

13.10/08/2013 PM 5,120 country. MYI

14.10/08/2013 PM 8,702 countrylanguage. frm

15.10/08/2013 PM 38,376 countrylanguage. MYD

16.10/08/2013 PM 18,432 countrylanguage. MYI

17.10/08/2013 PM 61 db. opt

18. 10 File (s) 404,874 bytes

19. 2 Dir (s) 224,709,537,792 bytes free

20. mysql> use world

21. Database changed

22. mysql> show tables;

23. + ----------------- +

24. | Tables_in_world |

25. + ----------------- +

26. | city |

27. | country |

28. | countrylanguage |

29. + ----------------- +

30.3 rows in set (0.00 sec)

31. mysql> flush table cityfor export;

32. ERROR 1031 (HY000): Table storage engine for 'city' doesn' t havethis option

33. mysql> alter table cityengine = innodb;

34. mysql> flush table cityfor export; -- lock the table.

35. Query OK, 0 rows affected (0.18 sec)

36.

Copy the table file to the target location

1. C: \ ProgramData \ MySQL Server 5.6 \ data \ world> mkdir city
2. C: \ ProgramData \ MySQL Server 5.6 \ data \ world> copy city. * city
3. City. cfg
4. City. frm
5. City. ibd
6. 3 file (s) copied.
7. C: \ ProgramData \ MySQL Server 5.6 \ data \ world> cd city
8. C: \ ProgramData \ MySQL Server 5.6 \ data \ world \ city> dir
9. Volume in drive C has no label.
10. Volume Serial Number is D0FA-F7A0
11. Directory of C: \ ProgramData \ MySQL Server 5.6 \ data \ world \ city
12. 10/10/2013 10: 58 AM .
13. 10/10/2013 10: 58 AM ..
14. 10/10/2013 AM 582 city. cfg
15. 10/10/2013 AM 8,710 city. frm
16. 10/10/2013 AM 475,136 city. ibd
17. 3 File (s) 484,428 bytes
18. 2 Dir (s) 224,676,024,320 bytes free

Delete tablespaces of the same name on the target database.

1. Mysql> unlock tables; -- release the lock.

2. Query OK, 0 rowsaffected (0.07 sec)

3. mysql> alter table city discard tablespace; Delete possible tablespaces with the same name

4. Query OK, 0 rowsaffected (0.23 sec)

5. mysql> selectcount (*) from city;

6. ERROR 1814 (HY000): Tablespace has been discarded for table 'city'

7. mysql> alter tablecity import tablespace;

8. ERROR 1146 (42S02): Table 'World. City' doesn' t exist

9. C: \ ProgramData \ MySQL \ MySQLServer 5.6 \ data \ world \ city> copy city .*..

10. city. cfg

11. city. frm

12. Overwrite... \ city. frm? (Yes/No/All): yes

13. Access is denied.

14. city. ibd

15. 2 file (s) copied.

16. C: \ ProgramData \ MySQL Server 5.6 \ data \ world \ city>

17. mysql> alter table city import tablespace;

18. Query OK, 0 rows affected (0.94 sec)

19. mysql> select count (*) from city;

20. + ---------- +

21. | count (*) |

22. + ---------- +

23. | 4079 |

24. + ---------- +

25.1 row in set (0.08 sec)

The tablespace is successfully created.