MySQL物理备份-XtraBackup远程备份恢复
总括：
生产环境500G数据备份，8个压缩线程，约2小时，压缩后大小约130G，qp文件解压约1小时
模拟从库故障，从主库备份数据恢复从库，把主库数据远程备份到从库。
1.主从库安装xtrabackup工具
#安装依赖
sudo yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey qpress
#依赖包下载地址
https://repo.percona.com/yum/release/7/RPMS/x86_64/
rpm -ivh percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm
2.备份并释放流文件:
若未配置ssh免登录：
nohup innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p'P@ssw0rd' -S /opt/mysql/data/3310/mysqld.sock --stream=xbstream --compress --compress-threads=16 /opt/mysql/data/3310/ |sshpass -p'rootroot' ssh root@10.186.57.34 "xbstream -x -C /data/xbstream" > /soft/backup.log 2>&1 &
若配置ssh免密登录：
nohup innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p --socket=/opt/mysql/data/3310/mysqld.sock --stream=xbstream --compress --compress-threads=8 /opt/mysql/data/3310/ | ssh root@10.186.57.34 'xbstream -x -C /data/xbstream' > /soft/backup.log 2>&1 &
3.解压:
xtrabackup --parallel=8 --decompress --remove-original --target-dir=/data/xbstream/
4.恢复:
#清理/opt/mysql/etc/3310/my.cnf内数据目录和redolog目录数据
apply-log
innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf --parallel=4 --apply-log /data/xbstream/
##/opt/mysql/etc/3310/my.cnf 可以是故障从库的配置文件
copy-backup
xtrabackup --defaults-file=/opt/mysql/etc/3310/my.cnf --copy-back --target-dir=/data/xbstream/
5.修改数据目录和redolog目录属主
chown -R mysql.mysql /data
chown -R mysql.mysql /redolog
6.启动从库mysql服务
/opt/mysql/base/8.0.28/bin/mysqld --defaults-file=/opt/mysql/etc/3310/my.cnf --daemonize --pid-file=/opt/mysql/data/3310/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3310/mysqld.sock --port=3310
7.建立复制
[root@localhost xbstream]# cat xtrabackup_binlog_info
mysql-bin.000010 184074483 304e4a27-33ed-11ef-91b7-02000aba3b26:1-25571
change replication source to
source_host='10.186.59.38',
source_port=3310,
source_user='repl',
source_password='P@ssw0rd',
source_log_file='mysql-bin.000010',
source_log_pos=184074483;
start replica;
show replica status \G;
8.等待主从数据追平1.安装xtrabackup工具
#安装依赖
sudo yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey qpress
#依赖包下载地址
https://repo.percona.com/yum/release/7/RPMS/x86_64/
[root@localhost soft]# rpm -ivh percona-xtrabackup-24-2.4.29-1.el7.x86_64\ \(1\).rpm
warning: percona-xtrabackup-24-2.4.29-1.el7.x86_64 (1).rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:percona-xtrabackup-24-2.4.29-1.el################################# [100%]
[root@localhost soft]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-3.el7.x86_64.rpm
2.备份并释放流文件
nohup bash -c 'innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf -uroot -p"P@ssw0rd" -S /opt/mysql/data/3310/mysqld.sock --stream=xbstream --compress --compress-threads=8 /opt/mysql/data/3310/ |sshpass -p'rootroot' ssh root@10.186.57.34 "xbstream -x -C /data/xbstream"' > /soft/xtrabackup.log 2>&1 &
##备份命令说明
1.compress压缩，开启8个压缩线程（可视数据库负载大小调整），压缩率可达70%，500G压缩至130G.
2.|sshpass -p'rootroot' 可实现远程备份到目标服务器，若两台服务器配置了ssh免密登录，则可去除sshpass -p'rootroot'。
3.备份命令后台运行，可在/soft/xtrabackup.log查看备份进度和信息。
#远程服务器/data/xbstream目录
[root@localhost xbstream]# ip a |grep 10.186
inet 10.186.57.34/24 brd 10.186.57.255 scope global eth0
[root@localhost xbstream]# ll
total 38980
-rw-r----- 1 root root 484 Jul 18 07:19 backup-my.cnf.qp
drwxr-x--- 2 root root 23 Jul 18 07:19 ccic_prod_mass0
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass1
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass2
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass3
-rw-r----- 1 root root 372 Jul 18 07:19 ib_buffer_pool.qp
-rw-r----- 1 root root 35850662 Jul 18 07:19 ibdata1.qp
drwxr-x--- 2 root root 4096 Jul 18 07:19 mysql
drwxr-x--- 2 root root 8192 Jul 18 07:19 performance_schema
drwxr-x--- 2 root root 8192 Jul 18 07:19 sys
drwxr-x--- 2 root root 111 Jul 18 07:19 test
drwxr-x--- 2 root root 67 Jul 18 07:19 universe
-rw-r----- 1 root root 166 Jul 18 07:19 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 147 Jul 18 07:19 xtrabackup_checkpoints
-rw-r----- 1 root root 619 Jul 18 07:19 xtrabackup_info.qp
-rw-r----- 1 root root 4012668 Jul 18 07:19 xtrabackup_logfile.qp
[root@localhost xbstream]#
3.解压
#解压命令，--remove-original参数删除原来的qp压缩文件。
xtrabackup --parallel=8 --decompress --remove-original --target-dir=/data/xbstream/
[root@localhost xbstream]# ip a |grep 10.186
inet 10.186.57.34/24 brd 10.186.57.255 scope global eth0
[root@localhost xbstream]# ll
total 38980
-rw-r----- 1 root root 484 Jul 18 07:19 backup-my.cnf.qp
drwxr-x--- 2 root root 23 Jul 18 07:19 ccic_prod_mass0
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass1
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass2
drwxr-x--- 2 root root 311 Jul 18 07:19 ccic_prod_mass3
-rw-r----- 1 root root 372 Jul 18 07:19 ib_buffer_pool.qp
-rw-r----- 1 root root 35850662 Jul 18 07:19 ibdata1.qp
drwxr-x--- 2 root root 4096 Jul 18 07:19 mysql
drwxr-x--- 2 root root 8192 Jul 18 07:19 performance_schema
drwxr-x--- 2 root root 8192 Jul 18 07:19 sys
drwxr-x--- 2 root root 111 Jul 18 07:19 test
drwxr-x--- 2 root root 67 Jul 18 07:19 universe
-rw-r----- 1 root root 166 Jul 18 07:19 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 147 Jul 18 07:19 xtrabackup_checkpoints
-rw-r----- 1 root root 619 Jul 18 07:19 xtrabackup_info.qp
-rw-r----- 1 root root 4012668 Jul 18 07:19 xtrabackup_logfile.qp
[root@localhost xbstream]# xtrabackup --parallel=8 --decompress --remove-original --target-dir=/data/xbstream/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --parallel=8
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --target-dir=/data/xbstream/
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
240718 07:31:26 [02] decompressing ./ibdata1.qp
240718 07:31:26 [03] decompressing ./mysql/help_category.ibd.qp
240718 07:31:26 [06] decompressing ./mysql/plugin.ibd.qp
240718 07:31:26 [08] decompressing ./mysql/servers.ibd.qp
240718 07:31:26 [05] decompressing ./mysql/help_relation.ibd.qp
240718 07:31:26 [01] decompressing ./mysql/help_topic.ibd.qp
240718 07:31:26 [04] decompressing ./mysql/help_keyword.ibd.qp
240718 07:31:26 [06] removing ./mysql/plugin.ibd.qp
240718 07:31:26 [07] decompressing ./mysql/time_zone_name.ibd.qp
240718 07:31:26 [03] removing ./mysql/help_category.ibd.qp
[root@localhost xbstream]# ll
total 1057920
-rw-r--r-- 1 root root 496 Jul 18 07:31 backup-my.cnf
drwxr-x--- 2 root root 20 Jul 18 07:31 ccic_prod_mass0
drwxr-x--- 2 root root 284 Jul 18 07:31 ccic_prod_mass1
drwxr-x--- 2 root root 284 Jul 18 07:31 ccic_prod_mass2
drwxr-x--- 2 root root 284 Jul 18 07:31 ccic_prod_mass3
-rw-r--r-- 1 root root 419 Jul 18 07:31 ib_buffer_pool
-rw-r--r-- 1 root root 1073741824 Jul 18 07:31 ibdata1
drwxr-x--- 2 root root 4096 Jul 18 07:31 mysql
drwxr-x--- 2 root root 8192 Jul 18 07:31 performance_schema
drwxr-x--- 2 root root 8192 Jul 18 07:31 sys
drwxr-x--- 2 root root 96 Jul 18 07:31 test
drwxr-x--- 2 root root 58 Jul 18 07:31 universe
-rw-r--r-- 1 root root 72 Jul 18 07:31 xtrabackup_binlog_info
-rw-r----- 1 root root 147 Jul 18 07:19 xtrabackup_checkpoints
-rw-r--r-- 1 root root 690 Jul 18 07:31 xtrabackup_info
-rw-r--r-- 1 root root 9519104 Jul 18 07:31 xtrabackup_logfile
4.恢复
#删除恢复数据目录数据
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 1116516
-rw-r----- 1 actiontech-mysql actiontech-mysql 56 Jul 2 06:32 auto.cnf
-rw------- 1 actiontech-mysql actiontech-mysql 1680 Jul 2 06:32 ca-key.pem
-rw-r--r-- 1 actiontech-mysql actiontech-mysql 1112 Jul 2 06:32 ca.pem
-rw-r--r-- 1 actiontech-mysql actiontech-mysql 1112 Jul 2 06:32 client-cert.pem
-rw------- 1 actiontech-mysql actiontech-mysql 1680 Jul 2 06:32 client-key.pem
-rw-r----- 1 actiontech-mysql actiontech-mysql 1179648 Jul 2 06:44 #ib_16384_0.dblwr
-rw-r----- 1 actiontech-mysql actiontech-mysql 9568256 Jul 2 06:32 #ib_16384_1.dblwr
-rw-r----- 1 actiontech-mysql actiontech-mysql 4569 Jul 12 08:10 ib_buffer_pool
-rw-r----- 1 actiontech-mysql actiontech-mysql 1073741824 Jul 12 08:10 ibdata1
drwxr-x--- 2 actiontech-mysql actiontech-mysql 6 Jul 12 08:10 #innodb_temp
drwxr-x--- 2 actiontech-mysql actiontech-mysql 143 Jul 2 06:32 mysql
-rw-r----- 1 actiontech-mysql actiontech-mysql 36780 Jul 12 08:10 mysql-error.log
-rw-r----- 1 actiontech-mysql actiontech-mysql 25165824 Jul 2 06:44 mysql.ibd
-rw-r----- 1 actiontech-mysql actiontech-mysql 406 Jul 2 06:38 mysql-slow.log
drwxr-x--- 2 actiontech-mysql actiontech-mysql 8192 Jul 2 06:32 performance_schema
-rw------- 1 actiontech-mysql actiontech-mysql 1676 Jul 2 06:32 private_key.pem
-rw-r--r-- 1 actiontech-mysql actiontech-mysql 452 Jul 2 06:32 public_key.pem
-rw-r--r-- 1 actiontech-mysql actiontech-mysql 1112 Jul 2 06:32 server-cert.pem
-rw------- 1 actiontech-mysql actiontech-mysql 1680 Jul 2 06:32 server-key.pem
drwxr-x--- 2 actiontech-mysql actiontech-mysql 28 Jul 2 06:32 sys
-rw-r----- 1 actiontech-universe actiontech 114 Jul 2 06:32 U_MYSQL_DATA_INSTALLED
-rw-r----- 1 actiontech-mysql actiontech-mysql 16777216 Jul 2 06:44 undo_001
-rw-r----- 1 actiontech-mysql actiontech-mysql 16777216 Jul 2 06:44 undo_002
drwxr-x--- 2 actiontech-mysql actiontech-mysql 25 Jul 2 06:32 universe
[root@localhost xbstream]# rm -rf /opt/mysql/data/3310/*
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 0
[root@localhost xbstream]#
#恢复
[root@localhost xbstream]# rm -rf /opt/mysql/data/3310/*
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 0
[root@localhost xbstream]# innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf --parallel=4 --apply-log /data/xbstream/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:1G:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=2147483648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=876423215 --redo-log-version=1 --parallel=4
xtrabackup: recognized client arguments:
240718 07:36:15 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
xtrabackup: cd to /data/xbstream/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=10747904, start_lsn=(19600455855)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup: innodb_log_group_home_dir = .
。。。。。。
InnoDB: 5.7.44 started; log sequence number 19609974805
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 19609974824
240718 07:36:24 completed OK!
#copy_back
[root@localhost xbstream]# innobackupex --defaults-file=/opt/mysql/etc/3310/my.cnf --parallel=4 --apply-log /data/xbstream/ ^C
[root@localhost xbstream]# xtrabackup --defaults-file=/opt/mysql/etc/3310/my.cnf --copy-back --target-dir=/data/xbstream/
xtrabackup: recognized server arguments: --server-id=1766520255 --datadir=/opt/mysql/data/3310 --log_bin=/opt/mysql/log/binlog/3310/mysql-bin --innodb_log_group_home_dir=/opt/mysql/log/redolog/3310 --tmpdir=/opt/mysql/tmp/3310 --innodb_buffer_pool_size=1G --innodb_data_file_path=ibdata1:1G:autoextend --innodb_file_per_table=1 --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT --innodb_log_buffer_size=64M --innodb_log_file_size=2G --innodb_log_files_in_group=2 --innodb_max_dirty_pages_pct=75 --innodb_io_capacity=3000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=4096 --open_files_limit=65535
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xbstream/
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
xtrabackup: Can't create/write to file '/opt/mysql/log/redolog/3310/ib_logfile0' (Errcode: 17 - File exists)
[01] error: cannot open the destination stream for ib_logfile0
[01] Error: copy_file() failed.
##清理redolog目录
[root@localhost xbstream]# mkdir -p /soft/log/
[root@localhost xbstream]# mv /opt/mysql/log/redolog/3310/* /soft/log/
[root@localhost xbstream]# xtrabackup --defaults-file=/opt/mysql/etc/3310/my.cnf --copy-back --target-dir=/data/xbstream/
xtrabackup: recognized server arguments: --server-id=1766520255 --datadir=/opt/mysql/data/3310 --log_bin=/opt/mysql/log/binlog/3310/mysql-bin --innodb_log_group_home_dir=/opt/mysql/log/redolog/3310 --tmpdir=/opt/mysql/tmp/3310 --innodb_buffer_pool_size=1G --innodb_data_file_path=ibdata1:1G:autoextend --innodb_file_per_table=1 --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT --innodb_log_buffer_size=64M --innodb_log_file_size=2G --innodb_log_files_in_group=2 --innodb_max_dirty_pages_pct=75 --innodb_io_capacity=3000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=4096 --open_files_limit=65535
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/xbstream/
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
240718 07:40:12 [01] Copying ib_logfile0 to /opt/mysql/log/redolog/3310/ib_logfile0
240718 07:40:15 [01] ...done
240718 07:40:15 [01] Copying ib_logfile1 to /opt/mysql/log/redolog/3310/ib_logfile1
240718 07:40:19 [01] ...done
240718 07:40:19 [01] Copying ibdata1 to /opt/mysql/data/3310/ibdata1
。。。。。。
240718 07:40:29 [01] ...done
240718 07:40:29 [01] Copying ./xtrabackup_binlog_pos_innodb to /opt/mysql/data/3310/xtrabackup_binlog_pos_innodb
240718 07:40:29 [01] ...done
240718 07:40:29 [01] Copying ./xtrabackup_master_key_id to /opt/mysql/data/3310/xtrabackup_master_key_id
240718 07:40:29 [01] ...done
240718 07:40:29 [01] Copying ./ibtmp1 to /opt/mysql/data/3310/ibtmp1
240718 07:40:29 [01] ...done
240718 07:40:29 completed OK!
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 1060908
drwxr-x--- 2 root root 20 Jul 18 07:40 ccic_prod_mass0
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass1
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass2
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass3
-rw-r----- 1 root root 419 Jul 18 07:40 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Jul 18 07:40 ibdata1
-rw-r----- 1 root root 12582912 Jul 18 07:40 ibtmp1
drwxr-x--- 2 root root 4096 Jul 18 07:40 mysql
drwxr-x--- 2 root root 8192 Jul 18 07:40 performance_schema
drwxr-x--- 2 root root 8192 Jul 18 07:40 sys
drwxr-x--- 2 root root 96 Jul 18 07:40 test
drwxr-x--- 2 root root 58 Jul 18 07:40 universe
-rw-r----- 1 root root 27 Jul 18 07:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 690 Jul 18 07:40 xtrabackup_info
-rw-r----- 1 root root 1 Jul 18 07:40 xtrabackup_master_key_id
[root@localhost xbstream]#
#修改数据目录和redolog目录属主
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 1060908
drwxr-x--- 2 root root 20 Jul 18 07:40 ccic_prod_mass0
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass1
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass2
drwxr-x--- 2 root root 284 Jul 18 07:40 ccic_prod_mass3
-rw-r----- 1 root root 419 Jul 18 07:40 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Jul 18 07:40 ibdata1
-rw-r----- 1 root root 12582912 Jul 18 07:40 ibtmp1
drwxr-x--- 2 root root 4096 Jul 18 07:40 mysql
drwxr-x--- 2 root root 8192 Jul 18 07:40 performance_schema
drwxr-x--- 2 root root 8192 Jul 18 07:40 sys
drwxr-x--- 2 root root 96 Jul 18 07:40 test
drwxr-x--- 2 root root 58 Jul 18 07:40 universe
-rw-r----- 1 root root 27 Jul 18 07:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 690 Jul 18 07:40 xtrabackup_info
-rw-r----- 1 root root 1 Jul 18 07:40 xtrabackup_master_key_id
[root@localhost xbstream]# chown -R actiontech-mysql:actiontech-mysql /opt/mysql/data/3310/*
[root@localhost xbstream]# ll /opt/mysql/data/3310/
total 1060908
drwxr-x--- 2 actiontech-mysql actiontech-mysql 20 Jul 18 07:40 ccic_prod_mass0
drwxr-x--- 2 actiontech-mysql actiontech-mysql 284 Jul 18 07:40 ccic_prod_mass1
drwxr-x--- 2 actiontech-mysql actiontech-mysql 284 Jul 18 07:40 ccic_prod_mass2
drwxr-x--- 2 actiontech-mysql actiontech-mysql 284 Jul 18 07:40 ccic_prod_mass3
-rw-r----- 1 actiontech-mysql actiontech-mysql 419 Jul 18 07:40 ib_buffer_pool
-rw-r----- 1 actiontech-mysql actiontech-mysql 1073741824 Jul 18 07:40 ibdata1
-rw-r----- 1 actiontech-mysql actiontech-mysql 12582912 Jul 18 07:40 ibtmp1
drwxr-x--- 2 actiontech-mysql actiontech-mysql 4096 Jul 18 07:40 mysql
drwxr-x--- 2 actiontech-mysql actiontech-mysql 8192 Jul 18 07:40 performance_schema
drwxr-x--- 2 actiontech-mysql actiontech-mysql 8192 Jul 18 07:40 sys
drwxr-x--- 2 actiontech-mysql actiontech-mysql 96 Jul 18 07:40 test
drwxr-x--- 2 actiontech-mysql actiontech-mysql 58 Jul 18 07:40 universe
-rw-r----- 1 actiontech-mysql actiontech-mysql 27 Jul 18 07:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 actiontech-mysql actiontech-mysql 690 Jul 18 07:40 xtrabackup_info
-rw-r----- 1 actiontech-mysql actiontech-mysql 1 Jul 18 07:40 xtrabackup_master_key_id
[root@localhost xbstream]#
[root@localhost xbstream]# chown -R actiontech-mysql:actiontech-mysql /opt/mysql/log/redolog/3310/*
[root@localhost xbstream]# ll /opt/mysql/log/redolog/3310/
total 4194304
-rw-r----- 1 actiontech-mysql actiontech-mysql 2147483648 Jul 18 07:40 ib_logfile0
-rw-r----- 1 actiontech-mysql actiontech-mysql 2147483648 Jul 18 07:40 ib_logfile1
[root@localhost xbstream]#
5.启动MySQL服务
[root@localhost xbstream]# /opt/mysql/base/8.0.28/bin/mysqld --defaults-file=/opt/mysql/etc/3310/my.cnf --daemonize --pid-file=/opt/mysql/data/3310/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3310/mysqld.sock --port=3310
mysqld will log errors to /opt/mysql/data/3310/mysql-error.log
mysqld is running as pid 8792
[root@localhost xbstream]# ps -ef |grep mysqld
actiont+ 8792 1 11 07:58 ? 00:00:05 /opt/mysql/base/8.0.28/bin/mysqld --defaults-file=/opt/mysql/etc/3310/my.cnf --daemonize --pid-file=/opt/mysql/data/3310/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3310/mysqld.sock --port=3310
root 9201 22919 0 07:59 pts/1 00:00:00 grep --color=auto mysqld
[root@localhost xbstream]# /opt/mysql/base/8.0.28/bin/mysql -uroot -p'P@ssw0rd' --socket=/opt/mysql/data/3310/mysqld.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 276
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| ccic_prod_mass0 |
| ccic_prod_mass1 |
| ccic_prod_mass2 |
| ccic_prod_mass3 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| universe |
+--------------------+
10 rows in set (0.00 sec)
mysql>
6.建立复制
[root@localhost xbstream]# cat xtrabackup_binlog_info
mysql-bin.000010 184074483 304e4a27-33ed-11ef-91b7-02000aba3b26:1-25571
mysql> CHANGE REPLICATION SOURCE TO
-> source_host='10.186.59.38',
-> source_port=3310,
-> source_user='repl',
-> source_password='P@ssw0rd',
-> source_log_file='mysql-bin.000010',
-> source_log_pos=184074483;
Query OK, 0 rows affected, 2 warnings (0.00 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: 10.186.59.38
Source_User: repl
Source_Port: 3310
Connect_Retry: 60
Source_Log_File: mysql-bin.000010
Read_Source_Log_Pos: 186664572
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 572087
Relay_Source_Log_File: mysql-bin.000010
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: 184646255
Relay_Log_Space: 2590602
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: 2262
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: 876423215
Source_UUID: 304e4a27-33ed-11ef-91b7-02000aba3b26
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 304e4a27-33ed-11ef-91b7-02000aba3b26:25572-31380
Executed_Gtid_Set: 304e4a27-33ed-11ef-91b7-02000aba3b26:1-4701:25572-26867,
8ef190cf-44db-11ef-91f2-02000aba3922:1-3,
d1476f88-383c-11ef-b4a6-02000aba3922:1-32
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>
原文地址：https://blog.csdn.net/m0_71902491/article/details/140521780
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！