演示环境：
数据库版本：5.7.19
本次演示包括：xtrabackup软件的安装， 全量备份，增量备份，恢复 操作
1、安装xtrabackup软件
我要评论
--查看软件
[root@single-instance oracle_setup]# ll *xtrabackup*
-rw-r--r-- 1 root root 7745224 Mar 19 21:21 percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm
--安装报错 一些依赖包没有
[root@single-instance oracle_setup]# rpm -ivh percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64
perl(DBD::mysql) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64
perl(Digest::MD5) is needed by percona-xtrabackup-24-2.4.10-1.el7.x86_64
--安装依赖包
[root@single-instance oracle_setup]# rpm -ivh libev4-4.24-alt1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:libev4-4.24-alt1 ################################# [100%]
[root@single-instance yum.repos.d]# yum -y install perl-Digest-MD5.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed
--> Processing Dependency: perl(Digest::base) >= 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64
--> Running transaction check
---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================
Installing:
perl-Digest-MD5 x86_64 2.52-3.el7 rhel7 30 k
Installing for dependencies:
perl-Digest noarch 1.17-245.el7 rhel7 23 k
Transaction Summary
=============================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 53 k
Installed size: 82 k
Downloading packages:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 6.8 MB/s | 53 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
cyrus-sasl-lib-2.1.26-20.el7_2.x86_64 is a duplicate with cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
Installing : perl-Digest-1.17-245.el7.noarch 1/2
Installing : perl-Digest-MD5-2.52-3.el7.x86_64 2/2
Verifying : perl-Digest-1.17-245.el7.noarch 1/2
Verifying : perl-Digest-MD5-2.52-3.el7.x86_64 2/2
Installed:
perl-Digest-MD5.x86_64 0:2.52-3.el7
Dependency Installed:
perl-Digest.noarch 0:1.17-245.el7
Complete!
[root@single-instance oracle_setup]# rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
warning: mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-compat-5.7.2################################# [100%]
--依赖 mysql-community-libs-compat
[root@single-instance yum.repos.d]# yum -y install perl-DBD-MySQL.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================
Installing:
perl-DBD-MySQL x86_64 4.023-5.el7 rhel7 140 k
Transaction Summary
=============================================================================================================================================================
Install 1 Package
Total download size: 140 k
Installed size: 323 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
cyrus-sasl-lib-2.1.26-20.el7_2.x86_64 is a duplicate with cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 1/1
Verifying : perl-DBD-MySQL-4.023-5.el7.x86_64 1/1
Installed:
perl-DBD-MySQL.x86_64 0:4.023-5.el7
Complete!
--安装xtrabackup
[root@single-instance oracle_setup]# rpm -ivh percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:percona-xtrabackup-24-2.4.10-1.el################################# [100%]
--查看安装文件
[root@single-instance xtrabackup_dir]# rpm -qa |grep xtrabackup
percona-xtrabackup-24-2.4.10-1.el7.x86_64
[root@single-instance xtrabackup_dir]# rpm -ql percona-xtrabackup-24-2.4.10-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-24-2.4.10
/usr/share/doc/percona-xtrabackup-24-2.4.10/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
2、基本设置
--创建备份用户 mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'S3cret2233$'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) --设置备份路径 [root@single-instance xtrabackup_dir]# mkdir -p /mysql/xtrabackup_dir/ [root@single-instance xtrabackup_dir]# chown mysql:mysql /mysql/xtrabackup_dir/ [root@single-instance ~]# vi /etc/my.cnf --添加参数 [xtrabackup] target_dir = /mysql/xtrabackup_dir/
3、xtrabackup全量备份
--xtrabackup 全量备份 [root@single-instance ~]# xtrabackup --backup --user=bkpuser --password=S3cret2233$ 180416 17:38:44 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser' (using password: YES). 180416 17:38:44 version_check Connected to MySQL server 180416 17:38:44 version_check Executing a version check against the server... 180416 17:38:44 version_check Done. 180416 17:38:44 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set Using server version 5.7.19-log xtrabackup version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 InnoDB: Number of pools: 1 180416 17:38:44 >> log scanned up to (2777824) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0 180416 17:38:44 [01] Copying ./ibdata1 to /mysql/xtrabackup_dir/ibdata1 180416 17:38:44 [01] ...done ...... 180416 17:38:44 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/xtrabackup_dir/mysql/slave_worker_info.ibd 180416 17:38:44 [01] ...done 180416 17:38:45 >> log scanned up to (2777824) 180416 17:38:45 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 180416 17:38:45 Executing FLUSH TABLES WITH READ LOCK... 180416 17:38:45 Starting to backup non-InnoDB tables and files 180416 17:38:45 [01] Copying ./flydb/t_list.frm to /mysql/xtrabackup_dir/flydb/t_list.frm 180416 17:38:45 [01] ...done ...... 180416 17:38:47 [01] Copying ./mysql/help_relation.frm to /mysql/xtrabackup_dir/mysql/help_relation.frm 180416 17:38:47 [01] ...done 180416 17:38:47 Finished backing up non-InnoDB tables and files 180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/xtrabackup_binlog_info 180416 17:38:47 [00] ...done 180416 17:38:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '2777815' xtrabackup: Stopping log copying thread. .180416 17:38:47 >> log scanned up to (2777824) 180416 17:38:47 Executing UNLOCK TABLES 180416 17:38:47 All tables unlocked 180416 17:38:47 [00] Copying ib_buffer_pool to /mysql/xtrabackup_dir/ib_buffer_pool 180416 17:38:47 [00] ...done 180416 17:38:47 Backup created in directory '/mysql/xtrabackup_dir/' MySQL binlog position: filename 'my-bin.000043', position '800' 180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/backup-my.cnf 180416 17:38:47 [00] ...done 180416 17:38:47 [00] Writing /mysql/xtrabackup_dir/xtrabackup_info 180416 17:38:47 [00] ...done xtrabackup: Transaction log of lsn (2777815) to (2777824) was copied. 180416 17:38:48 completed OK! --查看备份文件 [root@single-instance xtrabackup_dir]# cd /mysql/xtrabackup_dir/ [root@single-instance xtrabackup_dir]# ll total 12336 -rw-r----- 1 root root 426 Apr 16 17:38 backup-my.cnf drwxr-x--- 2 root root 4096 Apr 16 17:38 flydb -rw-r----- 1 root root 315 Apr 16 17:38 ib_buffer_pool -rw-r----- 1 root root 12582912 Apr 16 17:38 ibdata1 drwxr-x--- 2 root root 4096 Apr 16 17:38 mysql drwxr-x--- 2 root root 4096 Apr 16 17:38 performance_schema drwxr-x--- 2 root root 12288 Apr 16 17:38 sys -rw-r----- 1 root root 18 Apr 16 17:38 xtrabackup_binlog_info -rw-r----- 1 root root 113 Apr 16 17:38 xtrabackup_checkpoints -rw-r----- 1 root root 464 Apr 16 17:38 xtrabackup_info -rw-r----- 1 root root 2560 Apr 16 17:38 xtrabackup_logfile
4、innobackupex全量备份
--innobackupex 全量备份
--defaults-file=/etc/my.cnf //my.cnf文件
--user=bkpuser //user用户
--password //password密码
/mysql/innobackupex_dir/ //备份路径
[root@single-instance ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=S3cret2233$ /mysql/innobackupex_dir/
180416 19:56:45 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
180416 19:56:45 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser' (using password: YES).
180416 19:56:45 version_check Connected to MySQL server
180416 19:56:45 version_check Executing a version check against the server...
180416 19:56:45 version_check Done.
180416 19:56:45 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 5.7.19-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180416 19:56:45 >> log scanned up to (2777824)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
180416 19:56:46 [01] Copying ./ibdata1 to /mysql/innobackupex_dir/2018-04-16_19-56-45/ibdata1
180416 19:56:46 [01] ...done
......
180416 19:56:46 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/innobackupex_dir/2018-04-16_19-56-45/mysql/slave_worker_info.ibd
180416 19:56:46 [01] ...done
180416 19:56:46 >> log scanned up to (2777824)
180416 19:56:47 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180416 19:56:47 Executing FLUSH TABLES WITH READ LOCK...
180416 19:56:47 Starting to backup non-InnoDB tables and files
180416 19:56:47 [01] Copying ./flydb/t_list.frm to /mysql/innobackupex_dir/2018-04-16_19-56-45/flydb/t_list.frm
180416 19:56:47 [01] ...done
......
180416 19:56:49 [01] Copying ./mysql/help_relation.frm to /mysql/innobackupex_dir/2018-04-16_19-56-45/mysql/help_relation.frm
180416 19:56:49 [01] ...done
180416 19:56:49 Finished backing up non-InnoDB tables and files
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/xtrabackup_binlog_info
180416 19:56:49 [00] ...done
180416 19:56:49 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2777815'
xtrabackup: Stopping log copying thread.
.180416 19:56:49 >> log scanned up to (2777824)
180416 19:56:49 Executing UNLOCK TABLES
180416 19:56:49 All tables unlocked
180416 19:56:49 [00] Copying ib_buffer_pool to /mysql/innobackupex_dir/2018-04-16_19-56-45/ib_buffer_pool
180416 19:56:49 [00] ...done
180416 19:56:49 Backup created in directory '/mysql/innobackupex_dir/2018-04-16_19-56-45/'
MySQL binlog position: filename 'my-bin.000043', position '800'
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/backup-my.cnf
180416 19:56:49 [00] ...done
180416 19:56:49 [00] Writing /mysql/innobackupex_dir/2018-04-16_19-56-45/xtrabackup_info
180416 19:56:49 [00] ...done
xtrabackup: Transaction log of lsn (2777815) to (2777824) was copied.
180416 19:56:50 completed OK!
--查看备份文件
[root@single-instance innobackupex_dir]# cd /mysql/innobackupex_dir/2018-04-16_19-56-45/
[root@single-instance 2018-04-16_19-56-45]# ll
total 12336
-rw-r----- 1 root root 426 Apr 16 19:56 backup-my.cnf
drwxr-x--- 2 root root 4096 Apr 16 19:56 flydb
-rw-r----- 1 root root 315 Apr 16 19:56 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 16 19:56 ibdata1
drwxr-x--- 2 root root 4096 Apr 16 19:56 mysql
drwxr-x--- 2 root root 4096 Apr 16 19:56 performance_schema
drwxr-x--- 2 root root 12288 Apr 16 19:56 sys
-rw-r----- 1 root root 18 Apr 16 19:56 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Apr 16 19:56 xtrabackup_checkpoints
-rw-r----- 1 root root 510 Apr 16 19:56 xtrabackup_info
-rw-r----- 1 root root 2560 Apr 16 19:56 xtrabackup_logfile
5、innobackupex增量备份
--innobackupex 增量备份
只有innodb 可以， 有lsn号(相当oracle的scn)，可以增量备份； 其他的myisam都是全量再备份一份； memory的只备份了表结构， 数据备份不了。
--备份前新增表 并写入数据
mysql> use flydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table please_recovery_me_t(a int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into please_recovery_me_t values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into please_recovery_me_t values (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from please_recovery_me_t;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
--增量备份
--defaults-file=/etc/my.cnf //my.cnf文件
--user=bkpuser //user用户
--password //password密码
--incremental //增量备份标示
/mysql/innobackupex_dir/ //增量备份路径
--incremental-basedir //基于的全量备份路径
[root@single-instance 2018-04-16_19-56-45]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=S3cret2233$ --incremental /mysql/innobackupex_dir/ --incremental-basedir=/mysql/innobackupex_dir/2018-04-16_19-56-45/
180416 20:38:06 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
180416 20:38:06 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'bkpuser' (using password: YES).
180416 20:38:06 version_check Connected to MySQL server
180416 20:38:06 version_check Executing a version check against the server...
180416 20:38:06 version_check Done.
180416 20:38:06 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: not set, socket: not set
Using server version 5.7.19-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
incremental backup from 2777815 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180416 20:38:06 >> log scanned up to (2784824)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
xtrabackup: using the full scan for incremental backup
180416 20:38:07 [01] Copying ./ibdata1 to /mysql/innobackupex_dir/2018-04-16_20-38-06/ibdata1.delta
180416 20:38:07 [01] ...done
......
180416 20:38:11 [01] Copying ./mysql/slave_worker_info.ibd to /mysql/innobackupex_dir/2018-04-16_20-38-06/mysql/slave_worker_info.ibd.delta
180416 20:38:11 [01] ...done
180416 20:38:11 >> log scanned up to (2784824)
180416 20:38:12 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180416 20:38:12 Executing FLUSH TABLES WITH READ LOCK...
180416 20:38:12 Starting to backup non-InnoDB tables and files
180416 20:38:12 [01] Copying ./flydb/t_list.frm to /mysql/innobackupex_dir/2018-04-16_20-38-06/flydb/t_list.frm
180416 20:38:12 [01] ...done
......
180416 20:38:14 [01] Copying ./mysql/help_relation.frm to /mysql/innobackupex_dir/2018-04-16_20-38-06/mysql/help_relation.frm
180416 20:38:14 [01] ...done
180416 20:38:14 Finished backing up non-InnoDB tables and files
180416 20:38:14 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/xtrabackup_binlog_info
180416 20:38:14 [00] ...done
180416 20:38:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2784815'
xtrabackup: Stopping log copying thread.
.180416 20:38:14 >> log scanned up to (2784824)
180416 20:38:15 Executing UNLOCK TABLES
180416 20:38:15 All tables unlocked
180416 20:38:15 [00] Copying ib_buffer_pool to /mysql/innobackupex_dir/2018-04-16_20-38-06/ib_buffer_pool
180416 20:38:15 [00] ...done
180416 20:38:15 Backup created in directory '/mysql/innobackupex_dir/2018-04-16_20-38-06/'
MySQL binlog position: filename 'my-bin.000043', position '1527'
180416 20:38:15 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/backup-my.cnf
180416 20:38:15 [00] ...done
180416 20:38:15 [00] Writing /mysql/innobackupex_dir/2018-04-16_20-38-06/xtrabackup_info
180416 20:38:15 [00] ...done
xtrabackup: Transaction log of lsn (2784815) to (2784824) was copied.
180416 20:38:15 completed OK!
--查看增量备份文件
[root@single-instance 2018-04-16_20-38-06]# cd /mysql/innobackupex_dir/2018-04-16_20-38-06
[root@single-instance 2018-04-16_20-38-06]# ll
total 596
-rw-r----- 1 root root 426 Apr 16 20:38 backup-my.cnf
drwxr-x--- 2 root root 4096 Apr 16 20:38 flydb
-rw-r----- 1 root root 315 Apr 16 20:38 ib_buffer_pool
-rw-r----- 1 root root 557056 Apr 16 20:38 ibdata1.delta
-rw-r----- 1 root root 44 Apr 16 20:38 ibdata1.meta
drwxr-x--- 2 root root 4096 Apr 16 20:38 mysql
drwxr-x--- 2 root root 4096 Apr 16 20:38 performance_schema
drwxr-x--- 2 root root 12288 Apr 16 20:38 sys
-rw-r----- 1 root root 19 Apr 16 20:38 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Apr 16 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root 598 Apr 16 20:38 xtrabackup_info
-rw-r----- 1 root root 2560 Apr 16 20:38 xtrabackup_logfile
6、innobackupex全量恢复
--删除flydb 数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| flydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database flydb;
Query OK, 8 rows affected (0.12 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
关闭数据库
[root@single-instance ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
修改数据库目录
[root@single-instance lib]# mv mysql mysqlbak
--准备恢复(全备份)
--apply-log //恢复准备工作
--redo-only should be used when merging all incrementals except the last one. That’s why the previous
line doesn’t contain the --redo-only option. Even if the --redo-only was used on the last step, backup would
still be consistent but in that case server would perform the rollback phase.
/mysql/innobackupex_dir/2018-04-16_19-56-45/ //全量备份路径
--准备恢复(全备份)
[root@single-instance ~]# innobackupex --apply-log --redo-only /mysql/innobackupex_dir/2018-04-16_19-56-45/
180416 21:33:44 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!".
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: cd to /mysql/innobackupex_dir/2018-04-16_19-56-45/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2777815)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M: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:12M: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 __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: 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 2777815
InnoDB: Doing recovery: scanned up to log sequence number 2777824 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 2492, file name my-bin.000027
InnoDB: xtrabackup: Last MySQL binlog file position 2492, file name my-bin.000027
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2777833
InnoDB: Number of pools: 1
180416 21:33:45 completed OK!
--准备恢复(增量备份)
[root@single-instance ~]# innobackupex --apply-log /mysql/innobackupex_dir/2018-04-16_19-56-45/ --incremental-dir=/mysql/innobackupex_dir/2018-04-16_20-38-06/
180416 21:39:13 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!".
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
incremental backup from 2777815 is enabled.
xtrabackup: cd to /mysql/innobackupex_dir/2018-04-16_19-56-45/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2784815)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /mysql/innobackupex_dir/2018-04-16_20-38-06/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 54 for flydb/t_list#P#p0, old maximum was 0
xtrabackup: page size for /mysql/innobackupex_dir/2018-04-16_20-38-06//ibdata1.delta is 16384 bytes
Applying /mysql/innobackupex_dir/2018-04-16_20-38-06//ibdata1.delta to ./ibdata1...
......
xtrabackup: page size for /mysql/innobackupex_dir/2018-04-16_20-38-06//mysql/slave_master_info.ibd.delta is 16384 bytes
Applying /mysql/innobackupex_dir/2018-04-16_20-38-06//mysql/slave_master_info.ibd.delta to ./mysql/slave_master_info.ibd...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /mysql/innobackupex_dir/2018-04-16_20-38-06/
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 __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: 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 2784815
InnoDB: Doing recovery: scanned up to log sequence number 2784824 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043
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: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 2784824
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2786657
InnoDB: Number of pools: 1
180416 21:39:15 [01] Copying /mysql/innobackupex_dir/2018-04-16_20-38-06/flydb/t_list.frm to ./flydb/t_list.frm
180416 21:39:15 [01] ...done
......
180416 21:39:17 [00] Copying /mysql/innobackupex_dir/2018-04-16_20-38-06//xtrabackup_info to ./xtrabackup_info
180416 21:39:17 [00] ...done
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: 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 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2786657
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2786828
InnoDB: Doing recovery: scanned up to log sequence number 2786837 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1527, file name my-bin.000043
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: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 2786837
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2786856
180416 21:39:19 completed OK!
--全量恢复
[root@single-instance lib]# innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql/innobackupex_dir/2018-04-16_19-56-45
180416 21:49:39 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
180416 21:49:39 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
......
180416 21:49:41 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
180416 21:49:41 [01] ...done
180416 21:49:41 completed OK!
--打开mysql
[root@single-instance ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
--登录mysql
[root@single-instance ~]# mysql -uroot -p'Root123$'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
--查看flydb存在，全量备份恢复成功
mysql> use flydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
--查看全量备份后，增量备份前的数据在，增量备份恢复成功
mysql> select * from please_recovery_me_t;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
--启动库遇到的问题
[root@single-instance mysql]# chown -R mysql:mysql mysql/
[root@single-instance mysql]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@single-instance mysql]# mkdir -p /var/run/mysqld
[root@single-instance mysql]# chown mysql:mysql /var/run/mysqld
[root@single-instance mysql]# rm /tmp/mysql.sock
rm: remove regular empty file ‘/tmp/mysql.sock’ yes
如对本文有疑问， 点击进行留言回复！！
荐 Mysql2020最新教程（高级部分）之存储引擎与简单的sql优化
大型互联网必问的MySQL面试题：MySQL存储与索引+事务和锁+性能优
网友评论