Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the case study of MySQL 5.7. the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

1. Background

* A common table space is a shared InnoDB table space.

* similar to system tablespaces, general tablespaces are shared tablespaces that can store data from multiple tables

* General tablespaces have a potential memory advantage over file tablespaces.

* MySQL saves tablespace metadata to the life cycle of a tablespace. In fewer general tablespaces, multiple tables have less memory for tablespace metadata than the same number of tables in a separate file tablespace.

* A general tablespace data file may be placed in a directory relative to the MySQL data directory, which provides you with many data file and storage management functions for file tablespaces. Like the tablespaces of file tables, the ability to place data files outside the MySQL data directory allows you to manage the performance of key tables individually, set RAID or DRBD for specific tables, or bind tables to specific disks.

* MySQL 5.7began to support common tablespace management functions.

2. MySQL environment

Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show variables like 'version';+-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18 | +-+-+ 1 row in set (0.01 sec) mysql > show variables like' datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data/ | +-- -+ 1 row in set (0.04 sec)

3. Create a common tablespace

* create a table space file storage directory

[root@MySQL mytest] # mkdir-v / mysql_general_datamkdir: created directory `/ mysql_general_data'

* View mysqld running users

[root@MySQL mytest] # ps aux | grep mysqld | grep-v greproot 1468 0.0 110400 1532? S 16:00 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql_data-- pid-file=/data/mysql_data/MySQL.pidmysql 1614 0.1 5.0 1309380 196656? Sl 16:00 0:06 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql_data-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/data/mysql_data/error.log-pid-file=/data/mysql_data/MySQL.pid

* modify the user and group to which the table space file storage directory belongs to the mysql running user [this step is required]

[root@MySQL mytest] # chown-v mysql.mysql / mysql_general_data changed ownership of `/ mysql_general_data' to mysql:mysql

* create a common tablespace

ADD datafile: specify the storage path for common tablespace files

FILE_BLOCK_SIZE: specify the file block size, which is recommended to correspond to the Innodb_page_size parameter size

ENGINE: specifies the storage engine

Mysql > CREATE TABLESPACE ts1 ADD datafile'/ mysql_general_data/ts1.ibd' FILE_BLOCK_SIZE=16384 ENGINE=InnoDB;Query OK, 0 rows affected (0.06 sec) mysql > show variables like 'innodb_page_size' +-+-+ | Variable_name | Value | +-+-+ | innodb_page_size | 16384 | +-+-+ 1 row in set (0.02 sec)

* View common tablespace files

Mysql > system ls-l / mysql_general_data;total 64While RWMI r-1 mysql mysql 65536 Jul 5 17:15 ts1.ibd

4. Test common tablespace files

* create tables using common tablespaces as data stores

Mysql > CREATE TABLE test_general (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> name VARCHAR (64) NOT NULL->) ENGINE=InnoDB TABLESPACE=ts1 DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.04 sec)

* View table information

Mysql > show create table test_general +- -+ | Table | Create Table | | +- - -- + | test_general | CREATE TABLE `test_ general` (`id` bigint (20) NOT NULL AUTO_INCREMENT `name` varchar (64) NOT NULL PRIMARY KEY (`id`) / *! 50100 TABLESPACE `ts1` * / ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-+- -+ 1 row in set (0.06 sec)

* View table files

Mysql > select database (); +-+ | database () | +-+ | mytest | +-+ 1 row in set (0.01mm sec) mysql > system ls-l / data/mysql_data/mytest;total 16When Rwmuri r-1 mysql mysql 67 Jul 5 16:30 db.opt-rw-r- 1 mysql mysql 8586 Jul 5 17:19 test_general.frm

5. Delete tablespace files

* delete directly when the table is occupied

Mysql > drop tablespace ts1;ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

* Delete the occupancy table before deleting it

Mysql > drop table test_general;Query OK, 0 rows affected (0.04 sec) mysql > drop tablespace ts1;Query OK, 0 rows affected (0.03 sec)

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

For the above case study of MySQL 5.7Universal tablespace, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales ones on the official website. I hope this article can bring you some knowledge updates.

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.