MySQL 备份策略浅谈

影者东升

　　MySQL备份方法有许多种，要根据业务需求来制定适合自已的方案，以前在QQ群里经常见到有朋友询问如何备份的事情，也时常会有热心的朋友答复：做主从，然后在从上备份就行了，这个方法勉强能用，可是很多朋友们的生产环境大多是单台DB，就像我所在的游戏行业，一款游戏可能开几百组服务器，做个主从，是不可能的事情，毕竟成本很重要，我就说下目前我所知道的备份方法：
1,主从架构，在从上做备份。

2,对于单台服务器，如果存储引擎是innodb格式的，可以采用binlog来实时热备份，这种方式的优点：实时热备，数据完整，但需要注意磁盘空间。还有一种方法就是用xtrabackup,关于xtrabackup具体的使用方法我会在下一篇文章中说明。

3,mysqldump --single-transaction
假如我们的表为事务存储引擎InnoDB表或BDB表,此选项不会干扰对表的读写。因为数据库是可以做到所读取的数据处于同一个时间点的。

4，文件系统快照（LVM）在线备份。

一，Xtrabackup备份与恢复实例：

1，安装Xtrabackup，直接下载二进制版本解压，下面的实验是用的xtrabackup-0.9，现在最新的稳定版本是xtrabackup-1.2。

2，下面的试验是模拟清空了两表中的数据(清空了一张innodb格式与一张myisam格式表中的数据)，然后进行数据恢复,首先要保证表结构正常，在我们进行实验之前，innodb表与myisam表中各有100W条记录，我们首先进行了全备份,然后又插入了50W条记录，进行增量备份，接着又插入了200W条记录，进行第二次增量备份，也就是进行了1次全量+2次增量之后，我们清空两张表中的内容，并进行恢复。
mysql> select count(id) from innodb;
+-----------+
| count(id) |
+-----------+
| 1000000 |
+-----------+

1 row in set (0.02 sec)

mysql> select count(id) from myisam;
+-----------+
| count(id) |
+-----------+
| 1000000 |
+-----------+
1 row in set (0.00 sec)

全备份
[root@localhost quanbei]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql/
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (31 1338120523)
Copying /var/lib/mysql/ibdata1
to /var/bak/xtbak/quanbei/ibdata1
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
...done
Copying /var/lib/mysql/ibdata2
to /var/bak/xtbak/quanbei/ibdata2
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
>> log scanned up to (31 1338120523)
...done
xtrabackup: The latest check point (for incremental): '31:1338120523'
>> log scanned up to (31 1338120523)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (31 1338120523) to (31 1338120523) was copied.
[root@localhost quanbei]# ll
总计 2533832
-rw-r--r-- 1 root root 1048576000 02-06 15:39 ibdata1
-rw-r--r-- 1 root root 1543503872 02-06 15:40 ibdata2
-rw-r--r-- 1 root root 66 02-06 15:40 xtrabackup_checkpoints
-rw-r--r-- 1 root root 2560 02-06 15:40 xtrabackup_logfile
[root@localhost quanbei]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0:0
to_lsn = 31:1338120523
[root@localhost quanbei]#

增量1：
mysql> select count(id) from innodb;
+-----------+
| count(id) |
+-----------+
| 1500000 |
+-----------+
1 row in set (0.39 sec)

