通过系统命令进行全局冷备份
#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 |
+---------------------+-----------+
恢复备份
- 阻止用户访问
- 停止二进制日志生成
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
- 还原
## 恢复备份
MariaDB [test]> source /data/all.sql
## 恢复增量
MariaDB [test]> source /data/inc.sql
- 恢复二进制日志
set sql_log_bin=on;
- 恢复用户访问
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);
恢复
- 阻止用户访问
- 确定恢复二进制日志
从 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 |
+--------------+-----------+
- 生成二进制日志增量文件
[root@80_11 data]# mysqlbinlog --start-position=245 /data/mysql.000004 > /data/inc.sql
- vim /data/inc.sql
删除inc.sql中DROP TABLE命令 - 清空数据库，重启mysql，关闭二进制日志
[root@80_11 ~]# rm -rf /var/lib/mysql/*
[root@80_11 ~]# systemctl restart mariadb
MariaDB [(none)]> set sql_log_bin=off;
- 恢复数据库
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
完全备份和还原
备份
[root@80_11 ~]# xtrabackup --backup --target-dir=/backup在目标主机还原
# 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 完全备份
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
- 还原
#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
