mysql 单张表还原_浅谈mysql 针对单张表的备份与还原

A、MySQL 备份工具xtrabackup 的安装

1. percona 官方xtrabackup 的二进制版本；二进制版本解压就能用了。

2. 解压xtrabackup & 创建连接

tar -xzvf percona-xtrabackup-2.3.4-Linux-x86_64.tar.gz -C /usr/local/

ln -s /usr/local/percona-xtrabackup-2.3.4 /usr/local/xtrabackup

3. 设置PATH环境变量

export PATH=/usr/local/xtrabackup/bin/:$PATH

B、在mysql数据库中创建一个用户备份的用户 & 授权

1. 创建用户

create user backuper@'localhost' identified by 'backup123';

create user backuper@'127.0.0.1' identified by 'backup123';

2. 授权

grant reload,lock tables,replication client,process,super on *.* to 'backuper'@'localhost';

grant create,insert,select on percona_schema.xtrabackup_history to 'backuper'@'localhost';

grant reload,lock tables,replication client,process,super on *.* to 'backuper'@'127.0.0.1';

grant create,insert,select on percona_schema.xtrabackup_history to 'backuper'@'127.0.0.1';

C、备份前的检查，这一步的主要目地是在之后做还原操作时，验证还原是不是有效；(生产是没有这一步的,

1. select * from tempdb.dict__major;

select * from dict__major;

+--------------+-----------------+

| column_value | column_mean     |

+--------------+-----------------+

|            1 | 汉语言文学      |

|            2 | 精算            |

|            3 | 生物制药        |

|            4 | 材料化学        |

|            5 | 商务英语        |

|            6 | 考古            |

|            7 | 外交            |