| 失效链接处理 |
|
生产环境究竟是使用mysqldump还是xtrabackup来备份与恢复数据库 PDF 下载
本站整理下载：
相关截图：
主要内容：
一、下载与安装
1、下载
wget
2、安装依赖库
如果是debian系列的话
apt-get install debhelper autotools-dev libaio-dev wget automake libtool bison libncurses-dev libz-dev cmake bzr
如果是redhat系列的话
yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel
3、解压
tar zxvf xtrabackup-1.6.7.tar.gz
4、进入目录
cd xtrabackup-1.6.7
5、复制
cd bin
cp * /usr/bin
然后就安装完成了，下面开始备份
其中，
innobackupex是我们要使用的备份工具；
xtrabackup是被封装在innobackupex之中的，innobackupex运行时需要调用它；
xtrabackup_51是xtrabackup运行时需要调用的工具；
tar4ibd是以tar流的形式产生备份时用来打包的工具。
6、对某个数据库进行全部备份的命令介绍
innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --database=test --stream=tar /tmp/data/ 2>/tmp/data/err.log|gzip 1>/tmp/data/test.tar.gz
说明：
--database=test 单独对test数据库做备份 ，若是不添加此参数那就那就是对全库做备份
2>/tmp/data/err.log 输出信息写入日志中
1>/tmp/data/test.tar.gz 打包压缩存储到该文件中
二、对数据库的全部备份与恢复
下面开始测试xtrabackup的全部备份
（1）先进入mysql里创建一个新的test数据库
root@client2:/tmp# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop database test;
Query OK, 3 rows affected (0.13 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table test (id int);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into test values(2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
（2）然后备份test的整个数据库
使用下面的backup.sh脚本
root@client2:/tmp# cat backup.sh
#!/bin/bash
user='root'
passwd='123456'
database=test
my_config='/etc/mysql/my.cnf'
log=$database-$(date +%Y%m%d%H%M).log
str=$database-$(date +%Y%m%d%H%M).tar.gz
backup_dir='/tmp/data'
echo "Start to backup at $(date +%Y%m%d%H%M)"
if [ ! -d "$backup_dir" ];then
mkdir $backup_dir
fi
innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip 1>$backup_dir/$str
if [ $? -eq 0 ];then
echo "Backup is finish! at $(date +%Y%m%d%H%M)"
exit 0
else
echo "Backup is Fail! at $(date +%Y%m%d%H%M)"
exit 1
fi
现在开始运行此脚本
root@client2:/tmp# sh backup.sh
Start to backup at 201303072101
Backup is finish! at 201303072102
然后到data里查看结果
root@client2:/tmp# cd data
root@client2:/tmp/data# ll
total 3272
drwxr-xr-x 2 root root 4096 Mar 7 21:01 ./
drwxrwxrwt 13 root root 4096 Mar 7 21:02 ../
-rw-r--r-- 1 root root 3780 Mar 7 21:02 test-201303072101.log
-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz
root@client2:/tmp/data# cat test-201303072101.log
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
130307 21:01:39 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
130307 21:01:39 innobackupex: Connected to database with mysql child process (pid=12441)
130307 21:01:45 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /tmp/data
130307 21:01:45 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
130307 21:01:45 innobackupex: Connected to database with mysql child process (pid=12471)
130307 21:01:47 innobackupex: Connection to database server closed
130307 21:01:47 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --backup --suspend-at-end --log-stream --target-dir=/tmp
innobackupex: Waiting for ibbackup (pid=12478) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended'
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/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 = 5242880
130307 21:01:47 InnoDB: Using Linux native AIO
xtrabackup: Stream mode.
>> log scanned up to (59605543)
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