增量1：
mysql> select count(id) from myisam;
+-----------+
| count(id) |
+-----------+
| 1500000 |
+-----------+
1 row in set (0.00 sec)
增量1备份：
[root@localhost increment]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/var/bak/increment --incremental-basedir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
incremental backup from 31:1338120523 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql/
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (31 1566968552)
Copying /var/lib/mysql/ibdata1
to /var/bak/increment/ibdata1.delta
>> log scanned up to (31 1566968552)
>> log scanned up to (31 1566968552)
...done
Copying /var/lib/mysql/ibdata2
to /var/bak/increment/ibdata2.delta
>> log scanned up to (31 1566968552)
>> log scanned up to (31 1566968552)
>> log scanned up to (31 1566968552)
>> log scanned up to (31 1566968552)
>> log scanned up to (31 1566968552)
...done
xtrabackup: The latest check point (for incremental): '31:1566968552'
>> log scanned up to (31 1566968552)
xtrabackup: Stopping log copying thread..
xtrabackup: Transaction log of lsn (31 1566968552) to (31 1566968552) was copied.
[root@localhost increment]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31:1338120523
to_lsn = 31:1566968552
[root@localhost increment]#
增量2：
mysql> select count(id) from innodb;
+-----------+
| count(id) |
+-----------+
| 3500000 |
+-----------+
1 row in set (0.91 sec)
增量2：
mysql> select count(id) from myisam;
+-----------+
| count(id) |
+-----------+
| 3500000 |
+-----------+
1 row in set (0.00 sec)
增量2备份：
[root@localhost increment]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/var/bak/increment --incremental-basedir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
incremental backup from 31:1338120523 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql/
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (31 1887044038)
Copying /var/lib/mysql/ibdata1
to /var/bak/increment/ibdata1.delta
>> log scanned up to (31 1887044038)
>> log scanned up to (31 1887044038)
>> log scanned up to (31 1887044038)
...done
Copying /var/lib/mysql/ibdata2
to /var/bak/increment/ibdata2.delta
>> log scanned up to (31 1887044038)
>> log scanned up to (31 1887044038)
>> log scanned up to (31 1887044038)
>> log scanned up to (31 1887044038)
...done
xtrabackup: The latest check point (for incremental): '31:1887044038'
>> log scanned up to (31 1887044038)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (31 1887044038) to (31 1887044038) was copied.
[root@localhost increment]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31:1338120523
to_lsn = 31:1887044038

清空两张表中的所有记录，大家在生产环境中在执行这种操作的时候可要注意：
mysql> delete from innodb;
Query OK, 3500000 rows affected (22.62 sec)
mysql> delete from myisam;
Query OK, 3500000 rows affected (0.24 sec)
全恢复：
[root@localhost increment]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: cd to /var/bak/xtbak/quanbei
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(31 1338120523)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100206 16:06:58 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
100206 16:06:58 InnoDB: Started; log sequence number 31 1338120523
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100206 16:06:58 InnoDB: Starting shutdown...
100206 16:07:00 InnoDB: Shutdown completed; log sequence number 31 1338120523
[root@localhost increment]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: cd to /var/bak/xtbak/quanbei
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
100206 16:07:26 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
100206 16:07:29 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100206 16:07:32 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
100206 16:07:32 InnoDB: Started; log sequence number 31 1338120716
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100206 16:07:32 InnoDB: Starting shutdown...
100206 16:07:33 InnoDB: Shutdown completed; log sequence number 31 1338120716
增量恢复(只需要全备份加上最后一个增量备份即可)：
[root@localhost increment]# /usr/bin/xtrabackup --prepare --target-dir=/var/bak/xtbak/quanbei --incremental-dir=/var/bak/increment
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
incremental backup from 31:1338120523 is enabled.
xtrabackup: cd to /var/bak/xtbak/quanbei
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(31 1887044038)
Applying /var/bak/increment/ibdata1.delta ...
Applying /var/bak/increment/ibdata2.delta ...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/bak/increment
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
100206 16:08:04 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
100206 16:08:08 InnoDB: Started; log sequence number 31 1887044038
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
100206 16:08:08 InnoDB: Starting shutdown...
100206 16:08:09 InnoDB: Shutdown completed; log sequence number 31 1887044038
[root@localhost increment]# /usr/bin/xtrabackup --prepare --target-dir=/var/bak/xtbak/quanbei --incremental-dir=/var/bak/increment
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
incremental backup from 31:1338120523 is enabled.
xtrabackup: cd to /var/bak/xtbak/quanbei
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
Applying /var/bak/increment/ibdata1.delta ...
Applying /var/bak/increment/ibdata2.delta ...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/bak/increment
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
100206 16:08:41 InnoDB: Log file /var/bak/increment/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /var/bak/increment/ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
100206 16:08:47 InnoDB: Log file /var/bak/increment/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /var/bak/increment/ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Cannot initialize created log files because
InnoDB: data files were not in sync with each other
InnoDB: or the data files are corrupt.
xtrabackup: innodb_init(): Error occured.

