标签：binlog -- root xtrabackup 全量 mysql backup localhost
#一、利用xtrabackup实现完全备份及还原
1.下载并安装xtrabackup包
[root@centos8 ~]#wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
[root@localhost ~]# yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
2.安装mariadb数据库并启动服务
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl enable mariadb --now
[root@localhost ~]# mysql_secure_installation（初始化设置，设置root密码为123456，移除匿名用户等）
3.在原主机做完全备份到/backup目录
[root@localhost ~]# mkdir /backup
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
[root@localhost ~]# ls /backup/base/
backup-my.cnf mysql xtrabackup_checkpoints xtrabackup_logfile
ibdata1 performance_schema xtrabackup_info
4.目标主机无需创建/backup目录,直接复制目录本身
[root@centos7 ~]#scp -r /backup/ 目标主机:/backup1
5.删除数据库数据模拟目标主机
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# rm -rf /var/lib/mysql/*
6.还原数据
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@localhost ~]# ll /var/lib/mysql/
total 40976
-rw-r----- 1 root root 18874368 Nov 9 16:47 ibdata1
-rw-r----- 1 root root 5242880 Nov 9 16:47 ib_logfile0
-rw-r----- 1 root root 5242880 Nov 9 16:47 ib_logfile1
-rw-r----- 1 root root 12582912 Nov 9 16:47 ibtmp1
drwxr-x--- 2 root root 4096 Nov 9 16:47 mysql
drwxr-x--- 2 root root 4096 Nov 9 16:47 performance_schema
-rw-r----- 1 root root 440 Nov 9 16:47 xtrabackup_info
-rw-r----- 1 root root 1 Nov 9 16:47 xtrabackup_master_key_id
3）还原属性
[root@centos7 ~]#chown -R mysql:mysql /var/lib/mysql
[root@localhost ~]# ll /var/lib/mysql/
total 40976
-rw-r----- 1 mysql mysql 18874368 Nov 9 16:47 ibdata1
-rw-r----- 1 mysql mysql 5242880 Nov 9 16:47 ib_logfile0
-rw-r----- 1 mysql mysql 5242880 Nov 9 16:47 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov 9 16:47 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Nov 9 16:47 mysql
drwxr-x--- 2 mysql mysql 4096 Nov 9 16:47 performance_schema
-rw-r----- 1 mysql mysql 440 Nov 9 16:47 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Nov 9 16:47 xtrabackup_master_key_id
4）启动服务
[root@centos7 ~]#systemctl start mariadb
7.验证数据恢复成功
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#二、完全，增量+binlog还原
#完全备份
[root@centos7 ~]#yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
[root@centos7 ~]#mkdir /backup/
[root@centos7 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
#新增数据
MariaDB [(none)]> create database testdb
-> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
#第一次增量备份
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#第二次新增数据
MariaDB [(none)]> create database testdb2
-> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| testdb2 |
+--------------------+
5 rows in set (0.00 sec)
#第二次增量备份
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#二次增量备份后查看二进制位置，防止二次备份后再次有少量数据写入，这时就需要二进制日志来恢复数据
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 1645 |
+--------------------+-----------+
1 row in set (0.00 sec)
#第二次增备后新增数据：
MariaDB [(none)]> create database testdb3
-> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| testdb2 |
| testdb3 |
+--------------------+
6 rows in set (0.00 sec)
#拷贝binlog日志留作恢复
[root@localhost mysql]# cp -p /var/lib/mysql/mariadb-bin.000001 /backup/
#拷贝到还原主机
[root@localhost ~]#scp -r /backup/ 目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}
#数据还原过程：（数据库未启动状态）
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql/
6）启动服务：
[root@localhost ~]# systemctl start mariadb
#截至第二次增备的数据都已经恢复，但是第二次增备后原数据库还有可能会写入数据，最后的少量数据使用binlog恢复
[root@localhost mysql]# cat /backup/inc2/xtrabackup_binlog_info
mariadb-bin.000001 1645
#查看二进制文件，从1645开始，并导入到sql文件
[root@localhost ~]# mysqlbinlog /backup/mariadb-bin.000001 --start-position=1645 >/backup/binlog.sql
#临时关闭binlog记录功能，导入sql文件，再开机binlog功能
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /backup/binlog.sql
MariaDB [(none)]> set sql_log_bin=1;
#验证
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
| testdb2 |
| testdb3 |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
标签：binlog,
--,
root,
xtrabackup,
全量,
mysql,
backup,
localhost
From： https://www.cnblogs.com/tanll/p/17748201.html