转 shell 读取配置文件 和 mysqlbackup 和恢复
########sample 1
https://blog.csdn.net/vipchenvip/article/details/92806185
shell 读取配置文件的方法
转自一个bbs的问答。http://bbs.chinaunix.net/thread-3628456-1-1.html
配置文件config内容如下
ID=123 IP=192.168.3.154 Name=test
方法一，利用sed解析文本，提取配置信息
id=`sed '/^ID=/!d;s/.*=//' urfile` ip=`sed '/^IP=/!d;s/.*=//' urfile` name=`sed '/^Name=/!d;s/.*=//' urfile` echo $id echo $ip echo $name
方法二，利用eval方法解析
while read line;do
eval "$line"
done < config
echo $ID
echo $IP
echo $Name
方法三，直接将配置信息加载到session的环境变量中
source config echo $ID 123 echo $IP 192.168.3.154 echo $Name test
##########sample 2
https://blogs.oracle.com/mysql/meb-support-to-netbackup-mms
MEB Support to NetBackup MMS
In
MySQL Enterprise Backup 3.6, new option was introduced to support backup to tapes via
SBT interface. SBT stands for System Backup to Tape, an Oracle
API that helps to perform backup and restore jobs via media
management software such as Oracle's Secure Backup (OSB). There are
other storage managers like IBM's Tivoli Storage Manager (TSM) and
Symantec's Netbackup (NB) which are also supported by MEB but we
don't guarantee that it will function as expected for every release.
MEB supports SBT API version 2.0
In
this blog, I am primarily going to focus the interface of MEB and
Symantec's NB. If we are using tapes for backup, ensure that tape
library and tape drives are compatible.
Test Setup
1. Install NB 7.5 master and media servers in Linux OS. ( NB 7.1 can also be used but for testing purpose I used NB 7.5)
2. Install MEB 3.8 also in Linux OS.
3. Install NB admin console in your windows desktop and configure the NB master server from there.
Note:
Ensure that you have root user permission to install NetBackup.
Configuration Steps for MEB and NB
Once MEB and NB are installed,
- Ensure that NB is linked to MEB by specifying the library /usr/openv/netbackup/bin/libobk.so64 in the mysqlbackup command line using --sbt-lib-path.
- Configure the NB master server from windows console. That is configure the storage units by specifying the Storage unit name, Disk type, Media Server name etc.
- Create NetBackup policies that are user selectable. But please make sure that policy type is "Oracle".
- Define the clients where MEB will be executed. Some times this will be different host where MEB is run or some times in same Media server where NB and tapes are attached.
Now once the installation and configuration steps are performed for MEB and NB, the next part is the actual execution.
MEB should be run as single file backup using --backup-image option with prefix sbt:(it is a tag which tells MEB that it should stream the backup image through the SBT interface) which is sent to NB client via SBT interface . The resulting backup image is stored where NB stores the images that it backs up.
The following diagram shows how MEB interacts with MMS through SBT interface.
Backup
The following parameters should also be ready for the execution,
--sbt-lib-path : Path to SBT library specific to NetBackup MMS. SBT lib for NetBackup is in /usr/openv/netbackup/bin/libobk.so64
--sbt-environment: Environment variables must be defined specific to NetBackup. In our example below, we use
NB_ORA_SERV=myserver.com,
NB_ORA_CLIENT=myserver.com,
NB_ORA_POLICY=NBU-MEB
ORACLE_HOME = /export/home2/tmp/hema/mysql-server/
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
./mysqlbackup --port=13000 --protocol=tcp --user=root --backup-image=sbt:bkpsbtNB --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --sbt-environment="NB_ORA_SERV=myserver.com, NB_ORA_CLIENT=myserver.com, NB_ORA_POLICY=NBU-MEB, ORACLE_HOME=/export/home2/tmp/hema/mysql-server/” --backup-dir=/export/home2/tmp/hema/MEB_bkdir/ backup-to-image
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Once backup is completed successfully, this should appear in Activity Monitor in NetBackup Console.
For restore, image contents has to be extracted using image-to-backup-dir command and then apply-log and copy-back steps are applied.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
./mysqlbackup --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --backup-dir=/export/home2/tmp/hema/NBMEB/ --backup-image=sbt:bkpsbtNB image-to-backup-dir
-----------------------------------------------------------------------------------------------------------------------------------
Now apply logs as usual, shutdown the server and perform restore, restart the server and check the data contents.
- ./mysqlbackup ---backup-dir=/export/home2/tmp/hema/NBMEB/ apply-log
- ./mysqlbackup --datadir=/export/home2/tmp/hema/mysql-server/mysql-5.5-meb-repo/mysql-test/var/mysqld.1/data/ --backup-dir=/export/home2/tmp/hema/MEB_bkpdir/ innodb_log_files_in_group=2 --innodb_log_file_size=5M --user=root --port=13000 --protocol=tcp copy-back
The NB console should show 'Restore" job as done. If you don't see that there is something wrong with MEB or NetBackup.
You can also refer to more detailed steps of MEB and NB integration in whitepaper here
###########感谢
SlowTech
基于MySQL MEB的备份恢复
MEB（MySQL Enterprise Backup）是MySQL商业版中提供的备份工具，属于物理备份。
同XtraBackup一样，mysqlbackup的使用过程同样包含如下三个步骤：
备份（--backup）=> 应用日志（--apply-log）=> 恢复（--copy-back）
备份
# ./mysqlbackup --backup_dir=/backup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock backup
在备份目录下，会生成以下文件
# ls backup-my.cnf datadir meta server-all.cnf server-my.cnf
其中datadir和meta是目录，其它是变量配置文件
backup-my.cnf
官方解释如下：
Records the configuration parameters that specify the layout of the MySQL data files.
# cat backup-my.cnf # # Generated backup-my.cnf file. # Auto generated by mysqlbackup program. # [mysqld] innodb_data_file_path=ibdata1:12M:autoextend innodb_log_file_size=50331648 innodb_log_files_in_group=2 innodb_page_size=16384 innodb_checksum_algorithm=innodb
server-all.cnf
该文件记录了备份数据库的所有全局参数
server-my.cnf
该文件记录了备份数据库的非默认的全局参数
注意：在恢复数据库时，可使用server-all.cnf和server-my.cnf中的任意一个。如果需要将数据库恢复到非默认路径，还要修改路径相关的变量值。
再来看看meta目录中的文件
# ls meta/ backup_content.xml backup_create.xml backup_gtid_executed.sql backup_variables.txt MEB_2016-09-27.10-49-21_backup.log
MEB_2016-09-27.10-49-21_backup.log
记录了此次备份的详细信息
backup_content.xml
记录了备份文件的元数据信息和数据库定义信息，同时还记录了备份数据库的plugin信息，恢复时必须确保这些插件在目标数据库中同样存在。
backup_create.xml
记录了备份操作的相关信息，包括备份命令，备份的时间，备份的目录，数据库的参数信息
上述两个文件可通过--disable-manifest选项禁用。
backup_gtid_executed.sql
因为备份数据库启动了GTID复制，故会生成该文件记录GTID的信息
# cat backup_gtid_executed.sql # On a new slave, issue the following command if GTIDs are enabled: SET @@GLOBAL.GTID_PURGED='844e8202-8391-11e6-accb-000c29c64704:1-328944'; # Use the following command if you want to use the GTID handshake protocol: # CHANGE MASTER TO MASTER_AUTO_POSITION=1;
backup_variables.txt
记录了备份的相关信息，该文件由mysqlbackup来控制的，譬如apply_log_done指示是否进行了apply-log操作
# cat backup_variables.txt # # This file is auto generated by mysqlbackup. # [backup_variables] start_lsn=98224128 end_lsn=98227650 apply_log_done=0 is_incremental=0 is_incremental_with_redo_log_only=0 is_partial=0 is_compressed=0 is_skip_unused_pages=0 binlog_position=mysql-bin.000005:65395090 gtid_executed=844e8202-8391-11e6-accb-000c29c64704:1-328944 is_onlyinnodb=0
关于各文件的说明，可参考官方文档的解释
https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/meb-files-overview.html
应用日志
./mysqlbackup --backup-dir=/backup/ apply-log
MySQL Enterprise Backup version 3.9.0 [2013/08/23]
Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
./mysqlbackup --backup-dir=/backup/ apply-log
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'apply-log' run mysqlbackup
prints "mysqlbackup completed OK!".
160927 11:38:43 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.11-38-43_apply_log.log
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /backup/datadir
innodb_data_home_dir = /backup/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /backup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 14 buffers each of size 65536.
160927 11:38:43 mysqlbackup: INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads
160927 11:38:43 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
start lsn 98224128, end lsn 98227650,
start checkpoint 98224335.
InnoDB: Doing recovery: scanned up to log sequence number 98227650
mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
160927 11:38:48 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
lsn 98227650.
mysqlbackup: INFO: Last MySQL binlog file position 0 65394957, file name mysql-bin.000005
160927 11:38:48 mysqlbackup: INFO: The first data file is '/backup/datadir/ibdata1'
and the new created log files are at '/backup/datadir'
160927 11:38:48 mysqlbackup: INFO: Apply-log operation completed successfully.
160927 11:38:48 mysqlbackup: INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!
在应用日志之前，
数据目录中并没有redo log
[root@localhost datadir]# ls ibbackup_logfile ibdata1 mysql performance_schema test
在应用完日志之后，
数据目录中会生成redo log
[root@localhost datadir]# ls ibbackup_logfile ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
恢复
./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back
./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back
MySQL Enterprise Backup version 3.9.0 [2013/08/23]
Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
./mysqlbackup --defaults-file=/backup/server-my.cnf
--backup-dir=/backup/ copy-back
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back' run mysqlbackup
prints "mysqlbackup completed OK!".
160927 15:26:55 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.15-26-55_copy_back.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/
innodb_data_home_dir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /backup/datadir
innodb_data_home_dir = /backup/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /backup/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
160927 15:26:55 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
160927 15:26:55 mysqlbackup: INFO: Copying /backup/datadir/ibdata1 (to '/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data')
.160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/innodb_index_stats.ibd.
160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/innodb_table_stats.ibd.
160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_master_info.ibd.
160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_relay_log_info.ibd.
160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_worker_info.ibd.
160927 15:27:00 mysqlbackup: INFO: Copying /backup/datadir/test/test.ibd.
160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'mysql'
160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'performance_schema'
160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'test'
160927 15:27:00 mysqlbackup: INFO: Completing the copy of all non-innodb files.
160927 15:27:00 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
160927 15:27:04 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
160927 15:27:07 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /usr/local/mysql-advanced-5.6.23-
linux-glibc2.5-x86_64/data/160927 15:27:07 mysqlbackup: INFO: Copy-back operation completed successfully.
160927 15:27:07 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/'
mysqlbackup completed OK!
关于恢复，有以下几点需要注意
1. 恢复必须使用配置文件，建议备份目录下的server-all.cnf和server-my.cnf，事实上，在恢复的过程中，mysqlbackup需要明确上述输出中的参数，譬如，我使用了自己的配置文件进行恢复，结果报如下错误：
# ./mysqlbackup --defaults-file=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/my.cnf --backup-dir=/backup/
copy-backMySQL Enterprise Backup version 3.9.0 [2013/08/23]
Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
./mysqlbackup
--defaults-file=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/my.cnf
--backup-dir=/backup/ copy-back
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back' run mysqlbackup
prints "mysqlbackup completed OK!".
160927 15:26:09 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.15-26-09_copy_back.log
mysqlbackup: ERROR: Server innodb_log_files_in_group is missing or invalid
mysqlbackup: ERROR: Server repository configuration problem found.
mysqlbackup failed with errors!
很显然，我的配置文件中并没有定义innodb_log_files_in_group参数。
2. 可在上述配置文件中server-all.cnf和server-my.cnf自定义数据目录和base目录，不然默认的都是备份数据库的。
3. 虽然mysqlbackup --help中的解释是：The restore operation assumes the server is offline. Use of this command when server is running is not supported.
但事实上，在server是online的情况下，执行恢复操作并没有报错，甚至还显示“mysqlbackup completed OK!”。但是，并没有覆盖数据目录中的文件。
4. 在启动数据库的过程中，可以使用server-my.cnf或备份数据库的配置文件，不要使用server-all.cnf。在使用server-all.cnf启动数据库的过程中，会报如下错误：
2016-09-27 15:51:37 85229 [ERROR] /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/bin/mysqld: Table './mysql/user' is marked a s crashed and should be repaired2016-09-27 15:51:37 85229 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user' is marked as crashed and sh ould be repaired
这个坑定位了好久，因为server-all.cnf中定义的是所有参数的配置，怀疑跟某些参数有关，因参数较多，时间有限，并没有一一验证。
启动数据库
修改数据目录的权限
# chown -R mysql.mysql /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data
启动数据库
# /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/usr/local/mysql-advanced-5.
6.23-linux-glibc2.5-x86_64/my.cnf --user=mysql &
总结
以上只是mysqlbackup的一个简单的备份恢复流程，事实上，mysqlbackup还支持压缩备份等高级特性，后续再表。
