MySQL/MariaDB的备份与恢复

loinui

二进制日志简介

mysql的二进制日志从本质上来讲就是重做日志，它记录了mysql服务器上此前所发生的所有引起了数据改变或潜在会影响数据改变的相关的操作。这个在恢复数据时非常有用，可以基于备份，通过二进制日志文件将数据恢复至某一个具体的时间点。当然二进制日志文件不能和数据文件放在同一块磁盘上。
查看当前服务器的二进制日志文件

1 2 3 4 5 6 7

MariaDB [(none)]> show binary logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       355 | | mysql-bin.000002 |       351 | +------------------+-----------+

当一个日志文件的大小到达指定的大小后，日志文件会自动滚动。若要手动滚动，可以使用flush  logs。这个在备份的过程中经常用到。

1 2 3 4 5 6 7 8 9 10 11 12

MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> show binary logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       355 | | mysql-bin.000002 |       394 | | mysql-bin.000003 |       351 | +------------------+-----------+ 3 rows in set (0.00 sec)

显示指定的二进制日志文件中的相关事件
格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

MariaDB [(none)]> show binlog events in 'mysql-bin.000003'\G ........ *************************** 7. row ***************************    Log_name: mysql-bin.000003         Pos: 417 Event_type: Query   Server_id: 1 End_log_pos: 544        Info: use `sakila`; insert into actor(first_name,last_name) values('baby','James') *************************** 8. row ***************************    Log_name: mysql-bin.000003                          Pos: 544 Event_type: Xid   Server_id: 1 End_log_pos: 571        Info: COMMIT /* xid=16 */ .......

Log_name          #日志文件名
Pos                     #开始位置
Event_type         #事件类型
Server_id            #由哪一台服务器产生
End_log_pos       #结束位置
Info                     #日志信息


只是这里的日志没有时间，可以通过mysqlbinlog（客户端命令）工具来更详细地显示日志信息。
mysqlbinlog：

--start-position=#              #从指定的事件位置查看
--stop-position=#              #只显示到指定的事件位置
--start-datetime=YYYY-MM-DD hh:mm:ss               #从指定的时间查看
--stop-datetime=YYYY-MM-DD hh:mm:ss               #只显示到指定的时间

1 2 3 4 5 6 7 8 9 10

[iyunv@CentOS-6 mysql_bin_log]# mysqlbinlog mysql-bin.000003 ...... # at 637 #150728 15:41:22 server id 1  end_log_pos 764   Query   thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1438069282/*!*/; insert into actor(first_name,last_name) values('Neal','James') /*!*/; # at 764 ..........

# at 637                      #事件的开始位置（也是上一个事件的结束位置）
#150728 15:41:22      #事件发生的日期和时间

server id 1                  #事件发生所在的服务器的标识
end_log_pos 764        #事件的结束位置
Query                         #事件的类型
thread_id=5               #事件发生时所在的服务器执行此事件的线程的ID
exec_time=0              #语句的时间戳与将其写入二进制文件中的时间差
error_code=0             #错误代码
事件内容：

SET TIMESTAMP=1438069282/*!*/;
insert into actor(first_name,last_name) values('Neal','James')
/*!*/;            
# at 764                      #下一个日志的开始位置

日志的记录格式有3种：

1、基于“语句”记录：statement
2、基于“行”记录：row   （用这个记录数据更加精确）
3、“混合”模式 ：mixed

相关的服务器变量：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

MariaDB [(none)]> show global variables like '%binlog%'; +-----------------------------------+----------------------+ | Variable_name                     | Value                | +-----------------------------------+----------------------+ ............... | binlog_cache_size                 | 32768                |   #二进制日志缓存大小 | binlog_format                     | MIXED                |   #二进制日志记录格式(MIXED.statement ,row)    | binlog_stmt_cache_size            | 32768                | | max_binlog_cache_size             | 18446744073709547520 | | max_binlog_size                   | 1073741824           |  #二进制日志文件的单文件上限；达到这个大小了，会自动滚动 (单位字节) | max_binlog_stmt_cache_size        | 18446744073709547520 | | sync_binlog                       | 0                    |  #设定多久将缓存中的数据同步至二进制日志文件（磁盘上）；0表示不同步（有内部线程控制）；任何正值都表示记录多少个语句后同步一次； +-----------------------------------+----------------------+ MariaDB [(none)]> show global variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | log_bin                         | ON    |     #记二进制日志文件存放位置及文件名 | sql_log_bin                     | ON    |     #是否启用二进制日志 +---------------------------------+-------+

