通过系统命令进行全局冷备份

#1. 停止服务
systemctl stop mariadb

#2. 打包压缩数据库备份
tar Jcvf all.bak.xz /var/lib/mysql/

#3. 打包压缩二进制日志文件
tar Jcvf logbin.bak.xz /data/logbin/

#4. 创建文件夹备份数据库配置文件
mkdir db_backup
cp /etc/my.cnf db_backup

mysqldump备份工具

Scenario I

备份数据库

mysqldump -A --master-data=2 > /data/all.sql
mysql -e 'show master logs;'
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysql-logbin.000001 |      7655 |
+---------------------+-----------+

恢复备份

1. 阻止用户访问
2. 停止二进制日志生成

MariaDB [(none)]> set sql_log_bin=off;

3.准备二进制增量文件

• 查看备份文件 /data/all.sql 确定开始的二进制日志文件和开始位置
• 备份时的二进制日志文件是mysql.000001，位置是7655

CHANGE MASTER TO MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=7655;

MariaDB [(none)]> show master logs;
+--------------+-----------+
| Log_name     | File_size |
+--------------+-----------+
| mysql.000001 |      7674 |
| mysql.000002 |     30334 |
| mysql.000003 |   1038814 |
| mysql.000004 |       245 |
+--------------+-----------+

• 当前的二进制日志文件是mysql.000003
• 即生成增量的二进制文件：

[root@80_11 data]# mysqlbinlog  --start-position=7655 mysql.000001 > /data/inc.sql
[root@80_11 data]# mysqlbinlog mysql.000002 >> /data/inc.sql
[root@80_11 data]# mysqlbinlog mysql.000003 >> /data/inc.sql

4. 还原

## 恢复备份
MariaDB [test]> source /data/all.sql
## 恢复增量
MariaDB [test]> source /data/inc.sql

5. 恢复二进制日志

set sql_log_bin=on;

6. 恢复用户访问

Scenario II

image.png

备份

[root@80_11 data]# mysqldump -A --master-data=2 > /data/all_`date +%F`.sql
[root@80_11 data]# mysql -e "show master logs;"
+--------------+-----------+
| Log_name     | File_size |
+--------------+-----------+
| mysql.000001 |      7674 |
| mysql.000002 |     30334 |
| mysql.000003 |   1038814 |
| mysql.000004 |       245 |
+--------------+-----------+
CHANGE MASTER TO MASTER_LOG_FILE='mysql.000004', MASTER_LOG_POS=245;

模拟误操作

MariaDB [hellodb]> insert students(name,age) values('a',29);
MariaDB [hellodb]> insert students(name,age) values('b',39);
MariaDB [hellodb]> drop table students;
MariaDB [hellodb]> insert teachers(name,age) values('c',45);
MariaDB [hellodb]> insert teachers(name,age) values('d',88);

恢复

1. 阻止用户访问
2. 确定恢复二进制日志
   从 mysql.000004的245到mysql.000004的1325

MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+--------------+-----------+
| Log_name     | File_size |
+--------------+-----------+
| mysql.000001 |      7674 |
| mysql.000002 |     30334 |
| mysql.000003 |   1038814 |
| mysql.000004 |      1325 |
| mysql.000005 |       245 |
+--------------+-----------+

3. 生成二进制日志增量文件

[root@80_11 data]# mysqlbinlog --start-position=245 /data/mysql.000004 > /data/inc.sql

4. vim /data/inc.sql
   删除inc.sql中DROP TABLE命令
5. 清空数据库，重启mysql，关闭二进制日志

[root@80_11 ~]# rm -rf /var/lib/mysql/*
[root@80_11 ~]# systemctl restart mariadb
MariaDB [(none)]> set sql_log_bin=off;

6. 恢复数据库

MariaDB [test]> source /data/all_2020-09-19.sql
MariaDB [hellodb]> source /data/inc.sql

mysqldump 选项

# 备份同时生成新的日志
mysqldump -F -A --single-transaction --master-data=2 > /data/all`date +F%`.sql

# 清除已备份的日志
MariaDB [hellodb]> purge binary logs to 'mysql.000009';
# 清除日志从头计数
MariaDB [hellodb]> reset master;

建议的备份命令

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql 

分库备份压缩

for db in `mysql -e "show databases;" | grep -Ev '^(information_schema|performance_schema|Database)$'`; do mysqldump -B $db --single-transaction --master-data=2 | gzip > /data/$db.sql.gz; done

xtrabackup

完全备份和还原

1. 备份
   [root@80_11 ~]# xtrabackup --backup --target-dir=/backup

2. 在目标主机还原

# 1. 预准备，确保数据一致，提交完成的事务，回滚未完成的事务
[root@80_12 data]# xtrabackup --prepare --target-dir=/data/backup/
# 2. 复制到数据库目录（数据库目录为空，mysql不能启动）
xtrabackup --copy-back --target-dir=/data/backup
# 3. 还原属性
[root@80_12 data]# chown -R mysql.mysql mysql
# 4. 启动服务
systemctl restart mariadb

完全、增量备份及还原

1. 备份

#1 完全备份
xtrabackup --backup --target-dir=/backup/base
#2 第一次修改数据
MariaDB [hellodb]> insert students(name,age) values('lisi',40);
MariaDB [hellodb]> delete from students where name='a';
#3 第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base/
#4 第二次修改数据
delete from teachers where tid=6;
insert teachers(name,age) values('wangermazi',56);
#5 第二次增量
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1/
#6 复制到目标主机
scp -r /backup/ 192.168.80.12:/
#7 备份生成的三个目录
[root@80_11 ~]# tree -d /backup
/backup
├── base
│   ├── hellodb
│   ├── mysql
│   ├── performance_schema
│   └── test
├── inc1
│   ├── hellodb
│   ├── mysql
│   ├── performance_schema
│   └── test
└── inc2
    ├── hellodb
    ├── mysql
    ├── performance_schema
    └── test

2. 还原

#1 预准备完成备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base/
#2 合并第一次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base/ --incremental-dir=/backup/inc1
#3 合并第二次增量备份到完全备份
xtrabackup --prepare --target-dir=/backup/base/ --incremental-dri=/backup/inc2
#4 复制到数据库目录（数据库目录为空，mysql不能启动）
xtrabackup --copy-back --target-dir=/backup/base
#5 还原属性
chown -R mysql.mysql /data/mysql/
#6 启动服务
systemctl start mariadb