第六周作业
#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)
#实验环境 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主从复制,主主复制和半同步复制
#实验环境 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) <---立即返回成功
#实验环境
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()