腾讯云-云数据库MySQL8数据恢复

恢复简介

这里采用的是MySQL 8.0版本，腾讯云云上数据库备份默认是qpress结合 Percona Xtrabackup 备份的。新找台空闲服务器来操作，这里演示的服务器系统为Ubuntu 22.04 LTS版本。需要提前安装好MySQL 8.0以上的数据库版本、Percona Xtrabackup和qpress

数据库安装

这里采用脚本的方式来安装数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
#!/bin/bash
#
#********************************************************************
#Author:        mr.luo            
#Date:          2025-02-26
#FileName：     install_mysql_for_ubuntu.sh
#URL:           https://www.dklwj.com
#Description：      The test script
#Copyright (C):     2025 All rights reserved
#********************************************************************

color () {
    RES_COL=60
    MOVE_TO_COL="echo -en \\033[${RES_COL}G"
    SETCOLOR_SUCCESS="echo -en \\033[1;32m"
    SETCOLOR_FAILURE="echo -en \\033[1;31m"
    SETCOLOR_WARNING="echo -en \\033[1;33m"
    SETCOLOR_NORMAL="echo -en \E[0m"
    echo -n "$1" && $MOVE_TO_COL
    echo -n "["
    if [ $2 = "success" -o $2 = "0" ] ;then
        ${SETCOLOR_SUCCESS}
        echo -n $"  OK  "    
    elif [ $2 = "failure" -o $2 = "1"  ] ;then 
        ${SETCOLOR_FAILURE}
        echo -n $"FAILED"
    else
        ${SETCOLOR_WARNING}
        echo -n $"WARNING"
    fi
    ${SETCOLOR_NORMAL}
    echo -n "]"
    echo 
}

install() {
	softpath="/app/soft"

    mysqlversion="mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz"

# 解压MySQL 二进制包到/app/soft下
tar xf ${softpath}/${mysqlversion} -C ${softpath}

if id mysql &> /dev/null ;then 
    color "mysql 用户已存在" 1 
else
    useradd -r -s /sbin/nologin mysql
    color "MySql  用户创建成功" 0
fi

mkdir /app/mydata/{logs,data} -p  && chown -R mysql.mysql /app/mydata

ln -s ${softpath}/mysql-8.0.39-linux-glibc2.28-x86_64/ /usr/local/mysql && chown -R mysql.mysql /usr/local/mysql/

cat >> /etc/my.cnf <<EOF
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
server_id=1
port = 3306
user = mysql
character-set-server = utf8
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /app/mydata/data
pid-file = /app/mydata/data/mysql.pid
max_connections = 10000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
log_bin = mysql-bin
binlog_format  = MIXED
sort_buffer_size = 16M
join_buffer_size = 16M
skip_ssl 
#####====================================[innodb]==============================
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_rollback_on_timeout = on
innodb_lock_wait_timeout = 28800
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_undo_tablespaces=3
lower_case_table_names = 1
skip_name_resolve = on
#####====================================[log]==============================
log_error = /app/mydata/logs/mysql-error.log 
slow_query_log = 1
long_query_time = 20 
log-queries-not-using-indexes
slow_query_log_file = /app/mydata/logs/mysql-slow.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
EOF

apt update && apt-get -y install libaio-dev  libncurses5

cd /usr/local/mysql && ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/app/mydata/data --explicit_defaults_for_timestamp && \
cp support-files/mysql.server /etc/init.d/mysqld && echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh && \
source /etc/profile.d/mysql.sh 

/etc/init.d/mysqld start
}

install

安装qpress

1
2
root@k8s-node-19:/app/soft# wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0" https://docs-tencentdb-1256569818.cos.ap-guangzhou.myqcloud.com/qpress-11-linux-x64.tar
root@k8s-node-19:/app/soft# tar -xf qpress-11-linux-x64.tar -C /usr/local/bin

安装Percona XtraBackup 8.0

