ubuntu24
1. **下载并安装 percona‑release 仓库配置包：**
阿里云盘数据库下
# 安装 libaio1（提供 libaio.so.1）
sudo dpkg -i libaio1_0.3.112-5_amd64.deb
# 安装 xtrabackup
sudo dpkg -i percona-xtrabackup-24_2.4.29-1.jammy_amd64.deb
# 修复其余依赖（如 libev4 等）
sudo apt -f install
xtrabackup --version
2. **以上即可安装，如不行，尝试以下**
sudo apt --fix-broken install
sudo apt update
sudo apt upgrade
mysql5.7以下
sudo apt install percona-xtrabackup-24
mysql8
sudo apt install percona-xtrabackup-80
卸载
sudo apt remove percona-xtrabackup
sudo apt purge percona-xtrabackup
sudo apt autoremove
3. **安装 qpress 工具：**
阿里云盘mysql已下载
wget "https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230406/flxd/qpress-11-linux-x64.tar"
## 解压下载的tar包
tar -xvf qpress-11-linux-x64.tar
## 设置qpress文件的执行权限
sudo chmod 775 qpress
## 拷贝qpress到/usr/bin中，以便系统可以全局访问
sudo cp qpress /usr/bin
4. **两次解压：**
cat hins29720494_data_20240514053104_qp.xb | xbstream -x -v -C /db/2/
innobackupex --decompress --remove-original /db/2/
5. **准备：**
innobackupex --defaults-file=/db/2/backup-my.cnf --apply-log /db/2/
innobackupex --defaults-file=/db/2/backup-my.cnf --apply-log --use-memory=40G /db/2/
6. **docker-compose挂载my.cnf配置**
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
innodb_undo_directory=/var/lib/mysql
innodb_undo_tablespaces=2
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
7. **恢复：**
sudo innobackupex --defaults-file=/etc/my.cnf --copy-back /db/2/
因为是容器，临时创建一个my.cnf用于恢复，加入备份目录下backup-my.cnf中的内容
vim /etc/my.cnf
[mysqld]
datadir=/db/mysql5_data
innodb_undo_tablespaces=2
innodb_undo_directory=/db/mysql5_data
8. **修改密码**
docker compose加入跳过密码
services:
mysql:
image: registry.cn-hangzhou.aliyuncs.com/leiuvn/mysql:5.7
container_name: mysql5
restart: always
environment:
MYSQL_ALLOW_EMPTY_PASSWORD: "yes" # 可选：不设置密码，避免报错
command: --skip-grant-tables --skip-networking
ports:
- "3305:3306"
volumes:
- /db/mysql5_data:/var/lib/mysql
- /data/wwwroot/docker_compose/mysql5/my.cnf:/etc/my.cnf
networks:
lbu_net:
ipv4_address: 172.20.0.7
mem_limit: 16g
cpus: 4.0
networks:
lbu_net:
external: true
修改密码
ALTER USER 'aliyun_root'@'127.0.0.1' IDENTIFIED BY 'Shimujiuxia@2028';
FLUSH PRIVILEGES;
或新建root
FLUSH PRIVILEGES;
CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'Shimujiuxia@2028';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
如版本不一致需重新登录升级系统表结构
mysql_upgrade -h 127.0.0.1 -uroot -p
添加用户
create user 'lei'@'%' identified by 'Shimujiuxia@2028';
alter user 'lei'@'%' identified with mysql_native_password by 'Shimujiuxia@2028';
GRANT ALL PRIVILEGES ON *.* TO 'lei'@'%' IDENTIFIED BY 'Shimujiuxia@2028';
flush privileges;
centos7
https://help.aliyun.com/zh/rds/apsaradb-rds-for-mysql/restore-the-data-of-an-apsaradb-rds-for-mysql-instance-from-a-physical-backup-file-to-a-self-managed-mysql-database?spm=a2c4g.11186623.0.0.3d6c4ca3FWlRHW
1.安装工具
下载地址
https://www.percona.com/downloads
阿里云盘有下载好的
percona-release-1.0-27.noarch.rpm
rpm -ivh percona-release-1.0-27.noarch.rpm
yum list percona-xtrabackup*
yum install percona-xtrabackup-24.x86_64
2.解压
## 步骤一：解包
cat test_qp.xb | xbstream -x -v -C /var/mysql_bkdata/
cat hins29720494_data_20240514053104_qp.xb | xbstream -x -v -C /data/2/
## 步骤二：解压
### MySQL 5.5/5.6/5.7
innobackupex --decompress --remove-original /var/mysql_bkdata/
yum -y install qpress
innobackupex --decompress --remove-original /data/2/
### MySQL 8.0
/u01/xtrabackup80/bin/xtrabackup --decompress --remove-original --target-dir=/var/mysql_bkdata/
xtrabackup --decompress --remove-original --target-dir=/data/2/
3.准备
innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/
innobackupex --defaults-file=/data/2/backup-my.cnf --apply-log /data/2/
mysql8准备
xtrabackup --defaults-file=/data/mysql_data/backup-my.cnf --prepare --target-dir=/data/2/
--defaults-file
通过传入配置文件设置MySQL默认选项。
RDS MySQL备份文件中，提供名为backup-my.cnf的配置文件，该文件位于备份解压目录，即/var/mysql_bkdata/。
--apply-log
XtraBackup工具的准备命令。
该命令后配置存放备份文件的目录，即备份解压目录/var/mysql_bkdata/。
修改my.cnf文件
vim /etc/my.cnf
datadir = /data/mysql_data
innodb_undo_tablespaces=2
innodb_undo_directory=/data/mysql_data
cat /data/2/backup-my.cnf | grep innodb_undo_tablespaces
参数innodb_undo_tablespaces的取值需要与/var/mysql_bkdata/backup-my.cnf中的取值相同，您可以使用cat /var/mysql_bkdata/backup-my.cnf | grep innodb_undo_tablespaces查询。
4.恢复
sudo innobackupex --defaults-file=/etc/my.cnf --copy-back /data/2/
sudo xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/2/
参数解释：
--defaults-file自建数据库的my.cnf文件，根据此配置文件中设置的数据目录（datadir），获取恢复数据的目标路径。
--copy-backXtraBackup工具的恢复命令。
该命令后配置存放备份文件的目录，即备份解压目录/var/mysql_bkdata/，XtraBackup工具将此目录数据恢复到自建数据库的数据目录中。
5.重置root密码
vim /etc/my.cnf
skip-grant-tables
然后保存并关闭文件，重启mysql服务，此时mysql不再需要密码即可登录
mysql -u root -p
升级5.7后更新
1.启动数据库
./mysql_upgrade --defaults-file=/etc/my.cnf
2.重启服务
3.登录后先刷新权限
4.mysql5.7创建用户给权限修改密码
create user 'root'@'%' identified by 'shimujiuxia';
grant all on *.* to 'root'@'%';
alter user 'root'@'%' identified with mysql_native_password by 'shimujiuxia';
flush privileges;
update mysql.user set password=PASSWORD('shimujiuxia') where User='root';
flush privileges;
给远程用户权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'shimujiuxia';
flush privileges;