Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "mysql tablespace transfer process". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A new feature of removable tablespaces (replicated tablespaces to another server), mysql tablespace transfer, was introduced in MySQL version 5.6. This makes it very convenient for us to transmit data from tables or individual libraries.

However, the transport tablespace must meet the following conditions

1. Independent tablespaces must be used

two。 The page size of both mysql databases must be the same

3. The table structure of both tables must be the same

Let's transfer the score table under the lala library.

First, export score from the main library

We can output the error log-f command before exporting and track what it does

[root@potato data] # tail-f error.log

Root@localhost:mysql.sock 01:31:46 [lala] > flush tables score for export

Root@localhost:mysql.sock 02:08:21 [lala] > select * from score; +-+

| | id | name | score | |

+-+

| | 3 | xiaohong | 99 | |

| | 2 | xiaoming | 65 | |

| | 1 | xiaojun | 55 | |

+-+

3 rows in set (0.00 sec)

The following is the output of the error log

2016-12-19 01:32:44 25547 [Note] InnoDB: Sync to disk of'"lala". "score" 'started.

2016-12-19 01:32:44 25547 [Note] InnoDB: Stopping purge

2016-12-19 01:32:44 25547 [Note] InnoDB: Writing table metadata to'. / lala/score.cfg'

2016-12-19 01:32:44 25547 [Note] InnoDB: Table'"lala". "score" 'flushed to disk

It can be seen that it first stops working on the table, and then writes the score data and metadata to disk.

You can access the table at this point, but you cannot DML the data, as shown in

The query statement was initiated successfully.

Root@localhost:mysql.sock 01:33:11 [(none)] > select * from lala.score; +-+

| | id | name | score | |

+-+

| | 3 | xiaohong | 99 | |

| | 2 | xiaoming | 65 | |

| | 1 | xiaojun | 55 | |

+-+

3 rows in set (0.00 sec)

Initiate an insert statement and wait all the time

Root@localhost:mysql.sock 01:31:23 [(none)] > insert into lala.score values.

Cfg files are added under the library folder

[root@potato lala] # ls-l score*

-rw-r-. 1 mysql mysql 470 Dec 19 02:07 score.cfg

-rw-rw----. 1 mysql mysql 8618 Dec 19 02:03 score.frm

-rw-r-. 1 mysql mysql 98304 Dec 19 02:07 score.ibd

Let's copy the cfg and ibd files to the / tmp directory first

[root@potato lala] # cp score.cfg / tmp

[root@potato lala] # cp score.ibd / tmp

Then unlock the table so that the outside can access the table as soon as possible

Root@localhost:mysql.sock 01:35:02 [lala] > unlock tables

Query OK, 0 rows affected (0.00 sec)

The standby library does not have this table at this time.

Root@localhost:mysql.sock 01:35:28 [lala] > show tables

+-+

| | Tables_in_lala |

+-+

| | |

| | test |

| | test1 |

+-+

3 rows in set (0.00 sec)

View the score table-building statement of the main library

Root@localhost:mysql.sock 01:37:19 [lala] > show create table score\ G

* * 1. Row *

Table: score

Create Table: CREATE TABLE `score` (

`id`int (11) DEFAULT NULL

`name` varchar (15) DEFAULT NULL

`score` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Execute on the slave library

Root@localhost:mysql.sock 02:03:44 [lala] > CREATE TABLE `score` (

-> `id` int (11) DEFAULT NULL

-> `name` varchar (15) DEFAULT NULL

-> `score` int (11) DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

Query OK, 0 rows affected (0.02 sec)

Now the score table has no data.

Root@localhost:mysql.sock 02:04:11 [lala] > select * from score

Empty set (0.00 sec)

Uninstall score tablespace

Root@localhost:mysql.sock 02:05:12 [lala] > alter table score discard tablespace

Query OK, 0 rows affected (0.02 sec)

[root@potato lala] # ls-l score*

-rw-rw----. 1 mysql mysql 8618 Dec 19 02:03 score.frm

Transfer two files to the standby library on the main library

[root@potato lala] # scp / tmp/score* 192.168.161.55:/data/mysql/mytest_3306/data/lala

Root@192.168.161.55's password:

Score.cfg 100% 470 0.5KB/s 00:00

Score.ibd 100% 96KB 96.0KB/s 00:00

Modify the owner of the file transferred in the past

[root@potato lala] # ls-l score*

-rw-r-. 1 root root 8618 Dec 19 01:37 score.cfg

-rw-rw----. 1 mysql mysql 8618 Dec 19 02:03 score.frm

-rw-r-. 1 root root 98304 Dec 19 01:37 score.ibd

[root@potato lala] # chown mysql:mysql score*

[root@potato lala] # ls-l score*

-rw-r-. 1 mysql mysql 470 Dec 19 02:07 score.cfg

-rw-rw----. 1 mysql mysql 8618 Dec 19 02:03 score.frm

-rw-r-. 1 mysql mysql 98304 Dec 19 02:07 score.ibd

Import score tablespaces

Root@localhost:mysql.sock 02:05:29 [lala] > alter table score import tablespace; Query OK, 0 rows affected (0.36 sec)

Root@localhost:mysql.sock 02:08:21 [lala] > select * from score; +-+

| | id | name | score | |

+-+

| | 3 | xiaohong | 99 | |

| | 2 | xiaoming | 65 | |

| | 1 | xiaojun | 55 | |

+-+

3 rows in set (0.00 sec)

At this point, the tablespace is transferred successfully

This is the end of the introduction of "mysql tablespace transfer process". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.