南昌网站排名优化,怎么做二维码直接进入网站,英文wordpress转中文乱码,wordpress伪静态文件xtrabackup搭建MySQL 8.0 主从复制 安装MySQL 8.0.37安装xtrabackupGTIDs初始化从库参考#xff1a;GTID概述GTID相较与传统复制的优势GTID自身存在哪些限制GTID工作原理简单介绍如何开启GTID复制GTID与传统模式建立复制时候语句的不同点传统复制GTID复制 GTID同步状态简单解析… xtrabackup搭建MySQL 8.0 主从复制 安装MySQL 8.0.37安装xtrabackupGTIDs初始化从库参考GTID概述GTID相较与传统复制的优势GTID自身存在哪些限制GTID工作原理简单介绍如何开启GTID复制GTID与传统模式建立复制时候语句的不同点传统复制GTID复制 GTID同步状态简单解析 本次搭建的环境信息如下 OS版本:Red Hat Enterprise Linux Server release 7.6 (Maipo) MySQL版本Server version: 8.0.37 xtrabackup版本xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7) 本次采用物理备份和还原的方式来进行主从复制的初始化相比于mysqldump本方案适合数据量比较大的情况下初始化很快而且不像mysqldump的方式从库不需要先进行初始化。
安装MySQL 8.0.37
groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
cd /opt
unxz mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz
tar -xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar
cd /usr/local
ln -s /opt/mysql-8.0.37-linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql/
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
mv /opt/my.cnf /data/mysql/mysql3306/my3306.cnf
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/mysql3306//usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3306/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3306/my3306.cnf /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
alter user user() identified by mysql;安装xtrabackup
xtrabackup是社区开源产品。但是它不是MySQL自带的工具需要我们自己进行安装。下面就说下安装过程 1、 下载安装包 由于xtrabackup有版本的区别根据最佳实践我们安装的MySQL版本为8.0.37xtrabackup选择8.0以上最新版本即可不选择2.4的版本rpm安装方便简单。 下载地址https://www.percona.com/downloads version:选择最新的 software选择rpm就选RHELx。如果是二进制就选 Linux-Generic 2、安装为了解决软件依赖问题采用yum localinstall来安装 缺两个包:zstd和libev.so.4()(64bit)镜像里面没有这两个包我们手动下载匹配版本进行安装
# rpm -ivh libev-4.15-7.el7.x86_64.rpm
# rpm -ivh zstd-1.5.5-1.el7.x86_64.rpm重新安装成功
[rootpostgre opt]# yum localinstall percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
Marking percona-xtrabackup-80-8.0.35-31.1.el7.x86_64.rpm to be installed
Resolving Dependencies
-- Running transaction check
--- Package percona-xtrabackup-80.x86_64 0:8.0.35-31.1.el7 will be installed
-- Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
-- Processing Dependency: perl(Digest::MD5) for package: percona-xtrabackup-80-8.0.35-31.1.el7.x86_64
-- Running transaction check
--- Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
--- Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed
-- Processing Dependency: perl(Digest::base) 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64
-- Running transaction check
--- Package perl-Digest.noarch 0:1.17-245.el7 will be installed
-- Finished Dependency ResolutionDependencies ResolvedPackage Arch Version Repository SizeInstalling:percona-xtrabackup-80 x86_64 8.0.35-31.1.el7 /percona-xtrabackup-80-8.0.35-31.1.el7.x86_64 219 M
Installing for dependencies:perl-DBD-MySQL x86_64 4.023-6.el7 base 140 kperl-Digest noarch 1.17-245.el7 base 23 kperl-Digest-MD5 x86_64 2.52-3.el7 base 30 kTransaction SummaryInstall 1 Package (3 Dependent packages)Total size: 219 M
Total download size: 193 k
Installed size: 219 M
Is this ok [y/d/N]: y
Downloading packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 464 kB/s | 193 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 1/4 Installing : perl-Digest-1.17-245.el7.noarch 2/4 Installing : perl-Digest-MD5-2.52-3.el7.x86_64 3/4 Installing : percona-xtrabackup-80-8.0.35-31.1.el7.x86_64 4/4
base/productid | 1.6 kB 00:00:00 Verifying : perl-Digest-1.17-245.el7.noarch 1/4 Verifying : perl-Digest-MD5-2.52-3.el7.x86_64 2/4 Verifying : percona-xtrabackup-80-8.0.35-31.1.el7.x86_64 3/4 Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 4/4 Installed:percona-xtrabackup-80.x86_64 0:8.0.35-31.1.el7 Dependency Installed:perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 Complete!xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)GTIDs
搭建复制最基础的参数 | gtid_mode | ON | | binlog_format | ROW | 那么GTID是什么东西哪为什么在复制环境中这么重要 他其实就是一串数字给每个事务做个唯一标识一般表示为server_uuidtransaction_id,如8e14e45d-4ff1-11ef-9808-000c29e3c118:1-6 。其中UUID(即server_uuid) 存于文件
[rootpostgre data]# cat /data/mysql/mysql3306/data/auto.cnf
[auto]
server-uuidf25d3a39-53a2-11ef-9508-000c29a318a4是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量并且随着事务提交单调递增所以GTID能够保证每个MySQL实例事务的执行不会重复执行同一个事务并且会补全没有执行的事务。GTID在一组复制中全局唯一。 GTID用来代替传统复制方法不再使用MASTER_LOG_FILEMASTER_LOG_POS开启复制。 而是使用MASTER_AUTO_POSTION1的方式开始复制从服务器连接到主服务器之后把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码)SQL线程 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) IO线程发给主服务器主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候找出同步最成功的那台从服务器直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主 先change到同步最成功的那台从服务器 等把GTID全部补全了就可以把它提升为主了。
GTID是用区间来表示的事务 第一个事务8e14e45d-4ff1-11ef-9808-000c29e3c118:1 第二个事务8e14e45d-4ff1-11ef-9808-000c29e3c118:1-2 第三个事务8e14e45d-4ff1-11ef-9808-000c29e3c118:1-3 原则上来讲主从结构的GTIDs是连续的如果从库上的GTID是不连续那么肯定就是丢数据了。 既然开启GTID较之前有很多的优势那么在5.7值后我们一般都建议开启GTID进行复制。
初始化从库
[rootpostgre data]# xtrabackup --defaults-file/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p --backup --target-dir/data/backup/db3306_full
2024-08-06T13:54:21.17738408:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id3306 --datadir/data/mysql/mysql3306/data --open_files_limit65535 --log_bin/data/mysql/mysql3306/data/mybinlog --innodb_buffer_pool_size100M --innodb_data_file_pathibdata1:12M:autoextend --innodb_flush_log_at_trx_commit1 --innodb_log_buffer_size32M --innodb_log_file_size1G --innodb_log_files_in_group3 --innodb_io_capacity4000 --innodb_open_files65535 --innodb_flush_methodO_DIRECT --innodb_adaptive_hash_index0 --innodb_adaptive_hash_index0
2024-08-06T13:54:21.17755308:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port3306 --socket/tmp/mysql3306.sock --socket/tmp/mysql3306.sock --userroot --password --backup1 --target-dir/data/backup/db3306_full
Enter password:
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
240806 13:54:23 version_check Connecting to MySQL server with DSN dbi:mysql:;mysql_read_default_groupxtrabackup;port3306;mysql_socket/tmp/mysql3306.sock as root (using password: YES).
Failed to connect to MySQL server: DBI connect(;mysql_read_default_groupxtrabackup;port3306;mysql_socket/tmp/mysql3306.sock,root,...) failed: Authentication plugin caching_sha2_password cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
2024-08-06T13:54:23.64020508:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql3306.sock
2024-08-06T13:54:23.65540508:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.37
2024-08-06T13:54:23.68186508:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2024-08-06T13:54:23.68295008:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2024-08-06T13:54:23.68298808:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysql/mysql3306/data
2024-08-06T13:54:23.68300608:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 65535, set to 65535
........
performance_schema/keyring_componen_191.sdi to /data/backup/db3306_full/performance_schema/keyring_componen_191.sdi
2024-08-06T13:54:25.82805808:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2024-08-06T13:54:25.82809808:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2024-08-06T13:54:25.88479908:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2024-08-06T13:54:25.95050008:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/mysql/mysql3306/data/mybinlog.000003 to /data/backup/db3306_full/mybinlog.000003 up to position 197
2024-08-06T13:54:25.95057008:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/mysql/mysql3306/data/mybinlog.000003 to /data/backup/db3306_full/mybinlog.000003
2024-08-06T13:54:25.95169708:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/mybinlog.index
2024-08-06T13:54:25.95176808:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/mybinlog.index
2024-08-06T13:54:25.96054808:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/xtrabackup_binlog_info
2024-08-06T13:54:25.96061908:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/xtrabackup_binlog_info
2024-08-06T13:54:25.96151808:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2024-08-06T13:54:25.98888108:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): 20338027
2024-08-06T13:54:25.98897408:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 20338027
2024-08-06T13:54:25.99116508:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn 20337695
2024-08-06T13:54:26.01699008:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2024-08-06T13:54:26.02486508:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2024-08-06T13:54:26.02504108:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /data/backup/db3306_full/ib_buffer_pool
2024-08-06T13:54:26.02548808:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /data/backup/db3306_full/ib_buffer_pool
2024-08-06T13:54:26.02670908:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory /data/backup/db3306_full/
2024-08-06T13:54:26.02673508:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename mybinlog.000003, position 197, GTID of the last change f25d3a39-53a2-11ef-9508-000c29a318a4:1-4
2024-08-06T13:54:26.03617708:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/backup-my.cnf
2024-08-06T13:54:26.03624508:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/backup-my.cnf
2024-08-06T13:54:26.07873008:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db3306_full/xtrabackup_info
2024-08-06T13:54:26.07883308:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db3306_full/xtrabackup_info
2024-08-06T13:54:27.08885208:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (20338027) to (20338893) was copied.
2024-08-06T13:54:27.31901808:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[rootpostgre data]# 还原 –prepare的过程为了避免影响原备份文件可以先把备份文件拷贝到其他路径进行prepare。
[rootpostgre ~]# xtrabackup --prepare --target-dir/data/backup/db3306_full
2024-08-06T14:05:25.90659408:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithmcrc32 --innodb_log_checksums1 --innodb_data_file_pathibdata1:12M:autoextend --innodb_log_file_size1073741824 --innodb_page_size16384 --innodb_undo_directory./ --innodb_undo_tablespaces2 --server-id3306 --innodb_log_checksumsON --innodb_redo_log_encrypt0 --innodb_undo_log_encrypt0
2024-08-06T14:05:25.90677808:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare1 --target-dir/data/backup/db3306_full
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
2024-08-06T14:05:25.90680408:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/db3306_full/
2024-08-06T14:05:25.90688008:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2024-08-06T14:05:25.92038308:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size8388608, start_lsn(20338027)
2024-08-06T14:05:25.92438308:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-08-06T14:05:25.92441408:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir .
2024-08-06T14:05:25.92442008:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path ibdata1:12M:autoextend
2024-08-06T14:05:25.92448208:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir .
2024-08-06T14:05:25.92449208:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group 1
2024-08-06T14:05:25.92450308:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size 8388608
2024-08-06T14:05:25.92476908:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-08-06T14:05:25.92517708:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2024-08-06T14:05:25.92519208:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir .
2024-08-06T14:05:25.92519808:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path ibdata1:12M:autoextend
2024-08-06T14:05:25.92520908:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir .
2024-08-06T14:05:25.92521508:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group 1
2024-08-06T14:05:25.92522008:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size 8388608
2024-08-06T14:05:25.92522908:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery.
2024-08-06T14:05:25.92523508:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2024-08-06T14:05:25.92526508:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2024-08-06T14:05:25.92528008:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes
2024-08-06T14:05:25.92528608:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2024-08-06T14:05:25.92529608:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2024-08-06T14:05:25.92546408:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2024-08-06T14:05:25.92583808:00 0 [Note] [MY-012203] [InnoDB] Directories to scan ./
2024-08-06T14:05:25.92587308:00 0 [Note] [MY-012204] [InnoDB] Scanning ./
2024-08-06T14:05:25.92870008:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 5 files.
2024-08-06T14:05:25.92985408:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size 128.000000M, instances 1, chunk size 128.000000M
2024-08-06T14:05:25.93637908:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2024-08-06T14:05:26.05978608:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20
2024-08-06T14:05:26.12177708:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.15669508:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.18792808:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20
2024-08-06T14:05:26.27954808:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn 20338027 in redo log file ./#innodb_redo/#ib_redo0.
2024-08-06T14:05:26.28068408:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 19876654 in the system tablespace does not match the log sequence number 20338027 in the redo log files!
2024-08-06T14:05:26.28070608:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-08-06T14:05:26.28071608:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-08-06T14:05:26.29135608:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn 20337695, whereas checkpoint_lsn 20338027 and start_lsn 20337664
2024-08-06T14:05:26.29138208:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 20338027
2024-08-06T14:05:26.30876208:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-08-06T14:05:26.56544008:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2024-08-06T14:05:26.56547608:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed!
2024-08-06T14:05:26.66640408:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-08-06T14:05:26.67336608:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN20338027.
2024-08-06T14:05:26.67340408:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2024-08-06T14:05:26.67347608:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2024-08-06T14:05:26.73859908:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes
2024-08-06T14:05:26.73880808:00 0 [Note] [MY-013623] [InnoDB] fallocate() failed with errno 95 - falling back to writing NULLs.
2024-08-06T14:05:26.80214708:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0
2024-08-06T14:05:26.80824708:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN20338188
2024-08-06T14:05:26.80832308:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-08-06T14:05:27.06532708:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace ./undo_001.
2024-08-06T14:05:27.06612408:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace ./undo_002.
2024-08-06T14:05:27.06742008:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-08-06T14:05:27.06753708:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1567
2024-08-06T14:05:27.11314708:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 1 thread: 38436 ms.
2024-08-06T14:05:27.11609908:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-08-06T14:05:27.11625508:00 0 [Note] [MY-012265] [InnoDB] Setting file ./ibtmp1 size to 12 MB. Physically writing the file full; Please wait ...
2024-08-06T14:05:27.13544908:00 0 [Note] [MY-012266] [InnoDB] File ./ibtmp1 size is now 12 MB.
2024-08-06T14:05:27.13565308:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:./#innodb_temp/
2024-08-06T14:05:27.14888808:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2024-08-06T14:05:27.18033108:00 0 [Note] [MY-012976] [InnoDB] 8.0.35 started; log sequence number 20338198
2024-08-06T14:05:27.18095308:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2024-08-06T14:05:27.18188808:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 3 tablespaces into cache in 0.00126516 seconds
2024-08-06T14:05:27.20544708:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.0235197 seconds
2024-08-06T14:05:27.24997508:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown 1
2024-08-06T14:05:27.25019008:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2024-08-06T14:05:28.22732608:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2024-08-06T14:05:28.33251108:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-08-06T14:05:28.34940608:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 20338198
2024-08-06T14:05:28.35635908:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!–copy-back此处是把文件从备份处拷贝到datadir下所以指定了3308的配置文件里面有datadir具体的路径。
[rootpostgre db3306_full]# xtrabackup --defaults-file/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir/data/backup/db3306_full/
2024-08-06T14:10:35.14053308:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id3308 --datadir/data/mysql/mysql3308/data --open_files_limit65535 --log_bin/data/mysql/mysql3308/data/mybinlog --innodb_buffer_pool_size100M --innodb_data_file_pathibdata1:12M:autoextend --innodb_flush_log_at_trx_commit1 --innodb_log_buffer_size32M --innodb_log_file_size1G --innodb_log_files_in_group3 --innodb_io_capacity4000 --innodb_open_files65535 --innodb_flush_methodO_DIRECT --innodb_adaptive_hash_index0 --innodb_adaptive_hash_index0
2024-08-06T14:10:35.14068808:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port3308 --socket/tmp/mysql3308.sock --copy-back1 --target-dir/data/backup/db3306_full/
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
2024-08-06T14:10:35.14071108:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/db3306_full/
2024-08-06T14:10:35.14150708:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /data/mysql/mysql3308/data/undo_001
2024-08-06T14:10:35.20068708:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /data/mysql/mysql3308/data/undo_001
2024-08-06T14:10:35.20990708:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /data/mysql/mysql3308/data/undo_002
2024-08-06T14:10:35.24372508:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /data/mysql/mysql3308/data/undo_002
2024-08-06T14:10:35.26055008:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /data/mysql/mysql3308/data/ibdata1
......
2024-08-06T14:10:35.55716008:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./xtrabackup_info to /data/mysql/mysql3308/data/xtrabackup_info
2024-08-06T14:10:35.55722008:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./xtrabackup_info to /data/mysql/mysql3308/data/xtrabackup_info
2024-08-06T14:10:35.55784508:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2024-08-06T14:10:35.55787608:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2024-08-06T14:10:35.55795408:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to /data/mysql/mysql3308/data/ibtmp1
2024-08-06T14:10:35.59585508:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to /data/mysql/mysql3308/data/ibtmp1
2024-08-06T14:10:35.61869008:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!启动 /usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3308/my3308.cnf [rootpostgre data]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, 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 master status;
-----------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------------------------------
| mybinlog.000004 | 197 | | | f25d3a39-53a2-11ef-9508-000c29a318a4:1-4 |
-----------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)xtrabackup还原后可以看到从库的GTID信息已经有了而且是和主库一致的那么此时我们就可以直接change
mysql change master to master_host10.1.11.30, master_port3306, master_userrepl, master_passwordrepl,master_auto_position1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql start slave;
Query OK, 0 rows affected, 1 warning (0.37 sec)mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Connecting to sourceMaster_Host: 10.1.11.30Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: postgre-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: ConnectingSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 157Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 2061Last_IO_Error: Error connecting to source repl10.1.11.30:3306. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin caching_sha2_password reported error: Authentication requires secure connection.Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 240806 14:22:03Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.35 sec)mysql show slave status\G
*************************** 1. row ***************************
......Slave_IO_Running: ConnectingSlave_SQL_Running: Yes
.....Last_IO_Error: Error connecting to source repl10.1.11.30:3306. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin caching_sha2_password reported error: Authentication requires secure connection.
......Retrieved_Gtid_Set: Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-4
.....
1 row in set, 1 warning (0.07 sec)
直接start slave会报错。这是MySQL 8.0新的密码机制决定的我们手动登录一次即可或者change加参数GET_MASTER_PUBLIC_KEY1或者改为原始的认证方式mysql_native_passwor ,三者选其一即可。 我们采用第二种
mysql stop slave;
Query OK, 0 rows affected, 1 warning (0.07 sec)mysql change master to Get_master_public_key1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql start slave;
Query OK, 0 rows affected, 1 warning (0.12 sec)恢复正常:
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 10.1.11.30Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mybinlog.000004Read_Master_Log_Pos: 737Relay_Log_File: postgre-relay-bin.000002Relay_Log_Pos: 911Relay_Master_Log_File: mybinlog.000004Slave_IO_Running: YesSlave_SQL_Running: Yes
.......Retrieved_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:5-6Executed_Gtid_Set: f25d3a39-53a2-11ef-9508-000c29a318a4:1-6Auto_Position: 1
......Get_master_public_key: 1还有一点在上面start slave之前状态是这样的 Executed_Gtid_Set:表示从库已经执行了的GTID就跟前面一致现在从库的GTID已经执行到4 seq#,如果此时主库的GTID执行比4大那么Retrieved_Gtid_Set就会显示新的值比4大的从库缺少的从4以来的记录。
参考
MySQL主从复制之GTID模式介绍
GTID概述
MySQL5.6 在原有主从复制的基础上增加了一个新的复制方式即基于GTID的复制方式它由UUID和事务ID两个部分组成具有如下特点。 GTID事务是全局唯一性的并且一个事务对应一个GTID值。 一个GTID值在同一个MySQL实例上只会执行一次。
GTID相较与传统复制的优势
主从搭建更加简便不用手动特地指定position位置。 复制集群内有一个统一的标识识别、管理上更方便。 故障转移更容易不用像传统复制那样需要找 log_file 和 log_Pos的位置。 通常情况下GTID是连续没有空洞的更能保证数据的一致性零丢失。 相对于ROW复制模式数据安全性更高切换更简单。 比传统的复制更加安全一个GTID在一个MySQL实例上只会执行一次避免重复执行导致数据混乱或者主从不一致。
GTID自身存在哪些限制
在一个复制组中必须都要开启GTID。 MySQL5.6开启GTID需要重启。 不支持sql_slave_skip_counte操作传统复制可以使用这个命令跳过事务。 不允许在一个SQL同时更新一个事务引擎和非事务引擎的表如InnoDB和MyISAM。 对于create temporary table 和drop temporary table语句不支持。 不支持create table … select 语句复制。
GTID工作原理简单介绍
master节点在更新数据的时候会在事务前产生GTID信息一同记录到binlog日志中。 slave节点的io线程将binlog写入到本地relay log中。 然后SQL线程从relay log中读取GTID设置gtid_next的值为该gtid然后对比slave端的binlog是否有记录。 如果有记录的话说明该GTID的事务已经运行slave会忽略。 如果没有记录的话slave就会执行该GTID对应的事务并记录到binlog中。
如何开启GTID复制
除传统复制需要开启的binlog相关参数之外GTID同步需额外开启如下参数设置注意主从节点需要同步开启。
查看GTID相关参数
参数简要说明
GTID与传统模式建立复制时候语句的不同点
传统复制
change master to master_host“127.0.0.1”,master_port3310,MASTER_USER‘sync’,MASTER_PASSWORD‘GreatSQL’,MASTER_LOG_FILE‘log-bin.000005’, MASTER_LOG_POS4111;
GTID复制
change master to master_host“127.0.0.1”,master_port3310,MASTER_USER‘sync’,MASTER_PASSWORD‘GreatSQL’,MASTER_AUTO_POSITION1 GTID同步在建立复制的时候将传统复制由人为指定binlog的pos位点改为了MASTER_AUTO_POSITION1自动获取binlog的pos位点。
GTID同步状态简单解析
除了传统的查看binlog和pos值之外GTID模式可以更直观的查看某个事务执行的情况。
[rootGreatSQL][(none)]show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.215 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 2425 Relay_Log_File: mgr2-relay-bin.000002 Relay_Log_Pos: 2634 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: Yes Slave_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_Master_Log_Pos: 2425 Relay_Log_Space: 2842 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2153306 Master_UUID: 613743f5-8b1c-11ec-9922-00155dcff911 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10 Executed_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10, 652ade08-8b1c-11ec-9f62-00155dcff90a:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.01 sec)
ERROR: No query specified GTID相关键参数说明
整个过程如下
groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
cd /opt
unxz mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz
tar -xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar
cd /usr/local
ln -s /opt/mysql-8.0.37-linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql/
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
mv /opt/my.cnf /data/mysql/mysql3306/my3306.cnf
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/mysql3306//usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3306/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3306/my3306.cnf /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
alter user user() identified by mysql;
show databases;
create database test;
use test;
create table test(id int,name char);
insert into test values(1,a);
create user repl% identified by repl;
grant replication slave on *.* to repl%;cd /data/
mkdir -p backup/db3306_full/
xtrabackup --defaults-file/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p --backup --target-dir/data/backup/db3306_full
xtrabackup --prepare --target-dir/data/backup/db3306_full
mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
cp /data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3308/my3308.cnf
sed -i s/3306/3308/g /data/mysql/mysql3308/my3308.cnf
xtrabackup --defaults-file/data/mysql/mysql3308/my3308.cnf --copy-back --target-dir/data/backup/db3306_full/
chown -R mysql:mysql /data/mysql/mysql3308//usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3308/my3308.cnf
/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock
/usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -urepl -prepl
change master to master_host10.1.11.30, master_port3306, master_userrepl, master_passwordrepl,master_auto_position1;
start slave;
show slave status\G或者不用登录一次直接加参数解决
change master to master_host10.1.11.30, master_port3306, master_userrepl, master_passwordrepl,master_auto_position1 GET_MASTER_PUBLIC_KEY1;
文章转载自： http://www.morning.ljcf.cn.gov.cn.ljcf.cn http://www.morning.nbfkk.cn.gov.cn.nbfkk.cn http://www.morning.gbnsq.cn.gov.cn.gbnsq.cn http://www.morning.mrfr.cn.gov.cn.mrfr.cn http://www.morning.ttdxn.cn.gov.cn.ttdxn.cn http://www.morning.lthtp.cn.gov.cn.lthtp.cn http://www.morning.liyixun.com.gov.cn.liyixun.com http://www.morning.lmfxq.cn.gov.cn.lmfxq.cn http://www.morning.qdxwf.cn.gov.cn.qdxwf.cn http://www.morning.jhxdj.cn.gov.cn.jhxdj.cn http://www.morning.sskkf.cn.gov.cn.sskkf.cn http://www.morning.dtzsm.cn.gov.cn.dtzsm.cn http://www.morning.yqhdy.cn.gov.cn.yqhdy.cn http://www.morning.sgbsr.cn.gov.cn.sgbsr.cn http://www.morning.tgxrm.cn.gov.cn.tgxrm.cn http://www.morning.mzzqs.cn.gov.cn.mzzqs.cn http://www.morning.jgnst.cn.gov.cn.jgnst.cn http://www.morning.xjmyq.com.gov.cn.xjmyq.com http://www.morning.mpyry.cn.gov.cn.mpyry.cn http://www.morning.wjpsn.cn.gov.cn.wjpsn.cn http://www.morning.mnwmj.cn.gov.cn.mnwmj.cn http://www.morning.mhsmj.cn.gov.cn.mhsmj.cn http://www.morning.wskn.cn.gov.cn.wskn.cn http://www.morning.cwjxg.cn.gov.cn.cwjxg.cn http://www.morning.tfpmf.cn.gov.cn.tfpmf.cn http://www.morning.nlgnk.cn.gov.cn.nlgnk.cn http://www.morning.zffn.cn.gov.cn.zffn.cn http://www.morning.lssfd.cn.gov.cn.lssfd.cn http://www.morning.sgmis.com.gov.cn.sgmis.com http://www.morning.wpcfh.cn.gov.cn.wpcfh.cn http://www.morning.nmfml.cn.gov.cn.nmfml.cn http://www.morning.nbwyk.cn.gov.cn.nbwyk.cn http://www.morning.qfcnp.cn.gov.cn.qfcnp.cn http://www.morning.nyqm.cn.gov.cn.nyqm.cn http://www.morning.nfyc.cn.gov.cn.nfyc.cn http://www.morning.flfxb.cn.gov.cn.flfxb.cn http://www.morning.mkydt.cn.gov.cn.mkydt.cn http://www.morning.mjgxl.cn.gov.cn.mjgxl.cn http://www.morning.znlhc.cn.gov.cn.znlhc.cn http://www.morning.xsfg.cn.gov.cn.xsfg.cn http://www.morning.xbnkm.cn.gov.cn.xbnkm.cn http://www.morning.xsetx.com.gov.cn.xsetx.com http://www.morning.tddrh.cn.gov.cn.tddrh.cn http://www.morning.ybgcn.cn.gov.cn.ybgcn.cn http://www.morning.btwlp.cn.gov.cn.btwlp.cn http://www.morning.lcqrf.cn.gov.cn.lcqrf.cn http://www.morning.pngph.cn.gov.cn.pngph.cn http://www.morning.qpsxz.cn.gov.cn.qpsxz.cn http://www.morning.zlxrg.cn.gov.cn.zlxrg.cn http://www.morning.knscf.cn.gov.cn.knscf.cn http://www.morning.mnkhk.cn.gov.cn.mnkhk.cn http://www.morning.ntkpc.cn.gov.cn.ntkpc.cn http://www.morning.rykn.cn.gov.cn.rykn.cn http://www.morning.wyctq.cn.gov.cn.wyctq.cn http://www.morning.kjnfs.cn.gov.cn.kjnfs.cn http://www.morning.kbkcl.cn.gov.cn.kbkcl.cn http://www.morning.hxcrd.cn.gov.cn.hxcrd.cn http://www.morning.yhpl.cn.gov.cn.yhpl.cn http://www.morning.hqmfn.cn.gov.cn.hqmfn.cn http://www.morning.tongweishi.cn.gov.cn.tongweishi.cn http://www.morning.lmxzw.cn.gov.cn.lmxzw.cn http://www.morning.jqtb.cn.gov.cn.jqtb.cn http://www.morning.rqmr.cn.gov.cn.rqmr.cn http://www.morning.ftzll.cn.gov.cn.ftzll.cn http://www.morning.ykxnp.cn.gov.cn.ykxnp.cn http://www.morning.qxmnf.cn.gov.cn.qxmnf.cn http://www.morning.xnfg.cn.gov.cn.xnfg.cn http://www.morning.mzcrs.cn.gov.cn.mzcrs.cn http://www.morning.hhxwr.cn.gov.cn.hhxwr.cn http://www.morning.yrskc.cn.gov.cn.yrskc.cn http://www.morning.hmxb.cn.gov.cn.hmxb.cn http://www.morning.mfbcs.cn.gov.cn.mfbcs.cn http://www.morning.jtwck.cn.gov.cn.jtwck.cn http://www.morning.gfjgq.cn.gov.cn.gfjgq.cn http://www.morning.gblrn.cn.gov.cn.gblrn.cn http://www.morning.skbhl.cn.gov.cn.skbhl.cn http://www.morning.ywndg.cn.gov.cn.ywndg.cn http://www.morning.hmqmm.cn.gov.cn.hmqmm.cn http://www.morning.nkjpl.cn.gov.cn.nkjpl.cn http://www.morning.ggfdq.cn.gov.cn.ggfdq.cn
