[root@centos01 ~]# rpm -Uvh http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpmRetrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpmPreparing... (########################################### [100%] 1:percona-release (########################################### [100%]
[root@centos01 ~]# rpm -ql percona-release/etc/pki/rpm-gpg/etc/pki/rpm-gpg/RPM-GPG-KEY-percona/etc/yum.repos.d/etc/yum.repos.d/Percona.repo/usr/share/doc/percona-release-0.0/usr/share/doc/percona-release-0.0/RPM-GPG-KEY-perconaYou have new mail in /var/spool/mail/root
Xtrabackup是由percona开发的一个开源软件,此软件可以说是innodb热备工具ibbackup的一个开源替代品。这个软件是由2个部 分组成的:xtrabackup和innobackupex。Xtrabackup专门用于innodb引擎和 xtraDB引擎;而innobackupex是专门用于myisam和innodb引擎,及混合使用的引擎。
wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/binary/Linux/x86_64/percona-xtrabackup-2.0.0.tar.gz
http://pan.baidu.com/s/1dEOr75R #网盘资源percona-xtrabackup-2.0.0.tar.gz
[root@centos01 ~]# tar -xvf percona-xtrabackup-2.0.0.tar.gz
[root@centos01 ~]# cd percona-xtrabackup-2.0.0[root@centos01 percona-xtrabackup-2.0.0]# pwd/root/percona-xtrabackup-2.0.0[root@centos01 percona-xtrabackup-2.0.0]# lsbin share
[root@centos01 percona-xtrabackup-2.0.0]# cp bin/innobackupex /usr/bin/
[root@centos01 percona-xtrabackup-2.0.0]# cp bin/xtrabackup* /usr/bin/
[root@centos01 percona-xtrabackup-2.0.0]# which innobackupex /usr/bin/innobackupex
[root@centos01 dbdata]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=sx --stream=tar /data/dbdata/ 2>/data/dbdata/sx`date +%F`.log | gzip 1>/data/dbdata/sx`date +%F`.tar.gzYou have new mail in /var/spool/mail/root[root@centos01 dbdata]# ll -htotal 1.9M-rw-rw----. 1 mysql root 361 Apr 30 10:31 centos01.err-rw-r--r--. 1 root root 3.7K Apr 30 11:39 sx2016-04-30.log-rw-r--r--. 1 root root 1.9M Apr 30 11:39 sx2016-04-30.tar.gz-rw-r--r--. 1 root root 1.1K Apr 30 10:29 xtrabk.log[root@centos01 dbdata]# pwd/data/dbdata
说明:
--database=sx 单独对zztx数据库做备份 ,若是不添加此参数那就那就是对全库做备份
2>/data/dbdata/sx.log 输出信息写入日志中
1>/data/dbdata/sx.tar.gz 打包压缩存储到该文件中
脚本:
#!/bin/bash#backup.shecho "开始备份..."`date`log=sx`date +%y%m%d%H%M`.logstr=sx`date +%y%m%d%H%M`.tar.gzinnobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=sx --stream=tar /data/dbdata/ 2>/data/dbdata/$log | gzip 1>/data/dbdata/$strecho "备份完毕..."`date`
----------------------------------------------------------
[root@centos01 dbdata]# innobackupex --user=root --password=123456 --database=sx /data/dbdataInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.160430 12:34:53 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --160430 12:34:53 innobackupex: Connected to database with mysql child process (pid=26859)160430 12:34:59 innobackupex: Connection to database server closedIMPORTANT: 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.20, for Linux (x86_64) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.innobackupex: Created backup directory /data/dbdata/2016-04-30_12-34-59160430 12:34:59 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --160430 12:34:59 innobackupex: Connected to database with mysql child process (pid=26886)160430 12:35:01 innobackupex: Connection to database server closed160430 12:35:01 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/data/dbdata/2016-04-30_12-34-59innobackupex: Waiting for ibbackup (pid=26903) to suspendinnobackupex: Suspend file '/data/dbdata/2016-04-30_12-34-59/xtrabackup_suspended'xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: uses posix_fadvise().xtrabackup: cd to /alidata/server/mysql/dataxtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 12:35:01 InnoDB: Using Linux native AIO>> log scanned up to (24098851)[01] Copying ./ibdata1 to /data/dbdata/2016-04-30_12-34-59/ibdata1[01] ...done160430 12:35:05 innobackupex: Continuing after ibbackup has suspended160430 12:35:05 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --160430 12:35:05 innobackupex: Connected to database with mysql child process (pid=26919)>> log scanned up to (24098861)160430 12:35:07 innobackupex: Starting to lock all tables...>> log scanned up to (24098861)>> log scanned up to (24098861)160430 12:35:17 innobackupex: All tables locked and flushed to disk160430 12:35:17 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex: subdirectories of '/alidata/server/mysql/data'innobackupex: Backing up files '/alidata/server/mysql/data/sx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (109 files)160430 12:35:18 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex: Resuming ibbackupxtrabackup: The latest check point (for incremental): '24098861'xtrabackup: Stopping log copying thread...>> log scanned up to (24098861)xtrabackup: Transaction log of lsn (24098851) to (24098861) was copied.160430 12:35:20 innobackupex: All tables unlocked160430 12:35:20 innobackupex: Connection to database server closedinnobackupex: Backup created in directory '/data/dbdata/2016-04-30_12-34-59'innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 203 160430 12:35:20 innobackupex: completed OK!You have new mail in /var/spool/mail/root[root@centos01 dbdata]# ls2016-04-30_12-34-59 centos01.err sx2016-04-30.tar.gzbackup-my.cnf sx2016-04-30.log xtrabk.log[root@centos01 dbdata]# cd 2016-04-30_12-34-59/[root@centos01 2016-04-30_12-34-59]# lsbackup-my.cnf sx xtrabackup_binlog_info xtrabackup_logfileibdata1 xtrabackup_binary xtrabackup_checkpoints[root@centos01 2016-04-30_12-34-59]# ll -htotal 27M-rw-r--r--. 1 root root 310 Apr 30 12:34 backup-my.cnf-rw-r-----. 1 root root 26M Apr 30 12:35 ibdata1drwxr-xr-x. 2 root root 4.0K Apr 30 12:35 sx-rw-r--r--. 1 root root 13 Apr 30 12:35 xtrabackup_binary-rw-r--r--. 1 root root 23 Apr 30 12:35 xtrabackup_binlog_info-rw-r-----. 1 root root 79 Apr 30 12:35 xtrabackup_checkpoints-rw-r-----. 1 root root 2.5K Apr 30 12:35 xtrabackup_logfile
[root@centos01 2016-04-30_12-34-59]# cat xtrabackup_binlog_info mysql-bin.000014 203#mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
[root@centos01 2016-04-30_12-34-59]# cat xtrabackup_binary xtrabackup_55 ]# 备份中用到的xtrabackup的可执行文件
[root@centos01 2016-04-30_12-34-59]# cat backup-my.cnf #备份命令用到的配置选项信息# This MySQL options file was generated by innobackupex.# The MySQL server[mysqld]datadir=/data/dbdata/2016-04-30_12-34-59innodb_data_home_dir=/data/dbdata/2016-04-30_12-34-59innodb_log_files_in_group=2innodb_log_file_size=5242880innodb_fast_checksum=0innodb_page_size=16384innodb_log_block_size=512
另外还需注意:备份数据库的用户需要具有相应权限,如果要使用一个最小权限的用户进行备份,则可基于如下命令创建此类用户:
mysql> create user 'bkpuser'@'localhost' identified by"123456";Query OK, 0 rows affected (0.23 sec)
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;#如果报错,可以不写
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> flush privileges;Query OK, 0 rows affected (0.06 sec)
接下来做个实验:
mysql> create database ceshi;Query OK, 1 row affected (0.03 sec)mysql> USE ceshi;Database changedmysql> create table users (id int primary key auto_increment,name varchar(20) not null unique,password varchar(100) not null,address varchar(200))engine=MyISAM;Query OK, 0 rows affected (0.12 sec)
mysql> show create table users;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `password` varchar(100) NOT NULL, `address` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>
mysql> insert into users(id,name,password,address) values(1, -> 'zhang','1234',null),(2,'wang','4321','湖北武汉'),(3,'li','5678','北京海 淀');Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> create table articles (id int primary key auto_increment,content longtext not null)ENGINE=InnoDB;Query OK, 0 rows affected (0.14 sec)
mysql> insert into articles(id,content) values(11,'hahahaha'),(12,'xixixi'),(13,'aiaiaiaiaiai'),(14,'hohohoho');Query OK, 4 rows affected (0.17 sec)Records: 4 Duplicates: 0 Warnings: 0
#++++++++++++++++++++++全库备份于恢复过程=============================
[root@centos01 dbdata]# mkdir -p /backup/{full_data,dk_data,zl_data}
[root@centos01 dbdata]# tree /backup//backup/├── dk_data #单库备份目录├── full_data #全库备份目录└── zl_data #增量备份目录3 directories, 0 files
[root@centos01 /]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /backup/full_data/InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.160430 14:21:10 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 14:21:10 innobackupex: Connected to database with mysql child process (pid=28554)160430 14:21:16 innobackupex: Connection to database server closedIMPORTANT: 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.20, for Linux (x86_64) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.innobackupex: Created backup directory /backup/full_data/2016-04-30_14-21-16160430 14:21:16 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 14:21:16 innobackupex: Connected to database with mysql child process (pid=28581)160430 14:21:18 innobackupex: Connection to database server closed160430 14:21:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/full_data/2016-04-30_14-21-16innobackupex: Waiting for ibbackup (pid=28589) to suspendinnobackupex: Suspend file '/backup/full_data/2016-04-30_14-21-16/xtrabackup_suspended'xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: uses posix_fadvise().xtrabackup: cd to /alidata/server/mysql/dataxtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 14:21:18 InnoDB: Using Linux native AIO>> log scanned up to (24102641)[01] Copying ./ibdata1 to /backup/full_data/2016-04-30_14-21-16/ibdata1[01] ...done160430 14:21:20 innobackupex: Continuing after ibbackup has suspended160430 14:21:20 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 14:21:20 innobackupex: Connected to database with mysql child process (pid=28603)160430 14:21:22 innobackupex: Starting to lock all tables...>> log scanned up to (24102641)>> log scanned up to (24102641)160430 14:21:32 innobackupex: All tables locked and flushed to disk160430 14:21:32 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex: subdirectories of '/alidata/server/mysql/data'innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.frm'innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.MYI'innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/db.opt'innobackupex: Backing up file '/alidata/server/mysql/data/ceshi/users.MYD'innobackupex: Backing up file '/alidata/server/mysql/data/image/t1.frm'innobackupex: Backing up file '/alidata/server/mysql/data/image/db.opt'innobackupex: Backing up file '/alidata/server/mysql/data/test2/articles.frm'innobackupex: Backing up file '/alidata/server/mysql/data/test2/db.opt'innobackupex: Backing up file '/alidata/server/mysql/data/test/t1.frm'innobackupex: Backing up files '/alidata/server/mysql/data/sx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (109 files)innobackupex: Backing up files '/alidata/server/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)innobackupex: Backing up files '/alidata/server/mysql/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)160430 14:21:33 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex: Resuming ibbackupxtrabackup: The latest check point (for incremental): '24102641'xtrabackup: Stopping log copying thread..>> log scanned up to (24102641)xtrabackup: Transaction log of lsn (24102641) to (24102641) was copied.160430 14:21:35 innobackupex: All tables unlocked160430 14:21:35 innobackupex: Connection to database server closedinnobackupex: Backup created in directory '/backup/full_data/2016-04-30_14-21-16'innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 2601 160430 14:21:35 innobackupex: completed OK!You have new mail in /var/spool/mail/root
[root@centos01 2016-04-30_14-24-51]# lsbackup-my.cnf ceshi ibdata1 image mysql performance_schema sx test test2 xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile[root@centos01 2016-04-30_14-24-51]# pwd/backup/full_data/2016-04-30_14-24-51
[root@centos01 data]# rm -rf *[root@centos01 data]# pwd/alidata/server/mysql/datamysql> show databases;+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)
[root@centos01 data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /backup/full_data/2016-04-30_14-24-51InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".160430 14:31:14 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/full_data/2016-04-30_14-24-51xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/full_data/2016-04-30_14-24-51xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(24102651)xtrabackup: Temporary instance for recovery is set as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152160430 14:31:14 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)160430 14:31:14 InnoDB: The InnoDB memory heap is disabled160430 14:31:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins160430 14:31:14 InnoDB: Compressed tables use zlib 1.2.3160430 14:31:14 InnoDB: Using Linux native AIO160430 14:31:14 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead160430 14:31:14 InnoDB: Initializing buffer pool, size = 100.0M160430 14:31:14 InnoDB: Completed initialization of buffer pool160430 14:31:14 InnoDB: highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 24102651160430 14:31:14 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Doing recovery: scanned up to log sequence number 24102661 (0 %)InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014160430 14:31:15 InnoDB: Waiting for the background threads to start160430 14:31:16 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24102661[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014xtrabackup: starting shutdown with innodb_fast_shutdown = 1160430 14:31:16 InnoDB: Starting shutdown...160430 14:31:20 InnoDB: Shutdown completed; log sequence number 24103876160430 14:31:20 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/full_data/2016-04-30_14-24-51for creating ib_logfile*xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/full_data/2016-04-30_14-24-51xtrabackup: This target seems to be already prepared.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.xtrabackup: Temporary instance for recovery is set as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 14:31:20 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)160430 14:31:20 InnoDB: The InnoDB memory heap is disabled160430 14:31:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins160430 14:31:20 InnoDB: Compressed tables use zlib 1.2.3160430 14:31:20 InnoDB: Using Linux native AIO160430 14:31:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead160430 14:31:20 InnoDB: Initializing buffer pool, size = 100.0M160430 14:31:20 InnoDB: Completed initialization of buffer pool160430 14:31:20 InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 5 MBInnoDB: Database physically writes the file full: wait...160430 14:31:20 InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 5 MBInnoDB: Database physically writes the file full: wait...160430 14:31:20 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!160430 14:31:20 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014160430 14:31:21 InnoDB: Waiting for the background threads to start160430 14:31:22 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24103948[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014xtrabackup: starting shutdown with innodb_fast_shutdown = 1160430 14:31:22 InnoDB: Starting shutdown...160430 14:31:26 InnoDB: Shutdown completed; log sequence number 24103948160430 14:31:26 innobackupex: completed OK!
[root@centos01 data]# ls #此时还是空的
[root@centos01 data]# ps -ef |grep mysqldmysql 21570 1 0 10:32 ? 00:00:41 /alidata/server/mysql/bin/mysqld --basedir=/alidata/server/mysql --datadir=/alidata/server/mysql/data --plugin-dir=/alidata/server/mysql/lib/plugin --user=mysql --log-error=/alidata/server/mysql/data/centos01.err --pid-file=/alidata/server/mysql/data/centos01.pid --socket=/tmp/mysql.sock --port=3306root 29433 26814 0 14:34 pts/0 00:00:00 grep mysqld[root@centos01 data]# kill -9 21570[root@centos01 data]# ps -ef |grep mysqldroot 29435 26814 0 14:34 pts/0 00:00:00 grep mysqld
[root@centos01 data]# cp -ap /backup/full_data/2016-04-30_14-24-51/* /alidata/server/mysql/data/
[root@centos01 data]# chown -R mysql.mysql /alidata/server/mysql/data/
[root@centos01 data]# service mysqld startStarting MySQL.. SUCCESS!
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || ceshi || image || mysql || performance_schema || sx || test || test2 |+--------------------+8 rows in set (0.03 sec)
mysql> use ceshi
Database changed
mysql> show tables;
+-----------------+
| Tables_in_ceshi |
+-----------------+
| users |
+-----------------+
1 row in set (0.06 sec)
mysql> select * from users;
+----+-------+----------+--------------+
| id | name | password | address |
+----+-------+----------+--------------+
| 1 | zhang | 1234 | NULL |
| 2 | wang | 4321 | 湖北武汉 |
| 3 | li | 5678 | 北京海淀 |
+----+-------+----------+--------------+
3 rows in set (0.03 sec)
mysql> use test2
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| articles |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from articles;
+----+--------------+
| id | content |
+----+--------------+
| 11 | hahahaha |
| 12 | xixixi |
| 13 | aiaiaiaiaiai |
| 14 | hohohoho |
+----+--------------+
4 rows in set (0.00 sec)
##==========================单库增量备份与恢复操作过程===========================##
单库备份跟全库用法是一样的,只不过单库在备份里,要指定要备份的数据库名,即:
--databases=LIST
注:如果是备份从库的话,需要添加参数:--slave-info,即:
[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=123456 --slave-info--databases=test2 /backup/dk_data
[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=123456 --databases=test2 /backup/dk_dataInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.160430 14:59:28 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --160430 14:59:28 innobackupex: Connected to database with mysql child process (pid=30036)160430 14:59:34 innobackupex: Connection to database server closedIMPORTANT: 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.20, for Linux (x86_64) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.innobackupex: Created backup directory /backup/dk_data/2016-04-30_14-59-34160430 14:59:34 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --160430 14:59:34 innobackupex: Connected to database with mysql child process (pid=30063)160430 14:59:36 innobackupex: Connection to database server closed160430 14:59:36 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/dk_data/2016-04-30_14-59-34innobackupex: Waiting for ibbackup (pid=30071) to suspendinnobackupex: Suspend file '/backup/dk_data/2016-04-30_14-59-34/xtrabackup_suspended'xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: uses posix_fadvise().xtrabackup: cd to /alidata/server/mysql/dataxtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 14:59:36 InnoDB: Using Linux native AIO>> log scanned up to (24104048)[01] Copying ./ibdata1 to /backup/dk_data/2016-04-30_14-59-34/ibdata1[01] ...done160430 14:59:38 innobackupex: Continuing after ibbackup has suspended160430 14:59:38 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='bkpuser' --unbuffered --160430 14:59:38 innobackupex: Connected to database with mysql child process (pid=30085)160430 14:59:40 innobackupex: Starting to lock all tables...>> log scanned up to (24104048)innobackupex: Error: mysql child process has died: ERROR 1044 (42000) at line 3: Access denied for user 'bkpuser'@'localhost' to database 'mysql' while waiting for reply to MySQL request: 'USE mysql;' at /usr/bin/innobackupex line 371.[root@centos01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --databases=test2 /backup/dk_dataInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.160430 15:00:16 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 15:00:16 innobackupex: Connected to database with mysql child process (pid=30107)160430 15:00:22 innobackupex: Connection to database server closedIMPORTANT: 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.20, for Linux (x86_64) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.innobackupex: Created backup directory /backup/dk_data/2016-04-30_15-00-22160430 15:00:22 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 15:00:22 innobackupex: Connected to database with mysql child process (pid=30134)160430 15:00:24 innobackupex: Connection to database server closed160430 15:00:24 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/backup/dk_data/2016-04-30_15-00-22innobackupex: Waiting for ibbackup (pid=30142) to suspendinnobackupex: Suspend file '/backup/dk_data/2016-04-30_15-00-22/xtrabackup_suspended'xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: uses posix_fadvise().xtrabackup: cd to /alidata/server/mysql/dataxtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 15:00:24 InnoDB: Using Linux native AIO>> log scanned up to (24104048)[01] Copying ./ibdata1 to /backup/dk_data/2016-04-30_15-00-22/ibdata1[01] ...done160430 15:00:26 innobackupex: Continuing after ibbackup has suspended160430 15:00:26 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --160430 15:00:26 innobackupex: Connected to database with mysql child process (pid=30156)160430 15:00:28 innobackupex: Starting to lock all tables...>> log scanned up to (24104048)>> log scanned up to (24104048)160430 15:00:38 innobackupex: All tables locked and flushed to disk160430 15:00:38 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex: subdirectories of '/alidata/server/mysql/data'innobackupex: Backing up file '/alidata/server/mysql/data/test2/articles.frm'innobackupex: Backing up file '/alidata/server/mysql/data/test2/db.opt'160430 15:00:38 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex: Resuming ibbackupxtrabackup: The latest check point (for incremental): '24104048'xtrabackup: Stopping log copying thread..>> log scanned up to (24104048)xtrabackup: Transaction log of lsn (24104048) to (24104048) was copied.160430 15:00:41 innobackupex: All tables unlocked160430 15:00:41 innobackupex: Connection to database server closedinnobackupex: Backup created in directory '/backup/dk_data/2016-04-30_15-00-22'innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 203 160430 15:00:41 innobackupex: completed OK
[root@centos01 dk_data]# tree 2016-04-30_15-00-22/2016-04-30_15-00-22/├── backup-my.cnf├── ibdata1├── test2│ ├── articles.frm│ └── db.opt├── xtrabackup_binary├── xtrabackup_binlog_info├── xtrabackup_checkpoints└── xtrabackup_logfile1 directory, 8 files[root@centos01 dk_data]# pwd/backup/dk_data
mysql> drop database test2;Query OK, 1 row affected (0.16 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || ceshi || image || mysql || performance_schema || sx || test |+--------------------+7 rows in set (0.00 sec)
[root@centos01 data]# service mysqld stopShutting down MySQL.. SUCCESS!
2)恢复日志文件apply-log
[root@centos01 server]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /backup/dk_data/2016-04-30_15-47-05InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".160430 18:35:31 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/dk_data/2016-04-30_15-47-05xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/dk_data/2016-04-30_15-47-05xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(24103978)xtrabackup: Temporary instance for recovery is set as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152160430 18:35:31 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)160430 18:35:31 InnoDB: The InnoDB memory heap is disabled160430 18:35:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins160430 18:35:31 InnoDB: Compressed tables use zlib 1.2.3160430 18:35:31 InnoDB: Using Linux native AIO160430 18:35:31 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead160430 18:35:31 InnoDB: Initializing buffer pool, size = 100.0M160430 18:35:31 InnoDB: Completed initialization of buffer pool160430 18:35:31 InnoDB: highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 24103978160430 18:35:31 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Doing recovery: scanned up to log sequence number 24103988 (0 %)InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014160430 18:35:31 InnoDB: Waiting for the background threads to start160430 18:35:32 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24103988[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014xtrabackup: starting shutdown with innodb_fast_shutdown = 1160430 18:35:32 InnoDB: Starting shutdown...160430 18:35:36 InnoDB: Shutdown completed; log sequence number 24103988160430 18:35:36 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/etc/my.cnf" --prepare --target-dir=/backup/dk_data/2016-04-30_15-47-05for creating ib_logfile*xtrabackup_55 version 2.0.0 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/dk_data/2016-04-30_15-47-05xtrabackup: This target seems to be already prepared.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.xtrabackup: Temporary instance for recovery is set as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 5242880160430 18:35:36 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)160430 18:35:36 InnoDB: The InnoDB memory heap is disabled160430 18:35:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins160430 18:35:36 InnoDB: Compressed tables use zlib 1.2.3160430 18:35:36 InnoDB: Using Linux native AIO160430 18:35:36 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead160430 18:35:36 InnoDB: Initializing buffer pool, size = 100.0M160430 18:35:36 InnoDB: Completed initialization of buffer pool160430 18:35:36 InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 5 MBInnoDB: Database physically writes the file full: wait...160430 18:35:37 InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 5 MBInnoDB: Database physically writes the file full: wait...160430 18:35:37 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!160430 18:35:37 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014160430 18:35:37 InnoDB: Waiting for the background threads to start160430 18:35:38 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 24104460[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 2601, file name ./mysql-bin.000014xtrabackup: starting shutdown with innodb_fast_shutdown = 1160430 18:35:38 InnoDB: Starting shutdown...160430 18:35:42 InnoDB: Shutdown completed; log sequence number 24104460160430 18:35:42 innobackupex: completed OK!
3)把备份文件拷贝至原数据目录下
[root@centos01 data]# cp -ap /backup/dk_data/2016-04-30_15-47-05/test2/ /alidata/server/mysql/data/
[root@centos01 data]# chown -R mysql.mysql /alidata/server/mysql/data/test2
[root@centos01 data]# service mysqld restart ERROR! MySQL server PID file could not be found!Starting MySQL... SUCCESS!
mysql> show databases like '%test2';+-------------------+| Database (%test2) |+-------------------+| test2 |+-------------------+1 row in set (0.01 sec)
直接从备份中拷贝被drop表的相关文件来恢复是不行的,即使文件存在,也会报表不存在;
mysql> select count(*) from articles;ERROR 1146 (42S02): Table 'test2.articles' doesn't exist
[root@centos01 data]# ls /alidata/server/mysql/data/test2/
articles.frm db.opt
原标题:linux Xtrabackup安装及使用方法
关键词:linux