1 Cold
Backup & Restore
1.1 Copy Data file
Shutdown mysql or “flush tables with read lock;” then copy the data
file. It’s cause application can’t write data while backup.
1.2 Binary Logs
After cold backup restore we can use backup binary logs for point
in time recovery.
1.3 MyISAM Backup & Restore
Use mysqlhotcopy script to backup MyISAM data.
1.4 Logic Backup & Restore
Use mysqldump unload the data as sqls or flat file.
2 Hot Backup & Restore
2.1 InnoDB Online Backup & Restore
2.1.1
2.1.1.2 Backup
innobackup-1.5.1 --user=root --port=3309
--socket=/tmp/mysql.sock --use-memory=256 --compress=9
/opt/ms/mysql5140/my.cnf /opt/ms/innodb_hot_backup/backup
InnoDB Backup Utility v1.5.1; 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!".
innobackup: Using mysql Ver 14.14 Distrib
5.1.40, for unknown-linux-gnu (x86_64) using EditLine wrapper
innobackup: Using mysql server version 5.1.40-log
innobackup: Using InnoDB Hot Backup version 3.0.0
innobackup: Created backup directory
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07
100613 11:15:07 innobackup: Starting mysql
with options: --unbuffered --user=root --host=127.0.0.1 --port=3309
--socket=/tmp/mysql.sock
100613 11:15:07 innobackup: Connected to
database with mysql child process (pid=21947)
100613 11:15:11 innobackup: Connection to
database server closed
100613 11:15:11 innobackup: Starting ibbackup
with command: ibbackup --suspend-at-end --compress 9 --use-memory
256 /opt/ms/mysql5140/my.cnf
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07/backup-my.cnf
innobackup: Waiting for ibbackup (pid=21953) to suspend
innobackup: Suspend file
'/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07/ibbackup_suspended'
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase
Oy
License A19611 is granted to JunHe (he001@126.com)
(--apply-log works in any computer regardless of the
hostname)
Licensed for use in a computer whose hostname is 'adm76'
Expires 2010-8-1 (year-month-day) at 00:00
See http://www.innodb.com
for further information
Type ibbackup --license for detailed license terms, --help for
help
Contents of /opt/ms/mysql5140/my.cnf:
innodb_data_home_dir got value /opt/ms/mysql5140/Idata
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /opt/ms/mysql5140/var
innodb_log_group_home_dir got value
/opt/ms/mysql5140/Idata/ilog
innodb_log_files_in_group got value 3
innodb_log_file_size got value 268435456
Contents of
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07/backup-my.cnf:
innodb_data_home_dir got value
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07
innodb_log_group_home_dir got value
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07
innodb_log_files_in_group got value 3
innodb_log_file_size got value 268435456
ibbackup: Found checkpoint at lsn 10 1927124053
ibbackup: Starting log scan from lsn 10 1927123968
100613 11:15:11 ibbackup: Copying log...
100613 11:15:26 ibbackup: Switching to log
file 0, lsn 10 2147147776
100613 11:15:44 ibbackup: Log copied, lsn 10
2406256592
ibbackup: We wait 1 second before starting copying the data
files...
100613 11:15:45 ibbackup: Copying
/opt/ms/mysql5140/Idata/ibdata1
100613 11:15:50 ibbackup: Switching to log
file 1, lsn 10 2415581184
100613 11:15:53 ibbackup: Copying
/opt/ms/mysql5140/var/adm/t_camp_camp_ext_attribute.ibd
... ...
100613 11:15:57 ibbackup: Copying
/opt/ms/mysql5140/var/test/test.ibd
ibbackup: Progress in MB: 100 200 300 400 500 600 700 800 900
1000 1100 1200 1300
100613 11:19:06 ibbackup: Switching to log
file 2, lsn 10 2684014592
1400 1500 1600 1700 1800 1900 2000 2100 2200
2300 2400 2500 2600 2700
100613 11:22:18 ibbackup: Switching to log
file 0, lsn 10 2952448000
2800 2900 3000 3100 3200 3300 3400 3500 3600
3700 3800 3900 4000 4100
100613 11:25:28 ibbackup: Switching to log
file 1, lsn 10 3220881408
4200 4300 4400 4500 4600 4700 4800 4900 5000
5100 5200 5300 5400 5500 5600 5700
100613 11:28:34 ibbackup: Switching to log
file 2, lsn 10 3489314816
5800 5900 6000 6100 6200 6300 6400 6500 6600
6700 6800
100613 11:30:05 ibbackup: Copying
/opt/ms/mysql5140/var/test/tt.ibd
100613 11:30:05 ibbackup: Copying
/opt/ms/mysql5140/var/test/test2.ibd
ibbackup: Progress in MB: 100 200 300 400 500 600 700 800
100613 11:31:30 ibbackup: Copying
/opt/ms/mysql5140/var/report/t_rept_user_report.ibd
100613 11:31:30 ibbackup: Copying
/opt/ms/mysql5140/var/report/t_rept_report_log.ibd
100613 11:31:30 ibbackup: Copying
/opt/ms/mysql5140/var/report/t_sys_dict_code.ibd
100613 11:31:31 ibbackup: Copying
/opt/ms/mysql5140/var/report/t_sys_dict_code_i18.ibd
ibbackup: You had specified the option --suspend-at-end
100613 11:31:31 ibbackup: Suspending the
backup procedure to wait
ibbackup: until you delete the marker file
/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07/ibbackup_suspended
100613 11:31:31 innobackup: Continuing after
ibbackup has suspended
100613 11:31:31 innobackup: Starting mysql
with options: --unbuffered --user=root --host=127.0.0.1 --port=3309
--socket=/tmp/mysql.sock
100613 11:31:31 innobackup: Connected to
database with mysql child process (pid=22019)
100613 11:31:35 innobackup: Starting to lock
all tables...
100613 11:31:40 ibbackup: Switching to log
file 0, lsn 10 3757748224
100613 11:31:51 innobackup: All tables locked
and flushed to disk
100613 11:31:51 innobackup: Starting to backup
.frm, .MRG, .MYD, .MYI,
innobackup: .TRG, .TRN, and .opt files in
innobackup: subdirectories of '/opt/ms/mysql5140/var'
innobackup: Backing up files
'/opt/ms/mysql5140/var/adm/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52
files)
innobackup: Backing up file
'/opt/ms/mysql5140/var/test/test.frm'
innobackup: Backing up file
'/opt/ms/mysql5140/var/test/test2.frm'
innobackup: Backing up file
'/opt/ms/mysql5140/var/test/tt.frm'
innobackup: Backing up files
'/opt/ms/mysql5140/var/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (66
files)
innobackup: Backing up files
'/opt/ms/mysql5140/var/report/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (71
files)
100613 11:31:52 innobackup: Finished backing
up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files
innobackup: Resuming ibbackup
100613 11:31:53 ibbackup: Suspension ends.
Continuing the backup procedure.
100613 11:31:53 ibbackup: Copying of the last
data file is close to ending...
ibbackup: We still once copy the latest flushed log to
ibbackup_logfile.
100613 11:31:53 ibbackup: Copying
/opt/ms/mysql5140/var/mysql/ibbackup_binlog_marker.ibd
ibbackup: A copied database page was modified at 10
3701860788
ibbackup: Scanned log up to lsn 10 3765951701
ibbackup: Was able to parse the log up to lsn 10 3765951701
ibbackup: Maximum page number for a log record 436416
ibbackup: Compressed 7795 MB of data files to 644 MB
(compression 91%).
100613 11:31:53 ibbackup: Full backup
completed!
100613 11:31:57 innobackup: All tables
unlocked
100613 11:31:57 innobackup: Connection to
database server closed
innobackup: Backup created in directory
'/opt/ms/innodb_hot_backup/backup/2010-06-13_11-15-07'
innobackup: MySQL binlog position: filename 'mysql-bin.000067',
position 96968309
100613 11:31:57 innobackup: innobackup
completed OK!
2.1.1.3 Restore
1. Restore DB
innobackup-1.5.1 --apply-log
/opt/ms/innodb_hot_backup/backup
innobackup-1.5.1 --copy-back
/opt/ms/innodb_hot_backup/backup
2. Recovery DB
Using binary logs to recovery DB
mysqlbinlog --start-position=3335 mysql-bin.000003 |mysql -uroot
-P3309 test
2.1.1.4 Result
1. Backup smooth, impact DB lowest
2. After restore DB, can use binary logs to recovery DB.
3. Can recovery to point time.
2.1.1.5 Positive
1. Commercial edition, can get support when encounter err
2.1.1.6 Negative
4. The backup data need manual compress, occupy more storage
5. Need more CPU, will impact DB load while backup
6. After backup innodb data, need to backup .frm more
2.1.2 XtraBackup (Open Source, Free)
2.1.2.1 Introduce
Distinguish: Innobackupex-1.5.1 has backed up all database files
【Including form definition file, data file, index file 】 Xtrabackup
only backs up the data file of InnoDB form --Tablename.ibd file, so
we need to back up the database structure of the form definition
file or dump alone, it otherwise not appear can delete by the
situations of the catalogues data by mistake on ...uh ...It
uncertain to on it is newly
After install XtraBackup, you will see below tools:
/usr/bin/innobackupex-1.5.1
/usr/bin/tar4ibd
/usr/bin/xtrabackup
xtrabackup - binary, which allows to copy only InnoDB/XtraDB
tablespaces
innobackupex - script, based on innobackup Perl script,
distributed by Oracle/InnoDB under GPL license. Script was modified
to work with xtrabackup binary and accept new parameters. Script
provides functionality to backup whole MySQL database instance with
MyISAM, InnoDB, XtraDB tables.
2.1.2.3 Install
rpm -Uvh MySQL-client-community-5.0.91-1.rhel4.x86_64.rpm
rpm -Uvh xtrabackup-1.2-13.rhel4.x86_64.rpm
2.1.2.4 Backup[e1]
1. Using innobackupex-1.5.1 backup all databases.
ms@adm76:~/bakcup>
innobackupex-1.5.1 --user=root --stream=tar
/opt/ms/soft/xtrabackup/backup/ --slave-info --use-memory=256M
--host=127.0.0.1 --port=4309 --socket=/tmp/mysqlbk.sock
--defaults-file=/opt/ms/mysqlbk/my.cnf --no-lock | gzip
>
/opt/ms/soft/xtrabackup/backup/bak_mysql_20100628_1750.tar.gz
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009
Innobase Oy.
All Rights Reserved.
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /opt/ms/mysql5140/Idata
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /opt/ms/mysql5140/Idata/ilog
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Stream mode.
>> log scanned up to
(26586735764)
100612 15:32:33 innobackupex-1.5.1: Continuing
after ibbackup has suspended
innobackupex-1.5.1: Starting to backup InnoDB tables and
indexes
innobackupex-1.5.1: from original InnoDB data directory
'/opt/ms/mysql5140/Idata'
innobackupex-1.5.1: Backing up as tar stream 'ibdata1'
innobackupex-1.5.1: Backing up files
'/opt/ms/mysql5140/var/adm/*.ibd' (42 files)
innobackupex-1.5.1: Backing up file
'/opt/ms/mysql5140/var/test/test.ibd'
>> log scanned up to
(26587796751)
... ...
100612 15:34:41 innobackupex-1.5.1: Starting
to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of
'/opt/ms/mysql5140/var'
innobackupex-1.5.1: Backing up files
'/opt/ms/mysql5140/var/adm/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(52 files)
innobackupex-1.5.1: Backing up file
'/opt/ms/mysql5140/var/test/test.frm'
innobackupex-1.5.1: Backing up file
'/opt/ms/mysql5140/var/test/tt.frm'
innobackupex-1.5.1: Backing up files
'/opt/ms/mysql5140/var/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(65 files)
innobackupex-1.5.1: Backing up files
'/opt/ms/mysql5140/var/report/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(71 files)
100612 15:34:42 innobackupex-1.5.1: Finished
backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt
files
innobackupex-1.5.1: Backup created in directory
'/opt/ms/backup'
innobackupex-1.5.1: MySQL binlog position: filename
'mysql-bin.000037', position 140471897
innobackupex-1.5.1: MySQL slave binlog position: master host
'10.0.9.79', filename 'mysql-bin.000040', position 162029822
innobackupex-1.5.1: You must use -i (--ignore-zeros) option for
extraction of the tar stream.
100612 15:34:46 innobackupex-1.5.1: completed
OK!
2. Using xtrabackup backup InnoDB data.
2.1.2.5 Restore
1. Uncompress tar.gz file
tar -ixvf bak_mysql_20100628_1750.tar.gz
2. Restore DB
innobackupex-1.5.1 --apply-log
/opt/ms/soft/xtrabackup/backup
innobackupex-1.5.1 --copy-back
/opt/ms/soft/xtrabackup/backup
or
innobackupex-1.5.1 --socket=/tmp/mysql9999.sock
--defaults-file=/opt/mysql/my.cnf --no-lock --apply-log /opt/slave
innobackupex-1.5.1 --user=adm
--password=adm --host=localhost
--port=3306 --socket=/tmp/mysql9999.sock
--defaults-file=/opt/mysql/my.cnf --copy-back /opt/slave
3. Recovery DB
Using binary logs recovery DB
mysqlbinlog --start-position=3335 mysql-bin.000003 |mysql -uroot
-P3309 test
2.1.2.6 Result
1. Backup smooth, impact DB lowest
2. After restore DB, can use binary logs to recovery DB.
3. Can recovery to point time.
2.1.2.7 Positive
1. Can compress backup file while backup, so it’s occupy fewer
storage
2. Support increment backup
3. Need lower CPU
2.1.2.8 Negative
1. After backup innodb data, need to backup .frm more
2. When encounter err need buy support or research the code.
3.2 Install
rpm -Uvh MySQL-client-community-5.0.91-1.rhel4.x86_64.rpm
rpm -Uvh xtrabackup-1.2-13.rhel4.x86_64.rpm
本手册所有支持软件和脚本均符合要求，并在相关环境下测试通过。
adm221:/ # lsb_release -a
LSB
Version: core-2.0-noarch:core-3.0-noarch:core-2.0-x86_64:core-3.0-x86_64:desktop-3.1-amd64:desktop-3.1-noarch:graphics-2.0-amd64:graphics-2.0-noarch:graphics-3.1-amd64:graphics-3.1-noarch
Distributor ID: SUSE LINUX
Description: SUSE Linux Enterprise Server 10 (x86_64)
Release: 10
Codename: n/a
adm221:/ # SPident
CONCLUSION: System is up-to-date!
found SLE-10-x86_64-SP2
4 Adm Full backup script installation settings
Call fullbackupadm.sh through crontab.
Full backup sh file name:
In the master-slave environment at the slave node for
backup.
4.1 Backup prepare
4.1.1 Mysql：
/opt/ericsson/3pp/mysql/mysql5145/my.cnf
port=3306
socket= /opt/ericsson/3pp/mysql/mysql5145/mysql.sock
log_bin=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-bin
log_bin_index=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-bin.index
relay_log=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-relay-bin
relay_log_index=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-relay-bin.index
basedir=/opt/ericsson/3pp/mysql/mysql5145
datadir=/opt/ericsson/3pp/mysql/mysql5145/var
innodb_data_home_dir=/opt/ericsson/3pp/mysql/data/poland/adm/Idata
innodb_log_group_home_dir=/opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog
4.1.2 mysql db user
GRANT ALL PRIVILEGES ON adm.* TO adm@`%` IDENTIFIED BY 'adm';
GRANT ALL PRIVILEGES ON adm.* TO adm@`localhost` IDENTIFIED BY
'adm';
4.1.3 os user
Use adm os user backup online data.backup mysql and adm database by
crontab.
4.2 Setting fullbackupadm.sh variable
Backup command files stored in BACKUP_SHDIR
variable.
The default path is"/opt/ericsson/software/xtrabackup"
Backup files stored in BACKUP_DIR variable. Generate a directory
by date every day.
The default path is"/opt/ericsson/software/xtrabackup
/adm/${BACKUP_DATE}"
Example: /opt/ericsson/software/xtrabackup/adm/20100907/
4.3 Setting fullbackupadmdetail.sh variable
Backup files stored in BACKUP_DIR variable. Generate a directory by
date every day.
The default path is"/opt/ericsson/software/xtrabackup
/adm/${BACKUP_DATE}"
Example: /opt/ericsson/software/xtrabackup/adm/20100907/
configuration_file：Current mysql server configuration file
location.
The default path is /opt/ericsson/3pp/mysql/mysql5145/my.cnf
DBUSER ： user name of the backup mysql data
The default name is adm。
DBPWD:password of the backup mysql data
The default name is adm。
DBHOST：mysql server host
The default name is localhost
DBPORT：mysql server port
The default name is 3306
SOCKET：Localhost via UNIX socket area
The default path is /opt/ericsson/3pp/mysql/mysql5145/mysql.sock
If your environment with the default configuration is
inconsistent, can increase the symbolic links; or modify the
variable values
4.4 Backup log:
Record operating results of the backup.
Backup start time
back up every step of the operating instructions
and the end results of the implementation time of the backup
results
example:
backup up start... Tue Sep 7 15:20:46 CST 2010
drop exist
/opt/ericsson/software/xtrabackup/adm/20100907/backupadm_20100907.tar.gz
and /opt/ericsson/software/xtrabackup/adm/20100907/my.cnf
cp /opt/ericsson/3pp/mysql/mysql5145/my.cnf to
/opt/ericsson/software/xtrabackup/adm/20100907
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 innobackupex-1.5.1
prints "completed OK!".
innobackupex-1.5.1: Using mysql Ver 14.14
Distrib 5.1.45, for unknown-linux-gnu (x86_64)
using EditLine wrapper
innobackupex-1.5.1: Using mysql server version Copyright
2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
innobackupex-1.5.1: Created backup directory
/opt/ericsson/software/xtrabackup/adm/20100907
100907 15:20:46 innobackupex-1.5.1: Starting
mysql with options: --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf"
--password=adm --user=adm --host=localhost --port=3306 --unbuffered
--
100907 15:20:46 innobackupex-1.5.1: Connected
to database with mysql child process (pid=6217)
100907 15:20:50 innobackupex-1.5.1: Connection
to database server closed
100907 15:20:50 innobackupex-1.5.1: Starting
ibbackup with command: xtrabackup --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf" --backup
--suspend-at-end --log-stream --target-dir=./ --use-memory
=256M
innobackupex-1.5.1: Waiting for ibbackup (pid=6237) to
suspend
innobackupex-1.5.1: Suspend file
'/opt/ericsson/3pp/mysql/data/poland/adm/var/xtrabackup_suspended'
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to
/opt/ericsson/3pp/mysql/data/poland/adm/var
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir =
/opt/ericsson/3pp/mysql/data/poland/adm/Idata
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup: innodb_log_group_home_dir =
/opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Stream mode.
>> log scanned up to (1653754)
100907 15:20:52 innobackupex-1.5.1: Continuing
after ibbackup has suspended
innobackupex-1.5.1: Starting to backup InnoDB tables and
indexes
innobackupex-1.5.1: from original InnoDB data directory
'/opt/ericsson/3pp/mysql/data/poland/adm/Idata'
innobackupex-1.5.1: Backing up as tar stream 'ibdata1'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/adm/*.ibd' (40
files)
100907 15:20:54 innobackupex-1.5.1: Starting
mysql with options: --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf"
--password=adm --user=adm --host=localhost --port=3306 --unbuffered
--
100907 15:20:54 innobackupex-1.5.1: Connected
to database with mysql child process (pid=6347)
>> log scanned up to (1653754)
100907 15:20:58 innobackupex-1.5.1: Starting
to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of
'/opt/ericsson/3pp/mysql/data/poland/adm/var'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(65 files)
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/adm/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(53 files)
100907 15:20:59 innobackupex-1.5.1: Finished
backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt
files
innobackupex-1.5.1: Resuming ibbackup
xtrabackup: The latest check point (for incremental):
'1653754'
>> log scanned up to (1653754)
100907 15:20:52 innobackupex-1.5.1: Continuing
after ibbackup has suspended
innobackupex-1.5.1: Starting to backup InnoDB tables and
indexes
innobackupex-1.5.1: from original InnoDB data directory
'/opt/ericsson/3pp/mysql/data/poland/adm/Idata'
innobackupex-1.5.1: Backing up as tar stream 'ibdata1'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/adm/*.ibd' (40
files)
100907 15:20:54 innobackupex-1.5.1: Starting
mysql with options: --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf"
--password=adm --user=adm --host=localhost --port=3306 --unbuffered
--
100907 15:20:54 innobackupex-1.5.1: Connected
to database with mysql child process (pid=6347)
>> log scanned up to (1653754)
100907 15:20:58 innobackupex-1.5.1: Starting
to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of
'/opt/ericsson/3pp/mysql/data/poland/adm/var'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(65 files)
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/adm/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(53 files)
100907 15:20:59 innobackupex-1.5.1: Finished
backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt
files
innobackupex-1.5.1: Resuming ibbackup
xtrabackup: The latest check point (for incremental):
'1653754'
>> log scanned up to (1653754)
xtrabackup: Transaction log of lsn (1653754) to (1653754) was
copied.
100907 15:21:00 innobackupex-1.5.1: Connection
to database server closed
innobackupex-1.5.1: Backup created in directory
'/opt/ericsson/software/xtrabackup/adm/20100907'
innobackupex-1.5.1: You must use -i (--ignore-zeros) option for
extraction of the tar stream.
100907 15:21:00 innobackupex-1.5.1: completed
OK!
backup successed,create
/opt/ericsson/software/xtrabackup/adm/20100907/backupadm_20100907.tar.gz
back up end ... Tue Sep 7 15:21:00 CST
2010
5 Report Full backup script installation settings
Call fullbackuprept.sh through crontab.
Full backup sh file name:
5.1 Backup prepare
5.1.1 Mysql：
/opt/ericsson/3pp/mysql/mysql5145/my.cnf
port=3306
socket= /opt/ericsson/3pp/mysql/mysql5145/mysql.sock
log_bin=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-bin
log_bin_index=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-bin.index
relay_log=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-relay-bin
relay_log_index=/opt/ericsson/3pp/mysql/bin_log/poland/adm/mysql-relay-bin.index
basedir=/opt/ericsson/3pp/mysql/mysql5145
datadir=/opt/ericsson/3pp/mysql/data/poland/adm/var
innodb_data_home_dir=/opt/ericsson/3pp/mysql/data/poland/adm/Idata
innodb_log_group_home_dir=/opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog
5.1.2 mysql db user
GRANT ALL PRIVILEGES ON report.* TO report@`%` IDENTIFIED BY 'report';
GRANT ALL PRIVILEGES ON report.* TO report@`localhost` IDENTIFIED BY
'report';
5.1.3 os user
Use adm os user backup online data.backup mysql and adm database by
crontab.
5.2 Setting fullbackuprept.sh variable
Backup command files stored in BACKUP_SHDIR
variable.
The default path is" /opt/ericsson/software/xtrabackup"
Backup files stored in BACKUP_DIR variable. Generate a directory
by date every day.
The default path is"
/opt/ericsson/software/xtrabackup/report/${BACKUP_DATE}"
Example: /opt/ericsson/software/xtrabackup/report/20100907/
5.3 Setting fullbackupreptdetail.sh variable
Backup files stored in BACKUP_DIR variable. Generate a directory by
date every day. The default path BACKUP_DIR="/opt/ericsson/software/xtrabackup/report/${BACKUP_DATE}"
Example: /opt/ericsson/software/xtrabackup/adm/20100907/
In the master-slave environment at the slave node for
backup.
Use adm os user backup online data.
backup mysql and report database by crontab.
configuration_file：Current mysql server configuration file
location.
The default path is /opt/ericsson/3pp/mysql/mysql5145/my.cnf
DBUSER ： user name of the backup mysql data
The default name is report。
DBPWD:password of the backup mysql data
The default name report。
DBHOST：mysql server host
The default name is localhost
DBPORT：mysql server port
The default name is =3306
SOCKET：Localhost via UNIX socket area
The default path is /opt/ericsson/3pp/mysql/mysql5145/mysql.sock
If your environment with the default configuration is
inconsistent, can increase the symbolic links; or modify the
variable values
5.4 Backup log:
Record operating results of the backup.
Backup start time
back up every step of the operating instructions
and the end results of the implementation time of the backup
results
example:
backup up start... Tue Sep 7 15:49:21 CST 2010
cp /opt/ericsson/3pp/mysql/mysql5145/my.cnf to
/opt/ericsson/software/xtrabackup/report/20100907
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 innobackupex-1.5.1
prints "completed OK!".
innobackupex-1.5.1: Using mysql Ver 14.14
Distrib 5.1.45, for unknown-linux-gnu (x86_64)
using EditLine wrapper
innobackupex-1.5.1: Using mysql server version Copyright
2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
innobackupex-1.5.1: Created backup directory
/opt/ericsson/software/xtrabackup/report/20100907
100907 15:49:21 innobackupex-1.5.1: Starting
mysql with options: --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf"
--password=report --user=report --host=localhost --port=3306
--unbuffered --
100907 15:49:21 innobackupex-1.5.1: Connected
to database with mysql child process (pid=14632)
100907 15:49:25 innobackupex-1.5.1: Connection
to database server closed
100907 15:49:25 innobackupex-1.5.1: Starting
ibbackup with command: xtrabackup --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf" --backup
--suspend-at-end --log-stream --target-dir=./ --use-memory=256M
innobackupex-1.5.1: Waiting for ibbackup (pid=14651) to
suspend
innobackupex-1.5.1: Suspend file
'/opt/ericsson/3pp/mysql/data/poland/adm/var/xtrabackup_suspended'
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to
/opt/ericsson/3pp/mysql/data/poland/adm/var
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir =
/opt/ericsson/3pp/mysql/data/poland/adm/Idata
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend
xtrabackup: innodb_log_group_home_dir =
/opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Stream mode.
>> log scanned up to (1653754)
100907 15:49:27 innobackupex-1.5.1: Continuing
after ibbackup has suspended
innobackupex-1.5.1: Starting to backup InnoDB tables and
indexes
innobackupex-1.5.1: from original InnoDB data directory
'/opt/ericsson/3pp/mysql/data/poland/adm/Idata'
innobackupex-1.5.1: Backing up as tar stream 'ibdata1'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/report/*.ibd' (77
files)
100907 15:49:29 innobackupex-1.5.1: Starting
mysql with options: --defaults-file="/opt/ericsson/3pp/mysql/mysql5145/my.cnf"
--password=report --user=report --host=localhost --port=3306
--unbuffered --
100907 15:49:29 innobackupex-1.5.1: Connected
to database with mysql child process (pid=14832)
>> log scanned up to (1653754)
100907 15:49:33 innobackupex-1.5.1: Starting
to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of
'/opt/ericsson/3pp/mysql/data/poland/adm/var'
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(65 files)
innobackupex-1.5.1: Backing up files
'/opt/ericsson/3pp/mysql/data/poland/adm/var/report/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}'
(23 files)
100907 15:49:34 innobackupex-1.5.1: Finished
backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt
files
innobackupex-1.5.1: Resuming ibbackup
xtrabackup: The latest check point (for incremental):
'1653754'
>> log scanned up to (1653754)
xtrabackup: Transaction log of lsn (1653754) to (1653754) was
copied.
100907 15:49:34 innobackupex-1.5.1: Connection
to database server closed
innobackupex-1.5.1: Backup created in directory
'/opt/ericsson/software/xtrabackup/report/20100907'
innobackupex-1.5.1: You must use -i (--ignore-zeros) option for
extraction of the tar stream.
100907 15:49:34 innobackupex-1.5.1: completed
OK!
backup successed,create
/opt/ericsson/software/xtrabackup/report/20100907/backuprept_20100907.tar.gz
back up end ... Tue Sep 7 15:49:34 CST
2010
6 Increment backup script installation settings
In the master-slave environment at the all node
for backup.
6.1 Prepare
Mysql –uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql. sock
Mysql>grant super on *.* to purger@'localhost' identified by
'purger';
如果部署在bdb的复制节点上，请执行下列语句：
grant ALL on adm.* to repl@'%' identified by 'repl';
用于判断bdb的复制节点是否正常运行。
如果部署在rdb的复制节点上，请执行下列语句：
grant ALL on report.* to repl@'%' identified by 'repl';
用于判断bdb的复制节点是否正常运行。
Increment backup sh file name: Incrementbackup.sh
Use adm os user backup online data.
backup bin log what I can.
6.2 backup scritpt variables declaration
BAKDIR=
/opt/ericsson/software/xtrabackup/incradm # Backup directory
BINLOGDIR= /opt/ericsson/3pp/mysql/mysql5145/var #bin log
directory
SLAVEHOST= 147.128.104.222 #slave node
host info
SLAVEDBUSER=repl #slave node mysql user
info
SLAVEDBPWD=repl #slave node
mysql user password info
SLAVEPORT=3306 #slave node mysql port info
DBUSER=purger #master node mysql user，super
limit
DBPWD=purger #master node mysql user password info
DBHOST=localhost #master node mysql host info
DBPORT=3306 #master node mysql PORT info
SOCKET=/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
# master node Localhost via UNIX socket
area
6.3 Backup log:
Record operating results of the backup.
Backup start time
back up every step of the operating instructions
and the end results of the implementation time of the backup
results
backup success example:
adm@adm221:/opt/ericsson/software/xtrabackup>
more ./incradm/backup.20100914/*.log ::::::::::::::
./incradm/backup.20100914/backup.log
::::::::::::::
Tue Sep 14 14:53:25 CST 2010 backup up start...
Remove existing backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914,Create
backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914
Tue Sep 14 14:53:25 CST 2010 test network
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000001 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000001.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000002 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000002.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000003 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000003.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000004 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000004.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000005 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000005.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000006 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000006.tar.gz
Tue Sep 14 14:53:25 CST 2010 copying mysql-bin.000007 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914/mysql-bin.000007.tar.gz
Tue Sep 14 14:53:25 CST 2010 purge 147.128.104.221 logs to
mysql-bin.000008
Tue Sep 14 14:53:25 CST 2010 back up end
...
::::::::::::::
./incradm/backup.20100914/testslave.log
::::::::::::::
Ok
backup unsuccess example:
Tue Sep 14 14:59:55 CST 2010 backup up start...
Remove existing backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914,Create
backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914
Tue Sep 14 14:59:55 CST 2010 test network
Tue Sep 14 14:59:55 CST 2010 backup unsuccessed,Reason: slave
node ping error ,stop backup
Tue Sep 14 14:59:55 CST 2010 back up end
...
backup success but not do example:
Tue Sep 7 18:22:16 CST 2010 backup up start...
Tue Sep 7 18:22:16 CST 2010 test network
Tue Sep 7 18:22:16 CST 2010 purge 147.128.104.221 logs to
mysql-bin.000004
Tue Sep 7 18:22:16 CST 2010 back up end
...
::::::::::::::
./incradm/backup.20100914/testslave.log
::::::::::::::
Ok
7 Restore adm data to the most recent point in
time
7.1 场景：在存储设备异常的情况下，通过 full backup file of the
most recent point and upgrade log restore mysql data ，假设存储设备已更换和mysql
replication已按mysql安装手册重新配置。
说明：利用full backup file可以恢复到最近的全备份时刻，利用已归档的upgrade
log可以恢复到已归档的时刻。由于未归档的upgrade
log存放在存储设备上。存储设备异常情况下，这部分数据将丢失，如果这部分数据存在时，可以利用未归档的upgrade
log恢复到故障前时刻。
为了保障未归档的bin
log安全，建议采用存储设备冗余的方式保障数据可靠性。可以将数据库服务器各连接一台存储设备，这样存储设备异常会导致一个数据库节点不可用，从而发生服务切换。在最坏的情况下，可以通过幸存存储设备的数据文件和bin
log恢复数据到最近时刻。如果在T2时间点以后继续有数据写入bin log，需要在恢复前把这部分bin log另存。
应该满足full backup、 Increment backup、master-master
replication的mysql权限条件。
master-master replication设置参见MySql Configuration Guide.doc。
测试构造数据：
包含以下文件。
adm.sql
T1 时间点，对应full backup
upgradet_invt_inventory.sql
T2时间点，对应归档bin log
invt_schema.sql
T3 时间点,对应初始化环境
7.2 构造备份数据过程：
在adm221上完成此流程。
1) 导入adm.sql
T1 时间点，做一次Full backup。
adm@adm221:/backup>
./fullbackupadm.sh
total 29012
-rw-r--r-- 1 adm users 29652762 2010-09-14 15:49
backupadm_20100914.tar.gz
-rw-r--r-- 1 adm
users 7720 2010-09-14 15:49 my.cnf
-rw-r--r-- 1 adm
users 4620 2010-09-14 15:49 run.log
adm@adm221:/opt/ericsson/software/xtrabackup>
mysql -uroot -S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
Welcome to the MySQL monitor. Commands end
with ; or \g.
Your MySQL connection id is 3948
Server version: 5.1.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
mysql> use adm;
mysql> SELECT count(*) from t_invt_inventory;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
|
Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001
| 21390 |
| mysql-bin.000002
| 106 |
+------------------+-----------+
2 rows in set (0.00 sec)
No query specified
t_invt_inventory中含有2条记录。
2) 导入upgradet_invt_inventory.sql
这时t_invt_inventory中含有3条记录。其他不变。
T2 时间点，upgrade log包含t_invt_inventory中新的1条记录.
mysql> use adm;
mysql> SELECT count(*) from t_invt_inventory;
+----------+
| count(*) |
+----------+
| 3|
+----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)
mysql> show master logs;
+------------------+-----------+
|
Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001
| 21390 |
| mysql-bin.000002
| 397 |
| mysql-bin.000003
| 106 |
+------------------+-----------+
3 rows in set
adding: mysql-bin.000001 (deflated 37%)
adding: mysql-bin.000002 (deflated 38%)
adm@adm221:/opt/ericsson/software/xtrabackup>
more ./incradm/backup.20100914181425/backup.log
Tue Sep 14 18:14:25 CST 2010 backup up start...
Remove existing backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914181425,Create
backup folder:
/opt/ericsson/software/xtrabackup/incradm/backup.20100914181425
Tue Sep 14 18:14:25 CST 2010 test network
Tue Sep 14 18:14:25 CST 2010 copying mysql-bin.000001 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914181425/mysql-bin.000001.tar.gz
Tue Sep 14 18:14:25 CST 2010 copying mysql-bin.000002 to
/opt/ericsson/software/xtrabackup/incradm/backup.20100914181425/mysql-bin.000002.tar.gz
Tue Sep 14 18:14:26 CST 2010 purge 147.128.104.221 logs to
mysql-bin.000003
Tue Sep 14 18:14:26 CST 2010 back up end
...
这时做一次增量备份，备份并压缩所有的日志并清除过期日志。
total 16
-rw-r--r-- 1 adm users 685 2010-09-14 18:14 backup.log
-rw-r--r-- 1 adm users 414 2010-09-14 18:14
mysql-bin.000001.tar.gz
-rw-r--r-- 1 adm users 409 2010-09-14 18:14
mysql-bin.000002.tar.gz
-rw-r--r-- 1 adm users 5
2010-09-14 18:14 testslave.log
7.3 模拟宕机后重新安装数据库集群
重新导入invt_schema.sql删除所有数据。这时数据为空，master-master架构正常。
adm@adm221:/opt/ericsson/software/xtrabackup>
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock <.>
mysql> use adm;
Database changed
mysql> select count(*) from t_invt_inventory;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set
mysql>
T3时间点数据被清空。
说明：导入invt_schema.sql不会生成新的bin log。
Prepare：
adm@adm221:/opt/ericsson/software/xtrabackup>
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
mysql>reset master;
adm@adm222:/opt/ericsson/software/xtrabackup>
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
mysql>reset master;
清空各节点上的master日志。
adm@adm222:/opt/ericsson/software/xtrabackup>
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
mysql> reset slave;
adm@adm221:/opt/ericsson/software/xtrabackup>
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
mysql> reset slave;
清空各节点上的slave中继日志。
adm221
mysql> CHANGE MASTER TO MASTER_HOST='adm222',
MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
adm222
mysql> CHANGE MASTER TO MASTER_HOST='adm221',
MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
重新配置双向复制。
这时相当于完全重新安装了mysql集群。
7.4 恢复目标：
恢复到 T2时间点 t_invt_inventory中含有3条记录。
7.5 恢复过程：
7.5.1 停机做备份准备
adm@adm221:/opt/ericsson/software/xtrabackup>
mysqladmin -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock shutdown
adm@adm222:/opt/ericsson/software/xtrabackup>
mysqladmin -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/mysql.sock shutdown
在adm221/adm222上删除下列目录并重建，按adm:users授权。
rm –rf /opt/ericsson/3pp/mysql/data/poland/adm/var
rm –rf /opt/ericsson/3pp/mysql/data/poland/adm/Idata
mkdir –p /opt/ericsson/3pp/mysql/data/poland/adm/var
mkdir –p /opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog
adm222操作步骤同上。
7.5.2 Restore to Full backup of the
most recent point
7.5.2.1 在各节点上，解压full backup file ， 重新复制my.cnf到mysql basedir
adm@adm221:/opt/ericsson/software/xtrabackup>
cp /opt/ericsson/software/xtrabackup/adm/20100914/my.cnf
/opt/ericsson/3pp/mysql/mysql5145/my.cnf
Modify my.cnf
Master Server-id:1，目前是adm221
(147.128.104.221)
Slave Server-id:400000
，目前是adm222(147.128.104.222)
mkdir –p ./ba
cd ./ba
adm@adm221:/opt/ericsson/software/xtrabackup/ba>
tar -ixvf
/opt/ericsson/software/xtrabackup/adm/20100914/backupadm_20100914.tar.gz
backup-my.cnf
ibdata1
adm/batch_job_execution.ibd
adm/batch_job_execution_context.ibd
adm/batch_job_instance.ibd
adm/batch_job_params.ibd
adm/batch_step_execution.ibd
adm/batch_step_execution_context.ibd
adm/t_camp_ad_content.ibd
adm/t_camp_ad_content_ext_attribute.ibd
adm/t_camp_ad_file.ibd
adm/t_camp_camp_ext_attribute.ibd
adm/t_camp_camp_target_item.ibd
adm/t_camp_camp_task_click_action.ibd
adm/t_camp_camp_task_ext_attribute.ibd
adm/t_camp_camp_task_placement.ibd
adm/t_camp_camp_task_placement_comment.ibd
adm/t_camp_campaign.ibd
adm/t_camp_campaign_task.ibd
adm/t_camp_consumer_list.ibd
adm/t_camp_map_ad_content_placement.ibd
adm/t_camp_map_task_consumer_list.ibd
adm/t_camp_media_plan.ibd
adm/t_camp_media_plan_placement.ibd
adm/t_cons_consumer.ibd
adm/t_cons_consumer1#P#p0.ibd
adm/t_cons_consumer1#P#p1.ibd
adm/t_cons_consumer1#P#p2.ibd
adm/t_cons_consumer1#P#p3.ibd
adm/t_cons_consumer2#P#p0.ibd
adm/t_cons_consumer2#P#p1.ibd
adm/t_cons_consumer2#P#p2.ibd
adm/t_cons_consumer2#P#p3.ibd
adm/t_cons_consumer_ext_attribute.ibd
adm/t_cons_consumer_target_item.ibd
adm/t_cons_profile_definition.ibd
adm/t_cons_profile_provider.ibd
adm/t_invt_inventory.ibd
adm/t_invt_inventory_ext_attribute.ibd
adm/t_invt_inventory_target_item.ibd
adm/t_invt_invt_pricing_agreement.ibd
adm/t_invt_invt_pricing_agreement_item.ibd
adm/t_orgn_ext_attribute.ibd
adm/t_orgn_org_billing_attribute.ibd
adm/t_orgn_org_biz_role.ibd
adm/t_orgn_org_contact.ibd
adm/t_orgn_org_user.ibd
adm/t_orgn_organization.ibd
adm/t_orgn_user_privilege.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale10.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale11.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale12.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale6.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale7.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale8.ibd
adm/t_rept_fct_invt_day_sale#P#rept_fct_invt_day_sale9.ibd
adm/t_sys_dict_code.ibd
adm/t_sys_dict_code_i18.ibd
adm/t_camp_camp_ext_attribute.frm
adm/t_cons_consumer.frm
adm/t_camp_ad_content_ext_attribute.frm
adm/t_cons_profile_definition.frm
adm/t_invt_inventory_target_item.frm
adm/t_orgn_org_biz_role.frm
adm/t_camp_map_task_consumer_list.frm
adm/batch_step_execution_seq.MYD
adm/batch_step_execution_seq.MYI
adm/batch_step_execution_seq.frm
adm/t_camp_camp_task_placement.frm
adm/t_camp_campaign.frm
adm/t_orgn_org_contact.frm
adm/batch_job_execution_seq.MYD
adm/batch_job_execution_seq.MYI
adm/batch_job_execution_seq.frm
adm/t_camp_media_plan_placement.frm
adm/t_camp_campaign_task.frm
adm/t_camp_camp_task_placement_comment.frm
adm/t_camp_map_ad_content_placement.frm
adm/batch_step_execution_context.frm
adm/t_cons_consumer_target_item.frm
adm/t_sys_dict_code.frm
adm/db.opt
adm/t_camp_consumer_list.frm
adm/t_invt_invt_pricing_agreement_item.frm
adm/batch_job_execution.frm
adm/t_orgn_organization.frm
adm/t_cons_profile_provider.frm
adm/t_orgn_user_privilege.frm
adm/t_camp_camp_task_click_action.frm
adm/t_camp_ad_file.frm
adm/t_sys_dict_code_i18.frm
adm/t_invt_inventory.frm
adm/batch_job_params.frm
adm/t_camp_camp_task_ext_attribute.frm
adm/t_orgn_org_user.frm
adm/batch_step_execution.frm
adm/t_camp_camp_target_item.frm
adm/batch_job_instance.frm
adm/t_cons_consumer_ext_attribute.frm
adm/t_orgn_ext_attribute.frm
adm/batch_job_execution_context.frm
adm/t_camp_media_plan.frm
adm/t_invt_invt_pricing_agreement.frm
adm/t_orgn_org_billing_attribute.frm
adm/t_camp_ad_content.frm
adm/t_invt_inventory_ext_attribute.frm
adm/batch_job_seq.MYD
adm/batch_job_seq.MYI
adm/batch_job_seq.frm
adm/t_rept_fct_invt_day_sale.frm
adm/t_rept_fct_invt_day_sale.par
adm/t_cons_consumer1.frm
adm/t_cons_consumer1.par
adm/t_cons_consumer2.frm
adm/t_cons_consumer2.par
mysql/procs_priv.MYD
mysql/procs_priv.MYI
mysql/procs_priv.frm
mysql/help_keyword.MYD
mysql/help_keyword.MYI
mysql/help_keyword.frm
mysql/func.MYD
mysql/func.MYI
mysql/func.frm
mysql/columns_priv.MYD
mysql/columns_priv.MYI
mysql/columns_priv.frm
mysql/tables_priv.MYD
mysql/tables_priv.MYI
mysql/tables_priv.frm
mysql/help_topic.MYD
mysql/help_topic.MYI
mysql/help_topic.frm
mysql/time_zone_transition_type.MYD
mysql/time_zone_transition_type.MYI
mysql/time_zone_transition_type.frm
mysql/servers.MYD
mysql/servers.MYI
mysql/servers.frm
mysql/time_zone_leap_second.MYD
mysql/time_zone_leap_second.MYI
mysql/time_zone_leap_second.frm
mysql/event.MYD
mysql/event.MYI
mysql/event.frm
mysql/db.MYD
mysql/db.MYI
mysql/db.frm
mysql/plugin.MYD
mysql/plugin.MYI
mysql/plugin.frm
mysql/host.MYD
mysql/host.MYI
mysql/host.frm
mysql/help_relation.MYD
mysql/help_relation.MYI
mysql/help_relation.frm
mysql/time_zone.MYD
mysql/time_zone.MYI
mysql/time_zone.frm
mysql/proc.MYD
mysql/proc.MYI
mysql/proc.frm
mysql/general_log.frm
mysql/user.MYD
mysql/user.MYI
mysql/user.frm
mysql/help_category.MYD
mysql/help_category.MYI
mysql/help_category.frm
mysql/time_zone_name.MYD
mysql/time_zone_name.MYI
mysql/time_zone_name.frm
mysql/slow_log.frm
mysql/time_zone_transition.MYD
mysql/time_zone_transition.MYI
mysql/time_zone_transition.frm
mysql/ndb_binlog_index.MYD
mysql/ndb_binlog_index.MYI
mysql/ndb_binlog_index.frm
xtrabackup_logfile
xtrabackup_checkpoints
adm222操作步骤同上。
adm@adm221:/opt/ericsson/software/xtrabackup/ba>
scp
/opt/ericsson/software/xtrabackup/adm/20100914/backupadm_20100914.tar.gz 147.128.104.222://opt/ericsson/software/xtrabackup/ba
Password:
backupadm_20100914.tar.gz 100% 28MB 28.3MB/s 00:01
7.5.2.2 在各节点上，innobackupx恢复full backup file，恢复到T1时刻。(停机)
adm@adm221:/opt/ericsson/software/xtrabackup/ba>
innobackupex-1.5.1 --socket=/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
--defaults-file=/opt/ericsson/3pp/mysql/mysql5145/my.cnf
--no-lock --apply-log
/opt/ericsson/software/xtrabackup/ba 2>./log1
cat ./log1 |grep "InnoDB: Last MySQL binlog
file"> ./log2
InnoDB: Last MySQL binlog file position 0 21390, file name
./mysql-bin.000001
查看apply-log日志，获得bin log的bin log file 和position。
bin log file：./mysql-bin.000001
start_position： 0
end_position： 21390
所以增量恢复从./mysql-bin.000001文件的position21390开始。(前提是所有的bin
log被解压恢复到binlog目录，否则master log会重新从106开始。)
grep “Error” ./log1
查看./log1，看对innodb引擎和transaction log
checkpoint的恢复是否成功，如果日志提示：“100915 11:00:25 innobackupex-1.5.1: completed OK!”说明恢复成功。
innobackupex-1.5.1 --user=adm
--password=adm --host=localhost
--port=3306 --socket=/opt/ericsson/3pp/mysql/mysql5145/mysql.sock
--defaults-file=/opt/ericsson/3pp/mysql/mysql5145/my.cnf
--copy-back /opt/ericsson/software/xtrabackup/ba
2>./log3
说明：如果恢复report db， mysql user请使用report@localhost.
通过./log3可以查明对/opt/ericsson/3pp/mysql/data/poland/adm/var、
/opt/ericsson/3pp/mysql/data/poland/adm/Idata、
/opt/ericsson/3pp/mysql/data/poland/adm/Idata/ilog的复制是否执行成功。如果日志提示
“100915 11:04:48 innobackupex-1.5.1: completed
OK!”说明复制成功。
rm log1 log2 log3
7.5.3 在各节点上启动mysql server,以root@localhost登录，重新设置双向复制。(在线)
mysqld_safe
--defaults-file=/opt/ericsson/3pp/mysql/mysql5145/my.cnf
&
mysql -uroot
-S/opt/ericsson/3pp/mysql/mysql5145/adm-mysql.sock
利用第三节的bin log file和position设置双向复制。
以下是从adm221到adm222的复制设置
Master
mysql> flush tables with read lock;
mysql> lock tables;
Slave
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='adm221',
MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=21390;
mysql> start slave;
mysql> show slave status;
mysql> Show slave status\G;
Master
mysql> unlock tables;
以下是从adm222到adm221的复制设置
Master
mysql> flush tables with read lock;
mysql> lock tables;
Slave
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='adm222',
MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=354;
mysql> start slave;
mysql> show slave status;
mysql> Show slave status\G;
Master
mysql> unlock tables;
7.5.4 Restore to Archive log 。
(在线)
从磁带机中获取相应bin
log备份文件(mysql-bin.000002以后的所有文件)，重放磁带机上的bin
log，将数据恢复到T2时刻。通过mysqlbinlog从Position开始应用日志。在恢复前需要先验证所有的bin
log是否可以正常使用。
show master logs\G;
File: mysql-bin.000001
Position: 354
mysqlbinlog --start-position=354 mysql-bin.000001 | mysql -uroot
–p adm
只需要在adm221上设置即可。
7.5.5 Restore unArchive log。
(在线)
说明：如果在T2时间点以后继续有数据写入bin log，需要在恢复前把这部分bin
log另存，待对归档日志导入数据库后再导入未归档日志。未归档日志的恢复过程同归档日志的恢复过程。未归档日志的恢复起始点可通过一下步骤获得：
show master logs\G;
File: mysql-bin.000002
Position: 754
利用bin log file 和position设置change master命令。
通过mysqlbinlog从Position开始应用日志。在恢复前需要先验证所有的bin log是否可以正常使用。
利用归档bin log在最近全备数据的基础上进行恢复；
起始时间点(file，position)从innobackupex-1.5.1 --apply-log 的输出获得；
然后必须将最近的归档bin log文件从磁带上放回原来的bin log目录(不能在别的目录直接导入数据库)，这样该节点的log
进程才会继续从原来的position继续增长。否则他会重新生成mysql-bin.000001,position从106开始新增。
原来innobackupex-1.5.1 --apply-log 记录log起始点就没有用了。这时应该通过bin
log记录的时间进行恢复，这样需要在full backup file上记录backup time(这个功能需要在full backup
script中补上)。从backup time开始做增量恢复。归档bin
log时除了数据文件，建议同时归档备份日志文件。可以通过备份日志文件获得backup time。backup
time可以通过分析处于哪个bin log进行恢复。
8 ADM Backup & Restore Strategy
Backup from slave node, reduce impact on system.
8.1 adm
8.1.1 DB Backup time is less than 1 hour
1. Backup DB using XtraBackup every evening when nearly no DB
access
2. Backup binary logs or increment backup every 3 hours
3. retain the most recent backup file.
8.1.2 DB Backup more than 1 hour
1. Backup DB using XtraBackup every week, Saturday or Sunday
2. Increment Backup every evening
3. Backup and Compression binary logs every evening
4. retain the most recent backup file.
8.2 report
8.2.1 DB Backup time is less than 1 hour
1. Backup DB using XtraBackup every evening when nearly no DB
access
2. Backup binary logs every 3 hours
3. retain the most recent full backup file.
8.2.2 DB Backup more than 1 hour
Level 1:
1. Backup DB using XtraBackup every week, Saturday or Sunday
2. Increment Backup every evening
3. Backup binary logs every evening
4. retain the most recent backup file.
Level 2:
1. Archive application logs which DW data generate from.
--------------------------------------------------------------------------------
[e1]backup restore verify
500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
