做电器的集团网站,新型建站技术,沙田镇网站建设,盐田做网站的公司一、物理复制的基本概念 物理备份:直接复制数据库文件#xff0c;适用于大型的数据库环境#xff0c;不受存储引擎的限制#xff0c;但不能恢复到不同的mysql版本 完整备份#xff1a;也叫完全备份#xff0c;每次将所有数据#xff08;不管自第一次备份有没有修改过适用于大型的数据库环境不受存储引擎的限制但不能恢复到不同的mysql版本 完整备份也叫完全备份每次将所有数据不管自第一次备份有没有修改过进行一次完整的复制备份后会清楚文件的存档属性方便日后增量备份或者差异备份进行版本比较。 特点占用空间大备份速度慢但是恢复时一次恢复到位恢复速度快 增量备份每次备份上一次备份到现在产生的数据 在第一次完整备份后第二次开始每次都添加了存档属性并在备份后将存档属性清除为了在下一次备份时文档是否有变化因为用户在每次备份以后修改清除存档属性的文件存档属性就会自动加上告诉系统这些文件有变化下一次备份这些文件这就是增加备份的工作机制 特点备份体积小备份速度快但是恢复的时候需要按备份的时间顺序逐个备份版本进行恢复恢复时间长。 差异备份只备份和完整备份不一样的 特点占用空间的增量备份大比完整备份小恢复时仅需恢复第一个完整版和最后一个差异版恢复速度介于完整备份和增量备份之间 二、安装xtrabackup 下载安装包并解压安装
三、完全备份和数据恢复
1.创建备份目录
[rootlocalhost ~]# mkdir /xt/full -p2.备份 语法innobackupx --user用户 --password密码 备份目录 [rootlocalhost yum.repos.d]# innobackupex --userroot --password123 /xtrabackup/full
xtrabackup: recognized server arguments: --server-id1 --datadir/var/lib/mysql --log_bin/var/lib/mysql/mysql-bin.log
xtrabackup: recognized client arguments:
231006 11:46:47 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints completed OK!.231006 11:46:48 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup as root (using password: YES)..
.
.231006 11:46:49 [00] ...done
xtrabackup: Transaction log of lsn (3698047) to (3698056) was copied.
231006 11:46:49 completed OK!
3.查看备份文件
[rootlocalhost ~]# cd /xt/full
[rootlocalhost full]# ls2023-10-06_11-46-47
[rootlocalhost full]# cd 2023-10-06_11-46-47/
[rootlocalhost 2023-10-06_11-46-47]# ls
backup-my.cnf hf mysql sys xtrabackup_info
db1 ib_buffer_pool performance_schema xtrabackup_binlog_info xtrabackup_logfile
db3 ibdata1 school xtrabackup_checkpoints
4.完全备份恢复数据
1关闭数据库
[rootlocalhost ~]# systemctl stop mysqld
[rootlocalhost ~]# rm -rf /var/lib/mysql/*
2恢复之前的验证
[rootlocalhost ~]# innobackupex --apply-log /xt/full/2023-10-06_11-46-47/
xtrabackup: recognized server arguments: --innodb_checksum_algorithmcrc32 --
.
innodb_undo_tablespaces0 --server-id1 --redo-log-version1
xtrabackup: recognized client arguments:
231006 12:35:55 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /xt/full/2023-10-06_11-46-47/
.
.
231006 12:35:59 completed OK!
3查看配置文件确认数据库恢复目录
[rootlocalhost ~]# vim /etc/my.cnfdatadir/var/lib/mysql
4恢复数据
[rootlocalhost ~]# innobackupex --copy-back /xt/full/2023-10-06_11-46-47/
xtrabackup: recognized server arguments: --server-id1 --datadir/var/lib/mysql --log_bin/var/lib/mysql/mysql-bin.log
xtrabackup: recognized client arguments:
231006 12:38:02 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully.At the end of a successful copy-back run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
231006 12:38:02 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
.
.
231006 12:38:02 [01] ...done
231006 12:38:02 completed OK!
5修改权限
# chown mysql.mysql /var/lib/mysql -R
6启动数据库
# systemctl start mysqld
四、增量备份和数据恢复
1.先完整备份周一 语法innobackupex --user用户 --password密码 备份路径 [rootlocalhost log]# innobackupex --userroot --password123 /opt/full
xtrabackup: recognized server arguments: --server-id1 --log_bin/opt/log/mysql-bin.log --datadir/var/lib/mysql
xtrabackup: recognized client arguments:
231007 21:05:07 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints completed OK!.231007 21:05:07 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup as root (using password: YES).
231007 21:05:07 version_check Connected to MySQL server
231007 21:05:07 version_check Executing a version check against the server...
231007 21:05:07 version_check Done.
231007 21:05:07 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3080362) to (3080371) was copied.
231007 21:05:08 completed OK![rootlocalhost notfull]# cd /opt/full
[rootlocalhost full]# ls
2023-10-07_21-05-07
2.增量备份(周二) 语法 innobackupex --user用户 --password密码 --incremental 备份路径 --incremental-basedir完整备份的文件周一 [rootlocalhost log]# innobackupex --userroot --password123 --incremental /opt/notfull --incremental-basedir/opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --server-id1 --log_bin/opt/log/mysql-bin.log --datadir/var/lib/mysql
xtrabackup: recognized client arguments:
231007 21:16:51 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints completed OK!.231007 21:16:51 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup as root (using password: YES).
231007 21:16:51 version_check Connected to MySQL server
231007 21:16:51 version_check Executing a version check against the server...
231007 21:16:51 version_check Done.
231007 21:16:51 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080362 is enabled.
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3080717) to (3080726) was copied.
231007 21:16:52 completed OK![rootlocalhost log]# cd /opt/notfull
[rootlocalhost notfull]# ls
2023-10-07_21-16-51
3.增量备份周三 语法innobackupex --user用户 --password密码 --incremental 备份的路径 --incremental-basedir上次备份的文件周二 [rootlocalhost full]# innobackupex --userroot --password123 --incremental /opt/notfull --incremental-basedir/opt/notfull/2023-10-07_21-16-51/
xtrabackup: recognized server arguments: --server-id1 --log_bin/opt/log/mysql-bin.log --datadir/var/lib/mysql
xtrabackup: recognized client arguments:
231007 21:22:21 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints completed OK!.231007 21:22:21 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup as root (using password: YES).
231007 21:22:21 version_check Connected to MySQL server
231007 21:22:21 version_check Executing a version check against the server...
231007 21:22:21 version_check Done.
231007 21:22:21 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080717 is enabled.
xtrabackup: uses posix_fadvise()
.
.
xtrabackup: Transaction log of lsn (3081072) to (3081081) was copied.
231007 21:22:22 completed OK![rootlocalhost full]# cd /opt/notfull
[rootlocalhost notfull]# ls
2023-10-07_21-16-51 2023-10-07_21-22-21
4.增量备份恢复流程
1.关闭数据库
[rootlocalhost ~]# systemctl stop mysqld2.清理环境生产环境不可用
[rootlocalhost ~]# rm -rf /var/lib/mysql/*
3.依次重演回滚
1重演周一
innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --innodb_checksum_algorithmcrc32 --innodb_log_checksum_algorithmstrict_crc32 --innodb_data_file_pathibdata1:12M:autoextend --innodb_log_files_in_group2 --innodb_log_file_size50331648 --innodb_fast_checksum0 --innodb_page_size16384 --innodb_log_block_size512 --innodb_undo_directory./ --innodb_undo_tablespaces0 --server-id1 --redo-log-version1
xtrabackup: recognized client arguments:
231007 21:26:59 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size8388608, start_lsn(3080362)
xtrabackup: using the following InnoDB configuration for recovery:
.
.
InnoDB: Number of pools: 1
231007 21:27:00 completed OK!
(2)重演周二
[rootlocalhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir/opt/notfull/2023-10-07_21-16-51/
xtrabackup: recognized server arguments: --innodb_checksum_algorithmcrc32 --innodb_log_checksum_algorithmstrict_crc32 --innodb_data_file_pathibdata1:12M:autoextend --innodb_log_files_in_group2 --innodb_log_file_size50331648 --innodb_fast_checksum0 --innodb_page_size16384 --innodb_log_block_size512 --innodb_undo_directory./ --innodb_undo_tablespaces0 --server-id1 --redo-log-version1
xtrabackup: recognized client arguments:
231007 21:27:49 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080362 is enabled.
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size8388608, start_lsn(3080717)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir .
.
.
231007 21:27:51 [00] ...done
231007 21:27:51 completed OK!
(3)回滚周三 [rootlocalhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir/opt/notfull/2023-10-07_21-22-21/
xtrabackup: recognized server arguments: --innodb_checksum_algorithmcrc32 --innodb_log_checksum_algorithmstrict_crc32 --innodb_data_file_pathibdata1:12M:autoextend --innodb_log_files_in_group2 --innodb_log_file_size50331648 --innodb_fast_checksum0 --innodb_page_size16384 --innodb_log_block_size512 --innodb_undo_directory./ --innodb_undo_tablespaces0 --server-id1 --redo-log-version1
xtrabackup: recognized client arguments:
231007 21:28:19 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 3080717 is enabled.
xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size8388608, start_lsn(3081072)
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 /opt/notfull/2023-10-07_21-22-21/
xtrabackup: innodb_log_files_in_group 1
xtrabackup: innodb_log_file_size 8388608
xtrabackup: Generating a list of tablespaces
.
.
231007 21:28:20 [00] ...done
231007 21:28:20 completed OK!
(4)重演恢复数据
[rootlocalhost ~]# innobackupex --copy-back /opt/full/2023-10-07_21-05-07/
xtrabackup: recognized server arguments: --server-id1 --log_bin/opt/log/mysql-bin.log --datadir/var/lib/mysql
xtrabackup: recognized client arguments:
231007 21:28:57 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully.At the end of a successful copy-back run innobackupexprints completed OK!.innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
231007 21:28:57 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
231007 21:28:57 [01] ...done.
.
231007 21:28:57 [01] ...done
231007 21:28:57 completed OK!
4.修改权限
[rootlocalhost ~]# chown -R mysql.mysql /var/lib/mysql/*
5.重启mysqld
[rootlocalhost ~]# systemctl start mysqld
五、差异备份和数据恢复 环境准备
mysql create table db3(id int,name varchar(30),time varchar(30));
Query OK, 0 rows affected (0.01 sec)mysql insert into db3 values(1,xiaoliu,星期一);
Query OK, 1 row affected (0.00 sec)[rootlocalhost ~]# rm -rf /opt/full
1.完整备份周一 语法innobackupex --user用户 --password密码 完整备份的路径 [rootlocalhost ~]# innobackupex --userroot --password123 /opt/full
xtrabackup: recognized server arguments: --server-id1 --log_bin/opt/log/mysql-bin.log --datadir/var/lib/mysql
xtrabackup: recognized client arguments:
231007 23:06:28 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.At the end of a successful backup run innobackupexprints completed OK!.231007 23:06:28 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup as root (using password: YES).
231007 23:06:28 version_check Connected to MySQL server
231007 23:06:28 version_check Executing a version check against the server...
231007 23:06:28 version_check Done.
231007 23:06:28 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.43-log
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
.
.
xtrabackup: Transaction log of lsn (3085687) to (3085696) was copied.
231007 23:06:30 completed OK!
2.差异备份周二
mysql insert into db.db3 values (2,xiaozhao,星期二); 语法innobackupex --userroot --password密码 --incremental 备份路径 --incremental-basedir完整备份的路径第一天 [rootlocalhost ~]# innobackupex --userroot --password123 --incremental /opt/diff --incremental-basedir/opt/full/2023-10-07_23-06-28/
.
.
xtrabackup: Transaction log of lsn (3086067) to (3086076) was copied.
231007 23:12:31 completed OK!
3.差异备份周三
mysql insert into db.db3 values(3,xiaozhang,星期三);
Query OK, 1 row affected (0.00 sec) 语法innobackupex --user用户 --password密码 --incremental 备份路径 --incremental-basedir完整备份的路径周一 [rootlocalhost ~]# innobackupex --userroot --password123 --incremental /opt/diff --incremental-basedir/opt/full/2023-10-07_23-06-28/
.
.
xtrabackup: Transaction log of lsn (3086448) to (3086457) was copied.
231007 23:17:24 completed OK! 4.数据恢复
mysql drop table db3;
Query OK, 0 rows affected (0.01 sec)1停止数据库
[rootlocalhost ~]# systemctl stop mysqld
2清理环境
[rootlocalhost ~]# rm -rf /var/lib/mysql/*
3重演
重演周一
[rootlocalhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/
重演周三
[rootlocalhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/ --incremental-dir /opt/diff/2023-10-07_23-12-29/
.
.
231007 23:40:37 [00] ...done
231007 23:40:37 completed OK!
回滚
[rootlocalhost ~]# innobackupex --copy-back /opt/full/2023-10-07_23-06-28/
..
231007 23:41:07 [01] ...done
231007 23:41:07 completed OK!
(4)修改权限
[rootlocalhost ~]# chown -R mysql.mysql /var/lib/mysql/*
(5)重启服务
[rootlocalhost ~]# systemctl start mysqld
