第六周作业

1、作业内容(略)

2、作业解答

2.1 简述DDL,DML,DCL,DQL,并且说明mysql各个关键字查询时候的先后顺序

#mysql关键字书写顺序
SELECT * FROM student s LEFT JOIN teacher t ON s.id = t.student_id WHERE s.id IN (1,2,3) GROUP BY s.id HAVING s.age > 20  ORDER BY s.age ASC LIMIT 1,1;

#mysql关键字执行顺序
FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY -> LIMIT

2.2 自行设计10个sql查询语句,需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT],至少同时用到两个

#根据性别统计学生表中男女生数量,并按统计数量升序排序。
MySQL> select Gender,count(*) from students group by Gender order by 2 asc; 
+--------+----------+
| Gender | count(*) |
+--------+----------+
| F      |       10 |
| M      |       15 |
+--------+----------+
2 rows in set (0.01 sec)

#统计查询学生表中班级学生总人数等于3的班级
MySQL> select ClassID,count(*) from students group by ClassID HAVING count(*)=3;
+---------+----------+
| ClassID | count(*) |
+---------+----------+
|       2 |        3 |
|       7 |        3 |
+---------+----------+
2 rows in set (0.05 sec)

#统计查询学生表中年龄最大的前5名学生
MySQL> select Name,Age from students order by Age desc limit 5;
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Sun Dasheng  | 100 |
| Xie Yanke    |  53 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Ding Dian    |  32 |
+--------------+-----+
5 rows in set (0.00 sec)

#查询学生成绩表中得分最高的学生姓名和课程名称
MySQL> select a.name,b.Course,c.Score from students a, courses b, scores c where a.StuID=c.StuID and b.CourseID=c.CourseID order by c.Score desc limit 1;
+------------+---------------+-------+
| name       | Course        | Score |
+------------+---------------+-------+
| Shi Potian | Daiyu Zanghua |    97 |
+------------+---------------+-------+
1 row in set (0.00 sec)

#按课程名称统计查询每门课程的最高分和最低分
MySQL> select a.Course,max(b.Score),min(b.Score) from courses a,scores b where a.CourseID=b.CourseID group by a.Course;
+----------------+--------------+--------------+
| Course         | max(b.Score) | min(b.Score) |
+----------------+--------------+--------------+
| Kuihua Baodian |           89 |           47 |
| Weituo Zhang   |           93 |           75 |
| Daiyu Zanghua  |           97 |           71 |
| Hamo Gong      |           96 |           39 |
| Dagou Bangfa   |           83 |           63 |
| Taiji Quan     |           57 |           57 |
| Jinshe Jianfa  |           93 |           93 |
+----------------+--------------+--------------+
7 rows in set (0.00 sec)

#查询统计学生成绩总分在130至160分之间的学生姓名及总分
mysql> select a.name,sum(b.Score) from students a join scores b on a.StuID=b.StuID group by a.name having sum(b.Score) between 130 and 160 order by 2;
+------------+--------------+
| name       | sum(b.Score) |
+------------+--------------+
| Shi Potian |          144 |
| Lin Daiyu  |          150 |
| Ding Dian  |          160 |
+------------+--------------+
3 rows in set (0.00 sec)

#查询统计同时学习了课程2和课程6的学生姓名
mysql> select a.name from students a,scores b where a.StuID=b.StuID  and b.CourseID=2 and exists ( select * from scores c where c.StuID=b.StuID and c.CourseID=6 );
+-------------+
| name        |
+-------------+
| Shi Zhongyu |
| Xie Yanke   |
+-------------+
2 rows in set (0.00 sec)

#查询统计至少有一门课程与学号=4的同学所学课程相同的同学姓名
mysql> select distinct(a.name) from students a,scores b where a.StuID=b.StuID and b.CourseID in ( select CourseID from scores where StuID=4 ) and a.StuID <> 4;
+--------------+
| name         |
+--------------+
| Shi Zhongyu  |
| Shi Potian   |
| Xie Yanke    |
| Ren Yingying |
| Yue Lingshan |
+--------------+
5 rows in set (0.00 sec)

#查询统计有两门及以上课程不及格的学生学号和姓名
mysql> select a.StuID,a.name from students a,scores b where a.StuID=b.StuID and a.StuID in ( select StuID from scores where score < 60 group by StuID having count(1) >= 2 ) group by a.StuID,a.name;
+-------+-------------+
| StuID | name        |
+-------+-------------+
|     1 | Shi Zhongyu |
|     2 | Shi Potian  |
|     3 | Xie Yanke   |
|     5 | Yu Yutong   |
+-------+-------------+
4 rows in set (0.00 sec)

#查询统计每门课程最高分的学生信息和课程名称及最高分
mysql> select * from (
    -> select c.StuID,a.name,c.CourseID,b.Course,c.Score,row_number() over (partition by c.CourseID order by c.Score desc,a.name) ranks 
    -> from students a, courses b, scores c
    -> where a.StuID = c.StuID and b.CourseID=c.CourseID ) d where ranks=1;
+-------+--------------+----------+----------------+-------+-------+
| StuID | name         | CourseID | Course         | Score | ranks |
+-------+--------------+----------+----------------+-------+-------+
|     6 | Shi Qing     |        1 | Hamo Gong      |    96 |     1 |
|     4 | Ding Dian    |        2 | Kuihua Baodian |    89 |     1 |
|     8 | Lin Daiyu    |        3 | Jinshe Jianfa  |    93 |     1 |
|     7 | Xi Ren       |        4 | Taiji Quan     |    93 |     1 |
|     2 | Shi Potian   |        5 | Daiyu Zanghua  |    97 |     1 |
|     9 | Ren Yingying |        6 | Weituo Zhang   |    95 |     1 |
|     8 | Lin Daiyu    |        7 | Dagou Bangfa   |    96 |     1 |
+-------+--------------+----------+----------------+-------+-------+
7 rows in set (0.00 sec)

2.3 xtrabackup备份和还原数据库练习

2.3.1 xtrabackup完全备份及还原

#实验环境
mysql-db版本:mysql-8.0.30
xtrabackup版本:xtrabackup-80-8.0.30
mysqldb-1备份源主机:192.168.100.101
mysqldb-2恢复目标主机:192.168.100.102

#备份源主机安装xtrabackup工具
[root@mysqldb-1 ~]#yum install -y percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
Last metadata expiration check: 5:34:59 ago on Tue 03 Jan 2023 02:48:38 PM CST.
Dependencies resolved.
=============================================================================================================================================================
 Package                                 Architecture             Version                                               Repository                      Size
=============================================================================================================================================================
Installing:
 percona-xtrabackup-80                   x86_64                   8.0.30-23.1.el8                                       @commandline                    18 M
Installing dependencies:
 libev                                   x86_64                   4.24-6.el8                                            appstream                       51 k
 mariadb-connector-c                     x86_64                   3.1.11-2.el8_3                                        appstream                      199 k
 perl-DBD-MySQL                          x86_64                   4.046-3.module+el8.6.0+904+ef468285                   appstream                      155 k
 perl-DBI                                x86_64                   1.641-4.module+el8.6.0+891+677074cb                   appstream                      739 k
Enabling module streams:
 perl-DBD-MySQL                                                   4.046                                                                                     
 perl-DBI                                                         1.641                                                                                     

Transaction Summary
=============================================================================================================================================================
Install  5 Packages

Total size: 19 M
Total download size: 1.1 M
Installed size: 76 M
Downloading Packages:
(1/4): perl-DBD-MySQL-4.046-3.module+el8.6.0+904+ef468285.x86_64.rpm                                                         578 kB/s | 155 kB     00:00    
(2/4): libev-4.24-6.el8.x86_64.rpm                                                                                           121 kB/s |  51 kB     00:00    
(3/4): mariadb-connector-c-3.1.11-2.el8_3.x86_64.rpm                                                                         543 kB/s | 199 kB     00:00    
(4/4): perl-DBI-1.641-4.module+el8.6.0+891+677074cb.x86_64.rpm                                                               1.9 MB/s | 739 kB     00:00    
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        1.5 MB/s | 1.1 MB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                     1/1 
  Installing       : perl-DBI-1.641-4.module+el8.6.0+891+677074cb.x86_64                                                                                 1/5 
  Installing       : mariadb-connector-c-3.1.11-2.el8_3.x86_64                                                                                           2/5 
  Installing       : perl-DBD-MySQL-4.046-3.module+el8.6.0+904+ef468285.x86_64                                                                           3/5 
  Installing       : libev-4.24-6.el8.x86_64                                                                                                             4/5 
  Installing       : percona-xtrabackup-80-8.0.30-23.1.el8.x86_64                                                                                        5/5 
  Running scriptlet: percona-xtrabackup-80-8.0.30-23.1.el8.x86_64                                                                                        5/5 
  Verifying        : libev-4.24-6.el8.x86_64                                                                                                             1/5 
  Verifying        : mariadb-connector-c-3.1.11-2.el8_3.x86_64                                                                                           2/5 
  Verifying        : perl-DBD-MySQL-4.046-3.module+el8.6.0+904+ef468285.x86_64                                                                           3/5 
  Verifying        : perl-DBI-1.641-4.module+el8.6.0+891+677074cb.x86_64                                                                                 4/5 
  Verifying        : percona-xtrabackup-80-8.0.30-23.1.el8.x86_64                                                                                        5/5 