log_bin往往在配置文件中指定，如果指定了其他的存放目录，需要将该目录的属主和数组改成mysql，否则数据库无法启动。
在备份和恢复的过程中往往还需要查看数据库当前所记录的二进制日志文件以及当前事件所处的位置，

1 2 3 4 5 6

MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 |      791 |              |                  | +------------------+----------+--------------+------------------+

如果备份文件只备份了某个事件之前的所有数据（在备份时往往会记录这个事件的位置，简单起见，也可以直接滚动日志），那么后面所做的操作都需要通过二进制日志文件来恢复。通过--start-position指定备份结束的事件位置，--stop-position指定需要恢复到哪个事件。

1 2	[iyunv@CentOS-6 mysql_bin_log]# mysqlbinlog --start-position=num1 --stop-position=num2 mysql-bin.###### > /tmp/a.sql [iyunv@CentOS-6 mysql_bin_log]# mysql < /tmp/a.sql

当然，备份文件的事件结束位置和需要恢复至某个事件的位置之间可能隔了很多个日志文件，将它们全部导出，再导入至服务器执行一次即可。这个也可以通过--start-datetime，--stop-datetime来实现。


mysqldump备份恢复
mysqldump是一个客户端工具，通过mysql协议连接至数据库服务器，并将获取的数据结果转化为DDL，DML语句保存下来。备份的机制比较简单，但是如果数据库中的数据很多，mysqldump产生的SQL语句就会非常庞大，使得恢复的速度非常慢。mysqldump支持MyISAM表的温备，对InnoDB支持到热备。
为了操作的方便可以在家目录下的.my.cnf文件中添加[mysqldump]或[client]，将数据库管理员的用户名密码写在下面即可。如下：

1 2 3 4

[client] user = root password = ###### host = localhost

mysqldump常用选项：
-u,-h,-p                        #指定连接数据库的用户名密码，地址
-A, --all-databases       #备份所有数据库
-x, --lock-all-tables      #锁定所有表
-l, --lock-tables           #锁定备份的表（只有备份单张表才建议使用这个选项）
-B, --databases db_name1 db_name2 ...     #备份指定的数据库
--single-transaction     #启动一个大的单一事务实现备份（热备，只能针对InnoDB存储引擎来实                                             #现，如果备份的表中有MyISAM的表，则对于这些表没有任何意义）
-C, --compress             #压缩传输（在服务器端会占用更过的cup时钟周期）
-E, --events                  #备份指定库的事件调度器；
-R, --routines               #备份存储过程和存储函数；
--triggers                      #备份触发器
-F,--flush-logs              #锁定表之后执行flush logs命令（日志滚动）；
--master-data[=num]   #在完全备份的备份文件中记录下来，在备份那一时刻（锁定所有表时），对
                                      #应的二进制日志文件的文件名及其位置。num=0表示不记录，num=1，
                                      #记录CHANGE MASTER TO语句（语句未被注释），num=2，记录为注
                                      #释语句；

mysqldump备份过程

1、对所有表进行锁定，然后备份所有库。

1	[iyunv@CentOS-6 data]# mysqldump --all-databases  --lock-all-tables --master-data=2 > /backup/`date +%F`.all.sql

2、数据库更改之后，通过二进制日志文件对其进行增量备份。增量备份前先查看完全备份文件中记录的事件位置（--master-data），然后查看当前的事件位置，备份两个事件之间的所有事件。这里没有进行日志滚动，简单起见，可以直接在执行完全备份时加上--flush-logs，自动实现日志滚动。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[iyunv@CentOS-6 backup]# vim 2015-07-28.all.sql ..... -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1093; ....                #备份时事件点 MariaDB [hellodb]> show master status;                           #当前事件点 +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 |     9677 |              |                  | +------------------+----------+--------------+------------------+ [iyunv@CentOS-6 data]# mysqlbinlog --start-position=1093 --stop-position=9677 /data/mysql_bin_log/mysql-bin.000003 > /backupup/`date +%F`.inc.sql

