四种常用的mysql备份工具，各有缺点：
0 tar缺点：不支持热备份，要关闭mysqld服务器再进行备份。恢复时也必须关闭mysqld。1 mysqldump缺点：MySQL自己提供的mysqldump是把数据转换为SQL语句，这种方式的效率比较低，备份和还原的速度都很慢，而且在dump过程中为了保证数据一致性，任何数据插入和更新操作都会被挂起。 2 ibbackup缺点：付费。3 LVM缺点：这种方法是利用的逻辑卷的 镜像 功能对整个分区进行在线备份，这种备份数据量大，而且备份性能低下。
4 Percona的XtraBackup。XtraBackup开源，热备份，不锁表，支持innodb，支持部分数据的备份（比如只备份某数据库中的某个表），高性能，备份时对系统的负载影响较小。通过tar4ibd 可直接生成压缩之后的备份文件，支持增量备份！有时间点的概念，可与mysql binary log配合。
官网的详解~
简明的步骤~
步骤 1. Master:/etc/my.cnf 加上 datadir=/~~~~~~ server-id log-bin 2. Slave:/etc/my.cnf server-id=2 datadir=/var/lib/mysql
3. Master:在master数据库设置用来同步的slave用户权限
GRANT REPLICATION SLAVE ON *.* TO '<slave_username>'@'<slave_ip>' IDENTIFIED BY '<slave_password>';
4. Master:导出数据到slave 采用xtrabackup来备份mysql，好处是在master的锁表时间很短，在实际的生产环境也可以使用，并且xtrabackup会自动记录同步日志文件的位置。
innobackupex-1.5.1 --stream=tar /tmp/ | ssh <slave_host> "mkdir /tmp/db; tar xfi - -C /tmp/db/" 这个步骤会把master的数据包括表结构整个导出并压缩复制给slave，同时解压到slave的/tmp/db目录下。
5. Slave:导入数据到slave innobackupex-1.5.1 --apply-log /tmp/db innobackupex-1.5.1 --copy-back /tmp/db chown -R mysql.mysql /var/lib/mysql/* 6. Slave:开始同步数据
查看/var/lib/mysql/xtrabackup_binlog_info，获得日志文件以及position。
CHANGE MASTER TO MASTER_HOST='<master_host>', MASTER_USER='<slave_username>', MASTER_PASSWORD='<slave_password>', MASTER_LOG_FILE='<see xtrabackup_binlog_info>', MASTER_LOG_POS=<see xtrabackup_binlog_info>;
START SLAVE; |
详细的说明~
1. 其中最大的不足在于mysqldump备份数据库，会锁定所有的表，无法写入，
2. 如果数据库比较大，就是一件很悲剧的事情。
3.
4. 今天介绍一下使用 percona 的xtrabackup 快速且无锁表地建立mysql主从体系。
5.
6. 在mysql 中创建用户的步骤就都略过了。
7.
8. 安装xtrabackup:
9.
10.rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
11.这个是64位的。
12.32位的地址是：
13.http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
14.
15.如果装了这个套装之后却找不到innobackupex命令。。。就。。。：
16.http://www.percona.com/software/percona-xtrabackup/downloads/
17.
18.
19.
20.mkdir /data/backup -p
21.确保在my.cnf中存在[mysqld]
22.并且在[mysqld]后面存在 datadir = ....
23.
24.[root@localhost ~]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf /data/backup
25.
26.
27.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
28.and Percona Inc 2009-2012. All Rights Reserved.
29.
30.This software is published under
31.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
32.
33.120419 10:46:26 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
34.120419 10:46:26 innobackupex: Connected to database with mysql child process (pid=4394)
35.120419 10:46:32 innobackupex: Connection to database server closed
36.IMPORTANT: Please check that the backup run completes successfully.
37. At the end of a successful backup run innobackupex
38. prints "completed OK!".
39.
40.innobackupex: Using mysql Ver 14.14 Distrib 5.1.58, for redhat-linux-gnu (i686) using readline 5.1
41.innobackupex: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
42.
43.innobackupex: Created backup directory /data/backup/2012-04-19_10-46-32
44.120419 10:46:32 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
45.120419 10:46:32 innobackupex: Connected to database with mysql child process (pid=4418)
46.120419 10:46:34 innobackupex: Connection to database server closed
47.
48.120419 10:46:34 innobackupex: Starting ibbackup with command: xtrabackup_51 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/data/backup/2012-04-19_10-46-32
49.innobackupex: Waiting for ibbackup (pid=4426) to suspend
50.innobackupex: Suspend file '/data/backup/2012-04-19_10-46-32/xtrabackup_suspended'
51.
52.xtrabackup_51 version 2.0.0 for MySQL server 5.1.59 pc-linux-gnu (i686) (revision id: 417)
53.xtrabackup: uses posix_fadvise().
54.xtrabackup: cd to /var/lib/mysql
55.xtrabackup: Target instance is assumed as followings.
56.xtrabackup: innodb_data_home_dir = ./
57.xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
58.xtrabackup: innodb_log_group_home_dir = ./
59.xtrabackup: innodb_log_files_in_group = 2
60.xtrabackup: innodb_log_file_size = 5242880
61.>> log scanned up to (0 9651018)
62.[01] Copying ./ibdata1 to /data/backup/2012-04-19_10-46-32/ibdata1
63.[01] ...done
64.
65.120419 10:46:38 innobackupex: Continuing after ibbackup has suspended
66.120419 10:46:38 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
67.120419 10:46:38 innobackupex: Connected to database with mysql child process (pid=4434)
68.>> log scanned up to (0 9651018)
69.120419 10:46:40 innobackupex: Starting to lock all tables...
70.>> log scanned up to (0 9651018)
71.>> log scanned up to (0 9651018)
72.120419 10:46:50 innobackupex: All tables locked and flushed to disk
73.
74.120419 10:46:50 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
75.innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
76.innobackupex: subdirectories of '/var/lib/mysql'
77.innobackupex: Backing up files '/var/lib/mysql/shipincon/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (14 files)
78.innobackupex: Backing up file '/var/lib/mysql/test/t.MYI'
79.innobackupex: Backing up file '/var/lib/mysql/test/t.MYD'
80.innobackupex: Backing up file '/var/lib/mysql/test/t.frm'
81.innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (54 files)
82.120419 10:46:50 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files
83.
84.innobackupex: Resuming ibbackup
85.
86.xtrabackup: The latest check point (for incremental): '0:9651018'
87.xtrabackup: Stopping log copying thread.
88..>> log scanned up to (0 9651018)
89.
90.xtrabackup: Transaction log of lsn (0 9651018) to (0 9651018) was copied.
91.120419 10:46:53 innobackupex: All tables unlocked
92.120419 10:46:53 innobackupex: Connection to database server closed
93.
94.innobackupex: Backup created in directory '/data/backup/2012-04-19_10-46-32'
95.innobackupex: MySQL binlog position: filename 'log_bin.000027', position 2973624
96.120419 10:46:53 innobackupex: completed OK!
97.最后输出 completed OK! 表示备份成功了。
98.可以看到在备份myisam类型表的时候，还是会锁表~~ innodb就不会锁表。哼。
99.
100. 备份好的文件保存在 /data/backup目录中，比如：
101. /data/backup/2012-04-19_10-46-32/
102. [root@localhost ~]# ls /data/backup/2012-04-19_10-46-32/
103. backup-my.cnf ibdata1 mysql shipincon test xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile
104.
105. 备份日志：
106. 刚刚备份好的数据文件，并不是直接可用的。大概是处于一种数据库挂掉的状态~~~，
107. 细节不讲了，要用日志对其进行恢复：
108.
109. [root@localhost ~]# innobackupex --apply-log /data/backup/2012-04-19_10-46-32/
110. 这个过程与数据库挂掉之后重启mysqld时的自动修复过程差不多。
111.
112. 把数据复制到从服务器：
113.
114. $ scp -r /data/backup/2012-04-19_10-46-32/ root@newslave:/data/
115. 关闭从服务器并切换数据：
116.
117. $ /etc/init.d/mysql stop
118. $ cd /data
119. $ mv mysql mysql_old
120. $ mv 2012-04-19_10-46-32 mysql
121. 修改my.cnf， 给它一个独一无二的server_id。
122. 一个比较好的办法是用服务器的IP地址，把其中的.去掉即可。
123.
124. 然后启动mysqld:
125.
126. $ /etc/init.d/mysql start
127. 最后change master。
128. 与mysqldump备份的步骤比起来，这次我们没有flush tables with read lock，
129. 也没有show master status来获取日志文件名和座标。
130. 因为xtrabackup完成备份之后，自动保存了这些信息。
131.
132. $ cat /data/mysql/xtrabackup_binlog_info
133.
134. log_bin.000027 2973624
135. mysql> CHANGE master to-> master_user=’rep’,-> master_password=’rep’,
136. -> master_host=’10.20.30.40′,
137.
138. -> master_log_file=’log_bin.000027′,
139.
140. -> master_log_pos= 2973624;
141. 然后 start slave 即可。