mysql 第四章 数据备份与恢复（二）

环境准备：

#数据库为社区版本
[root@localhost mysql_3306]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost mysql_3306]# ./bin/mysql -uroot -p
Enter password:
#手工定义配置文件
root@localhost ~]# vim /mysql_3306/my.cnf
[root@localhost mysql_3306]# cat my.cnf 
[mysqld]
basedir=/mysql_3306
datadir=/mysql_3306/data
socket=/tmp/mysql.sock
[root@localhost ~]# service mysql_3306 restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS! 
[root@localhost mysql_3307]# ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 Source distribution

Copyright (c) 2000, 2020, 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.

mysql> exit
Bye

1.使用navicat进行逻辑备份

2.使用xtrabackup进行物理备份

全量备份

安装xtrabackup

[root@localhost ~]# ll
total 1433864
-rw-------. 1 root root       1454 Feb  7 20:40 anaconda-ks.cfg
-rw-r--r--  1 root root      43764 Mar  7 11:29 libev-4.15-3.el7.x86_64.rpm
-rw-r--r--  1 root root    7732612 Mar  7 11:29 percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh libev-4.15-3.el7.x86_64.rpm 
warning: libev-4.15-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libev-4.15-3.el7                 ################################# [100%]
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm -y

第一步：创建原始数据

mysql > create database db_it default charset=utf8;
mysql > use db_it;
mysql > create table t1(id int,name varchar(10)) engine=myisam;
mysql > insert into t1 values (1,'吕布');
mysql > create table t2(id int,name varchar(20)) engine=innodb;
mysql > insert into t2 values (1,'貂蝉');

#-------------操作
[root@localhost mysql_3307]# ./bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 Source distribution

Copyright (c) 2000, 2020, 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.

