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

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/

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网