mysql的备份方式，目前我想到的有五种，有可能还有 1，mysqldump方式，加上具体参数名（单库，多库，触发器，存储过程，表结构，字符集，single-transaction，等等） 2，mysqlhotcopy 只能备份myisam数据表备份，速度相当快，因为是文件拷贝，可能瞬间被锁表
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 (pid=28809) to suspend
innobackupex: suspend file ‘/home/mysql/xtrabackup_suspended’
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/mysql
xtrabackup: target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10m:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 4194304
xtrabackup: stream mode.
>> log scanned up to (0 85364)
090823 17:37:57 innobackupex: continuing after ibbackup has suspended
innobackupex: starting to backup innodb tables and indexes
innobackupex: from original innodb data directory ‘/home/mysql’
innobackupex: backing up as tar stream ‘ibdata1′
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_djgameserver.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_djgameserverlog.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_repairhistory.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_serverinfo.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_serverinfolog.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_tlgameserver.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_tlgameserverlog.ibd’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_user.ibd’
090823 17:37:58 innobackupex: starting mysql with options: –unbuffered –user=root –socket=/home/mysql/mysql.sock
090823 17:37:58 innobackupex: connected to database with mysql child process (pid=28834)
>> log scanned up to (0 85364)
090823 17:38:02 innobackupex: starting to lock all tables…
>> log scanned up to (0 85364)
>> log scanned up to (0 88314)
>> log scanned up to (0 88415)
090823 17:38:20 innobackupex: all tables locked and flushed to disk
090823 17:38:20 innobackupex: starting to backup .frm, .mrg, .myd, .myi,
innobackupex: .trg, .trn, and .opt files in
innobackupex: subdirectories of ‘/home/mysql’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_djgameserver.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_djgameserverlog.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_repairhistory.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_serverinfo.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_serverinfolog.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_tlgameserver.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_tlgameserverlog.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/simsys_user.frm’
innobackupex: backing up file ‘/home/mysql/serverinfo/db.opt’
090823 17:38:20 innobackupex: finished backing up .frm, .mrg, .myd, .myi, .trg, .trn, and .opt files
innobackupex: resuming ibbackup
xtrabackup: the latest check point (for incremental): ‘0:88415′
>> log scanned up to (0 88415)
xtrabackup: transaction log of lsn (0 85364) to (0 88415) was copied.
090823 17:38:24 innobackupex: all tables unlocked
090823 17:38:24 innobackupex: connection to database server closed
innobackupex: backup created in directory ‘/root’
innobackupex: mysql binlog position: filename ”, position
innobackupex: mysql slave binlog position: master host ”, filename ”, position
090823 17:38:24 innobackupex: innobackup completed ok!
innobackupex: you must use -i (–ignore-zeros) option for extraction of the tar stream.
[@root.localhost.nova ~]#
[@root.localhost.nova ~]# ls -ltr
total 11752
drwxr-xr-x 2 oracle ftp 4096 apr 20 15:43 linux
-rw-r–r– 1 root root 17969 apr 20 15:43 linux.tgz
-rw-r–r– 1 root root 4698 jun 5 16:08 install.sh
-rw-r–r– 1 root root 3565 jun 18 11:21 hp-set.sh
-rw-r–r– 1 root root 98676 jul 27 13:33 install.log.syslog
-rw-r–r– 1 root root 68464 jul 27 13:33 install.log
-rw-r–r– 1 root root 1299 jul 27 13:33 anaconda-ks.cfg
-rw-rw—- 1 mysql mysql 10485760 aug 14 10:57 ibdata1
-rw-r–r– 1 root root 1230176 aug 23 17:12 xtrabackup-0.7-1.rhel4.x86_64.rpm
-rw-r–r– 1 root root 44998 aug 23 17:38 bak_mysql.tar.gz
[@root.localhost.nova ~]#
[@root.localhost.nova tmp]# ls -lr
.:
total 10336
-rw-r–r– 1 root root 259 aug 23 17:37 backup-my.cnf
-rw-r–r– 1 root root 44998 aug 23 17:43 bak_mysql.tar.gz
-rw-rw—- 1 mysql mysql 10485760 aug 23 17:28 ibdata1
-rw-r–r– 1 root root 0 aug 23 17:37 mysql-stderr
-rw-r–r– 1 root root 506 aug 23 17:38 mysql-stdout
drwxr-xr-x 2 root root 380 aug 23 17:43 serverinfo
-rw-r–r– 1 root root 1 aug 23 17:38 xtrabackup_binlog_info
-rw-r–r– 1 root root 60 aug 23 17:38 xtrabackup_checkpoints
-rw-r–r– 1 root root 5632 aug 23 17:38 xtrabackup_logfile
-rw-r–r– 1 root root 53 aug 23 17:38 xtrabackup_slave_info
./serverinfo:
total 1020
-rw-rw—- 1 mysql mysql 61 aug 12 19:59 db.opt
-rw-rw—- 1 mysql mysql 34626 aug 12 20:00 simsys_djgameserver.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_djgameserver.ibd
-rw-rw—- 1 mysql mysql 8760 aug 12 20:00 simsys_djgameserverlog.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_djgameserverlog.ibd
-rw-rw—- 1 mysql mysql 12944 aug 12 20:00 simsys_repairhistory.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_repairhistory.ibd
-rw-rw—- 1 mysql mysql 26072 aug 12 20:00 simsys_serverinfo.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_serverinfo.ibd
-rw-rw—- 1 mysql mysql 8760 aug 12 20:00 simsys_serverinfolog.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_serverinfolog.ibd
-rw-rw—- 1 mysql mysql 56550 aug 12 20:00 simsys_tlgameserver.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_tlgameserver.ibd
-rw-rw—- 1 mysql mysql 8760 aug 12 20:00 simsys_tlgameserverlog.frm
-rw-rw—- 1 mysql mysql 98304 aug 12 20:00 simsys_tlgameserverlog.ibd
-rw-rw—- 1 mysql mysql 8646 aug 12 20:00 simsys_user.frm
-rw-rw—- 1 mysql mysql 98304 aug 14 10:57 simsys_user.ibd
备份出来的文件是：bak_mysql.tar.gz，解压方法：tar zxvfi bak_mysql.tar.gz
恢复时候就采用复制文件，覆盖，然后mysqldump方式导入导出。
http://www.imdba.cn/2009/08/31/mysqlbakup-eg/