[root@localhost increment]# cd /var/bak/xtbak/quanbei/
[root@localhost quanbei]# ll
总计 3060696
-rw-r--r-- 1 root root 1048576000 02-06 16:08 ibdata1
-rw-r--r-- 1 root root 1543503872 02-06 16:08 ibdata2
-rw-r--r-- 1 root root 268435456 02-06 16:07 ib_logfile0
-rw-r--r-- 1 root root 268435456 02-06 16:07 ib_logfile1
-rw-r--r-- 1 root root 66 02-06 16:08 xtrabackup_checkpoints
-rw-r--r-- 1 root root 2097152 02-06 16:07 xtrabackup_logfile
[root@localhost quanbei]# cp ib* /var/lib/mysql/
cp：是否覆盖“/var/lib/mysql/ibdata1”? y
cp：是否覆盖“/var/lib/mysql/ibdata2”? y
cp：是否覆盖“/var/lib/mysql/ib_logfile0”? y
cp：是否覆盖“/var/lib/mysql/ib_logfile1”? y
[root@localhost quanbei]# service mysql stop
Shutting down MySQL....[确定]
[root@localhost quanbei]# service mysql start
Starting MySQL..[确定]
[root@localhost quanbei]# mysql -uroot -proot test;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select count(id) from innodb;
+-----------+
| count(id) |
+-----------+
| 3500000 |
+-----------+
1 row in set (1.97 sec)
mysql> select count(id) from myisam;
+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql>
innodb格式的数据能完全恢复，而myisam格式的数据不能恢复。

3，
下面的试验是模拟删除了mysql目录下以ib开头的文件,首先还是要有备份，然后利用备份进行恢复。
备份：
[root@localhost quanbei]# /usr/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/var/bak/xtbak/quanbei
/usr/bin/xtrabackup Ver 0.9 Rev 83 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql/
xtrabackup: innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (31 2263387648)
Copying /var/lib/mysql/ibdata1
to /var/bak/xtbak/quanbei/ibdata1
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
...done
Copying /var/lib/mysql/ibdata2
to /var/bak/xtbak/quanbei/ibdata2
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
>> log scanned up to (31 2263387648)
...done
xtrabackup: The latest check point (for incremental): '31:2263388012'
>> log scanned up to (31 2263387648)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (31 2263388012) to (31 2263387648) was copied.
[root@localhost quanbei]# ll
总计 2533832
-rw-r--r-- 1 root root 1048576000 02-06 17:47 ibdata1
-rw-r--r-- 1 root root 1543503872 02-06 17:47 ibdata2
-rw-r--r-- 1 root root 66 02-06 17:47 xtrabackup_checkpoints
-rw-r--r-- 1 root root 2048 02-06 17:46 xtrabackup_logfile
[root@localhost quanbei]#
删除以ib开头的文件:
[root@localhost quanbei]# rm -rf /var/lib/mysql/ib*

[root@localhost quanbei]# service mysql stop
Shutting down MySQL....[确定]
[root@localhost quanbei]# cp ib* /var/lib/mysql/
cp：是否覆盖“/var/lib/mysql/ibdata1”? y
cp：是否覆盖“/var/lib/mysql/ibdata2”? y
[root@localhost quanbei]# ll -h /var/lib/mysql/

[root@localhost quanbei]# service mysql start
Starting MySQL.Manager of pid-file quit without updating file.[失败]

[root@localhost mysql]# chown mysql.mysql ib*
[root@localhost mysql]# service mysql start
Starting MySQL..[确定]

mysql> select count(id) from innodb;
+-----------+
| count(id) |
+-----------+
| 3500000 |
+-----------+
1 row in set (1.64 sec)
mysql> select count(id) from myisam;
+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

未完待续

　　

[MySQL] MySQL 备份策略浅谈

相关帖子

浏览过的版块

常用JavaScript的注释方法示例

怎么在JavaScript中加注释

常用HTML网页跳转的几种方法

Win10取消开机密码的2种方法

js保留两位小数方法总结

php使用GD库创建图片缩略图的方法

Discuz应用中心恢复授权的方法

Discuz X重置应用中心数据的方法

react如何引入css

阿里云盘获取refresh_token方法

影者东升 LV4