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

This article introduces the knowledge of "expansion, contraction and migration of mysql shared tablespaces". Many people will encounter this dilemma in the operation of actual cases, 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!

one。 Expand innodb file

1. Close mysql db

# / usr/local/mysql/bin/mysqladmin-S / tmp/mysql3307.sock shutdown

two。 Open the parameter file

Innodb_data_file_path = ibdata1:512M:autoextend

Adjust the ibdata1 to close to the actual size, and append the new file to it:

Innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend

3. Start the database

# / usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my3307.cnf-user=mysql &

two。 Shrink the tablespace

Shared tablespace files. Once the extension cannot automatically shrink, it needs to be reduced manually.

1. Export all databases

# / usr/local/mysql/bin/mysqldump-uroot-p-A-S / tmp/mysql3307.sock > / tmp/3307all.sql

Enter password:

two。 Close mysql db

# / usr/local/mysql/bin/mysqladmin-S / tmp/mysql3307.sock shutdown

3. Delete the data directory of mysql

Rm-rf / home/mysql3307/mysql3307/*

4. Initialize mysql

/ usr/local/mysql/bin/mysqld-defaults-file=/etc/my3307.cnf-initialize-insecure-basedir=/usr/local/mysql-datadir=/home/mysql3307/mysql3307-user=mysql

5. Start mysql

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my3307.cnf-user=mysql &

6. Import data

Source / tmp/3307all.sql

III. Innodb tablespace migration

Original database table structure:

Click (here) to collapse or open

CREATE TABLE `www.j`.`t1` (

`id` INT NOT NULL

`name` VARCHAR (45) NULL

PRIMARY KEY (`id`)

Alter table wwj.t1 add index idx_name (name)

Insert into wwj.t1 values (1)

1. Create the same table on the target instance

Click (here) to collapse or open

CREATE TABLE `www.j2`.`t1` (

`id` INT NOT NULL

`name` VARCHAR (45) NULL

PRIMARY KEY (`id`)

two。 Execute ALTER TABLE t DISCARD TABLESPACE on the target library

ALTER TABLE t1 DISCARD TABLESPACE

-discard means detached from the database, which deletes the ibd file and retains the frm file.

-that means you can operate on frm files, such as rename table,drop table, but not on ibd files, such as dml

3. Execute FLUSH TABLES t FOR EXPORT; to generate .cfg file on the source library

Flush tables t1 for export

-rw-r-. 1 mysql mysql 67 Mar 24 06:59 db.opt

-rw-r-. 1 mysql mysql 467 Mar 24 18:32 t1.cfg

-rw-r-. 1 mysql mysql 8586 Mar 24 06:59 t1.frm

-rw-r-. 1 mysql mysql 114688 Mar 24 06:59 t1.ibd

At this point, the .cfg file is in the data directory of InnoDB

Flush tables.. For export will be locked, at this time, do not exit the terminal or session, otherwise the lock is invalid and the .cfg file is automatically deleted.

4. Copy .ibd files and .cfg files to the target instance

[root@mysql5 wwj] # cp t1.cfg / home/mysql3306/mysql3306/wwj2

[root@mysql5 wwj] # cp t1.ibd / home/mysql3306/mysql3306/wwj2

Modify permissions

5. Execute unlock tables in the source database

Mysql > UNLOCK TABLES

Query OK, 0 rows affected (0.00 sec)

6. Execute ALTER TABLE t IMPORT TABLESPACE in the target library

First execution: successful after appending index to the target library

Mysql > alter table T1 import tablespace

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 3

Current database: wwj2

ERROR 1808 (HY000): Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes)

Considerations for innodb transportable tablespaces

-

Innodb_file_per_table must be enabled

When the table is in quiesced state, it cannot even be select

The page size of both instances is the same.

Prior to version 5.7, the partition table transport is not supported

For tables related to foreign keys, foreign_key_checks=0 must be set to succeed

ALTER TABLE... IMPORT TABLESPACE does not need .cfg metadata file. However, in this case, MySQL will not verificate schema

Version 5.6 and later, the import&export version must be in the same series

In a replication environment, both master and slave must enable innodb_file_per_table

For InnoDB general tablespace, discard & import tablespace is not supported

If the table row_format settings of the servers on both sides are different, it will cause schema mismatch error.

Encrypted InnoDB tablespace must copy .cfp files

IV. MyISAM tablespace migration

1. Flush table with read lock

two。 Copy data files and table structure files directly

This is the end of "expanding, shrinking and migrating mysql shared tablespaces". 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.