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

已于 2024-07-18 16:34:36 修改
阅读量273 收藏
点赞数 8
文章标签： mysql 数据库
于 2024-07-18 16:20:57 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_71902491/article/details/140521780
版权

总括：

生产环境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>

排骨~年糕
关注
  • 8
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用percona-xtrabackup备份MySQL数据
qq_41941256的博客
08-18 472
使用xtrabackup备份MySQL数据库
MySQL备份恢复--Xtrabackup
勿弃字纸
11-12 332
Percona XtraBackup是一个免费的，开源的，完整的在线备份解决方案，适用于所有版本的Percona Server for MySQL，MySQL和MariaDB。 Percona XtraBackup具有超过2,100,000次的下载量，可以在事务处理系统上执行在线非阻塞，紧密压缩，高度安全的备份，以便在计划维护期间应用程序保持完全可用状态。据官方介绍，这也是世界上惟一一款开源的能够对
MySQL热备份---Xtrabackup（一）完全备份
白雪滑落树梢
10-28 2782
文章目录一、xtrabackup介绍二、Xtrabackup优点三、备份原理四、参数选项四、完全备份并恢复 一、xtrabackup介绍 percona公司官网 https://www.percona.com/ Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）； mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就
MySQL数据库的物理备份（percona-xtrabackup 物理备份）
qq_52251769的博客
02-24 1117
注意：每次增量备份都是针对上一次的备份（周一完备，周二对周一进行增备，周三对周二进行增备）。现在整个备份过程完成，下面利用这些备份文件进行恢复数据。然后对周三进行差异备份（也是基于周一的备份）原理：每次备份上一次备份到现在产生的新数据。然后对周二进行差异备份（是基于周一的备份）这样整个数据库的数据就被备份下来了。进入数据库查看数据是否被恢复。然后对周三进行增量备份，回滚到某次差异，比如周三。查看一下生成的备份文件。然后对周二进行增量备份。
物理备份，xtrabackup包 适用于MySQL5.7及以前
10-07
总结来说，Xtrabackup作为MySQL物理备份的利器，不仅提供了高效、灵活的备份方式，而且在数据恢复方面表现出色，对于维护数据库的安全性至关重要。在实际操作中，需要根据具体环境和需求，合理规划和执行备份策略，...
基于xtrabackup的MySQL数据库备份及还原Shell脚本.zip
08-27
`xtrabackup`通过读取InnoDB的数据文件和Redo日志，生成一个完整的物理备份。由于它是无锁备份，所以在备份过程中，数据库仍然可以接受新的写入操作，这大大提高了备份效率。备份过程通常包括以下步骤： 1. **全量...
percona-xtrabackup
08-02
总结来说，Percona Xtrabackup作为MySQL数据库备份恢复的得力助手，能够帮助管理员有效应对数据安全挑战。通过了解和掌握其工作原理及使用技巧，可以大大提高数据库管理效率，保障业务连续性，降低因数据丢失带来的...
percona-xtrabackup-2.0.0
08-13
同时，应定期测试备份恢复流程，确保在真正需要时能够顺利恢复数据。 总结起来，Percona Xtrabackup 2.0.0是MySQL备份的利器，它的热备份、增量备份、压缩和恢复等功能为数据库管理员提供了强大的保障。对于任何...
编写脚本令Xtrabackup对MySQL数据进行备份的教程
12-15
【Xtrabackup MySQL数据备份教程】 Xtrabackup是Percona公司开发的一款用于MySQL数据库备份的开源工具，特别适用于InnoDB存储引擎的数据备份。它采用了高效且可靠的技术来确保备份过程中的数据一致性。在备份InnoDB...
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 398
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL 进阶（三）【SQL 优化】
梦想是动物管理员
07-13 1252
MySQL SQL 优化
mysql链接错误，unblock with ‘mysqladmin flush-hosts‘“
java之书-资源
07-16 269
message from server: "Host '172.16.39.238' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".
Mysql的insert批量插入性能问题
最新发布
m0_54123999的博客
07-17 157
同时分批插入也有数据一致性风险，如果在批次中途出现失败，部分数据可能已经插入，而部分数据可能未插入，需要额外处理数据一致性；当需要往数据库插入大量数据是，一条一条插入会导致插入时间长，性能不佳，所以我们一般会进行insert批量插入，一次性插入较大批次的数据，而不是每条记录单独插入，这通常会提高性能，因为批量操作减少了上下文切换和 SQL 解析的开销。事务期间，所有涉及的表或行会被锁定（根据具体的存储引擎），可能会导致更长时间的锁定，从而影响系统的并发性能，且资源占用高，大事务可能会占用较多的数据库资源。
Mysql具体数据操作和表的约束（上）
SDU MATH
07-13 657
Mysql具体数据操作和表的约束
基于SpringBoot+MySQL的租房项目+文档
欧阳小白闯天涯
07-11 974
在当今快节奏的生活中，房屋租赁市场的需求与日俱增。为了有效管理租房流程、提升用户体验，本文基于SpringBoot+MySQL技术栈，设计并实现了一套全方位的租房系统。该系统融合了经纪人、维修员、管理员和用户四个角色，各自拥有独特的功能模块，包括房源管理、维修处理、员工用户管理、统计分析等。经纪人角色能够方便地添加房源、管理自己的房源、处理房源出租事务，并通过系统提供的月出租量统计功能进行业绩分析与优化。其次，维修员可以及时响应待处理的房源报修请求，有效地管理已处理的房源维修记录。
【Python进阶】正则表达式、pymysql模块
weixin_52854743的博客
07-14 1278
Python中正则表达式和Pymysql的概述和操作
Postfix+Dovecot+Roundcube开源邮件系统搭建系列1-2：系统搭建目标+MariaDB数据库配置（MySQL）
sdexcel的专栏
07-14 1115
本系列文章主要讨论基于Postfix+Dovecot+Roundcube搭建开源邮件系统的方案。为了贴合实际需要，以虚拟邮件用户为基础，通过MariaDB数据库来管理邮件用户。请注意阅读前后文。
MySql知识点合集1
m0_61077780的博客
07-16 606
（1）用户定义函数不用于执行修改数据库状态的操作，（2）用户定义函数属于数据库，只能在该数据库下调用，（3）与系统函数一样，用户定义函数可以从查询中调用，其中用户定义函数可以在SQL查询语句的各个部分（如SELECTWHEREGROUP BYORDER BY等）中被调用和使用。这使得可以在查询中利用函数的功能来执行特定的计算、转换或逻辑操作。系统函数是数据库系统提供的预定义函数，如SUM()COUNT()等。这些函数可以直接用于查询中，例如计算总和、统计行数、格式化日期等操作。
用percona-xtrabackup工具写一个mysql增量备份的脚本
04-10
很高兴回答你的问题。对于使用Percona XtraBackup工具来实现MySQL增量备份的脚本，您可以参考以下代码： #!/bin/bash # MySQL credentials user="username" password="password" host="localhost" db_name="database_name" # Backup directory backup_path="/path/to/backup/folder" # Timestamp (sortable AND readable) timestamp=$(date +%Y-%m-%d_%H-%M-%S) # Full backup file name full_backup_name="$db_name-$timestamp-full-backup.sql" # Incremental backup file name incremental_backup_name="$db_name-$timestamp-incremental-backup.sql" # Log file log_file="$backup_path/backup.log" # Number of days to keep daily backups backup_retention=7 # Create backup folder mkdir -p $backup_path # Remove backups older than retention period find $backup_path/* -mtime +$backup_retention -exec rm {} \; # Lock tables to get consistent backup mysql --user=$user --password=$password --host=$host --execute="FLUSH TABLES WITH READ LOCK;" # Full backup echo "Starting full backup..." xtrabackup --backup --user=$user --password=$password --host=$host --target-dir=$backup_path --no-lock --parallel=4 --extra-lsndir=$backup_path --backup-and-apply-log-only > $log_file 2>&1 echo "Full backup complete: $full_backup_name" # Unlock tables to restore normal operation mysql --user=$user --password=$password --host=$host --execute="UNLOCK TABLES;" # Sleep to ensure that next backup is incremental sleep 10 # Incremental backup echo "Starting incremental backup..." xtrabackup --backup --user=$user --password=$password --host=$host --target-dir=$backup_path --no-lock --parallel=4 --extra-lsndir=$backup_path --backup-and-apply-log-only --incremental-basedir=$backup_path > $log_file 2>&1 echo "Incremental backup complete: $incremental_backup_name" # Unlock tables to restore normal operation mysql --user=$user --password=$password --host=$host --execute="UNLOCK TABLES;" # Compress backups echo "Compressing backups..." gzip -9 $backup_path/*.sql echo "Compression complete." # Done! echo "All backups complete."

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值