1
2
3
4
5
6
7
8
9
10
root@k8s-node-19:/app/soft# apt update
root@k8s-node-19:/app/soft# apt install curl
root@k8s-node-19:/app/soft# curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
root@k8s-node-19:/app/soft# apt -y install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
root@k8s-node-19:/app/soft# apt update
root@k8s-node-19:/app/soft# percona-release setup pxb-80
root@k8s-node-19:/app/soft# apt -y install percona-xtrabackup-80
root@k8s-node-19:/app/soft# xtrabackup --version 
2025-02-26T11:03:32.995888+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --datadir=/app/mydata/data --open_files_limit=65535 --log_bin=mysql-bin --innodb_buffer_pool_size=1024M --innodb_file_per_table=1 --innodb_write_io_threads=4 --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=1 --innodb_log_file_size=512M --innodb_log_files_in_group=2 --innodb_log_buffer_size=16M --innodb_max_dirty_pages_pct=80 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_undo_tablespaces=3 
xtrabackup version 8.0.35-32 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c339924a)

恢复数据

1、创建新目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
root@k8s-node-19:/app/soft# mkdir /app/data
# 上传要恢复的数据文件。从腾讯云下载的备份文件
# 解压备份文件
root@k8s-node-19:/app/soft# xbstream -x -C /app/data < /app/xiaohuniu_mysql_backup_20250225000558.xb
# 由于腾讯云压缩后文件名以.qb结尾因此需要解压
root@k8s-node-19:/app/soft# cd /app/data
root@k8s-node-19:/app/data# ls
backup-my.cnf.qp     ibdata1.qp           mysql-bin.index.qp  sys          xhn_tax                    xtrabackup_info.qp        xtrabackup_tablespaces.qp
__cdb_recycle_bin__  mysql                mysql.ibd.qp        undo_001.qp  xtrabackup_binlog_info.qp  xtrabackup_logfile.qp
ib_buffer_pool.qp    mysql-bin.000116.qp  performance_schema  undo_002.qp  xtrabackup_checkpoints     xtrabackup_slave_info.qp
root@k8s-node-19:/app/data# xtrabackup --decompress --target-dir=/app/data
2025-02-26T10:26:51.549479+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --datadir=/app/mydata/data --open_files_limit=65535 --log_bin=mysql-bin --innodb_buffer_pool_size=1024M --innodb_file_per_table=1 --innodb_write_io_threads=4 --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=1 --innodb_log_file_size=512M --innodb_log_files_in_group=2 --innodb_log_buffer_size=16M --innodb_max_dirty_pages_pct=80 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_undo_tablespaces=3 
2025-02-26T10:26:51.549819+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --decompress=1 --target-dir=/app/data 
xtrabackup version 8.0.35-32 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c339924a)
2025-02-26T10:26:51.550474+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xhn_tax/yzh_depart_daili_amount_cash_record.ibd.qp
...
2025-02-26T10:26:52.721956+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_info.qp
2025-02-26T10:26:52.723406+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_tablespaces.qp
2025-02-26T10:26:52.758142+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
# 查看解压后的目录文件
root@k8s-node-19:/app/data# ls
backup-my.cnf        ibdata1              mysql-bin.index     sys          xhn_tax                    xtrabackup_info.qp        xtrabackup_tablespaces
backup-my.cnf.qp     ibdata1.qp           mysql-bin.index.qp  undo_001     xtrabackup_binlog_info     xtrabackup_logfile        xtrabackup_tablespaces.qp
__cdb_recycle_bin__  mysql                mysql.ibd           undo_001.qp  xtrabackup_binlog_info.qp  xtrabackup_logfile.qp
ib_buffer_pool       mysql-bin.000116     mysql.ibd.qp        undo_002     xtrabackup_checkpoints     xtrabackup_slave_info
ib_buffer_pool.qp    mysql-bin.000116.qp  performance_schema  undo_002.qp  xtrabackup_info            xtrabackup_slave_info.qp

2、备份解压出来之后，执行如下命令进行 apply log 操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root@k8s-node-19:/app/data# xtrabackup --prepare --target-dir=/app/data
2025-02-26T10:28:01.673279+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=1048576000 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
2025-02-26T10:28:01.673362+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/app/data 
xtrabackup version 8.0.35-32 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c339924a)
2025-02-26T10:28:01.673373+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /app/data/
2025-02-26T10:28:01.673399+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2025-02-26T10:28:01.677695+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(1070030577)
2025-02-26T10:28:01.678039+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2025-02-26T10:28:01.678057+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2025-02-26T10:28:01.678060+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2025-02-26T10:28:01.678097+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = .
2025-02-26T10:28:01.678100+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1
.....
2025-02-26T10:28:03.508334+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 1070038038
2025-02-26T10:28:03.510993+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
root@k8s-node-19:/app/data# ls
 backup-my.cnf         ibdata1         mysql-bin.000116      mysql.ibd.qp         undo_002                    xtrabackup_checkpoints   xtrabackup_slave_info
 backup-my.cnf.qp      ibdata1.qp      mysql-bin.000116.qp   performance_schema   undo_002.qp                 xtrabackup_info          xtrabackup_slave_info.qp
 __cdb_recycle_bin__   ibtmp1          mysql-bin.index       sys                  xhn_tax                     xtrabackup_info.qp       xtrabackup_tablespaces
 ib_buffer_pool       '#innodb_redo'   mysql-bin.index.qp    undo_001             xtrabackup_binlog_info      xtrabackup_logfile       xtrabackup_tablespaces.qp
 ib_buffer_pool.qp     mysql           mysql.ibd             undo_001.qp          xtrabackup_binlog_info.qp   xtrabackup_logfile.qp