mysql>create database db_it default charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use db_it;
Database changed
mysql> create table t1(id int,name varchar(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(id int,name varchar(20)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,'貂蝉');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (1,'吕布');
Query OK, 1 row affected (0.00 sec)

第二步：开通一个备份账号，并授予权限

#开通备份用户admin,并授权
#--修改密码难度以满足数据库需求
[root@localhost mysql_3307]# ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31 Source distribution

Copyright (c) 2000, 2020, 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.

mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by 'mysql123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'MySql@1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

第三步：使用xtarbackup进行备份

[root@localhost mysql_3306]# mkdir /var/lib/mysql
[root@localhost mysql_3306]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@localhost mysql_3306]# innobackupex -S /tmp/mysql.sock --user=admin --password=mysql123 /full_xtrabackup
250307 14:39:25 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!".

250307 14:39:25  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'admin'  (using password: YES).
250307 14:39:25  version_check Connected to MySQL server
250307 14:39:25  version_check Executing a version check against the server...
250307 14:39:25  version_check Done.
250307 14:39:25 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.44
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql_3306/data/
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
250307 14:39:25 >> log scanned up to (2772669)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
250307 14:39:26 [01] Copying ./ibdata1 to /full_xtrabackup/2025-03-07_14-39-25/ibdata1
250307 14:39:26 [01]        ...done
250307 14:39:26 [01] Copying ./mysql/plugin.ibd to /full_xtrabackup/2025-03-07_14-39-25/mysql/plugin.ibd
250307 14:39:26 [01]        ...done
250307 14:39:26 [01] Copying ./mysql/servers.ibd to /full_xtrabackup/2025-03-07_14-39-25/mysql/servers.ibd
250307 14:39:26 [01]        ...done
...

第四步：预备阶段，将备份这段时间内产生的日志整合到全量备份中

[root@localhost mysql_3306]# ll /full_xtrabackup/
total 0
drwxr-x--- 7 root root 233 Mar  7 14:39 2025-03-07_14-39-25
[root@localhost mysql_3306]# innobackupex --user=admin --password=mysql123 --apply0-log /full_xtrabackup/2025-03-07_14-39-25

第五步：模拟数据库故障

 第六步：快速恢复数据库中的数据

[root@localhost mysql_3306]# rm -rf /mysql_3306/data/*
[root@localhost mysql_3306]# pkill mysqld
[root@localhost mysql_3306]# innobackupex --copy-back /full_xtrabackup/2025-03-07_14-39-25
250307 14:40:26 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.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Error: datadir must be specified.
[root@localhost mysql_3306]# innobackupex --defaults-file=/mysql_3306/my.cnf --copy-back /full_xtrabackup/2025-03-07_14-39-25
250307 14:41:08 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.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Original data directory /mysql_3306/data is not empty!
[root@localhost mysql_3306]# rm -rf /mysql_3306/data/*
[root@localhost mysql_3306]# innobackupex --defaults-file=/mysql_3306/my.cnf --copy-back /full_xtrabackup/2025-03-07_14-39-25
250307 14:41:21 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.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
250307 14:41:21 [01] Copying ibdata1 to /mysql_3306/data/ibdata1
250307 14:41:21 [01]        ...done
250307 14:41:21 [01] Copying ./mysql/plugin.ibd to /mysql_3306/data/mysql/plugin.ibd
250307 14:41:21 [01]        ...done
250307 14:41:21 [01] Copying ./mysql/servers.ibd to /mysql_3306/data/mysql/servers.ibd
250307 14:41:21 [01]        ...done
...

第七步：恢复数据库时，记得更改目录下的文件所属组以及所有者权限，否则MySQL无法启动

[root@localhost mysql_3306]# chown -R mysql.mysql /mysql_3306/data/
[root@localhost mysql_3306]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/localhost.localdomain.err'.
. SUCCESS! 
[root@localhost mysql_3306]# ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.

mysql> use databases;
ERROR 1049 (42000): Unknown database 'databases'
mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| #mysql50#2025-03-07_14-39-55 |
| db_it                        |
| mysql                        |
| performance_schema           |
| sys                          |
+------------------------------+
6 rows in set (0.00 sec)

mysql> use db_it;
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 t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 吕布   |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 貂蝉   |
+------+--------+
1 row in set (0.01 sec)

mysql> exit
Bye

增量备份

第一步：准备数据【已存在】

第二步：创建账号，用于备份【已存在】

第三步：全量备份

[root@localhost mysql_3306]# rm -rf /full_xtrabackup/*
[root@localhost mysql_3306]# innobackupex --user=admin --password=mysql123 /full_xtrabackup/
250307 15:18:01 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!".

250307 15:18:01  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
250307 15:18:01  version_check Connected to MySQL server
250307 15:18:01  version_check Executing a version check against the server...
250307 15:18:01  version_check Done.
250307 15:18:01 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Using server version 5.7.44
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql_3306/data/
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
250307 15:18:01 >> log scanned up to (2768433)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
250307 15:18:01 [01] Copying ./ibdata1 to /full_xtrabackup/2025-03-07_15-18-01/ibdata1
250307 15:18:01 [01]        ...done
...

第四步：把全备过程中产生的日志进行整合

[root@localhost mysql_3306]# innobackupex --user=admin --password=mysql123 --apply-log --redo-only /full_xtrabackup/2025-03-07_15-18-01

第五步：创增量数据

mysql> use db_it;
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> insert into db_it.t1 values (2,'王朗');
Query OK, 1 row affected (0.01 sec)

mysql> insert into db_it.t1 values (3,'袁术');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 吕布   |
|    2 | 王朗   |
|    3 | 袁术   |
+------+--------+
3 rows in set (0.00 sec)

 第六步：做增量备份（指定基于某一个全量备份）

[root@localhost mysql_3306]# innobackupex --user=admin --password=mysql123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/2025-03-07_15-18-01
250307 15:35:16 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!".

250307 15:35:16  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
250307 15:35:16  version_check Connected to MySQL server
250307 15:35:16  version_check Executing a version check against the server...
250307 15:35:16  version_check Done.
250307 15:35:16 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Using server version 5.7.44
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
incremental backup from 2768424 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql_3306/data/
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
250307 15:35:16 >> log scanned up to (2768433)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
250307 15:35:16 [01] Copying ./ibdata1 to /incre_backup/2025-03-07_15-35-16/ibdata1.delta
250307 15:35:16 [01]        ...done
...
[root@localhost mysql_3306]# ll /incre_backup/
total 0
drwxr-x--- 2 root root   6 Mar  7 15:32 2025-03-07_15-32-51
drwxr-x--- 6 root root 232 Mar  7 15:35 2025-03-07_15-35-16

第七步：将增量备份产生的数据以及日志文件与全量备份进行整合

...
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: 5.7.13 started; log sequence number 2768917
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2768945
250307 15:41:32 completed OK!
[root@localhost mysql_3306]# innobackupex --user=admin --password=mysql123 --apply-log /full_xtrabackup/2025-03-07_15-18-01 --incremental-dir=/incre_backup/2025-03-07_15-35-16

第八步：数据库故障

[root@localhost mysql_3306]# rm -rf /mysql_3306/data/*
[root@localhost mysql_3306]# pkill mysqld

第九步：恢复

[root@localhost mysql_3306]# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=mysql123 --copy-back /full_xtrabackup/2025-03-07_15-18-01
250307 15:49:34 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.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Original data directory /mysql_3306/data is not empty!
[root@localhost mysql_3306]# rm -rf /mysql_3306/data/*
[root@localhost mysql_3306]# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=mysql123 --copy-back /full_xtrabackup/2025-03-07_15-18-01
[root@localhost mysql_3306]# chown -R mysql.mysql /mysql_3306/data/
[root@localhost mysql_3306]# service mysql_3306 start
Starting MySQL.Logging to '/mysql_3306/data/localhost.localdomain.err'.
. SUCCESS! 
[root@localhost mysql_3306]# 
[root@localhost mysql_3306]# ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.

mysql> use db_it;
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 t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 吕布   |
|    2 | 王朗   |
|    3 | 袁术   |
+------+--------+
3 rows in set (0.01 sec)

mysql> exit
Bye