Example analysis of table space transfer in mysql

Description: MySQL (5.6.6 and above), innodb_file_per_table is turned on.

1.1. Operation steps:

0. The target server creates the same table structure
1. Destination server: ALTER TABLE t DISCARD TABLESPACE;
2. Source server: FLUSH TABLES t FOR EXPORT;
3. Copy t.ibd, t.cfg files from the source server to the destination server
4. Source server: UNLOCK TABLES;
5. Destination server: ALTER TABLE t IMPORT TABLESPACE;

1.2. Demonstration
Transfer the test_purge table under the burn_test library in [mysql5711] in multiple instances to the test_purge table under the burn_test2 library in [mysql57112]

1.2.1. Preparation

1. Create a table space on the target server

-- Source server[mysql5711]

mysql> select * from burn_test.test_purge;
---- ------
| a | b |
---- ------
| 1 | 10 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 10 | 100 |
---- -- ----
8 rows in set (0.01 sec)

-- Target server [mysql57112]
--
-- test_purge does not exist on the target server, create the table first
mysql> CREATE TABLE `test_purge` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4；
Query OK, 0 rows affected (0.16 sec)

2. Creation completed Check afterwards

## Target server

#[root@MyServer burn_test_2]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm # Table structure
-rw-r-----. 1 mysql mysql 57344 Mar 21 10:31 test_purge.ibd # Table space, table space files need to be deleted through DISCARD
ALTER TABLE test_purge DISCARD TABLESPACE; means to keep the test_purge.frm file and delete test_purge.ibd

3. Use discard to delete the ibd file

--Target server

mysql> ; alter table test_purge discard tablespace;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
------------------- ----
| Tables_in_burn_test_2 |
-----------------------
| test_backup1 |
| test_purge |
-----------------------
2 rows in set (0.00 sec)
mysql> select * from test_purge;
ERROR 1814 (HY000): Tablespace has been discarded for table 'test_purge'
[root@MyServer burn_test_2]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm

1.2.2. Export table space
1. On the source server, use the export command to export the table space (add a read lock at the same time)

--Source server

mysql> flush table test_purge for export; -- Actually add a read lock to this table
Query OK, 0 rows affected (0.00 sec)
2. Export the cfg file and ibd file , copy to the database of the target server

## source server

#[root@MyServer burn_test]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 462 Mar 21 10:58 test_purge.cfg # After export, the extra file contains some metadata information
-rw-r-----. 1 mysql mysql 8578 Mar 4 15:41 test_purge.frm
-rw-r-----. 1 mysql mysql 57344 Mar 5 15:28 test_purge.ibd
[root@MyServer burn_test]> cp test_purge.cfg test_purge.ibd /data/mysql_data /5.7.11_2/burn_test_2/ # Copy the table space and cfg file. Please use scp remotely (local multi-instance demonstration, the library name here is different)
3. After exporting the table space, unlock it as soon as possible

-- Source server

mysql> unlock tables; -- Unlock as soon as possible
Query OK, 0 rows affected (0.00 sec)
Note: Be sure to copy the cfg and ibd files first, and then unlock, because when unlocking, the cfg file will be deleted
# Logs on the source server
[Note] InnoDB: Stopping purge #In fact, to stop purge, just find a test table for export
[Note ] InnoDB: Writing table metadata to './burn_test/test_purge.cfg'
[Note] InnoDB: Table `burn_test`.`test_purge` flushed to disk
[Note] InnoDB: Deleting the meta-data file ' ./burn_test/test_purge.cfg' # After unlocking the table, the file is automatically deleted
[Note] InnoDB: Resuming purge # After unlocking, restore the purge thread
4. Modify the cfg file and ibd file on the target server Permissions

## Target server

#[root@MyServer burn_test_2]> chown mysql.mysql test_purge.cfg test_purge.ibd
5. Run the import command on the target server Import tablespace
-- Target server
--
mysql> alter table test_purge import tablespace; -- Import tablespace
Query OK, 0 rows affected (0.24 sec)
mysql> select * from test_purge; -- You can read the data copied from the source server
---- ------
| a | b |
---- ----- -
| 1 | 10 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 10 | 100 |
---- ------
8 rows in set (0.00 sec)

# error.log The message that appears
InnoDB: Importing tablespace for table 'burn_test/test_purge' that was exported from host 'MyServer'

Note:
The names of the tables must be the same. After the above test, the library names can be different
This method can also be used for the backup and recovery of partition tables

The above is the detailed content of Example analysis of table space transfer in mysql. For more information, please follow other related articles on the PHP Chinese website!