MySQL 备份策略浅谈

sunyke

　　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:>　　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>　　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>　　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:>　　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>　　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>　　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>　　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)
　　未完待续