Mysql 8.0 XtraBackup+Mysqlbinlog 完全恢复
[TOC]
一、安装mysql 8.0.19
1.1 安装包准备
[root@mysql8 ~]# tar -Jxf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
[root@mysql8 ~]# mv mysql-8.0.19-linux-glibc2.12-x86_64 /usr/local/mysql
复制1.2创建mysql用户. 组
[root@mysql8 ~]# groupadd mysql
[root@mysql8 ~]# useradd -r -g mysql mysql
[root@mysql8 ~]# chown -R mysql.mysql /usr/local/mysql
复制1.3 MySQL初始化
– 5.7.6之后使用mysqld初始化，–initialize-insecure 该参数，默认密码为空
[root@mysql8 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2022-10-06T11:14:12.640246+08:00 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.19) initializing of server in progress as process 13389
2022-10-06T11:14:15.051146+08:00 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
复制1.4 配置MySQL参数文件
-- 测试环境，配置MySQL基本参数
[root@mysql8 ~]# cat > /etc/my.cnf <<"EOF"
> [mysqld]
> basedir=/usr/local/mysql
> datadir=/usr/local/mysql/data
> port=3306
> server_id=80193306
> log-bin
> skip-name-resolve
> character_set_server=utf8mb4
> default-time-zone = '+8:00'
> log_timestamps = SYSTEM
> default_authentication_plugin=mysql_native_password
> EOF
[root@mysql8 ~]#
-- 注：mysql 8.0远程连接，在参数文件的[mysqld]下添加：
default_authentication_plugin=mysql_native_password
复制1.5 配置环境变量
[root@mysql8 ~]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /root/.bashrc
[root@mysql8 ~]# source /root/.bashrc
复制1.6 配置MySQL服务
[root@mysql8 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql8 ~]# chmod 755 /etc/init.d/mysqld
[root@mysql8 ~]# chkconfig --add mysqld
[root@mysql8 ~]# chkconfig mysqld on
[root@mysql8 ~]# chkconfig --level 345 mysqld on
[root@mysql8 ~]# systemctl status mysqld
â— mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: inactive (dead)
Docs: man:systemd-sysv-generator(8)
复制1.7 启动MySQL
[root@mysql8 ~]# systemctl start mysqld
[root@mysql8 ~]# systemctl status mysqld
â— mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Wed 2022-10-05 10:14:17 CST; 3s ago
Docs: man:systemd-sysv-generator(8)
Process: 1207 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
CGroup: /docker/26968992884ed54bd76ebd667c8493037d77f83f5df382451dae94e4d2c7ffc3/system.slice/mysqld.service
â”─1218 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/m...
└─1429 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-...
Oct 05 10:14:15 mysql8 systemd[1]: Starting LSB: start and stop MySQL...
Oct 05 10:14:15 mysql8 mysqld[1207]: Starting MySQL.Logging to '/usr/local/mysql/data/mysql8.err'.
Oct 05 10:14:17 mysql8 mysqld[1207]: .[ OK ]
Oct 05 10:14:17 mysql8 systemd[1]: Started LSB: start and stop MySQL.
复制1.8 修改密码和允许远程登陆
-- 登录mysql，初始化默认密码为空
[root@mysql8 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 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>
-- 修改密码和允许远程登陆
mysql> alter user root@'localhost' identified with mysql_native_password by 'root123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'localhost' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create user root@'%' identified with mysql_native_password by 'root123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| user | host | grant_priv | super_priv | authentication_string | password_last_changed |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| root | % | Y | Y | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | 2022-10-05 10:16:33 |
| mysql.infoschema | localhost | N | N | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-05 10:08:47 |
| mysql.session | localhost | N | Y | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-05 10:08:47 |
| mysql.sys | localhost | N | N | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-05 10:08:47 |
| root | localhost | Y | Y | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | 2022-10-05 10:16:25 |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
--再次登录需要输入密码
[root@mysql8 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
复制1.9 关闭MySQL
[root@mysql8 ~]# mysqladmin -uroot -p shutdown
or
[root@mysql8 ~]# systemctl stop mysqld
复制二、安装XtraBackup
注意：
-
Percona Xtrabackup 8.0版本移除了innobackupex命令且只能备份8.0版本的MySQL，只能使用xtrabackup命令
-
2.4版本支持MySQL 5.11 , 5.5, 5.6 和5.7的版本，不支持mysql 8.0。
因为我们数据库是8.0版本，所以选择安装Xtrabackup 8.0版本。采用二进制方式安装。
2.1 安装包准备
[root@mysql8 ~]# ll percona-xtrabackup-8.0.11-Linux-x86_64.el7.tar.gz
-rw-r--r-- 1 root root 435770690 Oct 29 2020 percona-xtrabackup-8.0.11-Linux-x86_64.el7.tar.gz
复制2.2 安装xtrabackup
[root@mysql8 ~]# tar -zxvf percona-xtrabackup-8.0.11-Linux-x86_64.el7.tar.gz
[root@mysql8 ~]# mv percona-xtrabackup-8.0.11-Linux-x86_64 /usr/local/xtrabackup8011
复制2.3 配置环境变量
[root@mysql8 ~]# echo "export PATH=$PATH:/usr/local/xtrabackup8011/bin" >> /root/.bashrc
[root@mysql8 ~]# source /root/.bashrc
[root@mysql8 ~]#
[root@mysql8 ~]# xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=80193306 --log_bin
xtrabackup version 8.0.11 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 486c270)
复制三、模拟测试环境
3.1.创建测试表
--1.创建测试环境
mysql> create database test1 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> create table test1.users (id int primary key auto_increment,name varchar(20) not null unique,password varchar(100) not null,address varchar(200)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1.users (id,name,password,address) values (1,'zhang','1234',null),(2,'wang','4321','nanjing'), (3,'li','5678','suzhou');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create database test2 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> create table test2.articles (id int primary key auto_increment,content longtext not null) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test2.articles (id,content) values (11,'ninhao'),(12,'hello'),(13,'hi');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test1.users;
+----+-------+----------+---------+
| id | name | password | address |
+----+-------+----------+---------+
| 1 | zhang | 1234 | NULL |
| 2 | wang | 4321 | nanjing |
| 3 | li | 5678 | suzhou |
+----+-------+----------+---------+
3 rows in set (0.01 sec)
mysql> select * from test2.articles;
+----+---------+
| id | content |
+----+---------+
| 11 | ninhao |
| 12 | hello |
| 13 | hi |
+----+---------+
3 rows in set (0.00 sec)
复制3.2 xtrabackup全量备份
[root@mysql8 ~]# mkdir -p /backup/full
[root@mysql8 full]# xtrabackup -uroot -proot123 -S/tmp/mysql.sock --backup --no-timestamp --target-dir=/backup/full/full_backup_`date +%F`
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=80193306 --log_bin --innodb_data_home_dir=/usr/local/mysql/data
xtrabackup: recognized client arguments: --user=root --password=* --socket=/tmp/mysql.sock --backup=1 --target-dir=/backup/full/full_backup_2022-10-06
xtrabackup version 8.0.11 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 486c270)
221006 11:16:31 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
221006 11:16:31 version_check Connected to MySQL server
221006 11:16:31 version_check Executing a version check against the server...
221006 11:16:31 version_check Done.
221006 11:16:31 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 8.0.19
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = /usr/local/mysql/data
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
Number of pools: 1
WARNING: unknown option --no-timestamp
221006 11:16:32 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
xtrabackup: Redo Log Archiving is not set up.
221006 11:16:32 >> log scanned up to (19996708)
xtrabackup: Generating a list of tablespaces
Directories to scan '/usr/local/mysql/data;./;.'
Scanning '/usr/local/mysql/data/'
Completed space ID check of 2 files.
Allocated tablespace ID 2 for test2/articles, old maximum was 0
Using undo tablespace '/usr/local/mysql/data/undo_001'.
Using undo tablespace '/usr/local/mysql/data/undo_002'.
Opened 2 existing undo tablespaces.
221006 11:16:32 [01] Copying /usr/local/mysql/data/ibdata1 to /backup/full/full_backup_2022-10-06/ibdata1
221006 11:16:32 [01] ...done
221006 11:16:32 [01] Copying /usr/local/mysql/data/sys/sys_config.ibd to /backup/full/full_backup_2022-10-06/sys/sys_config.ibd
221006 11:16:32 [01] ...done
221006 11:16:32 [01] Copying /usr/local/mysql/data/test2/articles.ibd to /backup/full/full_backup_2022-10-06/test2/articles.ibd
221006 11:16:32 [01] ...done
221006 11:16:32 [01] Copying /usr/local/mysql/data/mysql.ibd to /backup/full/full_backup_2022-10-06/mysql.ibd
221006 11:16:32 [01] ...done
221006 11:16:32 [01] Copying ./undo_002 to /backup/full/full_backup_2022-10-06/undo_002
221006 11:16:32 [01] ...done
221006 11:16:32 [01] Copying ./undo_001 to /backup/full/full_backup_2022-10-06/undo_001
221006 11:16:32 [01] ...done
221006 11:16:33 >> log scanned up to (19996708)
221006 11:16:33 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
221006 11:16:33 Executing FLUSH TABLES WITH READ LOCK...
221006 11:16:33 Starting to backup non-InnoDB tables and files
221006 11:16:33 [01] Copying mysql/general_log_201.sdi to /backup/full/full_backup_2022-10-06/mysql/general_log_201.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying mysql/general_log.CSM to /backup/full/full_backup_2022-10-06/mysql/general_log.CSM
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying mysql/general_log.CSV to /backup/full/full_backup_2022-10-06/mysql/general_log.CSV
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying mysql/slow_log_202.sdi to /backup/full/full_backup_2022-10-06/mysql/slow_log_202.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying mysql/slow_log.CSM to /backup/full/full_backup_2022-10-06/mysql/slow_log.CSM
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying mysql/slow_log.CSV to /backup/full/full_backup_2022-10-06/mysql/slow_log.CSV
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/cond_instances_77.sdi to /backup/full/full_backup_2022-10-06/performance_schema/cond_instances_77.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_cur_78.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_cur_78.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_his_79.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_his_79.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_his_80.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_his_80.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_81.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_81.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_82.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_82.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_83.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_83.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_84.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_84.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_85.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_85.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_waits_sum_86.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_waits_sum_86.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/file_instances_87.sdi to /backup/full/full_backup_2022-10-06/performance_schema/file_instances_87.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/file_summary_by__88.sdi to /backup/full/full_backup_2022-10-06/performance_schema/file_summary_by__88.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/file_summary_by__89.sdi to /backup/full/full_backup_2022-10-06/performance_schema/file_summary_by__89.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/host_cache_90.sdi to /backup/full/full_backup_2022-10-06/performance_schema/host_cache_90.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/mutex_instances_91.sdi to /backup/full/full_backup_2022-10-06/performance_schema/mutex_instances_91.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/objects_summary__92.sdi to /backup/full/full_backup_2022-10-06/performance_schema/objects_summary__92.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/performance_time_93.sdi to /backup/full/full_backup_2022-10-06/performance_schema/performance_time_93.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/rwlock_instances_94.sdi to /backup/full/full_backup_2022-10-06/performance_schema/rwlock_instances_94.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/setup_actors_95.sdi to /backup/full/full_backup_2022-10-06/performance_schema/setup_actors_95.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/setup_consumers_96.sdi to /backup/full/full_backup_2022-10-06/performance_schema/setup_consumers_96.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/setup_instrument_97.sdi to /backup/full/full_backup_2022-10-06/performance_schema/setup_instrument_97.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/setup_objects_98.sdi to /backup/full/full_backup_2022-10-06/performance_schema/setup_objects_98.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/setup_threads_99.sdi to /backup/full/full_backup_2022-10-06/performance_schema/setup_threads_99.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/table_io_waits_s_100.sdi to /backup/full/full_backup_2022-10-06/performance_schema/table_io_waits_s_100.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/table_io_waits_s_101.sdi to /backup/full/full_backup_2022-10-06/performance_schema/table_io_waits_s_101.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/table_lock_waits_102.sdi to /backup/full/full_backup_2022-10-06/performance_schema/table_lock_waits_102.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/threads_103.sdi to /backup/full/full_backup_2022-10-06/performance_schema/threads_103.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_cu_104.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_cu_104.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_hi_105.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_hi_105.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_hi_106.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_hi_106.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_su_107.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_su_107.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_su_108.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_su_108.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_su_109.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_su_109.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_su_110.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_su_110.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_stages_su_111.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_stages_su_111.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_112.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_112.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_113.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_113.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_114.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_114.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_115.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_115.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_116.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_116.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_117.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_117.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_118.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_118.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_119.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_119.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_120.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_120.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_121.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_121.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_122.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_122.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_statement_123.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_statement_123.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_124.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_124.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_125.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_125.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_126.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_126.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_127.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_127.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_128.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_128.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_129.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_129.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_130.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_130.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_transacti_131.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_transacti_131.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_errors_su_132.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_errors_su_132.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_errors_su_133.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_errors_su_133.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_errors_su_134.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_errors_su_134.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_errors_su_135.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_errors_su_135.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/events_errors_su_136.sdi to /backup/full/full_backup_2022-10-06/performance_schema/events_errors_su_136.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/users_137.sdi to /backup/full/full_backup_2022-10-06/performance_schema/users_137.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/accounts_138.sdi to /backup/full/full_backup_2022-10-06/performance_schema/accounts_138.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/hosts_139.sdi to /backup/full/full_backup_2022-10-06/performance_schema/hosts_139.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/socket_instances_140.sdi to /backup/full/full_backup_2022-10-06/performance_schema/socket_instances_140.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/socket_summary_b_141.sdi to /backup/full/full_backup_2022-10-06/performance_schema/socket_summary_b_141.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/socket_summary_b_142.sdi to /backup/full/full_backup_2022-10-06/performance_schema/socket_summary_b_142.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/session_connect__143.sdi to /backup/full/full_backup_2022-10-06/performance_schema/session_connect__143.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/session_account__144.sdi to /backup/full/full_backup_2022-10-06/performance_schema/session_account__144.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/keyring_keys_145.sdi to /backup/full/full_backup_2022-10-06/performance_schema/keyring_keys_145.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/memory_summary_g_146.sdi to /backup/full/full_backup_2022-10-06/performance_schema/memory_summary_g_146.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/memory_summary_b_147.sdi to /backup/full/full_backup_2022-10-06/performance_schema/memory_summary_b_147.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/memory_summary_b_148.sdi to /backup/full/full_backup_2022-10-06/performance_schema/memory_summary_b_148.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/memory_summary_b_149.sdi to /backup/full/full_backup_2022-10-06/performance_schema/memory_summary_b_149.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/memory_summary_b_150.sdi to /backup/full/full_backup_2022-10-06/performance_schema/memory_summary_b_150.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/table_handles_151.sdi to /backup/full/full_backup_2022-10-06/performance_schema/table_handles_151.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/metadata_locks_152.sdi to /backup/full/full_backup_2022-10-06/performance_schema/metadata_locks_152.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/data_locks_153.sdi to /backup/full/full_backup_2022-10-06/performance_schema/data_locks_153.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/data_lock_waits_154.sdi to /backup/full/full_backup_2022-10-06/performance_schema/data_lock_waits_154.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_conn_155.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_conn_155.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_grou_156.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_grou_156.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_conn_157.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_conn_157.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_158.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_158.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_159.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_159.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_160.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_160.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_161.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_161.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_grou_162.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_grou_162.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_163.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_163.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/replication_appl_164.sdi to /backup/full/full_backup_2022-10-06/performance_schema/replication_appl_164.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/log_status_165.sdi to /backup/full/full_backup_2022-10-06/performance_schema/log_status_165.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/prepared_stateme_166.sdi to /backup/full/full_backup_2022-10-06/performance_schema/prepared_stateme_166.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/user_variables_b_167.sdi to /backup/full/full_backup_2022-10-06/performance_schema/user_variables_b_167.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/status_by_accoun_168.sdi to /backup/full/full_backup_2022-10-06/performance_schema/status_by_accoun_168.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/status_by_host_169.sdi to /backup/full/full_backup_2022-10-06/performance_schema/status_by_host_169.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/status_by_thread_170.sdi to /backup/full/full_backup_2022-10-06/performance_schema/status_by_thread_170.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/status_by_user_171.sdi to /backup/full/full_backup_2022-10-06/performance_schema/status_by_user_171.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/global_status_172.sdi to /backup/full/full_backup_2022-10-06/performance_schema/global_status_172.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/session_status_173.sdi to /backup/full/full_backup_2022-10-06/performance_schema/session_status_173.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/variables_by_thr_174.sdi to /backup/full/full_backup_2022-10-06/performance_schema/variables_by_thr_174.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/global_variables_175.sdi to /backup/full/full_backup_2022-10-06/performance_schema/global_variables_175.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/session_variable_176.sdi to /backup/full/full_backup_2022-10-06/performance_schema/session_variable_176.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/variables_info_177.sdi to /backup/full/full_backup_2022-10-06/performance_schema/variables_info_177.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/persisted_variab_178.sdi to /backup/full/full_backup_2022-10-06/performance_schema/persisted_variab_178.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying performance_schema/user_defined_fun_179.sdi to /backup/full/full_backup_2022-10-06/performance_schema/user_defined_fun_179.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying test1/users_345.sdi to /backup/full/full_backup_2022-10-06/test1/users_345.sdi
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying test1/users.MYI to /backup/full/full_backup_2022-10-06/test1/users.MYI
221006 11:16:33 [01] ...done
221006 11:16:33 [01] Copying test1/users.MYD to /backup/full/full_backup_2022-10-06/test1/users.MYD
221006 11:16:33 [01] ...done
221006 11:16:33 Finished backing up non-InnoDB tables and files
221006 11:16:33 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
221006 11:16:33 Selecting LSN and binary log position from p_s.log_status
221006 11:16:33 [00] Copying /usr/local/mysql/data/mysql8-bin.000004 to /backup/full/full_backup_2022-10-06/mysql8-bin.000004 up to position 155
221006 11:16:33 [00] ...done
221006 11:16:33 [00] Writing /backup/full/full_backup_2022-10-06/mysql8-bin.index
221006 11:16:33 [00] ...done
221006 11:16:33 [00] Writing /backup/full/full_backup_2022-10-06/xtrabackup_binlog_info
221006 11:16:33 [00] ...done
221006 11:16:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '19996708'
xtrabackup: Stopping log copying thread at LSN 19996718.
221006 11:16:33 >> log scanned up to (19996728)
Starting to parse redo log at lsn = 19996708
221006 11:16:34 Executing UNLOCK TABLES
221006 11:16:34 All tables unlocked
221006 11:16:34 [00] Copying ib_buffer_pool to /backup/full/full_backup_2022-10-06/ib_buffer_pool
221006 11:16:34 [00] ...done
221006 11:16:34 Backup created in directory '/backup/full/full_backup_2022-10-06/'
MySQL binlog position: filename 'mysql8-bin.000004', position '155'
221006 11:16:34 [00] Writing /backup/full/full_backup_2022-10-06/backup-my.cnf
221006 11:16:34 [00] ...done
221006 11:16:34 [00] Writing /backup/full/full_backup_2022-10-06/xtrabackup_info
221006 11:16:34 [00] ...done
xtrabackup: Transaction log of lsn (19996708) to (19996738) was copied.
221006 11:16:35 completed OK!
复制3.3 模拟DML操作
mysql> insert into test1.users (id,name,password,address) values (4,'yan','123123','shanxi');
Query OK, 1 row affected (0.00 sec)
mysql> update test2.articles set content ='how are you' where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> delete from test1.users where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2.articles (id,content) values (14,'ninhao');
Query OK, 1 row affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test1.users (id,name,password,address) values (5,'shi','321','huaian');
Query OK, 1 row affected (0.00 sec)
-- 最终数据：
mysql> select * from test1.users;
+----+------+----------+---------+
| id | name | password | address |
+----+------+----------+---------+
| 5 | shi | 321 | huaian |
| 2 | wang | 4321 | nanjing |
| 3 | li | 5678 | suzhou |
| 4 | yan | 123123 | shanxi |
+----+------+----------+---------+
4 rows in set (0.00 sec)
mysql> select * from test2.articles;
+----+-------------+
| id | content |
+----+-------------+
| 11 | how are you |
| 12 | hello |
| 13 | hi |
| 14 | ninhao |
+----+-------------+
4 rows in set (0.00 sec)
复制3.4 使用xtrabackup 异机恢复
– 恢复环境准备过程，略。。。。
3.4.1 将备份拷贝到目标环境
3.4.2 恢复：prepare
-- 查看备份信息
[root@mysql8 full_backup_2022-10-06]# cat xtrabackup_binlog_info
mysql8-bin.000004 155
[root@mysql8 full_backup_2022-10-06]# xtrabackup --prepare --target-dir=/backup/full/full_backup_2022-10-06
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=80193306 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/full/full_backup_2022-10-06
xtrabackup version 8.0.11 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 486c270)
xtrabackup: cd to /backup/full/full_backup_2022-10-06/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(19996708)
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)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 5 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 19562803 in the system tablespace does not match the log sequence number 19996708 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 19996708, whereas checkpoint_lsn = 19996708
Doing recovery: scanned up to log sequence number 19996738
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 1573
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.18 started; log sequence number 19996738
Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 19996738
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
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 5 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Setting log file ./ib_logfile101 size to 48 MB
Setting log file ./ib_logfile1 size to 48 MB
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=19997196
Starting to parse redo log at lsn = 19997196, whereas checkpoint_lsn = 19997196
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 1573
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.18 started; log sequence number 19997196
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 19997196
221006 11:22:00 completed OK!
[root@mysql8 full_backup_2022-10-06]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 19996708
last_lsn = 19996718
flushed_lsn = 0
复制3.4.3 恢复：copy-back
[root@mysql8 full_backup_2022-10-06]# xtrabackup --copy-back --target-dir=/backup/full/full_backup_2022-10-06 --datadir=/usr/local/mysql/data/
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=80193306 --log_bin --innodb_data_home_dir=/usr/local/mysql/data --datadir=/usr/local/mysql/data/
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/backup/full/full_backup_2022-10-06
xtrabackup version 8.0.11 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 486c270)
221006 11:22:51 [01] Copying undo_001 to /usr/local/mysql/data/undo_001
221006 11:22:51 [01] ...done
221006 11:22:51 [01] Copying undo_002 to /usr/local/mysql/data/undo_002
221006 11:22:51 [01] ...done
221006 11:22:51 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ibdata1 to /usr/local/mysql/data/ibdata1
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying mysql8-bin.000004 to /usr/local/mysql/data/mysql8-bin.000004
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying mysql8-bin.index to /usr/local/mysql/data/mysql8-bin.index
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./sys/sys_config.ibd to /usr/local/mysql/data/sys/sys_config.ibd
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./test2/articles.ibd to /usr/local/mysql/data/test2/articles.ibd
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql.ibd to /usr/local/mysql/data/mysql.ibd
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/general_log_201.sdi to /usr/local/mysql/data/mysql/general_log_201.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/general_log.CSM to /usr/local/mysql/data/mysql/general_log.CSM
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/general_log.CSV to /usr/local/mysql/data/mysql/general_log.CSV
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/slow_log_202.sdi to /usr/local/mysql/data/mysql/slow_log_202.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/slow_log.CSM to /usr/local/mysql/data/mysql/slow_log.CSM
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./mysql/slow_log.CSV to /usr/local/mysql/data/mysql/slow_log.CSV
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/cond_instances_77.sdi to /usr/local/mysql/data/performance_schema/cond_instances_77.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_cur_78.sdi to /usr/local/mysql/data/performance_schema/events_waits_cur_78.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_his_79.sdi to /usr/local/mysql/data/performance_schema/events_waits_his_79.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_his_80.sdi to /usr/local/mysql/data/performance_schema/events_waits_his_80.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_81.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_81.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_82.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_82.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_83.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_83.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_84.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_84.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_85.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_85.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_waits_sum_86.sdi to /usr/local/mysql/data/performance_schema/events_waits_sum_86.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/file_instances_87.sdi to /usr/local/mysql/data/performance_schema/file_instances_87.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/file_summary_by__88.sdi to /usr/local/mysql/data/performance_schema/file_summary_by__88.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/file_summary_by__89.sdi to /usr/local/mysql/data/performance_schema/file_summary_by__89.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/host_cache_90.sdi to /usr/local/mysql/data/performance_schema/host_cache_90.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/mutex_instances_91.sdi to /usr/local/mysql/data/performance_schema/mutex_instances_91.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/objects_summary__92.sdi to /usr/local/mysql/data/performance_schema/objects_summary__92.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/performance_time_93.sdi to /usr/local/mysql/data/performance_schema/performance_time_93.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/rwlock_instances_94.sdi to /usr/local/mysql/data/performance_schema/rwlock_instances_94.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/setup_actors_95.sdi to /usr/local/mysql/data/performance_schema/setup_actors_95.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/setup_consumers_96.sdi to /usr/local/mysql/data/performance_schema/setup_consumers_96.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/setup_instrument_97.sdi to /usr/local/mysql/data/performance_schema/setup_instrument_97.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/setup_objects_98.sdi to /usr/local/mysql/data/performance_schema/setup_objects_98.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/setup_threads_99.sdi to /usr/local/mysql/data/performance_schema/setup_threads_99.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/table_io_waits_s_100.sdi to /usr/local/mysql/data/performance_schema/table_io_waits_s_100.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/table_io_waits_s_101.sdi to /usr/local/mysql/data/performance_schema/table_io_waits_s_101.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/table_lock_waits_102.sdi to /usr/local/mysql/data/performance_schema/table_lock_waits_102.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/threads_103.sdi to /usr/local/mysql/data/performance_schema/threads_103.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_cu_104.sdi to /usr/local/mysql/data/performance_schema/events_stages_cu_104.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_hi_105.sdi to /usr/local/mysql/data/performance_schema/events_stages_hi_105.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_hi_106.sdi to /usr/local/mysql/data/performance_schema/events_stages_hi_106.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_su_107.sdi to /usr/local/mysql/data/performance_schema/events_stages_su_107.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_su_108.sdi to /usr/local/mysql/data/performance_schema/events_stages_su_108.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_su_109.sdi to /usr/local/mysql/data/performance_schema/events_stages_su_109.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_su_110.sdi to /usr/local/mysql/data/performance_schema/events_stages_su_110.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_stages_su_111.sdi to /usr/local/mysql/data/performance_schema/events_stages_su_111.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_112.sdi to /usr/local/mysql/data/performance_schema/events_statement_112.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_113.sdi to /usr/local/mysql/data/performance_schema/events_statement_113.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_114.sdi to /usr/local/mysql/data/performance_schema/events_statement_114.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_115.sdi to /usr/local/mysql/data/performance_schema/events_statement_115.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_116.sdi to /usr/local/mysql/data/performance_schema/events_statement_116.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_117.sdi to /usr/local/mysql/data/performance_schema/events_statement_117.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_118.sdi to /usr/local/mysql/data/performance_schema/events_statement_118.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_119.sdi to /usr/local/mysql/data/performance_schema/events_statement_119.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_120.sdi to /usr/local/mysql/data/performance_schema/events_statement_120.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_121.sdi to /usr/local/mysql/data/performance_schema/events_statement_121.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_122.sdi to /usr/local/mysql/data/performance_schema/events_statement_122.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_statement_123.sdi to /usr/local/mysql/data/performance_schema/events_statement_123.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_124.sdi to /usr/local/mysql/data/performance_schema/events_transacti_124.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_125.sdi to /usr/local/mysql/data/performance_schema/events_transacti_125.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_126.sdi to /usr/local/mysql/data/performance_schema/events_transacti_126.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_127.sdi to /usr/local/mysql/data/performance_schema/events_transacti_127.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_128.sdi to /usr/local/mysql/data/performance_schema/events_transacti_128.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_129.sdi to /usr/local/mysql/data/performance_schema/events_transacti_129.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_130.sdi to /usr/local/mysql/data/performance_schema/events_transacti_130.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_transacti_131.sdi to /usr/local/mysql/data/performance_schema/events_transacti_131.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_errors_su_132.sdi to /usr/local/mysql/data/performance_schema/events_errors_su_132.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_errors_su_133.sdi to /usr/local/mysql/data/performance_schema/events_errors_su_133.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_errors_su_134.sdi to /usr/local/mysql/data/performance_schema/events_errors_su_134.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_errors_su_135.sdi to /usr/local/mysql/data/performance_schema/events_errors_su_135.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/events_errors_su_136.sdi to /usr/local/mysql/data/performance_schema/events_errors_su_136.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/users_137.sdi to /usr/local/mysql/data/performance_schema/users_137.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/accounts_138.sdi to /usr/local/mysql/data/performance_schema/accounts_138.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/hosts_139.sdi to /usr/local/mysql/data/performance_schema/hosts_139.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/socket_instances_140.sdi to /usr/local/mysql/data/performance_schema/socket_instances_140.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/socket_summary_b_141.sdi to /usr/local/mysql/data/performance_schema/socket_summary_b_141.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/socket_summary_b_142.sdi to /usr/local/mysql/data/performance_schema/socket_summary_b_142.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/session_connect__143.sdi to /usr/local/mysql/data/performance_schema/session_connect__143.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/session_account__144.sdi to /usr/local/mysql/data/performance_schema/session_account__144.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/keyring_keys_145.sdi to /usr/local/mysql/data/performance_schema/keyring_keys_145.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/memory_summary_g_146.sdi to /usr/local/mysql/data/performance_schema/memory_summary_g_146.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/memory_summary_b_147.sdi to /usr/local/mysql/data/performance_schema/memory_summary_b_147.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/memory_summary_b_148.sdi to /usr/local/mysql/data/performance_schema/memory_summary_b_148.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/memory_summary_b_149.sdi to /usr/local/mysql/data/performance_schema/memory_summary_b_149.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/memory_summary_b_150.sdi to /usr/local/mysql/data/performance_schema/memory_summary_b_150.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/table_handles_151.sdi to /usr/local/mysql/data/performance_schema/table_handles_151.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/metadata_locks_152.sdi to /usr/local/mysql/data/performance_schema/metadata_locks_152.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/data_locks_153.sdi to /usr/local/mysql/data/performance_schema/data_locks_153.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/data_lock_waits_154.sdi to /usr/local/mysql/data/performance_schema/data_lock_waits_154.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_conn_155.sdi to /usr/local/mysql/data/performance_schema/replication_conn_155.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_grou_156.sdi to /usr/local/mysql/data/performance_schema/replication_grou_156.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_conn_157.sdi to /usr/local/mysql/data/performance_schema/replication_conn_157.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_158.sdi to /usr/local/mysql/data/performance_schema/replication_appl_158.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_159.sdi to /usr/local/mysql/data/performance_schema/replication_appl_159.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_160.sdi to /usr/local/mysql/data/performance_schema/replication_appl_160.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_161.sdi to /usr/local/mysql/data/performance_schema/replication_appl_161.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_grou_162.sdi to /usr/local/mysql/data/performance_schema/replication_grou_162.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_163.sdi to /usr/local/mysql/data/performance_schema/replication_appl_163.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/replication_appl_164.sdi to /usr/local/mysql/data/performance_schema/replication_appl_164.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/log_status_165.sdi to /usr/local/mysql/data/performance_schema/log_status_165.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/prepared_stateme_166.sdi to /usr/local/mysql/data/performance_schema/prepared_stateme_166.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/user_variables_b_167.sdi to /usr/local/mysql/data/performance_schema/user_variables_b_167.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/status_by_accoun_168.sdi to /usr/local/mysql/data/performance_schema/status_by_accoun_168.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/status_by_host_169.sdi to /usr/local/mysql/data/performance_schema/status_by_host_169.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/status_by_thread_170.sdi to /usr/local/mysql/data/performance_schema/status_by_thread_170.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/status_by_user_171.sdi to /usr/local/mysql/data/performance_schema/status_by_user_171.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/global_status_172.sdi to /usr/local/mysql/data/performance_schema/global_status_172.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/session_status_173.sdi to /usr/local/mysql/data/performance_schema/session_status_173.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/variables_by_thr_174.sdi to /usr/local/mysql/data/performance_schema/variables_by_thr_174.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/global_variables_175.sdi to /usr/local/mysql/data/performance_schema/global_variables_175.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/session_variable_176.sdi to /usr/local/mysql/data/performance_schema/session_variable_176.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/variables_info_177.sdi to /usr/local/mysql/data/performance_schema/variables_info_177.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/persisted_variab_178.sdi to /usr/local/mysql/data/performance_schema/persisted_variab_178.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./performance_schema/user_defined_fun_179.sdi to /usr/local/mysql/data/performance_schema/user_defined_fun_179.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./test1/users_345.sdi to /usr/local/mysql/data/test1/users_345.sdi
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./test1/users.MYI to /usr/local/mysql/data/test1/users.MYI
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./test1/users.MYD to /usr/local/mysql/data/test1/users.MYD
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./xtrabackup_master_key_id to /usr/local/mysql/data/xtrabackup_master_key_id
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
221006 11:22:52 [01] ...done
221006 11:22:52 [01] Creating directory ./#innodb_temp
221006 11:22:52 [01] ...done.
221006 11:22:52 completed OK!
复制3.4.4 恢复：赋予权限
[root@mysql8 ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@mysql8 ~]# ll /usr/local/mysql/data/
total 167972
-rw-r----- 1 mysql mysql 5803 Oct 6 11:22 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Oct 6 11:22 ibdata1
-rw-r----- 1 mysql mysql 50331648 Oct 6 11:22 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Oct 6 11:22 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Oct 6 11:22 ibtmp1
drwxr-x--- 2 mysql mysql 143 Oct 6 11:22 mysql
-rw-r----- 1 mysql mysql 155 Oct 6 11:22 mysql8-bin.000004
-rw-r----- 1 mysql mysql 18 Oct 6 11:22 mysql8-bin.index
-rw-r----- 1 mysql mysql 25165824 Oct 6 11:22 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Oct 6 11:22 performance_schema
drwxr-x--- 2 mysql mysql 28 Oct 6 11:22 sys
drwxr-x--- 2 mysql mysql 61 Oct 6 11:22 test1
drwxr-x--- 2 mysql mysql 26 Oct 6 11:22 test2
-rw-r----- 1 mysql mysql 10485760 Oct 6 11:22 undo_001
-rw-r----- 1 mysql mysql 10485760 Oct 6 11:22 undo_002
-rw-r----- 1 mysql mysql 522 Oct 6 11:22 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Oct 6 11:22 xtrabackup_master_key_id
复制3.4.5 启动mysql
[root@mysql8 mysql]# systemctl start mysqld [root@mysql8 mysql]# mysql -uroot -proot123 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 10 Server version: 8.0.30 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 test1.users ; +----+-------+----------+---------+ | id | name | password | address | +----+-------+----------+---------+ | 1 | zhang | 1234 | NULL | | 2 | wang | 4321 | nanjing | | 3 | li | 5678 | suzhou | +----+-------+----------+---------+ 3 rows in set (0.00 sec) mysql> select * from test2.articles ; +----+---------+ | id | content | +----+---------+ | 11 | ninhao | | 12 | hello | | 13 | hi | +----+---------+ 3 rows in set (0.00 sec)复制
3.4.6 追加binlog数据
–将备份及其后面的binlog文件mysql8-bin.000004、mysql8-bin.000005、mysql8-bin.000006 拷贝到异机/backup/full目录下。
--1）根据之前查看的备份信息，备份结束点为mysql8-bin.000004 position=155，所以我们从该时间点应用后面所有的binlog
[root@mysql8 ~]# cd /backup/full/
[root@mysql8 full]# ll
total 16
drwxr-x--- 8 root root 4096 Oct 6 11:21 full_backup_2022-10-06
-rw-r----- 1 root root 874 Oct 6 11:24 mysql8-bin.000004
-rw-r----- 1 root root 836 Oct 6 11:24 mysql8-bin.000005
-rw-r----- 1 root root 523 Oct 6 11:24 mysql8-bin.000006
--2） 执行恢复操作之前的肉眼检查要恢复的数据是否正确
[root@mysql8 full]# mysqlbinlog --start-position=155 mysql8-bin.000004 -vv --base64-output=DECODE-ROWS |more
-- 3）应用binlog
[root@mysql8 full]# mysqlbinlog --start-position=155 mysql8-bin.000004 mysql8-bin.000005 mysql8-bin.000006 |mysql -uroot -proot123
mysql: [Warning] Using a password on the command line interface can be insecure.
复制3.4.7 验证数据
mysql> select * from test1.users ;
+----+------+----------+---------+
| id | name | password | address |
+----+------+----------+---------+
| 5 | shi | 321 | huaian |
| 2 | wang | 4321 | nanjing |
| 3 | li | 5678 | suzhou |
| 4 | yan | 123123 | shanxi |
+----+------+----------+---------+
4 rows in set (0.00 sec)
mysql> select * from test2.articles ;
+----+-------------+
| id | content |
+----+-------------+
| 11 | how are you |
| 12 | hello |
| 13 | hi |
| 14 | ninhao |
+----+-------------+
4 rows in set (0.00 sec)
复制最后修改时间：2022-10-09 12:29:44
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1131次阅读
2025-03-13 11:40:53
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
420次阅读
2025-03-04 21:56:13
MySQL8.0统计信息总结
闫建(Rock Yan)
403次阅读
2025-03-17 16:04:03
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
401次阅读
2025-03-13 14:38:19
SQL优化 - explain查看SQL执行计划（一）
金同学
354次阅读
2025-03-13 16:04:22
MySQL突然崩溃？教你用gdb解剖core文件，快速锁定“元凶”!
szrsu
307次阅读
2025-03-13 00:29:43
MySQL生产实战优化（利用Index skip scan优化性能提升257倍）
chengang
286次阅读
2025-03-17 10:36:40
利用 jemalloc 优化 MySQL 内存
JiekeXu
253次阅读
2025-02-24 22:36:07
[MYSQL] xtrabackup备份报错Unable to obtain lock分析
大大刺猬
226次阅读
2025-02-28 16:43:00
一键装库脚本3分钟极速部署，传统耗时砍掉95%！
IT邦德
205次阅读
2025-03-10 07:58:44