Installed:
  libev-4.24-6.el8.x86_64                                   mariadb-connector-c-3.1.11-2.el8_3.x86_64           percona-xtrabackup-80-8.0.30-23.1.el8.x86_64
  perl-DBD-MySQL-4.046-3.module+el8.6.0+904+ef468285.x86_64 perl-DBI-1.641-4.module+el8.6.0+891+677074cb.x86_64

Complete!

#mysqldb-1源主机创建数据库备份目录
[root@mysqldb-1 ~]#mkdir /backup

#mysqldb-1源主机执行完全备份
[root@mysqldb-1 ~]#xtrabackup -uroot --backup --target-dir=/backup/base
2023-01-03T20:44:08.203424+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T20:44:08.203578+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --backup=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
230103 20:44:09  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
230103 20:44:09  version_check Connected to MySQL server
230103 20:44:09  version_check Executing a version check against the server...

# A software update is available:
230103 20:44:11  version_check Done.
2023-01-03T20:44:11.448470+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T20:44:11.456705+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2023-01-03T20:44:11.551769+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-01-03T20:44:11.584670+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-01-03T20:44:11.584752+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-01-03T20:44:11.584765+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2023-01-03T20:44:11.584798+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-01-03T20:44:11.584819+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T20:44:11.584828+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T20:44:11.584848+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-01-03T20:44:11.584865+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-01-03T20:44:11.584904+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-01-03T20:44:11.585305+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T20:44:11.586983+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T20:44:13.511940+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T20:44:13.581462+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-01-03T20:44:13.618683+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19514733)
2023-01-03T20:44:13.856053+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T20:44:13.856152+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T20:44:13.856167+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T20:44:13.857352+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T20:44:13.857574+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T20:44:13.858033+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T20:44:13.858428+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T20:44:13.858946+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T20:44:13.940500+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /backup/base/ibdata1
2023-01-03T20:44:14.342979+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /backup/base/ibdata1
2023-01-03T20:44:14.362992+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /backup/base/sys/sys_config.ibd
2023-01-03T20:44:14.363440+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /backup/base/sys/sys_config.ibd
2023-01-03T20:44:14.377955+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/classes.ibd to /backup/base/hellodb/classes.ibd
2023-01-03T20:44:14.378167+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/classes.ibd to /backup/base/hellodb/classes.ibd
2023-01-03T20:44:14.389702+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/coc.ibd to /backup/base/hellodb/coc.ibd
2023-01-03T20:44:14.389905+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/coc.ibd to /backup/base/hellodb/coc.ibd
2023-01-03T20:44:14.447679+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/courses.ibd to /backup/base/hellodb/courses.ibd
2023-01-03T20:44:14.448002+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/courses.ibd to /backup/base/hellodb/courses.ibd
2023-01-03T20:44:14.448938+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/scores.ibd to /backup/base/hellodb/scores.ibd
2023-01-03T20:44:14.449131+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/scores.ibd to /backup/base/hellodb/scores.ibd
2023-01-03T20:44:14.450171+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/students.ibd to /backup/base/hellodb/students.ibd
2023-01-03T20:44:14.450346+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/students.ibd to /backup/base/hellodb/students.ibd
2023-01-03T20:44:14.450821+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/teachers.ibd to /backup/base/hellodb/teachers.ibd
2023-01-03T20:44:14.451008+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/teachers.ibd to /backup/base/hellodb/teachers.ibd
2023-01-03T20:44:14.451592+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/toc.ibd to /backup/base/hellodb/toc.ibd
2023-01-03T20:44:14.451786+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/toc.ibd to /backup/base/hellodb/toc.ibd
2023-01-03T20:44:14.462184+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /backup/base/mysql.ibd
2023-01-03T20:44:14.618984+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19514733)
2023-01-03T20:44:14.644562+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /backup/base/mysql.ibd
2023-01-03T20:44:14.645477+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_002 to /backup/base/undo_002
2023-01-03T20:44:14.842512+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_002 to /backup/base/undo_002
2023-01-03T20:44:14.843142+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_001 to /backup/base/undo_001
2023-01-03T20:44:14.914561+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /backup/base/undo_001
2023-01-03T20:44:15.619962+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19514733)
2023-01-03T20:44:15.860333+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files
2023-01-03T20:44:15.860836+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log_213.sdi to /backup/base/mysql/general_log_213.sdi
2023-01-03T20:44:15.879551+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log_213.sdi to /backup/base/mysql/general_log_213.sdi
2023-01-03T20:44:15.880729+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSM to /backup/base/mysql/general_log.CSM
2023-01-03T20:44:15.881189+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSM to /backup/base/mysql/general_log.CSM
2023-01-03T20:44:15.881542+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSV to /backup/base/mysql/general_log.CSV
2023-01-03T20:44:15.881575+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSV to /backup/base/mysql/general_log.CSV
2023-01-03T20:44:15.881754+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log_214.sdi to /backup/base/mysql/slow_log_214.sdi
2023-01-03T20:44:15.882054+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log_214.sdi to /backup/base/mysql/slow_log_214.sdi
2023-01-03T20:44:15.882388+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSM to /backup/base/mysql/slow_log.CSM
2023-01-03T20:44:15.882679+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSM to /backup/base/mysql/slow_log.CSM
2023-01-03T20:44:15.882935+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSV to /backup/base/mysql/slow_log.CSV
2023-01-03T20:44:15.883016+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSV to /backup/base/mysql/slow_log.CSV
2023-01-03T20:44:15.883281+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/cond_instances_82.sdi to /backup/base/performance_schema/cond_instances_82.sdi
2023-01-03T20:44:15.908021+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying 
...(中间日志省略)
performance_schema/keyring_componen_191.sdi to /backup/base/performance_schema/keyring_componen_191.sdi
2023-01-03T20:44:16.237385+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying performance_schema/keyring_componen_191.sdi to /backup/base/performance_schema/keyring_componen_191.sdi
2023-01-03T20:44:16.262807+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2023-01-03T20:44:16.262882+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2023-01-03T20:44:16.266260+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2023-01-03T20:44:16.270488+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /var/lib/mysql/binlog.000003 to /backup/base/binlog.000003 up to position 157
2023-01-03T20:44:16.270626+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /var/lib/mysql/binlog.000003 to /backup/base/binlog.000003
2023-01-03T20:44:16.271140+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/binlog.index
2023-01-03T20:44:16.271220+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/binlog.index
2023-01-03T20:44:16.274076+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/xtrabackup_binlog_info
2023-01-03T20:44:16.274303+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/xtrabackup_binlog_info
2023-01-03T20:44:16.275105+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2023-01-03T20:44:16.277043+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19514733'
2023-01-03T20:44:16.277095+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19514733
2023-01-03T20:44:16.277212+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19514391
2023-01-03T20:44:16.278119+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2023-01-03T20:44:16.278341+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-01-03T20:44:16.278431+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /backup/base/ib_buffer_pool
2023-01-03T20:44:16.278537+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /backup/base/ib_buffer_pool
2023-01-03T20:44:16.279004+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/backup/base/'
2023-01-03T20:44:16.279038+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000003', position '157'
2023-01-03T20:44:16.279116+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/backup-my.cnf
2023-01-03T20:44:16.279234+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/backup-my.cnf
2023-01-03T20:44:16.280086+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/xtrabackup_info
2023-01-03T20:44:16.280181+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/xtrabackup_info
2023-01-03T20:44:17.281458+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19514733) to (19514743) was copied.
2023-01-03T20:44:17.488318+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#将mysqldb-1源主机的备份目录拷贝到目标主机mysqldb-2
[root@mysqldb-1 ~]# scp -r /backup 192.168.100.102:/
root@192.168.100.102's password: 
xtrabackup_logfile                     100% 2560     1.7MB/s   00:00    
ibdata1                                100%   12MB  55.1MB/s   00:00    
sys_config.ibd                         100%  112KB   5.5MB/s   00:00    
classes.ibd                            100%  112KB   4.9MB/s   00:00    
coc.ibd                                100%  112KB  37.9MB/s   00:00    
courses.ibd                            100%  112KB  42.2MB/s   00:00    
...(中间日志省略) 
backup-my.cnf                          100%  447   408.2KB/s   00:00
xtrabackup_info                        100%  458   276.5KB/s   00:00
xtrabackup_tablespaces                 100%   39    23.1KB/s   00:00    

#mysqldb-2目标主机执行数据库还原恢复
#注意:还原恢复之前需要确认mysql服务已停止,并且数据目录(默认/var/lib/mysql)为空。
[root@mysqldb-2 /]#systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@mysqldb-2 /]#ls -l /var/lib/mysql
total 0

#mysqldb-2目标主机安装xtrabackup工具
[root@mysqldb-2 ~]#yum install -y percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm

#mysqldb-2目标主机恢复预准备操作
[root@mysqldb-2 /]#xtrabackup --prepare --target-dir=/backup/base
2023-01-03T21:13:51.946429+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2023-01-03T21:13:51.946543+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T21:13:51.946563+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T21:13:51.946606+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2023-01-03T21:13:51.946977+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T21:13:51.951843+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19514733)
2023-01-03T21:13:51.952406+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T21:13:51.952439+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T21:13:51.952445+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T21:13:51.952471+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2023-01-03T21:13:51.952477+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T21:13:51.952561+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T21:13:51.952828+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T21:13:51.953139+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T21:13:51.953207+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T21:13:51.953212+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T21:13:51.953222+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2023-01-03T21:13:51.953226+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T21:13:51.953255+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T21:13:51.953265+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2023-01-03T21:13:51.953271+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2023-01-03T21:13:51.953313+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2023-01-03T21:13:51.953344+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2023-01-03T21:13:51.953351+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2023-01-03T21:13:51.953356+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.12
2023-01-03T21:13:51.953589+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T21:13:51.953716+08:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2023-01-03T21:13:51.954209+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2023-01-03T21:13:51.954275+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T21:13:51.954766+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 11 files.
2023-01-03T21:13:51.955278+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
2023-01-03T21:13:51.961243+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2023-01-03T21:13:51.964776+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2023-01-03T21:13:51.965287+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2023-01-03T21:13:51.966259+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2023-01-03T21:13:51.966285+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2023-01-03T21:13:52.026312+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 19514733 in redo log file ./#innodb_redo/#ib_redo0.
2023-01-03T21:13:52.026422+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 18869608 in the system tablespace does not match the log sequence number 19514733 in the redo log files!
2023-01-03T21:13:52.026465+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2023-01-03T21:13:52.026492+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2023-01-03T21:13:52.026901+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 19514391, whereas checkpoint_lsn = 19514733 and start_lsn = 19514368
2023-01-03T21:13:52.026944+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 19514733
2023-01-03T21:13:52.036685+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2023-01-03T21:13:52.037476+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2023-01-03T21:13:52.037530+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2023-01-03T21:13:52.138727+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2023-01-03T21:13:52.141674+08:00 0 [Note] [MY-013041] [InnoDB] Resizing redo log from 8M to 1024M (LSN=19514733) synchronously. If this takes too long, consider starting the server with large --innodb_redo_log_capacity, and resizing the redo log online using SET.
2023-01-03T21:13:52.143613+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2023-01-03T21:13:52.143715+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2023-01-03T21:13:52.205996+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes
2023-01-03T21:13:52.207747+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2023-01-03T21:13:52.208356+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=19514892
2023-01-03T21:13:52.208425+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2023-01-03T21:13:52.208901+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T21:13:52.209165+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T21:13:52.209674+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T21:13:52.209753+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1904
2023-01-03T21:13:52.215543+08:00 0 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
2023-01-03T21:13:52.215591+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 4 thread: 5847 ms.
2023-01-03T21:13:52.215715+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2023-01-03T21:13:52.215807+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-01-03T21:13:52.233759+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2023-01-03T21:13:52.234045+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2023-01-03T21:13:52.275423+08:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2023-01-03T21:13:52.275738+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.30 started; log sequence number 19514902
2023-01-03T21:13:52.275980+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2023-01-03T21:13:52.293833+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2023-01-03T21:13:52.293901+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2023-01-03T21:13:53.293912+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2023-01-03T21:13:53.299225+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2023-01-03T21:13:53.310762+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19514902
2023-01-03T21:13:53.312704+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T21:13:53.313760+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2目标主机复制备份数据到数据目录
[root@mysqldb-2 /]#xtrabackup --copy-back --target-dir=/backup/base
2023-01-03T21:15:33.942179+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T21:15:33.942321+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --copy-back=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T21:15:33.942375+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T21:15:33.942929+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /var/lib/mysql/undo_001
2023-01-03T21:15:34.058255+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /var/lib/mysql/undo_001
2023-01-03T21:15:34.341248+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /var/lib/mysql/undo_002
2023-01-03T21:15:34.359088+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /var/lib/mysql/undo_002
2023-01-03T21:15:34.376199+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /var/lib/mysql/ibdata1
2023-01-03T21:15:34.391719+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ibdata1 to /var/lib/mysql/ibdata1
2023-01-03T21:15:34.407561+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.000003 to /var/lib/mysql/binlog.000003
2023-01-03T21:15:34.407706+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.000003 to /var/lib/mysql/binlog.000003
2023-01-03T21:15:34.408457+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.index to /var/lib/mysql/binlog.index
2023-01-03T21:15:34.408636+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.index to /var/lib/mysql/binlog.index
2023-01-03T21:15:34.409673+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /var/lib/mysql/sys/sys_config.ibd
2023-01-03T21:15:34.409899+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /var/lib/mysql/sys/sys_config.ibd
2023-01-03T21:15:34.410782+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/classes.ibd to /var/lib/mysql/hellodb/classes.ibd
2023-01-03T21:15:34.411167+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/classes.ibd to /var/lib/mysql/hellodb/classes.ibd
2023-01-03T21:15:34.430238+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/coc.ibd to /var/lib/mysql/hellodb/coc.ibd
2023-01-03T21:15:34.430517+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/coc.ibd to /var/lib/mysql/hellodb/coc.ibd
2023-01-03T21:15:34.431481+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/courses.ibd to /var/lib/mysql/hellodb/courses.ibd
2023-01-03T21:15:34.431769+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/courses.ibd to /var/lib/mysql/hellodb/courses.ibd
2023-01-03T21:15:34.433049+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/scores.ibd to /var/lib/mysql/hellodb/scores.ibd
2023-01-03T21:15:34.433415+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/scores.ibd to /var/lib/mysql/hellodb/scores.ibd
2023-01-03T21:15:34.434560+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/students.ibd to /var/lib/mysql/hellodb/students.ibd
2023-01-03T21:15:34.434786+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/students.ibd to /var/lib/mysql/hellodb/students.ibd
2023-01-03T21:15:34.436042+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/teachers.ibd to /var/lib/mysql/hellodb/teachers.ibd
2023-01-03T21:15:34.436365+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/teachers.ibd to /var/lib/mysql/hellodb/teachers.ibd
2023-01-03T21:15:34.437341+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/toc.ibd to /var/lib/mysql/hellodb/toc.ibd
2023-01-03T21:15:34.437591+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/toc.ibd to /var/lib/mysql/hellodb/toc.ibd
2023-01-03T21:15:34.438591+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /var/lib/mysql/mysql.ibd
2023-01-03T21:15:34.472559+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /var/lib/mysql/mysql.ibd
...(中间日志省略)
2023-01-03T21:15:34.608045+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
2023-01-03T21:15:34.608249+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
2023-01-03T21:15:34.608933+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
2023-01-03T21:15:34.609178+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
2023-01-03T21:15:34.609661+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2023-01-03T21:15:34.609731+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2023-01-03T21:15:34.609830+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2023-01-03T21:15:34.621939+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2023-01-03T21:15:34.712566+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2目标主机查看数据目录
[root@mysqldb-2 /]# ls -l /var/lib/mysql
total 81952
-rw-r----- 1 root root      157 Jan  3 21:15  binlog.000003
-rw-r----- 1 root root       14 Jan  3 21:15  binlog.index
drwxr-x--- 2 root root      132 Jan  3 21:15  hellodb
-rw-r----- 1 root root     5958 Jan  3 21:15  ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan  3 21:15  ibdata1
-rw-r----- 1 root root 12582912 Jan  3 21:15  ibtmp1
drwxr-x--- 2 root root        6 Jan  3 21:15 '#innodb_redo'
drwxr-x--- 2 root root      143 Jan  3 21:15  mysql
-rw-r----- 1 root root 25165824 Jan  3 21:15  mysql.ibd
drwxr-x--- 2 root root     8192 Jan  3 21:15  performance_schema
drwxr-x--- 2 root root       28 Jan  3 21:15  sys
-rw-r----- 1 root root 16777216 Jan  3 21:15  undo_001
-rw-r----- 1 root root 16777216 Jan  3 21:15  undo_002
-rw-r----- 1 root root      458 Jan  3 21:15  xtrabackup_info

#mysqldb-2目标主机还原修改数据目录属性
[root@mysqldb-2 /]#chown -R mysql.mysql /var/lib/mysql
[root@mysqldb-2 /]#ls -l /var/lib/mysql              
total 81952
-rw-r----- 1 mysql mysql      157 Jan  3 21:15  binlog.000003
-rw-r----- 1 mysql mysql       14 Jan  3 21:15  binlog.index
drwxr-x--- 2 mysql mysql      132 Jan  3 21:15  hellodb
-rw-r----- 1 mysql mysql     5958 Jan  3 21:15  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan  3 21:15  ibdata1
-rw-r----- 1 mysql mysql 12582912 Jan  3 21:15  ibtmp1
drwxr-x--- 2 mysql mysql        6 Jan  3 21:15 '#innodb_redo'
drwxr-x--- 2 mysql mysql      143 Jan  3 21:15  mysql
-rw-r----- 1 mysql mysql 25165824 Jan  3 21:15  mysql.ibd
drwxr-x--- 2 mysql mysql     8192 Jan  3 21:15  performance_schema
drwxr-x--- 2 mysql mysql       28 Jan  3 21:15  sys
-rw-r----- 1 mysql mysql 16777216 Jan  3 21:15  undo_001
-rw-r----- 1 mysql mysql 16777216 Jan  3 21:15  undo_002
-rw-r----- 1 mysql mysql      458 Jan  3 21:15  xtrabackup_info

