mysql 在线热备_MySQL备份的几种方式的讨论，重点关注在线热备

MySQL的备份方式，目前我想到的有五种，有可能还有

1，mysqldump方式，加上具体参数名(单库，多库，触发器，存储过程，表结构，字符集，–single-transaction，等等)

2，mysqlhotcopy 只能备份myisam数据表备份，速度相当快，因为是文件拷贝，可能瞬间被锁表，任何的数据操作，比如插入和更新都会挂起。

3，LVM的快照功能进行数据库分区的备份，这种方法是利用的逻辑卷的镜像功能，对整个分区进行在线备份，这种备份数据量大，而且备份性能低下，因为每次备份都是整个镜像，不能针对数据做备份。桶装备份

4，开启二进制同步日志功能，主从复制，从机器做备份功能。

5，在线的热备份，采用开源的 Xtrabackup 备份工具对innodb 数据表进行在线备份，测试阶段。

下面是备份的xtrabackup的测试例子。

[@root.localhost.nova ~]# /usr/bin/innobackupex-1.5.1 –user=root

–defaults-file=/home/mysql/my.cnf –socket=/home/mysql/mysql.sock

–database=serverinfo –slave-info –stream=tar /root/ |gzip >

/root/bak_mysql.tar.gz

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.

All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackup

prints “innobackup completed OK!”.

innobackupex: Using mysql Ver 14.12 Distrib 5.0.83, for pc-linux-gnu (i686) using readline 5.1

innobackupex: Using mysql server version 5.0.83-community-log

innobackupex: Created backup directory /root

090823 17:37:51 innobackupex: Starting mysql with options: –unbuffered –user=root –socket=/home/mysql/mysql.sock

090823 17:37:51 innobackupex: Connected to database with mysql child process (pid=28803)

090823 17:37:55 innobackupex: Connection to database server closed

090823 17:37:55 innobackupex: Starting ibbackup with command:

xtrabackup –defaults-file=/home/mysql/my.cnf –backup –suspend-at-end

–log-stream –target-dir=./

innobackupex: Waiting for ibbackup (pi