ITPub博客

首页 > Linux操作系统 > Linux操作系统 > innobackupex备份mysql

innobackupex备份mysql

原创 Linux操作系统 作者:lixiaxin200319 时间:2012-06-21 19:51:03 0 删除 编辑
一、问题记录
问题:innobackupex提示找不到mysql命令
原因:没有在PATH中添加mysql中的bin目录。
处理:export PATH=/usr/local/mysql3307/bin:$PATH

错误:innobackupex: fatal error: no 'mysqld' group in MySQL options
innobackupex: fatal error: OR no 'datadir' option in group 'mysqld' in MySQL options
原因:没有把innobackupex的路径加入PATH变量中;没有在my.cnf文件中配置datadir。
处理:把innobackupex的路径添加到PATH中,export PATH=/usr/local/percona-xtrabackup-2.0.0
      在my.cnf文件中添加数据目路径,vi /usr/local/mysql3307/my.cnf,添加 datadir=/usr/local/mysql3307/data
      
     
二、完整备份

2.1、完整备份
 ./innobackupex --defaults-file=/usr/local/mysql3307/my.cnf --user=root --password=probiz  --socket=/usr/local/mysql3307/mysql.sock  /home/backup
 
2.2、恢复完整备份
innobackupex --apply-log --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf /home/backup/2012-06-20_14-46-40/
 
把对应的表空间文件、表定义文件、数据库定义文件(比如:db.opt  t2.frm  t2.ibd)复制到数据库中。
 


三、增量备份

3.1、备份

3.1.1、完整备份
 ./innobackupex --defaults-file=/usr/local/mysql3307/my.cnf --user=root --password=probiz  --socket=/usr/local/mysql3307/mysql.sock  /home/backup

3.1.2、增量备份一
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --incremental /home/backup --incremental-basedir=/home/backup/2012-06-20_15-01-58/ --defaults-file=/usr/local/mysql3307/my.cnf

3.1.3、增量备份二
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --incremental /home/backup --incremental-basedir=/home/backup/2012-06-20_15-13-26/ --defaults-file=/usr/local/mysql3307/my.cnf

输出信息:
innobackupex: Backup created in directory '/home/backup/2012-06-20_15-20-08'
innobackupex: MySQL binlog position: filename 'mysql-bin.000010', position 1232
120620 15:20:54  innobackupex: completed OK!

3.2、恢复备份

3.2.1、恢复完整备份
 innobackupex --apply-log --redo-only /home/backup/2012-06-19_20-01-58/ --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf
 
3.2.2 恢复第一个增量备份
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --apply-log --redo-only /home/backup/2012-06-19_20-01-58 --incremental-dir=/home/backup/2012-06-20_15-13-26

输出信息:
120620 15:33:46  innobackupex: completed OK!

3.2.3 恢复第二个增量备份
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --apply-log --redo-only /home/backup/2012-06-19_20-01-58 --incremental-dir=/home/backup/2012-06-20_15-20-08

输出信息:
120620 15:34:51  innobackupex: completed OK!

把恢复好的表空间表定义和数据库定义文件(比如:db.opt  t2.frm  t2.ibd)复制到对应数据库下。




四、使用数据流备份进行压缩

4.1 完整备份
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf /home/backup

innobackupex: Backup created in directory '/home/backup/2012-06-20_16-18-32'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 16:21:23  innobackupex: completed OK!

4.2使用数据流进行增量备份
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --incremental --incremental-lsn=1648444 --stream=xbstream ./ > incremantal.xbstream

innobackupex: Backup created in directory '/usr/local/percona-xtrabackup-2.0.0/bin'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 16:41:22  innobackupex: completed OK!


[root@localhost bin]# mv incremantal.xbstream /home/backup
[root@localhost bin]# cd /home/backup
[root@localhost backup]# xbstream -x < incremantal.xbstream

解压后得到文件
backup-my.cnf  ibdata2.delta  ibdata4.meta          performance_schema
db1            ibdata2.meta   ibdata5.delta         xtrabackup_binary
db2            ibdata3.delta  ibdata5.meta          xtrabackup_binlog_info
ibdata1.delta  ibdata3.meta   incremantal.xbstream  xtrabackup_checkpoints
ibdata1.meta   ibdata4.delta  mysql                 xtrabackup_logfile

完整备份不能进行流式备份。
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf /home/backup --stream=xbstream ./ > incremantal.xbstream
错误提示:innobackupex: Too many command line arguments 译文:太多的命令行参数

4.3 流备份方式,对备份进行压缩
innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --stream=tar ./ > out.tar

innobackupex: Backup created in directory '/home/backup/cc'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
120620 18:36:45  innobackupex: completed OK!


innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --stream=tar ./|gzip - > backup.tar.gz

innobackupex: Backup created in directory '/home/backup/cc'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
120620 18:40:15  innobackupex: completed OK!

innobackupex --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --stream=tar ./|bzip2 - > backup.tar.bz2


五、备份指定的数据库

#################################################################
--database和--databasese选项有问题无法按要求备份指定的数据库。
##################################################################

5.1 使用--database=db1过滤数据库(无法完成备份指定数据库的要求)。
innobackupex  --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --database=db1 /home/backup/bb/

innobackupex: Backup created in directory '/home/backup/bb/2012-06-20_17-33-38'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 17:36:30  innobackupex: completed OK!

我指定备份DB1但把DB1和DB2都备份了,但DB2只备份了表空间文件。

5.2 使用--database="db1"过滤数据库(无完成备份指定数据库的要求)。
innobackupex  --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --database="db1" /home/backup/bb/

innobackupex: Backup created in directory '/home/backup/bb/2012-06-20_17-43-14'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 17:46:07  innobackupex: completed OK!



5.3 使用--database="db1.*"过滤数据库(无法完成备份指定数据库的要求)。
 innobackupex  --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --database="db1.*" /home/backup/bb/

innobackupex: Backup created in directory '/home/backup/bb/2012-06-20_17-47-14'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 17:50:05  innobackupex: completed OK!

结果:db1和db2都备份了,但都只备份了表空间文件,没有备份表定义文件和数据库定义文件。


5.4 是用databases选项过滤数据库(无法完成备份指定数据库的要求)。
innobackupex  --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --databases=db1 /home/backup/bb/

innobackupex: Backup created in directory '/home/backup/bb/2012-06-20_17-56-21'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 17:58:56  innobackupex: completed OK!



innobackupex  --user=root --password=probiz --socket=/usr/local/mysql3307/mysql.sock --defaults-file=/usr/local/mysql3307/my.cnf --databases="db1.*" /home/backup/bb/
 
innobackupex: Backup created in directory '/home/backup/bb/2012-06-20_18-03-54'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120620 18:06:30  innobackupex: completed OK!

结果:db1和db2都备份了,但都只备份了表空间文件,没有备份表定义文件和数据库定义文件。


##########################################################################################

5.5 使用 --tables-file 选项备份
# vi database.txt
db1.t1

备注:这个地方要注意,如果使用db1.*会导致db1数据库什么文件都不备份。用--tables-file选项备份,不会备份db.opt文件。

innobackupex --defaults-file=/usr/local/mysql3307/my.cnf --user=root --password=probiz  --socket=/usr/local/mysql3307/mysql.sock  ./ --tables-file database.txt


部分输出信息:
[01] Copying ./db1/t1.ibd to /home/backup/2012-06-21_14-00-47/./db1/t1.ibd
[01]        ...done
[01] Skipping ./db2/t2.ibd

120621 14:03:03  innobackupex: Continuing after ibbackup has suspended
120621 14:03:03  innobackupex: Starting mysql with options:  --defaults-file='/usr/local/mysql3307/my.cnf' --password=xxxxxxxx --user='root' --socket='/usr/local/mysql3307/mysql.sock' --unbuffered --
120621 14:03:03  innobackupex: Connected to database with mysql child process (pid=4815)
>> log scanned up to (1648444)
120621 14:03:05  innobackupex: Starting to lock all tables...
>> log scanned up to (1648444)
>> log scanned up to (1648444)
120621 14:03:15  innobackupex: All tables locked and flushed to disk

120621 14:03:15  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/usr/local/mysql3307/data'
innobackupex: Backing up file '/usr/local/mysql3307/data/db1/t1.frm'
120621 14:03:15  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '1648444'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1648444)

xtrabackup: Transaction log of lsn (1648444) to (1648444) was copied.
120621 14:03:18  innobackupex: All tables unlocked
120621 14:03:18  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/home/backup/2012-06-21_14-00-47'
innobackupex: MySQL binlog position: filename 'mysql-bin.000011', position 107
120621 14:03:18  innobackupex: completed OK!




5.6 主从复制备份,在出服务器上执行。--slave-info在xtrabackup_slave_info文件中记录MASTER_LOG_FILE和MASTER_LOG_POS;--safe-slave-backup选项会先关闭从服务器的SQL线程,直到显示状态Slave_open_temp_tables是 为零。备份完后再启动重服务器的SQL线程。
innobackupex --defaults-file=/usr/local/mysql3307/my.cnf --user=root --password=probiz  --socket=/usr/local/mysql3308/mysql.sock /home/backup/20120621/ --slave-info --safe-slave-backup

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21582653/viewspace-733597/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-10

  • 博文量
    66
  • 访问量
    165090