以上的备份是温备，备份过程中需要对表进行锁定，数据无法进行更改。如果需要热备，加上--single-transaction即可，当然锁定表就不需要了（--lock-all-tables）。热备需要

mysqldump恢复过程
1、在恢复过程中，也会产生大量的二进制日志，这些日志没有什么用，可以在恢复开始之前将数据库上的二进制日志功能关闭。

1 2	MariaDB [(none)]> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec)

2、在最后一次增量备份之后与数据库崩溃之前的这段时间内，数据的变化没有备份，这段时间内的数据变化信息需要通过二进制日志文件获取，所以在数据库崩溃之后，首先要保护其二进制日志文件。假设数据库崩溃之前的最后一个事件位置为10510。（这段时间内产生的二进制日志文件可能有很多个，除了最后一个日志文件，其余的一一导出即可，最后一个日志文件导出至某一个事件位置之前）

1	[iyunv@CentOS-6 data]# mysqlbinlog --start-position=9677 --stop-position=10510 /data/mysql_bin_log/mysql-bin.000003 > /backup/`date +%F`.inc2.sql

3、导入备份的数据。

1 2 3

[iyunv@CentOS-6 backup]# mysql < 2015-07-28.all.sql [iyunv@CentOS-6 backup]# mysql < 2015-07-28.inc.sql [iyunv@CentOS-6 backup]# mysql < 2015-07-28.inc2.sql

mysqldump部分备份
mysqldump [OPTIONS] database [tables]：备份一个或多个表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库

备份恢复单个表

1 2 3 4 5 6 7 8 9

[iyunv@CentOS-6 backup]# mysqldump hellodb students --single-transaction --master-data=2 > /backup/students.sql; ##### [iyunv@CentOS-6 backup]# vim students.sql ..... use hellodb;                         #在还原表前，在备份文件开头添加使用的数据库 ###### [iyunv@CentOS-6 backup]# mysql < students.sql          # 恢复表

备份多个表或库，与此类似，只是通过二进制日志恢复时，需要在二进制日志文件中筛选出仅针对某一个表或者库的操作，然后在导出，恢复。在实现部分备份时，要确保备份的数据中所有的InnoDB表都需要存放为单个表空间（每表单独表空间），不然只能备份所有库。


lvm2快照备份
lvm2属于几乎热备，在快照的那一刻，需要对全表进行锁定，快照完成之后立刻释放锁。
1、锁定所有表

1 2	MariaDB [hellodb]> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

2、记录当前的事件位置，也可以直接滚动日志

1 2 3 4 5 6 7

MariaDB [(none)]> show master status ; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 |      365 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

3、创建快照（前提是数据库的数据目录必须放在逻辑卷上）

1 2	[iyunv@CentOS-6 mysql_bin_log]# lvcreate -L 2G -s -p r -n dataBackup /dev/babyVG/babyLV   Logical volume "dataBackup" created

4、解锁所有表

1 2	MariaDB [(none)]> unlock tables; Query OK, 0 rows affected (0.00 sec)

5、挂载快照，复制快照中的数据至备份目录。然后删除快照

1 2 3 4 5

[iyunv@CentOS-6 backup]# mount /dev/babyVG/dataBackup /backup/snapshot/ mount: block device /dev/mapper/babyVG-dataBackup is write-protected, mounting read-only [iyunv@CentOS-6 backup]# cp -a /backup/snapshot/* /backup/lvmbackup/ [iyunv@CentOS-6 backup]# lvremove /dev/babyVG/dataBackup   Logical volume babyVG/dataBackup contains a filesystem in use.

还原：（服务关闭状态下）
1、备份之后的数据变化通过日志导出

1	[iyunv@CentOS-6 mysql_bin_log]# mysqlbinlog --start-position=365 --stop-position=1183 mysql-bin.000005 > /tmp/a.sql;

2、复制备份数据至数据目录，修改权限

1 2	[iyunv@CentOS-6 mysql]# cp -a /backup/lvmbackup/* ./ [iyunv@CentOS-6 mysql]# chown -R mysql:mysql ./*

3、启动服务，导入数据

1 2 3

