mysql备份工具xtrabackup

mysql备份工具xtrabackup

安装

yum安装基础环境

[root@irda ~]# yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL -y 
安装xtrabackup,centos7版本

[root@mysql-server56 mysql_script]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

yum本地安装

[root@mysql-server56 mysql_script]# yum localinstall percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm -y

查看

[root@mysql-server56 mysql_script]# ls -l `which xtrabackup innobackupex`
lrwxrwxrwx 1 root root       10 Apr 21 13:54 /usr/bin/innobackupex -> xtrabackup
-rwxr-xr-x 1 root root 21659096 Nov 23  2017 /usr/bin/xtrabackup

创建数据备份用户

[root@mysql-server56 mysql_script]# mysql -uroot -ppico-nf-8100 -P3306 -h127.0.0.1
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 44
Server version: 5.6.40-log MySQL Community Server (GPL)
​
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
​
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> create user 'backup'@'localhost' identified by 'chaoge888';
Query OK, 0 rows affected (0.00 sec)

​

回收用户默认权限

​

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)

​

授权部分权限

​

mysql> grant reload,lock tables,process,replication client on *.* to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)
#刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

全量备份与恢复

备份王者荣耀数据库kings

这是现有数据

[root@mysql-server56 data]# mysql -uroot -ppico-nf-8100 -P3306 -h127.0.0.1 -e "select * from kings.tanks;"

准备一个用于备份的文件夹

mkdir -p /xtrabackup/data/
[root@mysql-server56 data]# innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=pico-nf-8100 --socket=/data/3306/mysql.sock --no-timestamp /xtrabackup/data/all_db

执行命令结果

部分如下
[root@mysql-server56 data]# innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=pico-nf-8100 --socket=/data/3306/mysql.sock --no-timestamp /xtrabackup/data/all_db
210421 18:54:15 innobackupex: Starting the backup operation
​
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
​
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
/bin/pwd: couldn't find directory entry in ‘..’ with matching i-node
210421 18:54:15  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
210421 18:54:15  version_check Connected to MySQL server
210421 18:54:15  version_check Executing a version check against the server...
210421 18:54:15  version_check Done.
210421 18:54:15 Connecting to MySQL server host: localhost, user: root, password: set, port: 3307, socket: /data/3307/mysql.sock
Using server version 5.6.40-log
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision 
210421 18:54:16 Finished backing up non-InnoDB tables and files
210421 18:54:16 [00] Writing /xtrabackup/data/all_db/xtrabackup_binlog_info
210421 18:54:16 [00]        ...done