MySQL备份及恢复
文章目录
1.Percona 介绍
Percona Server由领先的MySQL咨询公司Percona发布。 Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB 。
Percona团队的最终声明是“Percona Server是由Oracle发布的最接近官方MySQL Enterprise发行版的版本”，因此与其他更改了大量基本核心MySQL代码的分支有所区别。 Percona Server的一个缺点是他们自己管理代码，不接受外部开发人员的贡献，以这种方式确保他们对产品中所包含功能的控制。
Percona提供了高性能XtraDB引擎，还提供PXC高可用解决方案，并且附带了perconatoolkit等DBA管理工具箱
percona官网地址：https://www.percona.com/
2.安装Percona 需要的 MySQL 包
mysql官方源
[root@mysql-server ~]#yum install -y mysql80-community-release-el7-7.noarch.rpm
yum工具
[root@mysql-server ~]# yum install -y yum-utils
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
mysql57 | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
zabbix | 2.9 kB 00:00:00
zabbix-frontend | 2.9 kB 00:00:00
zabbix-non-supported | 2.9 kB 00:00:00
(1/2): epel/x86_64/updateinfo | 1.0 MB 00:00:13
(2/2): epel/x86_64/primary_db | 7.0 MB 00:01:15
软件包 yum-utils-1.1.31-54.el7_8.noarch 已安装并且是最新版本
无须任何处理
禁用mysql80
[root@mysql-server ~]# yum-config-manager --disable mysql80-community
已加载插件：fastestmirror, langpacks
启用mysql57
[root@mysql-server ~]# yum-config-manager --enable mysql57-community
已加载插件：fastestmirror, langpacks
查看是否安装有 mysql-community-libs-compat.x86_64 ，如果没有安装则需要安装
[root@mysql-server ~]# yum list |grep mysql-community-libs
mysql-community-libs.x86_64 5.7.40-1.el7 @mysql57
mysql-community-libs-compat.x86_64 5.7.40-1.el7 @mysql57
[root@mysql-server ~]# yum install mysql-community-libs-compat -y
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
软件包 mysql-community-libs-compat-5.7.40-1.el7.x86_64 已安装并且是最新版本
无须任何处理
3.安装percona-xtrabackup
安装Percona
[root@mysql-server 桌面]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
已加载插件：fastestmirror, langpacks
percona-release-latest.noarch.rpm | 20 kB 00:00:00
正在检查 /var/tmp/yum-root-wGNiWD/percona-release-latest.noarch.rpm: percona-release-1.0-27.noarch
/var/tmp/yum-root-wGNiWD/percona-release-latest.noarch.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 percona-release.noarch.0.1.0-27 将被 安装
--> 解决依赖关系完成
依赖关系解决
===========================================================================================
Package 架构 版本 源 大小
===========================================================================================
正在安装:
percona-release noarch 1.0-27 /percona-release-latest.noarch 32 k
事务概要
===========================================================================================
安装 1 软件包
总计：32 k
安装大小：32 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : percona-release-1.0-27.noarch 1/1
* Enabling the Percona Original repository
<*> All done!
* Enabling the Percona Release repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
https://www.percona.com/doc/percona-repo-config/percona-release.html
验证中 : percona-release-1.0-27.noarch 1/1
已安装:
percona-release.noarch 0:1.0-27
完毕！
[root@mysql-server 桌面]# yum -y install percona-xtrabackup-24.x86_64
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
正在解决依赖关系
--> 正在检查事务
---> 软件包 percona-xtrabackup-24.x86_64.0.2.4.26-1.el7 将被 安装
--> 正在处理依赖关系 perl(Digest::MD5)，它被软件包 percona-xtrabackup-24-2.4.26-1.el7.x86_64 需要
--> 正在处理依赖关系 libev.so.4()(64bit)，它被软件包 percona-xtrabackup-24-2.4.26-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 libev.x86_64.0.4.15-7.el7 将被 安装
---> 软件包 perl-Digest-MD5.x86_64.0.2.52-3.el7 将被 安装
--> 正在处理依赖关系 perl(Digest::base) >= 1.00，它被软件包 perl-Digest-MD5-2.52-3.el7.x86_64 需要
--> 正在检查事务
---> 软件包 perl-Digest.noarch.0.1.17-245.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
===========================================================================================
Package 架构 版本 源 大小
===========================================================================================
正在安装:
percona-xtrabackup-24 x86_64 2.4.26-1.el7 percona-release-x86_64 7.6 M
为依赖而安装:
libev x86_64 4.15-7.el7 extras 44 k
perl-Digest noarch 1.17-245.el7 base 23 k
perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k
事务概要
===========================================================================================
安装 1 软件包 (+3 依赖软件包)
总下载量：7.7 M
安装大小：7.8 M
Downloading packages:
(1/4): perl-Digest-1.17-245.el7.noarch.rpm | 23 kB 00:00:02
(2/4): perl-Digest-MD5-2.52-3.el7.x86_64.rpm | 30 kB 00:00:02
(3/4): libev-4.15-7.el7.x86_64.rpm | 44 kB 00:00:03
warning: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm 的公钥尚未安装
(4/4): percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm | 7.6 MB 00:00:13
-------------------------------------------------------------------------------------------
总计 594 kB/s | 7.7 MB 00:00:13
从 file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY 检索密钥
导入 GPG key 0x8507EFA5:
用户ID : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
指纹 : 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
软件包 : percona-release-1.0-27.noarch (@/percona-release-latest.noarch)
来自 : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : libev-4.15-7.el7.x86_64 1/4
正在安装 : perl-Digest-1.17-245.el7.noarch 2/4
正在安装 : perl-Digest-MD5-2.52-3.el7.x86_64 3/4
正在安装 : percona-xtrabackup-24-2.4.26-1.el7.x86_64 4/4
验证中 : perl-Digest-1.17-245.el7.noarch 1/4
验证中 : perl-Digest-MD5-2.52-3.el7.x86_64 2/4
验证中 : libev-4.15-7.el7.x86_64 3/4
验证中 : percona-xtrabackup-24-2.4.26-1.el7.x86_64 4/4
已安装:
percona-xtrabackup-24.x86_64 0:2.4.26-1.el7
作为依赖被安装:
libev.x86_64 0:4.15-7.el7 perl-Digest.noarch 0:1.17-245.el7
perl-Digest-MD5.x86_64 0:2.52-3.el7
完毕！
查询安装结果
[root@mysql-server 桌面]# yum list |grep percona-xtrabackup
percona-xtrabackup-24.x86_64 2.4.26-1.el7 @percona-release-x86_64
percona-xtrabackup.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-24-debuginfo.x86_64 2.4.26-1.el7 percona-release-x86_64
percona-xtrabackup-80.x86_64 8.0.29-22.1.el7 percona-release-x86_64
percona-xtrabackup-80-debuginfo.x86_64 8.0.29-22.1.el7 percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-test-24.x86_64 2.4.26-1.el7 percona-release-x86_64
percona-xtrabackup-test-80.x86_64 8.0.29-22.1.el7 percona-release-x86_64
[root@mysql-server 桌面]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.26
/usr/share/doc/percona-xtrabackup-24-2.4.26/LICENSE
/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
[root@mysql-server 桌面]# yum list |grep percona-xtrabackup
percona-xtrabackup-24.x86_64 2.4.26-1.el7 @percona-release-x86_64
percona-xtrabackup.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-24-debuginfo.x86_64 2.4.26-1.el7 percona-release-x86_64
percona-xtrabackup-80.x86_64 8.0.29-22.1.el7 percona-release-x86_64
percona-xtrabackup-80-debuginfo.x86_64 8.0.29-22.1.el7 percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test.x86_64 2.3.10-1.el7 percona-release-x86_64
percona-xtrabackup-test-22.x86_64 2.2.13-1.el7 percona-release-x86_64
percona-xtrabackup-test-24.x86_64 2.4.26-1.el7 percona-release-x86_64
percona-xtrabackup-test-80.x86_64 8.0.29-22.1.el7 percona-release-x86_64
4.完全备份流程
[root@mysql-server ~]# mysql_upgrade -uroot -p123456 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: (non fatal) [ERROR] 1728: Cannot load from mysql.proc. The table is probably corrupted
mysql_upgrade: (non fatal) [ERROR] 1545: Failed to open mysql.event
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
zabbix.acknowledges OK
zabbix.actions OK
zabbix.alerts OK
zabbix.application_discovery OK
zabbix.application_prototype OK
zabbix.application_template OK
zabbix.applications OK
zabbix.auditlog OK
zabbix.auditlog_details OK
zabbix.autoreg_host OK
zabbix.conditions OK
zabbix.config OK
zabbix.config_autoreg_tls OK
zabbix.corr_condition OK
zabbix.corr_condition_group OK
zabbix.corr_condition_tag OK
zabbix.corr_condition_tagpair OK
zabbix.corr_condition_tagvalue OK
zabbix.corr_operation OK
zabbix.correlation OK
zabbix.dashboard OK
zabbix.dashboard_user OK
zabbix.dashboard_usrgrp OK
zabbix.dbversion OK
zabbix.dchecks OK
zabbix.dhosts OK
zabbix.drules OK
zabbix.dservices OK
zabbix.escalations OK
zabbix.event_recovery OK
zabbix.event_suppress OK
zabbix.event_tag OK
zabbix.events OK
zabbix.expressions OK
zabbix.functions OK
zabbix.globalmacro OK
zabbix.globalvars OK
zabbix.graph_discovery OK
zabbix.graph_theme OK
zabbix.graphs OK
zabbix.graphs_items OK
zabbix.group_discovery OK
zabbix.group_prototype OK
zabbix.history OK
zabbix.history_log OK
zabbix.history_str OK
zabbix.history_text OK
zabbix.history_uint OK
zabbix.host_discovery OK
zabbix.host_inventory OK
zabbix.host_tag OK
zabbix.hostmacro OK
zabbix.hosts OK
zabbix.hosts_groups OK
zabbix.hosts_templates OK
zabbix.housekeeper OK
zabbix.hstgrp OK
zabbix.httpstep OK
zabbix.httpstep_field OK
zabbix.httpstepitem OK
zabbix.httptest OK
zabbix.httptest_field OK
zabbix.httptestitem OK
zabbix.icon_map OK
zabbix.icon_mapping OK
zabbix.ids OK
zabbix.images OK
zabbix.interface OK
zabbix.interface_discovery OK
zabbix.interface_snmp OK
zabbix.item_application_prototype OK
zabbix.item_condition OK
zabbix.item_discovery OK
zabbix.item_preproc OK
zabbix.item_rtdata OK
zabbix.items OK
zabbix.items_applications OK
zabbix.lld_macro_path OK
zabbix.lld_override OK
zabbix.lld_override_condition OK
zabbix.lld_override_opdiscover OK
zabbix.lld_override_operation OK
zabbix.lld_override_ophistory OK
zabbix.lld_override_opinventory OK
zabbix.lld_override_opperiod OK
zabbix.lld_override_opseverity OK
zabbix.lld_override_opstatus OK
zabbix.lld_override_optag OK
zabbix.lld_override_optemplate OK
zabbix.lld_override_optrends OK
zabbix.maintenance_tag OK
zabbix.maintenances OK
zabbix.maintenances_groups OK
zabbix.maintenances_hosts OK
zabbix.maintenances_windows OK
zabbix.mappings OK
zabbix.media OK
zabbix.media_type OK
zabbix.media_type_message OK
zabbix.media_type_param OK
zabbix.module OK
zabbix.opcommand OK
zabbix.opcommand_grp OK
zabbix.opcommand_hst OK
zabbix.opconditions OK
zabbix.operations OK
zabbix.opgroup OK
zabbix.opinventory OK
zabbix.opmessage OK
zabbix.opmessage_grp OK
zabbix.opmessage_usr OK
zabbix.optemplate OK
zabbix.problem OK
zabbix.problem_tag OK
zabbix.profiles OK
zabbix.proxy_autoreg_host OK
zabbix.proxy_dhistory OK
zabbix.proxy_history OK
zabbix.regexps OK
zabbix.rights OK
zabbix.screen_user OK
zabbix.screen_usrgrp OK
zabbix.screens OK
zabbix.screens_items OK
zabbix.scripts OK
zabbix.service_alarms OK
zabbix.services OK
zabbix.services_links OK
zabbix.services_times OK
zabbix.sessions OK
zabbix.slides OK
zabbix.slideshow_user OK
zabbix.slideshow_usrgrp OK
zabbix.slideshows OK
zabbix.sysmap_element_trigger OK
zabbix.sysmap_element_url OK
zabbix.sysmap_shape OK
zabbix.sysmap_url OK
zabbix.sysmap_user OK
zabbix.sysmap_usrgrp OK
zabbix.sysmaps OK
zabbix.sysmaps_elements OK
zabbix.sysmaps_link_triggers OK
zabbix.sysmaps_links OK
zabbix.tag_filter OK
zabbix.task OK
zabbix.task_acknowledge OK
zabbix.task_check_now OK
zabbix.task_close_problem OK
zabbix.task_data OK
zabbix.task_remote_command OK
zabbix.task_remote_command_result OK
zabbix.task_result OK
zabbix.timeperiods OK
zabbix.trends OK
zabbix.trends_uint OK
zabbix.trigger_depends OK
zabbix.trigger_discovery OK
zabbix.trigger_tag OK
zabbix.triggers OK
zabbix.users OK
zabbix.users_groups OK
zabbix.usrgrp OK
zabbix.valuemaps OK
zabbix.widget OK
zabbix.widget_field OK
Upgrade process completed successfully.
Checking if update is needed.
[root@mysql-server ~]# systemctl restart mysqld
[root@mysql-server ~]# innobackupex --user=root --password='123456' /xtrabackup/full
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221105 21:04:58 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!".
221105 21:04:58 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221105 21:04:58 version_check Connected to MySQL server
221105 21:04:58 version_check Executing a version check against the server...
221105 21:04:58 version_check Done.
221105 21:04:58 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
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
221105 21:04:58 >> log scanned up to (87785422)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 137 for mysql/plugin, old maximum was 0
221105 21:04:59 [01] Copying ./ibdata1 to /xtrabackup/full/2022-11-05_21-04-58/ibdata1
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/plugin.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/plugin.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/servers.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/servers.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/time_zone_name.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/time_zone_name.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/help_topic.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/help_topic.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/help_category.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/help_category.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/help_keyword.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/help_keyword.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/help_relation.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/help_relation.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/time_zone.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/time_zone.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/time_zone_transition.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/time_zone_transition.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/time_zone_transition_type.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/time_zone_transition_type.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/time_zone_leap_second.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/time_zone_leap_second.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/innodb_table_stats.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/innodb_table_stats.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/innodb_index_stats.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/innodb_index_stats.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/slave_relay_log_info.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/slave_relay_log_info.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/slave_master_info.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/slave_master_info.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/slave_worker_info.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/slave_worker_info.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/gtid_executed.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/gtid_executed.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/server_cost.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/server_cost.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./mysql/engine_cost.ibd to /xtrabackup/full/2022-11-05_21-04-58/mysql/engine_cost.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 [01] Copying ./sys/sys_config.ibd to /xtrabackup/full/2022-11-05_21-04-58/sys/sys_config.ibd
221105 21:04:59 [01] ...done
221105 21:04:59 >> log scanned up to (87785422)
221105 21:05:00 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
221105 21:05:00 Executing FLUSH TABLES WITH READ LOCK...
221105 21:05:00 Starting to backup non-InnoDB tables and files
221105 21:05:00 [01] Copying ./mysql/slow_log.CSM to /xtrabackup/full/2022-11-05_21-04-58/mysql/slow_log.CSM
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/func.MYD to /xtrabackup/full/2022-11-05_21-04-58/mysql/func.MYD
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/host.frm to /xtrabackup/full/2022-11-05_21-04-58/mysql/host.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/host.MYI to /xtrabackup/full/2022-11-05_21-04-58/mysql/host.MYI
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/host.MYD to /xtrabackup/full/2022-11-05_21-04-58/mysql/host.MYD
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/func.MYI to /xtrabackup/full/2022-11-05_21-04-58/mysql/func.MYI
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/func.frm to /xtrabackup/full/2022-11-05_21-04-58/mysql/func.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/slow_log.frm to /xtrabackup/full/2022-11-05_21-04-58/mysql/slow_log.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/general_log.CSM to /xtrabackup/full/2022-11-05_21-04-58/mysql/general_log.CSM
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/procs_priv.MYI to /xtrabackup/full/2022-11-05_21-04-58/mysql/procs_priv.MYI
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/columns_priv.MYI to /xtrabackup/full/2022-11-05_21-04-58/mysql/columns_priv.MYI
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/columns_priv.MYD to /xtrabackup/full/2022-11-05_21-04-58/mysql/columns_priv.MYD
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/columns_priv.frm to /xtrabackup/full/2022-11-05_21-04-58/mysql/columns_priv.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./mysql/proxies_priv.MYD to /xtrabackup/full/2022-11-05_21-04-58/mysql/proxies_priv.MYD
221105 21:05:00 [01] Copying ./zabbix/acknowledges.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/acknowledges.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./zabbix/problem_tag.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/problem_tag.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./zabbix/media_type_param.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/media_type_param.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./zabbix/users_groups.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/users_groups.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./zabbix/operations.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/operations.frm
221105 21:05:00 [01] ...done
221105 21:05:00 [01] Copying ./zabbix/opcommand_grp.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/opcommand_grp.frm
221105 21:05:00 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/trigger_depends.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/trigger_depends.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/hostmacro.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/hostmacro.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/media.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/media.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/history_str.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/history_str.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/sessions.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/sessions.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/httptest.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/httptest.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/maintenances_windows.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/maintenances_windows.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/interface_discovery.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/interface_discovery.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/httpstep.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/httpstep.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/opmessage_usr.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/opmessage_usr.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/opconditions.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/opconditions.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/graphs.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/graphs.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/hosts_groups.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/hosts_groups.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/history_log.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/history_log.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/task.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/task.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/interface.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/interface.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/trends.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/trends.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/item_rtdata.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/item_rtdata.frm
、
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/httptest_field.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/httptest_field.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/httpstep_field.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/httpstep_field.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/dashboard.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/dashboard.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/dashboard_user.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/dashboard_user.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/dashboard_usrgrp.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/dashboard_usrgrp.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/event_suppress.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/event_suppress.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/lld_override_opdiscover.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/lld_override_opdiscover.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/lld_override_ophistory.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/lld_override_ophistory.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./zabbix/lld_override_opseverity.frm to /xtrabackup/full/2022-11-05_21-04-58/zabbix/lld_override_opseverity.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/events_waits_summary_by_thread_by_event_name.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/events_waits_summary_global_by_event_name.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/file_instances.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/file_instances.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/file_summary_by_event_name.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/file_summary_by_instance.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/file_summary_by_instance.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/socket_instances.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/socket_instances.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /xtrabackup/full/2022-11-05_21-04-58/performance_schema/socket_summary_by_instance.frm
221105 21:05:01 [01] ...done
221105 21:05:01 [01] Copying
221105 21:05:02 [01] Copying ./sys/user_summary_by_statement_type.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/user_summary_by_statement_type.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/ps_check_lost_instrumentation.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/ps_check_lost_instrumentation.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/latest_file_io.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/latest_file_io.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/waits_by_host_by_latency.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/waits_by_host_by_latency.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/x@0024latest_file_io.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/x@0024latest_file_io.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/waits_global_by_latency.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/waits_global_by_latency.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/io_global_by_file_by_latency.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/io_global_by_file_by_latency.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/schema_table_statistics.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/schema_table_statistics.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/x@0024io_global_by_file_by_latency.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/x@0024io_global_by_file_by_latency.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/x@0024user_summary.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/x@0024user_summary.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/io_global_by_wait_by_bytes.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/io_global_by_wait_by_bytes.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/x@0024schema_table_statistics.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/x@0024schema_table_statistics.frm
221105 21:05:02 [01] ...done
221105 21:05:02 [01] Copying ./sys/x@0024io_global_by_wait_by_bytes.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/x@0024io_global_by_wait_by_bytes.frm
221105 21:05:02 [01] Copying ./sys/session_ssl_status.frm to /xtrabackup/full/2022-11-05_21-04-58/sys/session_ssl_status.frm
221105 21:05:02 [01] ...done
221105 21:05:02 Finished backing up non-InnoDB tables and files
221105 21:05:02 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '87785413'
xtrabackup: Stopping log copying thread.
.221105 21:05:02 >> log scanned up to (87785422)
221105 21:05:02 Executing UNLOCK TABLES
221105 21:05:02 All tables unlocked
221105 21:05:02 [00] Copying ib_buffer_pool to /xtrabackup/full/2022-11-05_21-04-58/ib_buffer_pool
221105 21:05:02 [00] ...done
221105 21:05:02 Backup created in directory '/xtrabackup/full/2022-11-05_21-04-58/'
221105 21:05:02 [00] Writing /xtrabackup/full/2022-11-05_21-04-58/backup-my.cnf
221105 21:05:02 [00] ...done
221105 21:05:02 [00] Writing /xtrabackup/full/2022-11-05_21-04-58/xtrabackup_info
221105 21:05:02 [00] ...done
xtrabackup: Transaction log of lsn (87785413) to (87785422) was copied.
221105 21:05:03 completed OK!
切换到 /xtrabackup/full目录下，查看备份目录。数据库，配置文件，日志文件
[root@mysql-server ~]# cd /xtrabackup/full
[root@mysql-server full]# ls
2022-11-05_21-04-58
[root@mysql-server full]# ls /xtrabackup/full/2022-11-05_21-04-58
backup-my.cnf ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
ib_buffer_pool mysql sys xtrabackup_info zabbix
观察二进制日志位置
[root@mysql-server full]# cat /xtrabackup/full/2022-11-05_14-26-08/xtrabackup_binlog_info
5.完全恢复流程
停止数据库
[root@mysql-server full]# systemctl stop mysqld
[root@mysql-server full]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 六 2022-11-05 14:45:58 CST; 10s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 7794 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 7012 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 7811 (code=exited, status=0/SUCCESS)
11月 04 17:56:17 zabbix-proxy systemd[1]: Starting MySQL Server...
11月 04 17:56:41 zabbix-proxy systemd[1]: Started MySQL Server.
11月 05 14:45:56 mysql-server systemd[1]: Stopping MySQL Server...
11月 05 14:45:58 mysql-server systemd[1]: Stopped MySQL Server.
清理环境(模拟数据库损坏了)
[root@mysql-server full]# ls /var/lib/mysql
aria_log.00000001 client-key.pem ibtmp1 private_key.pem
aria_log_control guanguan mysql public_key.pem
auto.cnf ib_buffer_pool mysql-server-slow.log server-cert.pem
ca-key.pem ibdata1 mysql.sock server-key.pem
ca.pem ib_logfile0 mysql.sock.lock zabbix
client-cert.pem ib_logfile1 performance_schema zabbix-proxy-slow.log
[root@mysql-server full]# rm -rf /var/lib/mysql/*
[root@mysql-server full]# ls /var/lib/mysql
[root@mysql-server full]# ls /var/lib/mysql -d
/var/lib/mysql
#将日志也删除掉
[root@mysql-server full]# rm -rf /var/log/mysqld.log
生成回滚日志
[root@mysql-server mysql]# cd /xtrabackup/full
[root@mysql-server full]# ls
2022-11-05_21-04-58
[root@mysql-server full]# innobackupex --apply-log /xtrabackup/full/2022-11-05_21-04-58
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221105 21:21:40 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
xtrabackup: cd to /xtrabackup/full/2022-11-05_21-04-58/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(87785413)
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 87785413
InnoDB: Doing recovery: scanned up to log sequence number 87785422 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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.35 started; log sequence number 87785422
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 87785441
InnoDB: Number of pools: 1
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=87785441
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 87785484
InnoDB: Doing recovery: scanned up to log sequence number 87785493 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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.35 started; log sequence number 87785493
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 87785512
221105 21:21:44 completed OK!
恢复文件
[root@mysql-server full]# systemctl start mysqld
[root@mysql-server full]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 六 2022-11-05 21:24:28 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 10089 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 10034 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 10092 (mysqld)
Tasks: 28
CGroup: /system.slice/mysqld.service
└─10092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
11月 05 21:24:23 mysql-server systemd[1]: Starting MySQL Server...
11月 05 21:24:28 mysql-server systemd[1]: Started MySQL Server.
[root@mysql-server full]# grep password /var/log/mysqld.log
2022-11-05T13:24:25.814023Z 1 [Note] A temporary password is generated for root@localhost: waq=lh3ff<iO
2022-11-05T13:24:31.688353Z 2 [Note] Access denied for user 'zabbix'@'localhost' (using password: YES)
2022-11-05T13:24:41.690359Z 3 [Note] Access denied for user 'zabbix'@'localhost' (using password: YES)
2022-11-05T13:24:51.692113Z 4 [Note] Access denied for user 'zabbix'@'localhost' (using password: YES)
2022-11-05T13:25:01.694525Z 5 [Note] Access denied for user 'zabbix'@'localhost' (using password: YES)
[root@mysql-server full]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-server full]# mysql -uroot -pwaq=lh3ff<iO
bash: iO: 没有那个文件或目录
[root@mysql-server full]# mysql -uroot -p'waq=lh3ff<iO'
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 14
Server version: 5.7.40-log
Copyright (c) 2000, 2022, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> quit;
Bye
[root@mysql-server full]# mysqladmin -uroot -p'waq=lh3ff<iO' password '123456'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@mysql-server full]# mysql -uroot -p'waq=lh3ff<iO' password '123456'
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 14
Server version: 5.7.40-log
Copyright (c) 2000, 2022, 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> show databases;
[root@mysql-server full]# ls
2022-11-05_21-04-58
[root@mysql-server full]# innobackupex --copy-back /xtrabackup/full/2022-11-05_21-04-58
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221105 21:39:29 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
Original data directory /var/lib/mysql is not empty!
登录验证
[root@mysql-server full]# ls /var/lib/mysql
auto.cnf ib_buffer_pool mysql private_key.pem
ca-key.pem ibdata1 mysql-server-slow.log public_key.pem
ca.pem ib_logfile0 mysql.sock server-cert.pem
client-cert.pem ib_logfile1 mysql.sock.lock server-key.pem
client-key.pem ibtmp1 performance_schema sys
[root@mysql-server full]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server full]# systemctl start mysqld
[root@mysql-server full]# mysql -uroot -p123456
做免密
[root@mysql-server full]# cat /etc/my.cnf
[mysqld]
skip-grant-tables
...
[root@mysql-server full]# grep password /var/log/mysqld.log
[root@mysql-server full]# mysqladmin -uroot -p'5l,2ps3VmVZo' password '123456'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@mysql-server full]# mysql -uroot -p123456
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 73
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
6.增量备份流程
增量备份是基于完全备份进行备份的
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32
[root@mysql-server ~]# rm -rf /xtrabackup/full/2022-11-06_00-31-10
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32
[root@mysql-server ~]# date 11070030
2022年 11月 07日 星期一 00:30:00 CST
[root@mysql-server ~]# mysql -uroot -p123456
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 60
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> insert into testdb.test values(2)
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from testdb.test;
+------+
| id |
+------+
| 119 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> quit;
Bye
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32
[root@mysql-server ~]# innobackupex --user=root --password='123456' --incremental /xtrabackup/full --incremental-basedir=/xtrabackup/full/2022-11-06_00-32-32
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221107 00:34:52 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!".
221107 00:34:52 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221107 00:34:52 version_check Connected to MySQL server
221107 00:34:52 version_check Executing a version check against the server...
221107 00:34:52 version_check Done.
221107 00:34:52 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87789704 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
221107 00:34:52 >> log scanned up to (87790789)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 138 for mysql/plugin, old maximum was 0
xtrabackup: using the full scan for incremental backup
221107 00:34:52 [01] Copying ./ibdata1 to /xtrabackup/full/2022-11-07_00-34-52/ibdata1.delta
221107 00:34:52 [01] ...done
221107 00:34:52 [01] Copying ./mysql/plugin.ibd to /xtrabackup/full/2022-11-07_00-34-52/mysql/plugin.ibd.delta
221107 00:34:52 [01] ...done
221107 00:34:53 [01] Copying ./mysql/servers.ibd to /xtrabackup/full/2022-11-07_00-34-52/mysql/servers.ibd.delta
221107 00:34:53 [01] ...done
221107 00:34:53 [01] Copying ./mysql/time_zone_name.ibd to /xtrabackup/full/2022-11-07_00-34-52/mysql/time_zone_name.ibd.delta
221107 00:34:53 [01] ...done
221107 00:34:53 [01] Copying ./mysql/help_topic.ibd to /xtrabackup/full/2022-11-07_00-34-52/mysql/help_topic.ibd.delta
221107 00:34:53 [01] ...done
221107 00:34:53 [01] Copying ./mysql/help_category.ibd to /xtrabackup/full/2022-11-07_00-34-52/mysql/help_category.ibd.delta
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/maintenances_groups.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/maintenances_groups.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/housekeeper.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/housekeeper.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/users.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/users.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/opmessage.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/opmessage.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/opgroup.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/opgroup.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/mappings.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/mappings.frm
221107 00:34:54 [01] ...done
221107 00:34:54 [01] Copying ./zabbix/graph_theme.frm to /xtrabackup/full/2022-11-07_00-34-52/zabbix/graph_theme.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/events_waits_summary_by_thread_by_event_name.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/events_waits_summary_global_by_event_name.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/file_instances.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/file_instances.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/file_summary_by_event_name.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/file_summary_by_instance.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/file_summary_by_instance.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/socket_instances.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/socket_instances.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/socket_summary_by_instance.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/socket_summary_by_event_name.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/host_cache.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/host_cache.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/mutex_instances.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/mutex_instances.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/objects_summary_global_by_type.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./performance_schema/performance_timers.frm to /xtrabackup/full/2022-11-07_00-34-52/performance_schema/performance_timers.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/io_global_by_file_by_bytes.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/io_global_by_file_by_bytes.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/x@0024schema_index_statistics.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/x@0024schema_index_statistics.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/x@0024io_global_by_file_by_bytes.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/x@0024io_global_by_file_by_bytes.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/user_summary.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/user_summary.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/io_global_by_file_by_latency.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/io_global_by_file_by_latency.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/schema_table_statistics.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/schema_table_statistics.frm
221107 00:34:55 [01] ...done
221107 00:34:55 [01] Copying ./sys/x@0024io_global_by_file_by_latency.frm to /xtrabackup/full/2022-11-07_00-34-52/sys/x@0024io_global_by_file_by_latency.frm
...
221107 00:34:56 completed OK!
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52
[root@mysql-server ~]# mysql -uroot -p123456
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 135
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> insert into testdb.test values(110);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdb.test;
+------+
| id |
+------+
| 119 |
| 2 |
| 110 |
+------+
3 rows in set (0.00 sec)
mysql> quit;
Bye
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52
[root@mysql-server ~]# date 11080050
2022年 11月 08日 星期二 00:50:00 CST
[root@mysql-server ~]# innobackupex --user=root --password='123456' --incremental /xtrabackup/full --incremental-basedir=/xtrabackup/full/2022-11-07_00-34-52
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221108 00:50:18 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!".
221108 00:50:18 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221108 00:50:18 version_check Connected to MySQL server
221108 00:50:18 version_check Executing a version check against the server...
221108 00:50:18 version_check Done.
221108 00:50:18 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87790780 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
...
221108 00:50:22 [00] ...done
221108 00:50:22 Backup created in directory '/xtrabackup/full/2022-11-08_00-50-18/'
221108 00:50:22 [00] Writing /xtrabackup/full/2022-11-08_00-50-18/backup-my.cnf
221108 00:50:22 [00] ...done
221108 00:50:22 [00] Writing /xtrabackup/full/2022-11-08_00-50-18/xtrabackup_info
221108 00:50:22 [00] ...done
xtrabackup: Transaction log of lsn (87790939) to (87790948) was copied.
221108 00:50:22 completed OK!
增量备份恢复流程
将前面的增量备份合在一起，回滚合并
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52 2022-11-08_00-50-18
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-11-06_00-32-32/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221108 00:59:01 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
xtrabackup: cd to /xtrabackup/full/2022-11-06_00-32-32/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(87789704)
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 87789704
InnoDB: Doing recovery: scanned up to log sequence number 87789713 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 87789722
InnoDB: Number of pools: 1
221108 00:59:01 completed OK!
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52 2022-11-08_00-50-18
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-11-06_00-32-32/ --incremental-dir=/xtrabackup/full/2022-11-07_00-34-52
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221108 01:01:05 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87789704 is enabled.
xtrabackup: cd to /xtrabackup/full/2022-11-06_00-32-32/
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=(87790780)
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/full/2022-11-07_00-34-52/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 138 for mysql/plugin, old maximum was 0
xtrabackup: page size for /xtrabackup/full/2022-11-07_00-34-52//ibdata1.delta is 16384 bytes space id is 0
....
221108 01:01:08 [01] ...done
221108 01:01:08 [00] Copying /xtrabackup/full/2022-11-07_00-34-52//xtrabackup_info to ./xtrabackup_info
221108 01:01:08 [00] ...done
221108 01:01:08 completed OK!
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# ls /xtrabackup/full
2022-11-06_00-32-32 2022-11-07_00-34-52 2022-11-08_00-50-18
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-11-06_00-32-32/ --incremental-dir=/xtrabackup/full/2022-11-08_00-50-18
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221108 01:04:51 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87790780 is enabled.
xtrabackup: cd to /xtrabackup/full/2022-11-06_00-32-32/
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=(87790939)
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/full/2022-11-08_00-50-18/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
221108 01:04:52 [01] ...done
221108 01:04:52 [00] Copying /xtrabackup/full/2022-11-08_00-50-18//xtrabackup_info to ./xtrabackup_info
221108 01:04:52 [00] ...done
221108 01:04:52 completed OK!
[root@mysql-server ~]# ls /var/lib/mysql
aria_log.00000001 client-key.pem mysql-server-slow.log server-key.pem
aria_log_control ib_buffer_pool mysql_upgrade_info sys
auto.cnf ibdata1 performance_schema testdb
ca-key.pem ib_logfile0 private_key.pem zabbix
ca.pem ib_logfile1 public_key.pem zabbix-proxy-slow.log
client-cert.pem mysql server-cert.pem
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# ls
anaconda-ks.cfg zabbix 模板 图片 下载 桌面
initial-setup-ks.cfg 公共 视频 文档 音乐
[root@mysql-server ~]# ls /var/lib/mysql
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2022-11-06_00-32-32/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221108 01:11:01 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
221108 01:11:01 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/plugin.ibd to /var/lib/mysql/mysql/plugin.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/servers.ibd to /var/lib/mysql/mysql/servers.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/time_zone_name.ibd to /var/lib/mysql/mysql/time_zone_name.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/help_topic.ibd to /var/lib/mysql/mysql/help_topic.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/help_category.ibd to /var/lib/mysql/mysql/help_category.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/help_keyword.ibd to /var/lib/mysql/mysql/help_keyword.ibd
221108 01:11:01 [01] ...done
221108 01:11:01 [01] Copying ./mysql/help_relation.ibd to /var/lib/mysql/mysql/help_relation.ibd
221108 01:11:01 [01] ...done
...
221108 01:11:03 [01] ...done
221108 01:11:03 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
221108 01:11:03 [01] ...done
221108 01:11:03 completed OK!
[root@mysql-server ~]# ls /var/lib/mysql
ib_buffer_pool mysql sys xtrabackup_info zabbix
ibdata1 performance_schema testdb xtrabackup_master_key_id
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
[root@mysql-server ~]# mysql -uroot -p'123456' -e 'select * from testdb.test'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 119 |
| 2 |
| 110 |
+------+
[root@mysql-server ~]#
7.差异备份
[root@mysql-server ~]# ls /xtrabackup/*
2022-11-06_00-32-32 2022-11-07_00-34-52 2022-11-08_00-50-18
[root@mysql-server ~]# rm -rf /xtrabackup/*
[root@mysql-server ~]# date 11080000
2022年 11月 08日 星期二 00:00:00 CST
[root@mysql-server ~]# mysql -uroot -p123456
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 2266
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.10 sec)
mysql> use testdb;
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 test2
-> ;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table test2(id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test2 values(888);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test2;
+------+
| id |
+------+
| 888 |
+------+
1 row in set (0.01 sec)
mysql> quit;
Bye
[root@mysql-server ~]# innobackupex --user=root --password='123456' /xtrabackup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221108 00:04:32 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!".
221108 00:04:32 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221108 00:04:32 version_check Connected to MySQL server
221108 00:04:32 version_check Executing a version check against the server...
221108 00:04:32 version_check Done.
221108 00:04:32 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
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
221108 00:04:32 >> log scanned up to (87795745)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 138 for mysql/plugin, old maximum was 0
221108 00:04:32 [01] Copying ./ibdata1 to /xtrabackup/2022-11-08_00-04-32/ibdata1
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/plugin.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/plugin.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/servers.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/servers.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/time_zone_name.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/time_zone_name.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/help_topic.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/help_topic.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/help_category.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/help_category.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/help_keyword.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/help_keyword.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/help_relation.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/help_relation.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/time_zone.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/time_zone.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/time_zone_transition.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/time_zone_transition.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/time_zone_transition_type.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/time_zone_transition_type.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/time_zone_leap_second.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/time_zone_leap_second.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/innodb_table_stats.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/innodb_table_stats.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/innodb_index_stats.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/innodb_index_stats.ibd
221108 00:04:33 [01] ...done
221108 00:04:33 [01] Copying ./mysql/slave_relay_log_info.ibd to /xtrabackup/2022-11-08_00-04-32/mysql/slave_relay_log_info.ibd
221108 00:04:35 [01] ...done
221108 00:04:35 [01] Copying ./performance_schema/status_by_account.frm to /xtrabackup/2022-11-08_00-04-32/performance_schema/status_by_account.frm
221108 00:04:35 [01] ...done
221108 00:04:35 [01] Copying ./performance_schema/global_status.frm to /xtrabackup/2022-11-08_00-04-32/performance_schema/global_status.frm
221108 00:04:35 [01] ...done
221108 00:04:35 [01] Copying ./performance_schema/session_status.frm to /xtrabackup/2022-11-08_00-04-32/performance_schema/session_status.frm
221108 00:04:35 [01] ...done
221108 00:04:35 Finished backing up non-InnoDB tables and files
221108 00:04:35 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '87795736'
xtrabackup: Stopping log copying thread.
.221108 00:04:35 >> log scanned up to (87795745)
221108 00:04:35 Executing UNLOCK TABLES
221108 00:04:35 All tables unlocked
221108 00:04:35 [00] Copying ib_buffer_pool to /xtrabackup/2022-11-08_00-04-32/ib_buffer_pool
221108 00:04:35 [00] ...done
221108 00:04:35 Backup created in directory '/xtrabackup/2022-11-08_00-04-32/'
221108 00:04:35 [00] Writing /xtrabackup/2022-11-08_00-04-32/backup-my.cnf
221108 00:04:35 [00] ...done
221108 00:04:35 [00] Writing /xtrabackup/2022-11-08_00-04-32/xtrabackup_info
221108 00:04:35 [00] ...done
xtrabackup: Transaction log of lsn (87795736) to (87795745) was copied.
221108 00:04:35 completed OK!
[root@mysql-server ~]# ls /xtrabackup
2022-11-08_00-04-32
[root@mysql-server ~]# date 11090000
2022年 11月 09日 星期三 00:00:00 CST
[root@mysql-server ~]# mysql -uroot -p123456 -e'insert into testdb.test2 values(999)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# mysql -uroot -p123456
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 2337
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> select * from testdb.test2;
+------+
| id |
+------+
| 888 |
| 999 |
+------+
2 rows in set (0.01 sec)
mysql> quit;
Bye
[root@mysql-server ~]# ls /xtrabackup
2022-11-08_00-04-32
[root@mysql-server ~]# innobackupex --user=root --password='123456' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2022-11-08_00-04-32
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221109 00:05: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!".
221109 00:05:01 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221109 00:05:01 version_check Connected to MySQL server
221109 00:05:01 version_check Executing a version check against the server...
221109 00:05:01 version_check Done.
221109 00:05:01 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87795736 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
221109 00:05:01 >> log scanned up to (87795909)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 138 for mysql/plugin, old maximum was 0
xtrabackup: using the full scan for incremental backup
221109 00:05:02 [01] Copying ./ibdata1 to /xtrabackup/2022-11-09_00-05-01/ibdata1.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/plugin.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/plugin.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/servers.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/servers.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/time_zone_name.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_name.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/help_topic.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/help_topic.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/help_category.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/help_category.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/help_keyword.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/help_keyword.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/help_relation.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/help_relation.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/time_zone.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 >> log scanned up to (87795909)
221109 00:05:02 [01] Copying ./mysql/time_zone_transition.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_transition.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/time_zone_transition_type.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_transition_type.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/time_zone_leap_second.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_leap_second.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:02 [01] Copying ./mysql/innodb_table_stats.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/innodb_table_stats.ibd.delta
221109 00:05:02 [01] ...done
221109 00:05:03 [01] Copying ./mysql/innodb_index_stats.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/innodb_index_stats.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/slave_relay_log_info.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/slave_relay_log_info.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/slave_master_info.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/slave_master_info.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/slave_worker_info.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/slave_worker_info.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/gtid_executed.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/gtid_executed.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/server_cost.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/server_cost.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./mysql/engine_cost.ibd to /xtrabackup/2022-11-09_00-05-01/mysql/engine_cost.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./testdb/test.ibd to /xtrabackup/2022-11-09_00-05-01/testdb/test.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./testdb/test2.ibd to /xtrabackup/2022-11-09_00-05-01/testdb/test2.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 [01] Copying ./sys/sys_config.ibd to /xtrabackup/2022-11-09_00-05-01/sys/sys_config.ibd.delta
221109 00:05:03 [01] ...done
221109 00:05:03 >> log scanned up to (87795909)
221109 00:05:04 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
221109 00:05:04 Executing FLUSH TABLES WITH READ LOCK...
221109 00:05:04 Starting to backup non-InnoDB tables and files
221109 00:05:04 [01] Copying ./mysql/slow_log.CSM to /xtrabackup/2022-11-09_00-05-01/mysql/slow_log.CSM
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/func.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/func.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/host.frm to /xtrabackup/2022-11-09_00-05-01/mysql/host.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/host.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/host.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/host.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/host.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/func.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/func.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/func.frm to /xtrabackup/2022-11-09_00-05-01/mysql/func.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/slow_log.frm to /xtrabackup/2022-11-09_00-05-01/mysql/slow_log.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/general_log.CSM to /xtrabackup/2022-11-09_00-05-01/mysql/general_log.CSM
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/procs_priv.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/procs_priv.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/columns_priv.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/columns_priv.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/columns_priv.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/columns_priv.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/columns_priv.frm to /xtrabackup/2022-11-09_00-05-01/mysql/columns_priv.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/proxies_priv.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/proxies_priv.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/ndb_binlog_index.frm to /xtrabackup/2022-11-09_00-05-01/mysql/ndb_binlog_index.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/time_zone.frm to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/plugin.frm to /xtrabackup/2022-11-09_00-05-01/mysql/plugin.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/time_zone_leap_second.frm to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_leap_second.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/help_relation.frm to /xtrabackup/2022-11-09_00-05-01/mysql/help_relation.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/help_keyword.frm to /xtrabackup/2022-11-09_00-05-01/mysql/help_keyword.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/servers.frm to /xtrabackup/2022-11-09_00-05-01/mysql/servers.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/time_zone_name.frm to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_name.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/time_zone_transition_type.frm to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_transition_type.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/time_zone_transition.frm to /xtrabackup/2022-11-09_00-05-01/mysql/time_zone_transition.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/proc.frm to /xtrabackup/2022-11-09_00-05-01/mysql/proc.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/proc.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/proc.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/procs_priv.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/procs_priv.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/procs_priv.frm to /xtrabackup/2022-11-09_00-05-01/mysql/procs_priv.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/event.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/event.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/db.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/db.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/help_category.frm to /xtrabackup/2022-11-09_00-05-01/mysql/help_category.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/event.frm to /xtrabackup/2022-11-09_00-05-01/mysql/event.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/slow_log.CSV to /xtrabackup/2022-11-09_00-05-01/mysql/slow_log.CSV
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/db.frm to /xtrabackup/2022-11-09_00-05-01/mysql/db.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/event.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/event.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/user.MYD to /xtrabackup/2022-11-09_00-05-01/mysql/user.MYD
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/help_topic.frm to /xtrabackup/2022-11-09_00-05-01/mysql/help_topic.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/proxies_priv.MYI to /xtrabackup/2022-11-09_00-05-01/mysql/proxies_priv.MYI
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/proxies_priv.frm to /xtrabackup/2022-11-09_00-05-01/mysql/proxies_priv.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/innodb_table_stats.frm to /xtrabackup/2022-11-09_00-05-01/mysql/innodb_table_stats.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/innodb_index_stats.frm to /xtrabackup/2022-11-09_00-05-01/mysql/innodb_index_stats.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/slave_relay_log_info.frm to /xtrabackup/2022-11-09_00-05-01/mysql/slave_relay_log_info.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/slave_master_info.frm to /xtrabackup/2022-11-09_00-05-01/mysql/slave_master_info.frm
221109 00:05:04 [01] ...done
221109 00:05:04 [01] Copying ./mysql/slave_worker_info.frm to /xtrabackup/2022-11-09_00-05-01/mysql/slave_worker_info.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/variables_by_thread.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/variables_by_thread.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/global_variables.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/global_variables.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/session_variables.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/session_variables.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/status_by_thread.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/status_by_thread.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/status_by_user.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/status_by_user.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/status_by_host.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/status_by_host.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/status_by_account.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/status_by_account.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/global_status.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/global_status.frm
221109 00:05:05 [01] ...done
221109 00:05:05 [01] Copying ./performance_schema/session_status.frm to /xtrabackup/2022-11-09_00-05-01/performance_schema/session_status.frm
221109 00:05:05 [01] ...done
221109 00:05:05 Finished backing up non-InnoDB tables and files
221109 00:05:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '87795900'
xtrabackup: Stopping log copying thread.
.221109 00:05:05 >> log scanned up to (87795909)
221109 00:05:05 Executing UNLOCK TABLES
221109 00:05:05 All tables unlocked
221109 00:05:05 [00] Copying ib_buffer_pool to /xtrabackup/2022-11-09_00-05-01/ib_buffer_pool
221109 00:05:05 [00] ...done
221109 00:05:05 Backup created in directory '/xtrabackup/2022-11-09_00-05-01/'
221109 00:05:05 [00] Writing /xtrabackup/2022-11-09_00-05-01/backup-my.cnf
221109 00:05:05 [00] ...done
221109 00:05:05 [00] Writing /xtrabackup/2022-11-09_00-05-01/xtrabackup_info
221109 00:05:05 [00] ...done
xtrabackup: Transaction log of lsn (87795900) to (87795909) was copied.
221109 00:05:05 completed OK!
[root@mysql-server ~]# date 11100000
2022年 11月 10日 星期四 00:00:00 CST
[root@mysql-server ~]# mysql -uroot -p123456 -e'insert into testdb.test2 values(777)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ls /xtrabackup
2022-11-08_00-04-32 2022-11-09_00-05-01
[root@mysql-server ~]# innobackupex --user=root --password='123456' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2022-11-08_00-04-32
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221109 00:01:35 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!".
221109 00:01:35 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
221109 00:01:35 version_check Connected to MySQL server
221109 00:01:35 version_check Executing a version check against the server...
221109 00:01:35 version_check Done.
221109 00:01:35 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.40-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87795736 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
221109 00:01:35 >> log scanned up to (87796073)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 138 for mysql/plugin, old maximum was 0
xtrabackup: using the full scan for incremental backup
221109 00:01:35 [01] Copying ./ibdata1 to /xtrabackup/2022-11-09_00-01-35/ibdata1.delta
221109 00:01:35 [01] ...done
221109 00:01:36 [01] Copying ./mysql/plugin.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/plugin.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/servers.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/servers.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/time_zone_name.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone_name.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/help_topic.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/help_topic.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/help_category.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/help_category.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/help_keyword.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/help_keyword.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/help_relation.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/help_relation.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/time_zone.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 >> log scanned up to (87796073)
221109 00:01:36 [01] Copying ./mysql/time_zone_transition.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone_transition.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/time_zone_transition_type.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone_transition_type.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/time_zone_leap_second.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone_leap_second.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/innodb_table_stats.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/innodb_table_stats.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/innodb_index_stats.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/innodb_index_stats.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/slave_relay_log_info.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/slave_relay_log_info.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/slave_master_info.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/slave_master_info.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/slave_worker_info.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/slave_worker_info.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/gtid_executed.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/gtid_executed.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:36 [01] Copying ./mysql/server_cost.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/server_cost.ibd.delta
221109 00:01:36 [01] ...done
221109 00:01:37 [01] Copying ./mysql/engine_cost.ibd to /xtrabackup/2022-11-09_00-01-35/mysql/engine_cost.ibd.delta
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./testdb/test.ibd to /xtrabackup/2022-11-09_00-01-35/testdb/test.ibd.delta
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./testdb/test2.ibd to /xtrabackup/2022-11-09_00-01-35/testdb/test2.ibd.delta
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./sys/sys_config.ibd to /xtrabackup/2022-11-09_00-01-35/sys/sys_config.ibd.delta
221109 00:01:37 [01] ...done
221109 00:01:37 >> log scanned up to (87796073)
221109 00:01:37 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
221109 00:01:37 Executing FLUSH TABLES WITH READ LOCK...
221109 00:01:37 Starting to backup non-InnoDB tables and files
221109 00:01:37 [01] Copying ./mysql/slow_log.CSM to /xtrabackup/2022-11-09_00-01-35/mysql/slow_log.CSM
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/func.MYD to /xtrabackup/2022-11-09_00-01-35/mysql/func.MYD
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/host.frm to /xtrabackup/2022-11-09_00-01-35/mysql/host.frm
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/host.MYI to /xtrabackup/2022-11-09_00-01-35/mysql/host.MYI
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/host.MYD to /xtrabackup/2022-11-09_00-01-35/mysql/host.MYD
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/func.MYI to /xtrabackup/2022-11-09_00-01-35/mysql/func.MYI
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/func.frm to /xtrabackup/2022-11-09_00-01-35/mysql/func.frm
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/time_zone_transition.frm to /xtrabackup/2022-11-09_00-01-35/mysql/time_zone_transition.frm
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/proc.frm to /xtrabackup/2022-11-09_00-01-35/mysql/proc.frm
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/proc.MYI to /xtrabackup/2022-11-09_00-01-35/mysql/proc.MYI
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/procs_priv.MYD to /xtrabackup/2022-11-09_00-01-35/mysql/procs_priv.MYD
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/procs_priv.frm to /xtrabackup/2022-11-09_00-01-35/mysql/procs_priv.frm
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/event.MYD to /xtrabackup/2022-11-09_00-01-35/mysql/event.MYD
221109 00:01:37 [01] ...done
221109 00:01:37 [01] Copying ./mysql/db.MYD to /xtrabackup/2022-11-09_00-01-35/mysql/db.MYD
221109 00:01:37 [01] ...done
35/sys/x@0024innodb_buffer_stats_by_table.frm
221109 00:01:38 [01] ...done
221109 00:01:38 [01] Copying ./sys/host_summary.frm to /xtrabackup/2022-11-09_00-01-35/sys/host_summary.frm
221109 00:01:38 [01] ...done
22110
221109 00:05:05 Executing UNLOCK TABLES
221109 00:05:05 All tables unlocked
221109 00:05:05 [00] Copying ib_buffer_pool to /xtrabackup/2022-11-09_00-05-01/ib_buffer_pool
221109 00:05:05 [00] ...done
221109 00:05:05 Backup created in directory '/xtrabackup/2022-11-09_00-05-01/'
221109 00:05:05 [00] Writing /xtrabackup/2022-11-09_00-05-01/backup-my.cnf
221109 00:05:05 [00] ...done
221109 00:05:05 [00] Writing /xtrabackup/2022-11-09_00-05-01/xtrabackup_info
221109 00:05:05 [00] ...done
xtrabackup: Transaction log of lsn (87795900) to (87795909) was copied.
221109 00:05:05 completed OK!
8.差异恢复流程
停止数据库
[root@mysql-server ~]# systemctl stop mysqld
清理环境
[root@mysql-server ~]# rm -rf /var/lib/mysqld/*
重演回滚 redo log (周一，某次差异) -->恢复数据
[root@mysql-server ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2022-11-09 00:42:41 CST; 2min 32s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 17844 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 17823 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 17847 (mysqld)
Tasks: 28
CGroup: /system.slice/mysqld.service
└─17847 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
11月 09 00:42:37 mysql-server systemd[1]: Starting MySQL Server...
11月 09 00:42:41 mysql-server systemd[1]: Started MySQL Server.
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# ls /var/lib/mysql
auto.cnf ib_buffer_pool mysql-server-slow.log server-key.pem zabbix
ca-key.pem ibdata1 performance_schema sys
ca.pem ib_logfile0 private_key.pem testdb
client-cert.pem ib_logfile1 public_key.pem xtrabackup_info
client-key.pem mysql server-cert.pem xtrabackup_master_key_id
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# ls /var/lib/mysql
运用完整备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-11-08_00-04-32
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221109 00:46:08 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
xtrabackup: cd to /xtrabackup/2022-11-08_00-04-32/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
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
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
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: 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=87795918
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 87796236
InnoDB: Doing recovery: scanned up to log sequence number 87796245 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 87796254
InnoDB: Number of pools: 1
221109 00:46:09 completed OK!
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-11-08_00-04-32 --incremental-dir=/xtrabackup/2022-11-09_00-05-01
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments:
221109 00:46:20 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 87795736 is enabled.
xtrabackup: cd to /xtrabackup/2022-11-08_00-04-32/
xtrabackup: This target seems to be already prepared with --apply-log-only.
xtrabackup: error: This incremental backup seems not to be proper for the target.
xtrabackup: Check 'to_lsn' of the target and 'from_lsn' of the incremental.
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2022-11-08_00-04-32
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
221109 00:46:37 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.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
221109 00:46:37 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/plugin.ibd to /var/lib/mysql/mysql/plugin.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/servers.ibd to /var/lib/mysql/mysql/servers.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_name.ibd to /var/lib/mysql/mysql/time_zone_name.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_topic.ibd to /var/lib/mysql/mysql/help_topic.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_category.ibd to /var/lib/mysql/mysql/help_category.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_keyword.ibd to /var/lib/mysql/mysql/help_keyword.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_relation.ibd to /var/lib/mysql/mysql/help_relation.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone.ibd to /var/lib/mysql/mysql/time_zone.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_transition.ibd to /var/lib/mysql/mysql/time_zone_transition.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_transition_type.ibd to /var/lib/mysql/mysql/time_zone_transition_type.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_leap_second.ibd to /var/lib/mysql/mysql/time_zone_leap_second.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_relay_log_info.ibd to /var/lib/mysql/mysql/slave_relay_log_info.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_master_info.ibd to /var/lib/mysql/mysql/slave_master_info.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_worker_info.ibd to /var/lib/mysql/mysql/slave_worker_info.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/gtid_executed.ibd to /var/lib/mysql/mysql/gtid_executed.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/server_cost.ibd to /var/lib/mysql/mysql/server_cost.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/engine_cost.ibd to /var/lib/mysql/mysql/engine_cost.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/ndb_binlog_index.frm to /var/lib/mysql/mysql/ndb_binlog_index.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/innodb_index_stats.frm to /var/lib/mysql/mysql/innodb_index_stats.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_relay_log_info.frm to /var/lib/mysql/mysql/slave_relay_log_info.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_master_info.frm to /var/lib/mysql/mysql/slave_master_info.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/slave_worker_info.frm to /var/lib/mysql/mysql/slave_worker_info.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/gtid_executed.frm to /var/lib/mysql/mysql/gtid_executed.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/server_cost.frm to /var/lib/mysql/mysql/server_cost.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/engine_cost.frm to /var/lib/mysql/mysql/engine_cost.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/ndb_binlog_index.MYI to /var/lib/mysql/mysql/ndb_binlog_index.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/ndb_binlog_index.MYD to /var/lib/mysql/mysql/ndb_binlog_index.MYD
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./testdb/test.ibd to /var/lib/mysql/testdb/test.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./testdb/test2.ibd to /var/lib/mysql/testdb/test2.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./testdb/db.opt to /var/lib/mysql/testdb/db.opt
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./testdb/test.frm to /var/lib/mysql/testdb/test.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./testdb/test2.frm to /var/lib/mysql/testdb/test2.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/sys_config.ibd to /var/lib/mysql/sys/sys_config.ibd
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/db.opt to /var/lib/mysql/sys/db.opt
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/version.frm to /var/lib/mysql/sys/version.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/sys_config.frm to /var/lib/mysql/sys/sys_config.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/statements_with_full_table_scans.frm to /var/lib/mysql/sys/statements_with_full_table_scans.frm
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/sys_config_insert_set_user.TRN to /var/lib/mysql/sys/sys_config_insert_set_user.TRN
221109 00:46:37 [01] ...done
221109 00:46:37 [01] Copying ./sys/processlist.frm to /var/lib/mysql/sys/processlist.frm
221109 00:46:37 [01] ...done
221109 00:46:38 [01] Copying ./sys/sys_config.TRG to /var/lib/mysql/sys/sys_config.TRG
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/statements_with_sorting.frm to /var/lib/mysql/sys/statements_with_sorting.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/sys_config_update_set_user.TRN to /var/lib/mysql/sys/sys_config_update_set_user.TRN
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statements_with_sorting.frm to /var/lib/mysql/sys/x@0024statements_with_sorting.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/innodb_buffer_stats_by_schema.frm to /var/lib/mysql/sys/innodb_buffer_stats_by_schema.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_index_statistics.frm to /var/lib/mysql/sys/schema_index_statistics.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024innodb_buffer_stats_by_schema.frm to /var/lib/mysql/sys/x@0024innodb_buffer_stats_by_schema.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/statements_with_temp_tables.frm to /var/lib/mysql/sys/statements_with_temp_tables.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/innodb_buffer_stats_by_table.frm to /var/lib/mysql/sys/innodb_buffer_stats_by_table.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024ps_schema_table_statistics_io.frm to /var/lib/mysql/sys/x@0024ps_schema_table_statistics_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024innodb_buffer_stats_by_table.frm to /var/lib/mysql/sys/x@0024innodb_buffer_stats_by_table.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary.frm to /var/lib/mysql/sys/host_summary.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/innodb_lock_waits.frm to /var/lib/mysql/sys/innodb_lock_waits.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary.frm to /var/lib/mysql/sys/x@0024host_summary.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024innodb_lock_waits.frm to /var/lib/mysql/sys/x@0024innodb_lock_waits.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/waits_by_user_by_latency.frm to /var/lib/mysql/sys/waits_by_user_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_object_overview.frm to /var/lib/mysql/sys/schema_object_overview.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary_by_file_io_type.frm to /var/lib/mysql/sys/user_summary_by_file_io_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_auto_increment_columns.frm to /var/lib/mysql/sys/schema_auto_increment_columns.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary_by_file_io.frm to /var/lib/mysql/sys/user_summary_by_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_flattened_keys.frm to /var/lib/mysql/sys/x@0024schema_flattened_keys.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary_by_file_io.frm to /var/lib/mysql/sys/x@0024user_summary_by_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_redundant_indexes.frm to /var/lib/mysql/sys/schema_redundant_indexes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary_by_statement_type.frm to /var/lib/mysql/sys/user_summary_by_statement_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/ps_check_lost_instrumentation.frm to /var/lib/mysql/sys/ps_check_lost_instrumentation.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/latest_file_io.frm to /var/lib/mysql/sys/latest_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/waits_by_host_by_latency.frm to /var/lib/mysql/sys/waits_by_host_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024latest_file_io.frm to /var/lib/mysql/sys/x@0024latest_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/waits_global_by_latency.frm to /var/lib/mysql/sys/waits_global_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/io_by_thread_by_latency.frm to /var/lib/mysql/sys/io_by_thread_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary_by_stages.frm to /var/lib/mysql/sys/user_summary_by_stages.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024io_by_thread_by_latency.frm to /var/lib/mysql/sys/x@0024io_by_thread_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary_by_stages.frm to /var/lib/mysql/sys/x@0024user_summary_by_stages.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/io_global_by_file_by_bytes.frm to /var/lib/mysql/sys/io_global_by_file_by_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_index_statistics.frm to /var/lib/mysql/sys/x@0024schema_index_statistics.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024io_global_by_file_by_bytes.frm to /var/lib/mysql/sys/x@0024io_global_by_file_by_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary.frm to /var/lib/mysql/sys/user_summary.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/io_global_by_file_by_latency.frm to /var/lib/mysql/sys/io_global_by_file_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_table_statistics.frm to /var/lib/mysql/sys/schema_table_statistics.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024io_global_by_file_by_latency.frm to /var/lib/mysql/sys/x@0024io_global_by_file_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary.frm to /var/lib/mysql/sys/x@0024user_summary.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/io_global_by_wait_by_bytes.frm to /var/lib/mysql/sys/io_global_by_wait_by_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_table_statistics.frm to /var/lib/mysql/sys/x@0024schema_table_statistics.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024io_global_by_wait_by_bytes.frm to /var/lib/mysql/sys/x@0024io_global_by_wait_by_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary_by_file_io_type.frm to /var/lib/mysql/sys/host_summary_by_file_io_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/io_global_by_wait_by_latency.frm to /var/lib/mysql/sys/io_global_by_wait_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_table_statistics_with_buffer.frm to /var/lib/mysql/sys/schema_table_statistics_with_buffer.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024io_global_by_wait_by_latency.frm to /var/lib/mysql/sys/x@0024io_global_by_wait_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary_by_file_io.frm to /var/lib/mysql/sys/host_summary_by_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/memory_by_user_by_current_bytes.frm to /var/lib/mysql/sys/memory_by_user_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_table_lock_waits.frm to /var/lib/mysql/sys/schema_table_lock_waits.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024memory_by_user_by_current_bytes.frm to /var/lib/mysql/sys/x@0024memory_by_user_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary_by_file_io.frm to /var/lib/mysql/sys/x@0024host_summary_by_file_io.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/memory_by_host_by_current_bytes.frm to /var/lib/mysql/sys/memory_by_host_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_table_lock_waits.frm to /var/lib/mysql/sys/x@0024schema_table_lock_waits.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024memory_by_host_by_current_bytes.frm to /var/lib/mysql/sys/x@0024memory_by_host_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/statement_analysis.frm to /var/lib/mysql/sys/statement_analysis.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/memory_by_thread_by_current_bytes.frm to /var/lib/mysql/sys/memory_by_thread_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statement_analysis.frm to /var/lib/mysql/sys/x@0024statement_analysis.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024memory_by_thread_by_current_bytes.frm to /var/lib/mysql/sys/x@0024memory_by_thread_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary_by_statement_type.frm to /var/lib/mysql/sys/host_summary_by_statement_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/memory_global_by_current_bytes.frm to /var/lib/mysql/sys/memory_global_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/statements_with_errors_or_warnings.frm to /var/lib/mysql/sys/statements_with_errors_or_warnings.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024memory_global_by_current_bytes.frm to /var/lib/mysql/sys/x@0024memory_global_by_current_bytes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/metrics.frm to /var/lib/mysql/sys/metrics.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/memory_global_total.frm to /var/lib/mysql/sys/memory_global_total.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary_by_stages.frm to /var/lib/mysql/sys/host_summary_by_stages.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024memory_global_total.frm to /var/lib/mysql/sys/x@0024memory_global_total.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/session.frm to /var/lib/mysql/sys/session.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_table_statistics_with_buffer.frm to /var/lib/mysql/sys/x@0024schema_table_statistics_with_buffer.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statements_with_errors_or_warnings.frm to /var/lib/mysql/sys/x@0024statements_with_errors_or_warnings.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_tables_with_full_table_scans.frm to /var/lib/mysql/sys/schema_tables_with_full_table_scans.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/schema_unused_indexes.frm to /var/lib/mysql/sys/schema_unused_indexes.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024schema_tables_with_full_table_scans.frm to /var/lib/mysql/sys/x@0024schema_tables_with_full_table_scans.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary_by_stages.frm to /var/lib/mysql/sys/x@0024host_summary_by_stages.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statements_with_full_table_scans.frm to /var/lib/mysql/sys/x@0024statements_with_full_table_scans.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024ps_digest_avg_latency_distribution.frm to /var/lib/mysql/sys/x@0024ps_digest_avg_latency_distribution.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024ps_digest_95th_percentile_by_avg_us.frm to /var/lib/mysql/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/statements_with_runtimes_in_95th_percentile.frm to /var/lib/mysql/sys/statements_with_runtimes_in_95th_percentile.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024processlist.frm to /var/lib/mysql/sys/x@0024processlist.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024session.frm to /var/lib/mysql/sys/x@0024session.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statements_with_runtimes_in_95th_percentile.frm to /var/lib/mysql/sys/x@0024statements_with_runtimes_in_95th_percentile.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024statements_with_temp_tables.frm to /var/lib/mysql/sys/x@0024statements_with_temp_tables.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary_by_file_io_type.frm to /var/lib/mysql/sys/x@0024user_summary_by_file_io_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary_by_statement_type.frm to /var/lib/mysql/sys/x@0024user_summary_by_statement_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/user_summary_by_statement_latency.frm to /var/lib/mysql/sys/user_summary_by_statement_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024user_summary_by_statement_latency.frm to /var/lib/mysql/sys/x@0024user_summary_by_statement_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary_by_file_io_type.frm to /var/lib/mysql/sys/x@0024host_summary_by_file_io_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary_by_statement_type.frm to /var/lib/mysql/sys/x@0024host_summary_by_statement_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/host_summary_by_statement_latency.frm to /var/lib/mysql/sys/host_summary_by_statement_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024host_summary_by_statement_latency.frm to /var/lib/mysql/sys/x@0024host_summary_by_statement_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/wait_classes_global_by_avg_latency.frm to /var/lib/mysql/sys/wait_classes_global_by_avg_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024wait_classes_global_by_avg_latency.frm to /var/lib/mysql/sys/x@0024wait_classes_global_by_avg_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/wait_classes_global_by_latency.frm to /var/lib/mysql/sys/wait_classes_global_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024wait_classes_global_by_latency.frm to /var/lib/mysql/sys/x@0024wait_classes_global_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024waits_by_user_by_latency.frm to /var/lib/mysql/sys/x@0024waits_by_user_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024waits_by_host_by_latency.frm to /var/lib/mysql/sys/x@0024waits_by_host_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /var/lib/mysql/sys/x@0024waits_global_by_latency.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./sys/session_ssl_status.frm to /var/lib/mysql/sys/session_ssl_status.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/db.opt to /var/lib/mysql/zabbix/db.opt
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/maintenances_groups.frm to /var/lib/mysql/zabbix/maintenances_groups.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/housekeeper.frm to /var/lib/mysql/zabbix/housekeeper.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/users.frm to /var/lib/mysql/zabbix/users.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opmessage.frm to /var/lib/mysql/zabbix/opmessage.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opgroup.frm to /var/lib/mysql/zabbix/opgroup.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/mappings.frm to /var/lib/mysql/zabbix/mappings.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/graph_theme.frm to /var/lib/mysql/zabbix/graph_theme.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/expressions.frm to /var/lib/mysql/zabbix/expressions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/images.frm to /var/lib/mysql/zabbix/images.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/module.frm to /var/lib/mysql/zabbix/module.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/maintenances.frm to /var/lib/mysql/zabbix/maintenances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_condition.frm to /var/lib/mysql/zabbix/corr_condition.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/valuemaps.frm to /var/lib/mysql/zabbix/valuemaps.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httpstepitem.frm to /var/lib/mysql/zabbix/httpstepitem.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opinventory.frm to /var/lib/mysql/zabbix/opinventory.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/media_type.frm to /var/lib/mysql/zabbix/media_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/trends_uint.frm to /var/lib/mysql/zabbix/trends_uint.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/problem.frm to /var/lib/mysql/zabbix/problem.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/actions.frm to /var/lib/mysql/zabbix/actions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opmessage_grp.frm to /var/lib/mysql/zabbix/opmessage_grp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/optemplate.frm to /var/lib/mysql/zabbix/optemplate.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/functions.frm to /var/lib/mysql/zabbix/functions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmaps_links.frm to /var/lib/mysql/zabbix/sysmaps_links.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/proxy_dhistory.frm to /var/lib/mysql/zabbix/proxy_dhistory.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/tag_filter.frm to /var/lib/mysql/zabbix/tag_filter.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dchecks.frm to /var/lib/mysql/zabbix/dchecks.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/timeperiods.frm to /var/lib/mysql/zabbix/timeperiods.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/escalations.frm to /var/lib/mysql/zabbix/escalations.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/widget.frm to /var/lib/mysql/zabbix/widget.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/hstgrp.frm to /var/lib/mysql/zabbix/hstgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/items_applications.frm to /var/lib/mysql/zabbix/items_applications.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/globalvars.frm to /var/lib/mysql/zabbix/globalvars.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/group_prototype.frm to /var/lib/mysql/zabbix/group_prototype.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/group_discovery.frm to /var/lib/mysql/zabbix/group_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/maintenances_hosts.frm to /var/lib/mysql/zabbix/maintenances_hosts.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/item_discovery.frm to /var/lib/mysql/zabbix/item_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/screens.frm to /var/lib/mysql/zabbix/screens.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/hosts_templates.frm to /var/lib/mysql/zabbix/hosts_templates.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/events.frm to /var/lib/mysql/zabbix/events.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/application_template.frm to /var/lib/mysql/zabbix/application_template.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/screens_items.frm to /var/lib/mysql/zabbix/screens_items.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmaps_elements.frm to /var/lib/mysql/zabbix/sysmaps_elements.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/history_text.frm to /var/lib/mysql/zabbix/history_text.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/trigger_tag.frm to /var/lib/mysql/zabbix/trigger_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/screen_user.frm to /var/lib/mysql/zabbix/screen_user.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/icon_mapping.frm to /var/lib/mysql/zabbix/icon_mapping.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/proxy_history.frm to /var/lib/mysql/zabbix/proxy_history.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/event_tag.frm to /var/lib/mysql/zabbix/event_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/screen_usrgrp.frm to /var/lib/mysql/zabbix/screen_usrgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/services.frm to /var/lib/mysql/zabbix/services.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/history.frm to /var/lib/mysql/zabbix/history.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/host_discovery.frm to /var/lib/mysql/zabbix/host_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/slideshows.frm to /var/lib/mysql/zabbix/slideshows.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmaps.frm to /var/lib/mysql/zabbix/sysmaps.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/history_uint.frm to /var/lib/mysql/zabbix/history_uint.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/item_condition.frm to /var/lib/mysql/zabbix/item_condition.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/slideshow_user.frm to /var/lib/mysql/zabbix/slideshow_user.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmaps_link_triggers.frm to /var/lib/mysql/zabbix/sysmaps_link_triggers.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/autoreg_host.frm to /var/lib/mysql/zabbix/autoreg_host.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/host_tag.frm to /var/lib/mysql/zabbix/host_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/slideshow_usrgrp.frm to /var/lib/mysql/zabbix/slideshow_usrgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opcommand.frm to /var/lib/mysql/zabbix/opcommand.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/conditions.frm to /var/lib/mysql/zabbix/conditions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/globalmacro.frm to /var/lib/mysql/zabbix/globalmacro.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/ids.frm to /var/lib/mysql/zabbix/ids.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dservices.frm to /var/lib/mysql/zabbix/dservices.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/hosts.frm to /var/lib/mysql/zabbix/hosts.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/slides.frm to /var/lib/mysql/zabbix/slides.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opcommand_hst.frm to /var/lib/mysql/zabbix/opcommand_hst.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/config.frm to /var/lib/mysql/zabbix/config.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/graphs_items.frm to /var/lib/mysql/zabbix/graphs_items.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_user.frm to /var/lib/mysql/zabbix/sysmap_user.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/proxy_autoreg_host.frm to /var/lib/mysql/zabbix/proxy_autoreg_host.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/drules.frm to /var/lib/mysql/zabbix/drules.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_usrgrp.frm to /var/lib/mysql/zabbix/sysmap_usrgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dhosts.frm to /var/lib/mysql/zabbix/dhosts.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_operation.frm to /var/lib/mysql/zabbix/corr_operation.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/triggers.frm to /var/lib/mysql/zabbix/triggers.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_element_url.frm to /var/lib/mysql/zabbix/sysmap_element_url.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/graph_discovery.frm to /var/lib/mysql/zabbix/graph_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/applications.frm to /var/lib/mysql/zabbix/applications.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/scripts.frm to /var/lib/mysql/zabbix/scripts.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/acknowledges.frm to /var/lib/mysql/zabbix/acknowledges.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/problem_tag.frm to /var/lib/mysql/zabbix/problem_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/media_type_param.frm to /var/lib/mysql/zabbix/media_type_param.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/users_groups.frm to /var/lib/mysql/zabbix/users_groups.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/operations.frm to /var/lib/mysql/zabbix/operations.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opcommand_grp.frm to /var/lib/mysql/zabbix/opcommand_grp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/trigger_depends.frm to /var/lib/mysql/zabbix/trigger_depends.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/hostmacro.frm to /var/lib/mysql/zabbix/hostmacro.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/media.frm to /var/lib/mysql/zabbix/media.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/history_str.frm to /var/lib/mysql/zabbix/history_str.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sessions.frm to /var/lib/mysql/zabbix/sessions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httptest.frm to /var/lib/mysql/zabbix/httptest.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/maintenances_windows.frm to /var/lib/mysql/zabbix/maintenances_windows.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/interface_discovery.frm to /var/lib/mysql/zabbix/interface_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httpstep.frm to /var/lib/mysql/zabbix/httpstep.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opmessage_usr.frm to /var/lib/mysql/zabbix/opmessage_usr.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/opconditions.frm to /var/lib/mysql/zabbix/opconditions.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/graphs.frm to /var/lib/mysql/zabbix/graphs.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/hosts_groups.frm to /var/lib/mysql/zabbix/hosts_groups.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/history_log.frm to /var/lib/mysql/zabbix/history_log.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task.frm to /var/lib/mysql/zabbix/task.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/interface.frm to /var/lib/mysql/zabbix/interface.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/trends.frm to /var/lib/mysql/zabbix/trends.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/item_rtdata.frm to /var/lib/mysql/zabbix/item_rtdata.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/items.frm to /var/lib/mysql/zabbix/items.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httptestitem.frm to /var/lib/mysql/zabbix/httptestitem.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/media_type_message.frm to /var/lib/mysql/zabbix/media_type_message.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/usrgrp.frm to /var/lib/mysql/zabbix/usrgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_url.frm to /var/lib/mysql/zabbix/sysmap_url.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/auditlog.frm to /var/lib/mysql/zabbix/auditlog.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/profiles.frm to /var/lib/mysql/zabbix/profiles.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/trigger_discovery.frm to /var/lib/mysql/zabbix/trigger_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/alerts.frm to /var/lib/mysql/zabbix/alerts.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/auditlog_details.frm to /var/lib/mysql/zabbix/auditlog_details.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/event_recovery.frm to /var/lib/mysql/zabbix/event_recovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/services_times.frm to /var/lib/mysql/zabbix/services_times.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/host_inventory.frm to /var/lib/mysql/zabbix/host_inventory.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/rights.frm to /var/lib/mysql/zabbix/rights.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/services_links.frm to /var/lib/mysql/zabbix/services_links.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/icon_map.frm to /var/lib/mysql/zabbix/icon_map.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/regexps.frm to /var/lib/mysql/zabbix/regexps.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/service_alarms.frm to /var/lib/mysql/zabbix/service_alarms.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/correlation.frm to /var/lib/mysql/zabbix/correlation.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_condition_group.frm to /var/lib/mysql/zabbix/corr_condition_group.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_condition_tagpair.frm to /var/lib/mysql/zabbix/corr_condition_tagpair.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_condition_tagvalue.frm to /var/lib/mysql/zabbix/corr_condition_tagvalue.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_close_problem.frm to /var/lib/mysql/zabbix/task_close_problem.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/item_preproc.frm to /var/lib/mysql/zabbix/item_preproc.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_remote_command.frm to /var/lib/mysql/zabbix/task_remote_command.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_remote_command_result.frm to /var/lib/mysql/zabbix/task_remote_command_result.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_data.frm to /var/lib/mysql/zabbix/task_data.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_result.frm to /var/lib/mysql/zabbix/task_result.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_acknowledge.frm to /var/lib/mysql/zabbix/task_acknowledge.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_shape.frm to /var/lib/mysql/zabbix/sysmap_shape.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/sysmap_element_trigger.frm to /var/lib/mysql/zabbix/sysmap_element_trigger.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httptest_field.frm to /var/lib/mysql/zabbix/httptest_field.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/httpstep_field.frm to /var/lib/mysql/zabbix/httpstep_field.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dashboard.frm to /var/lib/mysql/zabbix/dashboard.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dashboard_user.frm to /var/lib/mysql/zabbix/dashboard_user.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dashboard_usrgrp.frm to /var/lib/mysql/zabbix/dashboard_usrgrp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/event_suppress.frm to /var/lib/mysql/zabbix/event_suppress.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_opdiscover.frm to /var/lib/mysql/zabbix/lld_override_opdiscover.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_ophistory.frm to /var/lib/mysql/zabbix/lld_override_ophistory.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_opseverity.frm to /var/lib/mysql/zabbix/lld_override_opseverity.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_optag.frm to /var/lib/mysql/zabbix/lld_override_optag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/application_prototype.frm to /var/lib/mysql/zabbix/application_prototype.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/item_application_prototype.frm to /var/lib/mysql/zabbix/item_application_prototype.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/task_check_now.frm to /var/lib/mysql/zabbix/task_check_now.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/application_discovery.frm to /var/lib/mysql/zabbix/application_discovery.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/widget_field.frm to /var/lib/mysql/zabbix/widget_field.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/maintenance_tag.frm to /var/lib/mysql/zabbix/maintenance_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_macro_path.frm to /var/lib/mysql/zabbix/lld_macro_path.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/interface_snmp.frm to /var/lib/mysql/zabbix/interface_snmp.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_optemplate.frm to /var/lib/mysql/zabbix/lld_override_optemplate.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/config_autoreg_tls.frm to /var/lib/mysql/zabbix/config_autoreg_tls.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override.frm to /var/lib/mysql/zabbix/lld_override.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_condition.frm to /var/lib/mysql/zabbix/lld_override_condition.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_operation.frm to /var/lib/mysql/zabbix/lld_override_operation.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_opstatus.frm to /var/lib/mysql/zabbix/lld_override_opstatus.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_opperiod.frm to /var/lib/mysql/zabbix/lld_override_opperiod.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_optrends.frm to /var/lib/mysql/zabbix/lld_override_optrends.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/lld_override_opinventory.frm to /var/lib/mysql/zabbix/lld_override_opinventory.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/dbversion.frm to /var/lib/mysql/zabbix/dbversion.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./zabbix/corr_condition_tag.frm to /var/lib/mysql/zabbix/corr_condition_tag.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/cond_instances.frm to /var/lib/mysql/performance_schema/cond_instances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_current.frm to /var/lib/mysql/performance_schema/events_waits_current.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_history.frm to /var/lib/mysql/performance_schema/events_waits_history.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_history_long.frm to /var/lib/mysql/performance_schema/events_waits_history_long.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_instance.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_host_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_user_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_account_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/file_instances.frm to /var/lib/mysql/performance_schema/file_instances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /var/lib/mysql/performance_schema/file_summary_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/file_summary_by_instance.frm to /var/lib/mysql/performance_schema/file_summary_by_instance.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/socket_instances.frm to /var/lib/mysql/performance_schema/socket_instances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /var/lib/mysql/performance_schema/socket_summary_by_instance.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /var/lib/mysql/performance_schema/socket_summary_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/host_cache.frm to /var/lib/mysql/performance_schema/host_cache.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /var/lib/mysql/performance_schema/objects_summary_global_by_type.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/performance_timers.frm to /var/lib/mysql/performance_schema/performance_timers.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/rwlock_instances.frm to /var/lib/mysql/performance_schema/rwlock_instances.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/setup_actors.frm to /var/lib/mysql/performance_schema/setup_actors.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/setup_consumers.frm to /var/lib/mysql/performance_schema/setup_consumers.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/setup_instruments.frm to /var/lib/mysql/performance_schema/setup_instruments.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/setup_objects.frm to /var/lib/mysql/performance_schema/setup_objects.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/setup_timers.frm to /var/lib/mysql/performance_schema/setup_timers.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /var/lib/mysql/performance_schema/table_io_waits_summary_by_index_usage.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /var/lib/mysql/performance_schema/table_io_waits_summary_by_table.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm to /var/lib/mysql/performance_schema/table_lock_waits_summary_by_table.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/threads.frm to /var/lib/mysql/performance_schema/threads.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/processlist.frm to /var/lib/mysql/performance_schema/processlist.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_stages_current.frm to /var/lib/mysql/performance_schema/events_stages_current.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_stages_history.frm to /var/lib/mysql/performance_schema/events_stages_history.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_stages_history_long.frm to /var/lib/mysql/performance_schema/events_stages_history_long.frm
221109 00:46:38 [01] ...done
221109 00:46:38 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_thread_by_event_name.frm
221109 00:46:38 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_host_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_user_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_by_account_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_stages_summary_global_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_current.frm to /var/lib/mysql/performance_schema/events_statements_current.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_history.frm to /var/lib/mysql/performance_schema/events_statements_history.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_history_long.frm to /var/lib/mysql/performance_schema/events_statements_history_long.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_thread_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_host_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_user_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_account_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_statements_summary_global_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_current.frm to /var/lib/mysql/performance_schema/events_transactions_current.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_history.frm to /var/lib/mysql/performance_schema/events_transactions_history.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_history_long.frm to /var/lib/mysql/performance_schema/events_transactions_history_long.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_transactions_summary_by_thread_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/events_transactions_summary_by_host_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/events_transactions_summary_by_user_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/events_transactions_summary_by_account_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_transactions_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_transactions_summary_global_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/hosts.frm to /var/lib/mysql/performance_schema/hosts.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/users.frm to /var/lib/mysql/performance_schema/users.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/accounts.frm to /var/lib/mysql/performance_schema/accounts.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/memory_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/memory_summary_global_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/memory_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/memory_summary_by_thread_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/memory_summary_by_account_by_event_name.frm to /var/lib/mysql/performance_schema/memory_summary_by_account_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/memory_summary_by_host_by_event_name.frm to /var/lib/mysql/performance_schema/memory_summary_by_host_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/memory_summary_by_user_by_event_name.frm to /var/lib/mysql/performance_schema/memory_summary_by_user_by_event_name.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_digest.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/events_statements_summary_by_program.frm to /var/lib/mysql/performance_schema/events_statements_summary_by_program.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/prepared_statements_instances.frm to /var/lib/mysql/performance_schema/prepared_statements_instances.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_connection_configuration.frm to /var/lib/mysql/performance_schema/replication_connection_configuration.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_group_member_stats.frm to /var/lib/mysql/performance_schema/replication_group_member_stats.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_group_members.frm to /var/lib/mysql/performance_schema/replication_group_members.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_connection_status.frm to /var/lib/mysql/performance_schema/replication_connection_status.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_applier_configuration.frm to /var/lib/mysql/performance_schema/replication_applier_configuration.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_applier_status.frm to /var/lib/mysql/performance_schema/replication_applier_status.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_applier_status_by_coordinator.frm to /var/lib/mysql/performance_schema/replication_applier_status_by_coordinator.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/replication_applier_status_by_worker.frm to /var/lib/mysql/performance_schema/replication_applier_status_by_worker.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/session_connect_attrs.frm to /var/lib/mysql/performance_schema/session_connect_attrs.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /var/lib/mysql/performance_schema/session_account_connect_attrs.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/table_handles.frm to /var/lib/mysql/performance_schema/table_handles.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/metadata_locks.frm to /var/lib/mysql/performance_schema/metadata_locks.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/user_variables_by_thread.frm to /var/lib/mysql/performance_schema/user_variables_by_thread.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/variables_by_thread.frm to /var/lib/mysql/performance_schema/variables_by_thread.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/global_variables.frm to /var/lib/mysql/performance_schema/global_variables.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/session_variables.frm to /var/lib/mysql/performance_schema/session_variables.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/status_by_thread.frm to /var/lib/mysql/performance_schema/status_by_thread.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/status_by_user.frm to /var/lib/mysql/performance_schema/status_by_user.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/status_by_host.frm to /var/lib/mysql/performance_schema/status_by_host.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/status_by_account.frm to /var/lib/mysql/performance_schema/status_by_account.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/global_status.frm to /var/lib/mysql/performance_schema/global_status.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./performance_schema/session_status.frm to /var/lib/mysql/performance_schema/session_status.frm
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id
221109 00:46:39 [01] ...done
221109 00:46:39 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
221109 00:46:39 [01] ...done
221109 00:46:39 completed OK!
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql/
[root@mysql-server ~]# ls /var/lib/mysql/
ib_buffer_pool ib_logfile0 mysql sys xtrabackup_info zabbix
ibdata1 ib_logfile1 performance_schema testdb xtrabackup_master_key_id
启动mysqld
[root@mysql-server ~]# systemctl start mysqld
[root@mysql-server ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 三 2022-11-09 00:47:12 CST; 9s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 18041 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 18020 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 18044 (mysqld)
Tasks: 28
CGroup: /system.slice/mysqld.service
└─18044 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
11月 09 00:47:10 mysql-server systemd[1]: Starting MySQL Server...
11月 09 00:47:12 mysql-server systemd[1]: Started MySQL Server.
[root@mysql-server ~]# mysql -uroot -p123456
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.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from testdb.test2
-> ;
+------+
| id |
+------+
| 888 |
| 999 |
+------+
2 rows in set (0.01 sec)
mysql>
mysql> quit;
Bye
[root@mysql-server ~]#
9.记录的导入和导出
记录的导出
[root@mysql-server ~]# vim /etc/my.cnf
[mysqld]
secure-file-priv=/backup #mysql不信任该目录
slow_query_log=1
long_query_time=3
mysql用户没有权限
[root@mysql-server ~]# mkdir /backup
[root@mysql-server ~]# chown mysql.mysql /backup
[root@mysql-server ~]# ls -d /backup
/backup
[root@mysql-server ~]# ls -d /backup -l
drwxr-xr-x 2 mysql mysql 6 11月 9 01:09 /backup
登录mysql数据库
[root@mysql-server ~]# mysql -uroot -p123456
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 167
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testdb;
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> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
| test2 |
+------------------+
2 rows in set (0.00 sec)
mysql> show table test2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test2' at line 1
mysql> select * from test2
-> ;
+------+
| id |
+------+
| 888 |
| 999 |
+------+
2 rows in set (0.00 sec)
mysql> select * from test2 into outfile '/backup/testdb.tset2.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> system ls #使用system命令可以在mysql中使用在命令行的命令
anaconda-ks.cfg zabbix 模板 图片 下载 桌面
initial-setup-ks.cfg 公共 视频 文档 音乐
mysql> system systemctl restart mysqld #重启mysql
mysql> select * from test2 into outfile '/backup/testdb.tset2.txt'; #导出文件
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: testdb
Query OK, 2 rows affected (0.08 sec)
mysql> exti;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exti' at line 1
mysql> exit;
Bye
[root@mysql-server ~]# ls /backup
testdb.tset2.txt
[root@mysql-server ~]# ls /backup
testdb.tset2.txt
#以文本导出
[root@mysql-server ~]# mysql -uroot -p'123456' -e'select * from testdb.test2' > /backup/testdb.test3.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# ls /backup/testdb.test3.txt
/backup/testdb.test3.txt
[root@mysql-server ~]# cat /backup/testdb.test3.txt
id
888
999
#以xml格式导出
[root@mysql-server ~]# mysql -uroot -p123456 --xml -e'select * from testdb.test2' > /backup/testdb.test4.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# cat /backup/testdb.test4.txt
<?xml version="1.0"?>
<resultset statement="select * from testdb.test2
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="id">888</field>
</row>
<row>
<field name="id">999</field>
</row>
</resultset>
#以html格式导出
[root@mysql-server ~]# mysql -uroot -p123456 --html -e'select * from testdb.test2' > /backup/testdb.test5.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-server ~]# cat /backup/testdb.test5.txt
<TABLE BORDER=1><TR><TH>id</TH></TR><TR><TD>888</TD></TR><TR><TD>999</TD></TR></TABLE>[root@mysql-server ~]#
导入文本文件
删除表的内容：因为表的导出和导入只备份表记录，不会备份表的结构。因此需要通过 mysqldump 备份表的结构，恢复时先恢复表结构，在导入数据。
[root@mysql-server ~]#cat /mysql -uroot -p123456
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 151
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testdb;
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> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test2 |
+------------------+
2 rows in set (0.00 sec)
mysql> delete from testdb.test2; #删除表的内容
Query OK, 2 rows affected (0.02 sec)
mysql> select * from testdb.test2;
Empty set (0.00 sec)
mysql> desc testdb.test2;#表的结构依然存在
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.05 sec)
[root@mysql-server ~]# ls /backup
testdb.test3.txt testdb.test4.txt testdb.test5.txt testdb.tset2.txt
[root@mysql-server ~]# mysql -uroot -p123456
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 205
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> load data infile '/backup/testdb.tset2.txt' into table testdb.test2; #将/backup/testdb.tset2.txt的文件导入到testdb库中的test2表里
Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from testdb.test2;
+------+
| id |
+------+
| 888 |
| 999 |
+------+
2 rows in set (0.00 sec)
mysql>
10.mysqldump+binlog
优势：
1.自动记录日志position位置
2.可用性，一致性
语法：
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
参数说明：
--single-transaction #InnoDB 一致性 服务可用性
--master-data=1|2 #该选项将会记录binlog的日志位置与文件名并追加到文件中。
库的范围
帮助
[root@mysql-server ~]#mysqldump --help
备份练习
1.准备两套密码分别为123456和guan123456
2.准备库 testdb
[root@mysql-server ~]# mysql -uroot -p123456
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 244
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> create table testdb.test2(id int(100));
Query OK, 0 rows affected (0.10 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
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 testdb.test2 values(888);
Query OK, 1 row affected (0.02 sec)
mysql> insert into testdb.test2 values(999);
Query OK, 1 row affected (0.00 sec)
mysql> insert into testdb.test2 values (111),(222),(333);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| |
| test2 |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from testdb.test2;
+------+
| id |
+------+
| 888 |
| 999 |
| 111 |
| 222 |
| 333 |
+------+
5 rows in set (0.00 sec)
mysql>
执行备份
[root@mysql-server ~]# rm -rf /backup
[root@mysql-server ~]# mkdir /backup
[root@mysql-server ~]# ls /backup
[root@mysql-server ~]# date +%F-%H //一种时间输出格式
2022-111-08-00
//这里有个错误提醒，二进制日志没开启
[root@mysql-server ~]# mysqldump -uroot -p'123456' --all-databases --single-transaction --master-data=2 --flush-logs > /backup/`date +%F-%H`mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: Binlogging on server not active
在进行mysqldump备份的时候报错：
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)
1
检查performance_schema 数据库发现不存在
原因：可能是升级导致该表损坏或者误删除
解决方法：
执行下面的命令，重新生成了sys、performance_schema数据库
mysql_upgrade -u root -p --force
重启mysqld
[root@mysql-server ~]# systemctl restart mysqld
//开启二进制日志
[root@localhost ~]# vim /etc/my.cnf //在里面添加下面的内容
[mysqld]
log_bin=mysql_bin
server_id=1
[root@mysql-server ~]# systemctl restart mysqld //重启数据库
[root@mysql-server ~]# mysql -uroot -p'123456' //登录数据库查看一下二进制日志状态，ON就是开启成功
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
//把二进制日志没开启之前的备份删除掉，再重新执行一下备份操作
//master-data=2注释掉日志记录，flush-logs刷新日志
[root@mysql-server ~]# mysqldump -uroot -p'123456' --all-databases --single-transaction --master-data=2 --flush-logs > /backup/`date +%F-%H`mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /backup/
[root@mysql-server ~]# ls /backup
2022-11-08-13mysql-all.sql
[root@mysql-server ~]# ls /var/lib/mysql
aria_log.00000001 ib_buffer_pool mysql_bin.index public_key.pem
aria_log_control ibdata1 mysql-server-slow.log server-cert.pem
auto.cnf ib_logfile0 mysql.sock server-key.pem
ca-key.pem ib_logfile1 mysql.sock.lock sys
ca.pem ibtmp1 mysql_upgrade_info testdb
client-cert.pem mysql performance_schema zabbix
client-key.pem mysql_bin.000001 private_key.pem zabbix-proxy-slow.log
观察备份数据
[root@mysql-server ~]# vim /backup/2022-11-08-13mysql-all.sql
//在前面备份时如果写的是master-data=1,那么在CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154的前面就不会有注释了
[root@mysql-server ~]# vim /backup/2022-11-08-13mysql-all.sql
//第22行可以看到mysql_bin.0000001,在/etc/lib/mysql里面有对应的文件
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;
LOCK TABLES `columns_priv` WRITE; //观察各种锁机制，用来保证数据的一致性
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;
备份之后数据又有变更了
[root@mysql-server ~]# mysql -uroot -p123456
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 53
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> create database testdb2;
Query OK, 1 row affected (0.00 sec)
mysql> create table testdb2.test2(id int(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into testdb2.test2 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from testdb2.test2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> quit;
Bye
[root@mysql-server ~]# systemctl restart mysqld
[root@mysql-server ~]# ls /var/lib/mysql
aria_log.00000001 ibdata1 mysql-server-slow.log server-key.pem
aria_log_control ib_logfile0 mysql.sock sys
auto.cnf ib_logfile1 mysql.sock.lock testdb
ca-key.pem ibtmp1 mysql_upgrade_info testdb2
ca.pem mysql performance_schema zabbix
client-cert.pem mysql_bin.000001 private_key.pem zabbix-proxy-slow.log
client-key.pem mysql_bin.000002 public_key.pem
ib_buffer_pool mysql_bin.index server-cert.pem
[root@mysql-server ~]# cp /var/lib/mysql/*bin* .
[root@mysql-server ~]# ls
[root@mysql-server ~]# ls
anaconda-ks.cfg mysql_bin.000001 mysql_bin.index 公共 视频 文档 音乐
initial-setup-ks.cfg mysql_bin.000002 zabbix 模板 图片 下载 桌面
[root@mysql-server ~]# systemctl stop mysqld
模拟清理环境
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
root@mysql-server ~]# ls /var/lib/mysql
[root@mysql-server ~]#
启动数据库
[root@mysql-server ~]# systemctl start mysqld
[root@mysql-server ~]# grep password /var/log/mysqld.log
2022-11-08T06:13:24.764470Z 1 [Note] A temporary password is generated for root@localhost: jCCzW8jNPw>f
//这里更改一下数据库的密码可以更直观的感受到数据库恢复数据的过程
[root@mysql-server ~]# mysqladmin -uroot -p're%f<7H1bi7s' password 'guan123456'
装完mysql启动服务，第一次使用需要重置密码，显示我输入的密码"guan123456"太简单,在个人练习过程中，不想把密码设置太复杂
[root@mysql-server ~]# grep password /var/log/mysql.log
2022-11-08T06:13:24.764470Z 1 [Note] A temporary password is generated for root@localhost: jCCzW8jNPw>f
# mysqladmin -uroot -p"jCCzW8jNPw>f" password "guan123456"
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'
解决方案：
# mysql -uroot -p'jCCzW8jNPw>f'
(1)修改validate_password_policy参数值为0(1为开启复杂策略)
注意：此参数(policy)必须优先修改，因为policy策略不修改为0会影响下面的length参数。
mysql> set global validate_password_policy=0;
(2)修改validate_password_length参数值为1
mysql> set global validate_password_length=1;
退出重新修改密码
mysql > exit;
[root@mysql-server ~]# mysqladmin -uroot -p'jCCzW8jNPw>f' password'guan123456'
[root@mysql-server ~]# mysql -uroot -p'guan123456'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
MySQL恢复数据
11.MySQL恢复数据
//恢复数据
[root@mysql-server ~]# mysql -uroot -p'guan123456' < /backup/2022-11-08-00-mysql-all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
//刷新一下
[root@mysql-server ~]# mysql -uroot -p'guan123456' -e 'flush privileges'
mysql: [Warning] Using a password on the command line interface can be insecure.
//用新密码登录数据库发现报错，登不进去了，这时候要用旧密码登录了，千万要注意这里，
如果没改密码就无所谓了，一旦改了密码刷新之后新密码是登不进去的
[root@mysql-server ~]# mysql -uroot -p'guan123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-server ~]# mysql -uroot -p'123456'
mysql> show databases; //可以看到数据库已经恢复了,但是少了一个在备份之后创建的testdb2库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testdb;
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> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test2 |
+------------------+
1 row in set (0.01 sec)
mysql> select * from testdb.test2;
+------+
| id |
+------+
| 888 |
| 999 |
| 111 |
| 222 |
| 333 |
+------+
5 rows in set (0.00 sec)
mysql>
12.二进制日志恢复
观察二进制截取记录
如果在备份之后又操作了数据库，那么这些数据是没有备份的，想要恢复可以通过恢复二进制日志来恢复备份之后的一些操作
[root@mysql-server ~]# vim /backup/2022-11-08-13mysql-all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;
//mysql_bin.000000就不用写了，这里的数据要看2022-11-08-13mysql-all.sql
//在/etc/lib/mysql,里面有多少个二进制日志文件就要在mysql_bin.000001后面跟着增加多少个二进制文件
[root@mysql-server ~]# cp /var/lib/mysql/*bin* .
[root@mysql-server ~]# ls
anaconda-ks.cfg mysql_bin.000001 mysql_bin.index mysql_bin.000002
[root@mysql-server ~]# mysqlbinlog mysql_bin.000001 --start-position=154 | mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
//假如说在/etc/lib/mysql里面有mysql_bin.000001 mysql_bin.000002 mysql_bin.000003那么就要这样写
[root@mysql-server ~]# mysqlbinlog mysql_bin.000002 mysql_bin.000003 --start-position154 | mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
观察数据库是否恢复完整
[root@mysql-server ~]# mysql -uroot -p'guan123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql-server ~]# mysql -uroot -p'123456'
mysql> show databases; //可以看到数据库已经恢复了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb2 |
| testdb |
| zabbix |
+--------------------+
7 rows in set (0.00 sec)
mysql> use testdb2;
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> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test2 |
+------------------+
1 rows in set (0.00 sec)
mysql> select * from testdb2.test2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
13.误删除库的问题
如何保留人为删除的testdb3库(这种问题，能避就避)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb3 |
| zabbix |
+--------------------+
7 rows in set (0.00 sec)
mysql> desc testdb3.test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(100) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> drop database testdb3;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| zabbix |
+--------------------+
6 rows in set (0.01 sec)
mysql> quit;
Bye
[root@mysql-server ~]# mysqlbinlog mysql_bin.000001 mysql_bin.000002 ..bin.003.. ..bin.004.. > log.txt
[root@mysql-server ~]# vim log.txt #找到删除testdb3的记录,并将删除testdb3的记录从log.tex文件中删除
...
drop database testdb3
...
[root@mysql-server ~]# cat log.txt | mysql -p'123456' #将删除testdb3的记录去除后还原到mysql中，可能会包库已存在的错误
//需要清理环境
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# ls /var/lib/mysql/
//进行mysqldump还原
[root@mysql-server ~]# systemctl start mysqld
[root@mysql-server ~]# grep password /var/lib/mysqld.log
[root@mysql-server ~]# mysqladmin -uroot -p'jCCzW8jNPw>f' password'123456'
[root@mysql-server ~]# mysql -uroot -p'123456'
[root@mysql-server ~]# mysql -uroot -p'123456' < /backup/2022-11-08-13mysql-all.sql #完整
[root@mysql-server ~]# cat log.txt | mysql -p'123456' #二进制还原
14.数据库恢复时的多余日志
mysql> set sql_log_bin=0; //暂时关闭二进制日志文件
Query OK, 0 rows affected (0.00 sec)
mysql> source /backup/*.sql //读取二进制文件
mysql> set sql_log_bin=1; //读取二进制日志文件完后，开启二进制日志文件
Query OK, 0 rows affected (0.00 sec)
455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
