rds下载备份集在另外一台机器上恢复并应用binlog日志
-----------------------------备份还原并启动数据库----------------------------------
1.创建目录，并把下载的压缩文件拷贝到相应的目录
[root@localhost opt]# mkdir -p /opt/xbackup_file
[root@localhost opt]# cd /soft
[root@localhost soft]# cp hins5246445_data_20180903064437.tar.gz /opt/xbackup_file/
解压缩
[root@localhost xbackup_file]# tar -zxvf hins5246445_data_20180903064437.tar.gz
查看解压后的文件
[root@localhost xbackup_file]# ls
[root@localhost xbackup_file]# ls -al
total 362780
drwxr-xr-x 15 root root 4096 Feb 16 11:33 .
drwxr-xr-x. 4 root root 130 Feb 16 11:14 ..
-rw-rw---- 1 root root 450 Feb 15 01:41 backup-my.cnf
drwxr-xr-x 2 root root 4096 Feb 16 11:32 db_admin
drwxr-xr-x 2 root root 12288 Feb 16 11:32 db_xdmwxxc
-rw-rw---- 1 root root 209715200 Feb 15 01:41 ibdata1
-rw-rw---- 1 root root 99777236 Feb 15 01:40 log000000000003.tokulog29
drwxr-xr-x 2 root root 4096 Feb 16 11:32 mysql
drwxr-xr-x 2 root root 4096 Feb 16 11:32 performance_schema
drwxr-xr-x 2 root root 294 Feb 16 11:32 push
drwxr-xr-x 2 root root 4096 Feb 16 11:32 server_statistics
drwxr-xr-x 2 root root 20 Feb 16 11:32 test
-rw-rw---- 1 root root 16384 Aug 15 2016 tokudb.directory
-rw-rw---- 1 root root 32768 Apr 23 2018 tokudb.environment
-rw-rw---- 1 root root 32768 Apr 23 2018 tokudb.rollback
-rw-rw---- 1 root root 124 Feb 15 01:41 xtrabackup_binlog_info
-rw-rw---- 1 root root 123 Feb 15 01:41 xtrabackup_checkpoints
-rw-rw---- 1 root root 823 Feb 15 01:41 xtrabackup_info
-rw-rw---- 1 root root 61804032 Feb 15 01:41 xtrabackup_logfile
-rw-rw---- 1 root root 81 Feb 15 01:41 xtrabackup_slave_filename_info
-rw-rw---- 1 root root 164 Feb 15 01:41 xtrabackup_slave_info
drwxr-xr-x 2 root root 12288 Feb 16 11:32 yf_zdyu
drwxr-xr-x 2 root root 4096 Feb 16 11:32 yf_leif
drwxr-xr-x 2 root root 4096 Feb 16 11:32 zprogram
2.修改目录权限
将刚才解压的目录权限赋予mysql
[root@localhost opt]# cd /opt
[root@localhost opt]# chown -R mysql:mysql xbackup_file
3.停掉原来的数据库
要是恢复的机器上之前安装了mysql,需要将其停掉,若没有安装mysql的话,需要事先安装mysql,注意安装的版本需要跟rds的要一致
[root@localhost bin]# ./mysqladmin -h localhost -uroot -pmysql shutdown
4.删除之前数据库的data目录下的文件
我的做法是重命名data目录,重新创建
[root@localhost mysql]# cd /db/mysql
[root@localhost mysql]# mv data bakdata
[root@localhost mysql]# mkdir data
[root@localhost mysql]# chown -R mysql:mysql ./data
5.准备配置文件(my.cnf)
拷贝配置文件到数据库安装目录
rds的配置文件内容如下:
[root@hxl]# more backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_log_buffer_size=8388608
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
rds_encrypt_data=false
innodb_encrypt_algorithm=aes_128_ecb
红色标注的需要去掉
[root@localhost xbackup_file]# cp backup-my.cnf /opt/mysql-5.6.40/conf/
重命名为my.cnf(若原来的目录有my.cnf,需要重命名该文件)
[mysql@localhost conf]$ mv backup-my.cnf my.cnf
文件拷贝后需要注意修改权限
[root@localhost opt]# chown -R mysql:mysql ./mysql-5.6.40/
修改该文件的内容如下(rds版本是5.6的参数文件):
[mysqld]
innodb_checksum_algorithm=innodb
##innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_log_buffer_size=8388608
##innodb_fast_checksum=false
innodb_page_size=16384
##innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
port=3306
server-id=1
datadir=/nasdata/middle/mysql56/data
max_connections = 1500
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
gtid_mode=on
log-bin=mysql-bin
log_slave_updates=1
enforce_gtid_consistency=ON
binlog_format=row
skip-grant-tables
sync_binlog=0
innodb_flush_log_at_trx_commit=0
##rds_encrypt_data=false
##innodb_encrypt_algorithm=aes_128_ecb
interactive_timeout=86400
wait_timeout=86400
max_allowed_packet=1073741824
rds是5.7的配置文件
# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
##innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_log_buffer_size=8388608
##innodb_fast_checksum=false
innodb_page_size=16384
##innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=2
server_id=2174116263
##redo_log_version=1
##server_uuid=7b101a0a-4169-11ee-b1fb-0c42a1070cc2
##master_key_id=0
##innodb_encrypt_algorithm=AES_256_CBC
port=3306
datadir=/nasdata/middle/mysql57/data
max_connections = 1500
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
gtid_mode=on
log-bin=mysql-bin
log_slave_updates=1
enforce_gtid_consistency=ON
binlog_format=row
skip-grant-tables
sync_binlog=0
innodb_flush_log_at_trx_commit=0
interactive_timeout=86400
wait_timeout=86400
max_allowed_packet=1073741824
6.删除掉redolog目录下的日志组
要是redolog有日志组的话需要删除掉,这里根据个人的环境配置不同而不同,若redolog也是放在data目录下的话,可以忽略此步骤.
7.进行恢复
首先执行:
/opt/xtrabackup247/bin/innobackupex --defaults-file=/db/mysql/conf/my.cnf --user=root --apply-log /opt/xbackup_file
[root@localhost conf]# /opt/xtrabackup247/bin/innobackupex --defaults-file=/db/mysql/conf/my.cnf --user=root --apply-log /opt/xbackup_file
180903 16:51:56 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
/opt/xtrabackup247/bin/innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: cd to /opt/xbackup_file/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(133592877048)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 133592877048
InnoDB: Doing recovery: scanned up to log sequence number 133592931244 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 133592931244 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 133592931244 (0%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 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
InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 406368, file name mysql-bin.000078
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 10438ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.13 started; log sequence number 133592931244
InnoDB: xtrabackup: Last MySQL binlog file position 406368, file name mysql-bin.000078
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 133592932517
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 1572864000
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 1500 MB
InnoDB: Progress in MB:
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
InnoDB: Setting log file ./ib_logfile1 size to 1500 MB
InnoDB: Progress in MB:
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=133592932517
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 133592932876
InnoDB: Doing recovery: scanned up to log sequence number 133592932885 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 133592932885 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 406368, file name mysql-bin.000078
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 243181ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.13 started; log sequence number 133592932885
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 133592934827
180903 16:56:18 completed OK!
接着执行:
/opt/xtrabackup247/bin/innobackupex --defaults-file=/db/mysql/conf/my.cnf --user=root --copy-back --rsync /opt/xbackup_file
这里输出很多,省略.
8.修改权限
[root@localhost db]# cd /db
[root@localhost db]# chown -R mysql:mysql ./mysql
9.启动数据库
[root@localhost bin]# cd /db/mysql/bin
[root@localhost bin]#./mysqld_safe --defaults-file=/db/mysql/conf/my.cnf --user=mysql &
启动保持,发现有不支持的参数
2018-09-03 17:57:19 24869 [ERROR] /db/mysql/bin/mysqld: unknown variable 'innodb_log_checksum_algorithm=innodb'
2018-09-03 18:01:11 25745 [ERROR] /db/mysql/bin/mysqld: unknown variable 'innodb_fast_checksum=false'
2018-09-03 18:02:04 26077 [ERROR] /db/mysql/bin/mysqld: unknown variable 'innodb_log_block_size=512'
2018-09-03 18:03:14 26412 [ERROR] /db/mysql/bin/mysqld: unknown variable 'rds_encrypt_data=false'
2018-09-03 18:03:52 26701 [ERROR] /db/mysql/bin/mysqld: unknown variable 'innodb_encrypt_algorithm=aes_128_ecb'
将这些参数去掉
innodb_log_checksum_algorithm=innodb
innodb_fast_checksum=false
innodb_log_block_size=512
rds_encrypt_data=false
innodb_encrypt_algorithm=aes_128_ecb
10.跳过密码登陆启动
步骤9启动后,发现登陆不了数据库，这个时候需要跳过密码启动，修改root账号的密码
在my.cnf配置文件添加如下项目
skip-grant-tables
停掉(kill掉mysql进程)再启动
[root@localhost bin]# ./mysqld_safe --defaults-file=/db/mysql/conf/my.cnf --user=mysql &
11.修改root账号密码
mysql -h localhost -uroot
use mysql;
update user set password=password("mysql") where user="root";
flush privileges;
12.去掉skip-grant-tables后再启动后进行登陆
[root@localhost bin]# ./mysql -h 127.0.0.1 -uroot -pmysql
到这里数据库恢复完成,下面进行日志的应用.
----------------------------日志的应用--------------------------------------
说明:日志应用建议关闭双1,否则很慢
sync_binlog=0
innodb_flush_log_at_trx_commit=0
从上面的恢复可以看到,数据库目前恢复到的位置为:
InnoDB: xtrabackup: Last MySQL binlog file position 406368, file name mysql-bin.000078
若是上面没有输出的话,也可以从xtrabackup信息里查看,如下:
[root@localhost xbackup_file]# more xtrabackup_info
binlog_pos = filename 'mysql-bin.000078', position 406368, GTID of the last change '8bfac327-62cb-11e6-9715-6c92bf2c45fc:1-661898,
9f7dd74e-62cb-11e6-9716-6c92bf315fa3:1-1185674168'
我们要应用该位置之后的日志
可以查看mysql-bin.000078日志内容:
[root@localhost bin]# ./mysqlbinlog --base64-output=decode-rows -v /soft/rds_binlog/mysql-bin.000078>/tmp/aa.txt ##可读性的语句
[root@localhost bin]# ./mysqlbinlog /soft/rds_binlog/mysql-bin.000078>/tmp/bb.txt ##不可读,记录的是binlong事务号,可以通过show processlist查看应用到那个位置
我这里输出到aa.txt文件,我们这里从位置406368开始应用日志:
[root@localhost rds_binlog]# /db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000078 --start-position="406368"|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
Warning: Using a password on the command line interface can be insecure.
ERROR 1781 (HY000) at line 15: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF
解决办法:
1.查看当前数据库的gtid_mode模式
mysql> show global variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.00 sec)
2. 5.6版本需要修改配置文件my.cnf,添加如下项目重启动mysql
gtid_mode=on
log-bin=mysql-bin
log_slave_updates=1
enforce_gtid_consistency=ON
5.7版本的话可以执行如下命令
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
重启动mysql
mysqld_safe --defaults-file=/db/mysql/conf/my.cnf --user=mysql &
再次执行应用日志,发现报错:
[root@localhost rds_binlog]# /db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000078 --start-position="406368"|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
Warning: Using a password on the command line interface can be insecure.
ERROR 1666 (HY000) at line 48: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.
解决办法:
因为rds数据库的BINLOG_FORMAT设置的为ROW模式,所以本地数据库也要设置为ROW模式,如下
binlog_format=row
修改完配置文件后需要重启动
再次执行应用日志
应用mysql-bin.000078
/db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000078 --start-position="406368"|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
应用mysql-bin.000079
/db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000079|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
应用mysql-bin.000080
/db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000080|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
应用mysql-bin.000081
/db/mysql/bin/mysqlbinlog /soft/rds_binlog/mysql-bin.000081|/db/mysql/bin/mysql -h 127.0.0.1 -uroot -pmysql
日志应用可以--start-position和--stop-datetime一起使用
#/opt/mysql57/bin/mysqlbinlog /tmp/mysql-bin.000335 --start-position="16086259" --stop-datetime='2023-11-14 11:00:01'|/opt/mysql57/bin/mysql -h 127.0.0.1 -uroot
附上整个还原环境的配置文件参数(my.cnf)
[root@localhost conf]# more my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_page_size=16384
innodb_undo_directory=.
innodb_undo_tablespaces=0
port=3306
server-id=1
datadir=/opt/mysql-5.6.40/data
max_connections = 1500
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
gtid_mode=on
log-bin=mysql-bin
log_slave_updates=1
enforce_gtid_consistency=ON
binlog_format=row
skip-grant-tables
sync_binlog=0##关闭双1
innodb_flush_log_at_trx_commit=0 ##关闭双1
max_allowed_packet=1073741824 ##应用大binlog文件,超过1GB的大事物好像报错误Got a packet bigger than 'max_allowed_packet' bytes
wait_timeout=86400
interactive_timeout=86400
log-warnings=2 ##输出详细信息
###################使用技巧:#######################################
通配符使用
mysqlbinlog --stop-datetime="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.0* | /opt/mysql57/bin/mysql -h 127.0.0.1 -uroot
mysqlbinlog -–stop-datetime="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.[0-9]* | /opt/mysql57/bin/mysql -h 127.0.0.1 -uroot
技巧2:
应用具体某个库
你可以通过--one-database 参数选择性的恢复单个数据库,example在下面,爽吧。
/opt/mysql57/bin/mysqlbinlog /tmp/mysql-bin.000335 --start-position="16086259"|/opt/mysql57/bin/mysql -h 127.0.0.1 -uroot --one-database db_dataassistant
技巧3(需要验证):
/opt/mysql57/bin/mysqlbinlog /tmp/mysql-bin.000335 --start-position="16086259">/tmp/335.sql
类似的语句将日志导成了ASCII文本文件,命令行模式执行[source *.sql]，
mysql>source /tmp/335.sql;
或是使用
/opt/mysql57/bin/mysql -h 127.0.0.1 -uroot --default-character-set=utf8</tmp/335.sql
注意,这种方式生产的文件也可以:
/opt/mysql57/bin/mysqlbinlog --base64-output=decode-rows -v /tmp/mysql-bin.000335 --start-position="16086259">/tmp/335_read.sql
mysql>source /tmp/335_read.sql;
-- The End--
