在线迁移

源库锁表

FLUSH TABLE tb1 FOR EXPORT

只针对 InnoDB，t1表加全局读锁（只能读，不能写，保证数据的一致性） 表，可以确保对表的修改被刷新到磁盘上，MySQL可以通过直接拷贝底层文件的方式来复制表

源库拷贝数据字典与数据文件到远程机器

scp tb1.cfg tb1.ibd 100.64.xx.xx:/tmp

源库解锁表

unlock tables;

目标库里新建表结构

create table tb1 xxx;

目标库里丢弃表空间

alter table tb1 discard tablespace;  

该命令会关闭tb1表的数据空间，并会删除其数据文件

从/tmp中拷贝ibd和cfg文件到数据目录并且修改权限

cp /tmp/{tb1.ibd,tb1.cfg} /xfjrdb/xfjrdata/test/
chown -R mysql.mysql /xfjrdb/xfjrdata/test/{tb1.ibd,tb1.cfg}

导入表空间

alter table tb1 import tablespace;
check table tb1;
select count(*) from tb1;

物理备份恢复单表

安装工具

mysql-utilities，其中mysqlfrm可以读取表结构。

需安装以下rpm包

mysql-connector-python
mysql-utilities

从物理备份获取ibd文件

innobackupex --defaults-file=/xfjrdb/xfjrdb.cnf  --user=root --password=123456 --socket=/xfjrdb/xfjrdb11.sock --no-timestamp --slave-info --parallel=4 /databack/xfjrdb/

若要指定某些库或表可用下列参数
--include='^dbname[.]*'
--tables-file=/tmp/tables.txt

innobackupex --apply-log --redo-only /databack/xfjrdb

利用mysql-utilities工具读取表结构

将读取的表结构拿到数据库进行创建

mysqlfrm --diagnostic /databack/xfjrdb/test/tb1.frm

加一个写锁，确保安全

lock tables tb1 write;

丢弃表空间

alter table tb1 discard tablespace;  

从备份中拷贝ibd文件，并且修改权限

cp /databack/xfjrdb/test/tb1.ibd /xfjrdb/xfjrdata/test/
chown -R mysql.mysql /xfjrdb/xfjrdata/test/tb1.ibd

导入表空间

alter table sbtest1 import tablespace

解锁表

unlock tables;

批量拷贝ibd文件加载空间恢复表数据

问题描述

出现测试环境数据库ibdata1损坏，无法启动。

分析

通过开发环境的mysql全量备份，恢复到测试环境。由于开发环境的admin库数据库结构跟测试环境的admin库一样，但是数据不一样，需要用原测试环境数据文件恢复原表数据。

问题解决

1.备份数据

mkdir backup
cp dbs/admin/* backup/

2.开发环境mysqlbackup全量备份恢复到测试环境

3.卸载库admin所有表空间

mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ DISCARD tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_discard.sql

vi admin_discard.sql
删除第一行concat(‘alter table ‘,table_name,’ DISCARD tablespace;’)

mysql -uroot -p admin < admin_discard.sql

4.拷贝回.ibd文件

cp -f backup/*.ibd dbs/admin/

5.加载库admin所有表空间

mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ IMPORT tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_import.sql

select concat('alter table ',table_name,' DISCARD tablespace;') from information_schema.TABLES where table_schema='prodccsdb' and table_name in('ccs_repay_hst','ccs_loan','ccs_app_main');


vi admin_import.sql
删除第一行concat(‘alter table ‘,table_name,’ IMPORT tablespace;’)

mysql -uroot -p admin < admin_import.sql