【Linux】重生之从零开始学习运维之备份恢复

备份恢复

准备工作

16主机-ubuntu系统

准备日志目录

mkdir -p /data/mysql/logs/
chown mysql:mysql -R /data/mysql

定制日志配置

vim /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin=/data/mysql/logs/binlog
systemctl restart mariadb

删除db1数据库

drop database db1;

13主机-ubuntu系统

准备工作

mkdir -p /data/mysql/logs
chown mysql:mysql -R /data/mysql
vim /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin=/data/mysql/logs/binlog
systemctl restart mariadb

CREATE TABLE `student` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) NOT NULL,
    ->   `age` int(11) NOT NULL,
    ->   `gender` enum('M', 'F') NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');

冷备份

16主机创建目录

mkdir -p /data/backup
cd /data/backup

13主机冷备份

关闭13主机服务

systemctl stop mariadb.service

备份数据并传给16主机

mkdir -p /data/backup
cd /data/backup
tar zcf base_data.tar.gz /var/lib/mysql
tar zcf binlog_data.tar.gz /data/mysql/logs
scp ./* root@10.0.0.16:/data/backup/

冷还原

16主机清空数据

systemctl stop mariadb.service
\rm -rf /var/lib/mysql/*
\rm -rf /data/mysql/logs/*
tar xf base_data.tar.gz
tar xf binlog_data.tar.gz

还原数据

mv ./data/mysql/logs/* /data/mysql/logs/
mv ./var/lib/mysql/* /var/lib/mysql/

systemctl start mariadb

测试

Mysqldump备份还原

单个数据库备份还原

准备工作

创建数据库

create database db2;
create table db2.student select * from db1.student;
create table db2.student2 select * from db1.student;
create table db2.student3 select * from db1.student;

insert into db1.student(name,age,gender) values('db1-user',55,'M');
insert into db2.student(name,age,gender) values('db2-user1',55,'M');
insert into db2.student2(name,age,gender) values('db2-user2',55,'M');
insert into db2.student3(name,age,gender) values('db2-user3',55,'M');

\rm -rf ./*

备份

mysqldump db1 > /data/backup/db1-bak.sql

破坏db1数据库

drop table db1.student;
show tables from db1;

还原

mysql db1 < ./db1-bak.sql

select * from db1.student;

多个数据库备份还原

备份

mysqldump -A > /data/backup/allsql-bak.sql

破坏数据库

drop database db1;
drop database db2;

还原

mysql < ./allsql-bak.sql
show databases;

二进制 POS 信息选择性恢复

准备工作

\rm -rf *

执行备份

mysqldump -B db2 --single-transaction --master-data > ./db2.sql

进行误操作

use db2;
select * from student;
insert into student (name,age) values('db2-user1',66);
update student set age=44 where id=4;
select * from student;
drop table student;误操作
insert into student2 (name,age) values('111',11);

查找误操作日志

grep "CHANGE MASTER" /data/backup/db2.sql
mysqlbinlog --start-position=2497717 /data/mysql/logs/binlog.000002 > db2_logbin.sql
grep -i 'drop' db2_logbin.sql
vim db2_logbin.sql
对误操作添加注释--

传递给13主机

scp ./db2_logbin.sql root@10.0.0.13:/root/
scp ./db2.sql  root@10.0.0.13:/root/

13主机还原数据

set sql_log_bin=0;临时关闭下
source /root/db2.sql;
set sql_log_bin=1;导入后重开

误操作数据传回16主机

mysqldump db2 student > db2-student.sql
scp db2-student.sql root@10.0.0.16:/root/

再回16主机还原误操作数据

set sql_log_bin=0;临时关闭
source /root/db2-student.sql;
set sql_log_bin=1;导入后再开启