xtrabackup实现MySQL全量备份和增量备份

xtrabackup实现MySQL全量备份和增量备份

全量备份

第一步：完全备份到/ken下

[root@ken ~]innobackupex -uroot -pxx /ken ;#xx表示密码，输入你的数据库密码<br>
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit= --server-id= --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit= --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group= --innodb_max_dirty_pages_pct= --innodb_file_per_table= --innodb_io_capacity= --innodb_flush_method=O_DIRECT<br>
xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit= --server-id= --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit= --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group= --innodb_max_dirty_pages_pct= --innodb_file_per_table= --innodb_io_capacity= --innodb_flush_method=O_DIRECT<br>
:: innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.<br>
At the end of a successful backup run innobackupex<br>
prints "completed OK!".
...
...
:: Executing UNLOCK TABLES
:: All tables unlocked
:: [] Copying ib_buffer_pool to /ken/--23_04--/ib_buffer_pool<br>
:: [] ...done
:: Backup created in directory '/ken/2018-10-23_04-51-51/'
MySQL binlog position: filename 'mysql-bin.000004', position ''
:: [] Writing /ken/--23_04--/backup-my.cnf
:: [] ...done
:: [] Writing /ken/--23_04--/xtrabackup_info
:: [] ...done
xtrabackup: Transaction log of lsn () to () was copied.
:: completed OK! #最后出现comleted ok表示备份成功

第二步：删除mysql数据库（模拟故障）

[root@ken ~]mysql -uroot -p #登录数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id isServer version: 5.7.-log MySQL Community Server (GPL)Copyright (c) , , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; #查看现有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| jobs |
| ke |
| mysql |
| performance_schema |
| sys |
| test |
| test5 |
+--------------------+
rows in set (0.06 sec)

mysql> drop database mysql; #删除mysql数据库
Query OK, rows affected, warnings (0.24 sec)

第三步：退出mysql并关闭mysql

[root@ken ~]mysqladmin -uroot -p shutdown #关闭mysql

[root@ken ~ss -tnl | grep 3306 #查看mysql进程是否关闭

第四步：整理备份数据（将备份整理就绪）

[root@ken ~]innobackupex --apply-log /ken/--23_04--/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group= --innodb_log_file_size= --innodb_fast_checksum= --innodb_page_size= --innodb_log_block_size= --innodb_undo_directory=./ --innodb_undo_tablespaces= --server-id= --redo-log-version=
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group= --innodb_log_file_size= --innodb_fast_checksum= --innodb_page_size= --innodb_log_block_size= --innodb_undo_directory=./ --innodb_undo_tablespaces= --server-id= --redo-log-version=
:: 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!".
...
...
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn
InnoDB: Doing recovery: scanned up to log sequence number