xtrabackup 备份
安装
数据库关键参数设置
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES
脚本
ALTER TABLE tbl_name
IMPORT TABLESPACE;
单表恢复
备份目录数据概览
InnoDB
tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB
table and associated indexes in its own data file. This feature is called “file-per-table tablespaces” because each table has its own tablespace, and each tablespace has its own .ibd
data file. This feature is controlled by the innodb_file_per_table
configuration option.Portability Considerations for .ibd Files
You cannot freely move .ibd
files between database directories as you can with MyISAM
table files. The table definition stored in the InnoDB
shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.
To move an .ibd
file and the associated table from one database to another, use a RENAME TABLE
statement:
db1.tbl_name
db2.tbl_name
If you have a “clean” backup of an .ibd
file, you can restore it to the MySQL installation from which it originated as follows:
The table must not have been dropped or truncated since you copied the
.ibd
file, because doing so changes the table ID stored inside the tablespace.Issue this
ALTER TABLE
statement to delete the current.ibd
file:ALTER TABLE
tbl_name
DISCARD TABLESPACE;Copy the backup
.ibd
file to the proper database directory.Issue this
ALTER TABLE
statement to tellInnoDB
to use the new.ibd
file for the table:ALTER TABLE
tbl_name
IMPORT TABLESPACE;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步