#mysqldb-2目标主机启动数据库和查看备份数据是否已还原恢复
[root@mysqldb-2 /]#systemctl start mysqld
[root@mysqldb-2 /]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 Source distribution

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           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)

2.3.2 xtrabackup完全、增量备份及还原

#实验环境
mysql-db版本:mysql-8.0.30
xtrabackup版本:xtrabackup-80-8.0.30
mysqldb-1备份源主机:192.168.100.101
mysqldb-2恢复目标主机:192.168.100.102

#备份源主机和恢复目标主机安装xtrabackup工具
[root@mysqldb-1 ~]#yum install -y percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
[root@mysqldb-2 ~]#yum install -y percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm

#mysqldb-1源主机执行完全备份
[root@mysqldb-1 ~]#mkdir /backup
[root@mysqldb-1 /]#xtrabackup -uroot --backup --target-dir=/backup/base
2023-01-03T22:06:06.049568+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T22:06:06.049697+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --backup=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
230103 22:06:06  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
230103 22:06:06  version_check Connected to MySQL server
230103 22:06:06  version_check Executing a version check against the server...
230103 22:06:06  version_check Done.
2023-01-03T22:06:06.133708+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:06:06.139789+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2023-01-03T22:06:06.142550+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-01-03T22:06:06.144125+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-01-03T22:06:06.144188+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-01-03T22:06:06.144208+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2023-01-03T22:06:06.144245+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-01-03T22:06:06.144268+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:06:06.144275+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:06:06.144302+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-01-03T22:06:06.144350+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-01-03T22:06:06.144366+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-01-03T22:06:06.144814+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:06:06.145763+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:06:06.236150+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:06:06.240735+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-01-03T22:06:06.337063+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19514753)
2023-01-03T22:06:06.541587+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:06:06.541660+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:06:06.541675+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:06:06.543017+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T22:06:06.544618+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T22:06:06.547152+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T22:06:06.547627+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T22:06:06.548644+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T22:06:06.549788+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /backup/base/ibdata1
2023-01-03T22:06:06.736973+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /backup/base/ibdata1
...(中间日志省略)
2023-01-03T22:06:08.858241+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2023-01-03T22:06:08.858309+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2023-01-03T22:06:08.862702+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2023-01-03T22:06:08.864957+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /var/lib/mysql/binlog.000004 to /backup/base/binlog.000004 up to position 157
2023-01-03T22:06:08.865128+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /var/lib/mysql/binlog.000004 to /backup/base/binlog.000004
2023-01-03T22:06:08.866490+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/binlog.index
2023-01-03T22:06:08.866650+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/binlog.index
2023-01-03T22:06:08.869068+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/xtrabackup_binlog_info
2023-01-03T22:06:08.869222+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/xtrabackup_binlog_info
2023-01-03T22:06:08.869852+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2023-01-03T22:06:08.872138+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19514753'
2023-01-03T22:06:08.872179+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19514753
2023-01-03T22:06:08.872281+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19514391
2023-01-03T22:06:08.873357+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2023-01-03T22:06:08.873485+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-01-03T22:06:08.873561+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /backup/base/ib_buffer_pool
2023-01-03T22:06:08.873847+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /backup/base/ib_buffer_pool
2023-01-03T22:06:08.874203+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/backup/base/'
2023-01-03T22:06:08.874231+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000004', position '157'
2023-01-03T22:06:08.874306+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/backup-my.cnf
2023-01-03T22:06:08.874426+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/backup-my.cnf
2023-01-03T22:06:08.875111+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/base/xtrabackup_info
2023-01-03T22:06:08.875291+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/base/xtrabackup_info
2023-01-03T22:06:09.877112+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19514753) to (19514763) was copied.
2023-01-03T22:06:10.084774+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-1源数据库第一次修改数据
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

mysql> insert into teachers values(5,'Wang Fei',22,'M');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Wang Fei      |  22 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

#mysqldb-1源数据库执行第一次增量备份
[root@mysqldb-1 /]#xtrabackup -uroot --backup --target-dir=/backup/incre1 --incremental-basedir=/backup/base
2023-01-03T22:11:46.486531+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T22:11:46.486805+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --backup=1 --target-dir=/backup/incre1 --incremental-basedir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
230103 22:11:46  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
230103 22:11:46  version_check Connected to MySQL server
230103 22:11:46  version_check Executing a version check against the server...
230103 22:11:46  version_check Done.
2023-01-03T22:11:46.572129+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:11:46.577873+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2023-01-03T22:11:46.580861+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-01-03T22:11:46.581756+08:00 0 [Note] [MY-011825] [Xtrabackup] incremental backup from 19514753 is enabled.
2023-01-03T22:11:46.582749+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-01-03T22:11:46.582809+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-01-03T22:11:46.582821+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2023-01-03T22:11:46.582854+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-01-03T22:11:46.582876+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:11:46.582882+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:11:46.582905+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-01-03T22:11:46.582923+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-01-03T22:11:46.582932+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-01-03T22:11:46.583428+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:11:46.584559+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:11:46.670568+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:11:46.771844+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19515155)
2023-01-03T22:11:46.879705+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-01-03T22:11:47.080340+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:11:47.080437+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:11:47.080468+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:11:47.082102+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T22:11:47.083921+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T22:11:47.086350+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T22:11:47.086758+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T22:11:47.087883+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T22:11:47.088083+08:00 0 [Note] [MY-011825] [Xtrabackup] using the full scan for incremental backup
2023-01-03T22:11:47.772070+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19515155)
2023-01-03T22:11:48.772363+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19515155)
2023-01-03T22:11:49.253052+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /backup/incre1/ibdata1.delta
2023-01-03T22:11:49.461223+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /backup/incre1/ibdata1.delta
...(中间日志省略)
2023-01-03T22:11:50.291604+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2023-01-03T22:11:50.291658+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2023-01-03T22:11:50.296498+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2023-01-03T22:11:50.298873+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /var/lib/mysql/binlog.000005 to /backup/incre1/binlog.000005 up to position 157
2023-01-03T22:11:50.299055+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /var/lib/mysql/binlog.000005 to /backup/incre1/binlog.000005
2023-01-03T22:11:50.299739+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre1/binlog.index
2023-01-03T22:11:50.299843+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre1/binlog.index
2023-01-03T22:11:50.302614+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre1/xtrabackup_binlog_info
2023-01-03T22:11:50.302746+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre1/xtrabackup_binlog_info
2023-01-03T22:11:50.303697+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2023-01-03T22:11:50.306438+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19515155'
2023-01-03T22:11:50.306487+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19515155
2023-01-03T22:11:50.306617+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19514907
2023-01-03T22:11:50.307465+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2023-01-03T22:11:50.307742+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-01-03T22:11:50.307807+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /backup/incre1/ib_buffer_pool
2023-01-03T22:11:50.308035+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /backup/incre1/ib_buffer_pool
2023-01-03T22:11:50.308401+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/backup/incre1/'
2023-01-03T22:11:50.308429+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000005', position '157'
2023-01-03T22:11:50.308487+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre1/backup-my.cnf
2023-01-03T22:11:50.308570+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre1/backup-my.cnf
2023-01-03T22:11:50.309334+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre1/xtrabackup_info
2023-01-03T22:11:50.309421+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre1/xtrabackup_info
2023-01-03T22:11:51.310332+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19515155) to (19515165) was copied.
2023-01-03T22:11:51.518880+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-1源数据库第二次修改数据
mysql> insert into teachers values(6,'Yao xiaolu',23,'F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Wang Fei      |  22 | M      |
|   6 | Yao xiaolu    |  23 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

#mysqldb-1源数据库执行第二次增量备份
[root@mysqldb-1 /]#xtrabackup -uroot --backup --target-dir=/backup/incre2 --incremental-basedir=/backup/incre1
2023-01-03T22:14:41.544019+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T22:14:41.544194+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --backup=1 --target-dir=/backup/incre2 --incremental-basedir=/backup/incre1 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
230103 22:14:41  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
230103 22:14:41  version_check Connected to MySQL server
230103 22:14:41  version_check Executing a version check against the server...
230103 22:14:41  version_check Done.
2023-01-03T22:14:41.629791+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:14:41.635259+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2023-01-03T22:14:41.638279+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-01-03T22:14:41.639275+08:00 0 [Note] [MY-011825] [Xtrabackup] incremental backup from 19515155 is enabled.
2023-01-03T22:14:41.640357+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-01-03T22:14:41.640425+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-01-03T22:14:41.640438+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024
2023-01-03T22:14:41.640482+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-01-03T22:14:41.640507+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:14:41.640514+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:14:41.640540+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-01-03T22:14:41.640561+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-01-03T22:14:41.640569+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-01-03T22:14:41.640989+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:14:41.642968+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:14:41.786748+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
2023-01-03T22:14:41.791464+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-01-03T22:14:41.888095+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19518449)
2023-01-03T22:14:42.092208+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:14:42.092289+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:14:42.092299+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:14:42.092952+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T22:14:42.094461+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T22:14:42.096447+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T22:14:42.096848+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T22:14:42.097672+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T22:14:42.097714+08:00 0 [Note] [MY-011825] [Xtrabackup] using the full scan for incremental backup
2023-01-03T22:14:42.113482+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /backup/incre2/ibdata1.delta
2023-01-03T22:14:42.420382+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /backup/incre2/ibdata1.delta
...(中间日志省略)
2023-01-03T22:14:43.399957+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2023-01-03T22:14:43.400078+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2023-01-03T22:14:43.404079+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2023-01-03T22:14:43.407024+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /var/lib/mysql/binlog.000006 to /backup/incre2/binlog.000006 up to position 157
2023-01-03T22:14:43.407395+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /var/lib/mysql/binlog.000006 to /backup/incre2/binlog.000006
2023-01-03T22:14:43.409040+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre2/binlog.index
2023-01-03T22:14:43.409191+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre2/binlog.index
2023-01-03T22:14:43.411379+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre2/xtrabackup_binlog_info
2023-01-03T22:14:43.411498+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre2/xtrabackup_binlog_info
2023-01-03T22:14:43.412100+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2023-01-03T22:14:43.414758+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19518449'
2023-01-03T22:14:43.414799+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19518449
2023-01-03T22:14:43.414976+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19517988
2023-01-03T22:14:43.415920+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2023-01-03T22:14:43.416225+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-01-03T22:14:43.416308+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /backup/incre2/ib_buffer_pool
2023-01-03T22:14:43.416561+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /backup/incre2/ib_buffer_pool
2023-01-03T22:14:43.434488+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/backup/incre2/'
2023-01-03T22:14:43.434534+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000006', position '157'
2023-01-03T22:14:43.434610+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre2/backup-my.cnf
2023-01-03T22:14:43.434679+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre2/backup-my.cnf
2023-01-03T22:14:43.435666+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /backup/incre2/xtrabackup_info
2023-01-03T22:14:43.435784+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /backup/incre2/xtrabackup_info
2023-01-03T22:14:44.458229+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19518449) to (19518459) was copied.
2023-01-03T22:14:44.664583+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#将mysqldb-1源主机的备份目录拷贝到目标主机mysqldb-2
[root@mysqldb-1 ~]#ls -l /backup/
total 12
drwxr-x--- 6 root root 4096 Jan  3 22:06 base
drwxr-x--- 6 root root 4096 Jan  3 22:11 incre1
drwxr-x--- 6 root root 4096 Jan  3 22:14 incre2

