MySQL之备份与恢复

--

-- Current Database: `test`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--

-- Table structure for table `test01`

--

备份二进制日志

[root@localhost ~]#mysqlbinlog localhost-bin.000003 > /root/binlog.sql

删除二进制日志中的错误SQL语句

vim binlog.sql

...

DROP TABLE `test01`

DROP TABLE IF EXISTS `test01`

...

恢复：

mysql -uroot -p  test < backbinlog.sql

查看恢复后的表：

mysql> select * from test01;

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

| id   | name |

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

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

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

5 rows in set (0.00 sec)

2 xtrabackup——对InnoDB做数据备份的工具，备份方式为物理备份，而且支持热备，

1）安装，可以提前安装好epel的yum源

[root@localhost ~]# wget [root@localhost ~]# rpm -ivh https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.8/binary/redhat/6/i386/percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~]# yum -y localinstall percona-xtrabackup-2.3.8-1.el6.i686.rpm 

2）备份和恢复

备份：

a 建立备份账户：

mysql> grant reload,lock tables,replication client on *.* to ‘dbbak‘@‘localhost‘ identified by ‘bk2016‘ ;

b 完全备份，socket可以不指定，除非和默认不一致

[root@localhost ~]# innobackupex --socket=/tmp/mysqld.sock --user=dbbak --password=bk2016 /root/

...

170501 23:20:35 Backup created in directory ‘/root/2017-05-01_23-20-25/‘

MySQL binlog position: filename ‘localhost-bin.000003‘, position ‘15651‘

170501 23:20:35 [00] Writing backup-my.cnf

170501 23:20:35 [00]        ...done

170501 23:20:35 [00] Writing xtrabackup_info

170501 23:20:35 [00]        ...done

xtrabackup: Transaction log of lsn (2031876) to (2031876) was copied.

170501 23:20:35 completed OK!

[root@localhost ~]# cd 2017-05-01_23-20-25/            #查看备份后的目录

[root@localhost 2017-05-01_23-20-25]# ls

backup-my.cnf  ibdata1  mysql  performance_schema  test  xiaoshou  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  yewu

备份目录说明：

xtrabackup_checkpoints——备份类型（如完全和增量）、备份状态、LSN（日志序列号）范围信息

xtrabackup_my.cnf——备份命令用到的配置选项信息

xtrabackup_binlog_info——MySQL服务器当前使用的二进制日志文件及到备份这一刻为止二进制日志事件的位置

c 预处理备份文件，准备一个完全备份，可增加内存选项，--use-memory=1G

[root@localhost ~]# innobackupex --apply-log /root/2017-05-01_23-20-25 

d 数据更改之后，通过二进制进行增量备份

[root@localhost 2017-05-01_23-20-25]# cat xtrabackup_binlog_info 

localhost-bin.000003 15651

[root@localhost ~]# mysqlbinlog /usr/data/mysql/localhost-bin.000003 > /root/bin$(date +%F).sql

恢复：

a 停止服务

[root@localhost ~]# service mysqld stop

b 恢复全备，MySQL数据目录必须为空，否则会报错

[root@localhost ~]# innobackupex --copy-back --user=dbbak --password=bk2016 /root/2017-05-01_23-20-25/

[root@localhost mysql]# cd /usr/local/mysql/            #修改数据目录的属主属组

[root@localhost mysql]# chown -R mysql.mysql *

c 启动服务

[root@localhost mysql]# service mysqld start

d 通过二进制文件恢复增量备份

mysql> set sql_log_bin=0;                    #暂时关闭二进制日志

Query OK, 0 rows affected (0.03 sec)

mysql> source /root/bin2017-05-01.sql;

...

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set sql_log_bin=1;                    #开启二进制日志

Query OK, 0 rows affected (0.00 sec)

e 恢复完成后，重新进行一次完全备份

    表的导入和导出

本文出自 “一万年太久，只争朝夕” 博客，请务必保留此出处http://zengwj1949.blog.51cto.com/10747365/1921030

MySQL之备份与恢复

标签：mysql 备份 恢复