浅谈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 | 外交            |