MySQL 备份策略浅谈

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL 备份策略浅谈

余二五 2017-11-16 18:51:00 浏览645
展开阅读全文

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)

 

 未完待续










本文转自 trt2008 51CTO博客,原文链接:http://blog.51cto.com/chlotte/376294,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注