使用Xb文件恢复msyql ubuntu18 mysql5.7

下载ubuntu 18

下载18.04 

安装 vmBox 建议文件大小为40g ,第一次还原 由于存储不够导致,后期又新创建40g

安装数据库   下载对应版本mysql

 wget https://cdn.mysql.com/archives/mysql-5.7/mysql-server_5.7.35-1ubuntu18.04_amd64.deb-bundle.tar
 apt --fix-broken install
 tar -xvf mysql-server_5.7.35-1ubuntu18.04_amd64.deb-bundle.tar 
 dpkg -i mysql-common_5.7.35-1ubuntu18.04_amd64.deb
 dpkg -i libmysqlclient20_5.7.35-1ubuntu18.04_amd64.deb 
 dpkg -i libmysqld-dev_5.7.35-1ubuntu18.04_amd64.deb 
 dpkg -i mysql-community-client_5.7.35-1ubuntu18.04_amd64.deb 
 dpkg -i mysql-client_5.7.35-1ubuntu18.04_amd64.deb
 dpkg -i mysql-community-source_5.7.35-1ubuntu18.04_amd64.deb 
 dpkg -i mysql-community-server_5.7.35-1ubuntu18.04_amd64.deb
 service mysql  status
 service mysql stop 
 service mysql  start
 service mysql  status 
 rm -rf *.deb

下载安装  percona-release_latest.bionic_all.deb

wget https://repo.percona.com/apt/percona-release_latest.bionic_all.deb
sudo dpkg -i percona-release_latest.bionic_all.deb 
apt-get update
sudo apt-get install percona-xtrabackup-24

建议安装rz 命令 代替ftp工具  实际场景还要看实际情况

下载安装 qpress下载  安装配置

tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
source /etc/profile

rz 上传备份  xb 文件 

#解包备份文件
xbstream -x --parallel=2  -C /data/mysql < /data/test.xb
#解压备份文件
xtrabackup --decompress --target-dir=/data/mysql


#/data/mysql为之前存储备份文件的目标目录 ，您可根据实际情况替换为实际路径。
#Percona Xtrabackup 在2.4.6及以上版本中才支持--remove-original选项。
#xtrabackup默认在解压缩时不删除原始的压缩文件，若需解压完删除原始的压缩文件，可在上面的命令中加上--#remove-original参数。

还原数据

xtrabackup --prepare  --target-dir=/data/mysql

显示如图表示成功

 修改配置文件

vim /data/mysql/backup-my.cnf

由于存在的版本问题，请将解压文件backup-my.cnf中如下参数进行注释。

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version

# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
#innodb_checksum_algorithm=crc32
#innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=4
innodb_log_file_size=1048576000
#innodb_fast_checksum=false
#innodb_page_size=16384
#innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=0
#redo_log_version=1
#server_uuid=76b51740-c209-11eb-a35028492ee0
#master_key_id=0
#innodb_fast_checksum=false

 关闭服务 清除之前 msyql目录 建文件拷贝到原mysql/data地址

 service mysql stop

更改文件权限 

chown -R mysql:mysql /data/mysql

启动恢复

mysqld_safe --defaults-file=/data/mysql/backup-my.cnf --user=mysql --datadir=/data/mysql &

root@luke-VirtualBox:/var/lib/mysql# mysqld_safe --defaults-file=/var/lib/mysql/backup-my.cnf --user=mysql --datadir=/var/lib/mysql &
[1] 7017
root@luke-VirtualBox:/var/lib/mysql# 2021-12-10T01:52:14.475803Z mysqld_safe Logging to '/var/lib/mysql/luke-VirtualBox.err'.
2021-12-10T01:52:14.487296Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

操作时可以新开一个窗口查看日志  /var/lib/mysql/luke-VirtualBox.err

mysql 安装后默认配置文件

/etc/mysql/mysql.conf.d/mysqld.cnf

root@luke-VirtualBox:/var/lib/mysql# cd /etc/mysql/
root@luke-VirtualBox:/etc/mysql# ll
总用量 32
drwxrwxrwx   4 mysql mysql  4096 12月 10 06:53 ./
drwxr-xr-x 123 root  root  12288 12月 10 06:54 ../
drwxr-xr-x   2 mysql mysql  4096 12月 10 06:53 conf.d/
lrwxrwxrwx   1 mysql mysql    24 12月  9 15:35 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--   1 mysql mysql   839 8月   3  2016 my.cnf.fallback
-rw-r--r--   1 mysql mysql  1200 6月   7  2021 mysql.cnf
drwxr-xr-x   2 mysql mysql  4096 12月 10 09:05 mysql.conf.d/
root@luke-VirtualBox:/etc/mysql# cd mysql.conf.d/
root@luke-VirtualBox:/etc/mysql/mysql.conf.d# ll
总用量 12
drwxr-xr-x 2 mysql mysql 4096 12月 10 09:05 ./
drwxrwxrwx 4 mysql mysql 4096 12月 10 06:53 ../
-rwxrwxrwx 1 mysql mysql 1676 12月 10 09:05 mysqld.cnf*
root@luke-VirtualBox:/etc/mysql/mysql.conf.d# more mysqld.cnf 
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#innodb_force_recovery = 6
#innodb_purge_threads = 1
#skip-name-resolve
#skip-grant-tables
root@luke-VirtualBox:/etc/mysql/mysql.conf.d# 

数据存储路径

datadir         = /var/lib/mysql

客户端登录 mysql 验证。
mysql  -uroot 

恢复后使用恢复文件中的用户名密码.

show databases; 查看库

use llll 进入库

show tables; 查看表