[root@mysqldb-1 ~]#scp -r /backup 192.168.100.102:/
root@192.168.100.102's password: 
xtrabackup_logfile                                    100% 2560     7.0KB/s   00:00    
ibdata1                                               100%   12MB 111.1MB/s   00:00    
sys_config.ibd                                        100%  112KB   1.7MB/s   00:00    
classes.ibd                                           100%  112KB  58.0MB/s   00:00    
coc.ibd                                               100%  112KB  37.4MB/s   00:00 
...(中间日志省略)
backup-my.cnf                                         100%  447   525.4KB/s   00:00    
xtrabackup_info                                       100%  504   681.2KB/s   00:00    
xtrabackup_tablespaces                                100%   39    51.2KB/s   00:00  

#mysqldb-2目标数据库执行数据库还原恢复
#注意:还原恢复之前需要确认mysql服务已停止,并且数据目录(默认/var/lib/mysql)为空。
[root@mysqldb-2 /]#systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@mysqldb-2 /]#ls -l /var/lib/mysql
total 0

#mysqldb-2目标数据库执行预准备操作
[root@mysqldb-2 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2023-01-03T22:23:51.678564+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2023-01-03T22:23:51.678679+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T22:23:51.678717+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T22:23:51.678763+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2023-01-03T22:23:51.679024+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:23:51.682906+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19514753)
2023-01-03T22:23:51.683374+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T22:23:51.683406+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:23:51.683412+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:23:51.683468+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2023-01-03T22:23:51.683508+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T22:23:51.683563+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T22:23:51.683768+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:23:51.683935+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T22:23:51.683967+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:23:51.683972+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:23:51.683984+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2023-01-03T22:23:51.683990+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T22:23:51.683997+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T22:23:51.684005+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2023-01-03T22:23:51.684012+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2023-01-03T22:23:51.684038+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2023-01-03T22:23:51.684070+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2023-01-03T22:23:51.684077+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2023-01-03T22:23:51.684085+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.12
2023-01-03T22:23:51.684196+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:23:51.684273+08:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2023-01-03T22:23:51.684592+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './'
2023-01-03T22:23:51.684672+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:23:51.685202+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 11 files.
2023-01-03T22:23:51.686027+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
2023-01-03T22:23:51.693250+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
...(中间日志省略)
2023-01-03T22:23:52.145240+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.30 started; log sequence number 19514763
2023-01-03T22:23:52.145574+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2023-01-03T22:23:52.164441+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2023-01-03T22:23:52.164507+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2023-01-03T22:23:53.164185+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2023-01-03T22:23:53.169326+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2023-01-03T22:23:53.181977+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19514763
2023-01-03T22:23:53.183158+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:23:53.184233+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2合并第一次增量备份到完全备份
[root@mysqldb-2 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/incre1
2023-01-03T22:26:36.742385+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2023-01-03T22:26:36.742484+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup/base --incremental-dir=/backup/incre1 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T22:26:36.742555+08:00 0 [Note] [MY-011825] [Xtrabackup] incremental backup from 19514753 is enabled.
2023-01-03T22:26:36.742596+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T22:26:36.742637+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be already prepared with --apply-log-only.
2023-01-03T22:26:36.742971+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:26:36.747494+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19515155)
2023-01-03T22:26:36.748016+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T22:26:36.748061+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:26:36.748074+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:26:36.748134+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = /backup/incre1/
2023-01-03T22:26:36.748161+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T22:26:36.748170+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T22:26:36.748420+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:26:36.950178+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:26:36.950241+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:26:36.950250+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:26:36.950863+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T22:26:36.951123+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T22:26:36.951434+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T22:26:36.951983+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T22:26:36.952659+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T22:26:36.952775+08:00 0 [Note] [MY-011825] [Xtrabackup] page size for /backup/incre1//ibdata1.delta is 16384 bytes
2023-01-03T22:26:36.953998+08:00 0 [Note] [MY-011825] [Xtrabackup] Applying /backup/incre1//ibdata1.delta to ./ibdata1
2023-01-03T22:26:36.955082+08:00 0 [Note] [MY-011825] [Xtrabackup] page size for /backup/incre1//sys/sys_config.ibd.delta is 16384 bytes
...(中间日志省略)
2023-01-03T22:26:39.021234+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
2023-01-03T22:26:39.021312+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
2023-01-03T22:26:39.021820+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre1//xtrabackup_info to ./xtrabackup_info
2023-01-03T22:26:39.021908+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre1//xtrabackup_info to ./xtrabackup_info
2023-01-03T22:26:39.022460+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre1//xtrabackup_tablespaces to ./xtrabackup_tablespaces
2023-01-03T22:26:39.022571+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre1//xtrabackup_tablespaces to ./xtrabackup_tablespaces
2023-01-03T22:26:39.023090+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre1/binlog.000005 to ./binlog.000005
2023-01-03T22:26:39.023186+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre1/binlog.000005 to ./binlog.000005
2023-01-03T22:26:39.023542+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre1/binlog.index to ./binlog.index
2023-01-03T22:26:39.023638+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre1/binlog.index to ./binlog.index
2023-01-03T22:26:39.024270+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2合并第二次增量备份到完全备份(最后一次合并不需要增加--apply-log-only选项)
[root@mysqldb-2 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/incre2
2023-01-03T22:29:38.066234+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2023-01-03T22:29:38.066330+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/backup/base --incremental-dir=/backup/incre2 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T22:29:38.066385+08:00 0 [Note] [MY-011825] [Xtrabackup] incremental backup from 19515155 is enabled.
2023-01-03T22:29:38.066415+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T22:29:38.066460+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be already prepared with --apply-log-only.
2023-01-03T22:29:38.066819+08:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2023-01-03T22:29:38.072804+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19518449)
2023-01-03T22:29:38.073517+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2023-01-03T22:29:38.073576+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-01-03T22:29:38.073627+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-01-03T22:29:38.073679+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = /backup/incre2/
2023-01-03T22:29:38.073713+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
2023-01-03T22:29:38.073755+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608
2023-01-03T22:29:38.074062+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-01-03T22:29:38.275546+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:29:38.275644+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-01-03T22:29:38.275659+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-01-03T22:29:38.276024+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-01-03T22:29:38.276158+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
2023-01-03T22:29:38.276459+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2023-01-03T22:29:38.276746+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2023-01-03T22:29:38.277337+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2023-01-03T22:29:38.277420+08:00 0 [Note] [MY-011825] [Xtrabackup] page size for /backup/incre2//ibdata1.delta is 16384 bytes
2023-01-03T22:29:38.277511+08:00 0 [Note] [MY-011825] [Xtrabackup] Applying /backup/incre2//ibdata1.delta to ./ibdata1
2023-01-03T22:29:38.278238+08:00 0 [Note] [MY-011825] [Xtrabackup] page size for /backup/incre2//sys/sys_config.ibd.delta is 16384 bytes
...(中间日志省略)
2023-01-03T22:29:40.048886+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre2//xtrabackup_info to ./xtrabackup_info
2023-01-03T22:29:40.049001+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre2//xtrabackup_info to ./xtrabackup_info
2023-01-03T22:29:40.049402+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre2//xtrabackup_tablespaces to ./xtrabackup_tablespaces
2023-01-03T22:29:40.049483+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre2//xtrabackup_tablespaces to ./xtrabackup_tablespaces
2023-01-03T22:29:40.050056+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre2/binlog.000006 to ./binlog.000006
2023-01-03T22:29:40.050168+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre2/binlog.000006 to ./binlog.000006
2023-01-03T22:29:40.050601+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /backup/incre2/binlog.index to ./binlog.index
2023-01-03T22:29:40.050721+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /backup/incre2/binlog.index to ./binlog.index
2023-01-03T22:29:40.051530+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2目标主机复制备份数据到数据目录
total 0
[root@mysqldb-2 ~]#xtrabackup --copy-back --target-dir=/backup/base
2023-01-03T22:32:50.043694+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2023-01-03T22:32:50.043893+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --copy-back=1 --target-dir=/backup/base 
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2023-01-03T22:32:50.043955+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /backup/base/
2023-01-03T22:32:50.044838+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /var/lib/mysql/undo_001
2023-01-03T22:32:50.277522+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /var/lib/mysql/undo_001
2023-01-03T22:32:50.473838+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /var/lib/mysql/undo_002
2023-01-03T22:32:50.642230+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /var/lib/mysql/undo_002
2023-01-03T22:32:50.688584+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /var/lib/mysql/ibdata1
2023-01-03T22:32:50.778785+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ibdata1 to /var/lib/mysql/ibdata1
2023-01-03T22:32:50.798237+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.000006 to /var/lib/mysql/binlog.000006
2023-01-03T22:32:50.798394+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.000006 to /var/lib/mysql/binlog.000006
2023-01-03T22:32:50.799046+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying binlog.index to /var/lib/mysql/binlog.index
2023-01-03T22:32:50.799189+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying binlog.index to /var/lib/mysql/binlog.index
2023-01-03T22:32:50.800269+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /var/lib/mysql/sys/sys_config.ibd
2023-01-03T22:32:50.800676+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /var/lib/mysql/sys/sys_config.ibd
2023-01-03T22:32:50.818438+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/classes.ibd to /var/lib/mysql/hellodb/classes.ibd
2023-01-03T22:32:50.818869+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/classes.ibd to /var/lib/mysql/hellodb/classes.ibd
2023-01-03T22:32:50.819568+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/coc.ibd to /var/lib/mysql/hellodb/coc.ibd
2023-01-03T22:32:50.819891+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/coc.ibd to /var/lib/mysql/hellodb/coc.ibd
2023-01-03T22:32:50.820624+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/courses.ibd to /var/lib/mysql/hellodb/courses.ibd
2023-01-03T22:32:50.820878+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/courses.ibd to /var/lib/mysql/hellodb/courses.ibd
2023-01-03T22:32:50.821885+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./hellodb/scores.ibd to /var/lib/mysql/hellodb/scores.ibd
2023-01-03T22:32:50.822127+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./hellodb/scores.ibd to /var/lib/mysql/hellodb/scores.ibd
...(中间日志省略)
2023-01-03T22:32:51.067654+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
2023-01-03T22:32:51.067756+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
2023-01-03T22:32:51.068063+08:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2023-01-03T22:32:51.068094+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2023-01-03T22:32:51.068168+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2023-01-03T22:32:51.077500+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
2023-01-03T22:32:51.123541+08:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
2023-01-03T22:32:51.123729+08:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
2023-01-03T22:32:51.203235+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

