在本章中,我们将介绍以下配方:
在开始本章之前,您应该了解InnoDB
的基础知识。
根据 MySQL 文档,
系统表空间(共享表空间) “InnoDB 系统表空间包含 InnoDB 数据字典(用于 InnoDB 相关对象的元数据)和是双写缓冲区、更改缓冲区和撤消日志的存储区域。系统表空间还包含在系统表空间中创建的任何用户创建表的表和索引数据。系统表空间被视为共享表空间,因为它由多个表共享。
系统表空间由一个或多个数据文件表示。默认情况下,在 MySQL 数据目录中创建一个名为 ibdata1 的系统数据文件。系统数据文件的大小和数量由 innodb_data_file_path 启动选项控制。“
每个表空间的文件
每个表空间的文件是在其自己的数据文件中而不是在系统表空间中创建的单个表空间。启用innodb_file_per_table
选项后,将在每个表的文件表空间中创建表。否则,InnoDB
表将在系统表空间中创建。每个表空间的每个文件都由一个.ibd
数据文件表示,默认情况下,该文件在数据库目录中创建。
每个表的文件表空间支持DYNAMIC
和COMPRESSED
行格式,支持变长数据的页外存储和表压缩等功能。
要了解每表文件表空间的优点和缺点,请参阅https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-tablespaces.html 和https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_file_per_table 。
通用表空间
通用表空间是使用CREATE TABLESPACE
语法创建的共享InnoDB
表空间。通用表空间可以在 MySQLdata directory
之外创建,能够容纳多个表,支持所有行格式的表。
撤销表空间
撤消日志是与单个事务关联的撤消日志记录的集合。撤消日志记录包含有关如何撤消事务对聚集索引记录的最新更改的信息。如果另一个事务需要查看原始数据(作为一致读取操作的一部分),则从撤消日志记录中检索未修改的数据。撤消日志存在于撤消日志段中,撤消日志段包含在回滚段中。回滚段位于系统表空间、临时表空间和撤消表空间中。
UNDO
表空间包含一个或多个包含撤消日志的文件。InnoDB
使用的撤消表空间数量由innodb_undo_tablespaces
配置选项定义。
这些日志用于回滚事务,也用于多版本并发控制。
数据字典
data dictionary
是跟踪数据库对象(如表、索引和表列)的元数据。对于 MySQL 8.0 中引入的 MySQLdata dictionary
,元数据物理上位于 MySQL 数据库目录中每个表空间文件的InnoDB
文件中。对于InnoDB data dictionary
,元数据物理上位于InnoDB
系统表空间中。
MySQL 数据字典
MySQL 服务器包含一个事务性的data dictionary
,用于存储有关数据库对象的信息。在以前的 MySQL 版本中,字典数据存储在元数据文件、非事务表和特定于存储引擎的data dictionaries
中。
在以前的 MySQL 版本中,字典数据部分存储在元数据文件中。基于文件的元数据存储的问题包括昂贵的文件扫描、易受文件系统相关错误的影响、用于处理复制和崩溃恢复故障状态的复杂代码,以及缺乏扩展性,因此难以为新功能和关系对象添加元数据。
MySQLdata dictionary
的好处包括:
data dictionary
模式的简单性INFORMATION_SCHEMA
表的更简单和改进的实现下面列出的元数据文件将从 MySQL 中删除。除非另有说明,以前存储在元数据文件中的数据现在存储在data dictionary
表中:
.frm
文件:用于表定义的表元数据文件。.par
文件:分区定义文件。InnoDB
停止使用 MySQL 5.7 中的.definition
分区文件,引入了对InnoDB
表的本机分区支持。.trn
文件:触发名称空间文件。.trg
文件:触发参数文件。.isl
文件:InnoDB
符号链接文件,包含在 MySQLdata directory
之外创建的每个表空间文件的位置。db.opt
文件:数据库配置文件。这些文件(每个数据库目录一个)包含数据库默认字符集属性。MySQLdata dictionary
的局限性如下:
data directory
下手动创建数据库目录(例如,使用mkdir
)。MySQL 服务器无法识别手动创建的数据库目录。.frm
文件,DDL 操作需要更长的时间。字典数据的事务性存储
模式将字典数据存储在事务性(InnoDB
表中。data dictionary
表与non-data dictionary
系统表一起位于mysql
数据库中。
data dictionary
表是在 MySQLdata directory
中名为mysql.ibd
的单个InnoDB
表空间中创建的。mysql.ibd
表空间文件必须位于 MySQLdata directory
中,其名称不能被其他表空间修改或使用。以前,这些表是在 MySQL 数据库目录中的单个表空间文件中创建的。
ib_logfile0
文件和ib_logfile1
是在data directory
内部创建的默认InnoDB
重做日志文件,各 48 MB。如果您希望更改重做日志文件的大小,只需在配置文件中更改它并重新启动 MySQL 即可。在以前的版本中,您必须缓慢关闭 MySQL 服务器,删除重做日志文件,更改配置文件,然后启动 MySQL 服务器。
从 MySQL 8 开始,InnoDB
检测到innodb_log_file_size
与重做日志文件大小不同。它写入日志检查点,关闭并删除旧日志文件,以请求的大小创建新日志文件,并打开新日志文件。
shell> sudo ls -lhtr /var/lib/mysql/ib_logfile*
-rw-r-----. 1 mysql mysql 48M Oct 7 10:16 /var/lib/mysql/ib_logfile1
-rw-r-----. 1 mysql mysql 48M Oct 7 10:18 /var/lib/mysql/ib_logfile0
shell> sudo systemctl stop mysqld
shell> sudo vi /etc/my.cnf
[mysqld]
innodb_log_file_size=128M
innodb_log_files_in_group=4
shell> sudo systemctl start mysqld
shell> sudo less /var/log/mysqld.log
2017-10-07T11:09:35.111926Z 1 [Warning] InnoDB: Resizing redo log from 2*3072 to 4*8192 pages, LSN=249633608
2017-10-07T11:09:35.213717Z 1 [Warning] InnoDB: Starting to delete and rewrite log files.
2017-10-07T11:09:35.224724Z 1 [Note] InnoDB: Setting log file ./ib_logfile101 size to 128 MB
2017-10-07T11:09:35.225531Z 1 [Note] InnoDB: Progress in MB:
100
2017-10-07T11:09:38.924955Z 1 [Note] InnoDB: Setting log file ./ib_logfile1 size to 128 MB
2017-10-07T11:09:38.925173Z 1 [Note] InnoDB: Progress in MB:
100
2017-10-07T11:09:42.516065Z 1 [Note] InnoDB: Setting log file ./ib_logfile2 size to 128 MB
2017-10-07T11:09:42.516309Z 1 [Note] InnoDB: Progress in MB:
100
2017-10-07T11:09:46.098023Z 1 [Note] InnoDB: Setting log file ./ib_logfile3 size to 128 MB
2017-10-07T11:09:46.098246Z 1 [Note] InnoDB: Progress in MB:
100
2017-10-07T11:09:49.715400Z 1 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2017-10-07T11:09:49.715497Z 1 [Warning] InnoDB: New log files created, LSN=249633608
shell> sudo ls -lhtr /var/lib/mysql/ib_logfile*
-rw-r-----. 1 mysql mysql 128M Oct 7 11:09 /var/lib/mysql/ib_logfile1
-rw-r-----. 1 mysql mysql 128M Oct 7 11:09 /var/lib/mysql/ib_logfile2
-rw-r-----. 1 mysql mysql 128M Oct 7 11:09 /var/lib/mysql/ib_logfile3
-rw-r-----. 1 mysql mysql 128M Oct 7 11:09 /var/lib/mysql/ib_logfile0
data directory
中的ibdata1
文件是默认的系统表空间。您可以使用innodb_data_file_path
和innodb_data_home_dir
配置选项配置ibdata1
。innodb_data_file_path
配置选项用于配置InnoDB
系统表空间数据文件。innodb_data_file_path
的值应该是一个或多个数据文件规范的列表。如果命名两个或多个数据文件,请使用分号(;
字符)将其分隔。
如果您想要一个表空间,其中包含一个名为ibdata1
的固定大小的 50 MB 数据文件和一个名为data directory
的 50 MB 自动扩展文件,可以如下配置:
shell> sudo vi /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
如果ibdata
文件变得如此之大,特别是当innodb_file_per_table
未启用且磁盘已满时,您可能需要在另一个磁盘上添加另一个数据文件。
调整InnoDB
系统表空间的大小是一个您希望了解更多的主题。让我们进入它的细节。
假设innodb_data_file_path
为ibdata1:50M:autoextend
,大小已达到 76MB,而您的磁盘只有 100MB,您可以添加另一个磁盘,并配置为在新磁盘上添加另一个表空间:
shell> sudo systemctl stop mysql
ibdata1
文件的大小:shell> sudo ls -lhtr /var/lib/mysql/ibdata1
-rw-r----- 1 mysql mysql 76M Oct 6 13:33 /var/lib/mysql/ibdata1
/var/lib/mysql_extend
上,将所有权变更为mysql
;确保尚未创建该文件。如果使用 AppArmour 或 SELinux,请确保正确设置别名或上下文:shell> sudo chown mysql:mysql /var/lib/mysql_extend
shell> sudo chmod 750 /var/lib/mysql_extend
shell> sudo ls -lhtr /var/lib/mysql_extend
my.cnf
并添加以下内容:shell> sudo vi /etc/my.cnf [mysqld]
innodb_data_home_dir=
innodb_data_file_path = ibdata1:76M;/var/lib/mysql_extend/ibdata2:50M:autoextend
由于ibdata1
的现有大小为 76 MB,因此必须选择至少 76 MB 的最大值。下一个ibdata
文件将在/var/lib/mysql_extend/
上安装的新磁盘上创建。应指定innodb_data_home_dir
选项;否则,mysqld
查看另一条路径并失败,出现错误:
2017-10-07T06:30:00.658039Z 1 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-10-07T06:30:00.658084Z 1 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-10-07T06:30:00.658088Z 1 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-10-07T06:30:00.658092Z 1 [ERROR] InnoDB: File .//var/lib/mysql_extend/ibdata2: 'create' returned OS error 71\. Cannot continue operation
shell> sudo systemctl start mysql
shell> sudo ls -lhtr /var/lib/mysql_extend/
total 50M
-rw-r-----. 1 mysql mysql 50M Oct 7 07:38 ibdata2
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+----------------------------------------------------------+
| Variable_name | Value |
+-----------------------+----------------------------------------------------------+
| innodb_data_file_path | ibdata1:12M;/var/lib/mysql_extend/ibdata2:50M:autoextend |
+-----------------------+----------------------------------------------------------+
1 row in set (0.00 sec)
如果未使用innodb_file_per_table
,则所有表数据都存储在系统表空间中。如果删除表格,则不会回收空间。您可以缩小系统表空间并回收磁盘空间。这需要大量的停机时间,因此建议在从机上执行此任务,方法是使其停止旋转,然后将其升级为主机。
您可以通过查询INFORMATION_SCHEMA
表查看可用空间:
mysql> SELECT SUM(data_free)/1024/1024 FROM INFORMATION_SCHEMA.TABLES;
+--------------------------+
| sum(data_free)/1024/1024 |
+--------------------------+
| 6.00000000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.READ_ONLY=1;
;如果是从机,请停止复制并保存二进制日志坐标:mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G
mysqldump
或mydumper
进行完整备份,不包括sys
数据库:shell> mydumper -u root --password=<password> --trx-consistency-only --kill-long-queries --long-query-guard 500 --regex '^(?!sys)' --outputdir /backups
shell> sudo systemctl stop mysql
*.ibd
、*.ib_log
和ibdata
文件。如果您仅使用InnoDB
表,则可以擦除data directory
和存储系统表空间的所有位置(innodb_data_file_path
:shell> sudo rm -rf /var/lib/mysql/ib* /var/lib/mysql/<database directories>
shell> sudo rm -rf /var/lib/mysql_extend/*
data directory
:shell> sudo mysqld --initialize --datadir=/var/lib/mysql
shell> chown -R mysql:mysql /var/lib/mysql/
shell> chown -R mysql:mysql /var/lib/mysql_extend/
shell> sudo grep "temporary password is generated" /var/log/mysql/error.log | tail -1
2017-10-07T09:33:31.966223Z 4 [Note] A temporary password is generated for root@localhost: lI-qerr5agpa
shell> sudo systemctl start mysqld
shell> mysql -u root -plI-qerr5agpa
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx';
Query OK, 0 rows affected (0.01 sec)
shell> /opt/mydumper/myloader --directory=/backups/ --queries-per-transaction=50000 --threads=6 --user=root --password=xxxx --overwrite-tables
mysql> SET @@GLOBAL.READ_ONLY=0;
启用写入。如果是从机,则通过执行CHANGE MASTER TO COMMAND
和START SLAVE;
来恢复复制。在上一节中,您了解了如何在另一个磁盘中创建系统表空间。在本节中,您将学习如何在另一个磁盘中创建单个表空间。
您可以将具有特定性能或容量特征的新磁盘(如快速 SSD 或高容量 HDD)装入目录,并配置InnoDB
以使用该目录。在目标目录中,MySQL 创建一个与数据库名称对应的子目录,并在其中为新表创建一个.ibd
文件。记住,您不能将DATA DIRECTORY
子句与ALTER TABLE
语句一起使用:
shell> sudo chown -R mysql:mysql /var/lib/mysql_fast_storage
shell> sudo chmod 750 /var/lib/mysql_fast_storage
mysql> CREATE TABLE event_tracker (
event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
event_name varchar(10),
ts timestamp NOT NULL,
event_type varchar(10)
)
TABLESPACE = innodb_file_per_table
DATA DIRECTORY = '/var/lib/mysql_fast_storage';
.ibd
文件:shell> sudo ls -lhtr /var/lib/mysql_fast_storage/employees/
total 128K
-rw-r-----. 1 mysql mysql 128K Oct 7 13:48 event_tracker.ibd
复制表空间文件(.ibd
文件)是移动数据的最快方式,而不是通过mysqldump
或mydumper
导出和导入。数据立即可用,而无需重新插入和重建索引。您可能会将每个表空间的InnoDB
文件复制到不同实例的原因有很多:
概要是:您使用相同的表定义在目标上创建表,并在目标上执行DISCARD TABLESPACE
命令。在源代码上执行FLUSH TABLES FOR EXPORT
,确保对命名表的更改已刷新到磁盘上,因此可以在实例运行时进行二进制表复制。在该语句之后,表被锁定并且不接受任何写入;但是,读取也可能发生。您可以将该表的.ibd
文件复制到目的地,在源上执行UNLOCK
表,最后执行IMPORT TABLESPACE
命令,该命令接受复制的.ibd
文件。
例如,您希望将测试数据库中的events_history
表从一台服务器(源)复制到另一台服务器(目标)。
创建event_history
如果尚未创建,则为演示插入几行:
mysql> USE test;
mysql> CREATE TABLE IF NOT EXISTS `event_history`(
`event_id` int(11) NOT NULL,
`event_name` varchar(10) DEFAULT NULL,
`created_at` datetime NOT NULL,
`last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`event_type` varchar(10) NOT NULL,
`msg` tinytext NOT NULL,
PRIMARY KEY (`event_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (to_days(`created_at`))
(PARTITION 2017_oct_week1 VALUES LESS THAN (736974) ENGINE = InnoDB,
PARTITION p20171008 VALUES LESS THAN (736975) ENGINE = InnoDB,
PARTITION p20171009 VALUES LESS THAN (736976) ENGINE = InnoDB,
PARTITION p20171010 VALUES LESS THAN (736977) ENGINE = InnoDB,
PARTITION p20171011 VALUES LESS THAN (736978) ENGINE = InnoDB,
PARTITION p20171012 VALUES LESS THAN (736979) ENGINE = InnoDB,
PARTITION p20171013 VALUES LESS THAN (736980) ENGINE = InnoDB,
PARTITION p20171014 VALUES LESS THAN (736981) ENGINE = InnoDB,
PARTITION p20171015 VALUES LESS THAN (736982) ENGINE = InnoDB,
PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION p20171017 VALUES LESS THAN (736984) ENGINE = InnoDB);
mysql> INSERT INTO event_history VALUES
(1,'test','2017-10-07','2017-10-08','click','test_message'),
(2,'test','2017-10-08','2017-10-08','click','test_message'),
(3,'test','2017-10-09','2017-10-09','click','test_message'),
(4,'test','2017-10-10','2017-10-10','click','test_message'),
(5,'test','2017-10-11','2017-10-11','click','test_message'),
(6,'test','2017-10-12','2017-10-12','click','test_message'),
(7,'test','2017-10-13','2017-10-13','click','test_message'),
(8,'test','2017-10-14','2017-10-14','click','test_message');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> USE test;
mysql> CREATE TABLE IF NOT EXISTS `event_history`(
`event_id` int(11) NOT NULL,
`event_name` varchar(10) DEFAULT NULL,
`created_at` datetime NOT NULL,
`last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`event_type` varchar(10) NOT NULL,
`msg` tinytext NOT NULL,
PRIMARY KEY (`event_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (to_days(`created_at`))
(PARTITION 2017_oct_week1 VALUES LESS THAN (736974) ENGINE = InnoDB,
PARTITION p20171008 VALUES LESS THAN (736975) ENGINE = InnoDB,
PARTITION p20171009 VALUES LESS THAN (736976) ENGINE = InnoDB,
PARTITION p20171010 VALUES LESS THAN (736977) ENGINE = InnoDB,
PARTITION p20171011 VALUES LESS THAN (736978) ENGINE = InnoDB,
PARTITION p20171012 VALUES LESS THAN (736979) ENGINE = InnoDB,
PARTITION p20171013 VALUES LESS THAN (736980) ENGINE = InnoDB,
PARTITION p20171014 VALUES LESS THAN (736981) ENGINE = InnoDB,
PARTITION p20171015 VALUES LESS THAN (736982) ENGINE = InnoDB,
PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION p20171017 VALUES LESS THAN (736984) ENGINE = InnoDB);
mysql> ALTER TABLE event_history DISCARD TABLESPACE;
Query OK, 0 rows affected (0.05 sec)
FLUSH TABLES FOR EXPORT
:
mysql> FLUSH TABLES event_history FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
data directory
目录下的所有表相关文件(.ibd
、.cfg
复制到目的data directory
:
shell> sudo scp -i /home/mysql/.ssh/id_rsa /var/lib/mysql/test/event_history#P#* mysql@xx.xxx.xxx.xxx:/var/lib/mysql/test/
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql
:
shell> sudo ls -lhtr /var/lib/mysql/test
total 1.4M
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171017.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171016.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171015.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171014.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171013.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171012.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171011.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171010.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171009.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#p20171008.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:17 event_history#P#2017_oct_week1.ibd
.cfg
文件,则不会出现警告:
mysql> ALTER TABLE event_history IMPORT TABLESPACE;
Query OK, 0 rows affected, 12 warnings (0.31 sec)
mysql> SELECT * FROM event_history;
+----------+------------+---------------------+---------------------+------------+--------------+
| event_id | event_name | created_at | last_updated | event_type | msg |
+----------+------------+---------------------+---------------------+------------+--------------+
| 1 | test | 2017-10-07 00:00:00 | 2017-10-08 00:00:00 | click | test_message |
| 2 | test | 2017-10-08 00:00:00 | 2017-10-08 00:00:00 | click | test_message |
| 3 | test | 2017-10-09 00:00:00 | 2017-10-09 00:00:00 | click | test_message |
| 4 | test | 2017-10-10 00:00:00 | 2017-10-10 00:00:00 | click | test_message |
| 5 | test | 2017-10-11 00:00:00 | 2017-10-11 00:00:00 | click | test_message |
| 6 | test | 2017-10-12 00:00:00 | 2017-10-12 00:00:00 | click | test_message |
| 7 | test | 2017-10-13 00:00:00 | 2017-10-13 00:00:00 | click | test_message |
| 8 | test | 2017-10-14 00:00:00 | 2017-10-14 00:00:00 | click | test_message |
+----------+------------+---------------------+---------------------+------------+--------------+
8 rows in set (0.00 sec)
如果您在生产系统上执行此操作,为了最大限度地减少停机时间,您可以在本地复制到文件,这非常快。立即执行UNLOCK TABLES
,然后将文件复制到目的地。如果您无法承受停机时间,您可以使用 Percona XtraBackup,备份单个表,并应用重做日志,从而生成.ibd
文件。您可以将它们复制到目标并导入。
您在源上添加了events_history
表的新分区,并且只希望将新分区复制到目标。为了便于理解,在events_history
表上创建新分区并插入几行:
mysql> ALTER TABLE event_history ADD PARTITION
(PARTITION p20171018 VALUES LESS THAN (736985) ENGINE = InnoDB,
PARTITION p20171019 VALUES LESS THAN (736986) ENGINE = InnoDB);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO event_history VALUES
(9,'test','2017-10-17','2017-10-17','click','test_message'),(10,'test','2017-10-18','2017-10-18','click','test_message');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM event_history PARTITION (p20171018,p20171019);
+----------+------------+---------------------+---------------------+------------+--------------+
| event_id | event_name | created_at | last_updated | event_type | msg |
+----------+------------+---------------------+---------------------+------------+--------------+
| 9 | test | 2017-10-17 00:00:00 | 2017-10-17 00:00:00 | click | test_message |
| 10 | test | 2017-10-18 00:00:00 | 2017-10-18 00:00:00 | click | test_message |
+----------+------------+---------------------+---------------------+------------+--------------+
2 rows in set (0.00 sec)
假设要将新创建的分区复制到目标。
mysql> ALTER TABLE event_history ADD PARTITION
(PARTITION p20171018 VALUES LESS THAN (736985) ENGINE = InnoDB,
PARTITION p20171019 VALUES LESS THAN (736986) ENGINE = InnoDB);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE event_history DISCARD PARTITION p20171018, p20171019 TABLESPACE;
Query OK, 0 rows affected (0.06 sec)
FLUSH TABLE FOR EXPORT
:
**```sql mysql> FLUSH TABLES event_history FOR EXPORT; Query OK, 0 rows affected (0.01 sec)
4. 源上的**将分区的`.ibd`文件复制到目的地:**
**```sql
shell> sudo scp -i /home/mysql/.ssh/id_rsa \
/var/lib/mysql/test/event_history#P#p20171018.ibd \
/var/lib/mysql/test/event_history#P#p20171019.ibd \
mysql@35.198.210.229:/var/lib/mysql/test/
event_history#P#p20171018.ibd 100% 128KB 128.0KB/s 00:00 event_history#P#p20171019.ibd 100% 128KB 128.0KB/s 00:00
.ibd
文件,并且所有者为mysql
:shell> sudo ls -lhtr /var/lib/mysql/test/event_history#P#p20171018.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:54 /var/lib/mysql/test/event_history#P#p20171018.ibd
shell> sudo ls -lhtr /var/lib/mysql/test/event_history#P#p20171019.ibd
-rw-r----- 1 mysql mysql 128K Oct 7 17:54 /var/lib/mysql/test/event_history#P#p20171019.ibd
IMPORT PARTITION TABLESPACE
:
**```sql mysql> ALTER TABLE event_history IMPORT PARTITION p20171018, p20171019 TABLESPACE; Query OK, 0 rows affected, 2 warnings (0.10 sec)
只要表定义相同,就可以忽略警告。如果您也复制了`.cfg`文件,则不会出现警告:
```sql
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/event_history#P#p20171018.cfg', will attempt to import without schema verification |
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/event_history#P#p20171019.cfg', will attempt to import without schema verification |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM event_history PARTITION (p20171018,p20171019);
+----------+------------+---------------------+---------------------+------------+--------------+
| event_id | event_name | created_at | last_updated | event_type | msg |
+----------+------------+---------------------+---------------------+------------+--------------+
| 9 | test | 2017-10-17 00:00:00 | 2017-10-17 00:00:00 | click | test_message |
| 10 | test | 2017-10-18 00:00:00 | 2017-10-18 00:00:00 | click | test_message |
+----------+------------+---------------------+---------------------+------------+--------------+
2 rows in set (0.00 sec)
参见https://dev.mysql.com/doc/refman/8.0/en/tablespace-copying.html 了解该程序的更多限制。
您可以通过动态变量innodb_max_undo_log_size
管理UNDO
表空间的大小,默认为 1GB;通过innodb_undo_tablespaces
管理UNDO
表空间的数量,默认为 2GB,从 MySQL 8.0.2 开始动态。
默认情况下,innodb_undo_log_truncate
处于启用状态。超过innodb_max_undo_log_size
定义的阈值的表空间被标记为截断。只能截断撤消表空间。不支持截断驻留在系统表空间中的撤消日志。要进行截断,必须至少有两个撤消表空间。
验证UNDO
日志的大小:
shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 19M Oct 7 17:43 /var/lib/mysql/undo_002
-rw-r-----. 1 mysql mysql 16M Oct 7 17:43 /var/lib/mysql/undo_001
假设要减少超过 15 MB 的文件。请记住,只能截断一个撤消表空间。选择要截断的撤消表空间是以循环方式执行的,以避免每次截断相同的撤消表空间。释放撤消表空间中的所有回滚段后,将运行 truncate 操作,并将撤消表空间截断为其初始大小。撤消表空间文件的初始大小为 10 MB:
innodb_undo_log_truncate
已启用:mysql> SELECT @@GLOBAL.innodb_undo_log_truncate;
+-----------------------------------+
| @@GLOBAL.innodb_undo_log_truncate |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)
innodb_max_undo_log_size
设置为 15 MB:mysql> SELECT @@GLOBAL.innodb_max_undo_log_size;
+-----------------------------------+
| @@GLOBAL.innodb_max_undo_log_size |
+-----------------------------------+
| 1073741824 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.innodb_max_undo_log_size=15*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.innodb_max_undo_log_size;
+-----------------------------------+
| @@GLOBAL.innodb_max_undo_log_size |
+-----------------------------------+
| 15728640 |
+-----------------------------------+
1 row in set (0.00 sec)
innodb_purge_rseg_truncate_frequency
选项临时增加清除系统释放回滚段的频率:mysql> SELECT @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+-----------------------------------------------+
| @@GLOBAL.innodb_purge_rseg_truncate_frequency |
+-----------------------------------------------+
| 128 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.innodb_purge_rseg_truncate_frequency=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+-----------------------------------------------+
| @@GLOBAL.innodb_purge_rseg_truncate_frequency |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM employees;
Query OK, 300025 rows affected (16.23 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (2.38 sec)
UNDO
日志文件的增长:shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 19M Oct 7 17:43 /var/lib/mysql/undo_002
-rw-r-----. 1 mysql mysql 16M Oct 7 17:43 /var/lib/mysql/undo_001
shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 10M Oct 8 04:52 /var/lib/mysql/undo_001
-rw-r-----. 1 mysql mysql 27M Oct 8 04:52 /var/lib/mysql/undo_002
shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 10M Oct 8 04:52 /var/lib/mysql/undo_001
-rw-r-----. 1 mysql mysql 28M Oct 8 04:52 /var/lib/mysql/undo_002
shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 10M Oct 8 04:52 /var/lib/mysql/undo_001
-rw-r-----. 1 mysql mysql 29M Oct 8 04:52 /var/lib/mysql/undo_002
shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 10M Oct 8 04:52 /var/lib/mysql/undo_001
-rw-r-----. 1 mysql mysql 29M Oct 8 04:52 /var/lib/mysql/undo_002
您可能会注意到,undo_001
在undo_002
增长时被截断为 10MB,以适应DELETE
语句中被删除的行。
unod_002
也被截断为 10MB:shell> sudo ls -lhtr /var/lib/mysql/undo_00*
-rw-r-----. 1 mysql mysql 10M Oct 8 04:52 /var/lib/mysql/undo_001
-rw-r-----. 1 mysql mysql 10M Oct 8 04:54 /var/lib/mysql/undo_002
UNDO
表空间的缩减,请将innodb_purge_rseg_truncate_frequency
设置为默认值128
:mysql> SELECT @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+-----------------------------------------------+
| @@GLOBAL.innodb_purge_rseg_truncate_frequency |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.innodb_purge_rseg_truncate_frequency=128;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+-----------------------------------------------+
| @@GLOBAL.innodb_purge_rseg_truncate_frequency |
+-----------------------------------------------+
| 128 |
+-----------------------------------------------+
1 row in set (0.01 sec)
在 MySQL 8 之前,有两种类型的表空间:系统表空间和单个表空间。这两种类型都有优点和缺点。为了克服这些缺点,MySQL 8 中引入了通用表空间。与系统表空间类似,通用表空间是可以存储多个表的数据的共享表空间。但是您可以很好地控制一般表空间。与每个表空间中单独文件中相同数量的表相比,常规表空间中的多个表为表空间元数据消耗的内存更少。
限制如下:
.ibd
数据文件内部创建可用空间,该文件只能用于新的InnoDB
数据。空间不会像每个表的文件表空间那样释放回操作系统。在本节中,您将学习如何创建常规表空间,以及如何从中添加和删除表。
实际用法:
最初InnoDB
维护一个.frm
文件,包含表结构。MySQL 需要打开和关闭.frm
文件,这会降低性能。在 MySQL 8 中,.frm
文件被删除,所有元数据都使用事务data dictionary
进行处理。这样就可以使用通用表空间。
假设您对 SaaS 或多租户使用 MySQL 5.7 或更早版本,其中每个客户都有一个单独的模式,每个客户都有数百个表。如果您的客户增长,您将注意到性能问题。但是从 MySQL 8 中删除了.frm
文件后,性能大大提高。此外,您可以为每个模式(客户)创建单独的表空间。
首先,让我们开始创建它。
您可以在 MySQLdata directory
内部或外部创建通用表空间。
在 MySQLdata directory
中创建一个:
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
要在其外部创建表空间,请在/var/lib/mysql_general_ts
上装载新磁盘,并将所有权更改为mysql
:
shell> sudo chown mysql:mysql /var/lib/mysql_general_ts
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE '/var/lib/mysql_general_ts/ts2.ibd' Engine=InnoDB;Query OK, 0 rows affected (0.02 sec)
您可以在创建表空间时将表添加到表空间,也可以运行ALTER
命令将表从一个表空间移动到另一个表空间:
mysql> CREATE TABLE employees.table_gen_ts1 (id INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.01 sec)
假设要将employees
表移动到TABLESPACE ts2
:
mysql> USE employees;
Database changed
mysql> ALTER TABLE employees TABLESPACE ts2;
Query OK, 0 rows affected (3.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
您可以注意到ts2.ibd
文件的增加:
shell> sudo ls -lhtr /var/lib/mysql_general_ts/ts2.ibd
-rw-r-----. 1 mysql mysql 32M Oct 8 17:07 /var/lib/mysql_general_ts/ts2.ibd
您可以按如下方式移动表格:
employees
表从ts2
移动到ts1
:mysql> ALTER TABLE employees TABLESPACE ts1;
Query OK, 0 rows affected (3.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
shell> sudo ls -lhtr /var/lib/mysql/ts1.ibd
-rw-r-----. 1 mysql mysql 32M Oct 8 17:16 /var/lib/mysql/ts1.ibd
employees
表从ts1
移动到每个表的文件:mysql> ALTER TABLE employees TABLESPACE innodb_file_per_table;
Query OK, 0 rows affected (4.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
shell> sudo ls -lhtr /var/lib/mysql/employees/employees.ibd
-rw-r-----. 1 mysql mysql 32M Oct 8 17:18 /var/lib/mysql/employees/employees.ibd
employees
表从每个表的文件移动到系统表空间:mysql> ALTER TABLE employees TABLESPACE innodb_system;
Query OK, 0 rows affected (5.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
您可以在多个表空间中创建具有分区的表:
mysql> CREATE TABLE table_gen_part_ts1 (id INT, value varchar(100)) ENGINE = InnoDB
PARTITION BY RANGE(id) (
PARTITION p1 VALUES LESS THAN (1000000) TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (2000000) TABLESPACE ts2,
PARTITION p3 VALUES LESS THAN (3000000) TABLESPACE innodb_file_per_table,
PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE innodb_system);
Query OK, 0 rows affected (0.19 sec)
您可以在另一个表空间中添加一个新分区,或者,若您并没有提及任何内容,它将在表的默认表空间中创建。分区表上的ALTER TABLE tbl_name TABLESPACE tablespace_name
操作只修改表的默认表空间。它不会移动表分区。但是,在更改默认表空间后,如果没有使用TABLESPACE
子句明确定义另一个表空间,则重建表的操作(例如使用ALGORITHM=COPY
的ALTER TABLE
操作)会将分区移动到默认表空间。
如果希望跨表空间移动分区,则需要在分区上执行REORGANIZE
。例如,您想将分区p3
移动到ts2
:
mysql> ALTER TABLE table_gen_part_ts1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (3000000) TABLESPACE ts2);
您可以使用DROP TABLESPACE
命令删除表空间。但是,应删除或移动该表空间中的所有表:
mysql> DROP TABLESPACE ts2;
ERROR 3120 (HY000): Tablespace `ts2` is not empty.
在删除之前,必须将ts2
表空间中的表table_gen_part_ts1
的分区p2
和p3
移动到其他表空间:
mysql> ALTER TABLE table_gen_part_ts1 REORGANIZE PARTITION p2 INTO (PARTITION p2 VALUES LESS THAN (3000000) TABLESPACE ts1);
mysql> ALTER TABLE table_gen_part_ts1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN (3000000) TABLESPACE ts1);
现在可以删除表空间:
mysql> DROP TABLESPACE ts2;
Query OK, 0 rows affected (0.01 sec)
您可以创建以压缩形式存储数据的表。压缩有助于提高原始性能和可伸缩性。压缩意味着更少的数据在磁盘和内存之间传输,它占用更少的磁盘和内存空间。
根据 MySQL 文档:
“由于处理器和高速缓存的速度比磁盘存储设备提高得多,因此许多工作负载都是磁盘限制的。数据压缩以提高 CPU 利用率的小成本实现了更小的数据库大小、更少的 I/O 和更高的吞吐量。压缩对于读密集型应用程序尤其有价值,在具有足够 RAM 的系统上,可以将频繁使用的数据保存在内存中。对于具有二级索引的表来说,好处更大了,因为索引数据也被压缩了。”
要启用压缩,您需要使用ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE
选项创建或更改表。您可以更改KEY_BLOCK_SIZE
参数,该参数在磁盘上使用的页面大小小于配置的innodb_page_size
值。如果表在系统表空间中,压缩将不起作用。
要在常规表空间中创建压缩表,必须为常规表空间定义FILE_BLOCK_SIZE
,这是在创建表空间时指定的。FILE_BLOCK_SIZE
值必须是相对于innodb_page_size
值的有效压缩页面大小,并且由CREATE TABLE
或ALTER TABLE KEY_BLOCK_SIZE
子句定义的压缩表的页面大小必须等于FILE_BLOCK_SIZE/1024
。
在缓冲池中,压缩数据保存在小页面中,页面大小基于KEY_BLOCK_SIZE
值。为了提取或更新列值,MySQL 还使用未压缩的数据在缓冲池中创建未压缩的页面。在缓冲池中,对未压缩页面的任何更新也会重写回等效的压缩页面。您可能需要调整缓冲池的大小,以容纳压缩页和未压缩页的附加数据,尽管未压缩页在需要空间时会从缓冲池中移出,然后在下次访问时再次解压缩。
何时使用压缩?
通常,压缩在包含合理数量的字符串列的表上效果最好,并且数据的读取频率远远高于写入频率。因为没有可靠的方法可以预测压缩是否有利于特定情况,所以始终使用在代表性配置上运行的特定工作负载和数据集进行测试。
您需要选择参数KEY_BLOCK_SIZE
。innodb_page_size
为 16000;理想情况下,其中一半是 8000,这是一个良好的开端。要调整压缩,请参阅https://dev.mysql.com/doc/refman/8.0/en/innodb-compression-tuning.html 。
file_per_table
已启用:mysql> SET GLOBAL innodb_file_per_table=1;
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
:mysql> CREATE TABLE compressed_table (id INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.07 sec)
如果该表已经存在,可以执行ALTER
:
mysql> ALTER TABLE event_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
如果试图压缩系统表空间中的表,将出现错误:
mysql> ALTER TABLE employees ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ERROR 1478 (HY000): InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
要禁用压缩,请执行ALTER
表并指定ROW_FORMAT=DYNAMIC
或ROW_FORMAT=COMPACT
,后跟KEY_BLOCK_SIZE=0
。
例如,如果您不希望对event_history
表进行压缩:
mysql> ALTER TABLE event_history ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
首先,您需要通过提及FILE_BLOCK_SIZE
来创建一个压缩表空间;不能更改表空间的FILE_BLOCK_SIZE
。
如果希望创建压缩表,则需要在启用压缩的常规表空间中创建表;此外,KEY_BLOCK_SIZE
必须等于FILE_BLOCK_SIZE/1024
。如果您未提及KEY_BLOCK_SIZE
,则该值将自动取自FILE_BLOCK_SIZE
。
您可以使用不同的FILE_BLOCK_SIZE
值创建多个压缩的常规表空间,只需将表添加到所需的表空间:
FILE_BLOCK_SIZE
为 8k,另一个FILE_BLOCK_SIZE
为 4k,并移动所有KEY_BLOCK_SIZE
为 8 到 8k 和 4 到 4k 的表格:mysql> CREATE TABLESPACE `ts_8k` ADD DATAFILE 'ts_8k.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLESPACE `ts_4k` ADD DATAFILE 'ts_4k.ibd' FILE_BLOCK_SIZE = 4096 Engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
ROW_FORMAT=COMPRESSED
在这些表空间中创建压缩表:mysql> CREATE TABLE compress_table_1_8k (id INT PRIMARY KEY) TABLESPACE ts_8k ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.01 sec)
如果您不提及ROW_FORMAT=COMPRESSED,
您将得到一个错误:
mysql> CREATE TABLE compress_table_2_8k (id INT PRIMARY KEY) TABLESPACE ts_8k;
ERROR 1478 (HY000): InnoDB: Tablespace `ts_8k` uses block size 8192 and cannot contain a table with physical page size 16384
或者,您可以提及KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024
:
mysql> CREATE TABLE compress_table_8k (id INT PRIMARY KEY) TABLESPACE ts_8k ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)
如果您提到除FILE_BLOCK_SIZE/1024
之外的任何内容,您将得到一个错误:
mysql> CREATE TABLE compress_table_2_8k (id INT PRIMARY KEY) TABLESPACE ts_8k ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ERROR 1478 (HY000): InnoDB: Tablespace `ts_8k` uses block size 8192 and cannot contain a table with physical page size 4096
KEY_BLOCK_SIZE
匹配的情况下,才能将表从file_per_table
表空间移动到压缩的常规表空间。否则,您将得到一个错误:
mysql> CREATE TABLE compress_tables_4k (id INT PRIMARY KEY) TABLESPACE innodb_file_per_table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE compress_tables_4k TABLESPACE ts_4k;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE compress_tables_4k TABLESPACE ts_8k;
ERROR 1478 (HY000): InnoDB: Tablespace `ts_8k` uses block size 8192 and cannot contain a table with physical page size 4096
```******