华为云 通过全备文件.qp恢复到自建数据库

安装 mysql

# cat /etc/issue
Ubuntu 18.04.5 LTS \n \l

apt-get update

apt install mysql-server-5.7

# mysql -V
mysql  Ver 14.14 Distrib 5.7.42, for Linux (x86_64) using  EditLine wrapper

华为云 通过全备文件.qp恢复到自建数据库

wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
tar -xf qpress-11-linux-x64.tar
cp qpress /usr/local/bin/

https://docs.percona.com/percona-xtrabackup/2.4/installation/apt_repo.html#installing-percona-xtrabackup-via-percona-release
dpkg -i percona-release_latest.bionic_all.deb
percona-release enable-only tools release
apt install percona-xtrabackup-24
apt --fix-broken install

innobackupex  --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
innobackupex version 2.4.27 Linux (x86_64) (revision id: aae8e82)

# qpress -v
qpress 1.1 - Copyright 2006-2010 Lasse Reinhold - www.quicklz.com

导入全量备份文件

mkdir data

xbstream -x -p 4 < /root/备份文件.qp -C data/

# innobackupex --parallel 4 --decompress data/

 completed OK!

# innobackupex --apply-log data/

 completed OK!

systemctl stop mysql.service

cd /etc/mysql/
cp mysql.cnf mysql.cnf_backup

mv /var/lib/mysql /var/lib/mysql_bak
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql

# innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /seal/jiucun/data

 completed OK!

chown -R mysql:mysql /var/lib/mysql

systemctl restart mysql.service

journalctl -fu mysql

mysql查询表导出csv文件

SELECT *
INTO OUTFILE '/var/lib/mysql-files/user_1000000.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM user
LIMIT 1000000;

本地登录导出报错

mysql -u root -p

ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

修改配置

vim /etc/mysql/mysql.cnf

[mysqld]
skip-grant-tables

systemctl restart mysql.servic

systemctl status mysql.servic

参考

1. 通过备份文件恢复到自建MySQL数据库