#mysqldb-2目标主机查看数据目录
[root@mysqldb-2 ~]#ls -l /var/lib/mysql                            
total 81952
-rw-r----- 1 root root      157 Jan  3 22:32  binlog.000006
-rw-r----- 1 root root       14 Jan  3 22:32  binlog.index
drwxr-x--- 2 root root      132 Jan  3 22:32  hellodb
-rw-r----- 1 root root     5958 Jan  3 22:32  ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan  3 22:32  ibdata1
-rw-r----- 1 root root 12582912 Jan  3 22:32  ibtmp1
drwxr-x--- 2 root root        6 Jan  3 22:32 '#innodb_redo'
drwxr-x--- 2 root root      143 Jan  3 22:32  mysql
-rw-r----- 1 root root 25165824 Jan  3 22:32  mysql.ibd
drwxr-x--- 2 root root     8192 Jan  3 22:32  performance_schema
drwxr-x--- 2 root root       28 Jan  3 22:32  sys
-rw-r----- 1 root root 16777216 Jan  3 22:32  undo_001
-rw-r----- 1 root root 16777216 Jan  3 22:32  undo_002
-rw-r----- 1 root root      504 Jan  3 22:32  xtrabackup_info

#mysqldb-2目标主机还原修改数据目录属性
[root@mysqldb-2 ~]#chown -R mysql.mysql /var/lib/mysql
[root@mysqldb-2 ~]#ls -l /var/lib/mysql               
total 81952
-rw-r----- 1 mysql mysql      157 Jan  3 22:32  binlog.000006
-rw-r----- 1 mysql mysql       14 Jan  3 22:32  binlog.index
drwxr-x--- 2 mysql mysql      132 Jan  3 22:32  hellodb
-rw-r----- 1 mysql mysql     5958 Jan  3 22:32  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan  3 22:32  ibdata1
-rw-r----- 1 mysql mysql 12582912 Jan  3 22:32  ibtmp1
drwxr-x--- 2 mysql mysql        6 Jan  3 22:32 '#innodb_redo'
drwxr-x--- 2 mysql mysql      143 Jan  3 22:32  mysql
-rw-r----- 1 mysql mysql 25165824 Jan  3 22:32  mysql.ibd
drwxr-x--- 2 mysql mysql     8192 Jan  3 22:32  performance_schema
drwxr-x--- 2 mysql mysql       28 Jan  3 22:32  sys
-rw-r----- 1 mysql mysql 16777216 Jan  3 22:32  undo_001
-rw-r----- 1 mysql mysql 16777216 Jan  3 22:32  undo_002
-rw-r----- 1 mysql mysql      504 Jan  3 22:32  xtrabackup_info

#mysqldb-2目标主机启动数据库和查看备份数据是否已还原恢复
[root@mysqldb-2 /]#systemctl start mysqld
[root@mysqldb-2 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 Source distribution

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           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Wang Fei      |  22 | M      |
|   6 | Yao xiaolu    |  23 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.35 sec)
#可以看到两次增量修改备份的数据都已恢复

2.4 实现mysql主从复制,主主复制和半同步复制

2.4.1 mysql主从复制

#实验环境
mysql-db版本:mysql-8.0.30
mysqldb-master节点:192.168.100.101
mysqldb-slave节点:192.168.100.102

#master节点配置
[root@mysqldb-master ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=101
log-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-master ~]#systemctl restart mysqld

#master节点创建复制用户和授权
mysql> create user 'repluser'@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected (0.12 sec)

mysql> grant replication slave on *.* to 'repluser'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)

#master节点执行完全备份
[root@mysqldb-master ~]#mysqldump -A -F --source-data=1 --single-transaction > /data/full_back.sql 

#拷贝完全备份至slave节点
[root@mysqldb-master ~]#scp /data/full_back.sql  192.168.100.102:/data/

#slave节点配置
[root@mysqldb-slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=102
read-only
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-slave ~]#systemctl restart mysqld

#slave节点修改备份文件
[root@mysqldb-slave ~]#vim /data/full_back.sql
CHANGE MASTER TO 
MASTER_HOST='192.168.100.101',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysqldb-master-bin.000005', MASTER_LOG_POS=157;

#slave节点执行恢复备份
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /data/full_back.sql;
...(中间日志省略)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

#slave节点启用复制进程
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

#slave节点查看复制状态
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.100.101
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysqldb-master-bin.000005
          Read_Source_Log_Pos: 157
               Relay_Log_File: mysqldb-slave-relay-bin.000002
                Relay_Log_Pos: 335
        Relay_Source_Log_File: mysqldb-master-bin.000005
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 157
              Relay_Log_Space: 553
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 101
                  Source_UUID: 73ded33e-8b32-11ed-839e-000c29bbd45b
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#master节点执行表数据更新
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         101 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Wang Fei      |  22 | M      |
|   6 | Yao xiaolu    |  23 | F      |
|   7 | Deng Baoguo   |  31 | M      |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)

mysql> insert into hellodb.teachers values(8,'Yang Siyu',33,'F');
Query OK, 1 row affected (0.01 sec)

#slave节点查询表数据是否可以正常同步
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Wang Fei      |  22 | M      |
|   6 | Yao xiaolu    |  23 | F      |
|   7 | Deng Baoguo   |  31 | M      |
|   8 | Yang Siyu     |  33 | F      |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)

2.4.2 mysql主主复制

#实验环境
mysql-db版本:mysql-8.0.30
mysqldb-master节点1:192.168.100.101
mysqldb-master节点2:192.168.100.102

#mysqldb-master节点1配置
[root@mysqldb-master1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=101
log-bin
auto_increment_offset=1
auto_increment_increment=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-master1 ~]#systemctl start mysqld

#mysqldb-master节点1创建复制用户和授权
mysql> create user repluser@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repluser@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)