3、修改配置文件

修改backup-my.cnf文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
root@k8s-node-19:/app/data# vim backup-my.cnf
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksums=1
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_file_size=1048576000
innodb_page_size=16384
innodb_undo_directory=./
innodb_undo_tablespaces=2
innodb_log_checksums=ON
innodb_redo_log_encrypt=OFF
innodb_undo_log_encrypt=OFF
lower_case_table_names=1

4、修改目录属性

由于解压后属主和属组都是root，要给它修改成MySQL用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
root@k8s-node-19:/app/data# chown -R mysql.mysql /app/data/
root@k8s-node-19:/app/data# ll 
total 211244
-rw-r----- 1 mysql mysql       56 Feb 26 10:29  auto.cnf
-rw-r--r-- 1 mysql mysql      397 Feb 26 10:32  backup-my.cnf
-rw-r----- 1 mysql mysql      443 Feb 26 10:26  backup-my.cnf.qp
-rw-r----- 1 mysql mysql      180 Feb 26 10:30  binlog.000001
-rw-r----- 1 mysql mysql      180 Feb 26 10:32  binlog.000002
-rw-r----- 1 mysql mysql      157 Feb 26 10:32  binlog.000003
-rw-r----- 1 mysql mysql       48 Feb 26 10:32  binlog.index
-rw------- 1 mysql mysql     1705 Feb 26 10:30  ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Feb 26 10:30  ca.pem
drwxr-x--- 2 mysql mysql       37 Feb 26 10:26  __cdb_recycle_bin__/
-rw-r--r-- 1 mysql mysql     1112 Feb 26 10:30  client-cert.pem
-rw------- 1 mysql mysql     1701 Feb 26 10:30  client-key.pem
-rw-r----- 1 mysql mysql   196608 Feb 26 10:38 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql  8585216 Feb 26 10:30 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql     5517 Feb 26 10:32  ib_buffer_pool
-rw-r----- 1 mysql mysql     1015 Feb 26 10:26  ib_buffer_pool.qp
-rw-r--r-- 1 mysql mysql 12582912 Feb 26 10:38  ibdata1
-rw-r----- 1 mysql mysql   161187 Feb 26 10:26  ibdata1.qp
...

5、启动数据库

上面的操作都修改好后就可以启动数据库了 

1
2
3
4
5
6
7
8
9
10
root@k8s-node-19:/app/data# mysqld_safe --defaults-file=/app/data/backup-my.cnf  --user=mysql --datadir=/app/data &
root@k8s-node-19:/app/data# ss -tnl 
State              Recv-Q             Send-Q                           Local Address:Port                            Peer Address:Port             Process             
LISTEN             0                  128                                  127.0.0.1:6010                                 0.0.0.0:*                                    
LISTEN             0                  4096                             127.0.0.53%lo:53                                   0.0.0.0:*                                    
LISTEN             0                  128                                    0.0.0.0:22                                   0.0.0.0:*                                    
LISTEN             0                  70                                           *:33060                                      *:*                                    
LISTEN             0                  128                                      [::1]:6010                                    [::]:*                                    
LISTEN             0                  128                                       [::]:22                                      [::]:*                                    
LISTEN             0                  151                                          *:3306                                       *:*

随后就可以用Navicat 等数据库软件连接了

数据库
#数据库
腾讯云-云数据库MySQL8数据恢复
https://www.dklwj.com/2025/02/Tencent-Cloud-Database-MySQL-8.0-Data-Recovery.html
作者
阿伟
发布于
2025年2月26日
许可协议