[iyunv@CentOS-6 mysql]# service mysqld start Starting MySQL.                                            [  OK  ] [iyunv@CentOS-6 mysql]# mysql < /tmp/a.sql

Xtrabackup备份恢复
Xtrabackup是由percona提供的mysql数据库备份工具，是一个开源的工具，能够对innodb和xtradb数据库进行热备和增量备份，对于MyISAM，仅支持到温备，对MyISAM使用增量备份时，其实是完全备份。Xtrabackup使用简单，功能强大。Xtrabackup具备的特点：(1)备份过程快速、可靠；(2)备份过程不会打断正在执行的事务；(3)能够基于压缩等功能节约磁盘空间和流量；(4)自动实现备份检验；(5)还原速度快；
可从官网上下载到最新版本（http://www.percona.com/software/percona-xtrabackup/ ），直接下载对应的rpm包即可。下载到本地之后使用yum安装（yum install xxx.rmp），会自动解决依赖关系。


完全备份
1、首先创建一个专门用于备份用户（最小权限）

1 2 3 4 5 6 7 8

MariaDB [hellodb]> create user 'bkpuser'@'localhost' identified by 'bkpuser'; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> grant reload,lock tables,replication client on *.* to 'bkpuser'@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> flush privileges; Query OK, 0 rows affected (0.00 sec)

2、备份数据，--no-timestamp选项来阻止命令自动创建一个以时间命名的目录，此时需要自行命名目录。/back/whole.back即为备份的存放路径。

1 2 3 4

[iyunv@CentOS-6 ~]# innobackupex --user=bkpuser --password=bkpuser --no-timestamp /backup/whole.back ....... 150728 21:01:15  innobackupex: Connection to database server closed 150728 21:01:15  innobackupex: completed OK!

在使用innobakupex备份时，其会调用xtrabackup来实现备份，备份还包括触发器和数据库配置信息相关的文件。
下面是备份的存放目录。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[iyunv@CentOS-6 whole.back]# ll total 178216 -rw-r--r--. 1 root root      358 Jul 28 21:19 backup-my.cnf drwxr-x---. 2 root root     4096 Jul 28 21:45 hellodb -rw-r-----. 1 root root 79691776 Jul 28 21:45 ibdata1 -rw-r--r--. 1 root root 50331648 Jul 28 21:45 ib_logfile0 -rw-r--r--. 1 root root 50331648 Jul 28 21:45 ib_logfile1 drwx------. 2 root root     4096 Jul 28 21:45 mysql drwxr-xr-x. 2 root root     4096 Jul 28 21:19 performance_schema drwxr-x---. 2 root root     4096 Jul 28 21:45 sakila drwxr-xr-x. 2 root root     4096 Jul 28 21:19 test -rw-r--r--. 1 root root       33 Jul 28 21:38 xtrabackup_binlog_info -rw-r--r--. 1 root root       45 Jul 28 21:45 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root       91 Jul 28 21:45 xtrabackup_checkpoints -rw-r--r--. 1 root root      662 Jul 28 21:38 xtrabackup_info -rw-r-----. 1 root root  2097152 Jul 28 21:28 xtrabackup_logfile

xtrabackup_checkpoints   #备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）
                                          #和LSN(日志序列号)范围信息；
xtrabackup_binlog_info    #mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日
                                          #志事件的位置。
backup-my.cnf                  # 备份命令用到的配置选项信息
xtrabackup-info                #mysql服务器的属性设定信息
xtrabackup_logfile            #xtrabackup的日志文件


恢复
在备份完成后，数据还不能直接用于恢复，因为在备份的数据中可能会包含未提交的事务或已经提交但尚未同步至数据文件中的事务。通过-apply-log选项可以实现对未提交的事务进行回滚，同步已经提交的事务至数据文件，使得数据文件处于一致性状态。在这个过程中还可以使用--use-memory选项来设置其可以使用的内存大小（默认100M），以加快其速度。当看到completed OK!，说明上述的事件已经完成，数据已经可以用于恢复。
1、准备一个完全备份

1 2 3

[iyunv@CentOS-6 backup]# innobackupex --apply-log /backup/whole.back/ ...... 150728 21:07:56  innobackupex: completed OK!

2、通过--copy-back将数据复制到数据库的数据目录下。

1 2 3

