首页 > 数据库 >MySQL物理备份-XtraBackup远程备份恢复

MySQL物理备份-XtraBackup远程备份恢复

时间:2024-07-18 16:26:14浏览次数:19  
标签:07 -- 18 备份 MySQL Jul XtraBackup mysql root

总括:

模拟从库故障,从主库备份数据恢复从库,把主库数据远程备份到从库。

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 [email protected]  "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 [email protected] '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 [email protected]  "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>

标签:07,--,18,备份,MySQL,Jul,XtraBackup,mysql,root
From: https://blog.csdn.net/m0_71902491/article/details/140521780

相关文章

  • MySQL数据管理 --- 业务级MySQL
    MySQL数据管理---业务级MySQL外键(了解)方式一:在创建表的时候,增加约束(麻烦,比较复杂)DROPTABLEIFEXISTS`grade`;CREATETABLEIFNOTEXISTS`grade`(`gradeid`INT(5)NOTNULL,`gradename`VARCHAR(20)NOTNULL,PRIMARYKEY(`gradeid`))ENGINE=INNODBDEFAU......
  • mysql入门
    1.mysql下载 https://dev.mysql.com/downloads/mysql/我下载的是这个(两个的区别:上面的是联网安装,下面的是直接下载到本地安装)  2.下载后点击安装,按照指示一直点击下一步就可以安装过程中需要记录自己设置的密码,以及WindowsServiceName,之后连接的时候需要用到。 ......
  • MySQL如何优雅的执行DDL
    一、前言关于MySQLDDL表结构变更,各个工单平台基本上都支持了pt-osc及OnlineDDL的方式,但是,我相信仍然有一大部分人,不太了解这两种方式各自的优缺点是啥,以至于实际当中,会稀里糊涂的随机选一种去执行,选对了固然好,选错了,自然免不了领导的一顿K,这......当然是开玩笑的哈。 在各......
  • MySql指定数据库全量备份,保存七天
    1.mysql-bak.sh文件#配置变量DB_USER="root"DB_PASSWORD="xxx"DATABASES=("dbname1""dbname2")BACKUP_DIR="/data/mysql-bak"DATE=$(date+%Y%m%d)#创建备份文件名,包括时间戳forDB_NAMEin${DATABASES[*]}do BACKUP_NAME=&q......
  • springboot访问多个mysql数据库配置多数据源
    一、参考地址:https://github.com/baomidou/dynamic-datasource二、使用方法引入dynamic-datasource-spring-boot-starter或者dynamic-datasource-spring-boot3-starter。spring-boot1.5.x2.x.x点击查看代码<dependency><groupId>com.baomidou</groupId><art......
  • SpringBoot整合MyBatis+MySQL
    一、添加mysql驱动mysqlmysql-connector-java二、添加MyBatis依赖org.mybatis.spring.bootmybatis-spring-boot-starter3.0.1三、添加配置spring:datasource:name:xx-datasourcedriverClassName:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://127.0.0.1:3306/xx-......
  • Mysql联合索引和索引条件下推
    1、概念介绍联合索引联合索引和聚集索引一样使用B+树结构存储,联合索引的索引节点存储多个键值对,按首列数值排序,叶子节点存放所有的索引值以及对应的主键,如果需要查询非联合索引中的字段需要进行回表查询。联合索引的大小小于聚集索引。索引覆盖InnoDB存储引擎支持覆盖索引(cov......
  • mysql函数使用总结
    计算视频的完播率:https://www.nowcoder.com/discuss/353150021023506432?urlSource=sitemapSELECTvideo_id,ROUND((100*comp_play_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(TIMESTAMPDIFF(DAY,recently_end_date,cur_date)+1),0)ashot_indexFR......
  • docker-compose -- 创建 redis && mysql
    version:'3'services:nest-admin-web:image:buqiyuan/vue3-antdv-admin:stablecontainer_name:nest-admin-webvolumes:-./deploy/web:/etc/nginx/conf.dports:-'80:80'restart:alwaysnetworks:......
  • 一些数据库专家称,最新的 MySQL 版本令人失望
    据分析师认为,Oracle 的重点在其他方面,但要说所有创新都可以在社区版中运行是“不公平”的。MySQL 的最新版本让一些评论员和开发者们感到失望,他们担心开源数据库的管理者Oracle可能有其他的“优先事项”。本月初,长期以来一直推销其自有专有数据库系统的Oracle发布了MySQL ......