备份恢复
准备工作
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.sqlselect * 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;导入后再开启