#mysqldb-master节点1执行完全备份
[root@mysqldb-master1 ~]#mysqldump -A -F --source-data=1 --single-transaction > /data/full_back.sql

#拷贝完全备份至第二个master节点
[root@mysqldb-master1 ~]#scp /data/full_back.sql 192.168.100.102:/data/

#mysqldb-master节点2配置
[root@mysqldb-master2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=102
log-bin
auto_increment_offset=2
auto_increment_increment=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-master2 ~]#systemctl start mysqld

#mysqldb-master节点2修改备份文件
[root@mysqldb-master2 ~]#vim /data/full_back.sql
CHANGE MASTER TO
MASTER_HOST='192.168.100.101',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysqldb-master1-bin.000003', MASTER_LOG_POS=157;

#mysqldb-master节点2执行恢复备份
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /data/full_back.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

#mysqldb-master节点2启用复制进程
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

#mysqldb-master节点2查看复制状态
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.100.101
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysqldb-master1-bin.000003
Read_Source_Log_Pos: 157
Relay_Log_File: mysqldb-master2-relay-bin.000002
Relay_Log_Pos: 336
Relay_Source_Log_File: mysqldb-master1-bin.000003
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 157
Relay_Log_Space: 556
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 101
Source_UUID: fc5f454c-8bd8-11ed-925b-000c29bbd45b
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

#mysqldb-master节点1配置slave复制
mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='192.168.100.102',
-> SOURCE_USER='repluser',
-> SOURCE_PASSWORD='123456',
-> SOURCE_PORT=3306,
-> SOURCE_LOG_FILE='mysqldb-master2-bin.000002', SOURCE_LOG_POS=157;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.100.102
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysqldb-master2-bin.000002
Read_Source_Log_Pos: 465
Relay_Log_File: mysqldb-master1-relay-bin.000002
Relay_Log_Pos: 644
Relay_Source_Log_File: mysqldb-master2-bin.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 465
Relay_Log_Space: 864
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 102
Source_UUID: 62256273-8bda-11ed-9159-005056303f1f
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

#mysqldb-master节点1创建数据库和插入数据
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t1 (name) values ('user1');
Query OK, 1 row affected (0.00 sec)

#mysqldb-master节点2插入数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)

mysql> use db1;
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 t1 (name) values ('user2');
Query OK, 1 row affected (0.00 sec)

#mysqldb-master节点1和节点2同时插入数据
mysql> insert into t1 (name) values ('userx');
Query OK, 1 row affected (0.00 sec)

#查询t1表数据
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userx |
| 4 | userx |
+----+-------+
4 rows in set (0.00 sec)

#mysqldb-master节点1和节点2同时创建db2数据库
mysql> create database db2;
Query OK, 1 row affected (0.14 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

#查看复制状态
mysql> show replica status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.100.102
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysqldb-master2-bin.000002
Read_Source_Log_Pos: 2249
Relay_Log_File: mysqldb-master1-relay-bin.000002
Relay_Log_Pos: 1224
Relay_Source_Log_File: mysqldb-master2-bin.000002
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysqldb-master2-bin.000002, end_log_pos 2249. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Source_Log_Pos: 2062
Relay_Log_Space: 1631
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysqldb-master2-bin.000002, end_log_pos 2249. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Source_Server_Id: 102
Source_UUID: 62256273-8bda-11ed-9159-005056303f1f
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230104 11:39:41
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from performance_schema.replication_applier_status_by_worker limit 1 \G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1007
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysqldb-master2-bin.000002, end_log_pos 2249; Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2'
LAST_ERROR_TIMESTAMP: 2023-01-04 11:39:41.994172
LAST_APPLIED_TRANSACTION: ANONYMOUS
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-01-04 11:31:23.295788
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-01-04 11:31:23.295788
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2023-01-04 11:31:22.714539
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2023-01-04 11:31:22.716959
APPLYING_TRANSACTION: ANONYMOUS
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-01-04 11:39:42.444913
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-01-04 11:39:42.444913
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2023-01-04 11:39:41.863444
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)

ERROR:
No query specified

2.4.3 mysql半同步复制

#实验环境
mysql-db版本:mysql-8.0.30
mysqldb-master节点:192.168.100.101
mysqldb-slave节点1:192.168.100.102
mysqldb-slave节点2:192.168.100.103

#mysqldb-master节点配置
mysql> INSTALL PLUGIN rpl_semi_sync_source  SONAME 'semisync_source.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------+---------+
| Name                            | Status   | Type               | Library            | License |
+---------------------------------+----------+--------------------+--------------------+---------+
| rpl_semi_sync_source            | ACTIVE   | REPLICATION        | semisync_source.so | GPL     |
+---------------------------------+----------+--------------------+--------------------+---------+
46 rows in set (0.00 sec)

[root@mysqldb-master ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=101
log-bin
plugin-load-add=semisync_source
rpl_semi_sync_source_enabled=ON
rpl_semi_sync_source_timeout=3000
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-master ~]#systemctl restart mysqld

mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_source_enabled                | ON         |
| rpl_semi_sync_source_timeout                | 3000       |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+

#mysqldb-slave节点1配置
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PLUGINS;
+---------------------------------+----------+--------------------+---------------------+---------+
| Name                            | Status   | Type               | Library             | License |
+---------------------------------+----------+--------------------+---------------------+---------+
| rpl_semi_sync_replica           | ACTIVE   | REPLICATION        | semisync_replica.so | GPL     |
+---------------------------------+----------+--------------------+---------------------+---------+
46 rows in set (0.00 sec)

[root@mysqldb-slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=102
plugin_load_add=semisync_replica
rpl_semi_sync_replica_enabled=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-slave1 ~]#systemctl restart mysqld

mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| rpl_semi_sync_replica_enabled     | ON    |
| rpl_semi_sync_replica_trace_level | 32    |
+-----------------------------------+-------+
2 rows in set (0.00 sec)

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.100.101',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysqldb-master-bin.000004', MASTER_LOG_POS=692;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.100.101
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysqldb-master-bin.000004
          Read_Source_Log_Pos: 692
               Relay_Log_File: mysqldb-slave1-relay-bin.000002
                Relay_Log_Pos: 335
        Relay_Source_Log_File: mysqldb-master-bin.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 692
              Relay_Log_Space: 554
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 101
                  Source_UUID: 4067c48d-8bfa-11ed-bf09-000c29bbd45b
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#mysqldb-slave节点2配置
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PLUGINS;
+---------------------------------+----------+--------------------+---------------------+---------+
| Name                            | Status   | Type               | Library             | License |
+---------------------------------+----------+--------------------+---------------------+---------+
| rpl_semi_sync_replica           | ACTIVE   | REPLICATION        | semisync_replica.so | GPL     |
+---------------------------------+----------+--------------------+---------------------+---------+
46 rows in set (0.00 sec)

[root@mysqldb-slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=103
plugin_load_add=semisync_replica
rpl_semi_sync_replica_enabled=ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-slave2 ~]#systemctl restart mysqld

mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| rpl_semi_sync_replica_enabled     | ON    |
| rpl_semi_sync_replica_trace_level | 32    |
+-----------------------------------+-------+
2 rows in set (0.00 sec)

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.100.101',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysqldb-master-bin.000004', MASTER_LOG_POS=692;
Query OK, 0 rows affected, 9 warnings (0.12 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.100.101
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysqldb-master-bin.000004
          Read_Source_Log_Pos: 692
               Relay_Log_File: mysqldb-slave2-relay-bin.000002
                Relay_Log_Pos: 335
        Relay_Source_Log_File: mysqldb-master-bin.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 692
              Relay_Log_Space: 554
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 101
                  Source_UUID: 4067c48d-8bfa-11ed-bf09-000c29bbd45b
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#mysqldb-master节点查看半同步状态
mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_source_clients               | 2     |   <---两个slave节点
| Rpl_semi_sync_source_net_avg_wait_time     | 0     |
| Rpl_semi_sync_source_net_wait_time         | 0     |
| Rpl_semi_sync_source_net_waits             | 0     |
| Rpl_semi_sync_source_no_times              | 1     |
| Rpl_semi_sync_source_no_tx                 | 2     |
| Rpl_semi_sync_source_status                | ON    |
| Rpl_semi_sync_source_timefunc_failures     | 0     |
| Rpl_semi_sync_source_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_source_tx_wait_time          | 0     |
| Rpl_semi_sync_source_tx_waits              | 0     |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_source_wait_sessions         | 0     |
| Rpl_semi_sync_source_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

#mysqldb-slave节点查看半同步状态
mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | ON    |
+------------------------------+-------+
1 row in set (0.01 sec)

#验证测试
#mysqldb-master节点创建测试数据库db1
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)    <---立即返回成功
#mysqldb-slave节点查看同步结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#所有mysqldb-slave节点停止复制线程
mysql> stop replica;
Query OK, 0 rows affected (0.00 sec)

#mysqldb-master节点创建测试数据库db2
mysql> create database db2;
Query OK, 1 row affected (3.00 sec)   <---等待3秒才返回成功

#mysqldb-slave节点1恢复复制线程
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

#mysqldb-master节点创建测试数据库db3
mysql> create database db3;
Query OK, 1 row affected (0.01 sec)   <---立即返回成功