[iyunv@CentOS-6 backup]# innobackupex --copy-back /backup/whole.back/ ...... 150728 21:09:48  innobackupex: completed OK!

3、注意属组属主的修改，不然无法启动mysql。

1 2 3

[iyunv@CentOS-6 mysql]# chown -R mysql:mysql /data/mysql [iyunv@CentOS-6 mysql]# service mysqld start Starting MySQL.                                            [  OK  ]

在恢复的整个过程中，mysql服务不需要启动。xtrabackup的恢复不包括二进制日志文件的恢复。


Xtrabackup增量备份
使用--incremental选项指定这是一个增量备份过程，通过--incremental-basedir指定这个增量备份是基于哪个完全备份。如果不是第一次增量备份，--incremental-basedir应该指向上一次的增量备份。完成之后会有一个独立的备份文件。
1、完全备份

1 2 3

[iyunv@CentOS-6 ~]# innobackupex --user=bkpuser --password=bkpuser --no-timestamp /backup/whole.back ..... 150728 21:19:55  innobackupex: completed OK!

2、第一次增量备份

1 2 3

[iyunv@CentOS-6 ~]# innobackupex --no-timestamp --incremental /backup/inc.1.back --incremental-basedir=/backup/whole.back/ ...... 150728 21:22:36  innobackupex: completed OK!

3、第二次增量备份

1 2 3

[iyunv@CentOS-6 ~]# innobackupex --no-timestamp --incremental /backup/inc.2.back --incremental-basedir=/backup/inc.1.back/ ....... 150728 21:24:48  innobackupex: completed OK!

增量备份的恢复
--redo-only表示仅提交已完成的事务，而不回滚未提交的事务，因为未提交的事务可能在增量备份中提交，将增量备份合并至完全备份后，恢复即可。
1、合并所有的增量备份至完全备份。最后一个增量备份合并时可以不加--redo-only选项

1 2 3 4 5 6 7 8 9 10 11

[iyunv@CentOS-6 ~]# innobackupex --apply-log --redo-only /backup/whole.back/ ...... 150728 21:28:47  innobackupex: completed OK! [iyunv@CentOS-6 ~]# innobackupex --apply-log --redo-only /backup/whole.back/ --incremental-dir=/backup/inc.1.back/ ...... 150728 21:37:48  innobackupex: completed OK! [iyunv@CentOS-6 ~]# innobackupex --apply-log --redo-only /backup/whole.back/ --incremental-dir=/backup/inc.2.back/ ....... 150728 21:38:48  innobackupex: completed OK!

2、复制到数据库的数据目录下。

1 2 3 4

[iyunv@CentOS-6 ~]# innobackupex --copy-back /backup/whole.back/ [iyunv@CentOS-6 ~]# chown -R mysql:mysql /data/mysql [iyunv@CentOS-6 ~]# service mysqld start Starting MySQL....                                         [  OK  ]

单张表的导入导出
MyISAM表可以通过直接复制文件的方式，实现单张表在MySQL服务器之间的转移，InnoDB表无法通过此方式来实现转移。xtrabackup工具可以帮助实现此功能（前提是InnoDB表的表空间单独存放）。

1、这个需要在数据文件处于一致性状态下，通过--export选项将某表导出（所有的表）

1 2 3

[iyunv@CentOS-6 ~]# innobackupex --apply-log --export /backup/whole.back/ ........ 150728 21:45:08  innobackupex: completed OK!

2、在目标服务器上创建一个跟原表表结构完全一致的表。

1	MariaDB [hellodb]> CREATE TABLE mytable (...)  ENGINE=InnoDB;

3、删除此表的表空间

1	MariaDB [hellodb]> alter table database.tableName discard tablespace;

4、从“导出”表的服务器上，复制该表的table.ibd和table.exp文件至目标服务器的数据目录中（复制过来的文件属组属主都应该是mysql）

1 2 3

[iyunv@CentOS-6 hellodb]# cp -a students.ibd students.exp /data/mysql/hellodb/ [iyunv@CentOS-6 hellodb]# cd /data/mysql/hellodb/ [iyunv@CentOS-6 hellodb]# chown mysql:mysql students.ibd students.exp

5、导入表空间

1	MariaDB [hellodb]> alter table students import tablespace;

恢复完成.................^_^