+-------------------------+
|    連接/會(huì)話層          |
+-------------------------+
|    SQL解析/優(yōu)化層       |
+-------------------------+
|    存儲引擎接口層       |
+-------------------------+
| InnoDB | MyISAM | Memory | ... |
+-------------------------+

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

show create table 表名;

# 修改配置文件，然后重啟mysql即可
vim /etc/my.cnf
# 修改下面的配置 
[mysqld]
default_storage_engine=InnoDB

#創(chuàng)建表時(shí)設(shè)置
create table xxx (id int) engine=innodb charset=utf8mb4;
#創(chuàng)建表之后設(shè)置
alter table xxx engine=myisam;
alter table world.xxx engine=innodb;

#查看共享表空間
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

#查看共享表空間默認(rèn)擴(kuò)展大小，默認(rèn)每次擴(kuò)展64M
mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

# 編寫數(shù)據(jù)庫配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend
-- 需要注意的是ibdata1文件大小必須和實(shí)際數(shù)據(jù)庫要存儲的數(shù)據(jù)相匹配，否則會(huì)出現(xiàn)如下報(bào)錯(cuò)信息
 [ERROR] [MY-012264] [InnoDB] The innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the .cnf file!
-- 表示ibdate1指定大小超過了原有ibdata1實(shí)際的大小尺寸

# 初始化配置文件
root@master:~# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend
# 模擬初始化操作命令
root@master:~# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 模擬初始化重啟服務(wù)
root@master:~# /etc/init.d/mysqld start

#表示每個(gè)表就是一個(gè)獨(dú)立文件，進(jìn)行數(shù)據(jù)信息的獨(dú)立存儲，不建議進(jìn)行修改，如果改為0就是所有數(shù)據(jù)統(tǒng)一存儲在共享表空間
select @@innodb_file_per_table;
+---------------------------------+
| @@innodb_file_per_table |
+---------------------------------+
|                                 1 |
+---------------------------------+
#設(shè)置為0表示利用共享表空間存儲用戶數(shù)據(jù) 1表示利用獨(dú)立表空間存儲用戶數(shù)據(jù)
set global innodb_file_per_table=0

# 步驟一：鎖定源端t100w表
# 給t100w表加寫數(shù)據(jù)鎖
mysql > lock tables test.t100w write;
# 獲取創(chuàng)建表結(jié)構(gòu)數(shù)據(jù)信息
mysql > show create table test.t100w;
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
#步驟二：目標(biāo)端創(chuàng)建oldboy庫和t100w空表
# 創(chuàng)建新的數(shù)據(jù)庫
mysql> create database test;
# 創(chuàng)建新的數(shù)據(jù)表
mysql > CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#步驟三：刪除目標(biāo)端空的表空間文件
# 刪除t100w表的ibd數(shù)據(jù)文件信息，但是保留t100w的frm，ibdata1中關(guān)于t100w的系統(tǒng)數(shù)據(jù)
mysql> alter table test.t100w discard tablespace;
#操作步驟四：拷貝源端ibd文件到目標(biāo)端目錄，并設(shè)置權(quán)限
root@master: ~# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
root@master: ~# chown -R mysql.mysql /data/*
#操作步驟五：導(dǎo)入表空間
# 在目標(biāo)端加載識別遷移過來的數(shù)據(jù)文件信息
mysql> alter table test.t100w import tablespace;
# 查看數(shù)據(jù)表中是否有遷移過來的數(shù)據(jù)信息
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.04 sec)
#步驟六：解鎖源端數(shù)據(jù)庫
mysql> unlock tables;

# 設(shè)置undo表空間文件數(shù)量（默認(rèn)2個(gè)文件）
mysql> select @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)
# 設(shè)置undo表空間文件大小（字節(jié)）
mysql> select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+
1 row in set (0.00 sec)
# 設(shè)置undo表空間回收機(jī)制（默認(rèn)開啟）
mysql> select @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)
# 設(shè)置undo表空間信息檢測次數(shù) （128次）
mysql> select @@innodb_purge_rseg_truncate_frequency; 
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.00 sec)

# 關(guān)閉數(shù)據(jù)庫服務(wù)程序，清理數(shù)據(jù)庫服務(wù)數(shù)據(jù)目錄
root@master:~# vim /etc/my.cnf
[mysqld]
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

mysql> select @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

vim /etc/my.cnf
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M

mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.01 sec)

# 編寫數(shù)據(jù)庫配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_log_file_size=100M
innodb_log_files_in_group=3

+-------------------------+
| Buffer Pool（緩沖池）   |
|  - 數(shù)據(jù)頁緩存           |
|  - 索引頁緩存           |
|  - 自適應(yīng)哈希索引       |
|  - 插入緩沖             |
|  - 鎖信息、數(shù)據(jù)字典等   |
+-------------------------+
| Log Buffer（日志緩沖）  |
+-------------------------+

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                1073741824 |
+---------------------------+
1 row in set (0.00 sec)

# buffer pool默認(rèn)內(nèi)存空間大小為128M，生產(chǎn)建議大小可以設(shè)置為物理內(nèi)存總量的50%~80%
方式一：
set global innodb_buffer_pool_size=268435456;
方式二：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=256M

mysql> select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.00 sec)

方式一：
set global innodb_log_buffer_size=33554432;
方式二：
vim /etc/my.cnf
[mysqld]
innodb_log_buffer_size=32M