2.5 用mycat实现mysql的读写分离

 #实验环境

mysql-db版本:mysql-8.0.30
mysqldb-master节点:192.168.100.101
mysqldb-slave节点:192.168.100.102
mycat-server节点:192.168.100.103
mysql-client节点:192.168.100.104

#mysqldb-master节点配置
[root@mysqldb-master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=101
log-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-master ~]#systemctl start mysqld

#mysqldb-master节点创建复制用户和授权
mysql> create user repluser@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repluser@'192.168.100.%';
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+---------------------------+----------+--------------+------------------+-------------------+
| File                      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------------+----------+--------------+------------------+-------------------+
| mysqldb-master-bin.000002 |      692 |              |                  |                   |
+---------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#mysqldb-slave节点配置
[root@mysqldb-slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=102
read-only
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@mysqldb-slave ~]#systemctl start mysqld

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.100.101',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysqldb-master-bin.000002', MASTER_LOG_POS=692;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.100.101
                  Source_User: repluser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysqldb-master-bin.000002
          Read_Source_Log_Pos: 692
               Relay_Log_File: mysqldb-slave-relay-bin.000002
                Relay_Log_Pos: 335
        Relay_Source_Log_File: mysqldb-master-bin.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 692
              Relay_Log_Space: 553
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 101
                  Source_UUID: 79422403-8c07-11ed-b868-000c29bbd45b
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#mycat-server节点配置
#安装jdk
[root@mycat-server ~]#yum install -y java
[root@mycat-server ~]#java -version
openjdk version "1.8.0_352"
OpenJDK Runtime Environment (build 1.8.0_352-b08)
OpenJDK 64-Bit Server VM (build 25.352-b08, mixed mode)

#安装mycat
[root@mycat-server ~]#tar xzvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/services/
[root@mycat-server ~]#ls -l /usr/local/services/
total 4
-rw-r--r--. 1 root root 34 Dec  7 10:35 check_tmp_file.sh
drwxr-xr-x  7 root root 85 Jan  4 16:22 mycat

#配置环境变量
[root@mycat-server ~]#echo 'PATH=/usr/local/services/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat-server ~]#source /etc/profile.d/mycat.sh 

#修改mycat-server配置文件
[root@mycat-server ~]#vim //usr/local/services/mycat/conf/server.xml
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
[root@mycat-server ~]#vim /usr/local/services/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="192.168.100.101:3306" user="root" password="123456">
                <readHost host="host2" url="192.168.100.102:3306" user="root" password="123456" />              
                </writeHost>
        </dataHost>
</mycat:schema>

[root@mycat-server conf]#mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@mycat-server logs]#tailf wrapper.log 
STATUS | wrapper  | 2023/01/04 17:03:20 | Launching a JVM...
INFO   | jvm 1    | 2023/01/04 17:03:20 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/01/04 17:03:20 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/01/04 17:03:20 | 
INFO   | jvm 1    | 2023/01/04 17:03:24 | MyCAT Server startup successfully. see logs in logs/mycat.log

#mysql-client节点测试
[root@mysql-client ~]#mysql -uroot -p123456 -h192.168.100.103
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> 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 [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MySQL [TESTDB]> create table test(id int);
Query OK, 0 rows affected (0.01 sec)

MySQL [TESTDB]> insert into test values(@@server_id);
Query OK, 1 row affected (0.03 sec)

MySQL [TESTDB]> select * from test;   <---确认insert写入的数据是调度的master节点
+------+
| id   |
+------+
|  101 |
+------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select @@server_id;  <---确认select查询请求是调度的slave节点
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
1 row in set (0.00 sec)

#观察mysqldb-master节点和mysqldb-slave节点通用日志
[root@mysqldb-master ~]#tail -f /var/lib/mysql/mysqldb-master.log
2023-01-04T09:20:14.304097Z        28 Query     select user()
2023-01-04T09:20:24.305322Z        29 Query     select user()
2023-01-04T09:20:34.304178Z        26 Query     select user()
2023-01-04T09:20:44.304822Z        27 Query     select user()
2023-01-04T09:20:54.304479Z        28 Query     select user()
2023-01-04T09:21:04.304559Z        29 Query     select user()
2023-01-04T09:21:14.304608Z        26 Query     select user()
2023-01-04T09:21:24.304590Z        27 Query     select user()
2023-01-04T09:21:34.303877Z        28 Query     select user()
2023-01-04T09:21:44.304191Z        29 Query     select user()
2023-01-04T09:21:54.304534Z        26 Query     select user()
2023-01-04T09:22:04.304823Z        27 Query     select user()
2023-01-04T09:22:14.304590Z        28 Query     select user()
2023-01-04T09:22:24.304014Z        29 Query     select user()
2023-01-04T09:22:34.304486Z        26 Query     select user()
2023-01-04T09:22:44.304248Z        27 Query     select user()
2023-01-04T09:22:50.016475Z        28 Query     SET names utf8;
2023-01-04T09:22:50.016771Z        28 Query     delete from test
2023-01-04T09:22:54.303886Z        29 Query     select user()
2023-01-04T09:22:59.384603Z        26 Query     insert into test values(@@server_id)
2023-01-04T09:23:04.303757Z        27 Query     select user()
2023-01-04T09:23:14.303800Z        28 Query     select user()
2023-01-04T09:23:24.304408Z        29 Query     select user()

[root@mysqldb-slave mysql]#tail -f /var/lib/mysql/mysqldb-slave.log
/usr/libexec/mysqld, Version: 8.0.30 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2023-01-04T09:21:11.660632Z        35 Query     show replica status
2023-01-04T09:21:14.749698Z        30 Query     select user()
2023-01-04T09:21:19.801680Z        35 Query     show variables like 'general_log_file'
2023-01-04T09:21:20.858797Z        35 Quit
2023-01-04T09:21:24.749796Z        27 Query     select user()
2023-01-04T09:21:34.748916Z        28 Query     select user()
2023-01-04T09:21:44.749310Z        29 Query     select user()
2023-01-04T09:21:49.403710Z        32 Query     SET names utf8;
2023-01-04T09:21:49.403927Z        32 Query     select @@server_id
2023-01-04T09:21:54.749480Z        31 Query     select user()
2023-01-04T09:21:59.540443Z        34 Query     SET names utf8;
2023-01-04T09:21:59.540596Z        34 Query     select * from test
2023-01-04T09:22:04.749882Z        33 Query     select user()
2023-01-04T09:22:14.749621Z        30 Query     select user()
2023-01-04T09:22:24.749106Z        27 Query     select user()
2023-01-04T09:22:34.749496Z        28 Query     select user()
2023-01-04T09:22:44.749325Z        29 Query     select user()
2023-01-04T09:22:50.464277Z        23 Query     BEGIN
2023-01-04T09:22:50.464556Z        23 Query     COMMIT /* implicit, from Xid_log_event */
2023-01-04T09:22:54.748921Z        32 Query     select user()
2023-01-04T09:22:59.831456Z        23 Query     BEGIN
2023-01-04T09:22:59.831618Z        23 Query     COMMIT /* implicit, from Xid_log_event */
2023-01-04T09:23:03.570220Z        31 Query     select * from test
2023-01-04T09:23:04.748827Z        34 Query     select user()
2023-01-04T09:23:13.216395Z        33 Query     select @@server_id
2023-01-04T09:23:14.748878Z        30 Query     select user()
2023-01-04T09:23:24.749401Z        27 Query     select user()

#停止slave节点,查看mycat是否自动调度读请求到master节点
[root@mysqldb-slave mysql]#systemctl stop mysqld

[root@mysql-client ~]#mysql -uroot -p123456 -h192.168.100.103
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         101 |
+-------------+
1 row in set (0.02 sec)

MySQL [(none)]> select * from test;
+------+
| id   |
+------+
|  101 |
+------+
1 row in set (0.02 sec)

MySQL [(none)]>

[root@mysqldb-master ~]#tail -f /var/lib/mysql/mysqldb-master.log
2023-01-04T09:24:44.305015Z        27 Query     select user()
2023-01-04T09:24:54.304715Z        28 Query     select user()
2023-01-04T09:25:04.303957Z        29 Query     select user()
2023-01-04T09:25:14.305623Z        31 Query     select user()
2023-01-04T09:25:24.304616Z        30 Query     select user()
2023-01-04T09:25:34.303926Z        26 Query     select user()
2023-01-04T09:25:44.303842Z        27 Query     select user()
2023-01-04T09:25:54.305288Z        28 Query     select user()
2023-01-04T09:26:04.304321Z        29 Query     select user()
2023-01-04T09:26:14.304142Z        31 Query     select user()
2023-01-04T09:26:24.304006Z        30 Query     select user()
2023-01-04T09:26:34.304001Z        26 Query     select user()
2023-01-04T09:26:38.755413Z        27 Query     SET names utf8;
2023-01-04T09:26:38.755821Z        27 Query     select @@server_id
2023-01-04T09:26:41.094655Z        28 Query     select * from test
2023-01-04T09:26:44.304144Z        29 Query     select user()
2023-01-04T09:26:54.304071Z        31 Query     select user()
2023-01-04T09:27:04.304176Z        30 Query     select user()

 

posted @ 2023-01-08 22:09  zcloudsre  阅读(69)  评论(0编辑  收藏  举报